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/13 10:04:58 UTC

JOIN in subselect problems

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.

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.

Re: [VOTE] JOIN in subselect problems

Posted by "roger.keays" <ro...@ninthavenue.com.au>.
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: JOIN in subselect problems

Posted by Marc Prud'hommeaux <mp...@apache.org>.
Roger-

The query looks valid: it sounds like a pretty clear-cut bug. Can you  
file a JIRA?

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)



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.
>

Re: JOIN in subselect problems

Posted by Catalina Wei <ca...@gmail.com>.
Roger,
There is a similar problem already reported under bug OPENJPA-51.

Catalina

On 3/13/07, roger.keays <ro...@ninthavenue.com.au> 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.
>
>