[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2798) HQL update statement is translated into invalid SQL if there are multiple "member of"s
Ludger Springmann (JIRA)
noreply at atlassian.com
Mon Aug 20 07:59:13 EDT 2007
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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list