openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marc Prud'hommeaux <mprud...@apache.org>
Subject Re: JOIN in subselect problems
Date Tue, 13 Mar 2007 16:09:23 GMT
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.
>


Mime
View raw message