You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "roger.keays" <ro...@ninthavenue.com.au> on 2007/03/14 01:46:43 UTC
Re: [VOTE] JOIN in subselect problems
Hi Marc,
Marc Prud wrote:
>
> The query looks valid: it sounds like a pretty clear-cut bug. Can you
> file a JIRA?
>
I've filed issue 173: https://issues.apache.org/jira/browse/OPENJPA-173
> As for a workaround, is there an inverse from MailingList.subscribers
> (e.g., "Email.mailingList")? If so, then you might be able to re-cast
> the query as:
>
> UPDATE Email e SET e.totalDeliveries = e.totalDeliveries + 1
> WHERE e.email IN (
> SELECT m.email FROM Email m where m.mailingList IS NOT NULL)
>
I have an inverse many-to-many relationship, but unfortunately your
workaround would still require a JOIN for what I need to do (the query I
posted was a simple version of what I actually need).
Thanks for your help,
Roger
> On Mar 13, 2007, at 2:04 AM, roger.keays wrote:
>
>>
>> Is there anything wrong with the following query?
>>
>> UPDATE Email e SET e.totalDeliveries = e.totalDeliveries + 1
>> WHERE e.email IN (
>> SELECT m.email FROM MailingList l JOIN l.subscribers m)
>>
>> OpenJPA (0.9.6) parses this correctly, but can't seems to be
>> missing the
>> JOIN clause or some table names from the generated SQL:
>>
>> org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: syntax
>> error at
>> or near "WHERE" {prepstmnt 27475707 UPDATE FB_EMAILS SET
>> totalDeliveries =
>> (totalDeliveries + ?) WHERE email IN (SELECT DISTINCT t2.email FROM
>> WHERE
>> (t2.email IN (SELECT t3.email FROM FB_EMAILS t3 WHERE t0.id = t1.id
>> AND
>> t1.subscribers_email = t3.email))) [params=(long) 1]} [code=0,
>> state=42601]
>>
>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap
>> (LoggingConnectionDecorator.java:188)
>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800
>> (LoggingConnectionDecorator.java:53)
>>
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator
>> $LoggingConnection$LoggingPreparedStatement.executeUpdate
>> (LoggingConnectionDecorator.java:854)
>>
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(
>> DelegatingPreparedStatement.java:266)
>>
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(
>> DelegatingPreparedStatement.java:266)
>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager
>> $CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1360)
>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation
>> (JDBCStoreQuery.java:491)
>>
>> org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate
>> (JDBCStoreQuery.java:420)
>>
>> org.apache.openjpa.kernel.ExpressionStoreQuery
>> $DataStoreExecutor.executeUpdate(ExpressionStoreQuery.java:685)
>>
>> org.apache.openjpa.datacache.QueryCacheStoreQuery
>> $QueryCacheExecutor.executeUpdate(QueryCacheStoreQuery.java:343)
>> org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1028)
>> org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:793)
>> org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:868)
>> org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:864)
>>
>> org.apache.openjpa.kernel.DelegatingQuery.updateAll
>> (DelegatingQuery.java:560)
>>
>> org.apache.openjpa.persistence.QueryImpl.executeUpdate
>> (QueryImpl.java:304)
>>
>> Thanks,
>>
>> Roger
>> --
>> View this message in context: http://www.nabble.com/JOIN-in-
>> subselect-problems-tf3394694.html#a9450588
>> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>>
>
>
>
--
View this message in context: http://www.nabble.com/JOIN-in-subselect-problems-tf3394694.html#a9466594
Sent from the open-jpa-dev mailing list archive at Nabble.com.
Re: [VOTE] JOIN in subselect problems
Posted by "roger.keays" <ro...@ninthavenue.com.au>.
roger.keays wrote:
>
> Hi Marc,
>
> Marc Prud wrote:
>>
>> The query looks valid: it sounds like a pretty clear-cut bug. Can you
>> file a JIRA?
>>
> I've filed issue 173: https://issues.apache.org/jira/browse/OPENJPA-173
>
>
>> As for a workaround, is there an inverse from MailingList.subscribers
>> (e.g., "Email.mailingList")? If so, then you might be able to re-cast
>> the query as:
>>
>> UPDATE Email e SET e.totalDeliveries = e.totalDeliveries + 1
>> WHERE e.email IN (
>> SELECT m.email FROM Email m where m.mailingList IS NOT NULL)
>>
> Actually I found a similar solution to do what I need and the SQL it
> produces is quite optimal:
>
> UPDATE Email e SET e.totalDeliveries = e.totalDeliveries + 1
> WHERE ?1 MEMBER OF e.subscriptions OR ?2 MEMBER OF e.subscriptions
> OR ...
>
> Thanks again,
>
> Roger
>
>
>
>> On Mar 13, 2007, at 2:04 AM, roger.keays wrote:
>>
>>>
>>> Is there anything wrong with the following query?
>>>
>>> UPDATE Email e SET e.totalDeliveries = e.totalDeliveries + 1
>>> WHERE e.email IN (
>>> SELECT m.email FROM MailingList l JOIN l.subscribers m)
>>>
>>> OpenJPA (0.9.6) parses this correctly, but can't seems to be
>>> missing the
>>> JOIN clause or some table names from the generated SQL:
>>>
>>> org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: syntax
>>> error at
>>> or near "WHERE" {prepstmnt 27475707 UPDATE FB_EMAILS SET
>>> totalDeliveries =
>>> (totalDeliveries + ?) WHERE email IN (SELECT DISTINCT t2.email FROM
>>> WHERE
>>> (t2.email IN (SELECT t3.email FROM FB_EMAILS t3 WHERE t0.id = t1.id
>>> AND
>>> t1.subscribers_email = t3.email))) [params=(long) 1]} [code=0,
>>> state=42601]
>>>
>>>
>>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap
>>> (LoggingConnectionDecorator.java:188)
>>>
>>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800
>>> (LoggingConnectionDecorator.java:53)
>>>
>>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator
>>> $LoggingConnection$LoggingPreparedStatement.executeUpdate
>>> (LoggingConnectionDecorator.java:854)
>>>
>>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(
>>> DelegatingPreparedStatement.java:266)
>>>
>>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(
>>> DelegatingPreparedStatement.java:266)
>>>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager
>>> $CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1360)
>>>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeBulkOperation
>>> (JDBCStoreQuery.java:491)
>>>
>>> org.apache.openjpa.jdbc.kernel.JDBCStoreQuery.executeUpdate
>>> (JDBCStoreQuery.java:420)
>>>
>>> org.apache.openjpa.kernel.ExpressionStoreQuery
>>> $DataStoreExecutor.executeUpdate(ExpressionStoreQuery.java:685)
>>>
>>> org.apache.openjpa.datacache.QueryCacheStoreQuery
>>> $QueryCacheExecutor.executeUpdate(QueryCacheStoreQuery.java:343)
>>> org.apache.openjpa.kernel.QueryImpl.update(QueryImpl.java:1028)
>>> org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:793)
>>> org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:868)
>>> org.apache.openjpa.kernel.QueryImpl.updateAll(QueryImpl.java:864)
>>>
>>> org.apache.openjpa.kernel.DelegatingQuery.updateAll
>>> (DelegatingQuery.java:560)
>>>
>>> org.apache.openjpa.persistence.QueryImpl.executeUpdate
>>> (QueryImpl.java:304)
>>>
>>> Thanks,
>>>
>>> Roger
>>> --
>>> View this message in context: http://www.nabble.com/JOIN-in-
>>> subselect-problems-tf3394694.html#a9450588
>>> Sent from the open-jpa-dev mailing list archive at Nabble.com.
>>>
>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/JOIN-in-subselect-problems-tf3394694.html#a9467152
Sent from the open-jpa-dev mailing list archive at Nabble.com.