[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3114) Subqueries in Criteria-API use wrong table alias

[Gerald Glocker (JIRA)]

Subqueries in Criteria-API use wrong table alias
------------------------------------------------

                 Key: HHH-3114
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3114
             Project: Hibernate3
          Issue Type: Bug
          Components: query-criteria
    Affects Versions: 3.2.6
            Reporter: Gerald Glocker
            Priority: Blocker


Subqueries (exists, notExists, and probably others) are formulated using table alias "this_" but the outer query also uses the same table alias.
This is a serious mistake for subqueries working on the same table or for subqueries on tables with the same column names.


Example:

      DetachedCriteria c = DetachedCriteria.forClass(Person.class, "a");
      c.add(Restrictions.eq("lastname", "Smith"));

      DetachedCriteria sub = DetachedCriteria.forClass(Person.class);
      sub.setProjection(Projections.property("id"));
      sub.add(Restrictions.eqProperty("lastname", "a.lastname"));
      sub.add(Restrictions.gtProperty("dob", "a.dob"));

      c.add(Subqueries.exists(sub));

      List persons = ht.findByCriteria(c);


Produces SQL:


   select this_.id        as id4_0_,
          this_.firstname as firstname4_0_,
          this_.lastname  as lastname4_0_,
          this_.dob       as dob4_0_
     from Person this_
    where this_.lastname = ?
      and exists (select this_.id as y0_
             from Person this_
            where this_.lastname = this_.lastname
              and this_.dob > this_.dob)


The error can cleary be seen on the where-condition "this_.dob > this_.dob".

Please try to fix this problem soon. A running test could be provided.

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