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

[Gail Badner (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3114?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29673 ] 

Gail Badner commented on HHH-3114:
----------------------------------

See HHH-3154 for an explanation of what goes wrong.

> 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
>         Attachments: test-hibernate-inner.zip
>
>
> 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