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

Constraint causes wrong query result when using exists

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.4.2.0
    • 10.3.3.1, 10.4.2.1, 10.5.1.1
    • SQL
    • None
    • Running Sun JVM 1.6.0_10
    • High Value Fix
    • 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

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

              Dates

                Created:
                Updated:
                Resolved: