[hibernate-issues] [Hibernate-JIRA] Updated: (HHH-5020) java.sql.SQLException: ORA-00904: "Y6_": invalid identifier when performing search by criteria with columns aliases defined and with restriction defined

java.sql.SQLException: ORA-00904: "Y6_": invalid identifier when performing search by criteria with columns aliases defined and with restriction defined -------------------------------------------------------------------------------------------------------------------------------------------------------- Key: HHH-5020 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5020 Project: Hibernate Core Issue Type: Bug Components: query-criteria Affects Versions: 3.2.5, 3.5.2 Environment: Oracle 10g Reporter: Alexander Assignee: Gail Badner Fix For: 3.5.x Attachments: HHH-5020-TestCase.patch, stacktrace.JPG The exception is occured when I performs search by criteria like this DetachedCriteria headerCrit = DetachedCriteria.forClass(LOSHeader.class); ProjectionList prjList = Projections.projectionList(); prjList.add(Projections.groupProperty("channelId"),"channelId"); prjList.add(Projections.groupProperty("channel"),"channel"); prjList.add(Projections.groupProperty("subCategoryId"),"subCategoryId"); prjList.add(Projections.groupProperty("subCategory"),"subCategory"); prjList.add(Projections.groupProperty("bevCategoryId"),"bevCategoryId"); prjList.add(Projections.groupProperty("bevCategory"),"bevCategory"); prjList.add(Projections.groupProperty("accessFilter"),"accessFilter"); headerCrit.setProjection(prjList); headerCrit.add(Restrictions.like("accessFilter","%,INTERNAL;%")); headerCrit.setResultTransformer(Transformers.aliasToBean(LOSHeader.class)); List result = pipelineDAO.loadByCriteria(headerCrit, LOSHeader.class); Criteria string: DetachableCriteria(CriteriaImpl(com.ko.cokechannel.los.impl.entity.LOSHeader:this[][accessFilter like %,INTERNAL;%][channelId as channelId, channel as channel, subCategoryId as subCategoryId, subCategory as subCategory, bevCategoryId as bevCategoryId, bevCategory as bevCategory, accessFilter as accessFilter])) The is list of projections: [channelId as channelId, channel as channel, subCategoryId as subCategoryId, subCategory as subCategory, bevCategoryId as bevCategoryId, bevCategory as bevCategory, accessFilter as accessFilter] Method execution stack trace (also the screenshot of stack trace is attached) : CriteriaQueryTranslator.getWhereCondition()-> String sqlString = entry.getCriterion().toSqlString( entry.getCriteria(), this ); SimpleExpression.toSqlString() -> CriteriaQueryTranslator.getColumnsUsingProjection(criteria, propertyName); // propertyName is "accessFilter" ProjectionList.getColumnAliases(String alias, int loc){ // alias = "accessFilter" .... String[] result = getProjection(i).getColumnAliases(alias, loc); if (result!=null) return result; ..... result is: [y6_] // Restriction for accessFilter property is mathced with the value from projection aliases, so [y6_] is returned for the WHERE condition WHERE clause sql: y6_ like ? The final sql looks like: select this_.CHANNEL_ID as y0_, this_.CHANNEL_NM as y1_, this_.SUB_CATEGORY_ID as y2_, this_.SUB_CATEGORY_NM as y3_, this_.BEV_CATEGORY_ID as y4_, this_.BEV_CATEGORY_NM as y5_, this_.ACCESS_FILTER as y6_ from V_LOS_LIST this_ WHERE y6_ like ? <<<<<<< ---- THIS IS WRONG - group by this_.CHANNEL_ID, this_.CHANNEL_NM, this_.SUB_CATEGORY_ID, this_.SUB_CATEGORY_NM, this_.BEV_CATEGORY_ID, this_.BEV_CATEGORY_NM, this_.ACCESS_FILTER I think that the cause of this issue is the same as was mentioned in http://opensource.atlassian.com/projects/hibernate/browse/HHH-2847 To easy reproduce this issue just try to search by a criteria like this: DetachedCriteria criteria = DetachedCriteria.forClass(Entity.class); criteria.setProjection(Projections.groupProperty("propertyA"),"propertyA"); criteria.add(Restrictions.like("propertyA", "%")); criteria.setResultTransformer(Transformers.aliasToBean(Entity.class)); Thank you, Alex