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

Environment

mysql 5.0.51b

Activity

Show:
Vladimir Penkov
June 25, 2009, 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');

Bart Vanhaute
July 1, 2009, 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
July 7, 2009, 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.

Brett Meyer
April 7, 2014, 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!

Brett Meyer
July 8, 2014, 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!

Assignee

Unassigned

Reporter

Vladimir Penkov

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure