[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5719) Applying projection to criteria with restricted subcriterias generates wrong SQL

[Ben Boggess (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5719?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=41836#action_41836 ] 

Ben Boggess commented on HHH-5719:
----------------------------------

This exists in 3.3.1 as well.

> Applying projection to criteria with restricted subcriterias generates wrong SQL
> --------------------------------------------------------------------------------
>
>                 Key: HHH-5719
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5719
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-criteria
>    Affects Versions: 3.2.0.ga
>         Environment: Hibernate 3.2.0 GA
>            Reporter: Victor Cherkassky
>            Priority: Minor
>
> Detailed issue description and solution is here http://facingtech.blogspot.com/2010_09_01_archive.html
> I will describe only the technical aspect of the issue here.
> Considuer the following model.
> There is an entity {{User}}, which is in relation with {{Workplace}}. While {{Workplace}} has an {{Activity}} (the purpose of this workplace). Each {{Activity}} has a collection of {{Description}} entities ({{Description}} entities are just the same {{Activity}} description translated into several languages, thus having locale property).
> Result of the query should look like this:
> ||User.name||Workplace.name||Activity.code||Description.text||
> |John|Main office|R|Research|
> |Polly|Main office|R|Research|
> |Craig|Main office|R|Research|
> Here is the criteria for getting that result:
> {code}
> Criteria criteria = session.createCriteria(User.class);
> criteria.createAlias("workplace.activity.descriptions", "descriptionsAlias", Criteria.INNER_JOIN);
> criteria.add(Restrictions.eq("descriptionsAlias.locale", "en_US"));
> criteria.list();
> {code}
> It works just fine. Produces the table shown above and generates SQL like this: 
> {code}
>     select
>         this_.id as id18_3_,
>         this_.name as name18_3_,
>         this_.workplaceId as workplac3_18_3_,
>         workplace3_.id as id111_0_,
>         workplace3_.activityId as activityId111_0_,
>         workplace3_.name as name111_0_,
>         activity4_.id as id114_1_,
>         activity4_.code as code114_1_,
>         descriptio1_.id as id24_2_,
>         descriptio1_.locale as locale24_2_,
>         descriptio1_.text as text24_2_
>     from
>         user this_
>     left outer join
>         workplace workplace3_
>             on this_.workplaceId=workplace3_.id
>     left outer join
>         activity activity4_
>             on workplace3_.activityId=activity4_.id
>     inner join
>         description descriptio1_
>             on activity4_.id=descriptio1_.activityId
>     where
>         descriptio1_.locale=?
> {code}
> While if you want paging, you should make a rowcount projection first, so your query will look like this:
> {code}
> Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(User.class);}}
> criteria.createAlias("workplace.activity.descriptions", "descriptionsAlias", criteria.INNER_JOIN);
> criteria.add(Restrictions.eq("descriptionsAlias.locale", "en_US"));
> criteria.setProjection(Projections.rowCount());
> Integer rowCount = (Integer) criteria.uniqueResult();
> {code}
> And you expect Hibernate to produce this SQL:
> {code}
>     select
>         count(*) as y0_
>     from
>         user this_
>     left outer join
>         workplace workplace3_
>             on this_.workplaceId=workplace3_.id
>     left outer join
>         activity activity4_
>             on workplace3_.activityId=activity4_.id
>     inner join
>         description descriptio1_
>             on activity4_.id=descriptio1_.activityId
>     where
>         descriptio1_.locale=?
> {code}
> But the SQL produced is this:
> {code}
>     select
>         count(*) as y0_ 
>     from
>         user this_ 
>     where
>         descriptio1_.locale=?
> {code}
> I suppose that this is a bug, because you expect one behavior, while you get another. I don't think, that this is a big deal, but when you experience such an issue, it is time consuming to get it fixed.
> Here is a workaround (or fix) of the issue. You should always assign aliases to all associated "links" of the "entities chain" of the path to an entity you want to restrict if you use projections. In other words, if you have anything in where clause and you are using projections, you should assign aliases to all associations between root entity and the entity, fields of which are used in where clause.
> This criteria
> {code}
> Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(User.class);
> criteria.createAlias("workplace", "workplaceAlias");
> criteria.createAlias("workplaceAlias.activity", "activityAlias");
> criteria.createAlias("activityAlias.descriptions", "descriptionsAlias", Criteria.INNER_JOIN);
> criteria.add(Restrictions.eq("descriptionsAlias.locale", "en_US"));
> criteria.setProjection(Projections.rowCount());
> Integer rowCount = (Integer) criteria.uniqueResult();
> {code}
> Produces the right result
> {code}
>     select
>         count(*) as y0_ 
>     from
>         user this_ 
>     inner join
>         workplace workplacea1_ 
>             on this_.workplaceId=workplacea1_.id 
>     inner join
>         activity activityal2_ 
>             on workplacea1_.activityId=activityal2_.id 
>     inner join
>         description descriptio3_ 
>             on activityal2_.id=descriptio3_.activityId 
>     where
>         descriptio3_.locale=?
> {code}

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