[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2724) Update EJBQL query generating bad SQL

[Jonathan O'Connor (JIRA)]

Update EJBQL query generating bad SQL
-------------------------------------

                 Key: HHH-2724
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2724
             Project: Hibernate3
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.2.4.sp1
         Environment: Hibernate version: 3.2.4SP1 with 3.3.1GA EntityManager and 3.3.0GA Annotations, running on Postgresql 8.1
            Reporter: Jonathan O'Connor
         Attachments: test.jar

The generated SQL (show_sql=true):
update  Person  set  name=? where (id in (select customer1_.id from Customer customer1_ ) )   and person0_.name<>?

My EJBQL query is:
update Person set name = ?1 where id in (select c.id from Customer c) and name <> ?2

The generated SQL refers to an unknown table alias "person0_".

A workaround is to change around the conditions in the where clause. That is:

update Person set name = ?1 where name <> ?2 and id in (select c.id from Customer c)

does not generate bad code. If I add a where clause to the inner select, then the problem also goes away. Finally, correct SQL is generated in the matching select.

Doing a delete instead of update also causes bad code, and the workarounds are also the same:
delete Person where id in (select id from Customer) and name <> 'Y'

The attached jar file has two classes Person and Customer. There is also a persistence.xml in the META-INF directory. ts configured for a local postgres system. You may need to alter this file. I tested the SQL generation using Hibernate Tools plugin for Eclipse.

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