[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2609) Delete with multiple nested sub-queries generates invalid SQL (on Postgresql)

[Thomas (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=34777#action_34777 ] 

Thomas commented on HHH-2609:
-----------------------------

As it seems, any usages of JOINED entities in subselects yields incorrect SQL.  This happens in 3.2.x as well as in 3.3.2.  Example:

@Entity
class A
{
   List<Y> y;
}

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
class X
{
   boolean b;
}

@Entity
class Y extends X
{
}

"from A a where false = ALL (select y.b from a.y y)"

select a0_.pk as pk163_0_, ...
from A a0_ inner join Y y1_ on a0_.pk=y1_.A_PK inner join X x1_1_ on y1_.pk=x1_1_.pk
where 0=all (select x3_1_.b from Y y3_ where a0_.pk=y3_.A_PK) // or similar

As you can see, the X table is omitted in the "from" part of the subselect, whereas the "select" part expects it to be there.

> Delete with multiple nested sub-queries generates invalid SQL (on Postgresql)
> -----------------------------------------------------------------------------
>
>                 Key: HHH-2609
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2609
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.2.4
>         Environment: Linux, JDK 1.5.
>            Reporter: Michael Barker
>         Attachments: delete-ejbql-problem.zip
>
>
> When a delete uses multiple nest subqueries the resulting SQL query generated is invalid.  E.g. the EJB-QL:
> DELETE Foo f WHERE f.fooId NOT IN (SELECT bar.fooId FROM Bar bar) AND f.fooId NOT IN (SELECT baz.fooId FROM Baz baz)
> or 
> DELETE Foo WHERE fooId NOT IN (SELECT fooId FROM Bar) AND fooId NOT IN (SELECT fooId FROM Baz)
> Generates the following:
> delete from Foo where (fooId not in  (select bar1_.fooId from Bar bar1_)) and (foo0_.fooId not in  (select baz2_.fooId from Baz baz2_))
> Will fail because the field "foo0_.fooId" should be "fooId", "foo0_" is not declared.
> At test case is attached.

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