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

[Shawn Clowater (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37232#action_37232 ] 

Shawn Clowater commented on HHH-5020:
-------------------------------------

Gail, this looks related to HHH-5063 but it is still an issue under 3.5.2.

It looks to be very similar since if I apply my workaround that I did for 5063 initially then the sql is correctly generated.  Note that the generated query works fine with the standard test db when running the new test but the sql is invalid for at least Oracle.

> 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
>         Environment: Oracle 10g
>            Reporter: Alexander
>         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

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