[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3651) HQL delete with subquery generates invalid (ambiguous) SQL
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