Hi all

I found a problem in java with Hibernate 3 and Oracle 10g related to queries built on multiple N:M relationships, when the corresponding join tables have columns with the same name. It seems that the Criteria class generates SQL code that Oracle rejects with an exception of type "ORA-00918: column ambiguously defined". This is because Hibernate adds on the select clause duplicated and not required columns aliases.

This issue was already signaled on this forum with no replies at all (https://forum.hibernate.org/viewtopic.php?f=25&t=998515&start=0).

If there are no bugs in the Criteria I'd like to know the correct way to define/use it in a way that does not offend Oracle.
Otherwise this must be considered a bug: has anyone any useful information about it?

Instead of posting a long message, trying to describe the problem in a detailed way, I may send a very very simple Eclipse project that demonstrates the issue to anyone interested in (I don't know why but I cannot attach any file from my user in this forum ...) .
In few words:

• a class A has 2 N:M relationships with a class B and a class C
• A, B and C are mapped on Oracle tables with the same names
• the relationships A->B and A->C are mapped on join tables AB and AC having the expected foreign keys: AB[a_id,b_id], AC[a_id,c_id]
• if you use Hibernate Criteria for querying the DB about A objects having restrictions on both B and C sub-objects, Oracle complains that the generated query is ambiguous

The exception and the SQL generated are the following, with the duplicated columns highlighted in bold:


The Hibernate mapping file used is the following:


Any help will be greatly appreciated.

Thanks all

Fabrizio Casali

Hi

can you please post your Criteria ? I tried following and I am not getting any error.

Criteria crit = session.createCriteria(Category.class).createAlias("types","type").createAlias("areas", "area")
.add(Restrictions.like("type.name", "first")).add(Restrictions.like("area.name", "first"));

Generated SQL

select this_.id as id0_2_, this_.name as name0_2_, types4_.categoryId as categoryId, type1_.id as typeId, type1_.id as id3_0_, type1_.name as name3_0_, areas6_.categoryId as categoryId, area2_.id as areaId, area2_.id as id4_1_, area2_.name as name4_1_ from MyCateogry this_, categoryType types4_, MyType type1_, categoryAreas areas6_, MyArea area2_ where this_.id=types4_.categoryId and types4_.typeId=type1_.id and this_.id=areas6_.categoryId and areas6_.areaId=area2_.id and type1_.name like ? and area2_.name like ?


Here is the hbm file.

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

<class name="com.example.association.manytomany.Category"
table="MyCateogry">

<id name="id"><generator class="native" /></id>
<property name="name" />

<set name="types" cascade="all" table="categoryType">
<key column="categoryId" />
<many-to-many unique="true" column="typeId"
class="com.example.association.manytomany.Type" />
</set>

<set name="areas" cascade="all" table="categoryAreas">
<key column="categoryId" />
<many-to-many unique="true" column="areaId"
class="com.example.association.manytomany.Area" />
</set>

</class>

<!-- Type and Area are simple classes hence ommited -->

</hibernate-mapping>

Thank you so much for your reply

Yes, you're right: a simple query does not produce the error. I forgot to specify that the result has to be paginated onto the database.

If you review the query I signaled in the previous post you can see the use of rownum. So, if you add to your criteria the firstResult and maxResult properties you will see the exception.
This is due to the particular method used by Oracle for paging through the results: it encloses the original query into 2 nested queries "select * from ..." where the repeated column aliases go bad:


You also have the duplicated column in your query:


With other DB the exception does not raise: I tried with MySQL and it's OK, because for paging it uses another method


However, for completeness, the following is the incredibly complex code of the main method I used for produce the error :))



Bye

Fabrizio

Hello Fabrix,

Did you find any solution for this problem ? I am having the same error but don't know how to resolve it without changing hibernate version

Regards,
Mitesh Pandey