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


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

Andy Jefferson made changes - 29/Apr/10 09:44 AM
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Assignee Andy Jefferson [ andy ]
Fix Version/s 2.1.0.m3 [ 10943 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 24/May/10 03:43 PM
Status Resolved [ 5 ] Closed [ 6 ]