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

Attachments

2
  • 20 May 2010, 10:47 PM
  • 18 Mar 2010, 12:02 PM

Activity

Show:

Steve EbersoleMarch 21, 2011 at 7:09 PM

Bulk closing stale resolved issues

Former userAugust 31, 2010 at 12:44 AM

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

Matt SchmidtAugust 24, 2010 at 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));

Mark A. ZiesemerAugust 23, 2010 at 10:26 PM

(referring to HHH-817)

Mark A. ZiesemerAugust 23, 2010 at 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)...

Duplicate

Details

Assignee

Reporter

Components

Affects versions

Priority

Created March 18, 2010 at 12:02 PM
Updated March 21, 2011 at 7:09 PM
Resolved August 31, 2010 at 12:44 AM