11:08 a.m.
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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:18 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3988) double left join within same column names returns wrong result
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=c...
]
Penkov Vladimir commented on HHH-3988:
--------------------------------------
The same thing with hibernate-3.3.2.GA
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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:26 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3988) double left join within same column names returns wrong result
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=c...
]
Gail Badner commented on HHH-3988:
----------------------------------
Please attach a runnable test case (Java + mapping) to reproduce this issue.
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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:06 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-3988) double left join within same column names returns wrong result
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=c...
]
Penkov Vladimir updated HHH-3988:
---------------------------------
Attachment: jointest.xml
JoinTest.java
Test case
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
9:06 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3988) double left join within same column names returns wrong result
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=c...
]
Penkov Vladimir commented on HHH-3988:
--------------------------------------
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');
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:16 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3988) double left join within same column names returns wrong result
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:15 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3988) double left join within same column names returns wrong result
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3988?page=c...
]
Penkov Vladimir commented on HHH-3988:
--------------------------------------
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.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5457
days inactive
5470
days old
hibernate-issues@lists.jboss.org
6 comments
3 participants
participants (3)
-
Bart Vanhaute (JIRA) -
Gail Badner (JIRA)
-
Penkov Vladimir (JIRA)