Fix for an issue where alias references are incorrectly replaced by the table name in an INSERT ----------------------------------------------------------------------------------------------- Key: HHH-4023 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4023 Project: Hibernate Core Issue Type: Patch Components: query-hql Affects Versions: 3.3.2, 3.2.7 Environment: Hibernate versions 3.2.7 and 3.3.2 on MySQL and HSQLDB. Reporter: John Trimble Attachments: branch_3_2_r17009_insert_subquery_alias_fix.patch, branch_3_3_r17009_insert_subquery_alias_fix.patch In an INSERT, aliases defined in the top most FROM clause incorrectly have their references in subqueries replaced by the table name. For example, a query such as: HQL: insert into Foo (id, someInt) select bar.id as id, bar.someInt as someInt from Bar bar where exists ( select innerbar from Bar innerbar where innerbar.id=bar.id ) becomes, HSQLDB SQL: insert into Foo ( id, someInt ) select aliastable0_.id as col_0_0_, aliastable0_.someInt as col_1_0_ from Bar aliastable0_ where exists (select id from Bar aliastable1_ where aliastable1_.id=Bar.id) Note the use of 'Bar.id' instead of 'aliastable0_.id' resulting in the following exception: org.hibernate.exception.SQLGrammarException: could not execute update query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:87) at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:398) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1142) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) ... Caused by: java.sql.SQLException: Column not found: BAR.ID in statement [insert into Foo ( id, someInt ) select aliastable0_.id as col_0_0_, aliastable0_.someInt as col_1_0_ from Bar aliastable0_ where exists (select id from Bar aliastable1_ where aliastable1_.id=Bar.id)] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:528) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:95) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:88) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:65) ... 27 more This substitution is not always inappropriate, for example DELETE requires the replacement: HQL: delete from Bar bar where exists ( select innerbar from Bar innerbar where bar.id=innerbar.id ) HSQLDB SQL: delete from Bar where exists ( select id from Bar insertinto1_ where Bar.id=insertinto1_.id) However, I cannot think of a case where this behavior would be necessary for an INSERT, but perhaps there is one I haven't considered. Judging from the logs, it looks as though this may have worked at one point but broke due to a fix for http://opensource.atlassian.com/projects/hibernate/browse/HHH-1419. I have attached a patch for this issue, which includes an added test for it. The fix causes aliases in an INSERT to be handled in the same manner as for a SELECT. Tested on HSQLDB and MySQL with all tests passing. The issue exists in both the http://anonsvn.jboss.org/repos/hibernate/core/branches/Branch_3_2/ and http://anonsvn.jboss.org/repos/hibernate/core/branches/Branch_3_3/ branches (both of which I've included patches for), and may exist in the http://anonsvn.jboss.org/repos/hibernate/core/trunk/ as well, but I couldn't get it to build for me. Let me know if there are any issues with the patch--especially with regard to unintended side effects--and I'll gladly look into resolving them. -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira