DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
Issue Details (XML | Word | Printable)

Key: NUCRDBMS-377
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 42X04

Created: 27/Apr/10 09:00 PM   Updated: 24/May/10 03:43 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
And again a query that works with MySQL but doesn't with Derby:

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

*** SQL Query ***
SELECT DISTINCT
  'org.datanucleus.test.ModeOfPaymentFlavour' AS NUCLEUS_TYPE,
  "a0"."icon16x16data",
  "a0"."modeofpayment_modeofpaymentid_oid",
  "a0"."modeofpayment_organisationid_oid",
  "a0"."modeofpaymentflavourid",
  "c0"."modeofpaymentflavourid",
  "c0"."organisationid",
  "c0"."modeofpaymentflavour_modeofpaymentflavourid_oid",
  "c0"."modeofpaymentflavour_organisationid_oid",
  "a0"."organisationid",
  "a0"."primarykey"
FROM "jfiretrade_modeofpaymentflavour" "a0","jfiretrade_customergroup" "var_customergroup"
LEFT OUTER JOIN "jfiretrade_modeofpaymentflavourname" "c0" ON
  "a0"."modeofpaymentflavourid" = "c0"."modeofpaymentflavour_modeofpaymentflavourid_oid" AND
  "a0"."organisationid" = "c0"."modeofpaymentflavour_organisationid_oid"
WHERE
  "var_customergroup"."organisationid" = ? AND
  "var_customergroup"."customergroupid" = ? AND
  (
    (
      EXISTS (
        SELECT 1 FROM "jfiretrade_modeofpaymentflavour" "a0_sub"
        INNER JOIN "jfiretrade_customergroup_modeofpaymentflavours" "b0_sub" ON
          "a0_sub"."modeofpaymentflavourid" = "b0_sub"."modeofpaymentflavourid_vid" AND
          "a0_sub"."organisationid" = "b0_sub"."organisationid_vid"
        WHERE
          "b0_sub"."customergroupid_oid" = "var_customergroup"."customergroupid" AND
          "b0_sub"."organisationid_oid" = "var_customergroup"."organisationid" AND
          "a0_sub"."modeofpaymentflavourid" = "a0"."modeofpaymentflavourid" AND
          "a0_sub"."organisationid" = "a0"."organisationid"
      )
    ) OR
    (
      EXISTS (
        SELECT 1 FROM "jfiretrade_modeofpayment" "a0_sub"
        INNER JOIN "jfiretrade_customergroup_modeofpayments" "b0_sub" ON
          "a0_sub"."modeofpaymentid" = "b0_sub"."modeofpaymentid_vid" AND
          "a0_sub"."organisationid" = "b0_sub"."organisationid_vid"
        WHERE
          "b0_sub"."customergroupid_oid" = "var_customergroup"."customergroupid" AND
          "b0_sub"."organisationid_oid" = "var_customergroup"."organisationid" AND
          EXISTS (
            SELECT 1 FROM "jfiretrade_modeofpaymentflavour" "a0_sub"
            WHERE
              "a0_sub"."modeofpayment_modeofpaymentid_oid" = "a0_sub"."modeofpaymentid" AND
              "a0_sub"."modeofpayment_organisationid_oid" = "a0_sub"."organisationid" AND
              "a0_sub"."modeofpaymentflavourid" = "a0"."modeofpaymentflavourid" AND
              "a0_sub"."organisationid" = "a0"."organisationid"
          )
      )
    )
  )

*** 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.getAvailableModeOfPaymentFlavoursForOneCustomerGroup2(ModeOfPaymentFlavour.java:108)
at org.datanucleus.test.Main$QueryDataTransRunnable1.run(Main.java:160)
at org.datanucleus.test.Main.executeInTransaction(Main.java:51)
at org.datanucleus.test.Main.main(Main.java:184)
NestedThrowablesStackTrace:
java.sql.SQLSyntaxErrorException: Column 'a0_sub.modeofpaymentid' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'a0_sub.modeofpaymentid' is not a column in the target table.
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.getAvailableModeOfPaymentFlavoursForOneCustomerGroup2(ModeOfPaymentFlavour.java:108)
at org.datanucleus.test.Main$QueryDataTransRunnable1.run(Main.java:160)
at org.datanucleus.test.Main.executeInTransaction(Main.java:51)
at org.datanucleus.test.Main.main(Main.java:184)
Caused by: java.sql.SQLException: Column 'a0_sub.modeofpaymentid' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'a0_sub.modeofpaymentid' is not a column in the target table.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 23 more
Caused by: ERROR 42X04: Column 'a0_sub.modeofpaymentid' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'a0_sub.modeofpaymentid' is not a column in the target table.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.OrNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.BinaryLogicalOperatorNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.AndNode.bindExpression(Unknown Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(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)
... 17 more

*** Log ***
Derby (with exception): http://www.nightlabs.de/~marco/datanucleus/2010-04-27.00/datanucleus-derby-query1.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:04 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 - 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