[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4701) ORA-00920: invalid relational operator using subquery

[Julien HENRY (JIRA)]

ORA-00920: invalid relational operator using subquery
-----------------------------------------------------

                 Key: HHH-4701
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4701
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.5.0-Beta-2
         Environment: Oracle 9i
            Reporter: Julien HENRY


I have the following relations:

A*---B*---C

All my tables are using composite keys.

I want to delete all A in relation to a specific C entity.

My query is: 

delete from A a where a in (select a1 from A a1 left join a1.b b left join b.c c where c.compositeId=:cId)

The resulting Oracle query is something like:

delete from A where (A_1, A_2) in (select (A_1, A_2) from A a1, B b1, C c1 where [...])

which produce the following error:
ORA-00920: invalid relational operator

I tried to fix the SQL query by removing parenthesis:
delete from A where (A_1, A_2) in (select A_1, A_2 from A a1, B b1, C c1 where [...])

which now give the following error:
ORA-00918: column ambiguously defined

And finally I arrived to a correct quesry by adding table prefix:
delete from A where (A_1, A_2) in (select a1.A_1, a1.A_2 from A a1, B b1, C c1 where [...])

I will try to create a test case. Is there any documentation on how to provide test case?

Regards

-- 
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