Derby
  1. Derby
  2. DERBY-3301

Incorrect result from query with nested EXIST

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • 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
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Urgent
    • Issue & fix info:
      Release Note Needed

      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

      1. releaseNote.html
        4 kB
        Craig L Russell
      2. releaseNote.html
        4 kB
        Thomas Nielsen
      3. derby-3301-test-master-3.stat
        0.2 kB
        Thomas Nielsen
      4. derby-3301-test-master-3.diff
        3 kB
        Thomas Nielsen
      5. derby-3301-test-master-2.diff
        14 kB
        Thomas Nielsen
      6. derby-3301-test-master.stat
        0.1 kB
        Thomas Nielsen
      7. derby-3301-test-master.diff
        7 kB
        Thomas Nielsen
      8. derby-3301-test-3.stat
        0.2 kB
        Thomas Nielsen
      9. derby-3301-test-3.diff
        9 kB
        Thomas Nielsen
      10. derby-3301-test-2.diff
        9 kB
        Thomas Nielsen
      11. derby-3301-test-1.stat
        0.2 kB
        Thomas Nielsen
      12. derby-3301-test-1.diff
        8 kB
        Thomas Nielsen
      13. derby-3301-extra.sql
        2 kB
        Thomas Nielsen
      14. derby-3301-8.diff
        7 kB
        Thomas Nielsen
      15. derby-3301-7.diff
        7 kB
        Thomas Nielsen
      16. derby-3301-6.diff
        7 kB
        Thomas Nielsen
      17. derby-3301-5.diff
        7 kB
        Thomas Nielsen
      18. derby-3301-4c.diff
        7 kB
        Thomas Nielsen
      19. derby-3301-4b.stat
        0.2 kB
        Thomas Nielsen
      20. derby-3301-4b.diff
        7 kB
        Thomas Nielsen
      21. derby-3301-4.diff
        7 kB
        Thomas Nielsen
      22. derby-3301-3b.diff
        6 kB
        Thomas Nielsen
      23. derby-3301-3.diff
        6 kB
        Thomas Nielsen
      24. derby-3301-2.diff
        5 kB
        Thomas Nielsen
      25. derby-3301-1.stat
        0.2 kB
        Thomas Nielsen
      26. derby-3301-1.diff
        4 kB
        Thomas Nielsen
      27. derby-3301.sql
        2 kB
        Craig L Russell
      28. Derby-3301.html
        4 kB
        Craig L Russell
      29. d3301-queryplan.log
        8 kB
        Thomas Nielsen

        Issue Links

          Activity

          Craig L Russell created issue -
          A B made changes -
          Field Original Value New Value
          Link This issue is part of DERBY-2034 [ DERBY-2034 ]
          Craig L Russell made changes -
          Affects Version/s 10.2.1.6 [ 11187 ]
          Affects Version/s 10.3.2.1 [ 12312876 ]
          Fix Version/s 10.2.1.6 [ 11187 ]
          Craig L Russell made changes -
          Affects Version/s 10.1.3.1 [ 12311953 ]
          Craig L Russell made changes -
          Attachment derby-3301.sql [ 12372638 ]
          Michelle Caisse made changes -
          Link This issue blocks JDO-435 [ JDO-435 ]
          Jørgen Løland made changes -
          Link This issue is related to DERBY-3321 [ DERBY-3321 ]
          Thomas Nielsen made changes -
          Attachment d3301-queryplan.log [ 12373496 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-1.diff [ 12373726 ]
          Attachment derby-3301-1.stat [ 12373727 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-2.diff [ 12373812 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-3.diff [ 12373869 ]
          Thomas Nielsen made changes -
          Assignee Thomas Nielsen [ thomanie ]
          Derby Info [Existing Application Impact] [Patch Available, Existing Application Impact]
          Thomas Nielsen made changes -
          Attachment derby-3301-3b.diff [ 12373872 ]
          Thomas Nielsen made changes -
          Link This issue relates to DERBY-3349 [ DERBY-3349 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-1.stat [ 12373942 ]
          Attachment derby-3301-test-1.diff [ 12373943 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-4.diff [ 12374037 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-4b.stat [ 12374128 ]
          Attachment derby-3301-4b.diff [ 12374127 ]
          Attachment derby-3301-test-2.diff [ 12374129 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-4c.diff [ 12374199 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-3.stat [ 12374208 ]
          Attachment derby-3301-test-3.diff [ 12374207 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-3.diff [ 12374207 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-3.diff [ 12374216 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-extra.sql [ 12374405 ]
          Attachment derby-3301-5.diff [ 12374404 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-master.diff [ 12374412 ]
          Attachment derby-3301-test-master.stat [ 12374413 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-extra.sql [ 12374405 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-extra.sql [ 12374448 ]
          Attachment derby-3301-6.diff [ 12374449 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-7.diff [ 12374491 ]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-master-2.diff [ 12374505 ]
          Attachment derby-3301-8.diff [ 12374504 ]
          Dyre Tjeldvoll made changes -
          Derby Info [Existing Application Impact, Patch Available] [Existing Application Impact]
          Thomas Nielsen made changes -
          Attachment derby-3301-test-master-3.stat [ 12375011 ]
          Attachment derby-3301-test-master-3.diff [ 12375010 ]
          Thomas Nielsen made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Fix Version/s 10.4.0.0 [ 12312540 ]
          Craig L Russell made changes -
          Attachment Derby-3301.html [ 12378272 ]
          Craig L Russell made changes -
          Attachment releaseNote.html [ 12378274 ]
          Thomas Nielsen made changes -
          Attachment releaseNote.html [ 12378552 ]
          Dyre Tjeldvoll made changes -
          Derby Info [Existing Application Impact] [Existing Application Impact, Release Note Needed]
          Kathey Marsden made changes -
          Derby Info [Release Note Needed, Existing Application Impact] [Existing Application Impact, Release Note Needed]
          Fix Version/s 10.3.2.2 [ 12312885 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Release Note Needed, Existing Application Impact] [Release Note Needed]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-4585 [ DERBY-4585 ]
          Kathey Marsden made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Link This issue blocks JDO-435 [ JDO-435 ]
          Gavin made changes -
          Link This issue is depended upon by JDO-435 [ JDO-435 ]
          Gavin made changes -
          Workflow jira [ 12420166 ] Default workflow, editable Closed status [ 12802323 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development