[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

[Mark A. Ziesemer (JIRA)]

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

Mark A. Ziesemer commented on HHH-5020:
---------------------------------------

https://jira.jboss.org/browse/HIBERNATE-106 looks like it may be related to this.

I'm a little confused / concerned, as I'm still evaluating using Hibernate with a new project against Oracle 10g/11g - especially as others have mentioned running into this as early as 3.2.5 (from the affects versions field), 3.3.1 (from the linked JBoss ticket), or 3.3.2 (Shawn's comment here).  Is Oracle not a preferred / regularly tested database server for Hibernate, that this wasn't even been reported earlier than May?

This seems like a fairly common use case that would be difficult if not impossible to avoid.  Apparently it doesn't even require a projection - just a restriction.  Are there any easily workarounds here that I'm not seeing?  Or do we need to consider waiting for 3.5.6, or possibly reverting to a much older version - possibly prior to 3.2?

> 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.6.0.Beta4, 3.5.6
>
>         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