JDO
  1. JDO
  2. JDO-435

Query with projected variables returns duplicates and extra results that do not satisfy filter

    Details

      Description

      org.apache.jdo.tck.query.result.VariableInResult fails with several queries that return variables that are mapped to one-many and many-many relationships. In some cases, the filter is not satisfied. In others, duplicate results are returned.

        Issue Links

          Activity

          Craig L Russell created issue -
          Erik Bengtson made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          Hide
          Erik Bengtson added a comment -

          Status with JPOX CVS:
          RUN VariableInResult.testNavigation
          RUN VariableInResult.testDistinctNavigation
          RUN VariableInResult.testDistinctNoNavigation
          RUN VariableInResult.testNoNavigation
          RUN VariableInResult.testMultipleProjectionWithConstraints FAILURE
          RUN VariableInResult.testMultipleProjection FAILURE
          RUN VariableInResult.testProjectionWithConstraints

          The checkQueryResultWithoutOrder method does not like the results for testMultipleProjectionWithConstrains but the order is not important and the test should pass.

          2) testMultipleProjectionWithConstraints(org.apache.jdo.tck.query.result.VariableInResult)junit.framework.AssertionFailedError: Assertion A14.6.9-3 (VariableInResult) failed:
          Wrong query result:
          query: SELECT e, p FROM org.apache.jdo.tck.pc.company.Department WHERE employees.contains(e) && e.projects.contains(p)&& p.name == 'orange' VARIABLES Employee e; Project p
          expected: [[
          FullTimeEmployee(1, emp1Last, emp1First, born 10/Jun/1970, phone

          {work=123456-1, home=1111}, hired 1/Jan/1999, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)], [
          FullTimeEmployee(2, emp2Last, emp2First, born 22/Dec/1975, phone {work=123456-2, home=2222}, hired 1/Jul/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)], [
          PartTimeEmployee(3, emp3Last, emp3First, born 5/Sep/1972, phone {work=123456-3, home=3333}, hired 15/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)]]
          got: [[
          PartTimeEmployee(3, emp3Last, emp3First, born 5/Sep/1972, phone {work=123456-3, home=3333}, hired 15/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)], [
          FullTimeEmployee(1, emp1Last, emp1First, born 10/Jun/1970, phone {work=123456-1, home=1111}

          , hired 1/Jan/1999, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)], [
          FullTimeEmployee(2, emp2Last, emp2First, born 22/Dec/1975, phone

          {work=123456-2, home=2222}

          , hired 1/Jul/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)]]
          at org.apache.jdo.tck.JDO_Test.fail(JDO_Test.java:638)
          at org.apache.jdo.tck.query.QueryTest.queryFailed(QueryTest.java:508)
          at org.apache.jdo.tck.query.QueryTest.checkQueryResultWithoutOrder(QueryTest.java:540)
          at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1229)
          at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1069)
          at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:1006)
          at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:986)
          at org.apache.jdo.tck.query.result.VariableInResult.testMultipleProjectionWithConstraints(VariableInResult.java:253)

          Show
          Erik Bengtson added a comment - Status with JPOX CVS: RUN VariableInResult.testNavigation RUN VariableInResult.testDistinctNavigation RUN VariableInResult.testDistinctNoNavigation RUN VariableInResult.testNoNavigation RUN VariableInResult.testMultipleProjectionWithConstraints FAILURE RUN VariableInResult.testMultipleProjection FAILURE RUN VariableInResult.testProjectionWithConstraints The checkQueryResultWithoutOrder method does not like the results for testMultipleProjectionWithConstrains but the order is not important and the test should pass. 2) testMultipleProjectionWithConstraints(org.apache.jdo.tck.query.result.VariableInResult)junit.framework.AssertionFailedError: Assertion A14.6.9-3 (VariableInResult) failed: Wrong query result: query: SELECT e, p FROM org.apache.jdo.tck.pc.company.Department WHERE employees.contains(e) && e.projects.contains(p)&& p.name == 'orange' VARIABLES Employee e; Project p expected: [[ FullTimeEmployee(1, emp1Last, emp1First, born 10/Jun/1970, phone {work=123456-1, home=1111}, hired 1/Jan/1999, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)], [ FullTimeEmployee(2, emp2Last, emp2First, born 22/Dec/1975, phone {work=123456-2, home=2222}, hired 1/Jul/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)], [ PartTimeEmployee(3, emp3Last, emp3First, born 5/Sep/1972, phone {work=123456-3, home=3333}, hired 15/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)]] got: [[ PartTimeEmployee(3, emp3Last, emp3First, born 5/Sep/1972, phone {work=123456-3, home=3333}, hired 15/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)], [ FullTimeEmployee(1, emp1Last, emp1First, born 10/Jun/1970, phone {work=123456-1, home=1111} , hired 1/Jan/1999, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)], [ FullTimeEmployee(2, emp2Last, emp2First, born 22/Dec/1975, phone {work=123456-2, home=2222} , hired 1/Jul/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)]] at org.apache.jdo.tck.JDO_Test.fail(JDO_Test.java:638) at org.apache.jdo.tck.query.QueryTest.queryFailed(QueryTest.java:508) at org.apache.jdo.tck.query.QueryTest.checkQueryResultWithoutOrder(QueryTest.java:540) at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1229) at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1069) at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:1006) at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:986) at org.apache.jdo.tck.query.result.VariableInResult.testMultipleProjectionWithConstraints(VariableInResult.java:253)
          Erik Bengtson made changes -
          Assignee Erik Bengtson [ ebengtso ] Craig Russell [ clr ]
          Hide
          Craig L Russell added a comment -

          The test case incorrectly set up the expected result as an Object[ ][ ] instead of Collection<Object[ ]>. This caused the failure which appeared to complain about a mismatch between equivalent objects.

          I also improved the reporting of mismatches between expected and actual results.

          Please check that the test case now succeeds with the JPOX CVS.
          Thanks.

          svn commit tck20/src/java/org/apache/jdo/tck/query/
          Sending src/java/org/apache/jdo/tck/query/QueryTest.java
          Sending src/java/org/apache/jdo/tck/query/result/VariableInResult.java
          Transmitting file data ..
          Committed revision 464011.

          Show
          Craig L Russell added a comment - The test case incorrectly set up the expected result as an Object[ ][ ] instead of Collection<Object[ ]>. This caused the failure which appeared to complain about a mismatch between equivalent objects. I also improved the reporting of mismatches between expected and actual results. Please check that the test case now succeeds with the JPOX CVS. Thanks. svn commit tck20/src/java/org/apache/jdo/tck/query/ Sending src/java/org/apache/jdo/tck/query/QueryTest.java Sending src/java/org/apache/jdo/tck/query/result/VariableInResult.java Transmitting file data .. Committed revision 464011.
          Craig L Russell made changes -
          Assignee Craig Russell [ clr ] Erik Bengtson [ ebengtso ]
          Hide
          Craig L Russell added a comment -

          With the latest JPOX SNAPSHOT from 4-Nov-2006 I still get one exception from this test:

          testMultipleProjection(org.apache.jdo.tck.query.result.VariableInResult)junit.framework.AssertionFailedError: Assertion A14.6.9-3 (VariableInResult) failed:
          [java] Wrong query result:
          [java] query: SELECT e, p FROM org.apache.jdo.tck.pc.company.Department WHERE employees.contains(e) && e.projects.contains(p) VARIABLES Employee e; Project p
          [java] expected: java.util.ArrayList of size 7
          [java] [
          [FullTimeEmployee(1, emp1Last, emp1First, born 9/Jun/1970, phone

          {work=123456-1, home=1111}, hired 31/Dec/1998, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)],
          [FullTimeEmployee(2, emp2Last, emp2First, born 21/Dec/1975, phone {work=123456-2, home=2222}, hired 30/Jun/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)],
          [PartTimeEmployee(3, emp3Last, emp3First, born 4/Sep/1972, phone {work=123456-3, home=3333}, hired 14/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)],
          [FullTimeEmployee(2, emp2Last, emp2First, born 21/Dec/1975, phone {work=123456-2, home=2222}, hired 30/Jun/2003, weeklyhours 40.0, $10000.0), Project(2, name blue, budget 50000.00)]
          ,
          [PartTimeEmployee(3, emp3Last, emp3First, born 4/Sep/1972, phone {work=123456-3, home=3333}, hired 14/Aug/2002, weeklyhours 19.0, $15.0), Project(2, name blue, budget 50000.00)],
          [PartTimeEmployee(4, emp4Last, emp4First, born 5/Sep/1973, phone {work=124456-3, home=3343}, hired 14/Apr/2001, weeklyhours 0.0, $13.0), Project(3, name green, budget 2000.99)],
          [FullTimeEmployee(5, emp5Last, emp5First, born 4/Jul/1962, phone {work=126456-3, home=3363}, hired 14/Aug/1998, weeklyhours 0.0, $45000.0), Project(3, name green, budget 2000.99)]]
          [java] got: java.util.ArrayList of size 5
          [java] [
          [FullTimeEmployee(5, emp5Last, emp5First, born 4/Jul/1962, phone {work=126456-3, home=3363}, hired 14/Aug/1998, weeklyhours 0.0, $45000.0), Project(3, name green, budget 2000.99)]
          ,
          [PartTimeEmployee(4, emp4Last, emp4First, born 5/Sep/1973, phone {work=124456-3, home=3343}, hired 14/Apr/2001, weeklyhours 0.0, $13.0), Project(3, name green, budget 2000.99)]
          ,
          [PartTimeEmployee(3, emp3Last, emp3First, born 4/Sep/1972, phone {work=123456-3, home=3333}, hired 14/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)]
          ,
          [FullTimeEmployee(1, emp1Last, emp1First, born 9/Jun/1970, phone {work=123456-1, home=1111}

          , hired 31/Dec/1998, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)],
          [FullTimeEmployee(2, emp2Last, emp2First, born 21/Dec/1975, phone

          {work=123456-2, home=2222}

          , hired 30/Jun/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)]]

          Show
          Craig L Russell added a comment - With the latest JPOX SNAPSHOT from 4-Nov-2006 I still get one exception from this test: testMultipleProjection(org.apache.jdo.tck.query.result.VariableInResult)junit.framework.AssertionFailedError: Assertion A14.6.9-3 (VariableInResult) failed: [java] Wrong query result: [java] query: SELECT e, p FROM org.apache.jdo.tck.pc.company.Department WHERE employees.contains(e) && e.projects.contains(p) VARIABLES Employee e; Project p [java] expected: java.util.ArrayList of size 7 [java] [ [FullTimeEmployee(1, emp1Last, emp1First, born 9/Jun/1970, phone {work=123456-1, home=1111}, hired 31/Dec/1998, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)], [FullTimeEmployee(2, emp2Last, emp2First, born 21/Dec/1975, phone {work=123456-2, home=2222}, hired 30/Jun/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)], [PartTimeEmployee(3, emp3Last, emp3First, born 4/Sep/1972, phone {work=123456-3, home=3333}, hired 14/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)] , [FullTimeEmployee(2, emp2Last, emp2First, born 21/Dec/1975, phone {work=123456-2, home=2222}, hired 30/Jun/2003, weeklyhours 40.0, $10000.0), Project(2, name blue, budget 50000.00)] , [PartTimeEmployee(3, emp3Last, emp3First, born 4/Sep/1972, phone {work=123456-3, home=3333}, hired 14/Aug/2002, weeklyhours 19.0, $15.0), Project(2, name blue, budget 50000.00)], [PartTimeEmployee(4, emp4Last, emp4First, born 5/Sep/1973, phone {work=124456-3, home=3343}, hired 14/Apr/2001, weeklyhours 0.0, $13.0), Project(3, name green, budget 2000.99)], [FullTimeEmployee(5, emp5Last, emp5First, born 4/Jul/1962, phone {work=126456-3, home=3363}, hired 14/Aug/1998, weeklyhours 0.0, $45000.0), Project(3, name green, budget 2000.99)]] [java] got: java.util.ArrayList of size 5 [java] [ [FullTimeEmployee(5, emp5Last, emp5First, born 4/Jul/1962, phone {work=126456-3, home=3363}, hired 14/Aug/1998, weeklyhours 0.0, $45000.0), Project(3, name green, budget 2000.99)] , [PartTimeEmployee(4, emp4Last, emp4First, born 5/Sep/1973, phone {work=124456-3, home=3343}, hired 14/Apr/2001, weeklyhours 0.0, $13.0), Project(3, name green, budget 2000.99)] , [PartTimeEmployee(3, emp3Last, emp3First, born 4/Sep/1972, phone {work=123456-3, home=3333}, hired 14/Aug/2002, weeklyhours 19.0, $15.0), Project(1, name orange, budget 2500000.99)] , [FullTimeEmployee(1, emp1Last, emp1First, born 9/Jun/1970, phone {work=123456-1, home=1111} , hired 31/Dec/1998, weeklyhours 40.0, $20000.0), Project(1, name orange, budget 2500000.99)], [FullTimeEmployee(2, emp2Last, emp2First, born 21/Dec/1975, phone {work=123456-2, home=2222} , hired 30/Jun/2003, weeklyhours 40.0, $10000.0), Project(1, name orange, budget 2500000.99)]]
          Hide
          Andy Jefferson added a comment -

          In case someone has time to look at this

          JDOQL Query
          SELECT e,p FROM org.apache.jdo.tck.pc.company.Department
          WHERE employees.contains(e) && e.projects.contains(p)
          VARIABLES Employee e; Project p

          SQL invoked
          SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID
          FROM applicationidentity0.DEPARTMENTS THIS,
          applicationidentity0.PERSONS UNBOUND_E,
          applicationidentity0.PROJECTS UNBOUND_P
          WHERE EXISTS (
          SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E
          WHERE EXISTS (
          SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_EMPLOYEES_E_PROJECTS_P
          WHERE THIS_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_EMPLOYEES_E.PERSONID
          AND THIS_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_EMPLOYEES_E.PERSONID
          AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
          AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID
          AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID
          AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID)
          )

          Show
          Andy Jefferson added a comment - In case someone has time to look at this JDOQL Query SELECT e,p FROM org.apache.jdo.tck.pc.company.Department WHERE employees.contains(e) && e.projects.contains(p) VARIABLES Employee e; Project p SQL invoked SELECT UNBOUND_E.PERSONID, UNBOUND_P.PROJID FROM applicationidentity0.DEPARTMENTS THIS, applicationidentity0.PERSONS UNBOUND_E, applicationidentity0.PROJECTS UNBOUND_P WHERE EXISTS ( SELECT 1 FROM applicationidentity0.PERSONS THIS_EMPLOYEES_E WHERE EXISTS ( SELECT 1 FROM applicationidentity0.PROJECT_MEMBER THIS_EMPLOYEES_E_PROJECTS_P WHERE THIS_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_EMPLOYEES_E.PERSONID AND THIS_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_EMPLOYEES_E.PERSONID AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID AND THIS_EMPLOYEES_E.DEPARTMENT = THIS.ID AND UNBOUND_P.PROJID = THIS_EMPLOYEES_E_PROJECTS_P.PROJID AND UNBOUND_E.PERSONID = THIS_EMPLOYEES_E.PERSONID) )
          Hide
          Andy Jefferson added a comment -

          Executing that statement on MySQL gives 7 rows (the right answer). On Derby it is seemingly giving 5.

          Show
          Andy Jefferson added a comment - Executing that statement on MySQL gives 7 rows (the right answer). On Derby it is seemingly giving 5.
          Michelle Caisse made changes -
          Link This issue depends on DERBY-3301 [ DERBY-3301 ]
          Hide
          Michelle Caisse added a comment -

          This appears to be a Derby bug

          Show
          Michelle Caisse added a comment - This appears to be a Derby bug
          Hide
          Craig L Russell added a comment -

          This appears to be a Derby bug.

          Show
          Craig L Russell added a comment - This appears to be a Derby bug.
          Craig L Russell made changes -
          Assignee Erik Bengtson [ ebengtso ]
          Hide
          Craig L Russell added a comment -

          I thought I'd see what happens if I add yet another navigational expression by starting with Company and navigating through departments and employees and projects. The result is an exception. I'm posting it here before looking into it in detail in case it yields either another Derby bug or a JPOX bug.

          There was 1 error:testMultipleProjection(org.apache.jdo.tck.query.result.VariableInResult)javax.jdo.JDOException: JDOException thrown while executing query:
          SELECT e, p FROM org.apache.jdo.tck.pc.company.Company WHERE departments.contains(d) && d.employees.contains(e) && e.projects.contains(p) VARIABLES Department d; Employee e; Project p

          NestedThrowablesStackTrace:
          [java] javax.jdo.JDODataStoreException: [JPOX-042007] Error executing JDOQL query "
          SELECT UNBOUND_E.DATASTORE_IDENTITY,UNBOUND_P.DATASTORE_IDENTITY
          FROM datastoreidentity0.COMPANIES THIS , datastoreidentity0.PERSONS UNBOUND_E , datastoreidentity0.PROJECTS UNBOUND_P
          WHERE EXISTS (
          SELECT 1 FROM datastoreidentity0.DEPARTMENTS THIS_DEPARTMENTS_D
          WHERE EXISTS (
          SELECT 1 FROM datastoreidentity0.PERSONS THIS_DEPARTMENTS_D_EMPLOYEES_E
          WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E.DEPARTMENT = THIS_DEPARTMENTS_D.DATASTORE_IDENTITY
          AND THIS_DEPARTMENTS_D.COMPANYID = THIS.DATASTORE_IDENTITY))
          AND EXISTS (
          SELECT 1 FROM datastoreidentity0.PROJECT_MEMBER THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P
          WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY
          AND UNBOUND_P.DATASTORE_IDENTITY = THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P.PROJID)

          " : Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' 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_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is not a column in the target table..

          Show
          Craig L Russell added a comment - I thought I'd see what happens if I add yet another navigational expression by starting with Company and navigating through departments and employees and projects. The result is an exception. I'm posting it here before looking into it in detail in case it yields either another Derby bug or a JPOX bug. There was 1 error:testMultipleProjection(org.apache.jdo.tck.query.result.VariableInResult)javax.jdo.JDOException: JDOException thrown while executing query: SELECT e, p FROM org.apache.jdo.tck.pc.company.Company WHERE departments.contains(d) && d.employees.contains(e) && e.projects.contains(p) VARIABLES Department d; Employee e; Project p NestedThrowablesStackTrace: [java] javax.jdo.JDODataStoreException: [JPOX-042007] Error executing JDOQL query " SELECT UNBOUND_E.DATASTORE_IDENTITY,UNBOUND_P.DATASTORE_IDENTITY FROM datastoreidentity0.COMPANIES THIS , datastoreidentity0.PERSONS UNBOUND_E , datastoreidentity0.PROJECTS UNBOUND_P WHERE EXISTS ( SELECT 1 FROM datastoreidentity0.DEPARTMENTS THIS_DEPARTMENTS_D WHERE EXISTS ( SELECT 1 FROM datastoreidentity0.PERSONS THIS_DEPARTMENTS_D_EMPLOYEES_E WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E.DEPARTMENT = THIS_DEPARTMENTS_D.DATASTORE_IDENTITY AND THIS_DEPARTMENTS_D.COMPANYID = THIS.DATASTORE_IDENTITY)) AND EXISTS ( SELECT 1 FROM datastoreidentity0.PROJECT_MEMBER THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY AND UNBOUND_P.DATASTORE_IDENTITY = THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P.PROJID) " : Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' 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_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is not a column in the target table..
          Hide
          Craig L Russell added a comment -

          > Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is either not in any table in the FROM list or ...

          This appears to be a bug in the JPOX SQL generation. The alias THIS_DEPARTMENTS_D_EMPLOYEES_E is in the FROM clause of the nested WHERE EXISTS but this clause is terminated by "))" before the final AND EXISTS clause.

          I'll raise another JIRA for this issue, since it appears to be unrelated to the current Derby issue.

          Show
          Craig L Russell added a comment - > Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is either not in any table in the FROM list or ... This appears to be a bug in the JPOX SQL generation. The alias THIS_DEPARTMENTS_D_EMPLOYEES_E is in the FROM clause of the nested WHERE EXISTS but this clause is terminated by "))" before the final AND EXISTS clause. I'll raise another JIRA for this issue, since it appears to be unrelated to the current Derby issue.
          Hide
          Andy Jefferson added a comment -

          >> Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is either not in any table in the FROM list or ...
          > This appears to be a bug in the JPOX SQL generation. The alias THIS_DEPARTMENTS_D_EMPLOYEES_E
          > is in the FROM clause of the nested WHERE EXISTS but this clause is terminated by "))" before the final AND
          > EXISTS clause.
          > I'll raise another JIRA for this issue, since it appears to be unrelated to the current Derby issue.

          Hi Craig,
          that particular SQL generation is a bug in JPOX but it isn't one that will be fixed before JPOX 1.2.0-final (estimated early March), and really requires Eriks' query refactoring as a prerequisite (which won't be done for a while I'd guess), so I suggest you raise an issue but for "Maintenance release 2", and don't add the related test to SVN til after "Maintenance release 1" is out. Thx

          Show
          Andy Jefferson added a comment - >> Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is either not in any table in the FROM list or ... > This appears to be a bug in the JPOX SQL generation. The alias THIS_DEPARTMENTS_D_EMPLOYEES_E > is in the FROM clause of the nested WHERE EXISTS but this clause is terminated by "))" before the final AND > EXISTS clause. > I'll raise another JIRA for this issue, since it appears to be unrelated to the current Derby issue. Hi Craig, that particular SQL generation is a bug in JPOX but it isn't one that will be fixed before JPOX 1.2.0-final (estimated early March), and really requires Eriks' query refactoring as a prerequisite (which won't be done for a while I'd guess), so I suggest you raise an issue but for "Maintenance release 2", and don't add the related test to SVN til after "Maintenance release 1" is out. Thx
          Hide
          Craig L Russell added a comment -

          The root cause of this issue is a bug in Derby that has a scheduled fix date of April 2008. The failing test methods will be moved to a separate test class that will be excluded for now. Other test methods involving multiple navigation will also be put into the new test case.

          Show
          Craig L Russell added a comment - The root cause of this issue is a bug in Derby that has a scheduled fix date of April 2008. The failing test methods will be moved to a separate test class that will be excluded for now. Other test methods involving multiple navigation will also be put into the new test case.
          Craig L Russell made changes -
          Fix Version/s JDO 2 maintenance release 1 [ 12310923 ]
          Assignee Craig Russell [ clr ]
          Craig L Russell made changes -
          Fix Version/s JDO 2 maintenance release 2 [ 12313070 ]
          Hide
          Craig L Russell added a comment -

          The latest release of Derby, 10.4.1.3, corrects the failure. But additional testing reveals that DataNucleus now generates incorrect SQL for some navigation projection queries.

          The error is [java] javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT THIS.DATASTORE_IDENTITY,UNBOUND_E.DATASTORE_IDENTITY,UNBOUND_P.DATASTORE_IDENTITY FROM datastoreidentity0.COMPANIES THIS , datastoreidentity0.PERSONS UNBOUND_E , datastoreidentity0.PROJECTS UNBOUND_P WHERE EXISTS (SELECT 1 FROM datastoreidentity0.DEPARTMENTS THIS_DEPARTMENTS_D WHERE EXISTS (SELECT 1 FROM datastoreidentity0.PERSONS THIS_DEPARTMENTS_D_EMPLOYEES_E WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E.DEPARTMENT = THIS_DEPARTMENTS_D.DATASTORE_IDENTITY AND THIS_DEPARTMENTS_D.COMPANYID = THIS.DATASTORE_IDENTITY AND THIS."NAME" = ?)) AND EXISTS (SELECT 1 FROM datastoreidentity0.PROJECT_MEMBER THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY AND UNBOUND_P.DATASTORE_IDENTITY = THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P.PROJID)" : Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' 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_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is not a column in the target table..

          Show
          Craig L Russell added a comment - The latest release of Derby, 10.4.1.3, corrects the failure. But additional testing reveals that DataNucleus now generates incorrect SQL for some navigation projection queries. The error is [java] javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT THIS.DATASTORE_IDENTITY,UNBOUND_E.DATASTORE_IDENTITY,UNBOUND_P.DATASTORE_IDENTITY FROM datastoreidentity0.COMPANIES THIS , datastoreidentity0.PERSONS UNBOUND_E , datastoreidentity0.PROJECTS UNBOUND_P WHERE EXISTS (SELECT 1 FROM datastoreidentity0.DEPARTMENTS THIS_DEPARTMENTS_D WHERE EXISTS (SELECT 1 FROM datastoreidentity0.PERSONS THIS_DEPARTMENTS_D_EMPLOYEES_E WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E.DEPARTMENT = THIS_DEPARTMENTS_D.DATASTORE_IDENTITY AND THIS_DEPARTMENTS_D.COMPANYID = THIS.DATASTORE_IDENTITY AND THIS."NAME" = ?)) AND EXISTS (SELECT 1 FROM datastoreidentity0.PROJECT_MEMBER THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P WHERE THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P."MEMBER" = THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY AND UNBOUND_P.DATASTORE_IDENTITY = THIS_DEPARTMENTS_D_EMPLOYEES_E_PROJECTS_P.PROJID)" : Column 'THIS_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' 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_DEPARTMENTS_D_EMPLOYEES_E.DATASTORE_IDENTITY' is not a column in the target table..
          Craig L Russell made changes -
          Assignee Craig Russell [ clr ] Andy Jefferson [ andy ]
          Craig L Russell made changes -
          Link This issue relates to JDO-573 [ JDO-573 ]
          Craig L Russell made changes -
          Link This issue blocks JDO-573 [ JDO-573 ]
          Craig L Russell made changes -
          Link This issue blocks JDO-573 [ JDO-573 ]
          Hide
          Craig L Russell added a comment -

          Sending tck2/src/conf/exclude.list
          Sending tck2/src/conf/query.conf
          Sending tck2-legacy/project.xml
          Sending tck2-legacy/src/conf/exclude.list
          Sending tck2-legacy/src/conf/query.conf
          Transmitting file data .....
          Committed revision 662062.

          Show
          Craig L Russell added a comment - Sending tck2/src/conf/exclude.list Sending tck2/src/conf/query.conf Sending tck2-legacy/project.xml Sending tck2-legacy/src/conf/exclude.list Sending tck2-legacy/src/conf/query.conf Transmitting file data ..... Committed revision 662062.
          Craig L Russell made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Craig L Russell made changes -
          Assignee Andy Jefferson [ andy ] Craig Russell [ clr ]
          Andy Jefferson made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Link This issue depends on DERBY-3301 [ DERBY-3301 ]
          Gavin made changes -
          Link This issue depends upon DERBY-3301 [ DERBY-3301 ]

            People

            • Assignee:
              Craig L Russell
              Reporter:
              Craig L Russell
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development