[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4023) Fix for an issue where alias references are incorrectly replaced by the table name in an INSERT

[John Trimble (JIRA)]

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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira