Bulk delete generates invalid MySQL syntax?

rkennard · **Joined:** Sun Jul 31, 2005 7:53 pm **Posts:** 17

Dear All,

I am not sure whether this is a Hibernate bug, a EJB3 bug or a MySQL bug, so I thought I'd run it past you before opening a JIRA :)

I have found that the Hibernate EJB-QL query...

Code:

delete from AssetOwned a where a.member.company.id = :id0

...generates the following SQL (using the MySQL dialect)...

Code:

delete from AssetOwned, Member member1_ where company_id=?

...which gives...

Code:

   java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where company_id='5c6f394a0a2abaaa010a2ac56e9e0001'' at line 1

Yet it is quite possible to do this if you instead use the SQL...

Code:

   delete from AssetOwned where member_id in (select member_id from Member where company_id = '5c6f394a0a2abaaa010a2ac56e9e0001')

...instead.

Is this a bug? Or am I expecting too much?

Regards,

Richard.

emmanuel · **Posted:** Sat Mar 25, 2006 9:55 am

which Hibernate version?

rkennard · **Joined:** Sun Jul 31, 2005 7:53 pm **Posts:** 17

Sorry - 3.1.2, as ships with JBoss 4.0.4RC1