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

Filter operator applied twice for adhoc query parameters

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Duplicate
    • Cheshire-Cat
    • 7.0.0
    • query
    • None
    • Untriaged
    • 1
    • Yes

    Description

      Rerpo:
      CREATE INDEX ix1 ON default(a);
      \set -$a = 1;
      EXPLAIN SELECT * FROM default WHERE a = $a;

      "~children": [
                                  {
                                      "#operator": "Filter",
                                      "condition": "((`default`.`a`) = 1)"
                                  },
                                  {
                                      "#operator": "Filter",
                                      "condition": "((`default`.`a`) = $a)"
                                  },
      
      

      {
          "requestID": "d8139696-5569-48fe-b827-36266d381619",
          "signature": "json",
          "results": [
          {
              "plan": {
                  "#operator": "Sequence",
                  "~children": [
                      {
                          "#operator": "IndexScan3",
                          "index": "ix1",
                          "index_id": "9e2eceb58ecd3698",
                          "index_projection": {
                              "primary_key": true
                          },
                          "keyspace": "default",
                          "namespace": "default",
                          "spans": [
                              {
                                  "exact": true,
                                  "range": [
                                      {
                                          "high": "1",
                                          "inclusion": 3,
                                          "low": "1"
                                      }
                                  ]
                              }
                          ],
                          "using": "gsi"
                      },
                      {
                          "#operator": "Fetch",
                          "keyspace": "default",
                          "namespace": "default"
                      },
                      {
                          "#operator": "Parallel",
                          "~child": {
                              "#operator": "Sequence",
                              "~children": [
                                  {
                                      "#operator": "Filter",
                                      "condition": "((`default`.`a`) = 1)"
                                  },
                                  {
                                      "#operator": "Filter",
                                      "condition": "((`default`.`a`) = $a)"
                                  },
                                  {
                                      "#operator": "InitialProject",
                                      "result_terms": [
                                          {
                                              "expr": "self",
                                              "star": true
                                          }
                                      ]
                                  }
                              ]
                          }
                      }
                  ]
              },
              "text": "select * FROM default where a = $a;"
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "891.083µs",
              "executionTime": "819.644µs",
              "resultCount": 1,
              "resultSize": 2182,
              "serviceLoad": 2
          }
      }
      

      Attachments

        Issue Links

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

          Activity

            ajay.bhullar Ajay Bhullar added a comment -

            seems to be a duplicate of MB-33009

            ajay.bhullar Ajay Bhullar added a comment - seems to be a duplicate of MB-33009

            Problem: non prepare statements when query parameter is available plannner replaces query predicate parameter expression with parameter values. Generates the plan. During plan generation it splits the predicate based on keyspaces and applies appropriate places (After IndexScan, After Fetch) and removes from original predicate. Then applies remaining original predicate at the end of the query. Original predicate has parameter expression, while remove it has parameter value. So no match and nothing is removed.

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Problem: non prepare statements when query parameter is available plannner replaces query predicate parameter expression with parameter values. Generates the plan. During plan generation it splits the predicate based on keyspaces and applies appropriate places (After IndexScan, After Fetch) and removes from original predicate. Then applies remaining original predicate at the end of the query. Original predicate has parameter expression, while remove it has parameter value. So no match and nothing is removed.

            Covering query also has wrong filters. Once one IndexScan, one later.

             explain select META().id FROM default where a = $a;
            {
                "requestID": "2779a271-04fa-4114-b760-92b2588bcc02",
                "signature": "json",
                "results": [
                {
                    "plan": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "IndexScan3",
                                "covers": [
                                    "cover ((`default`.`a`))",
                                    "cover ((meta(`default`).`id`))"
                                ],
                                "filter": "(cover ((`default`.`a`)) = 1)",
                                "index": "ix1",
                                "index_id": "9e2eceb58ecd3698",
                                "keyspace": "default",
                                "namespace": "default",
                                "spans": [
                                    {
                                        "exact": true,
                                        "range": [
                                            {
                                                "high": "1",
                                                "inclusion": 3,
                                                "low": "1"
                                            }
                                        ]
                                    }
                                ],
                                "using": "gsi"
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "Filter",
                                            "condition": "(cover ((`default`.`a`)) = $a)"
                                        },
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "cover ((meta(`default`).`id`))"
                                                }
                                            ]
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    "text": "select META().id FROM default where a = $a;"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "1.238985ms",
                    "executionTime": "1.160551ms",
                    "resultCount": 1,
                    "resultSize": 1937,
                    "serviceLoad": 2
                }
            }
            

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Covering query also has wrong filters. Once one IndexScan, one later. explain select META().id FROM default where a = $a; { "requestID" : "2779a271-04fa-4114-b760-92b2588bcc02" , "signature" : "json" , "results" : [ { "plan" : { "#operator" : "Sequence" , "~children" : [ { "#operator" : "IndexScan3" , "covers" : [ "cover ((`default`.`a`))" , "cover ((meta(`default`).`id`))" ], "filter" : "(cover ((`default`.`a`)) = 1)" , "index" : "ix1" , "index_id" : "9e2eceb58ecd3698" , "keyspace" : "default" , "namespace" : "default" , "spans" : [ { "exact" : true , "range" : [ { "high" : "1" , "inclusion" : 3 , "low" : "1" } ] } ], "using" : "gsi" }, { "#operator" : "Parallel" , "~child" : { "#operator" : "Sequence" , "~children" : [ { "#operator" : "Filter" , "condition" : "(cover ((`default`.`a`)) = $a)" }, { "#operator" : "InitialProject" , "result_terms" : [ { "expr" : "cover ((meta(`default`).`id`))" } ] } ] } } ] }, "text" : "select META().id FROM default where a = $a;" } ], "status" : "success" , "metrics" : { "elapsedTime" : "1.238985ms" , "executionTime" : "1.160551ms" , "resultCount" : 1 , "resultSize" : 1937 , "serviceLoad" : 2 } }

            People

              mihir.kamdar Mihir Kamdar (Inactive)
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty