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

arbitrary variable names in array indexing doesn't work with UNNEST queries

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Fixed
    • Major
    • 6.5.0
    • 4.6.2, 5.0.0
    • query
    • None

    Description

      MB-22646 enabled usage of arbitrary variables names in the SELECT predicates (freeing it from the requirement of exact variable names in index definition). However bunch of cases doesn't work properly when using different variable names. 

        - Tried with both ALL and DISTINCT array index. UNNEST doesn't use the array index when different variable name is used. It works with ANY.

        - array index is not picked when EVERY predicate is used.

        - array index is picked, but not covered when ANY AND EVERY predicate is used.

      Not sure if these cover all the use cases for arbitrary variables. Can we make the arbitrary variables names work without any artificial limitations, with various predicates and nested array indexes etc.

       

      1) CREATE INDEX isched_c_all
      ON `travel-sample`(all ARRAY i.flight FOR i IN schedule END)
      WHERE (type = "route") ;

       

      EXPLAIN
      SELECT meta(t).id from `travel-sample` t
      use index (isched_c_all)
      UNNEST schedule x
      WHERE x.flight < 'UA' and t.type="route";

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "index": "def_type",

       

      2) EXPLAIN
      SELECT meta(t).id from `travel-sample` t
      use index (isched_c_all)
      UNNEST schedule i
      WHERE i.flight < 'UA' and t.type="route";

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "covers": [
      "cover ((`i`.`flight`))",
      "cover ((meta(`t`).`id`))"
      ],
      "filter_covers": {
      "cover (((`t`.`schedule`) < {}))": true,
      "cover (([] <= (`t`.`schedule`)))": true,
      "cover ((`t`.`type`))": "route",
      "cover (is_array((`t`.`schedule`)))": true
      },
      "index": "isched_c_all",

       

      3) EXPLAIN SELECT meta().id FROM `travel-sample`
      USE INDEX(isched_c_all)
      WHERE ANY AND EVERY x IN schedule SATISFIES x.flight >= "UA" END AND type = "route";

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "DistinctScan",
      "scan": {
      "#operator": "IndexScan",
      "index": "isched_c_all",
      "index_id": "a23589b0ccd12f85",
      "keyspace": "travel-sample",

      4) 

      EXPLAIN SELECT meta().id FROM `travel-sample`
      USE INDEX(isched_c)
      WHERE ANY AND EVERY x IN schedule SATISFIES x.flight >= "UA" END AND type = "route";

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "DistinctScan",
      "scan": {
      "#operator": "IndexScan",
      "index": "isched_c",
      "index_id": "aeabca8e085c49ca",
      "keyspace": "travel-sample",

       

      5) EXPLAIN SELECT meta().id FROM `travel-sample`
      USE INDEX(isched_c)
      WHERE EVERY x IN schedule SATISFIES x.flight >= "UA" END AND type = "route";

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "index": "def_type",
      "index_id": "79ccc513f7d10dd5",
      "keyspace": "travel-sample",
      "namespace": "default",

       

      6) EXPLAIN SELECT meta().id FROM `travel-sample`
      USE INDEX(isched_c_all)
      WHERE EVERY x IN schedule SATISFIES x.flight >= "UA" END AND type = "route";

      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "index": "def_type",
      "index_id": "79ccc513f7d10dd5",
      "keyspace": "travel-sample",
      "namespace": "default",
      "spans": [

      Attachments

        Issue Links

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

          Activity

            People

              ajay.bhullar Ajay Bhullar
              Prasad.Varakur Prasad Varakur
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty