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

• Key: JDO-435
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Craig Russell
• Reporter: Craig Russell
• Component/s: tck2
• Affects Version/s: JDO 2 final
• Fix Version/s: JDO 2 maintenance release 2

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.

Comments

[Erik Bengtson]

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)

[Craig Russell]

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 Russell]

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)]]

[Andy Jefferson]

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)
    )

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

[Michelle Caisse]

This appears to be a Derby bug

[Craig Russell]

This appears to be a Derby bug.

[Craig Russell]

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..

[Craig Russell]

> 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.

[Andy Jefferson]

>> 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

[Craig Russell]

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 Russell]

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 Russell]

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.