[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2610) Invalid SQL generation for dynamic filter

Invalid SQL generation for dynamic filter ----------------------------------------- Key: HHH-2610 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2610 Project: Hibernate3 Issue Type: Bug Components: core Affects Versions: 3.2.3 Environment: Windows XP, database MS-SQL and MySQL, Hibernate 3.2.3 Reporter: Calin Pavel I have a simple object model composed from three classes with their relationship as described above by mapping file. One of them is the superclass for the other two, and I have defined a filter for it. If filter is activated and try to retrieve an instance of subclass, generated SQL is invalid and an exception is thrown (see logs below). Hibernate: 3.2.3 DB: MySQL 5.0.27, MS-SQL 2000, ... Mappings: <hibernate-mapping> <class name="test.model.Entity" table="entity"> <id name="id" column="id" type="long"> <generator class="identity" /> </id> <property name="siteId" type="string"> <column name="siteId" /> </property> <joined-subclass name="test.model.User" table="user"> <key column="id" /> <property name="FirstName" type="string"> <column name="firstName" /> </property> <property name="LastName" type="string"> <column name="lastName" /> </property> <one-to-one name="Account" property-ref="User" cascade="delete" /> </joined-subclass> <joined-subclass name="test.model.Account" table="account"> <key column="id" /> <property name="name" type="string"> <column name="name" /> </property> <many-to-one name="User" column="user_id" class="test.model.User" unique="true" not-null="true" /> </joined-subclass> <filter name="siteIdFilter" condition="siteId=:siteId" /> </class> <filter-def name="siteIdFilter"> <filter-param name="siteId" type="string" /> </filter-def> </hibernate-mapping> Code: Session session = HibernateUtils.currentSession(); session.enableFilter("siteIdFilter").setParameter("siteId", "opp"); User user = (User) session.get(User.class, new Long(3)); session.close(); Stacktrace: 11:18:16,475 DEBUG DefaultLoadEventListener:143 - loading entity: [test.model.User#3] 11:18:16,475 DEBUG DefaultLoadEventListener:290 - attempting to resolve: [test.model.User#3] 11:18:16,475 DEBUG DefaultLoadEventListener:326 - object not resolved in any cache: [test.model.User#3] 11:18:16,475 DEBUG BasicEntityPersister:2467 - Materializing entity: [test.model.User#3] 11:18:16,522 DEBUG EntityLoader:95 - Static select for entity test.model.User: select user0_.id as id1_, user0_1_.siteId as siteId0_1_, user0_.firstName as firstName1_1_, user0_.lastName as lastName1_1_, account1_.id as id0_, account1_1_.siteId as siteId0_0_, account1_.name as name2_0_, account1_.user_id as user3_2_0_ from user user0_ inner join entity user0_1_ on user0_.id=user0_1_.id left outer join account account1_ on user0_.id=account1_.user_id and account1_1_.siteId=:siteIdFilter.siteId left outer join entity account1_1_ on account1_.id=account1_1_.id where user0_.id=? 11:18:16,522 DEBUG Loader:1340 - loading entity: [test.model.User#3] 11:18:16,522 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0) 11:18:16,522 DEBUG SQL:324 - select user0_.id as id1_, user0_1_.siteId as siteId0_1_, user0_.firstName as firstName1_1_, user0_.lastName as lastName1_1_, account1_.id as id0_, account1_1_.siteId as siteId0_0_, account1_.name as name2_0_, account1_.user_id as user3_2_0_ from user user0_ inner join entity user0_1_ on user0_.id=user0_1_.id left outer join account account1_ on user0_.id=account1_.user_id and account1_1_.siteId=? left outer join entity account1_1_ on account1_.id=account1_1_.id where user0_.id=? Hibernate: select user0_.id as id1_, user0_1_.siteId as siteId0_1_, user0_.firstName as firstName1_1_, user0_.lastName as lastName1_1_, account1_.id as id0_, account1_1_.siteId as siteId0_0_, account1_.name as name2_0_, account1_.user_id as user3_2_0_ from user user0_ inner join entity user0_1_ on user0_.id=user0_1_.id left outer join account account1_ on user0_.id=account1_.user_id and account1_1_.siteId=? left outer join entity account1_1_ on account1_.id=account1_1_.id where user0_.id=? 11:18:16,522 DEBUG AbstractBatcher:378 - preparing statement 11:18:16,553 DEBUG StringType:59 - binding 'opp' to parameter: 1 11:18:16,553 DEBUG LongType:59 - binding '3' to parameter: 2 11:18:16,569 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1) 11:18:16,569 DEBUG AbstractBatcher:416 - closing statement 11:18:16,569 DEBUG JDBCExceptionReporter:63 - could not load an entity: [test.model.User#3] [select user0_.id as id1_, user0_1_.siteId as siteId0_1_, user0_.firstName as firstName1_1_, user0_.lastName as lastName1_1_, account1_.id as id0_, account1_1_.siteId as siteId0_0_, account1_.name as name2_0_, account1_.user_id as user3_2_0_ from user user0_ inner join entity user0_1_ on user0_.id=user0_1_.id left outer join account account1_ on user0_.id=account1_.user_id and account1_1_.siteId=:siteIdFilter.siteId left outer join entity account1_1_ on account1_.id=account1_1_.id where user0_.id=?] java.sql.SQLException: Unknown column 'account1_1_.siteId' in 'on clause' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2994) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120) at org.hibernate.loader.Loader.getResultSet(Loader.java:1272) at org.hibernate.loader.Loader.doQuery(Loader.java:391) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218) at org.hibernate.loader.Loader.loadEntity(Loader.java:1345) at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116) at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:101) at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersister.java:2471) at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:351) at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:332) at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:113) at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:167) at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:79) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:621) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:614) at test.FilterMain.runMain2(FilterMain.java:47) at test.FilterMain.main(FilterMain.java:33) 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:324) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90) 11:18:16,569 WARN JDBCExceptionReporter:71 - SQL Error: 1054, SQLState: 42S22 11:18:16,569 ERROR JDBCExceptionReporter:72 - Unknown column 'account1_1_.siteId' in 'on clause' 11:18:16,569 INFO DefaultLoadEventListener:85 - Error performing load command org.hibernate.exception.SQLGrammarException: could not load an entity: [test.model.User#3] at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.loadEntity(Loader.java:1359) at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116) at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:101) at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersister.java:2471) at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:351) at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:332) at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:113) at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:167) at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:79) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:621) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:614) at test.FilterMain.runMain2(FilterMain.java:47) at test.FilterMain.main(FilterMain.java:33) 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:324) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90) Caused by: java.sql.SQLException: Unknown column 'account1_1_.siteId' in 'on clause' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2994) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120) at org.hibernate.loader.Loader.getResultSet(Loader.java:1272) at org.hibernate.loader.Loader.doQuery(Loader.java:391) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218) at org.hibernate.loader.Loader.loadEntity(Loader.java:1345) ... 17 more 11:18:16,584 DEBUG JDBCContext:322 - after autocommit org.hibernate.exception.SQLGrammarException: could not load an entity: [test.model.User#3] at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.loadEntity(Loader.java:1359) at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:116) at org.hibernate.loader.entity.EntityLoader.load(EntityLoader.java:101) at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersister.java:2471) at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:351) at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:332) at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:113) at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:167) at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:79) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:621) at org.hibernate.impl.SessionImpl.get(SessionImpl.java:614) at test.FilterMain.runMain2(FilterMain.java:47) at test.FilterMain.main(FilterMain.java:33) 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:324) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:90) Caused by: java.sql.SQLException: Unknown column 'account1_1_.siteId' in 'on clause' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2994) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120) at org.hibernate.loader.Loader.getResultSet(Loader.java:1272) at org.hibernate.loader.Loader.doQuery(Loader.java:391) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218) at org.hibernate.loader.Loader.loadEntity(Loader.java:1345) ... 17 more