*** Exception ***
javax.jdo.JDOException: Exception thrown when executing query
at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:440)
at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:275)
at org.datanucleus.test.ModeOfPaymentFlavour.getAvailableModeOfPaymentFlavoursMapForOneCustomerGroup(ModeOfPaymentFlavour.java:124)
at org.datanucleus.test.ModeOfPaymentFlavour.getAvailableModeOfPaymentFlavoursForOneCustomerGroup(ModeOfPaymentFlavour.java:113)
at org.datanucleus.test.Main$QueryDataTransRunnable0.run(Main.java:150)
at org.datanucleus.test.Main.executeInTransaction(Main.java:51)
at org.datanucleus.test.Main.main(Main.java:179)
NestedThrowablesStackTrace:
java.sql.SQLSyntaxErrorException: An ON clause associated with a JOIN operator is not valid.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:311)
at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:191)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:530)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1672)
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1514)
at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:266)
at org.datanucleus.test.ModeOfPaymentFlavour.getAvailableModeOfPaymentFlavoursMapForOneCustomerGroup(ModeOfPaymentFlavour.java:124)
at org.datanucleus.test.ModeOfPaymentFlavour.getAvailableModeOfPaymentFlavoursForOneCustomerGroup(ModeOfPaymentFlavour.java:113)
at org.datanucleus.test.Main$QueryDataTransRunnable0.run(Main.java:150)
at org.datanucleus.test.Main.executeInTransaction(Main.java:51)
at org.datanucleus.test.Main.main(Main.java:179)
Caused by: java.sql.SQLException: An ON clause associated with a JOIN operator is not valid.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 24 more
Caused by: ERROR 42972: An ON clause associated with a JOIN operator is not valid.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.JoinNode.deferredBindExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.JoinNode.bindResultColumns(Unknown Source)
at org.apache.derby.impl.sql.compile.FromList.bindResultColumns(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindResultColumns(Unknown Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
... 18 more

Marco Schulze added a comment - 27/Apr/10 09:03 PM Note, that the test is the same for NUCRDBMS-376 and NUCRDBMS-377 as they happen in the same situation (actually one query was used as a workaround for a query-bug in the old DN-query-engine).
Btw. great work! The original query that failed with the old DataNucleus query engine works now already fine with MySQL!

The SQL is valid (in ANSI SQL at least), except that it has one too many INNER JOIN (the join to "d0" is unnecessary - see NUCRDBMS-378 - we have the WHERE clause for the contains of "this", so no INNER JOIN when the contained object is already in the query). The SQL then becomes

Andy Jefferson added a comment - 28/Apr/10 05:16 PM The SQL is valid (in ANSI SQL at least), except that it has one too many INNER JOIN (the join to "d0" is unnecessary - see NUCRDBMS-378 - we have the WHERE clause for the contains of "this", so no INNER JOIN when the contained object is already in the query). The SQL then becomes
SELECT DISTINCT 'org.datanucleus.test.ModeOfPaymentFlavour' AS NUCLEUS_TYPE,A0.ICON16X16DATA,A0.MODEOFPAYMENT_MODEOFPAYMENTID_OID,A0.MODEOFPAYMENT_ORGANISATIONID_OID,A0.MODEOFPAYMENTFLAVOURID,D0.MODEOFPAYMENTFLAVOURID,D0.ORGANISATIONID,D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID,D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID,A0.ORGANISATIONID,A0.PRIMARYKEY
FROM JFIRETRADE_MODEOFPAYMENTFLAVOUR A0,JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP
INNER JOIN JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS C0 ON C0.CUSTOMERGROUPID_OID = VAR_CUSTOMERGROUP.CUSTOMERGROUPID AND C0.ORGANISATIONID_OID = VAR_CUSTOMERGROUP.ORGANISATIONID
LEFT OUTER JOIN JFIRETRADE_MODEOFPAYMENTFLAVOURNAME D0 ON D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID = A0.MODEOFPAYMENTFLAVOURID AND D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID = A0.ORGANISATIONID
WHERE C0.MODEOFPAYMENTFLAVOURID_VID = A0.MODEOFPAYMENTFLAVOURID
AND C0.ORGANISATIONID_VID = A0.ORGANISATIONID
AND VAR_CUSTOMERGROUP.ORGANISATIONID = ?
AND VAR_CUSTOMERGROUP.CUSTOMERGROUPID = ?
I checked the column types and the ON clause types are all consistent (VARCHAR(100)) so no idea what Derby is upset about.
There are some JIRAs open on Derby for this specific error message :-
https://issues.apache.org/jira/browse/DERBY-39https://issues.apache.org/jira/browse/DERBY-3395
Sadly Derby JIRA is as slow as Derby the database and haven't get to see the detail on those JIRAs yet either.
Also saw that Derby 10.6.0.0 has (or will have) some of these things fixed, or the error message changed.

I saw there was already a response to your post in DERBY-39 and their suggestion is to try out CROSS JOIN instead of a comma. AFAIK a CROSS JOIN should be supported by every ANSI SQL capable server and thus IMHO is an acceptable work-around.

What's your opinion about CROSS JOIN instead of comma? And does it work at all?

Marco Schulze added a comment - 28/Apr/10 10:00 PM Hi Andy,
thanks a lot for taking a look into this issue!
I saw there was already a response to your post in DERBY-39 and their suggestion is to try out CROSS JOIN instead of a comma. AFAIK a CROSS JOIN should be supported by every ANSI SQL capable server and thus IMHO is an acceptable work-around.
What's your opinion about CROSS JOIN instead of comma? And does it work at all?
Best regards, Marco :-)

Andy Jefferson added a comment - 29/Apr/10 08:29 AM CROSS JOIN is *not* supported by Derby until 10.6 (not yet released) so we use comma syntax.
Run this test on Derby 10.6.* (nightly builds) and it passes.