double left join within same column names returns wrong result

Description

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

Attachments

2

Activity

Show:

Brett Meyer July 8, 2014 at 3:11 PM

Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!

Brett Meyer April 7, 2014 at 5:47 PM

In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.

If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.

Thank you!

Vladimir Penkov July 7, 2009 at 8:14 AM

No, this workaround doesn't seem to work:

617 [main] INFO org.hibernate.tool.hbm2ddl.SchemaUpdate - schema update complete
Hibernate: 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
737 [main] INFO org.hibernate.type.StringType - could not read column value from result set: name; Column 'name' not found.
738 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: S0022
738 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Column 'name' not found.
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at mixdev.web.adressbook.module.JoinTest.test(JoinTest.java:54)
at mixdev.web.adressbook.module.JoinTest.main(JoinTest.java:35)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90)
Caused by: java.sql.SQLException: Column 'name' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1093)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5488)
at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:225)
at org.hibernate.type.StringType.get(StringType.java:41)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
at org.hibernate.loader.Loader.doQuery(Loader.java:724)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 13 more

Process finished with exit code 1

But the sql from workaround do works in mysql console.

Bart Vanhaute July 1, 2009 at 11:15 AM

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

Vladimir Penkov June 25, 2009 at 9:06 AM

this is database data for the test case:

CREATE TABLE test_log (
from_number VARCHAR( 5 ) NOT NULL,
to_number VARCHAR( 5 ) NOT NULL
);

CREATE TABLE test_entries (
number VARCHAR( 5 ) NOT NULL,
name VARCHAR( 5 ) NOT NULL
);

insert into test_log values ('111', '222');
insert into test_log values ('222', '111');
insert into test_log values ('111', '123');
insert into test_log values ('123', '111');
insert into test_log values ('222', '123');
insert into test_log values ('123', '222');

insert into test_entries values ('111', 'Bill');
insert into test_entries values ('222', 'Alisa');

Rejected

Details

Assignee

Reporter

Components

Affects versions

Priority

Created June 24, 2009 at 11:08 AM
Updated July 8, 2014 at 3:11 PM
Resolved July 8, 2014 at 3:11 PM