[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3335) HQL DELETE with sub-select creates invalid SQL

[John Baker (JIRA)]

HQL DELETE with sub-select creates invalid SQL
----------------------------------------------

                 Key: HHH-3335
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3335
             Project: Hibernate3
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.2.6
         Environment: Oracle 10, Java 1.5
            Reporter: John Baker


Hello,

Consider two objects, ISSO and Event, where ISSO extends Event and the id is called eventId (maps to variable id).  Event also has an attribute time.  Now consider this HQL:

delete from ISSO e where e.id in (select ev.id from Event ev where ev.time < 123)

I appreciate this could be written without the subselect but I've simplified a more complex subselect in order to demonstrate the problem.  This generates SQL that results in the following Oracle error:

ORA-00918: column ambiguously defined

The SQL generated is as follows:

insert into HT_ISSO select isso0_.eventId as eventId from ISSO isso0_ inner join Event isso0_1_ on isso0_.eventId=isso0_1_.eventId 
where eventId in (select event1_.eventId from Event event1_ where event1_.time<23)

And the problem is the eventId following where.  This should be using the alias, i.e. the SQL should be:

insert into HT_ISSO select isso0_.eventId as eventId from ISSO isso0_ inner join Event isso0_1_ on isso0_.eventId=isso0_1_.eventId 
where isso0_.eventId in (select event1_.eventId from Event event1_ where event1_.time<23)

That's it!  Can this be fixed?

Thanks.


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