[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

[Gail Badner (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5020?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Gail Badner updated HHH-5020:
-----------------------------

    Fix Version/s:     (was: 3.5.4)
                   3.5.x

> 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

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