9:11 a.m.
Columns in Formulas not always rewritten, triggers ambiguous column SQL error
-----------------------------------------------------------------------------
Key: HHH-5185
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5185
Project: Hibernate Core
Issue Type: Bug
Components: core
Affects Versions: 3.5.1
Environment: MySQL Server version: 5.0.51a-3ubuntu5.5 (Ubuntu)
Hibernate version 3.5.1-Final
Reporter: Chris Wilson
Attachments: HibernateJoinFormulaAliasTest.java
The attached test case joins an entity with two @Formulas twice. The first @Formula is the
algebraic expression "(kittens * 4) + 3", the second is a SQL function
"EXTRACT(MONTH FROM birthday)".
kittens is rewritten but birthday is not. Because the table is joined twice, MySQL reports
the error:
{code}
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column
'birthday' in field list is ambiguous
{code}
which shows up as:
{code}
org.hibernate.exception.ConstraintViolationException: could not load an entity:
[org.aptivate.hibernate.test.HibernateJoinFormulaAliasTest$House#1]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1937)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:76)
at
org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3270)
at
org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:496)
at
org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:477)
at
org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:227)
at
org.hibernate.event.def.DefaultLoadEventListener.lockAndLoad(DefaultLoadEventListener.java:403)
at
org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:155)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:1080)
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:1065)
at org.hibernate.impl.SessionImpl.get(SessionImpl.java:1056)
at
org.aptivate.hibernate.test.HibernateJoinFormulaAliasTest.testFailing(HibernateJoinFormulaAliasTest.java:97)
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:597)
at
org.aptivate.hibernate.test.base.HibernateTestBase.runTestMethod(HibernateTestBase.java:204)
at
org.aptivate.hibernate.test.base.HibernateTestBase.runTest(HibernateTestBase.java:117)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at
org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Column 'birthday' in field list is ambiguous
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1849)
at org.hibernate.loader.Loader.doQuery(Loader.java:718)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1933)
... 31 more
{code}
The rewritten SQL is:
{code}
select
hibernatej0_.id as id0_2_,
cats1x1_.firstHome_id as firstHome4_0_4_,
cats1x1_.id as id4_,
cats1x1_.id as id1_0_,
cats1x1_.birthday as birthday1_0_,
cats1x1_.firstHome_id as firstHome4_1_0_,
cats1x1_.kittens as kittens1_0_,
cats1x1_.secondHome_id as secondHome5_1_0_,
EXTRACT(MONTH
FROM
birthday) as formula0_0_,
(cats1x1_.kittens * 4) + 3 as formula1_0_,
cats2x2_.secondHome_id as secondHome5_0_5_,
cats2x2_.id as id5_,
cats2x2_.id as id1_1_,
cats2x2_.birthday as birthday1_1_,
cats2x2_.firstHome_id as firstHome4_1_1_,
cats2x2_.kittens as kittens1_1_,
cats2x2_.secondHome_id as secondHome5_1_1_,
EXTRACT(MONTH
FROM
birthday) as formula0_1_,
(cats2x2_.kittens * 4) + 3 as formula1_1_
from
houses hibernatej0_
left outer join
cats cats1x1_
on hibernatej0_.id=cats1x1_.firstHome_id
left outer join
cats cats2x2_
on hibernatej0_.id=cats2x2_.secondHome_id
where
hibernatej0_.id=?
{code}
There's no apparent workaround that lets us reference the same column in a formula in
a table that's joined twice.
--
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