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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira