Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4063

Constraint causes wrong query result when using exists

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.2.0
    • Fix Version/s: 10.3.3.1, 10.4.2.1, 10.5.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Running Sun JVM 1.6.0_10
    • Issue & fix info:
      High Value Fix
    • Bug behavior facts:
      Regression, Wrong query result

      Description

      Enabling the primary key constraint yields different results in an exists sub-select.

      The select statement below will return the values 1 and 3 when the primary key constraint is disabled in the project table (project_pk).
      When the constraint is enabled, the same query returns nothing.

      Another interesting effect on the result can be observed when the criteria "AND prj.other = 100" is enabled
      in the join clause and when the constraint is enabled.

      drop table child;
      drop table parent;
      drop table project;

      CREATE TABLE project (id INT NOT NULL, other INT NOT NULL
      --,CONSTRAINT project_pk PRIMARY KEY (id)
      );
      CREATE TABLE parent (id INT NOT NULL, project_id INT NOT NULL);
      CREATE TABLE child (id INT NOT NULL, parent_id INT NOT NULL);

      insert into project (id, other) values(50,100);
      insert into parent(id, project_id) values (10,50);
      insert into parent(id, project_id) values (20,50);
      insert into child(id, parent_id) values(1,10);
      insert into child(id, parent_id) values(2,20);
      insert into child(id, parent_id) values(3,20);

      SELECT c0.id
      FROM child c0
      WHERE EXISTS (
      SELECT MAX(c1.id)
      FROM child c1
      JOIN parent p ON p.id = c1.parent_id
      JOIN project prj ON prj.id = p.project_id
      --AND prj.other = 100
      GROUP BY c1.parent_id
      HAVING MAX(c1.id) = c0.id
      );

        Attachments

        1. noconst_nocond.txt
          8 kB
          Lars Gråmark
        2. withconst_nocond.txt
          8 kB
          Lars Gråmark
        3. withconst_withcond.txt
          8 kB
          Lars Gråmark

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                gramark Lars Gråmark
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: