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

[Julien HENRY (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4701?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julien HENRY updated HHH-4701:
------------------------------

    Attachment: HHH-4701.patch

Here is a patch with a TestCase that reproduce the issue and a fix for Oracle.

Once the patch is applied, everything works fine on Oracle. But the test case still produce an error, because it seems HSQL doesn't support use of tuple in a IN subquery.

> 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
>         Attachments: HHH-4701.patch
>
>
> 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