[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-951) setMaxResults causes "ORA-00918: column ambiguously defined" exception

[David Channon (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-951?page=comments#action_26405 ] 

David Channon commented on HHH-951:
-----------------------------------

We are in the process of upgrading an application from H 2.1.8 to H 3.2.2 GA.
There is a cse where we are seeing 'ORA-00918: column ambiguously defined' for certain queries.
We are only testing against Oracle 8i and Postgres - It works fine with Hibernate 2.1.8 on both databases. It works fine with Hibernate 3.2.2 GA for postgres but not Oracle.  The issue comes up for queries of the form:

Please ignore the quaily of the query!..

select equ, equ.division.divisionDesc, equ.equipSch.equipType, equ.location.locDesc 
from domainmodel.Equipment as equ 
left join fetch equ.siteType 

There is a setMaxResults being used.

It will work correctly if 
1) only the 'equ' alias (equipment object) and no other select items are included  or
2) The 'left join fetch equ.siteType' is removed from the query, eg, No extra joins are included in the query.

All I am doing locally for our fix is removing the redundently selected items from the query. The inexperienced staff have done this in a few places and in each case it has caused this problem under Oracle (8i only tested).

Hope this helps.


> setMaxResults causes "ORA-00918: column ambiguously defined" exception 
> -----------------------------------------------------------------------
>
>          Key: HHH-951
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-951
>      Project: Hibernate3
>         Type: Bug

>   Components: core
>     Versions: 3.0.5, 3.1 beta 2
>  Environment: hibernate3.0.5, hibernate3.1b2, Oracle 9
>     Reporter: Karel Sommer

>
>
> when create criteria with associations, i get this error:
> ORA-00918: column ambiguously defined
> mapping:
>     <class name="User" table="FRAME_USER" dynamic-update="true" dynamic-insert="true">
>         <id name="id" type="long" unsaved-value="null">
>             <column name="ID" not-null="true"/>
>             <generator class="sequence">
>                 <param name="sequence">frame_user_seq</param>
>             </generator>
>         </id>
>         <version type="timestamp" column="stamp" name="timestamp" unsaved-value="null"/>
>         <property name="user_name" type="string" not-null="true"/>
>         <property name="blocked" type="yes_no" not-null="true"/>
>         <property name="access_logon" type="timestamp"/>
>         <property name="denied_logon" type="timestamp"/>
>         <property name="inactivity_time" type="long"/>
>         <property name="session_count" type="long"/>
>         <idbag name="terminalGroups" table="FRAME_USER_TERMINAL" fetch="join" outer-join="true">
>             <collection-id column="ID" type="long">
>                 <generator class="sequence">
>                     <param name="sequence">frame_user_terminal_seq</param>
>                 </generator>
>             </collection-id>
>             <key column="id_user"/>
>             <many-to-many column="id_terminal_groups" class="TerminalGroup" fetch="join" outer-join="true"/>
>         </idbag>
>     </class>
>     <class name="TerminalGroup" table="FRAME_TERMINAL_GROUPS" dynamic-update="true" dynamic-insert="true">
>         <id name="id" type="long" unsaved-value="null">
>             <column name="ID" not-null="true"/>
>             <generator class="sequence">
>                 <param name="sequence">frame_terminal_groups_seq</param>
>             </generator>
>         </id>
>         <version type="timestamp" column="stamp" name="timestamp" unsaved-value="null"/>
>         <property name="name" column="group_name" type="string" not-null="true"/>
>         <idbag name="terminals" table="FRAME_TERMINAL_REL" fetch="join" outer-join="true">
>             <collection-id column="ID" type="long" >
>                 <generator class="sequence">
>                     <param name="sequence">frame_terminal_rel_seq</param>
>                 </generator>
>             </collection-id>
>             <key column="id_term_group"/>
>             <many-to-many column="id_term" class="Terminal" fetch="join" outer-join="true"/>
>         </idbag>
>     </class>
>     <class name="Terminal" table="FRAME_TERMINAL" where="status != 'D'" dynamic-update="true" dynamic-insert="true">
>         <id name="id" type="long" unsaved-value="null">
>             <column name="ID" not-null="true"/>
>             <generator class="sequence">
>                 <param name="sequence">frame_terminal_seq</param>
>             </generator>
>         </id>
>         <version type="timestamp" column="stamp" name="timestamp" unsaved-value="null"/>
>         <property name="status" type="char" not-null="true"/>
>         <property name="mac" type="string" not-null="true"/>
>         <property name="name" column="ident" type="string" not-null="true"/>
>         <property name="description" type="string"/>
> code:
> session.createCriteria(User.class)
> 	.add(Restrictions.like("user_name", "%")
> 	.createCriteria("terminalGroups")
> 	        .add( Restrictions.like("group_name", "%").
> 	.setProjection(Projections.rowCount())
> .uniqueResult();

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