[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3546) invalid sql when using projection and restriction

[Martin Trummer (JIRA)]

 invalid sql when using projection and restriction
--------------------------------------------------

                 Key: HHH-3546
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3546
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.2.6
         Environment: MS SQL Server/HSQLDB
            Reporter: Martin Trummer


from this forum "discussion" (in fact it's  a monologue): http://forum.hibernate.org/viewtopic.php?p=2398068#2398068

* the criteria

Criteria criteria = getActorDao().createCriteria();
criteria.add(Restrictions.or(
      Restrictions.ilike("name", "adi"),
      Restrictions.ilike("description", "adi")
      ));
criteria.setProjection(Projections.projectionList()
      .add(Projections.groupProperty("name"), "name")   
      .add(Projections.groupProperty("description"), "description")   
      );
criteria.setResultTransformer(new AliasToBeanResultTransformer(ActorTestDTO.class));
List result = criteria.list();


* the generated SQL (show_sql=true):

select this_.name as y0_, this_.description as y1_
from Actor this_
left outer join Action this_1_ on this_.id=this_1_.id
left outer join Location this_2_ on this_.id=this_2_.id
left outer join UserGroup this_3_ on this_.id=this_3_.id
left outer join WebUser this_4_ on this_.id=this_4_.id
where (lower(y0_) like ? or lower(y1_) like ?)
group by this_.name, this_.description


* the Problem
This generated SQL works on HSQLDB but NOT on MS-SqlServer.
The wrong code:
where (lower(y0_) like ? or lower(y1_) like ?)
 
The correct code:
where (lower(this_.name) like ? or lower(this_.description) like ?)
 
I verified this by issuing the sql directly in SQL-server. 

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