[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-1798) multiple outer joins producing wrong SQL

[Olaf (JIRA) <no...@at...>]

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