4:08 p.m.
Bug Oracle9iDialect Pagination
------------------------------
Key: HHH-3292
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.6
Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
Reporter: Mizael Montenegro
Priority: Critical
Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work
correctly, my English is terrible, then I cannot describe the complete problem here, more
I will post the string sql that solves the problem.
select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r
between ? AND ?
setInt(1, BEGIN);
setInt(2, END);
I altered the dialect and it perfectly worked with that solution. The largest problem is
when ordination, the order is used using pagination corresponds never to the exact return
of the original consultation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4:16 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3292) Bug Oracle9iDialect Pagination
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292?page=c...
]
Gail Badner commented on HHH-3292:
----------------------------------
Please attach a runnable test case (Java + mapping) that reproduces this issue.
Bug Oracle9iDialect Pagination
------------------------------
Key: HHH-3292
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.6
Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
Reporter: Mizael Montenegro
Priority: Critical
Original Estimate: 1 hour, 30 minutes
Remaining Estimate: 1 hour, 30 minutes
Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work
correctly, my English is terrible, then I cannot describe the complete problem here, more
I will post the string sql that solves the problem.
select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r
between ? AND ?
setInt(1, BEGIN);
setInt(2, END);
I altered the dialect and it perfectly worked with that solution. The largest problem is
when ordination, the order is used using pagination corresponds never to the exact return
of the original consultation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:08 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3292) Bug Oracle9iDialect Pagination
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292?page=c...
]
Mizael Montenegro commented on HHH-3292:
----------------------------------------
I execute the java code to generate consultation for RuaBean:
Session ses = HibernateUtil.getSession();
String hql = "from RuaBean order by tipoRua.nomTipoRua";
Query query = ses.createQuery(hql);
List list = query.list();
for (int i = 0; i < list.size(); i++) {
RuaBean rb = (RuaBean)list.get(i);
System.out.print(rb.getCodRua()+" - ");
}
The result is: 79 - 49 - 77 - 1 - 86 - 85 - 38 - 62 - 88 - 87 - 1 - 68 - 66 - 84 - 70
It's ok. But if use pagination:
query.setFirstResult(0);
query.setMaxResults(10);
This result is: 85 - 86 - 77 - 49 - 1 - 79 - 1 - 84 - 88 - 68
query.setFirstResult(10);
query.setMaxResults(5);
70 - 84 - 88 - 68 - 66
The sequence is totally wrong, it should be:
79 - 49 - 77 - 1 - 86 - 85 - 38 - 62 - 88 - 87 for the first consultation with pagination
and:
1 - 68 - 66 - 84 - 70 for second.
In the second, see that three values if they repeat.
This mapping of class RuaBean:
<class name="br.com.erpofm.bean.RuaBean" table="admin.tb_rua"
lazy="true">
<comment>Tabela de Ruas</comment>
<composite-id>
<key-property name="codRua" column="cod_rua"
type="integer" />
<key-many-to-one name="bairro"
class="br.com.erpofm.bean.BairroBean" >
<column name="cod_bairro" />
<column name="cod_cidade" />
<column name="cod_uf" />
</key-many-to-one>
</composite-id>
<many-to-one name="tipoRua"
class="br.com.erpofm.bean.TipoRuaBean">
<column name="cod_tipo_rua" />
</many-to-one>
<many-to-one name="usuarioCriacao"
class="br.com.erpofm.bean.UsuarioBean">
<column name="cod_usuario_criacao" />
</many-to-one>
<property name="nomRua" column="nom_rua"
type="string" />
<property name="dthCriacao" column="dth_criacao"
type="string" />
<property name="dthAlteracao" column="dth_alteracao"
type="string" />
<property name="codUsuarioAlteracao"
column="cod_usuario_alteracao" type="string" />
<property name="indBloqueado" column="ind_bloqueado"
type="string" />
</class>
TipoRuaBean:
<class name="br.com.erpofm.bean.TipoRuaBean"
table="admin.tb_tipo_rua" lazy="true">
<comment>Tabela de Tipos de Ruas</comment>
<composite-id>
<key-property column="cod_tipo_rua" name="codTipoRua"
type="integer"/>
</composite-id>
<many-to-one name="usuarioCriacao"
class="br.com.erpofm.bean.UsuarioBean">
<column name="cod_usuario_criacao"></column>
</many-to-one>
<property name="nomTipoRua" column="nom_tipo_rua"
type="string" />
<property name="dthCriacao" column="dth_criacao"
type="date" />
<property name="dthAlteracao" column="dth_alteracao"
type="string" />
<property name="codUsuarioAlteracao"
column="cod_usuario_alteracao" type="string" />
<property name="indBloqueado" column="ind_bloqueado"
type="string" />
</class>
UsuarioBean:
<class name="br.com.erpofm.bean.UsuarioBean" table="abd.tb_usr"
lazy="true">
<comment>Tabela de Usuarios</comment>
<composite-id>
<key-property column="nm_usr" name="nmUsr"
type="string"/>
</composite-id>
<property name="pwUsr" column="pw_usr"
type="string" />
<property name="nome" column="nome"
type="string" />
<property
name="cdUniOrc" column="cd_uni_orc" type="string" />
<property name="cargo" column="cargo"
type="string" />
<property name="cdUnadm"
column="cd_unadm" type="string" />
</class>
BairroBean:
<class name="br.com.erpofm.bean.BairroBean"
table="admin.tb_bairro" lazy="true">
<comment>Tabela de Bairros</comment>
<composite-id>
<key-property name="codBairro" column="cod_bairro"
type="integer" />
<key-many-to-one name="cidade"
class="br.com.erpofm.bean.CidadeBean">
<column name="cod_cidade" />
<column name="cod_uf" />
</key-many-to-one>
</composite-id>
<many-to-one name="usuarioCriacao"
class="br.com.erpofm.bean.UsuarioBean">
<column name="cod_usuario_criacao" />
</many-to-one>
<property name="nomBairro" column="nom_bairro"
type="string" />
<property name="dthCriacao" column="dth_criacao"
type="string" />
<property name="codUsuarioAlteracao"
column="cod_usuario_alteracao" type="string" />
<property name="dthAlteracao"
column="dth_alteracao" type="string" />
<property name="indBloqueado" column="ind_bloqueado"
type="string" />
</class>
it is oh all of the classes and mappings envolved. Thank you.
Bug Oracle9iDialect Pagination
------------------------------
Key: HHH-3292
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.6
Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
Reporter: Mizael Montenegro
Priority: Critical
Original Estimate: 1 hour, 30 minutes
Remaining Estimate: 1 hour, 30 minutes
Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work
correctly, my English is terrible, then I cannot describe the complete problem here, more
I will post the string sql that solves the problem.
select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r
between ? AND ?
setInt(1, BEGIN);
setInt(2, END);
I altered the dialect and it perfectly worked with that solution. The largest problem is
when ordination, the order is used using pagination corresponds never to the exact return
of the original consultation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:11 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3292) Bug Oracle9iDialect Pagination
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292?page=c...
]
Mizael Montenegro commented on HHH-3292:
----------------------------------------
The string sql generated:
select
*
from
( select
row_.*,
rownum rownum_
from
( select
ruabean0_.cod_rua as cod1_9_,
ruabean0_.cod_bairro as cod2_9_,
ruabean0_.cod_cidade as cod3_9_,
ruabean0_.cod_uf as cod4_9_,
ruabean0_.cod_tipo_rua as cod5_9_,
ruabean0_.cod_usuario_criacao as cod6_9_,
ruabean0_.nom_rua as nom7_9_,
ruabean0_.dth_criacao as dth8_9_,
ruabean0_.dth_alteracao as dth9_9_,
ruabean0_.cod_usuario_alteracao as cod10_9_,
ruabean0_.ind_bloqueado as ind11_9_
from
admin.tb_rua ruabean0_,
admin.tb_tipo_rua tiporuabea1_
where
ruabean0_.cod_tipo_rua=tiporuabea1_.cod_tipo_rua
order by
tiporuabea1_.nom_tipo_rua ) row_
where
rownum <= ?
)
where
rownum_ > ?
it is incorrect. Using:
select * from ( SELECT x.*, rownum as r FROM ( select
ruabean0_.cod_rua as cod1_9_,
ruabean0_.cod_bairro as cod2_9_,
ruabean0_.cod_cidade as cod3_9_,
ruabean0_.cod_uf as cod4_9_,
ruabean0_.cod_tipo_rua as cod5_9_,
ruabean0_.cod_usuario_criacao as cod6_9_,
ruabean0_.nom_rua as nom7_9_,
ruabean0_.dth_criacao as dth8_9_,
ruabean0_.dth_alteracao as dth9_9_,
ruabean0_.cod_usuario_alteracao as cod10_9_,
ruabean0_.ind_bloqueado as ind11_9_
from
admin.tb_rua ruabean0_,
admin.tb_tipo_rua tiporuabea1_
where
ruabean0_.cod_tipo_rua=tiporuabea1_.cod_tipo_rua
order by
tiporuabea1_.nom_tipo_rua ) x ) where r between ? AND ?
it's solves the problem. Thank you
Bug Oracle9iDialect Pagination
------------------------------
Key: HHH-3292
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.6
Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
Reporter: Mizael Montenegro
Priority: Critical
Original Estimate: 1 hour, 30 minutes
Remaining Estimate: 1 hour, 30 minutes
Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work
correctly, my English is terrible, then I cannot describe the complete problem here, more
I will post the string sql that solves the problem.
select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r
between ? AND ?
setInt(1, BEGIN);
setInt(2, END);
I altered the dialect and it perfectly worked with that solution. The largest problem is
when ordination, the order is used using pagination corresponds never to the exact return
of the original consultation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:08 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3292) Bug Oracle9iDialect Pagination
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292?page=c...
]
Mizael Montenegro commented on HHH-3292:
----------------------------------------
What was defined in relation to this bug?
Bug Oracle9iDialect Pagination
------------------------------
Key: HHH-3292
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3292
Project: Hibernate3
Issue Type: Bug
Components: core
Affects Versions: 3.2.6
Environment: Java2 6.0, Oracle 10i, Hibernate 3.2.6
Reporter: Mizael Montenegro
Priority: Critical
Original Estimate: 1 hour, 30 minutes
Remaining Estimate: 1 hour, 30 minutes
Hi, I found a flaw in the dialect of the oracle, the pagination doesn't work
correctly, my English is terrible, then I cannot describe the complete problem here, more
I will post the string sql that solves the problem.
select * from ( SELECT x.*, rownum as r FROM ( "CONSULT SQL" ) x ) where r
between ? AND ?
setInt(1, BEGIN);
setInt(2, END);
I altered the dialect and it perfectly worked with that solution. The largest problem is
when ordination, the order is used using pagination corresponds never to the exact return
of the original consultation.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5831
days inactive
5845
days old
hibernate-issues@lists.jboss.org
4 comments
2 participants
participants (2)
-
Gail Badner (JIRA) -
Mizael Montenegro (JIRA)