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

wrong query result in presence of a unique index

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.4.1.3
    • Fix Version/s: 10.3.3.0, 10.4.1.3
    • Component/s: SQL
    • Labels:
      None
    • Bug behavior facts:
      Regression

      Description

      The DDL to reproduce the bug is:

      CREATE TABLE tab_a (PId BIGINT NOT NULL);

      CREATE TABLE tab_c (Id BIGINT NOT NULL PRIMARY KEY, PAId BIGINT NOT NULL, PBId BIGINT NOT NULL);

      INSERT INTO tab_c VALUES (91, 81, 82);
      INSERT INTO tab_c VALUES (92, 81, 84);
      INSERT INTO tab_c VALUES (93, 81, 88);
      INSERT INTO tab_c VALUES (96, 81, 83);

      CREATE TABLE tab_v (OId BIGINT NOT NULL , UGId BIGINT NOT NULL, val CHAR(1) NOT NULL);
      CREATE UNIQUE INDEX tab_v_i1 ON tab_v (OId, UGId, val);
      CREATE INDEX tab_v_i2 ON tab_v (UGId, val, OId);

      INSERT INTO tab_v VALUES (81, 31, 'A');
      INSERT INTO tab_v VALUES (82, 31, 'A');
      INSERT INTO tab_v VALUES (83, 31, 'A');
      INSERT INTO tab_v VALUES (84, 31, 'A');
      INSERT INTO tab_v VALUES (85, 31, 'A');
      INSERT INTO tab_v VALUES (86, 31, 'A');
      INSERT INTO tab_v VALUES (87, 31, 'A');
      INSERT INTO tab_v VALUES (81, 32, 'A');
      INSERT INTO tab_v VALUES (82, 32, 'A');
      INSERT INTO tab_v VALUES (83, 32, 'A');
      INSERT INTO tab_v VALUES (84, 32, 'A');
      INSERT INTO tab_v VALUES (85, 32, 'A');
      INSERT INTO tab_v VALUES (86, 32, 'A');
      INSERT INTO tab_v VALUES (87, 32, 'A');

      CREATE TABLE tab_b (Id BIGINT NOT NULL PRIMARY KEY, OId BIGINT NOT NULL);

      INSERT INTO tab_b VALUES (141, 81);
      INSERT INTO tab_b VALUES (142, 82);
      INSERT INTO tab_b VALUES (143, 84);
      INSERT INTO tab_b VALUES (144, 88);
      INSERT INTO tab_b VALUES (151, 81);
      INSERT INTO tab_b VALUES (152, 83);

      CREATE TABLE tab_d (Id BIGINT NOT NULL PRIMARY KEY, PAId BIGINT NOT NULL, PBId BIGINT NOT NULL);

      INSERT INTO tab_d VALUES (181, 141, 142);
      INSERT INTO tab_d VALUES (182, 141, 143);
      INSERT INTO tab_d VALUES (186, 151, 152);

      The query returning the wrong result is:

      SELECT tab_b.Id
      FROM tab_b JOIN tab_c ON (tab_b.OId = tab_c.PAId OR tab_b.OId = tab_c.PBId)
      LEFT OUTER JOIN tab_a ON tab_b.OId = PId
      WHERE EXISTS (SELECT 'X' FROM tab_d WHERE (PAId = 141 AND PBId = tab_b.Id) OR (PBId = 141 AND PAId = tab_b.Id))
      AND EXISTS (SELECT 'X' FROM tab_v WHERE OId = tab_b.OId AND UGId = 31 AND val = 'A')

      The result should consist of two rows (142),(143), but it returns only one row (142).

      The correct result would be returned if the index tab_v_i1 had been created as non-unique.

      The correct result would also be returned if the condition ...AND val='A' had been replaced by ...AND val='A' || ''.

        Attachments

        1. releaseNote.html
          6 kB
          A B
        2. DERBY-3288.htm
          39 kB
          A B
        3. d3288_v2.patch
          33 kB
          A B
        4. d3288_incomplete_v1.patch
          2 kB
          A B
        5. d3288_10_3_merge.patch
          33 kB
          A B

          Issue Links

            Activity

              People

              • Assignee:
                army A B
                Reporter:
                gekhin Gerald Khin
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: