Hello, I made a small test case to demonstrate what I think is a bug in bulk delete. Before I file a bug in Jira, could someone please verify if I'm doing something wrong or if it is really a bug?
Hibernate version: 3.1
Mapping documents:
Code:
<hibernate-mapping package='com.csg.pmnet.se.model' default-access="field" default-cascade="all">
<class name='T1' table='se_t1'>
<id name='id'/>
<set name='t2s' inverse="true">
<key column='t1'/>
<one-to-many class='T2'/>
</set>
</class>
<class name='T2' table='se_t2'>
<id name='id'/>
<many-to-one name='t1' class='T1'/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():Code:
s.createQuery("from T1 t1 where size(t1.t2s)=0").list();
s.createQuery("delete from T1 t1 where size(t1.t2s)=0").executeUpdate();
Name and version of the database you are using:Oracle 9.2
The generated SQL (show_sql=true):Code:
select t1x0_.id as id0_ from pms.se_t1 t1x0_ where (select count(t2s1_.t1) from pms.se_t2 t2s1_ where t1x0_.id=t2s1_.t1)=0
delete from pms.se_t1 where (select count(t2s1_.t1) from pms.se_t2 t2s1_ where id=t2s1_.t1)=0
Note the difference between the select and the delete statement: the select statement has a qualified id column in the subquery (.. where t1x0_.id=....), the delete does not. The select statement does the right thing.
In my case, since both tables have a column named id, the delete statement is wrong in that it takes the id column from the se_t2 table instead of the se_t1 table, obviously with a wrong result (it tries to delete ALL records which is prevented by a foreign key constraint).