[hibernate-issues] [Hibernate-JIRA] Commented: (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:comment-tabpanel&focusedCommentId=35177#action_35177 ] 

Julien HENRY commented on HHH-4701:
-----------------------------------

It seems HSQLDB 2.0 will support multi-column (A,B,C) IN ((,,), (,,), ) both with literals and queries
(according to http://hsqldb.org/web/features190.html)

So I will try to update IT tests to use latest HSQLDB in order to have a working test case.

> 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