RI creates bad SQL for navigational queries

• Key: JDO-573
• Type: Bug
• Status: Open
• Priority: Major
• Assignee: Andy Jefferson
• Reporter: Craig Russell
• Component/s: tck2, tck2-legacy
• Affects Version/s: JDO 2 maintenance release 1, JDO 2 maintenance release 2

Description

The test org.apache.jdo.tck.query.result.VariableInResultNavigation fails with incorrect SQL generated.

    [java] 1) testNavigationWithThisAndCompany(org.apache.jdo.tck.query.result.VariableInResultNavigation)javax.jdo.JDOException: JDOException thrown while executing query:
    [java] SELECT this, e, p FROM org.apache.jdo.tck.pc.company.Company WHERE name == "Sun Microsystems, Inc." && departments.contains(d) && d.employees.contains(e) && e.projects.contains(p) VARIABLES Department d; Employee e; Project p
    [java] at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1314)
    [java] at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1135)
    [java] at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:1071)
    [java] at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:1051)
    [java] at org.apache.jdo.tck.query.result.VariableInResultNavigation.testNavigationWithThisAndCompany(VariableInResultNavigation.java:251)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [java] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    [java] at org.apache.jdo.tck.JDO_Test.runBare(JDO_Test.java:254)
    [java] at org.apache.jdo.tck.util.BatchTestRunner.doRun(BatchTestRunner.java:108)
    [java] at org.apache.jdo.tck.util.BatchTestRunner.start(BatchTestRunner.java:148)
    [java] at org.apache.jdo.tck.util.BatchTestRunner.main(BatchTestRunner.java:123)
    [java] NestedThrowablesStackTrace:
    [java] javax.jdo.JDODataStoreException: [JPOX-042007] 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..

Comments

[Craig Russell]

Since this is not expected to be fixed in time for JDO 2.1 release, the test will be put onto the exclude list.

[Craig Russell]

Kick the bug down the road to JDO 2.3.

The original failing test class, tck2/src/java/org/apache/jdo/tck/query/result/VariableInResultNavigation.java, has been updated to remove the failing test case, and a new test class, tck2/src/java/org/apache/jdo/tck/query/result/VariableInResultMultipleNavigation.java, has been added. The new test class is not listed in any configuration file.

[Andy Jefferson]

Is awaiting completion of replacement JDOQL implementation in DataNucleus; currently implemented large parts but is missing variables. Whether will be ready for JDO2.3 completion is not yet known

[Andy Jefferson]

Moving out of scope for 2.3 since uncertain timescales on new JDOQL impl; will be moved back in if in time - dependent on knowing what are the actual timescales for JDO2.3 of course