multiple outer joins producing wrong SQL
----------------------------------------
Key: HHH-1798
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1798
Project: Hibernate3
Type: Bug
Components: query-hql
Versions: 3.1.3
Environment: Hibernate 3.1.3 with DB2 8.2. Problem occurs with AST parser and classic parser.
Reporter: Olaf
Hibernate seems to wrongly "optimize away" multiple outer joins of same table:
Following HQL
---------------------
select
OrderImpl_AVS_1.Value,
OrderImpl_AVS_2.Value
from OrdersImpl as ord
left outer join ord.AttributeValueSet as avs_OrderImpl
left outer join avs_OrderImpl.AttributeValueSetValueSet as avss_OrderImpl
left outer join avss_OrderImpl.AttributeValue as OrderImpl_AVS_1 with OrderImpl_AVS_1.Attribute=1001101
left outer join avss_OrderImpl.AttributeValue as OrderImpl_AVS_2 with OrderImpl_AVS_2.Attribute=1001102
join ord.OrderItemSet as ois ,
Users as orderer,
Consumer as con,
.... statement continues ...
generates the following SQL:
----------------------------------------
select
attributev4_.VALUE as col_4_0_,
attributev4_.VALUE as col_5_0_
from WEBSHOP.ORDERS ordersimpl0_
left outer join WEBSHOP.ATTRIBUTE_VALUE_SET attributev2_ on ordersimpl0_.ATTRIBUTE_VALUE_SET_ID=attributev2_.ID
left outer join WEBSHOP.ATTRIBUTE_VALUE_SET_VALUE attributev3_ on attributev2_.ID=attributev3_.ATTRIBUTE_VALUE_SET_ID
left outer join WEBSHOP.ATTRIBUTE_VALUE attributev4_ on attributev3_.ATTRIBUTE_VALUE_ID=attributev4_.ID and (attributev4_.ATTRIBUTE_ID=1001102)
inner join WEBSHOP.ORDER_ITEM orderitems6_ on ordersimpl0_.ID=orderitems6_.ORDERS_ID and orderitems6_.subclass in (1,2,3,4,5,6,7),
WEBSHOP.USERS users7_,
WEBSHOP.CONSUMER consumer8_,
.... statement continues ...
which is semantically wrong. As you can see the two outer joins of the Table "AttributeValue" with the aliases
OrderImpl_AVS_1 and OrderImpl_AVS_2 are combined by Hibernate into one outer join with alias "attributev4_"
even though the with conditions differ ! Seems that the second outer join overwrites the first.
BTW:
====
the Porblem also occurs if you remove the with clause and define the additional conditions in the where clause, like
and (OrderImpl_AVS_1.Attribute=1001101 or OrderImpl_AVS_1.Attribute is null)
and (OrderImpl_AVS_2.Attribute=1001102 or OrderImpl_AVS_2.Attribute is null)
in this case hibernate just ignores the first "and ..." condition and only generates SQL for the second
--
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.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
|
