[Hibernate-JIRA] Commented: (HHH-951) setMaxResults causes "ORA-00918: column ambiguously defined" exception
12:39 p.m.
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-951?page=co...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5023
days inactive
5023
days old
hibernate-issues@lists.jboss.org
0 comments
1 participants
participants (1)
-
Danilo Ghirardelli (JIRA)