Issue Details (XML | Word | Printable)

Key: NUCRDBMS-376
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Marco Schulze
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

Derby: ERROR 42972: An ON clause associated with a JOIN operator is not valid.

Created: 27/Apr/10 08:49 PM   Updated: 29/Apr/10 04:32 PM   Resolved: 29/Apr/10 09:44 AM
Component/s: None
Affects Version/s: 2.1.0.m1
Fix Version/s: 2.1.0.m3

Datastore: Apache Derby
Severity: Development


 Description  « Hide
The following query is IMHO correctly translated to SQL (and it works fine with MySQL), but Derby seems to not support some part of it:

*** JDO Query ***
SELECT
FROM org.datanucleus.test.ModeOfPaymentFlavour
WHERE
  customerGroup.organisationID == paramOrganisationID &&
  customerGroup.customerGroupID == paramCustomerGroupID &&
  customerGroup.modeOfPaymentFlavours.containsValue(this)
VARIABLES CustomerGroup customerGroup
PARAMETERS String paramOrganisationID, String paramCustomerGroupID
import java.lang.String;import org.datanucleus.test.CustomerGroup

*** SQL Query ***
SELECT DISTINCT
  'org.datanucleus.test.ModeOfPaymentFlavour' AS NUCLEUS_TYPE,
  "a0"."icon16x16data",
  "a0"."modeofpayment_modeofpaymentid_oid",
  "a0"."modeofpayment_organisationid_oid",
  "a0"."modeofpaymentflavourid",
  "e0"."modeofpaymentflavourid",
  "e0"."organisationid",
  "e0"."modeofpaymentflavour_modeofpaymentflavourid_oid",
  "e0"."modeofpaymentflavour_organisationid_oid",
  "a0"."organisationid",
  "a0"."primarykey"
FROM "jfiretrade_modeofpaymentflavour" "a0","jfiretrade_customergroup" "var_customergroup"
INNER JOIN "jfiretrade_customergroup_modeofpaymentflavours" "c0" ON
  "var_customergroup"."customergroupid" = "c0"."customergroupid_oid" AND
  "var_customergroup"."organisationid" = "c0"."organisationid_oid"
INNER JOIN "jfiretrade_modeofpaymentflavour" "d0" ON
  "c0"."modeofpaymentflavourid_vid" = "d0"."modeofpaymentflavourid" AND
  "c0"."organisationid_vid" = "d0"."organisationid"
LEFT OUTER JOIN "jfiretrade_modeofpaymentflavourname" "e0" ON
  "a0"."modeofpaymentflavourid" = "e0"."modeofpaymentflavour_modeofpaymentflavourid_oid" AND
  "a0"."organisationid" = "e0"."modeofpaymentflavour_organisationid_oid"
WHERE
  "d0"."modeofpaymentflavourid" = "a0"."modeofpaymentflavourid" AND
  "d0"."organisationid" = "a0"."organisationid" AND
  "var_customergroup"."organisationid" = ? AND
  "var_customergroup"."customergroupid" = ?

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

*** Log ***
Derby (with exception): http://www.nightlabs.de/~marco/datanucleus/2010-04-27.00/datanucleus-derby-query0.log
MySQL (successful): http://www.nightlabs.de/~marco/datanucleus/2010-04-27.00/datanucleus-mysql.log

*** Test ***
http://www.nightlabs.de/~marco/datanucleus/2010-04-27.00/TestDataNucleusOneDatastore-no-lib.tar.gz
http://www.nightlabs.de/~marco/datanucleus/2010-04-27.00/TestDataNucleusOneDatastore-with-lib.tar.gz


Sort Order: Ascending order - Click to sort in descending order
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!

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-39
https://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.

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.

Andy Jefferson added a comment - 29/Apr/10 09:44 AM
SVN trunk works fine, with or without Derby 10.6. Versions prior to 10.6 use a cross join as "INNER JOIN tbl ON 1=1" to get around Derby bugs

Marco Schulze added a comment - 29/Apr/10 04:32 PM
Hi Andy,

thanks a lot for your efforts! It works fine in JFire now! Closing this issue.

Best regards, Marco :-)