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

Disable covering when arraykey spans has UNKOWNS

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 7.0.0, 7.1.0, 6.6.5
    • 7.1.0
    • query
    • Untriaged
    • 1
    • Unknown

    Description

      Repro:

      UPSERT INTO default VALUES("k001", {"a":1});
      UPSERT INTO default VALUES("k002", {"a":1, "reviews":1});
       SELECT META().id FROM default d USE INDEX (ix1) WHERE d.a = 1 AND ANY v IN d.reviews SATISFIES v.x IS NULL END; -- returns k002
       
       SELECT META().id FROM default d USE INDEX (ix1) WHERE d.a = 1 AND ANY v IN d.reviews SATISFIES v.x IS MISSING END; -- returns k001
      

      Try with primary index gives none

      array index key doesn't know if reviews is null or v.x is null false positives. We can't rely on the index key value.

      Attachments

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

        Activity

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

          See if these documents and how those indexed. And Write queries, ANY, UNNEST verify with primary index. Test corner cases

          UPSERT INTO default VALUES("k001", {"a":1});
          UPSERT INTO default VALUES("k002", {"a":1, "reviews":NULL});
          UPSERT INTO default VALUES("k003", {"a":1, "reviews":1});
          UPSERT INTO default VALUES("k004", {"a":1, "reviews":"abc"});
          UPSERT INTO default VALUES("ko01", {"a":1, "reviews":{"x":1, "y":2, "z":3}});
          UPSERT INTO default VALUES("ko02", {"a":1, "reviews":{"xx":1, "y":3, "z":3}});
          UPSERT INTO default VALUES("ko03", {"a":1, "reviews":{"x":1, "yy":3, "z":3}});
          UPSERT INTO default VALUES("ka04", {"a":1, "reviews":{"xx":1, "y":3, "z":3}});
          UPSERT INTO default VALUES("ka01", {"a":1, "reviews":[]});
          UPSERT INTO default VALUES("ka02", {"a":1, "reviews":[{"xx":1, "yy":2, "z":3}]});
          UPSERT INTO default VALUES("ka03", {"a":1, "reviews":[{"xx":1, "y":2, "z":3}]});
          UPSERT INTO default VALUES("ka04", {"a":1, "reviews":[{"x":1, "yy":2, "z":3}]});
          UPSERT INTO default VALUES("ka05", {"a":1, "reviews":[{"x":1, "y":2, "z":3}]});
          UPSERT INTO default VALUES("ka06", {"a":1, "reviews":[{"xx":1, "yy":2, "z":3}, {"x":1, "y":2, "z":3}]});
          UPSERT INTO default VALUES("ka07", {"a":1, "reviews":[{"xx":1, "y":2, "z":3}, {"x":1, "y":2, "z":3}]});
          UPSERT INTO default VALUES("ka08", {"a":1, "reviews":[{"x":1, "yy":2, "z":3}, {"x":1, "y":2, "z":3}]});
          UPSERT INTO default VALUES("ka09", {"a":1, "reviews":[{"x":1, "y":2, "z":3}, {"x":1, "y":2, "z":3}]});
           
          CREATE INDEX ix10 ON default (a, DISTINCT ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews END);
          CREATE INDEX ix11 ON default (a, DISTINCT ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews  WHEN v.z = 5 END);
          CREATE INDEX ix12 ON default ( ALL ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews END);
          CREATE INDEX ix13 ON default ( ALL ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews  WHEN v.z = 5 END);
          

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited See if these documents and how those indexed. And Write queries, ANY, UNNEST verify with primary index. Test corner cases UPSERT INTO default VALUES( "k001" , { "a" : 1 }); UPSERT INTO default VALUES( "k002" , { "a" : 1 , "reviews" :NULL}); UPSERT INTO default VALUES( "k003" , { "a" : 1 , "reviews" : 1 }); UPSERT INTO default VALUES( "k004" , { "a" : 1 , "reviews" : "abc" }); UPSERT INTO default VALUES( "ko01" , { "a" : 1 , "reviews" :{ "x" : 1 , "y" : 2 , "z" : 3 }}); UPSERT INTO default VALUES( "ko02" , { "a" : 1 , "reviews" :{ "xx" : 1 , "y" : 3 , "z" : 3 }}); UPSERT INTO default VALUES( "ko03" , { "a" : 1 , "reviews" :{ "x" : 1 , "yy" : 3 , "z" : 3 }}); UPSERT INTO default VALUES( "ka04" , { "a" : 1 , "reviews" :{ "xx" : 1 , "y" : 3 , "z" : 3 }}); UPSERT INTO default VALUES( "ka01" , { "a" : 1 , "reviews" :[]}); UPSERT INTO default VALUES( "ka02" , { "a" : 1 , "reviews" :[{ "xx" : 1 , "yy" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka03" , { "a" : 1 , "reviews" :[{ "xx" : 1 , "y" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka04" , { "a" : 1 , "reviews" :[{ "x" : 1 , "yy" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka05" , { "a" : 1 , "reviews" :[{ "x" : 1 , "y" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka06" , { "a" : 1 , "reviews" :[{ "xx" : 1 , "yy" : 2 , "z" : 3 }, { "x" : 1 , "y" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka07" , { "a" : 1 , "reviews" :[{ "xx" : 1 , "y" : 2 , "z" : 3 }, { "x" : 1 , "y" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka08" , { "a" : 1 , "reviews" :[{ "x" : 1 , "yy" : 2 , "z" : 3 }, { "x" : 1 , "y" : 2 , "z" : 3 }]}); UPSERT INTO default VALUES( "ka09" , { "a" : 1 , "reviews" :[{ "x" : 1 , "y" : 2 , "z" : 3 }, { "x" : 1 , "y" : 2 , "z" : 3 }]});   CREATE INDEX ix10 ON default (a, DISTINCT ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews END); CREATE INDEX ix11 ON default (a, DISTINCT ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews WHEN v.z = 5 END); CREATE INDEX ix12 ON default ( ALL ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews END); CREATE INDEX ix13 ON default ( ALL ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews WHEN v.z = 5 END);

          Build couchbase-server-7.1.0-1561 contains query commit eb26ce4 with commit message:
          MB-49110. Disable covering/pushdowns when sargable array index key produces Unkowns

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1561 contains query commit eb26ce4 with commit message: MB-49110 . Disable covering/pushdowns when sargable array index key produces Unkowns
          ajay.bhullar Ajay Bhullar added a comment - - edited

          in 7.1.0-2468, with the docs and indexes in the above comment

          CREATE INDEX ix10 ON default (a, DISTINCT ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews END);

          SELECT META().id FROM default d USE INDEX (ix10) WHERE d.a = 1 AND ANY v IN d.reviews SATISFIES v.x IS NULL END

          [

          { "id": "k002" }

          ,

          { "id": "k003" }

          ,

          { "id": "k004" }

          ,

          { "id": "ko01" }

          ,

          { "id": "ko02" }

          ,

          { "id": "ko03" }

          ]

          Same query with primary index returns

          SELECT META().id FROM default d USE INDEX (`#primary`) WHERE d.a = 1 AND ANY v IN d.reviews SATISFIES v.x IS NULL END

          { "results": [] }

          Explain of query with use index:

          {
              "optimizer_hints": {
                  "hints_followed": [
                      "INDEX(d ix10)"
                  ]
              },
              "plan": {
                  "#operator": "Sequence",
                  "~children": [
                      {
                          "#operator": "DistinctScan",
                          "scan": {
                              "#operator": "IndexScan3",
                              "as": "d",
                              "covers": [
                                  "cover ((`d`.`a`))",
                                  "cover ((`v`.`x`))",
                                  "cover ((`v`.`y`))",
                                  "cover ((meta(`d`).`id`))"
                              ],
                              "filter_covers": {
                                  "cover ((`d`.`reviews`))": [
                                      true
                                  ]
                              },
                              "index": "ix10",
                              "index_id": "b287d443807a3a94",
                              "index_projection": {
                                  "entry_keys": [
                                      0,
                                      1
                                  ],
                                  "primary_key": true
                              },
                              "keyspace": "default",
                              "namespace": "default",
                              "spans": [
                                  {
                                      "exact": true,
                                      "range": [
                                          {
                                              "high": "1",
                                              "inclusion": 3,
                                              "index_key": "`a`",
                                              "low": "1"
                                          },
                                          {
                                              "high": "null",
                                              "inclusion": 3,
                                              "index_key": "(`v`.`x`)",
                                              "low": "null"
                                          }
                                      ]
                                  }
                              ],
                              "using": "gsi"
                          }
                      },
                      {
                          "#operator": "Parallel",
                          "~child": {
                              "#operator": "Sequence",
                              "~children": [
                                  {
                                      "#operator": "Filter",
                                      "condition": "((cover ((`d`.`a`)) = 1) and any `v` in cover ((`d`.`reviews`)) satisfies (cover ((`v`.`x`)) is null) end)"
                                  },
                                  {
                                      "#operator": "InitialProject",
                                      "result_terms": [
                                          {
                                              "expr": "cover ((meta(`d`).`id`))"
                                          }
                                      ]
                                  }
                              ]
                          }
                      }
                  ]
              }
          }
          

          NOTE: this had the right behavior in 7.1.0-2405

          ajay.bhullar Ajay Bhullar added a comment - - edited in 7.1.0-2468, with the docs and indexes in the above comment CREATE INDEX ix10 ON default (a, DISTINCT ARRAY FLATTEN_KEYS(v.x, v.y) FOR v IN reviews END); SELECT META().id FROM default d USE INDEX (ix10) WHERE d.a = 1 AND ANY v IN d.reviews SATISFIES v.x IS NULL END [ { "id": "k002" } , { "id": "k003" } , { "id": "k004" } , { "id": "ko01" } , { "id": "ko02" } , { "id": "ko03" } ] Same query with primary index returns SELECT META().id FROM default d USE INDEX (`#primary`) WHERE d.a = 1 AND ANY v IN d.reviews SATISFIES v.x IS NULL END { "results": [] } Explain of query with use index: { "optimizer_hints": { "hints_followed": [ "INDEX(d ix10)" ] }, "plan": { "#operator": "Sequence", "~children": [ { "#operator": "DistinctScan", "scan": { "#operator": "IndexScan3", "as": "d", "covers": [ "cover ((`d`.`a`))", "cover ((`v`.`x`))", "cover ((`v`.`y`))", "cover ((meta(`d`).`id`))" ], "filter_covers": { "cover ((`d`.`reviews`))": [ true ] }, "index": "ix10", "index_id": "b287d443807a3a94", "index_projection": { "entry_keys": [ 0, 1 ], "primary_key": true }, "keyspace": "default", "namespace": "default", "spans": [ { "exact": true, "range": [ { "high": "1", "inclusion": 3, "index_key": "`a`", "low": "1" }, { "high": "null", "inclusion": 3, "index_key": "(`v`.`x`)", "low": "null" } ] } ], "using": "gsi" } }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Filter", "condition": "((cover ((`d`.`a`)) = 1) and any `v` in cover ((`d`.`reviews`)) satisfies (cover ((`v`.`x`)) is null) end)" }, { "#operator": "InitialProject", "result_terms": [ { "expr": "cover ((meta(`d`).`id`))" } ] } ] } } ] } } NOTE: this had the right behavior in 7.1.0-2405

          Build couchbase-server-7.1.0-2476 contains query commit 7ee5169 with commit message:
          MB-49110. Revise earlier fix regression

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-2476 contains query commit 7ee5169 with commit message: MB-49110 . Revise earlier fix regression

          Build couchbase-server-7.2.0-1013 contains query commit 7ee5169 with commit message:
          MB-49110. Revise earlier fix regression

          build-team Couchbase Build Team added a comment - Build couchbase-server-7.2.0-1013 contains query commit 7ee5169 with commit message: MB-49110 . Revise earlier fix regression
          ajay.bhullar Ajay Bhullar added a comment -

          verified in 7.1.0-2478, ANY/UNNEST using ALL/DISTINCT and partial indexes

          ajay.bhullar Ajay Bhullar added a comment - verified in 7.1.0-2478, ANY/UNNEST using ALL/DISTINCT and partial indexes

          People

            ajay.bhullar Ajay Bhullar
            Sitaram.Vemulapalli Sitaram Vemulapalli
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty