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

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 Assignee: Gail Badner 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.