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

[Danilo Ghirardelli (JIRA)]

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

Danilo Ghirardelli commented on HHH-951:
----------------------------------------

Same problem for me, Hibernate 3.6.1 Final on Oracle 10 and 11.

I was struck with this and I found a workaround, if could help anybody. My original query was like:

{code}
Criteria crit = ses.createCriteria(MyClass.class);
// Tons of conditions
crit.setMaxResults(50);
{code}

I changed it in a DetachedCriteria to "help" hibernate, like this:

{code}
DetachedCriteria crit = DetachedCriteria.forClass(MyClass.class);
// Tons of conditions (the same as before)

crit.setProjection(Projections.distinct(Projections.property("id")));

Criteria outerCrit = ses.createCriteria(MyClass.class);
outerCrit.add(Subqueries.propertyIn("id", crit));
outerCrit.setMaxResults(50);
{code}

This trick will get rid of all join conditions from the "external" query, so the "SELECT * FROM query WHERE ROWNUM <= ?" doesn't have the ambiguous column problem anymore.
Obviously an "IN" condition might be really heavy for the db, depending on cases. I hope the query planner is smart enough to understand that the external "IN" has no real meaning and can be simplified internally. For what I've tested, it seems that this is the case on the latest versions of Oracle, but might not be true for all cases. Hope this helps, anyway.

> setMaxResults causes "ORA-00918: column ambiguously defined" exception 
> -----------------------------------------------------------------------
>
>                 Key: HHH-951
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-951
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.0.5, 3.1 beta 2, 3.5.3, 3.6.1
>         Environment: hibernate3.0.5, hibernate3.1b2, Oracle 9
>            Reporter: Karel Sommer
>         Attachments: Hibernate_SQL_HHH-5579.txt, Hibernate_StackTrace_HHH-5579.txt, TestCase_for_Hibernate_Core_Issue_HHH-5579.zip
>
>
> 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