Was going to file this with Hibernate as a feature suggestion but their jira is broken. I'll have to do something screwy hear (like a big if statement) but ultimately Hibernate needs a query optimizer IMO.
From MySQL:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
"
Currently, you cannot delete from a table and select from the same table in a subquery.
"
However you can do this:
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
Thusly this query:
"delete from Flag f where f.id in (select m.flags.id "+
"from MessageData m where m.deleted = true and m.folder.id = :folder)")
.setParameter("folder", f.getId()).executeUpdate();
works on Hypersonic but makes MySQL angry.
And Hibernate rejects (somewhat rightly):
"delete f from Flag f, MessageData m where f.id = m.flags.id "+
"and m.deleted = true and m.folder.id = :folder")
.setParameter("folder", f.getId()).executeUpdate();
leaving us little choice on how to handle this stuff on mysql but a big fat IF MySQL statement
What would be ideal is:
QueryOptimizerImpl.java which takes imput from Dialect.
In the case of the first syntax and the dialect "supports-delete-alias" == false then the optimizer would reformat the second query
to be:
delete from Flag f where f.id in (select m.flags.id from MessageData m where m.deleted = true and m.folder.id = :folder).
Obviously this is a lot of work for THIS case. The idea is that other general optimizations and sql dialect differences could be based on the Dialect and optmimized with the optimizer. This would be useful for other things such as DB2 not being particularly efficient at optimizing particular kinds of queries.
Anyhow obviously a lot of this is MySQL-sucks-based, but there ought to be some way to support some general optimization and simple syntax substitution/reconstitution. Bug me more and I'll explain how I envision this working if it isn't clear.
-Andy
