Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-59538

[BP to 7.1.6-MP1 & 7.2.3-MP1]Nested loop join may give wrong results when inner references parent

    XMLWordPrintable

Details

    • Untriaged
    • 0
    • Unknown

    Description

       

      UPSERT INTO default (KEY k, VALUE v) SELECT "k00"||TO_STR(d) AS k, {"c1":d, "c2":d, "c3":d} AS v FROM ARRAY_RANGE(1,10) AS d;
      CREATE INDEX ix1 ON default(c1,c2, c3);
       
      CREATE OR REPLACE FUNCTION f11(a) {
      ( SELECT l, r
      FROM default AS l
      JOIN default AS r USE NL ON l.c3=r.c3
      WHERE l.c1 > 0 AND r.c1 > 0 AND r.c2 = a)
      };
       
      CREATE OR REPLACE FUNCTION f12(a) {
      ( SELECT l, r
      FROM default AS l
      JOIN default AS r  ON l.c3=r.c3
      WHERE l.c1 > 0 AND r.c1 > 0 AND r.c2 = a)
      };
       
      SELECT f11(t.c1) FROM default AS t
      WHERE t.c1 > 0;
       
      SELECT f12(t.c1) FROM default AS t
      WHERE t.c1 > 0;
       
      

       

      f12() uses l inner side and gives right results

      f11() uses l outer side and r innerside. Inner side of NL we don't pass any parent, any non table filters applied in the NL. 

      In case of UDF a is sort of parameter and consider as static/constant so it pushed as table filter here with out parent value not available and causes rows elminated

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              pierre.regazzoni Pierre Regazzoni
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty