Re: How to turn off sub-selects in updates?

[Dave]

On 3/16/07, Marc Prud'hommeaux <[EMAIL PROTECTED]> wrote:

This does look like a bug in MySQL (see http://bugs.mysql.com/bug.php?
id=5037 ). Supposedly this is fixed in as of version 5.0.20.


Thanks for looking into this.

I'm using 5.0.27-standard, so perhaps that bug is not the problem --
or it wasn't fixed.



In general, we use subselects for bulk operations because delete and
update SQL statements do not allow the specification of multiple
tables, so only the simplest bulk operations would be possible
without the ability to use subselects.


Is it possible to turn off OpenJPA generated sub-selects and only do a
sub-select when an application query uses one? That's the default
behavior in Toplink/JPA.

- Dave

Re: How to turn off sub-selects in updates?

[Dave]

On 3/12/07, Dave <[EMAIL PROTECTED]> wrote:

On 3/12/07, Patrick Linskey <[EMAIL PROTECTED]> wrote:
> What is the JPQL query that you're issuing that is resulting in that SQL
> statement? One easy way to get this information is by setting the
> 'Query' log channel to TRACE.

This is the query that caused the above trace.

   DELETE FROM WeblogEntryTagAggregateData w WHERE w.weblog = ?1


Ping!

Any idea what might be going wrong with sub-select support in MySQL?

I hope I'm not the first person to try this ;-)

- Dave

Re: How to turn off sub-selects in updates?

[Dave]

On 3/12/07, Patrick Linskey <[EMAIL PROTECTED]> wrote:

What is the JPQL query that you're issuing that is resulting in that SQL
statement? One easy way to get this information is by setting the
'Query' log channel to TRACE.


This is the query that caused the above trace.

  DELETE FROM WeblogEntryTagAggregateData w WHERE w.weblog = ?1

- Dave

How to turn off sub-selects in updates?

[Dave]

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