Re: [VOTE] JOIN in subselect problems
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
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.