[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2610) Invalid SQL generation for dynamic filter
Ariful Islam (JIRA)
noreply at atlassian.com
Tue Jun 19 10:40:52 EDT 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27282 ]
Ariful Islam commented on HHH-2610:
-----------------------------------
I'm also having same kind of problem on MySQL 5.0.27, Hibernate 3.2.4.
This is due to some change in MySQL query parsing strategy according to the SQL:2003
More on this topic can be found here:
http://bugs.mysql.com/bug.php?id=13551
> 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
--
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