[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-5185) Columns in Formulas not always rewritten, triggers ambiguous column SQL error

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 Assignee: Gail Badner 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.