I'm trying to get Apache Roller working with JPA and I've had some success (thanks to Sun's Craig Russell and Mitesh Meswani) -- I was able to get 100% of the Roller unit tests working with OpenJPA and Toplink/JPA and running against Derby and MySQL.
However, I'd like to be able to use sub-selects and thus far that's been impossible with OpenJPA. Problem is, if I turn on sub-select support via SupportsSubselect=true, OpenJPA starts using sub-selects all over the place -- in places where I didn't code a sub-select -- and I start getting exceptions like this: <0|false|0.9.6-incubating> org.apache.openjpa.persistence.PersistenceException: You can't specify target table 'roller_weblogentrytagagg' for upd ate in FROM clause {prepstmnt 9396085 DELETE FROM roller_weblogentrytagagg WHERE id IN (SELECT DISTINCT t0.id FROM roller_weblogentrytagagg t0 WH ERE (t0.websiteid = ?)) [params=(String) befd0c00-757c-4aef-b926-008d7d0696cb]} [code=1093, state=HY000] FailedObject: [EMAIL PROTECTED] at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3720) NestedThrowables: java.sql.SQLException: You can't specify target table 'roller_tasklock' for update in FROM clause at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695) at com.mysql.jdbc.Connection.execSQL(Connection.java:3026) Apparently referencing the update target table in a from-clause sub-select is not permitted by MySQL. I'm not sure why OpenJPA feels the need to use sub-selects in updates; it's unnecessary and problematic. So my question is this: is there some way to keep OpenJPA from using sub-selects in update queries? - Dave