Hi everybody,
we have experienced what seems to be an Hibernate bug (we are using version 3.0.5) in the way Hibernate builds SQL join queries in certain circumstances. In detail: when a field of the HQL select clause refers to a many-to-one property and the HQL from clause contains more than one class, including a class mapped as a subclass (using discriminator) and this class is the LAST in the from list, the generated SQL queries miss the join conditions required to obtain the correct result.
In the following example 'role' is the many-to-one property and ProductionPlant is a subclass of Plant. The mapping files are the following:
<class name="Role" table="SCB_ROLE">
<id name="cod" column="ID">
<generator class="assigned"/>
</id>
<property name="desc" column="DESCRIPTION"/>
</class>
<class name="Plant" table="SCB_PLANT" discriminator-value="0">
<id name="cod" column="ID">
<generator class="assigned"/>
</id>
<discriminator column="TYPE" type="integer"/>
<property name="desc" column="DESCRIPTION"/>
<subclass name="ProductionPlant" discriminator-value="1"/>
<subclass name="MainPlant" discriminator-value="2"/>
</class>
<class name="User" table="SCB_USER">
<id name="cod" column="ID">
<generator class="assigned"/>
</id>
<property name="desc" column="DESCRIPTION"/>
<many-to-one name="role" column="ROLE"/>
</class>
And these are the queries:
Not working:
HQL: select u.role, p.cod from User as u, ProductionPlant as p
SQL: select user0_.ROLE as col_0_0_, production1_.ID as col_1_0_, role2_.ID as ID, role2_.DESCRIPTION as DESCRIPT2_1_ from SCB_USER user0_, SCB_ROLE role2_, SCB_PLANT production1_ where production1_.TYPE=1
Working:
HQL: select u.role, p.cod from ProductionPlant as p, User as u
SQL: select user1_.ROLE as col_0_0_, production0_.ID as col_1_0_, role2_.ID as ID, role2_.DESCRIPTION as DESCRIPT2_1_ from SCB_PLANT production0_, SCB_USER user1_, SCB_ROLE role2_ where production0_.TYPE=1
and user1_.ROLE=role2_.ID
As you can see, the order in which the classes appear in the from clause affects the generated SQL query.
Thank you for your attention.
Regards,
Lorenzo and Luca
Login
FAQ
Search
