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

N1QL plans different while using Named Parameters with Array Indexes

    XMLWordPrintable

Details

    • Untriaged
    • Unknown

    Description

      When using named parameters, queries using "ANY ... SATISFIES ... END" produce different plans for execution that are not as optimised as compared to not using named parameters.

       

      Using travel-sample, the following Index is used :

      CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END);
      

       

      The following query which does not use a named parameter produces an expected query plan :

      SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = "US681" END;
      

      {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "926ns"
        },
        "~children": [
          {
            "#operator": "Authorize",
            "#stats": {
              "#phaseSwitches": 3,
              "execTime": "8.086µs",
              "servTime": "1.249702ms"
            },
            "privileges": {
              "List": [
                {
                  "Target": "default:travel-sample",
                  "Priv": 7
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 1,
                "execTime": "8.908µs"
              },
              "~children": [
                {
                  "#operator": "DistinctScan",
                  "#stats": {
                    "#itemsIn": 38,
                    "#itemsOut": 38,
                    "#phaseSwitches": 157,
                    "execTime": "91.318µs",
                    "kernTime": "13.324669ms"
                  },
                  "scan": {
                    "#operator": "IndexScan3",
                    "#stats": {
                      "#itemsOut": 38,
                      "#phaseSwitches": 157,
                      "execTime": "124.857µs",
                      "kernTime": "13.258µs",
                      "servTime": "13.168046ms"
                    },
                    "covers": [
                      "cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))",
                      "cover ((meta(`travel-sample`).`id`))"
                    ],
                    "filter_covers": {
                      "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = \"US681\") end)": true
                    },
                    "index": "idx_flight_test",
                    "index_id": "b6334268434aba20",
                    "index_projection": {
                      "primary_key": true
                    },
                    "keyspace": "travel-sample",
                    "namespace": "default",
                    "spans": [
                      {
                        "exact": true,
                        "range": [
                          {
                            "high": "\"US681\"",
                            "inclusion": 3,
                            "low": "\"US681\""
                          }
                        ]
                      }
                    ],
                    "using": "gsi",
                    "#time_normal": "00:00.0132",
                    "#time_absolute": 0.013292903000000002
                  },
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000091318
                },
                {
                  "#operator": "Sequence",
                  "#stats": {
                    "#phaseSwitches": 1,
                    "execTime": "6.068µs"
                  },
                  "~children": [
                    {
                      "#operator": "Filter",
                      "#stats": {
                        "#itemsIn": 38,
                        "#itemsOut": 38,
                        "#phaseSwitches": 155,
                        "execTime": "3.446106ms",
                        "kernTime": "13.437146ms"
                      },
                      "condition": "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = \"US681\") end)",
                      "#time_normal": "00:00.0034",
                      "#time_absolute": 0.003446106
                    },
                    {
                      "#operator": "InitialProject",
                      "#stats": {
                        "#itemsIn": 38,
                        "#itemsOut": 38,
                        "#phaseSwitches": 119,
                        "execTime": "61.761µs",
                        "kernTime": "16.892771ms"
                      },
                      "result_terms": [
                        {
                          "expr": "cover ((meta(`travel-sample`).`id`))"
                        }
                      ],
                      "#time_normal": "00:00.0000",
                      "#time_absolute": 0.00006176100000000001
                    },
                    {
                      "#operator": "FinalProject",
                      "#stats": {
                        "#itemsIn": 38,
                        "#itemsOut": 38,
                        "#phaseSwitches": 115,
                        "execTime": "25.357µs",
                        "kernTime": "152.933µs"
                      },
                      "#time_normal": "00:00.0000",
                      "#time_absolute": 0.000025356999999999998
                    }
                  ],
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000006067999999999999
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000008908
            },
            "#time_normal": "00:00.0012",
            "#time_absolute": 0.0012577880000000001
          },
          {
            "#operator": "Stream",
            "#stats": {
              "#itemsIn": 38,
              "#itemsOut": 38,
              "#phaseSwitches": 155,
              "execTime": "5.46µs",
              "kernTime": "18.425336ms"
            },
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.00000546
          }
        ],
        "~versions": [
          "2.0.0-N1QL",
          "5.5.2-3733-enterprise"
        ],
        "#time_normal": "00:00",
        "#time_absolute": 0
      }
      

       

      Using a named parameter however produces a different plan:

      SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;
      

      {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "12.611µs"
        },
        "~children": [
          {
            "#operator": "Authorize",
            "#stats": {
              "#phaseSwitches": 3,
              "execTime": "17.278µs",
              "servTime": "20.387192ms"
            },
            "privileges": {
              "List": [
                {
                  "Target": "default:travel-sample",
                  "Priv": 7
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 1,
                "execTime": "29.364µs"
              },
              "~children": [
                {
                  "#operator": "DistinctScan",
                  "#stats": {
                    "#itemsIn": 38,
                    "#itemsOut": 38,
                    "#phaseSwitches": 157,
                    "execTime": "121.912µs",
                    "kernTime": "24.965667ms"
                  },
                  "scan": {
                    "#operator": "IndexScan3",
                    "#stats": {
                      "#itemsOut": 38,
                      "#phaseSwitches": 157,
                      "execTime": "79.143µs",
                      "kernTime": "7.815µs",
                      "servTime": "24.856622ms"
                    },
                    "index": "idx_flight_test",
                    "index_id": "b6334268434aba20",
                    "index_projection": {
                      "primary_key": true
                    },
                    "keyspace": "travel-sample",
                    "namespace": "default",
                    "spans": [
                      {
                        "exact": true,
                        "range": [
                          {
                            "high": "\"US681\"",
                            "inclusion": 3,
                            "low": "\"US681\""
                          }
                        ]
                      }
                    ],
                    "using": "gsi",
                    "#time_normal": "00:00.0249",
                    "#time_absolute": 0.024935765000000002
                  },
                  "#time_normal": "00:00.0001",
                  "#time_absolute": 0.00012191200000000001
                },
                {
                  "#operator": "Fetch",
                  "#stats": {
                    "#itemsIn": 38,
                    "#itemsOut": 38,
                    "#phaseSwitches": 161,
                    "execTime": "125.7µs",
                    "kernTime": "25.113325ms",
                    "servTime": "218.202109ms"
                  },
                  "keyspace": "travel-sample",
                  "namespace": "default",
                  "#time_normal": "00:00.2183",
                  "#time_absolute": 0.218327809
                },
                {
                  "#operator": "Sequence",
                  "#stats": {
                    "#phaseSwitches": 1,
                    "execTime": "7.403µs"
                  },
                  "~children": [
                    {
                      "#operator": "Filter",
                      "#stats": {
                        "#itemsIn": 38,
                        "#itemsOut": 38,
                        "#phaseSwitches": 155,
                        "execTime": "9.705067ms",
                        "kernTime": "234.098171ms"
                      },
                      "condition": "any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end",
                      "#time_normal": "00:00.0097",
                      "#time_absolute": 0.009705067
                    },
                    {
                      "#operator": "InitialProject",
                      "#stats": {
                        "#itemsIn": 38,
                        "#itemsOut": 38,
                        "#phaseSwitches": 119,
                        "execTime": "200.696µs",
                        "kernTime": "241.29278ms"
                      },
                      "result_terms": [
                        {
                          "expr": "(meta(`travel-sample`).`id`)"
                        }
                      ],
                      "#time_normal": "00:00.0002",
                      "#time_absolute": 0.000200696
                    },
                    {
                      "#operator": "FinalProject",
                      "#stats": {
                        "#itemsIn": 38,
                        "#itemsOut": 38,
                        "#phaseSwitches": 115,
                        "execTime": "17.587µs",
                        "kernTime": "2.347419ms"
                      },
                      "#time_normal": "00:00.0000",
                      "#time_absolute": 0.000017587
                    }
                  ],
                  "#time_normal": "00:00.0000",
                  "#time_absolute": 0.000007402999999999999
                }
              ],
              "#time_normal": "00:00.0000",
              "#time_absolute": 0.000029364000000000002
            },
            "#time_normal": "00:00.0204",
            "#time_absolute": 0.020404469999999997
          },
          {
            "#operator": "Stream",
            "#stats": {
              "#itemsIn": 38,
              "#itemsOut": 38,
              "#phaseSwitches": 155,
              "execTime": "13.726µs",
              "kernTime": "264.321019ms"
            },
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000013726
          }
        ],
        "~versions": [
          "2.0.0-N1QL",
          "5.5.2-3733-enterprise"
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000012611
      }
      

      Attachments

        Issue Links

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

          Activity

            thomas.dotters Thomas Dotters created issue -
            thomas.dotters Thomas Dotters made changes -
            Field Original Value New Value
            Link This issue blocks CBSE-6398 [ CBSE-6398 ]
            raju Raju Suravarjjala made changes -
            Fix Version/s Mad-Hatter [ 15037 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Issue Type Bug [ 1 ] Improvement [ 4 ]

            Array index has parameters we can't implicitly cover. Either index must contain array as scalar or required Fetch.
            I am not sure that is possible to implement that.

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Array index has parameters we can't implicitly cover. Either index must contain array as scalar or required Fetch. I am not sure that is possible to implement that.
            keshav Keshav Murthy made changes -
            Fix Version/s feature-backlog [ 10342 ]
            Fix Version/s Mad-Hatter [ 15037 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Assignee Keshav Murthy [ keshav ] Kamini Jagtiani [ kamini.jagtiani ]
            fabrice.leray Fabrice Leray made changes -
            Link This issue blocks CBSE-8464 [ CBSE-8464 ]
            tim.bradgate Tim Bradgate (Inactive) made changes -
            Affects Version/s 6.5.0 [ 15037 ]
            hitesh.walia Hitesh Walia made changes -
            Link This issue blocks CBSE-8565 [ CBSE-8565 ]
            roi.katz Roi Katz made changes -
            Link This issue blocks CBSE-8773 [ CBSE-8773 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

            The only issue (non covering) with Array Index + query parameter is with adhoc queries.

            It will use covering with prepare statements.

            if it is non prepare statements and uses query parameter for execution i.e. adhoc. (In UI by providing query parameters and executing/EXPLAIN/ADVISE) it is not covering.

            The problem:
            During adoc queries we have query parameters. We copy where clause replace all query parameters in Query where clause with query parameters values do match partial indexes.
            Then we generate sargablity/spans on new replaced where clause. Query has original where clause with parameters , When covering determined it has original where clause and don’t match (filter covers has values vs query parameter expression)

            If it is prepared statement : Original query and filter covers has parameter expression and covers.

            [
              {
                "encoded_plan": "H4sIAAAAAAAA/wEAAP//AAAAAAAAAAA=",
                "featureControls": 12,
                "indexApiVersion": 4,
                "name": "[127.0.0.1:8091]p1",
                "namespace": "default",
                "operator": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "Authorize",
                      "privileges": {
                        "List": [
                          {
                            "Priv": 7,
                            "Target": "default:travel-sample"
                          }
                        ]
                      },
                      "~child": {
                        "#operator": "Sequence",
                        "~children": [
                          {
                            "#operator": "DistinctScan",
                            "scan": {
                              "#operator": "IndexScan3",
                              "covers": [
                                "cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))",
                                "cover ((meta(`travel-sample`).`id`))"
                              ],
                              "filter_covers": {
                                "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)": true
                              },
                              "index": "idx_flight_test",
                              "index_id": "1f90fa1c50c091b1",
                              "index_projection": {
                                "primary_key": true
                              },
                              "keyspace": "travel-sample",
                              "namespace": "default",
                              "spans": [
                                {
                                  "exact": true,
                                  "range": [
                                    {
                                      "high": "$flight",
                                      "inclusion": 3,
                                      "low": "$flight"
                                    }
                                  ]
                                }
                              ],
                              "using": "gsi"
                            }
                          },
                          {
                            "#operator": "Parallel",
                            "~child": {
                              "#operator": "Sequence",
                              "~children": [
                                {
                                  "#operator": "Filter",
                                  "condition": "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)"
                                },
                                {
                                  "#operator": "InitialProject",
                                  "result_terms": [
                                    {
                                      "expr": "cover ((meta(`travel-sample`).`id`))"
                                    }
                                  ]
                                },
                                {
                                  "#operator": "FinalProject"
                                }
                              ]
                            }
                          }
                        ]
                      }
                    },
                    {
                      "#operator": "Stream"
                    }
                  ]
                },
                "signature": {
                  "id": "json"
                },
                "text": "prepare p1 from SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;"
              }
            ]
            

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited The only issue (non covering) with Array Index + query parameter is with adhoc queries. It will use covering with prepare statements. if it is non prepare statements and uses query parameter for execution i.e. adhoc. (In UI by providing query parameters and executing/EXPLAIN/ADVISE) it is not covering. The problem: During adoc queries we have query parameters. We copy where clause replace all query parameters in Query where clause with query parameters values do match partial indexes. Then we generate sargablity/spans on new replaced where clause. Query has original where clause with parameters , When covering determined it has original where clause and don’t match (filter covers has values vs query parameter expression) If it is prepared statement : Original query and filter covers has parameter expression and covers. [ { "encoded_plan" : "H4sIAAAAAAAA/wEAAP//AAAAAAAAAAA=" , "featureControls" : 12 , "indexApiVersion" : 4 , "name" : "[127.0.0.1:8091]p1" , "namespace" : "default" , "operator" : { "#operator" : "Sequence" , "~children" : [ { "#operator" : "Authorize" , "privileges" : { "List" : [ { "Priv" : 7 , "Target" : "default:travel-sample" } ] }, "~child" : { "#operator" : "Sequence" , "~children" : [ { "#operator" : "DistinctScan" , "scan" : { "#operator" : "IndexScan3" , "covers" : [ "cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))" , "cover ((meta(`travel-sample`).`id`))" ], "filter_covers" : { "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)" : true }, "index" : "idx_flight_test" , "index_id" : "1f90fa1c50c091b1" , "index_projection" : { "primary_key" : true }, "keyspace" : "travel-sample" , "namespace" : "default" , "spans" : [ { "exact" : true , "range" : [ { "high" : "$flight" , "inclusion" : 3 , "low" : "$flight" } ] } ], "using" : "gsi" } }, { "#operator" : "Parallel" , "~child" : { "#operator" : "Sequence" , "~children" : [ { "#operator" : "Filter" , "condition" : "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)" }, { "#operator" : "InitialProject" , "result_terms" : [ { "expr" : "cover ((meta(`travel-sample`).`id`))" } ] }, { "#operator" : "FinalProject" } ] } } ] } }, { "#operator" : "Stream" } ] }, "signature" : { "id" : "json" }, "text" : "prepare p1 from SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;" } ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Issue Type Improvement [ 4 ] Bug [ 1 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Fix Version/s Cheshire-Cat [ 15915 ]
            Fix Version/s feature-backlog [ 10342 ]

            For Array Index keys we do generate covers here
            https://github.com/couchbase/query/blob/master/planner/build_scan_covering.go#L453

            covers, err := CoversFor(pred, origPred, keys)

            origPred – Query Parameter replaced with actual value + non-DNF Transformed keyspace predicate.
            pred – Query Parameter replaced with actual value + DNF Transformed keyspace predicate.

            To work we need 2 more without replacing query parameter. To get this it may make more complex do separation for each keyspace.
            If this not already done to avoid 2 time separation: First separate predicates with query parameter expression by keyspace. and store them as part of keyspace (expression based DNF, non-DNF). Then replace query parameters with value and store DNF, non-DNF.
            Then call above functions for twice or on all 4

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - For Array Index keys we do generate covers here https://github.com/couchbase/query/blob/master/planner/build_scan_covering.go#L453 covers, err := CoversFor(pred, origPred, keys) origPred – Query Parameter replaced with actual value + non-DNF Transformed keyspace predicate. pred – Query Parameter replaced with actual value + DNF Transformed keyspace predicate. To work we need 2 more without replacing query parameter. To get this it may make more complex do separation for each keyspace. If this not already done to avoid 2 time separation: First separate predicates with query parameter expression by keyspace. and store them as part of keyspace (expression based DNF, non-DNF). Then replace query parameters with value and store DNF, non-DNF. Then call above functions for twice or on all 4
            roi.katz Roi Katz added a comment -

            Hi Sitaram Vemulapalli

            can you elaborate on the covering index definition, Do you mean that the index should look like that

            CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END, schedule);

             instead of the original 

            CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END);

             

            Also, is the true only for equality ?

             

            roi.katz Roi Katz added a comment - Hi Sitaram Vemulapalli ,  can you elaborate on the covering index definition, Do you mean that the index should look like that CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END, schedule);  instead of the original  CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END);   Also, is the true only for equality ?  
            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited

            No change in index. Index is what given in the description

            CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END);
            PREPARE p1 FROM SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;
            Set named parameter $flight
            EXECUTE p1;
            

            PREPARE p2 FROM SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight >= $flight END;
            Also covers. Note that every combinations can't be covered there are limits. Predicate must be exact and no false positives possible then only it can cover array index implicitly

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - - edited No change in index. Index is what given in the description CREATE INDEX idx_flight_test ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END); PREPARE p1 FROM SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END; Set named parameter $flight EXECUTE p1; PREPARE p2 FROM SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight >= $flight END; Also covers. Note that every combinations can't be covered there are limits. Predicate must be exact and no false positives possible then only it can cover array index implicitly
            roi.katz Roi Katz added a comment -

            Sitaram Vemulapalli, The SDK does that automatically when using ad-hoc = false. 

            So theoretically, enabling that should help it either, or it would just spam the execution plans?

            roi.katz Roi Katz added a comment - Sitaram Vemulapalli , The SDK does that automatically when using ad-hoc = false.  So theoretically, enabling that should help it either, or it would just spam the execution plans?

            Yes. If you have doubts test it.

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - Yes. If you have doubts test it.

            Build couchbase-server-7.0.0-3772 contains query commit e80801a with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-3772 contains query commit e80801a with commit message: MB-33009 Delay processing of query parameters till actual use

            Build couchbase-server-7.0.0-3772 contains query-ee commit 273b156 with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-3772 contains query-ee commit 273b156 with commit message: MB-33009 Delay processing of query parameters till actual use
            bingjie.miao Bingjie Miao made changes -
            Assignee Kamini Jagtiani [ kamini.jagtiani ] Mihir Kamdar [ mihir.kamdar ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Resolved [ 5 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue is duplicated by MB-42038 [ MB-42038 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue relates to MB-36204 [ MB-36204 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue is triggering MB-42956 [ MB-42956 ]
            bingjie.miao Bingjie Miao added a comment -

            Reopen for 6.6.2.

            bingjie.miao Bingjie Miao added a comment - Reopen for 6.6.2.
            bingjie.miao Bingjie Miao made changes -
            Assignee Mihir Kamdar [ mihir.kamdar ] Bingjie Miao [ bingjie.miao ]
            Resolution Fixed [ 1 ]
            Status Resolved [ 5 ] Reopened [ 4 ]
            raju Raju Suravarjjala made changes -
            Link This issue blocks MB-43310 [ MB-43310 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue blocks CBSE-9705 [ CBSE-9705 ]

            Build couchbase-server-6.6.2-9529 contains query commit 7383dfb with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9529 contains query commit 7383dfb with commit message: MB-33009 Delay processing of query parameters till actual use

            Build couchbase-server-6.6.2-9529 contains query-ee commit e64e499 with commit message:
            MB-33009 Delay processing of query parameters till actual use

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9529 contains query-ee commit e64e499 with commit message: MB-33009 Delay processing of query parameters till actual use
            bingjie.miao Bingjie Miao made changes -
            Assignee Bingjie Miao [ bingjie.miao ] Mihir Kamdar [ mihir.kamdar ]
            Resolution Fixed [ 1 ]
            Status Reopened [ 4 ] Resolved [ 5 ]
            bingjie.miao Bingjie Miao made changes -
            Fix Version/s 6.6.2 [ 17103 ]
            pierre.regazzoni Pierre Regazzoni made changes -
            Assignee Mihir Kamdar [ mihir.kamdar ] Pierre Regazzoni [ JIRAUSER25157 ]

            Verified on 6.6.2-9529 and 7.0.0-4485

            cbq> explain SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;
            {
                "requestID": "c6b8ce3e-83a5-4f03-92c6-e431d530c716",
                "signature": "json",
                "results": [
                {
                    "plan": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "DistinctScan",
                                "scan": {
                                    "#operator": "IndexScan3",
                                    "covers": [
                                        "cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))",
                                        "cover ((meta(`travel-sample`).`id`))"
                                    ],
                                    "filter_covers": {
                                        "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)": true
                                    },
                                    "index": "idx_flight_test",
                                    "index_id": "80b731d88b5474e1",
                                    "index_projection": {
                                        "primary_key": true
                                    },
                                    "keyspace": "travel-sample",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "exact": true,
                                            "range": [
                                                {
                                                    "high": "$flight",
                                                    "inclusion": 3,
                                                    "low": "$flight"
                                                }
                                            ]
                                        }
                                    ],
                                    "using": "gsi"
                                }
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "Filter",
                                            "condition": "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)"
                                        },
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "cover ((meta(`travel-sample`).`id`))"
                                                }
                                            ]
                                        },
                                        {
                                            "#operator": "FinalProject"
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    "text": "SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;"
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "3.438649ms",
                    "executionTime": "3.179783ms",
                    "resultCount": 1,
                    "resultSize": 2714
                }
            } 

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 6.6.2-9529 and 7.0.0-4485 cbq> explain SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END; {     "requestID" : "c6b8ce3e-83a5-4f03-92c6-e431d530c716" ,     "signature" : "json" ,     "results" : [     {         "plan" : {             "#operator" : "Sequence" ,             "~children" : [                 {                     "#operator" : "DistinctScan" ,                     "scan" : {                         "#operator" : "IndexScan3" ,                         "covers" : [                             "cover ((distinct (array (`v`.`flight`) for `v` in (`travel-sample`.`schedule`) end)))" ,                             "cover ((meta(`travel-sample`).`id`))"                         ],                         "filter_covers" : {                             "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)" : true                         },                         "index" : "idx_flight_test" ,                         "index_id" : "80b731d88b5474e1" ,                         "index_projection" : {                             "primary_key" : true                         },                         "keyspace" : "travel-sample" ,                         "namespace" : "default" ,                         "spans" : [                             {                                 "exact" : true ,                                 "range" : [                                     {                                         "high" : "$flight" ,                                         "inclusion" : 3 ,                                         "low" : "$flight"                                     }                                 ]                             }                         ],                         "using" : "gsi"                     }                 },                 {                     "#operator" : "Parallel" ,                     "~child" : {                         "#operator" : "Sequence" ,                         "~children" : [                             {                                 "#operator" : "Filter" ,                                 "condition" : "cover (any `v` in (`travel-sample`.`schedule`) satisfies ((`v`.`flight`) = $flight) end)"                             },                             {                                 "#operator" : "InitialProject" ,                                 "result_terms" : [                                     {                                         "expr" : "cover ((meta(`travel-sample`).`id`))"                                     }                                 ]                             },                             {                                 "#operator" : "FinalProject"                             }                         ]                     }                 }             ]         },         "text" : "SELECT meta().id FROM `travel-sample` WHERE ANY v in schedule SATISFIES v.flight = $flight END;"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "3.438649ms" ,         "executionTime" : "3.179783ms" ,         "resultCount" : 1 ,         "resultSize" : 2714     } }
            pierre.regazzoni Pierre Regazzoni made changes -
            Status Resolved [ 5 ] Closed [ 6 ]
            wayne Wayne Siu made changes -
            Labels approved-for-6.6.2
            lynn.straus Lynn Straus made changes -
            Fix Version/s 7.0.0 [ 17233 ]
            lynn.straus Lynn Straus made changes -
            Fix Version/s Cheshire-Cat [ 15915 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-42956 [ MB-42956 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-42956 [ MB-42956 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-48266 [ MB-48266 ]
            mihir.kamdar Mihir Kamdar (Inactive) made changes -
            Labels approved-for-6.6.2 approved-for-6.6.2 needs_automation
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-52090 [ MB-52090 ]
            bingjie.miao Bingjie Miao made changes -
            Link This issue causes MB-52161 [ MB-52161 ]

            People

              pierre.regazzoni Pierre Regazzoni
              thomas.dotters Thomas Dotters
              Votes:
              2 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty