java.sql.SQLException: ORA-00904: "Y6_": invalid identifier when performing search by criteria with columns aliases defined and with restriction defined

Description

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

Environment

Oracle 10g

Activity

Show:
Mark A. Ziesemer
August 23, 2010, 10:25 PM

I don't want to call this a duplicate (yet), but it sure seems related (and has been open since 2005, which answers one of my previous confusions)...

Mark A. Ziesemer
August 23, 2010, 10:26 PM

(referring to HHH-817)

Matt Schmidt
August 24, 2010, 10:50 PM

This is still an issue in the current release (3.5.5) but I have found a workaround that i thought should be mentioned in the issue. Add a "this." identifier to the property of the restriction and you no longer get the invalid identifier error. Example given below:

DetachedCriteria criteria = DetachedCriteria.forClass(Entity.class);
criteria.setProjection(Projections.groupProperty("propertyA"),"propertyA");
criteria.add(Restrictions.like("this.propertyA", "%")); <------- workaround
criteria.setResultTransformer(Transformers.aliasToBean(Entity.class));

Gail Badner
August 31, 2010, 12:44 AM

This issue duplicates HHH-817. Mark, thanks for pointing that out.

Steve Ebersole
March 21, 2011, 7:09 PM

Bulk closing stale resolved issues

Duplicate

Assignee

Gail Badner

Reporter

Alexander Lobosov

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major