Incorrect result from query with nested EXIST

• Key: DERBY-3301
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Thomas Nielsen
• Reporter: Craig Russell
• Component/s: SQL
• Affects Version/s: 10.1.3.1, 10.2.1.6, 10.3.2.1
• Fix Version/s: 10.3.3.0, 10.4.1.3

Description

Derby returns unexpected results from a query with embedded EXIST clauses. The query SQL is generated by the JPOX jdo implementation and is supposed to return all of the PERSONS and PROJECTS where there is an entry in the join table. This query works as expected when using MySQL.

Here's the query:

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)
    );
PERSONID |PROJID
-----------------------
3 |1
5 |3
4 |3
2 |1
1 |1
5 rows selected

I'm expecting 7 rows to be returned here, one row for each row in the join table.

Here's the schema:
CREATE TABLE departments (
    ID INTEGER NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    EMP_OF_THE_MONTH INTEGER,
    COMPANYID INTEGER,
    DISCRIMINATOR VARCHAR(255),
    CONSTRAINT DEPTS_COMP_FK FOREIGN KEY (COMPANYID) 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 TIMESTAMP NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    HIREDATE TIMESTAMP,
    WEEKLYHOURS REAL,
    DEPARTMENT INTEGER,
    FUNDINGDEPT INTEGER,
    MANAGER INTEGER,
    MENTOR INTEGER,
    HRADVISOR INTEGER,
    SALARY REAL,
    WAGE REAL,
    DISCRIMINATOR varchar(255) NOT NULL,
    CONSTRAINT PERS_DEPT_FK FOREIGN KEY (DEPARTMENT) REFERENCES departments,
    CONSTRAINT PERS_FUNDDEPT_FK FOREIGN KEY (FUNDINGDEPT) REFERENCES departments,
    CONSTRAINT PERS_MANAGER_FK FOREIGN KEY (MANAGER) REFERENCES persons,
    CONSTRAINT PERS_MENTOR_FK FOREIGN KEY (MENTOR) REFERENCES persons,
    CONSTRAINT PERS_HRADVISOR_FK FOREIGN KEY (HRADVISOR) REFERENCES persons,
    CONSTRAINT EMPS_PK PRIMARY KEY (PERSONID)
);

CREATE TABLE projects (
    PROJID INTEGER NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    BUDGET DECIMAL(11,2) NOT NULL,
    DISCRIMINATOR VARCHAR(255),
    CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
);
CREATE TABLE project_member (
    PROJID INTEGER REFERENCES projects NOT NULL,
    MEMBER INTEGER REFERENCES persons NOT NULL
);

ij> connect 'jdbc:derby:/Users/clr/apache/jdo/trunk/tck2/target/database/derby/jdotckdb';
ij> set schema applicationidentity0;
0 rows inserted/updated/deleted
ij> select * from persons;
PERSONID |FIRSTNAME |LASTNAME |MIDDLENAME |BIRTHDATE |ADDRID |STREET |CITY |STA&|ZIPC&|COUNTRY |HIREDATE |WEEKLYHOURS |DEPARTMENT |FUNDINGDEPT|MANAGER |MENTOR |HRADVISOR |SALARY |WAGE |DISCRIMINATOR
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |emp1First |emp1Last |emp1Middle |1970-06-09 21:00:00.0 |NULL |NULL |NULL |NULL|NULL |NULL |1998-12-31 21:00:00.0 |40.0 |NULL |NULL |NULL |NULL |NULL |20000.0 |NULL |org.apache.jdo.tck.pc.company.FullTimeEmployee
2 |emp2First |emp2Last |emp2Middle |1975-12-21 21:00:00.0 |NULL |NULL |NULL |NULL|NULL |NULL |2003-06-30 21:00:00.0 |40.0 |NULL |NULL |NULL |NULL |NULL |10000.0 |NULL |org.apache.jdo.tck.pc.company.FullTimeEmployee
3 |emp3First |emp3Last |emp3Middle |1972-09-04 21:00:00.0 |NULL |NULL |NULL |NULL|NULL |NULL |2002-08-14 21:00:00.0 |19.0 |NULL |NULL |NULL |NULL |NULL |NULL |15.0 |org.apache.jdo.tck.pc.company.PartTimeEmployee
4 |emp4First |emp4Last |emp4Middle |1973-09-05 21:00:00.0 |NULL |NULL |NULL |NULL|NULL |NULL |2001-04-14 21:00:00.0 |0.0 |NULL |NULL |NULL |NULL |NULL |NULL |13.0 |org.apache.jdo.tck.pc.company.PartTimeEmployee
5 |emp5First |emp5Last |emp5Middle |1962-07-04 21:00:00.0 |NULL |NULL |NULL |NULL|NULL |NULL |1998-08-14 21:00:00.0 |0.0 |NULL |NULL |NULL |NULL |NULL |45000.0 |NULL |org.apache.jdo.tck.pc.company.FullTimeEmployee

5 rows selected
ij> select * from projects;
PROJID |NAME |BUDGET |DISCRIMINATOR
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |orange |2500000.99 |org.apache.jdo.tck.pc.company.Project
2 |blue |50000.00 |org.apache.jdo.tck.pc.company.Project
3 |green |2000.99 |org.apache.jdo.tck.pc.company.Project

3 rows selected
ij> select * from project_member;
PROJID |MEMBER
-----------------------
2 |3
1 |3
2 |2
3 |5
3 |4
1 |2
1 |1

7 rows selected