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

[BP MB-39684] Intersect Scan under inner of nested-loop join may produce wrong results

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.5.1
    • Fix Version/s: 6.6.1
    • Component/s: query
    • Security Level: Public
    • Triage:
      Untriaged
    • Operating System:
      Centos 64-bit
    • Story Points:
      1
    • Is this a Regression?:
      Unknown

      Description

      Untar the attachments, use following command to load data into two buckets KS1 and KS2:

      cbdocloader -c couchbase://127.0.0.1 -u Administrator -p password -m 100 -b KS1 -d ./KS1json
      cbdocloader -c couchbase://127.0.0.1 -u Administrator -p password  -m 100 -b KS2 -d ./KS2json
      

      Then create the following indexes:

      create index rand_idx_KS1 on KS1(rand_i);
      create index uniq_idx_KS2 on KS2(uniq_i);
      create index zipf_idx_KS2 on KS2(zipf_i);
      

      Then run the following query in a loop:

      select count(y.zipf_i) from KS1 x USE INDEX(rand_idx_KS1) join KS2 y on x.rand_i <= 16384 and x.uniq_i = y.uniq_i;
      

      The query should return a count of 1000, however when run repeatedly, it sometimes produces a count that's less than 1000 (early termination). The query uses intersect scan on KS2 (or y) under nested-loop join.

        Attachments

        1. KS1.tar
          1.47 MB
        2. KS2.tar
          2.93 MB

          Issue Links

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

            Activity

            Hide
            build-team Couchbase Build Team added a comment -

            Build couchbase-server-6.6.1-9155 contains query commit c42fb95 with commit message:
            MB-41605 Intersect scan under inner of nested-loop join may produce wrong results

            Show
            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.1-9155 contains query commit c42fb95 with commit message: MB-41605 Intersect scan under inner of nested-loop join may produce wrong results
            Hide
            pierre.regazzoni Pierre Regazzoni added a comment -

            Ran repro on build 6.6.1-9154 and 6.6.1-9155.

            Problem repro on 9154 where some time output will not be 1000 as expected:

            defect-0.out-    1000
            defect-10.out-    1000
            defect-11.out-    1000
            defect-12.out-    1000
            defect-13.out-    1000
            defect-14.out-    1000
            defect-15.out-    1000
            defect-16.out-    1000
            defect-17.out-    1000
            defect-18.out-    1000
            defect-19.out-    666
            defect-1.out-    173
            defect-20.out-    1000
            defect-21.out-    412
            defect-22.out-    1000
            defect-23.out-    1000
            defect-24.out-    1000
            defect-25.out-    1000
            defect-26.out-    1000
            defect-27.out-    1000
            defect-28.out-    1000
            defect-29.out-    1000
            defect-2.out-    1000
            defect-30.out-    1000
            defect-31.out-    1000
            defect-32.out-    1000
            defect-33.out-    581
            defect-34.out-    1000
            defect-35.out-    265
            defect-36.out-    1000
            defect-37.out-    1000
            defect-38.out-    1000
            defect-39.out-    1000
            defect-3.out-    1000
            defect-40.out-    1000
            defect-41.out-    1000
            defect-42.out-    1000
            defect-43.out-    1000
            defect-44.out-    1000
            defect-45.out-    1000
            defect-46.out-    1000
            defect-47.out-    1000
            defect-48.out-    1000
            defect-49.out-    1000
            defect-4.out-    1000
            defect-5.out-    25
            defect-6.out-    1000
            defect-7.out-    1
            defect-8.out-    1000
            defect-9.out-    622
            

            on build 9155, run was consistently returning expected 1000 count:

            defect-10.out-    1000
            defect-11.out-    1000
            defect-12.out-    1000
            defect-13.out-    1000
            defect-14.out-    1000
            defect-15.out-    1000
            defect-16.out-    1000
            defect-17.out-    1000
            defect-18.out-    1000
            defect-19.out-    1000
            defect-1.out-    1000
            defect-20.out-    1000
            defect-21.out-    1000
            defect-22.out-    1000
            defect-23.out-    1000
            defect-24.out-    1000
            defect-25.out-    1000
            defect-26.out-    1000
            defect-27.out-    1000
            defect-28.out-    1000
            defect-29.out-    1000
            defect-2.out-    1000
            defect-30.out-    1000
            defect-31.out-    1000
            defect-32.out-    1000
            defect-33.out-    1000
            defect-34.out-    1000
            defect-35.out-    1000
            defect-36.out-    1000
            defect-37.out-    1000
            defect-38.out-    1000
            defect-39.out-    1000
            defect-3.out-    1000
            defect-40.out-    1000
            defect-41.out-    1000
            defect-42.out-    1000
            defect-43.out-    1000
            defect-44.out-    1000
            defect-45.out-    1000
            defect-46.out-    1000
            defect-47.out-    1000
            defect-48.out-    1000
            defect-49.out-    1000
            defect-4.out-    1000
            defect-5.out-    1000
            defect-6.out-    1000
            defect-7.out-    1000
            defect-8.out-    1000
            defect-9.out-    1000
            

            Show
            pierre.regazzoni Pierre Regazzoni added a comment - Ran repro on build 6.6.1-9154 and 6.6.1-9155. Problem repro on 9154 where some time output will not be 1000 as expected: defect-0.out- 1000 defect-10.out- 1000 defect-11.out- 1000 defect-12.out- 1000 defect-13.out- 1000 defect-14.out- 1000 defect-15.out- 1000 defect-16.out- 1000 defect-17.out- 1000 defect-18.out- 1000 defect-19.out- 666 defect-1.out- 173 defect-20.out- 1000 defect-21.out- 412 defect-22.out- 1000 defect-23.out- 1000 defect-24.out- 1000 defect-25.out- 1000 defect-26.out- 1000 defect-27.out- 1000 defect-28.out- 1000 defect-29.out- 1000 defect-2.out- 1000 defect-30.out- 1000 defect-31.out- 1000 defect-32.out- 1000 defect-33.out- 581 defect-34.out- 1000 defect-35.out- 265 defect-36.out- 1000 defect-37.out- 1000 defect-38.out- 1000 defect-39.out- 1000 defect-3.out- 1000 defect-40.out- 1000 defect-41.out- 1000 defect-42.out- 1000 defect-43.out- 1000 defect-44.out- 1000 defect-45.out- 1000 defect-46.out- 1000 defect-47.out- 1000 defect-48.out- 1000 defect-49.out- 1000 defect-4.out- 1000 defect-5.out- 25 defect-6.out- 1000 defect-7.out- 1 defect-8.out- 1000 defect-9.out- 622 on build 9155, run was consistently returning expected 1000 count: defect-10.out- 1000 defect-11.out- 1000 defect-12.out- 1000 defect-13.out- 1000 defect-14.out- 1000 defect-15.out- 1000 defect-16.out- 1000 defect-17.out- 1000 defect-18.out- 1000 defect-19.out- 1000 defect-1.out- 1000 defect-20.out- 1000 defect-21.out- 1000 defect-22.out- 1000 defect-23.out- 1000 defect-24.out- 1000 defect-25.out- 1000 defect-26.out- 1000 defect-27.out- 1000 defect-28.out- 1000 defect-29.out- 1000 defect-2.out- 1000 defect-30.out- 1000 defect-31.out- 1000 defect-32.out- 1000 defect-33.out- 1000 defect-34.out- 1000 defect-35.out- 1000 defect-36.out- 1000 defect-37.out- 1000 defect-38.out- 1000 defect-39.out- 1000 defect-3.out- 1000 defect-40.out- 1000 defect-41.out- 1000 defect-42.out- 1000 defect-43.out- 1000 defect-44.out- 1000 defect-45.out- 1000 defect-46.out- 1000 defect-47.out- 1000 defect-48.out- 1000 defect-49.out- 1000 defect-4.out- 1000 defect-5.out- 1000 defect-6.out- 1000 defect-7.out- 1000 defect-8.out- 1000 defect-9.out- 1000

              People

              Assignee:
              pierre.regazzoni Pierre Regazzoni
              Reporter:
              bingjie.miao Bingjie Miao
              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