[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 11:16:32 EDT 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36854#action_36854 ]
Chris Wilson commented on HHH-5185:
-----------------------------------
The problem seems to be that Template.renderWhereStringTemplate() finds the word FROM in BEFORE_TABLE_KEYWORDS, so it thinks that the next word is a table name (sets beforeTable to true), and then the next word is treated as a table name and no placeholder is added.
I can't see an obvious way to work around this by changing the dialect. Removing FROM from BEFORE_TABLE_KEYWORDS would probably break support for embedded SELECT statements in formulas.
Perhaps the EJB specification needs an official standard template for table name placeholders for use in formulas, to avoid the need for implementations to try to parse SQL.
> 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