[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3988) double left join within same column names returns wrong result

[Bart Vanhaute (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33507#action_33507 ] 

Bart Vanhaute commented on HHH-3988:
------------------------------------

Note that the join does not even have to be on the same table. If you have two different tables with identical column names, and create a query that joins these two tables and select tthose identical columns, you will see the same problem. 

As a workaround, define an alias for at least one of the identical column names in your query. 
For instance: 
select log.from_number, log.to_number, entries_from.name fromName, entries_to.name toName
from test_log log
left join test_entries entries_from on log.from_number=entries_from.number
left join test_entries entries_to on log.to_number=entries_to.number


> double left join within same column names returns wrong result
> --------------------------------------------------------------
>
>                 Key: HHH-3988
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.2.4.sp1
>         Environment: mysql 5.0.51b
>            Reporter: Penkov Vladimir
>         Attachments: JoinTest.java, jointest.xml
>
>
> I have 2 tables: 
> audit: 
> from_number to_number 
> 123 321 
> 222 111 
> 333 123 
> address_book_contact: 
> phone name 
> 123 Bill 
> this query 
> SELECT a.from_number, a.to_number, b.name, c.name FROM audit a 
> LEFT JOIN address_book_contact b 
> ON (a.FROM_NUMBER=b.PHONE) 
> LEFT JOIN address_book_contact c 
> ON (a.TO_NUMBER=c.PHONE) 
> returns 
> 123 321 Bill null 
> 222 111 null null 
> 333 123 null Bill 
> in mysql query browser  and in java app using pure jdbc
> and returns 
> 123 321 Bill Bill 
> 222 111 null null 
> 333 123 null null 
> in java app using hibernate native query

-- 
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