Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached

      Description

      The exception:
      ---------------------------------------
      Error: An ON clause associated with a JOIN operator is not valid.
      ---------------------------------------

      happens when I run the below SQL script:
      ---------------------------------------
      SELECT
      THIS.DOSSIERTEMPLATE_ID
      FROM DOSSIERTEMPLATE THIS,
      ENTITLEMENT UNBOUND_ENTITLE
      INNER JOIN
      ENTITLEMENT II
      ON UNBOUND_ENTITLE.ENTITLEMENT_ID = II.ENTITLEMENT_ID
      INNER JOIN
      DOSSIERTEMPLATERESOURCE BB
      ON II.ENTITLED_TO_RESOURCE_ID_OID = BB.DOSSIERTEMPLATERESOURCE_ID
      INNER JOIN
      I18N THIS_LABEL
      ON THIS.LABEL_I18N_ID_OID = THIS_LABEL.I18N_ID
      ---------------------------------------

      It works fine if I run without the LABEL join
      ---------------------------------------
      SELECT
      THIS.DOSSIERTEMPLATE_ID
      FROM DOSSIERTEMPLATE THIS,
      ENTITLEMENT UNBOUND_ENTITLE
      INNER JOIN
      ENTITLEMENT II
      ON UNBOUND_ENTITLE.ENTITLEMENT_ID = II.ENTITLEMENT_ID
      INNER JOIN
      DOSSIERTEMPLATERESOURCE BB
      ON II.ENTITLED_TO_RESOURCE_ID_OID = BB.DOSSIERTEMPLATERESOURCE_ID
      ---------------------------------------

      The column LABEL_I18N_ID_OID is BIGINT and has a FK to I18N_ID, which is BIGINT as well

      1. d39.sql
        3 kB
        Knut Anders Hatlen
      2. derby-joinon.tar.gz
        10 kB
        James Alan Shepherd

        Issue Links

          Activity

          Hide
          Shreyas Kaushik added a comment -

          If you could provide the table structure , I mean the columns in the tables and the commands to create them it would be great. This would help to reproduce the problem and then investigate.

          Show
          Shreyas Kaushik added a comment - If you could provide the table structure , I mean the columns in the tables and the commands to create them it would be great. This would help to reproduce the problem and then investigate.
          Hide
          Rahul Merwah added a comment -

          I hit into a similar problem with the latest trunk and the analysis showed that the ON clause is not recognizing all the prior declared table aliases – just the last one before.

          In this example it translates to:
          Select A.ID
          FROM
          A, B
          INNER JOIN B'
          ON B.ID = B'.ID <-- (1) WORKS
          INNER JOIN C
          ON B'.FKEY = C.ID <-- (2) WORKS
          INNER JOIN D
          ON A.FKEY = D.ID <-- (3) DOESNT WORK

          (3) doesn't work because it is refering to anything besides C (which was the last one it saw before the Join). You can see that (1) will stop working if you switch the order of "FROM A, B" to "FROM B, A" as A is the last one on the stack.

          According to the specs the on clause can refer to anything table that is on the stack prior to that point... so C can refer to A, B, and B' ... B' can only refer to A, and B and so on.

          Hope this helps... I am currently re-writing my query to do it in the order as a workaround but this needs to be fixed in the code-base. Unfortunately my re-order query is possible since mine was a case of (1) so this will not help Erik who has a case of (3).

          Regards,

          • Rahul
          Show
          Rahul Merwah added a comment - I hit into a similar problem with the latest trunk and the analysis showed that the ON clause is not recognizing all the prior declared table aliases – just the last one before. In this example it translates to: Select A.ID FROM A, B INNER JOIN B' ON B.ID = B'.ID <-- (1) WORKS INNER JOIN C ON B'.FKEY = C.ID <-- (2) WORKS INNER JOIN D ON A.FKEY = D.ID <-- (3) DOESNT WORK (3) doesn't work because it is refering to anything besides C (which was the last one it saw before the Join). You can see that (1) will stop working if you switch the order of "FROM A, B" to "FROM B, A" as A is the last one on the stack. According to the specs the on clause can refer to anything table that is on the stack prior to that point... so C can refer to A, B, and B' ... B' can only refer to A, and B and so on. Hope this helps... I am currently re-writing my query to do it in the order as a workaround but this needs to be fixed in the code-base. Unfortunately my re-order query is possible since mine was a case of (1) so this will not help Erik who has a case of (3). Regards, Rahul
          Hide
          Satheesh Bandaram added a comment -

          I haven't checked what standard says yet. But trying this on DB2 that I have access to also fails. If it is allowed by standard, Derby should also support this.

          => select a.id from a,b inner join c on b.id=c.id inner join d on a.fkey=d.id
          SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement
          is not valid. SQLSTATE=42972

          Show
          Satheesh Bandaram added a comment - I haven't checked what standard says yet. But trying this on DB2 that I have access to also fails. If it is allowed by standard, Derby should also support this. => select a.id from a,b inner join c on b.id=c.id inner join d on a.fkey=d.id SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid. SQLSTATE=42972
          Hide
          Erik Bengtson added a comment -

          Back to the same issue again. I have another sample to reproduce the problem

          CREATE TABLE CONTENTTYPE
          (
          CONTENTTYPE_ID BIGINT NOT NULL,
          "TYPE" VARCHAR(255)
          )
          ALTER TABLE CONTENTTYPE ADD CONSTRAINT CONTENTTYPE_PK PRIMARY KEY (CONTENTTYPE_ID)
          CREATE TABLE DOCUMENT
          (
          DOCUMENT_ID BIGINT NOT NULL,
          DOCUMENT VARCHAR(255)
          )
          ALTER TABLE DOCUMENT ADD CONSTRAINT DOCUMENT_PK PRIMARY KEY (DOCUMENT_ID)
          CREATE TABLE CONTENT
          (
          CONTENT_ID BIGINT NOT NULL,
          CONTENT VARCHAR(255),
          CONTENT_TYPE_CONTENTTYPE_ID_OID BIGINT
          )
          ALTER TABLE CONTENT ADD CONSTRAINT CONTENT_PK PRIMARY KEY (CONTENT_ID)
          CREATE TABLE DOCUMENT_CONTENTS
          (
          DOCUMENT_ID_OID BIGINT NOT NULL,
          CONTENT_ID_EID BIGINT NOT NULL
          )
          ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_PK PRIMARY KEY (DOCUMENT_ID_OID,CONTENT_ID_EID)
          ALTER TABLE CONTENT ADD CONSTRAINT CONTENT_FK1 FOREIGN KEY (CONTENT_TYPE_CONTENTTYPE_ID_OID) REFERENCES CONTENTTYPE (CONTENTTYPE_ID)
          ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_FK2 FOREIGN KEY (CONTENT_ID_EID) REFERENCES CONTENT (CONTENT_ID)
          ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_FK1 FOREIGN KEY (DOCUMENT_ID_OID) REFERENCES DOCUMENT (DOCUMENT_ID)

          ---populate here something
          INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
          INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)
          INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?)

          --select
          SELECT
          DISTINCT 'org.jpox.samples.document.Document' AS JPOXMETADATA,THIS.DOCUMENT_ID,THIS.DOCUMENT

          FROM
          DOCUMENT THIS,
          CONTENT UNBOUND_CONTENT

          LEFT OUTER JOIN CONTENT UNBOUND_CONTENT_CONTENT
          ON UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT_CONTENT.CONTENT_ID

          INNER JOIN DOCUMENT_CONTENTS THIS_CONTENTS
          ON THIS_CONTENTS.DOCUMENT_ID_OID = THIS.DOCUMENT_ID

          WHERE

          UNBOUND_CONTENT_CONTENT.CONTENT = ? AND UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT.CONTENT_ID

          -------------------------------

          javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT DISTINCT 'org.jpox.samples.document.Document' AS JPOXMETADATA,THIS.DOCUMENT_ID,THIS.DOCUMENT FROM DOCUMENT THIS,CONTENT UNBOUND_CONTENT LEFT OUTER JOIN CONTENT UNBOUND_CONTENT_CONTENT ON UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT_CONTENT.CONTENT_ID INNER JOIN DOCUMENT_CONTENTS THIS_CONTENTS ON THIS_CONTENTS.DOCUMENT_ID_OID = THIS.DOCUMENT_ID WHERE UNBOUND_CONTENT_CONTENT.CONTENT = ? AND UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT.CONTENT_ID" : An ON clause associated with a JOIN operator is not valid.
          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.ReadCursorNode.bind(Unknown Source)
          at org.apache.derby.impl.sql.compile.CursorNode.bind(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)
          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.jdbc.Driver30.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.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
          at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)
          at org.jpox.store.query.Query.getStatement(Query.java:797)
          at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520)
          at org.jpox.store.query.Query.executeWithMap(Query.java:1113)
          at org.jpox.store.query.Query.executeWithArray(Query.java:1086)
          at org.jpox.store.query.Query.execute(Query.java:1009)
          at org.jpox.persistence.JDOQLBasicTest.testQueryUnboundVariables(JDOQLBasicTest.java:2284)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:324)
          at junit.framework.TestCase.runTest(TestCase.java:154)
          at junit.framework.TestCase.runBare(TestCase.java:127)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:118)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
          .
          at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:574)
          at org.jpox.store.query.Query.executeWithMap(Query.java:1113)
          at org.jpox.store.query.Query.executeWithArray(Query.java:1086)
          at org.jpox.store.query.Query.execute(Query.java:1009)
          at org.jpox.persistence.JDOQLBasicTest.testQueryUnboundVariables(JDOQLBasicTest.java:2284)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:324)
          at junit.framework.TestCase.runTest(TestCase.java:154)
          at junit.framework.TestCase.runBare(TestCase.java:127)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:118)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

          Show
          Erik Bengtson added a comment - Back to the same issue again. I have another sample to reproduce the problem CREATE TABLE CONTENTTYPE ( CONTENTTYPE_ID BIGINT NOT NULL, "TYPE" VARCHAR(255) ) ALTER TABLE CONTENTTYPE ADD CONSTRAINT CONTENTTYPE_PK PRIMARY KEY (CONTENTTYPE_ID) CREATE TABLE DOCUMENT ( DOCUMENT_ID BIGINT NOT NULL, DOCUMENT VARCHAR(255) ) ALTER TABLE DOCUMENT ADD CONSTRAINT DOCUMENT_PK PRIMARY KEY (DOCUMENT_ID) CREATE TABLE CONTENT ( CONTENT_ID BIGINT NOT NULL, CONTENT VARCHAR(255), CONTENT_TYPE_CONTENTTYPE_ID_OID BIGINT ) ALTER TABLE CONTENT ADD CONSTRAINT CONTENT_PK PRIMARY KEY (CONTENT_ID) CREATE TABLE DOCUMENT_CONTENTS ( DOCUMENT_ID_OID BIGINT NOT NULL, CONTENT_ID_EID BIGINT NOT NULL ) ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_PK PRIMARY KEY (DOCUMENT_ID_OID,CONTENT_ID_EID) ALTER TABLE CONTENT ADD CONSTRAINT CONTENT_FK1 FOREIGN KEY (CONTENT_TYPE_CONTENTTYPE_ID_OID) REFERENCES CONTENTTYPE (CONTENTTYPE_ID) ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_FK2 FOREIGN KEY (CONTENT_ID_EID) REFERENCES CONTENT (CONTENT_ID) ALTER TABLE DOCUMENT_CONTENTS ADD CONSTRAINT DOCUMENT_CONUK_FK1 FOREIGN KEY (DOCUMENT_ID_OID) REFERENCES DOCUMENT (DOCUMENT_ID) ---populate here something INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?) INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO CONTENT (CONTENT_ID,CONTENT,CONTENT_TYPE_CONTENTTYPE_ID_OID) VALUES (?,?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT (DOCUMENT_ID,DOCUMENT) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) INSERT INTO DOCUMENT_CONTENTS (DOCUMENT_ID_OID,CONTENT_ID_EID) VALUES (?,?) --select SELECT DISTINCT 'org.jpox.samples.document.Document' AS JPOXMETADATA,THIS.DOCUMENT_ID,THIS.DOCUMENT FROM DOCUMENT THIS, CONTENT UNBOUND_CONTENT LEFT OUTER JOIN CONTENT UNBOUND_CONTENT_CONTENT ON UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT_CONTENT.CONTENT_ID INNER JOIN DOCUMENT_CONTENTS THIS_CONTENTS ON THIS_CONTENTS.DOCUMENT_ID_OID = THIS.DOCUMENT_ID WHERE UNBOUND_CONTENT_CONTENT.CONTENT = ? AND UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT.CONTENT_ID ------------------------------- javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT DISTINCT 'org.jpox.samples.document.Document' AS JPOXMETADATA,THIS.DOCUMENT_ID,THIS.DOCUMENT FROM DOCUMENT THIS,CONTENT UNBOUND_CONTENT LEFT OUTER JOIN CONTENT UNBOUND_CONTENT_CONTENT ON UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT_CONTENT.CONTENT_ID INNER JOIN DOCUMENT_CONTENTS THIS_CONTENTS ON THIS_CONTENTS.DOCUMENT_ID_OID = THIS.DOCUMENT_ID WHERE UNBOUND_CONTENT_CONTENT.CONTENT = ? AND UNBOUND_CONTENT.CONTENT_ID = UNBOUND_CONTENT.CONTENT_ID" : An ON clause associated with a JOIN operator is not valid. 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.ReadCursorNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bind(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) 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.jdbc.Driver30.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.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355) at org.jpox.store.StatementText.prepareStatement(StatementText.java:204) at org.jpox.store.query.Query.getStatement(Query.java:797) at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520) at org.jpox.store.query.Query.executeWithMap(Query.java:1113) at org.jpox.store.query.Query.executeWithArray(Query.java:1086) at org.jpox.store.query.Query.execute(Query.java:1009) at org.jpox.persistence.JDOQLBasicTest.testQueryUnboundVariables(JDOQLBasicTest.java:2284) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) . at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:574) at org.jpox.store.query.Query.executeWithMap(Query.java:1113) at org.jpox.store.query.Query.executeWithArray(Query.java:1086) at org.jpox.store.query.Query.execute(Query.java:1009) at org.jpox.persistence.JDOQLBasicTest.testQueryUnboundVariables(JDOQLBasicTest.java:2284) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
          Hide
          Erik Bengtson added a comment -

          ONE MORE EXAMPLE

          – schema –
          CREATE TABLE CUSTOMER
          (
          CUSTOMER_ID BIGINT NOT NULL,
          "NAME" VARCHAR(100)
          )
          ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)

          CREATE TABLE SUPPLIER
          (
          SUPPLIER_ID BIGINT NOT NULL,
          "NAME" VARCHAR(100)
          )
          ALTER TABLE SUPPLIER ADD CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIER_ID)

          18:45:23,884 (main) DEBUG [JPOX.RDBMS.DDL] - CREATE TABLE CUSTOMER_SUPPLIER
          (
          CUSTOMER_ID BIGINT NOT NULL,
          SUPPLIER_ID BIGINT NOT NULL
          )
          ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_PK PRIMARY KEY (CUSTOMER_ID,SUPPLIER_ID)
          ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_FK2 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID)
          ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_FK1 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID)

          – data used –
          Customer customer1 = new Customer("C1");
          Customer customer2 = new Customer("C2");
          Customer customer3 = new Customer("C3");
          Supplier supplier1 = new Supplier("S1");
          Supplier supplier2 = new Supplier("S2");
          Supplier supplier3 = new Supplier("S3");
          Supplier supplier4 = new Supplier("S4");
          Supplier supplier5 = new Supplier("S5");
          customer1.addSupplier(supplier1);
          customer1.addSupplier(supplier2);
          customer2.addSupplier(supplier1);
          customer2.addSupplier(supplier3);
          customer2.addSupplier(supplier5);
          customer3.addSupplier(supplier1);
          customer3.addSupplier(supplier2);
          customer3.addSupplier(supplier3);
          customer3.addSupplier(supplier4);
          – query –

          SELECT UNBOUND_S.SUPPLIER_ID
          FROM CUSTOMER THIS,
          SUPPLIER UNBOUND_S
          INNER JOIN CUSTOMER_SUPPLIER THIS_SUPPLIERS ON THIS_SUPPLIERS.CUSTOMER_ID = THIS.CUSTOMER_ID

          junit.framework.AssertionFailedError: Error executing JDOQL query "SELECT UNBOUND_S.SUPPLIER_ID FROM CUSTOMER THIS,SUPPLIER UNBOUND_S INNER JOIN CUSTOMER_SUPPLIER THIS_SUPPLIERS ON THIS_SUPPLIERS.CUSTOMER_ID = THIS.CUSTOMER_ID" : An ON clause associated with a JOIN operator is not valid.
          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.ReadCursorNode.bind(Unknown Source)
          at org.apache.derby.impl.sql.compile.CursorNode.bind(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)
          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.jdbc.Driver30.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.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
          at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)
          at org.jpox.store.query.Query.getStatement(Query.java:797)
          at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520)
          at org.jpox.store.query.Query.executeWithMap(Query.java:1113)
          at org.jpox.store.query.Query.executeWithArray(Query.java:1086)
          at org.jpox.store.query.Query.execute(Query.java:1009)
          at org.jpox.persistence.JDOQLResultTest.testSetResultCartesianProductContains(JDOQLResultTest.java:868)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:324)
          at junit.framework.TestCase.runTest(TestCase.java:154)
          at junit.framework.TestCase.runBare(TestCase.java:127)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:118)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
          .
          at junit.framework.Assert.fail(Assert.java:47)
          at org.jpox.persistence.JDOQLResultTest.testSetResultCartesianProductContains(JDOQLResultTest.java:876)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:324)
          at junit.framework.TestCase.runTest(TestCase.java:154)
          at junit.framework.TestCase.runBare(TestCase.java:127)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:118)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

          Show
          Erik Bengtson added a comment - ONE MORE EXAMPLE – schema – CREATE TABLE CUSTOMER ( CUSTOMER_ID BIGINT NOT NULL, "NAME" VARCHAR(100) ) ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID) CREATE TABLE SUPPLIER ( SUPPLIER_ID BIGINT NOT NULL, "NAME" VARCHAR(100) ) ALTER TABLE SUPPLIER ADD CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIER_ID) 18:45:23,884 (main) DEBUG [JPOX.RDBMS.DDL] - CREATE TABLE CUSTOMER_SUPPLIER ( CUSTOMER_ID BIGINT NOT NULL, SUPPLIER_ID BIGINT NOT NULL ) ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_PK PRIMARY KEY (CUSTOMER_ID,SUPPLIER_ID) ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_FK2 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID) ALTER TABLE CUSTOMER_SUPPLIER ADD CONSTRAINT CUSTOMER_SUPTE_FK1 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) – data used – Customer customer1 = new Customer("C1"); Customer customer2 = new Customer("C2"); Customer customer3 = new Customer("C3"); Supplier supplier1 = new Supplier("S1"); Supplier supplier2 = new Supplier("S2"); Supplier supplier3 = new Supplier("S3"); Supplier supplier4 = new Supplier("S4"); Supplier supplier5 = new Supplier("S5"); customer1.addSupplier(supplier1); customer1.addSupplier(supplier2); customer2.addSupplier(supplier1); customer2.addSupplier(supplier3); customer2.addSupplier(supplier5); customer3.addSupplier(supplier1); customer3.addSupplier(supplier2); customer3.addSupplier(supplier3); customer3.addSupplier(supplier4); – query – SELECT UNBOUND_S.SUPPLIER_ID FROM CUSTOMER THIS, SUPPLIER UNBOUND_S INNER JOIN CUSTOMER_SUPPLIER THIS_SUPPLIERS ON THIS_SUPPLIERS.CUSTOMER_ID = THIS.CUSTOMER_ID junit.framework.AssertionFailedError: Error executing JDOQL query "SELECT UNBOUND_S.SUPPLIER_ID FROM CUSTOMER THIS,SUPPLIER UNBOUND_S INNER JOIN CUSTOMER_SUPPLIER THIS_SUPPLIERS ON THIS_SUPPLIERS.CUSTOMER_ID = THIS.CUSTOMER_ID" : An ON clause associated with a JOIN operator is not valid. 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.ReadCursorNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bind(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) 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.jdbc.Driver30.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.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355) at org.jpox.store.StatementText.prepareStatement(StatementText.java:204) at org.jpox.store.query.Query.getStatement(Query.java:797) at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520) at org.jpox.store.query.Query.executeWithMap(Query.java:1113) at org.jpox.store.query.Query.executeWithArray(Query.java:1086) at org.jpox.store.query.Query.execute(Query.java:1009) at org.jpox.persistence.JDOQLResultTest.testSetResultCartesianProductContains(JDOQLResultTest.java:868) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) . at junit.framework.Assert.fail(Assert.java:47) at org.jpox.persistence.JDOQLResultTest.testSetResultCartesianProductContains(JDOQLResultTest.java:876) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at junit.framework.TestCase.runTest(TestCase.java:154) at junit.framework.TestCase.runBare(TestCase.java:127) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
          Hide
          Erik Bengtson added a comment -

          I tried in MS SQL and this is not supported there too

          Show
          Erik Bengtson added a comment - I tried in MS SQL and this is not supported there too
          Hide
          Satheesh Bandaram added a comment -

          I tried both the SQL statements above (in two different comments) on DB2 that I have access to. Both were rejected there too... Erik already mentioned MS SQL doesn't like this syntax.

          Show
          Satheesh Bandaram added a comment - I tried both the SQL statements above (in two different comments) on DB2 that I have access to. Both were rejected there too... Erik already mentioned MS SQL doesn't like this syntax.
          Hide
          Erik Bengtson added a comment -

          Once more, I got the same error. This time the SQL should work in Derby IMO. It does in MSSQL

          CREATE TABLE persons (
          PERSONID INTEGER NOT NULL,
          FIRSTNAME VARCHAR(32) NOT NULL,
          LASTNAME VARCHAR(32) NOT NULL,
          MIDDLENAME VARCHAR(32),
          BIRTHDATE VARCHAR(32) NOT NULL,
          ADDRID INTEGER,
          STREET VARCHAR(64),
          CITY VARCHAR(64),
          STATE CHAR(2),
          ZIPCODE CHAR(5),
          COUNTRY VARCHAR(64),
          HIREDATE VARCHAR(32),
          WEEKLYHOURS REAL,
          DEPARTMENT INTEGER,
          FUNDINGDEPT INTEGER,
          MANAGER INTEGER,
          MENTOR INTEGER,
          HRADVISOR INTEGER,
          SALARY REAL,
          WAGE REAL,
          DISCRIMINATOR varchar(64) NOT NULL,
          CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
          );

          CREATE TABLE projects (
          PROJID INTEGER NOT NULL,
          NAME VARCHAR(32) NOT NULL,
          BUDGET DECIMAL(11,2) NOT NULL,
          CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
          );

          CREATE TABLE project_member (
          PROJID INTEGER NOT NULL,
          MEMBER INTEGER NOT NULL
          );

          SELECT THIS.DISCRIMINATOR,THIS.BIRTHDATE,THIS.FIRSTNAME,THIS.LASTNAME,THIS.PERSONID,THIS.HIREDATE,THIS.WEEKLYHOURS
          FROM
          APPLICATIONIDENTITY0.PERSONS THIS,
          APPLICATIONIDENTITY0.PROJECTS UNBOUND_P,
          APPLICATIONIDENTITY0.PERSONS UNBOUND_E
          LEFT OUTER JOIN APPLICATIONIDENTITY0.PERSONS UNBOUND_E_FIRSTNAME ON UNBOUND_E.PERSONID = UNBOUND_E_FIRSTNAME.PERSONID
          WHERE (THIS.DISCRIMINATOR = 'aa' OR THIS.DISCRIMINATOR = 'bb' OR THIS.DISCRIMINATOR = 'cc') AND UNBOUND_E.MANAGER = THIS.PERSONID
          AND
          (EXISTS (SELECT 1 FROM APPLICATIONIDENTITY0.PROJECT_MEMBER THIS_PROJECTS
          LEFT OUTER JOIN APPLICATIONIDENTITY0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
          WHERE THIS_PROJECTS."MEMBER" = THIS.PERSONID AND THIS_PROJECTS."MEMBER" = THIS.PERSONID
          AND UNBOUND_P.PROJID = THIS_PROJECTS.PROJID AND UNBOUND_P_NAME."NAME" = 'name'
          )
          )
          AND
          (EXISTS (SELECT 1 FROM APPLICATIONIDENTITY0.PERSONS THIS_TEAM WHERE THIS_TEAM.MANAGER = THIS.PERSONID AND UNBOUND_E_FIRSTNAME.FIRSTNAME = 'name'))

          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.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.DMLStatementNode.bindExpressions(Unknown Source)
          at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
          at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source)
          at org.apache.derby.impl.sql.compile.CursorNode.bind(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)
          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.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
          at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:241)
          at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
          at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)
          at org.jpox.store.query.Query.getStatement(Query.java:797)
          at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520)
          at org.jpox.store.query.Query.executeWithMap(Query.java:1113)
          at org.jpox.store.query.Query.executeWithArray(Query.java:1086)
          at org.jpox.store.query.Query.execute(Query.java:1009)
          at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1153)
          at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1031)
          at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:968)
          at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:948)
          at org.apache.jdo.tck.query.jdoql.variables.MixedVariables.testPositive(MixedVariables.java:122)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:585)
          at junit.framework.TestCase.runTest(TestCase.java:154)
          at org.apache.jdo.tck.JDO_Test.runBare(JDO_Test.java:204)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:118)
          at junit.framework.TestSuite.runTest(TestSuite.java:208)
          at junit.framework.TestSuite.run(TestSuite.java:203)
          at junit.framework.TestSuite.runTest(TestSuite.java:208)
          at junit.framework.TestSuite.run(TestSuite.java:203)
          at junit.textui.TestRunner.doRun(TestRunner.java:116)
          at junit.textui.TestRunner.doRun(TestRunner.java:109)
          at org.apache.jdo.tck.util.BatchTestRunner.start(BatchTestRunner.java:120)
          at org.apache.jdo.tck.util.BatchTestRunner.main(BatchTestRunner.java:95)

          ----------------
          If the lines below are comment, the statement compiles
          ...
          LEFT OUTER JOIN APPLICATIONIDENTITY0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
          ..
          AND UNBOUND_P.PROJID = THIS_PROJECTS.PROJID AND UNBOUND_P_NAME."NAME" = 'name'
          ...

          Show
          Erik Bengtson added a comment - Once more, I got the same error. This time the SQL should work in Derby IMO. It does in MSSQL CREATE TABLE persons ( PERSONID INTEGER NOT NULL, FIRSTNAME VARCHAR(32) NOT NULL, LASTNAME VARCHAR(32) NOT NULL, MIDDLENAME VARCHAR(32), BIRTHDATE VARCHAR(32) NOT NULL, ADDRID INTEGER, STREET VARCHAR(64), CITY VARCHAR(64), STATE CHAR(2), ZIPCODE CHAR(5), COUNTRY VARCHAR(64), HIREDATE VARCHAR(32), WEEKLYHOURS REAL, DEPARTMENT INTEGER, FUNDINGDEPT INTEGER, MANAGER INTEGER, MENTOR INTEGER, HRADVISOR INTEGER, SALARY REAL, WAGE REAL, DISCRIMINATOR varchar(64) NOT NULL, CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID) ); CREATE TABLE projects ( PROJID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, BUDGET DECIMAL(11,2) NOT NULL, CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) ); CREATE TABLE project_member ( PROJID INTEGER NOT NULL, MEMBER INTEGER NOT NULL ); SELECT THIS.DISCRIMINATOR,THIS.BIRTHDATE,THIS.FIRSTNAME,THIS.LASTNAME,THIS.PERSONID,THIS.HIREDATE,THIS.WEEKLYHOURS FROM APPLICATIONIDENTITY0.PERSONS THIS, APPLICATIONIDENTITY0.PROJECTS UNBOUND_P, APPLICATIONIDENTITY0.PERSONS UNBOUND_E LEFT OUTER JOIN APPLICATIONIDENTITY0.PERSONS UNBOUND_E_FIRSTNAME ON UNBOUND_E.PERSONID = UNBOUND_E_FIRSTNAME.PERSONID WHERE (THIS.DISCRIMINATOR = 'aa' OR THIS.DISCRIMINATOR = 'bb' OR THIS.DISCRIMINATOR = 'cc') AND UNBOUND_E.MANAGER = THIS.PERSONID AND (EXISTS (SELECT 1 FROM APPLICATIONIDENTITY0.PROJECT_MEMBER THIS_PROJECTS LEFT OUTER JOIN APPLICATIONIDENTITY0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID WHERE THIS_PROJECTS."MEMBER" = THIS.PERSONID AND THIS_PROJECTS."MEMBER" = THIS.PERSONID AND UNBOUND_P.PROJID = THIS_PROJECTS.PROJID AND UNBOUND_P_NAME."NAME" = 'name' ) ) AND (EXISTS (SELECT 1 FROM APPLICATIONIDENTITY0.PERSONS THIS_TEAM WHERE THIS_TEAM.MANAGER = THIS.PERSONID AND UNBOUND_E_FIRSTNAME.FIRSTNAME = 'name')) 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.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.DMLStatementNode.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bind(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) 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.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:241) at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355) at org.jpox.store.StatementText.prepareStatement(StatementText.java:204) at org.jpox.store.query.Query.getStatement(Query.java:797) at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:520) at org.jpox.store.query.Query.executeWithMap(Query.java:1113) at org.jpox.store.query.Query.executeWithArray(Query.java:1086) at org.jpox.store.query.Query.execute(Query.java:1009) at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1153) at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1031) at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:968) at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:948) at org.apache.jdo.tck.query.jdoql.variables.MixedVariables.testPositive(MixedVariables.java:122) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at junit.framework.TestCase.runTest(TestCase.java:154) at org.apache.jdo.tck.JDO_Test.runBare(JDO_Test.java:204) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:118) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) at junit.framework.TestSuite.runTest(TestSuite.java:208) at junit.framework.TestSuite.run(TestSuite.java:203) at junit.textui.TestRunner.doRun(TestRunner.java:116) at junit.textui.TestRunner.doRun(TestRunner.java:109) at org.apache.jdo.tck.util.BatchTestRunner.start(BatchTestRunner.java:120) at org.apache.jdo.tck.util.BatchTestRunner.main(BatchTestRunner.java:95) ---------------- If the lines below are comment, the statement compiles ... LEFT OUTER JOIN APPLICATIONIDENTITY0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID .. AND UNBOUND_P.PROJID = THIS_PROJECTS.PROJID AND UNBOUND_P_NAME."NAME" = 'name' ...
          Hide
          Erik Bengtson added a comment -

          More food Also works in MSSQL

          SELECT UNBOUND_P.PROJID FROM applicationidentity0.PERSONS THIS,
          applicationidentity0.PROJECTS UNBOUND_P WHERE
          EXISTS (SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_PROJECTS_P
          LEFT OUTER JOIN applicationidentity0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
          WHERE THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND UNBOUND_P.PROJID = THIS_PROJECTS_P.PROJID AND UNBOUND_P_NAME."NAME" = ?
          AND (THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ?))

          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.SubqueryNode.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.ReadCursorNode.bind(Unknown Source)
          at org.apache.derby.impl.sql.compile.CursorNode.bind(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)
          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.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
          at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:241)
          at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355)
          at org.jpox.store.StatementText.prepareStatement(StatementText.java:204)

          ---- SCHEMA ----

          CREATE TABLE companies (
          ID INTEGER NOT NULL,
          NAME VARCHAR(32) NOT NULL,
          FOUNDEDDATE VARCHAR(32) NOT NULL,
          ADDRID INTEGER,
          STREET VARCHAR(64),
          CITY VARCHAR(64),
          STATE CHAR(2),
          ZIPCODE CHAR(5),
          COUNTRY VARCHAR(64),
          CONSTRAINT COMPS_PK PRIMARY KEY (ID)
          );

          CREATE TABLE departments (
          ID INTEGER NOT NULL,
          NAME VARCHAR(32) NOT NULL,
          EMP_OF_THE_MONTH INTEGER,
          COMPANYID INTEGER REFERENCES companies,
          CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
          );

          CREATE TABLE persons (
          PERSONID INTEGER NOT NULL,
          FIRSTNAME VARCHAR(32) NOT NULL,
          LASTNAME VARCHAR(32) NOT NULL,
          MIDDLENAME VARCHAR(32),
          BIRTHDATE VARCHAR(32) NOT NULL,
          ADDRID INTEGER,
          STREET VARCHAR(64),
          CITY VARCHAR(64),
          STATE CHAR(2),
          ZIPCODE CHAR(5),
          COUNTRY VARCHAR(64),
          HIREDATE VARCHAR(32),
          WEEKLYHOURS REAL,
          DEPARTMENT INTEGER REFERENCES departments,
          FUNDINGDEPT INTEGER REFERENCES departments,
          MANAGER INTEGER REFERENCES persons,
          MENTOR INTEGER REFERENCES persons,
          HRADVISOR INTEGER REFERENCES persons,
          SALARY REAL,
          WAGE REAL,
          DISCRIMINATOR varchar(64) NOT NULL,
          CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
          );

          CREATE TABLE insuranceplans (
          INSID INTEGER NOT NULL,
          CARRIER VARCHAR(64) NOT NULL,
          LIFETIME_ORTHO_BENEFIT DECIMAL(22,3),
          PLANTYPE VARCHAR(8),
          DISCRIMINATOR varchar(64) NOT NULL,
          EMPLOYEE INTEGER REFERENCES persons,
          CONSTRAINT INS_PK PRIMARY KEY (INSID)
          );

          CREATE TABLE projects (
          PROJID INTEGER NOT NULL,
          NAME VARCHAR(32) NOT NULL,
          BUDGET DECIMAL(11,2) NOT NULL,
          CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
          );

          CREATE TABLE project_reviewer (
          PROJID INTEGER NOT NULL,
          REVIEWER INTEGER NOT NULL
          );

          CREATE TABLE project_member (
          PROJID INTEGER REFERENCES projects NOT NULL,
          MEMBER INTEGER REFERENCES persons NOT NULL
          );

          CREATE TABLE employee_phoneno_type (
          EMPID INTEGER REFERENCES persons NOT NULL,
          PHONENO VARCHAR(16) NOT NULL,
          TYPE VARCHAR(16) NOT NULL
          );

          ALTER TABLE project_reviewer
          ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
          (PROJID) REFERENCES projects(PROJID);

          ALTER TABLE project_reviewer
          ADD CONSTRAINT PR_REV_FK FOREIGN KEY
          (REVIEWER) REFERENCES persons(PERSONID);

          ALTER TABLE departments
          ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
          (EMP_OF_THE_MONTH) REFERENCES persons(PERSONID);

          Show
          Erik Bengtson added a comment - More food Also works in MSSQL SELECT UNBOUND_P.PROJID FROM applicationidentity0.PERSONS THIS, applicationidentity0.PROJECTS UNBOUND_P WHERE EXISTS (SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_PROJECTS_P LEFT OUTER JOIN applicationidentity0.PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID WHERE THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND THIS_PROJECTS_P."MEMBER" = THIS.PERSONID AND UNBOUND_P.PROJID = THIS_PROJECTS_P.PROJID AND UNBOUND_P_NAME."NAME" = ? AND (THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ?)) — 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.SubqueryNode.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.ReadCursorNode.bind(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.bind(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) 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.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:241) at org.jpox.store.rdbms.RDBMSManager.getStatement(RDBMSManager.java:355) at org.jpox.store.StatementText.prepareStatement(StatementText.java:204) ---- SCHEMA ---- CREATE TABLE companies ( ID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, FOUNDEDDATE VARCHAR(32) NOT NULL, ADDRID INTEGER, STREET VARCHAR(64), CITY VARCHAR(64), STATE CHAR(2), ZIPCODE CHAR(5), COUNTRY VARCHAR(64), CONSTRAINT COMPS_PK PRIMARY KEY (ID) ); CREATE TABLE departments ( ID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, EMP_OF_THE_MONTH INTEGER, COMPANYID INTEGER REFERENCES companies, CONSTRAINT DEPTS_PK PRIMARY KEY (ID) ); CREATE TABLE persons ( PERSONID INTEGER NOT NULL, FIRSTNAME VARCHAR(32) NOT NULL, LASTNAME VARCHAR(32) NOT NULL, MIDDLENAME VARCHAR(32), BIRTHDATE VARCHAR(32) NOT NULL, ADDRID INTEGER, STREET VARCHAR(64), CITY VARCHAR(64), STATE CHAR(2), ZIPCODE CHAR(5), COUNTRY VARCHAR(64), HIREDATE VARCHAR(32), WEEKLYHOURS REAL, DEPARTMENT INTEGER REFERENCES departments, FUNDINGDEPT INTEGER REFERENCES departments, MANAGER INTEGER REFERENCES persons, MENTOR INTEGER REFERENCES persons, HRADVISOR INTEGER REFERENCES persons, SALARY REAL, WAGE REAL, DISCRIMINATOR varchar(64) NOT NULL, CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID) ); CREATE TABLE insuranceplans ( INSID INTEGER NOT NULL, CARRIER VARCHAR(64) NOT NULL, LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), PLANTYPE VARCHAR(8), DISCRIMINATOR varchar(64) NOT NULL, EMPLOYEE INTEGER REFERENCES persons, CONSTRAINT INS_PK PRIMARY KEY (INSID) ); CREATE TABLE projects ( PROJID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, BUDGET DECIMAL(11,2) NOT NULL, CONSTRAINT PROJS_PK PRIMARY KEY (PROJID) ); CREATE TABLE project_reviewer ( PROJID INTEGER NOT NULL, REVIEWER INTEGER NOT NULL ); CREATE TABLE project_member ( PROJID INTEGER REFERENCES projects NOT NULL, MEMBER INTEGER REFERENCES persons NOT NULL ); CREATE TABLE employee_phoneno_type ( EMPID INTEGER REFERENCES persons NOT NULL, PHONENO VARCHAR(16) NOT NULL, TYPE VARCHAR(16) NOT NULL ); ALTER TABLE project_reviewer ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY (PROJID) REFERENCES projects(PROJID); ALTER TABLE project_reviewer ADD CONSTRAINT PR_REV_FK FOREIGN KEY (REVIEWER) REFERENCES persons(PERSONID); ALTER TABLE departments ADD CONSTRAINT EMP_MO_FK FOREIGN KEY (EMP_OF_THE_MONTH) REFERENCES persons(PERSONID);
          Hide
          Rick Hillegas added a comment -

          I'm puzzled by this last query. What is it trying to do? I get the error if I pare off the WHERE clause and just focus on the left join in the subselect:

          SELECT UNBOUND_P.PROJID FROM PERSONS THIS,
          PROJECTS UNBOUND_P WHERE
          EXISTS
          (SELECT 1 FROM PROJECT_MEMBER THIS_PROJECTS_P
          LEFT OUTER JOIN PROJECTS UNBOUND_P_NAME ON
          UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID
          );

          The ON clause does not join the tables in the subselect, which puzzles me. Instead, the ON clause joins one of the subselected tables to a table in the outer select. The following alternative query does make sense to me and does work:

          SELECT UNBOUND_P.PROJID FROM PERSONS THIS,
          PROJECTS UNBOUND_P WHERE
          EXISTS
          (SELECT 1 FROM PROJECT_MEMBER THIS_PROJECTS_P
          LEFT OUTER JOIN PROJECTS UNBOUND_P_NAME ON
          THIS_PROJECTS_P.PROJID = UNBOUND_P_NAME.PROJID
          );

          I would expect the ON clause to join the tables mentioned in the LEFT JOIN, but in the problem query it doesn't. Is it possible that some machine is generating this query and is as confused as I am?

          Show
          Rick Hillegas added a comment - I'm puzzled by this last query. What is it trying to do? I get the error if I pare off the WHERE clause and just focus on the left join in the subselect: SELECT UNBOUND_P.PROJID FROM PERSONS THIS, PROJECTS UNBOUND_P WHERE EXISTS (SELECT 1 FROM PROJECT_MEMBER THIS_PROJECTS_P LEFT OUTER JOIN PROJECTS UNBOUND_P_NAME ON UNBOUND_P.PROJID = UNBOUND_P_NAME.PROJID ); The ON clause does not join the tables in the subselect, which puzzles me. Instead, the ON clause joins one of the subselected tables to a table in the outer select. The following alternative query does make sense to me and does work: SELECT UNBOUND_P.PROJID FROM PERSONS THIS, PROJECTS UNBOUND_P WHERE EXISTS (SELECT 1 FROM PROJECT_MEMBER THIS_PROJECTS_P LEFT OUTER JOIN PROJECTS UNBOUND_P_NAME ON THIS_PROJECTS_P.PROJID = UNBOUND_P_NAME.PROJID ); I would expect the ON clause to join the tables mentioned in the LEFT JOIN, but in the problem query it doesn't. Is it possible that some machine is generating this query and is as confused as I am?
          Hide
          Erik Bengtson added a comment -

          > I'm puzzled by this last query.

          Me too This query is generated by a ORM tool, and I don't have control over it unless I fine tune the code that generates it. Since the ORM tool is covering a lot of scenarios, that's the best we can get for now.

          We want to return PERSON crossjoin PROJECT where PROJECT has NAME=? and exists for PERSON

          There is a join table PROJECT_MEMBER where member is PERSON
          Say

          PERSON =

          { "John", "Mary", "Mark" }

          PROJECT =

          {"Derby", "Ant", "Log4j" }

          PROJECT_MEMBER =

          {("John","Derby"),("Mary","Ant"),("Mark","Derby"),("Mark","Ant")}

          We want to return PERSON crossjoin PROJECT where PROJECT has NAME="Ant" and exists for PERSON

          results are

          {("Mary","Ant"),("Mark","Ant")}
          Show
          Erik Bengtson added a comment - > I'm puzzled by this last query. Me too This query is generated by a ORM tool, and I don't have control over it unless I fine tune the code that generates it. Since the ORM tool is covering a lot of scenarios, that's the best we can get for now. We want to return PERSON crossjoin PROJECT where PROJECT has NAME=? and exists for PERSON There is a join table PROJECT_MEMBER where member is PERSON Say PERSON = { "John", "Mary", "Mark" } PROJECT = {"Derby", "Ant", "Log4j" } PROJECT_MEMBER = {("John","Derby"),("Mary","Ant"),("Mark","Derby"),("Mark","Ant")} We want to return PERSON crossjoin PROJECT where PROJECT has NAME="Ant" and exists for PERSON results are {("Mary","Ant"),("Mark","Ant")}
          Hide
          James Alan Shepherd added a comment -

          I might have a similar issue, with workaround:

          Derby 10.3.2.1 I get the same error with:

          INSERT INTO Blue (
          ID , Name , Type
          ) SELECT
          P.ID , X.Name , X.Type
          FROM
          Red X
          JOIN Blue P
          ON X.nHelp = (SELECT MAX(nHelp) FROM Red)
          AND P.Name = X.ID
          LEFT JOIN Blue SO
          ON SO.ID = P.ID
          AND SO.Name = X.Name
          WHERE SO.ID IS NULL

          Interestingly, I only get the error when the LEFT JOIN has some data, that is where some rows have SO.ID NOT NULL.

          Now, if I remove the Subquery (SELECT MAX(nHelp) From Red) then all is sweetness and light and no bug.

          Only, that is obviously not functionally equivalent!

          Reading Rahul above, I have a cunning plan - rewrite the query so the ON clause in the LEFT JOIN only references the table preceding it, using a derived table:

          INSERT INTO Red (
          ID , Name , Type
          ) SELECT
          D.ID , D.Name , D.Type
          FROM
          ( SELECT
          P.ID , X.Name , X.Type
          FROM Red X
          JOIN Red P
          ON X.nHelp = (SELECT MAX(nHelp) FROM Red)
          AND P.Name = X.ID ) AS D
          LEFT JOIN Red SO
          ON SO.ID = D.ID
          AND SO.Name = D.Name
          WHERE SO.ID IS NULL

          It would be interesting to see if Erik's cross join problem could be fixed with the same work around.

          I'm travelling at the mo, but I will see if I can put together a fuller example later this week.

          Show
          James Alan Shepherd added a comment - I might have a similar issue, with workaround: Derby 10.3.2.1 I get the same error with: INSERT INTO Blue ( ID , Name , Type ) SELECT P.ID , X.Name , X.Type FROM Red X JOIN Blue P ON X.nHelp = (SELECT MAX(nHelp) FROM Red) AND P.Name = X.ID LEFT JOIN Blue SO ON SO.ID = P.ID AND SO.Name = X.Name WHERE SO.ID IS NULL Interestingly, I only get the error when the LEFT JOIN has some data, that is where some rows have SO.ID NOT NULL. Now, if I remove the Subquery (SELECT MAX(nHelp) From Red) then all is sweetness and light and no bug. Only, that is obviously not functionally equivalent! Reading Rahul above, I have a cunning plan - rewrite the query so the ON clause in the LEFT JOIN only references the table preceding it, using a derived table: INSERT INTO Red ( ID , Name , Type ) SELECT D.ID , D.Name , D.Type FROM ( SELECT P.ID , X.Name , X.Type FROM Red X JOIN Red P ON X.nHelp = (SELECT MAX(nHelp) FROM Red) AND P.Name = X.ID ) AS D LEFT JOIN Red SO ON SO.ID = D.ID AND SO.Name = D.Name WHERE SO.ID IS NULL It would be interesting to see if Erik's cross join problem could be fixed with the same work around. I'm travelling at the mo, but I will see if I can put together a fuller example later this week.
          Hide
          James Alan Shepherd added a comment -

          Apologies, the above is not a complete fix. The bug still appears sometimes.

          Moving:
          ON X.nHelp = (SELECT MAX(nHelp) FROM Red)

          to the WHERE clause does seem to be a work-around at the moment.

          Show
          James Alan Shepherd added a comment - Apologies, the above is not a complete fix. The bug still appears sometimes. Moving: ON X.nHelp = (SELECT MAX(nHelp) FROM Red) to the WHERE clause does seem to be a work-around at the moment.
          Hide
          James Alan Shepherd added a comment -

          The attached test case reproduces the bug in 10.3.2.1, but the bug does not show in 10.2.0.0.

          The test case is almost the same as that I submitted for DERBY-3221, so there may be a link?

          I note that previous posters noticed the bug in releases before 10.3.2.1, so I may have found a different infestation that happens to show the same symptoms.

          Show
          James Alan Shepherd added a comment - The attached test case reproduces the bug in 10.3.2.1, but the bug does not show in 10.2.0.0. The test case is almost the same as that I submitted for DERBY-3221 , so there may be a link? I note that previous posters noticed the bug in releases before 10.3.2.1, so I may have found a different infestation that happens to show the same symptoms.
          Hide
          Kathey Marsden added a comment -

          When I run the repro attached to this bug with a trunk sane build I get:
          ij> INSERT INTO SubShape (nID, nSubID) SELECT nID, nSubID FROM Test;
          java.sql.SQLException: Java exception: 'ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLInteger) exp
          ected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLLongint): org.apache.derby.shared.common.s
          anity.AssertFailure'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
          at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:88)
          at org.apache.derby.impl.jdbc.Util.javaException(Util.java:245)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1878)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1324)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1650)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:299)
          at SubShape.insert(SubShape.java:37)
          at org.apache.derby.exe.ac12564092x0117xf01cxf31fx00000011ade01.g0(Unknown Source)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:599)
          at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(ReflectMethod.java:46)
          at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(CallStatementResultSet.java:90)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372)
          at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:159)
          at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(StatementTriggerExecutor.java:80)
          at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:269)
          at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1150)
          at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:487)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1234)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:624)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:556)
          at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330)
          at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508)
          at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:350)
          at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248)
          at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
          at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
          at org.apache.derby.impl.tools.ij.Main.main(Main.java:73)
          at org.apache.derby.tools.ij.main(ij.java:59)
          Caused by: java.sql.SQLException: Java exception: 'ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLI
          nteger) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLLongint): org.apache.derby.shar
          ed.common.sanity.AssertFailure'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:13
          5)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
          ... 35 more
          Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED col1.getClass() (class org.apache.derby.ia
          pi.types.SQLInteger) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLLongint)
          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162)
          at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147)
          at org.apache.derby.impl.store.access.sort.MergeSort.checkColumnTypes(MergeSort.java:472)
          at org.apache.derby.impl.store.access.sort.MergeInserter.insert(MergeInserter.java:98)
          at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(SortResultSet.java:317)
          at org.apache.derby.impl.sql.execute.SortResultSet.openCore(SortResultSet.java:268)
          at org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(NormalizeResultSet.java:147)
          at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:407)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1234)
          ... 28 more
          ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
          ij> [C:/kmarsden/repro/derby-39/derby-joinon]

          It passes with 10.2 but won't run with 10.1 because the CALL syntax in CREATE TRIGGER is not supported. I tend to think the repro is a new issue and a regression. If noone objects I will open a new issue for the
          regression and close DERBY-39 CannotReproduce.

          Show
          Kathey Marsden added a comment - When I run the repro attached to this bug with a trunk sane build I get: ij> INSERT INTO SubShape (nID, nSubID) SELECT nID, nSubID FROM Test; java.sql.SQLException: Java exception: 'ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLInteger) exp ected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLLongint): org.apache.derby.shared.common.s anity.AssertFailure'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:88) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:245) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:1878) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1324) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1650) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:299) at SubShape.insert(SubShape.java:37) at org.apache.derby.exe.ac12564092x0117xf01cxf31fx00000011ade01.g0(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:599) at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(ReflectMethod.java:46) at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(CallStatementResultSet.java:90) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372) at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(GenericTriggerExecutor.java:159) at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(StatementTriggerExecutor.java:80) at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(TriggerEventActivator.java:269) at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1150) at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:487) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1234) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:624) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:556) at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330) at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:508) at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:350) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:248) at org.apache.derby.impl.tools.ij.Main.go(Main.java:215) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181) at org.apache.derby.impl.tools.ij.Main.main(Main.java:73) at org.apache.derby.tools.ij.main(ij.java:59) Caused by: java.sql.SQLException: Java exception: 'ASSERT FAILED col1.getClass() (class org.apache.derby.iapi.types.SQLI nteger) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLLongint): org.apache.derby.shar ed.common.sanity.AssertFailure'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:13 5) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70) ... 35 more Caused by: org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED col1.getClass() (class org.apache.derby.ia pi.types.SQLInteger) expected to be the same as col2.getClass() (class org.apache.derby.iapi.types.SQLLongint) at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:162) at org.apache.derby.shared.common.sanity.SanityManager.THROWASSERT(SanityManager.java:147) at org.apache.derby.impl.store.access.sort.MergeSort.checkColumnTypes(MergeSort.java:472) at org.apache.derby.impl.store.access.sort.MergeInserter.insert(MergeInserter.java:98) at org.apache.derby.impl.sql.execute.SortResultSet.loadSorter(SortResultSet.java:317) at org.apache.derby.impl.sql.execute.SortResultSet.openCore(SortResultSet.java:268) at org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(NormalizeResultSet.java:147) at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:407) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1234) ... 28 more ERROR XJ001: Java exception: ': java.lang.NullPointerException'. ij> [C:/kmarsden/repro/derby-39/derby-joinon] It passes with 10.2 but won't run with 10.1 because the CALL syntax in CREATE TRIGGER is not supported. I tend to think the repro is a new issue and a regression. If noone objects I will open a new issue for the regression and close DERBY-39 CannotReproduce.
          Hide
          James Alan Shepherd added a comment - - edited

          I believe running an insane build will reproduce this DERBY-39.

          Alternatively, to avoid this ASSERT problem, I have used a build with the following properties set:

          debug=true
          optimize=false
          sane=false

          that way you get line numbers. Though if you think what this then reproduces is a different bug, then closing DERBY-39 seems the way to go.

          Show
          James Alan Shepherd added a comment - - edited I believe running an insane build will reproduce this DERBY-39 . Alternatively, to avoid this ASSERT problem, I have used a build with the following properties set: debug=true optimize=false sane=false that way you get line numbers. Though if you think what this then reproduces is a different bug, then closing DERBY-39 seems the way to go.
          Hide
          Kathey Marsden added a comment -

          The issue exposed with the derby-joinon repro has been filed as DERBY-3395. We have not been able to get a reproduction for the original 10.0 issue so closing CannotReproduce until we can get a repro.

          Show
          Kathey Marsden added a comment - The issue exposed with the derby-joinon repro has been filed as DERBY-3395 . We have not been able to get a reproduction for the original 10.0 issue so closing CannotReproduce until we can get a repro.
          Hide
          Knut Anders Hatlen added a comment -

          Reopening since the comments mention plenty of repros. I don't know if it's a bug, but it is reproducible.

          Show
          Knut Anders Hatlen added a comment - Reopening since the comments mention plenty of repros. I don't know if it's a bug, but it is reproducible.
          Hide
          Knut Anders Hatlen added a comment -

          The attached script (d39.sql) contains the repro Erik provided in his comment 09/Feb/06 05:17 PM.

          It produces the following error on 10.2, 10.3 and trunk:

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

          (I'm not granting license, since it's not my code.)

          Show
          Knut Anders Hatlen added a comment - The attached script (d39.sql) contains the repro Erik provided in his comment 09/Feb/06 05:17 PM. It produces the following error on 10.2, 10.3 and trunk: ERROR 42972: An ON clause associated with a JOIN operator is not valid. (I'm not granting license, since it's not my code.)
          Hide
          Erik Bengtson added a comment -

          All scripts I provided here I grant rights to Apache as per Apache 2 License

          Show
          Erik Bengtson added a comment - All scripts I provided here I grant rights to Apache as per Apache 2 License
          Hide
          Kathey Marsden added a comment -

          I don't think this is a newcomer issue. Unmarking it as such.

          Show
          Kathey Marsden added a comment - I don't think this is a newcomer issue. Unmarking it as such.
          Hide
          Myrna van Lunteren added a comment -

          [10.5.2 Triage]

          Show
          Myrna van Lunteren added a comment - [10.5.2 Triage]
          Hide
          Knut Anders Hatlen added a comment -

          After the fix for DERBY-4380 (Subqueries not allowed in ON clause) the error message produced by d39.sql has changed to:

          ERROR 42X04: Column 'THIS.DOCUMENT_ID' 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 'THIS.DOCUMENT_ID' is not a column in the target table.

          Show
          Knut Anders Hatlen added a comment - After the fix for DERBY-4380 (Subqueries not allowed in ON clause) the error message produced by d39.sql has changed to: ERROR 42X04: Column 'THIS.DOCUMENT_ID' 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 'THIS.DOCUMENT_ID' is not a column in the target table.
          Hide
          Andy Jefferson added a comment - - edited

          Yet another example,

          SELECT DISTINCT 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 = ?

          And the tables are

          CREATE TABLE JFIRETRADE_MODEOFPAYMENTFLAVOUR

          { MODEOFPAYMENTFLAVOURID VARCHAR(100) NOT NULL, ORGANISATIONID VARCHAR(100) NOT NULL, ICON16X16DATA BLOB(2147483647), MODEOFPAYMENT_MODEOFPAYMENTID_OID VARCHAR(100), MODEOFPAYMENT_ORGANISATIONID_OID VARCHAR(100), PRIMARYKEY VARCHAR(255) }

          CREATE TABLE JFIRETRADE_CUSTOMERGROUP

          { CUSTOMERGROUPID VARCHAR(100) NOT NULL, ORGANISATIONID VARCHAR(100) NOT NULL, PRIMARYKEY VARCHAR(255) }

          CREATE TABLE JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS

          { CUSTOMERGROUPID_OID VARCHAR(100) NOT NULL, ORGANISATIONID_OID VARCHAR(100) NOT NULL, "KEY" VARCHAR(255) NOT NULL, MODEOFPAYMENTFLAVOURID_VID VARCHAR(100), ORGANISATIONID_VID VARCHAR(100) }

          CREATE TABLE JFIRETRADE_MODEOFPAYMENTFLAVOURNAME

          { MODEOFPAYMENTFLAVOURID VARCHAR(100) NOT NULL, ORGANISATIONID VARCHAR(100) NOT NULL, MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID VARCHAR(100), MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID VARCHAR(100) }

          and the exception
          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)

          with Derby 10.4.2.0.

          Anyone know what exactly is wrong with that innocuous query ? and when Derby will finally be capable of running it ? Or alternatively tell me how i can reasonably adjust that query to get Derby to swallow it ? (obviously since the query is generated by an ORM here then there is no guarantee that any workaround will be acceptable, but I'll humour the idea)

          Show
          Andy Jefferson added a comment - - edited Yet another example, SELECT DISTINCT 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 = ? And the tables are CREATE TABLE JFIRETRADE_MODEOFPAYMENTFLAVOUR { MODEOFPAYMENTFLAVOURID VARCHAR(100) NOT NULL, ORGANISATIONID VARCHAR(100) NOT NULL, ICON16X16DATA BLOB(2147483647), MODEOFPAYMENT_MODEOFPAYMENTID_OID VARCHAR(100), MODEOFPAYMENT_ORGANISATIONID_OID VARCHAR(100), PRIMARYKEY VARCHAR(255) } CREATE TABLE JFIRETRADE_CUSTOMERGROUP { CUSTOMERGROUPID VARCHAR(100) NOT NULL, ORGANISATIONID VARCHAR(100) NOT NULL, PRIMARYKEY VARCHAR(255) } CREATE TABLE JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS { CUSTOMERGROUPID_OID VARCHAR(100) NOT NULL, ORGANISATIONID_OID VARCHAR(100) NOT NULL, "KEY" VARCHAR(255) NOT NULL, MODEOFPAYMENTFLAVOURID_VID VARCHAR(100), ORGANISATIONID_VID VARCHAR(100) } CREATE TABLE JFIRETRADE_MODEOFPAYMENTFLAVOURNAME { MODEOFPAYMENTFLAVOURID VARCHAR(100) NOT NULL, ORGANISATIONID VARCHAR(100) NOT NULL, MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID VARCHAR(100), MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID VARCHAR(100) } and the exception 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) with Derby 10.4.2.0. Anyone know what exactly is wrong with that innocuous query ? and when Derby will finally be capable of running it ? Or alternatively tell me how i can reasonably adjust that query to get Derby to swallow it ? (obviously since the query is generated by an ORM here then there is no guarantee that any workaround will be acceptable, but I'll humour the idea)
          Hide
          Knut Anders Hatlen added a comment -

          In 10.6, the error message will at least give some more details about what Derby thinks is wrong with the query:

          ERROR 42X04: Column 'A0.MODEOFPAYMENTFLAVOURID' 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.MODEOFPAYMENTFLAVOURID' is not a column in the target table.

          The problem with the query is that a column in the A0 table is referenced in an ON clause, even if A0 is not in any of the operands of that JOIN operator. I haven't checked whether or not that's allowed by the SQL standard, but Derby at least currently does not look outside the left and right operands of the JOIN when resolving column references in an ON clause.

          I think the problem can be worked around by only using JOIN operators in the FROM list. With Derby 10.6 you can just replace the comma with "CROSS JOIN". With 10.5 and earlier, you can use "INNER JOIN ... ON 1=1" instead, like this:

          SELECT DISTINCT A0.PRIMARYKEY
          FROM JFIRETRADE_MODEOFPAYMENTFLAVOUR A0 INNER JOIN JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP ON 1=1
          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 = ?

          Show
          Knut Anders Hatlen added a comment - In 10.6, the error message will at least give some more details about what Derby thinks is wrong with the query: ERROR 42X04: Column 'A0.MODEOFPAYMENTFLAVOURID' 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.MODEOFPAYMENTFLAVOURID' is not a column in the target table. The problem with the query is that a column in the A0 table is referenced in an ON clause, even if A0 is not in any of the operands of that JOIN operator. I haven't checked whether or not that's allowed by the SQL standard, but Derby at least currently does not look outside the left and right operands of the JOIN when resolving column references in an ON clause. I think the problem can be worked around by only using JOIN operators in the FROM list. With Derby 10.6 you can just replace the comma with "CROSS JOIN". With 10.5 and earlier, you can use "INNER JOIN ... ON 1=1" instead, like this: SELECT DISTINCT A0.PRIMARYKEY FROM JFIRETRADE_MODEOFPAYMENTFLAVOUR A0 INNER JOIN JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP ON 1=1 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 = ?
          Hide
          Andy Jefferson added a comment -

          Thx for your reply.
          Not sure I understood the "The problem ... a column in the A0 table is referenced in an ON clause" ... you mean "LEFT OUTER JOIN ... " is making use of joining to A0 ? Looks perfectly valid to me, joining to a FK of the primary table of the statement, and indeed works on all other RDBMS I've used.

          Where do I get hold of derby 10.6.0.0 bearing in mind it isn't yet released ? Nightly builds ?

          Show
          Andy Jefferson added a comment - Thx for your reply. Not sure I understood the "The problem ... a column in the A0 table is referenced in an ON clause" ... you mean "LEFT OUTER JOIN ... " is making use of joining to A0 ? Looks perfectly valid to me, joining to a FK of the primary table of the statement, and indeed works on all other RDBMS I've used. Where do I get hold of derby 10.6.0.0 bearing in mind it isn't yet released ? Nightly builds ?
          Hide
          Kristian Waagan added a comment -

          Hi Andy,

          Please do not use the release candidate in production, it is not an official release.
          The first RC is located at http://people.apache.org/~rhillegas/10.6.1.0/ (link/contents may disappear at any time )

          Show
          Kristian Waagan added a comment - Hi Andy, Please do not use the release candidate in production, it is not an official release. The first RC is located at http://people.apache.org/~rhillegas/10.6.1.0/ (link/contents may disappear at any time )
          Hide
          Andy Jefferson added a comment -

          Thx Kristian. That build changes the generated query (with DataNucleus 2.1.0.m2) to

          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
          CROSS JOIN JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP
          INNER JOIN JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS C0 ON VAR_CUSTOMERGROUP.CUSTOMERGROUPID = C0.CUSTOMERGROUPID_OID AND VAR_CUSTOMERGROUP.ORGANISATIONID = C0.ORGANISATIONID_OID
          LEFT OUTER JOIN JFIRETRADE_MODEOFPAYMENTFLAVOURNAME D0 ON A0.MODEOFPAYMENTFLAVOURID = D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID AND A0.ORGANISATIONID = D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID
          WHERE C0.MODEOFPAYMENTFLAVOURID_VID = A0.MODEOFPAYMENTFLAVOURID
          AND C0.ORGANISATIONID_VID = A0.ORGANISATIONID
          AND VAR_CUSTOMERGROUP.ORGANISATIONID = ?
          AND VAR_CUSTOMERGROUP.CUSTOMERGROUPID = ?

          and there is no longer an error about "JOIN ON clause", so the CROSS JOIN handling is seemingly the issue, not any use of A0. The error becomes
          java.sql.SQLException: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type.
          but that's my problem for selecting a BLOB field (A0.ICON16X16DATA) when using DISTINCT and easily fixed.

          Thx for your time. Look forward to a 10.6 release

          Show
          Andy Jefferson added a comment - Thx Kristian. That build changes the generated query (with DataNucleus 2.1.0.m2) to 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 CROSS JOIN JFIRETRADE_CUSTOMERGROUP VAR_CUSTOMERGROUP INNER JOIN JFIRETRADE_CUSTOMERGROUP_MODEOFPAYMENTFLAVOURS C0 ON VAR_CUSTOMERGROUP.CUSTOMERGROUPID = C0.CUSTOMERGROUPID_OID AND VAR_CUSTOMERGROUP.ORGANISATIONID = C0.ORGANISATIONID_OID LEFT OUTER JOIN JFIRETRADE_MODEOFPAYMENTFLAVOURNAME D0 ON A0.MODEOFPAYMENTFLAVOURID = D0.MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID AND A0.ORGANISATIONID = D0.MODEOFPAYMENTFLAVOUR_ORGANISATIONID_OID WHERE C0.MODEOFPAYMENTFLAVOURID_VID = A0.MODEOFPAYMENTFLAVOURID AND C0.ORGANISATIONID_VID = A0.ORGANISATIONID AND VAR_CUSTOMERGROUP.ORGANISATIONID = ? AND VAR_CUSTOMERGROUP.CUSTOMERGROUPID = ? and there is no longer an error about "JOIN ON clause", so the CROSS JOIN handling is seemingly the issue, not any use of A0. The error becomes java.sql.SQLException: Columns of type 'BLOB' may not be used in CREATE INDEX, ORDER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because comparisons are not supported for that type. but that's my problem for selecting a BLOB field (A0.ICON16X16DATA) when using DISTINCT and easily fixed. Thx for your time. Look forward to a 10.6 release
          Hide
          Knut Anders Hatlen added a comment -

          For the record, these queries fail on PostgreSQL and MySQL too. And, according to earlier comments, they also fail on DB2.

          Here's what PostgreSQL 8.4 says:

          > ERROR: invalid reference to FROM-clause entry for table "a0"
          > LINE 4: ...MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID = A0.MODEOFP...
          > ^
          > HINT: There is an entry for table "a0", but it cannot be referenced from this part of the query.

          And here's the error reported by MySQL 5.1:

          > ERROR 1054 (42S22): Unknown column 'A0.MODEOFPAYMENTFLAVOURID' in 'on clause'

          Show
          Knut Anders Hatlen added a comment - For the record, these queries fail on PostgreSQL and MySQL too. And, according to earlier comments, they also fail on DB2. Here's what PostgreSQL 8.4 says: > ERROR: invalid reference to FROM-clause entry for table "a0" > LINE 4: ...MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID = A0.MODEOFP... > ^ > HINT: There is an entry for table "a0", but it cannot be referenced from this part of the query. And here's the error reported by MySQL 5.1: > ERROR 1054 (42S22): Unknown column 'A0.MODEOFPAYMENTFLAVOURID' in 'on clause'
          Hide
          Knut Anders Hatlen added a comment -

          Since this is a very old bug report, no one has shown any interest in working on it, and no other databases that we know of accept the query, should we close it as won't fix?

          Show
          Knut Anders Hatlen added a comment - Since this is a very old bug report, no one has shown any interest in working on it, and no other databases that we know of accept the query, should we close it as won't fix?
          Hide
          Dag H. Wanvik added a comment -

          If you have checked that the standard doesn't allow this, I'm +1 to closing.

          Show
          Dag H. Wanvik added a comment - If you have checked that the standard doesn't allow this, I'm +1 to closing.
          Hide
          Knut Anders Hatlen added a comment -

          No, I haven't checked what the standard says. In case someone wants to have a look, here's a minimal repro that can be used to investigate the issue::

          ij> create table t(x int);
          0 rows inserted/updated/deleted
          ij> select * from t a, t b join t c on a.x=b.x;
          ERROR 42X04: Column 'A.X' 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 'A.X' is not a column in the target table.

          Show
          Knut Anders Hatlen added a comment - No, I haven't checked what the standard says. In case someone wants to have a look, here's a minimal repro that can be used to investigate the issue:: ij> create table t(x int); 0 rows inserted/updated/deleted ij> select * from t a, t b join t c on a.x=b.x; ERROR 42X04: Column 'A.X' 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 'A.X' is not a column in the target table.
          Hide
          Kathey Marsden added a comment -

          Unmarking High Value Fix as this query does not work with other databases and may not be SQL Standard. According to earlier comments, we may want to close won''t fix.

          Show
          Kathey Marsden added a comment - Unmarking High Value Fix as this query does not work with other databases and may not be SQL Standard. According to earlier comments, we may want to close won''t fix.
          Hide
          Knut Anders Hatlen added a comment -

          The scope of a <table factor> is defined in SQL:2003, part 2, section 7.6 <table reference>, syntax rule 5. My understanding of that rule is that A (in the minimal repro posted on 18/Feb/11) is not in scope in the <join condition> because A is not contained in the corresponding <joined table>, so the query is not standard SQL. I'll close the bug report as Won't Fix.

          Show
          Knut Anders Hatlen added a comment - The scope of a <table factor> is defined in SQL:2003, part 2, section 7.6 <table reference>, syntax rule 5. My understanding of that rule is that A (in the minimal repro posted on 18/Feb/11) is not in scope in the <join condition> because A is not contained in the corresponding <joined table>, so the query is not standard SQL. I'll close the bug report as Won't Fix.

            People

            • Assignee:
              Unassigned
              Reporter:
              Erik Bengtson
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development