[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

[Jason Clawson (JIRA)]

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

Jason Clawson commented on HHH-5020:
------------------------------------

It has nothing to do with using a transformer although using one makes this situation likely.  You just need to be doing some kind of projection that creates an alias using the same name as the column name.  For some reason this causes hibernate to refer to the column in the WHERE clause using the alias instead of the column.  This only occurs when the alias is named the same as the column.  This generated SQL is technically incorrect for a number of RDBMS... at least for MySQL and Oracle.

Generated SQL would look something like this:

select this_.USERNAME as y0_, this_.ID as y1_ from USER this_ where y0_ like ?

where it should be:

select this_.USERNAME as y0_, this_.ID as y1_ from USER this_ where this_.USERNAME like ?

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