[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3651) HQL delete with subquery generates invalid (ambiguous) SQL

[Robin Houston (JIRA)]

HQL delete with subquery generates invalid (ambiguous) SQL
----------------------------------------------------------

                 Key: HHH-3651
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3651
             Project: Hibernate Core
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.3.1
         Environment: Hibernate 3.3.1GA, PostgreSQL database
            Reporter: Robin Houston
         Attachments: Edge.java, Node.java, schema.sql

A simple test case demonstrating this bug is attached. The HQL query

delete Node n where n in (
    select grandchild
    from Edge grandparent_child
       , Edge child_grandchild
         left join child_grandchild.target as grandchild
    where grandparent_child.target = child_grandchild.source
    and grandparent_child.source.name = :nodeName
)

generates the following SQL (formatted for readability):

delete from node where id in (
    select id
    from edge edge1_
       , edge edge2_
         left outer join node node3_ on edge2_.target_id = node3_.id
       , node node4_
    where edge1_.source_id=node4_.id
    and edge1_.target_id = edge2_.source_id
    and node4_.name = ?
)

Note that the 'select id' is ambiguous; it could refer either to node3_.id or node4_.id. (It should be node3_, of course.) This ambiguity causes the PostgreSQL parser to throw an exception.

I realise that the problem could be avoided in this particular example by removing the join and directly selecting child_grandchild.target. However, this is just a simple test case derived from a much more complicated example where such a workaround doesn't seem to be possible. (We are currently circumventing the problem by using an SQL delete statement.)

I attach the mapped classes and the schema definition for this test case.

Robin

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