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

Array Index WHEN clause and query named parameters not working

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Yes

    Description

      create index ix1 on default(DISTINCT ARRAY v.lname FOR v IN names WHEN v.mname = "abc" END);
      EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND  v.mname = "abc" END;  -- works
      cbq> \set -$mname  "abc";
      cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND  v.mname = $mname END;
      {
          "requestID": "adc9c3a6-4904-492c-a15b-94c6b3cca802",
          "errors": [
              {
                  "code": 4000,
                  "msg": "No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online."
              }
          ],
          "status": "fatal",
          "metrics": {
              "elapsedTime": "2.189019ms",
              "executionTime": "2.021909ms",
              "resultCount": 0,
              "resultSize": 0,
              "errorCount": 1
          }
      }
      
      

      last one is adhoc = true, If when clause value is parameterized it is not working. In adhoc=true we suppose to replace parameter with value before index selection.

      Looks like some case is missed in MB-33009

      Attachments

        Issue Links

          For Gerrit Dashboard: MB-48266
          # Subject Branch Project Status CR V

          Activity

            Build couchbase-server-7.1.0-1234 contains query commit fe9b8af with commit message:
            MB-48266 Replace query parameters for WHEN clause of ANY predicates

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1234 contains query commit fe9b8af with commit message: MB-48266 Replace query parameters for WHEN clause of ANY predicates

            Build couchbase-server-7.0.2-6634 contains query commit 8644cbb with commit message:
            MB-48266 Replace query parameters for WHEN clause of ANY predicates

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.2-6634 contains query commit 8644cbb with commit message: MB-48266 Replace query parameters for WHEN clause of ANY predicates

            Build couchbase-server-7.0.2-6635 contains query commit c9abaf2 with commit message:
            MB-48266 Improve previous fix

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.2-6635 contains query commit c9abaf2 with commit message: MB-48266 Improve previous fix

            Build couchbase-server-7.1.0-1235 contains query commit e9af516 with commit message:
            MB-48266 Improve previous fix

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.0-1235 contains query commit e9af516 with commit message: MB-48266 Improve previous fix

            Verified on 7.0.2-6640 and 7.1.0-1235

            cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND  v.mname = $mname END;
            {
                "requestID": "587e4bef-e554-4037-bbf8-ef639c1543b9",
                "signature": "json",
                "results": [
                {
                    "plan": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "DistinctScan",
                                "scan": {
                                    "#operator": "IndexScan3",
                                    "covers": [
                                        "cover ((distinct (array (`v`.`lname`) for `v` in (`default`.`names`) when ((`v`.`mname`) = \"abc\") end)))",
                                        "cover ((meta(`default`).`id`))"
                                    ],
                                    "filter": "cover (any `v` in (`default`.`names`) satisfies (((`v`.`lname`) = \"xyz\") and ((`v`.`mname`) = $mname)) end)",
                                    "filter_covers": {
                                        "cover (any `v` in (`default`.`names`) satisfies (((`v`.`lname`) = \"xyz\") and ((`v`.`mname`) = $mname)) end)": true
                                    },
                                    "index": "ix1",
                                    "index_id": "e9b7d7f5e2870a16",
                                    "index_projection": {
                                        "primary_key": true
                                    },
                                    "keyspace": "default",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "exact": true,
                                            "range": [
                                                {
                                                    "high": "\"xyz\"",
                                                    "inclusion": 3,
                                                    "index_key": "(distinct (array (`v`.`lname`) for `v` in `names` when ((`v`.`mname`) = \"abc\") end))",
                                                    "low": "\"xyz\""
                                                }
                                            ]
                                        }
                                    ],
                                    "using": "gsi"
                                }
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "1"
                                                }
                                            ]
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    "text": "SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = \"xyz\" AND  v.mname = $mname END;"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "2.065132ms",
                    "executionTime": "1.964994ms",
                    "resultCount": 1,
                    "resultSize": 2616,
                    "serviceLoad": 6
                }
            } 

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 7.0.2-6640 and 7.1.0-1235 cbq> EXPLAIN SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = "xyz" AND  v.mname = $mname END; {     "requestID" : "587e4bef-e554-4037-bbf8-ef639c1543b9" ,     "signature" : "json" ,     "results" : [     {         "plan" : {             "#operator" : "Sequence" ,             "~children" : [                 {                     "#operator" : "DistinctScan" ,                     "scan" : {                         "#operator" : "IndexScan3" ,                         "covers" : [                             "cover ((distinct (array (`v`.`lname`) for `v` in (`default`.`names`) when ((`v`.`mname`) = \"abc\") end)))" ,                             "cover ((meta(`default`).`id`))"                         ],                         "filter" : "cover (any `v` in (`default`.`names`) satisfies (((`v`.`lname`) = \"xyz\") and ((`v`.`mname`) = $mname)) end)" ,                         "filter_covers" : {                             "cover (any `v` in (`default`.`names`) satisfies (((`v`.`lname`) = \"xyz\") and ((`v`.`mname`) = $mname)) end)" : true                         },                         "index" : "ix1" ,                         "index_id" : "e9b7d7f5e2870a16" ,                         "index_projection" : {                             "primary_key" : true                         },                         "keyspace" : "default" ,                         "namespace" : "default" ,                         "spans" : [                             {                                 "exact" : true ,                                 "range" : [                                     {                                         "high" : "\"xyz\"" ,                                         "inclusion" : 3 ,                                         "index_key" : "(distinct (array (`v`.`lname`) for `v` in `names` when ((`v`.`mname`) = \"abc\") end))" ,                                         "low" : "\"xyz\""                                     }                                 ]                             }                         ],                         "using" : "gsi"                     }                 },                 {                     "#operator" : "Parallel" ,                     "~child" : {                         "#operator" : "Sequence" ,                         "~children" : [                             {                                 "#operator" : "InitialProject" ,                                 "result_terms" : [                                     {                                         "expr" : "1"                                     }                                 ]                             }                         ]                     }                 }             ]         },         "text" : "SELECT 1 FROM default WHERE ANY v IN names SATISFIES v.lname = \"xyz\" AND  v.mname = $mname END;"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "2.065132ms" ,         "executionTime" : "1.964994ms" ,         "resultCount" : 1 ,         "resultSize" : 2616 ,         "serviceLoad" : 6     } }
            wayne Wayne Siu added a comment -

            Re-opening it for 6.6.4.

            wayne Wayne Siu added a comment - Re-opening it for 6.6.4.

            Build couchbase-server-6.6.4-9915 contains query commit a2b428e with commit message:
            MB-48266 Replace query parameters for WHEN clause of ANY predicates

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.4-9915 contains query commit a2b428e with commit message: MB-48266 Replace query parameters for WHEN clause of ANY predicates

            Verified also on 6.6.4-9915

            pierre.regazzoni Pierre Regazzoni added a comment - Verified also on 6.6.4-9915

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty