HQL update statement is translated into invalid SQL if there are multiple "member of"s --------------------------------------------------------------------------------------- Key: HHH-2798 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2798 Project: Hibernate3 Issue Type: Bug Components: query-hql Affects Versions: 3.2.5 Environment: Problem detected in Hibernate 3.2.5 ga as well as in 3.2.0 ga Database DB2 v8.1 on Solaris Reporter: Ludger Springmann Attachments: testcase_hql_update.zip When using an update HQL with subselect containing at least two 'member of' expressions the SQL sent to DB2 is invalid. (An example as JUnit Test is attached to this entry.) The correspondig HQL with select instead of update works fine (see test). The problem is caused by a not qualifyed id in subselect query. Here is the result of the test (first select then update): HQL: from de.iskv.hibernate.test.Person person where person.oid in (select p.oid from de.iskv.hibernate.test.Person p, de.iskv.hibernate.test.Address a, de.iskv.hibernate.test.Blog b where a.city='New York' and b.url='http://mydomain.com/blogs/al' and a member of p.addresses and b member of p.blogs) Resulting SQL: select person0_.OID as OID0_, person0_.NAME as NAME0_, person0_.FIRSTNAME as FIRSTNAME0_ from SPRINGMA.TEST_PERSON person0_ where person0_.OID in ( select person1_.OID from SPRINGMA.TEST_PERSON person1_, SPRINGMA.TEST_PERSON address2_, SPRINGMA.TEST_BLOG blog3_ where address2_.CITY='New York' and blog3_.URL='http://mydomain.com/blogs/al' and ( address2_.OID in ( select addresses4_.OID from SPRINGMA.TEST_PERSON addresses4_ where person1_.OID=addresses4_.PERSON_ID ) ) and ( blog3_.OID in ( select blogs5_.OID from SPRINGMA.TEST_BLOG blogs5_ where person1_.OID=blogs5_.PERSON_ID ) ) ) HQL: update de.iskv.hibernate.test.Person person set name = ' ' where person.oid in (select p.oid from de.iskv.hibernate.test.Person p, de.iskv.hibernate.test.Address a, de.iskv.hibernate.test.Blog b where a.city='New York' and b.url='http://mydomain.com/blogs/al' and a member of p.addresses and b member of p.blogs) Resulting SQL: update SPRINGMA.TEST_PERSON set NAME=' ' where OID in ( select person1_.OID from SPRINGMA.TEST_PERSON person1_, SPRINGMA.TEST_PERSON address2_, SPRINGMA.TEST_BLOG blog3_ where address2_.CITY='New York' and blog3_.URL='http://mydomain.com/blogs/al' and ( OID in ( select addresses4_.OID from SPRINGMA.TEST_PERSON addresses4_ where person1_.OID=addresses4_.PERSON_ID ) ) and ( OID in ( select blogs5_.OID from SPRINGMA.TEST_BLOG blogs5_ where person1_.OID=blogs5_.PERSON_ID ) ) ) E Time: 2,857 There was 1 error: 1) testUpdate(de.iskv.hibernate.test.HibernateSelectUpdateTestCase)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:84) at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) at de.iskv.hibernate.test.HibernateSelectUpdateTestCase.testUpdate(HibernateSelectUpdateTestCase.java:57) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at de.iskv.hibernate.test.HibernateSelectUpdateTestCase.main(HibernateSelectUpdateTestCase.java:104) Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -203, SQLSTATE: 42702, SQLERRMC: OID at com.ibm.db2.jcc.a.rf.e(rf.java:1680) at com.ibm.db2.jcc.a.rf.a(rf.java:1239) at com.ibm.db2.jcc.b.jb.h(jb.java:139) at com.ibm.db2.jcc.b.jb.a(jb.java:43) at com.ibm.db2.jcc.b.w.a(w.java:30) at com.ibm.db2.jcc.b.cc.g(cc.java:160) at com.ibm.db2.jcc.a.rf.n(rf.java:1219) at com.ibm.db2.jcc.a.sf.gb(sf.java:1790) at com.ibm.db2.jcc.a.sf.d(sf.java:2266) at com.ibm.db2.jcc.a.sf.Y(sf.java:540) at com.ibm.db2.jcc.a.sf.executeUpdate(sf.java:523) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75) ... 22 more FAILURES!!! Tests run: 2, Failures: 0, Errors: 1 The DB2 error code means: SQL0203N A reference to column name is ambiguous. (here OID) Explanation: The column name is used in the statement and there is more than one possible column to which it could refer. This could be the result of: - two tables specified in a FROM clause that have columns with the same name - the ORDER BY clause refers to a name that applies to more than one column in the select list - a reference to a column from the subject table in a CREATE TRIGGER statement does not use the correlation name to indicate if it refers to the old or new transition variable. The column name needs further information to establish which of the possible table columns it is. The statement cannot be processed. User Response: Add a qualifier to the column name. The qualifier is the table name or correlation name. A column may need to be renamed in the select list. sqlcode: -203 sqlstate: 42702 Both member of expressions result in a reference two column OID. This is ambiguous. -- 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