openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "roger.keays" <roger.ke...@ninthavenue.com.au>
Subject Re: [VOTE] JOIN in subselect problems
Date Wed, 14 Mar 2007 01:45:27 GMT



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.


Mime
View raw message