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

Left outer join reassociation rewrite gives wrong result

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
    • Fix Version/s: 10.6.2.1, 10.7.1.1
    • Component/s: SQL
    • Bug behavior facts:
      Wrong query result

      Description

      The following script and output shows the problem:

      > create table r(c1 char(1));
      > create table s(c1 char(1), c2 char(1));
      > create table t(c1 char(1));

      > insert into r values 'a';
      > insert into s values ('b', default);
      > insert into t values ('c');

      > select * from s left outer join t on s.c2=t.c1 or s.c2 is null;

      C1 |C2 |C1
      --------------
      b |NULL|c

      > select * from r left outer join s on r.c1=s.c1;
      C1 |C1 |C2
      --------------
      a |NULL|NULL

      > select * from (r left outer join s on r.c1=s.c1) left outer join t on s.c2=t.c1 or s.c2 is null;

      C1 |C1 |C2 |C1
      -------------------
      a |NULL|NULL|c

      > select * from r left outer join (s left outer join t on s.c2=t.c1 or s.c2 is null) on r.c1=s.c1;

      C1 |C1 |C2 |C1
      -------------------
      a |NULL|NULL|c

      The last result is wrong. The correct answer should be:

      C1 |C1 |C2 |C1
      -------------------
      a |NULL|NULL|NULL

      since in the last form, the left table r has the value 'a', which does
      not match any row in result of the compound inner given the join
      predicate ("r.c1=s.c1"), so all nulls should be appended to the 'a'
      from the outer table r.

      This happens because internally the last form is rewritten to the
      second but the last form (left-deep), but this rewrite is not
      justified here unless the join predicate on s rejects null, which the
      present one explicitly does not ("or s.c2 is null"). Cf. for example
      [1], page 52, which describes this transform and its prerequisite
      condition as indentity #7.

      [1] Galindo-Legaria, C. & Rosenthal, A.: "Outerjoin simplification and
      reordering for query optimization", ACM Transactions on Database
      Systems, Vol 22, No 1, March 1997.

        Attachments

        1. derby-4471-1d.stat
          0.6 kB
          Dag H. Wanvik
        2. derby-4471-1d.diff
          2.06 MB
          Dag H. Wanvik
        3. derby-4471-1c.stat
          0.7 kB
          Dag H. Wanvik
        4. derby-4471-1c.diff
          2.06 MB
          Dag H. Wanvik
        5. derby-4471-1b.stat
          0.2 kB
          Dag H. Wanvik
        6. derby-4471-1b.diff
          103 kB
          Dag H. Wanvik
        7. derby-4471-1a.stat
          0.2 kB
          Dag H. Wanvik
        8. derby-4471-1a.diff
          96 kB
          Dag H. Wanvik
        9. query_plan_derby_4471.pdf
          77 kB
          Nirmal Fernando
        10. derby-4471-junit-repro.diff
          4 kB
          Dag H. Wanvik

          Issue Links

            Activity

              People

              • Assignee:
                dagw Dag H. Wanvik
                Reporter:
                dagw Dag H. Wanvik
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: