You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Dave <sn...@gmail.com> on 2007/03/12 17:18:36 UTC
How to turn off sub-selects in updates?
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: org.apache.openjpa.jdbc.kernel.exps.ExpContext@c617e0
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
Re: How to turn off sub-selects in updates?
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Dave-
> I'm using 5.0.27-standard, so perhaps that bug is not the problem --
> or it wasn't fixed.
If it's not working for MySQL 5, can you file an OpenJPA JIRA report
for this at https://issues.apache.org/jira/browse/OPENJPA ? We should
probably try to figure out some way to work around the limitation.
> 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.
If you set the DBDictionary parameter to SupportsSubselect=false,
then we won't generate the subselect (although we'll probably just
wind up having to perform the operation in-memory, which is usually
slower). Unfortunately, we'll probably also throw an exception when
you try to actually execute a subselect against the database.
On Mar 17, 2007, at 6:19 AM, Dave wrote:
> On 3/16/07, Marc Prud'hommeaux <mp...@apache.org> 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?
Posted by Dave <sn...@gmail.com>.
On 3/16/07, Marc Prud'hommeaux <mp...@apache.org> 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?
Posted by Marc Prud'hommeaux <mp...@apache.org>.
Dave-
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.
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.
I can't think of any way to work around this short of manually
deleting the record instead of performing the bulk delete operation
(e.g., doing em.remove(em.find(WeblogEntryTagAggregateData.class, 1))).
On Mar 13, 2007, at 7:42 PM, Dave wrote:
> On 3/12/07, Dave <sn...@gmail.com> wrote:
>> On 3/12/07, Patrick Linskey <pl...@bea.com> 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?
Posted by Dave <sn...@gmail.com>.
On 3/12/07, Dave <sn...@gmail.com> wrote:
> On 3/12/07, Patrick Linskey <pl...@bea.com> 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?
Posted by Dave <sn...@gmail.com>.
On 3/12/07, Patrick Linskey <pl...@bea.com> 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
RE: How to turn off sub-selects in updates?
Posted by Patrick Linskey <pl...@bea.com>.
Hi,
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.
-Patrick
--
Patrick Linskey
BEA Systems, Inc.
_______________________________________________________________________
Notice: This email message, together with any attachments, may contain
information of BEA Systems, Inc., its subsidiaries and affiliated
entities, that may be confidential, proprietary, copyrighted and/or
legally privileged, and is intended solely for the use of the individual
or entity named in this message. If you are not the intended recipient,
and have received this message in error, please immediately return this
by email and then delete it.
> -----Original Message-----
> From: Dave [mailto:snoopdave@gmail.com]
> Sent: Monday, March 12, 2007 9:19 AM
> To: open-jpa-dev@incubator.apache.org
> Subject: How to turn off sub-selects in updates?
>
> 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: org.apache.openjpa.jdbc.kernel.exps.ExpContext@c617e0
> at
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBD
> ictionary.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
>