I'm trying to execute the following delete statement with hibernate :

Delete Teilabschn Where teilnetz in (
Select tn From Teilnetz tn
join tn.objektbezFremd as bez
where bez.projekt=?
)

With hsqldb everything works. With oracle i get an

java.sql.SQLException: ORA-00918: Spalte nicht eindeutig definiert

(column not uniquely identified)

The generated sql looks like this :

delete from SYSADM5.TEILABSCHN
where TEILNETZ_ID in (
select teilnetz1_.ID from SYSADM5.TEILNETZ teilnetz1_, SYSADM5.SUPER_OBJEKT teilnetz1_1_, SYSADM5.OBJEKTBEZ objektbezf2_
where ID=objektbezf2_.FREMD_ID and teilnetz1_.ID=teilnetz1_1_.ID and PROJEKT_ID=?);


The problem is the column ID which occurs in every table. To Uniquely identify the ID column of the outer Table (TEILABSCHN), the table must be specified (either by table alias or be prefixing the full table). The following delete would be correct :

delete from SYSADM5.TEILABSCHN ta
where TEILNETZ_ID in (
select teilnetz1_.ID from SYSADM5.TEILNETZ teilnetz1_, SYSADM5.SUPER_OBJEKT teilnetz1_1_, SYSADM5.OBJEKTBEZ objektbezf2_
where ta.ID=objektbezf2_.FREMD_ID and teilnetz1_.ID=teilnetz1_1_.ID and PROJEKT_ID=?);


Hibernate version:
3.1beta2

Mapping documents:
<hibernate-mapping package="de.novasib.ttsib5.hbm.SYSADM5" schema="SYSADM5">
<class name="de.novasib.ttsib5.hbm.SYSADM5.Teilabschn" schema="SYSADM5" table="TEILABSCHN">
<id name="id" type="java.lang.String" column="ID">
<generator class="uuid.hex"/>
</id>
...
<many-to-one name="teilnetz" class="de.novasib.ttsib5.hbm.SYSADM5.Teilnetz" not-null="false">
<column name="TEILNETZ_ID"/>
</many-to-one>
...
</class>

...

<class name="de.novasib.ttsib5.hbm.SYSADM5.SuperObjekt" schema="SYSADM5" table="SUPER_OBJEKT">
<id name="id" type="java.lang.String" column="ID">
<generator class="uuid.hex"/>
</id>
...
<bag batch-size="5" cascade="none" name="objektbezFremd" inverse="true">
<key column="FREMD_ID" not-null="true"/>
<one-to-many class="de.novasib.ttsib5.hbm.SYSADM5.Objektbez"/>
</bag>
...
<joined-subclass name="de.novasib.ttsib5.hbm.SYSADM5.Teilnetz" schema="SYSADM5" table="TEILNETZ">
<key column="ID"/>
...
<bag batch-size="5" cascade="none" name="teilabschn" inverse="true">
<key column="TEILNETZ_ID" not-null="false"/>
<one-to-many class="de.novasib.ttsib5.hbm.SYSADM5.Teilabschn"/>
</bag>
...
</joined-subclass>
</class>

...

<class name="de.novasib.ttsib5.hbm.SYSADM5.Objektbez" schema="SYSADM5" table="OBJEKTBEZ">
<id name="id" type="java.lang.String" column="ID">
<generator class="uuid.hex"/>
</id>
...
<many-to-one name="projekt" class="de.novasib.ttsib5.hbm.SYSADM5.Projekt" not-null="false">
<column name="PROJEKT_ID"/>
</many-to-one>
...
<many-to-one lazy="false" name="fremd" class="de.novasib.ttsib5.hbm.SYSADM5.SuperObjekt" not-null="true">
<column name="FREMD_ID"/>
</many-to-one>
</class>
</hibernate-mapping>

Full stack trace of any exception that occurs:
INFO | jvm 1 | 2005/09/16 11:13:54 | java.sql.SQLException: ORA-00918: Spalte nicht eindeutig definiert
INFO | jvm 1 | 2005/09/16 11:13:54 |
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:182)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:785)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1027)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2885)
INFO | jvm 1 | 2005/09/16 11:13:54 | at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2957)
INFO | jvm 1 | 2005/09/16 11:13:54 | at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
INFO | jvm 1 | 2005/09/16 11:13:54 | at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:71)
INFO | jvm 1 | 2005/09/16 11:13:54 | at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:322)
INFO | jvm 1 | 2005/09/16 11:13:54 | at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1012)
INFO | jvm 1 | 2005/09/16 11:13:54 | at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)


Name and version of the database you are using:
Oracle 9i, Release 2