[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-817) using projections is causing SQL query error on oracle (ORA-00904 error)

[Waldemar Prabucki (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-817?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27231 ] 

Waldemar Prabucki commented on HHH-817:
---------------------------------------

I have simmilar problem with following query (@Hibernate 3.2.4sp1):
"delete SupplierBO libo1 where libo.oid not in (select abbo.supplier.oid from PartBO abbo) and libo.oid not in (select pobo.supplier.oid from PotentialanalyseSourceBO pobo)".

Everything is ok if there is only one condition either 
" libo.oid not in (select pobo.lieferwerk.oid from PotenzialanalyseQuelleBO pobo)" 
or 
"ibo.oid not in (select abbo.lieferwerk.oid from AbrufteilBO abbo)" 
but with both of them i run into ORA-00904: "SUPPLIER0_"."OID" from the second condition. 

I am new in Hibernate and I will be very thankful if anyone could suggets me some efficient and safe workaround to this. 

Is this published patch safe in this case? If yes could it have some negative effects on other hibernate components?


> using projections is causing SQL query error on oracle (ORA-00904 error)
> ------------------------------------------------------------------------
>
>                 Key: HHH-817
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-817
>             Project: Hibernate3
>          Issue Type: Bug
>    Affects Versions: 3.0.5
>         Environment: Oracle 9.2.0.6, Hibernate 3.0.5, Spring Framework 1.2.2 based application working on Jakarta Tomcat 5.0.28
>            Reporter: Michal Jastak
>            Priority: Minor
>         Attachments: HHH-817.patch
>
>
> following java code:
> protected Entity loadEntityLightweight(Serializable entityId) throws DataAccessException {
>     Criteria criteria = getSession().createCriteria(Entity.class);    
>     ProjectionList projectionList = Projections.projectionList();
>     projectionList.add(Property.forName(BaseEntity.PROP_ID), BaseEntity.PROP_ID);
>     projectionList.add(Property.forName(BaseEntity.PROP_TYPE), BaseEntity.PROP_TYPE);
>     criteria.setProjection(projectionList);
>     criteria.add(Restrictions.eq(BaseEntity.PROP_ID, entityId));
>     criteria.setResultTransformer(new AliasToBeanResultTransformer(Entity.class));
>     return (Entity) criteria.uniqueResult();
>   }
> generates following SQL query:
> select this_.id as y0_, this_.type as y1_ from entities this_ left outer join facilities this_1_ on this_.id=this_1_.id left outer join users this_2_ on this_.id=this_2_.id left outer join addresses address2_ on this_.address_id=address2_.id left outer join entities entity3_ on this_2_.employer_id=entity3_.id left outer join facilities entity3_1_ on entity3_.id=entity3_1_.id left outer join users entity3_2_ on entity3_.id=entity3_2_.id where y0_=?
> y0_ = ? expression in where clause is causing a 904 error on Oracle 9:
> ORA-00904: "Y0_": invalid identifier
> hibernate dialect: org.hibernate.dialect.Oracle9Dialect
> mapping for Entity class:
> <?xml version="1.0"?>
> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
>                                    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
> <hibernate-mapping default-lazy="false" default-cascade="save-update">
>  
>   <class name="Entity" table="entities" mutable="true">
>     <id name="id" type="java.lang.Long" unsaved-value="null">
>       <generator class="sequence">
>         <param name="sequence">entities_id_seq</param>
>       </generator>
>     </id>
>     <many-to-one  name="address"   class="Address" column="address_id" />
>     ...
>     <!--
>       -  Facilities
>       -->
>     <joined-subclass name="Facility" table="facilities">
>       <key column="id" />
>       ...
>       <set name="users" inverse="true" lazy="true">
>         <key column="facility_id" />
>         <one-to-many class="User" />
>       </set>
>     </joined-subclass>
>     <!--
>       -  Users
>       -->
>     <joined-subclass name="User" table="users" dynamic-insert="true" dynamic-update="true">
>       <key column="id" />
>       <many-to-one name="employer"   class="Entity"  column="employer_id" cascade="none" />
>       ...
>       <set name="userAuthorities" inverse="true" cascade="all-delete-orphan">
>         <key          column="user_id" />
>         <one-to-many  class="Authority" />
>       </set>
>     </joined-subclass>
>   </class>
> </hibernate-mapping>

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