[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5185) Columns in Formulas not always rewritten, triggers ambiguous column SQL error
Chris Wilson (JIRA)
noreply at atlassian.com
Mon May 3 10:50:28 EDT 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36852#action_36852 ]
Chris Wilson commented on HHH-5185:
-----------------------------------
I found a workaround: Template.renderWhereStringTemplate() always precedes any quoted identifier with the placeholder, so you can write:
{code}
@Formula("EXTRACT(MONTH FROM `birthday`)")
{code}
and the test passes.
> 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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list