Empty result objects when using covering index for double unnest query

SQL++
,
1

Hey,
I’m struggling with empty result objects on a query that worked before creating a covering index via index advisor.
I’m using Enterprise Edition 7.0.2.
The data has the following structure:

{
  "MINiML": {
    "Sample": [
      {
        "iid": "GSM2827678",
        "Channel": {
          "position": 1,
          "Characteristics": [
            {
              "tag": "strain",
              "$t": "AB strain"
            },
            {
              "tag": "treatment_raw",
              "$t": "control"
            }
          ]
        }
      },
      {
        "iid": "GSM2827679",
        "Channel": {
          "position": 2,
          "Characteristics": [
            {
              "tag": "strain",
              "$t": "CD strain"
            },
            {
              "tag": "treatment_raw",
              "$t": "control"
            }
          ]
        }
      }
    ]
  }
}

And I’m running the following query:

SELECT s.iid
FROM `test-data` AS t 
UNNEST t.MINiML.Sample AS s
UNNEST s.Channel.Characteristics AS c
WHERE c.tag="strain"

which returns:

[
  {
    "iid": "GSM2827678"
  },
  {
    "iid": "GSM2827679"
  }
]

If i now create a covering index recommended by the index advisor like so:

`CREATE INDEX adv_ALL_MINiML_Sample_Channel_Characteristics_tag ON `test-data`(ALL ARRAY (ALL ARRAY `c`.`tag` FOR c IN (`s`.`Channel`).`Characteristics` END) FOR s IN (`MINiML`).`Sample` END)`

and run the same query again using the index the two result objects are empty, why is that?

Here is the corresponding plan text:

{
  "#operator": "Authorize",
  "#stats": {
    "#phaseSwitches": 4,
    "execTime": "1.928µs",
    "servTime": "1.557968ms"
  },
  "privileges": {
    "List": [
      {
        "Target": "default:test-data",
        "Priv": 7,
        "Props": 0
      }
    ]
  },
  "~child": {
    "#operator": "Sequence",
    "#stats": {
      "#phaseSwitches": 2,
      "execTime": "1.293µs"
    },
    "~children": [
      {
        "#operator": "IndexScan3",
        "#stats": {
          "#heartbeatYields": 62,
          "#itemsOut": 1824,
          "#phaseSwitches": 7299,
          "execTime": "6.170245ms",
          "kernTime": "1.895076ms",
          "servTime": "5.086731ms"
        },
        "as": "t",
        "covers": [
          "cover ((`c`.`tag`))",
          "cover ((meta(`t`).`id`))"
        ],
        "filter": "cover (is_array(((`t`.`MINiML`).`Sample`)))",
        "filter_covers": {
          "cover ((((`s`.`Channel`).`Characteristics`) < {}))": true,
          "cover ((((`t`.`MINiML`).`Sample`) < {}))": true,
          "cover (([] <= ((`s`.`Channel`).`Characteristics`)))": true,
          "cover (([] <= ((`t`.`MINiML`).`Sample`)))": true,
          "cover (is_array(((`s`.`Channel`).`Characteristics`)))": true,
          "cover (is_array(((`t`.`MINiML`).`Sample`)))": true
        },
        "index": "adv_ALL_MINiML_Sample_Channel_Characteristics_tag",
        "index_id": "99c3ec72c6256bac",
        "keyspace": "test-data",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"strain\"",
                "inclusion": 3,
                "low": "\"strain\""
              }
            ]
          }
        ],
        "using": "gsi",
        "#time_normal": "00:00.011",
        "#time_absolute": 0.011256976000000002
      },
      {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 2,
          "execTime": "13.991728ms",
          "kernTime": "295ns",
          "state": "running"
        },
        "~children": [
          {
            "#operator": "Filter",
            "#stats": {
              "#heartbeatYields": 33,
              "#itemsIn": 1824,
              "#itemsOut": 1824,
              "#phaseSwitches": 7300,
              "execTime": "1.125589ms",
              "kernTime": "12.063502ms"
            },
            "condition": "(cover ((`c`.`tag`)) = \"strain\")",
            "#time_normal": "00:00.001",
            "#time_absolute": 0.001125589
          },
          {
            "#operator": "InitialProject",
            "#stats": {
              "#itemsIn": 1824,
              "#itemsOut": 1824,
              "#phaseSwitches": 5478,
              "execTime": "3.429319ms",
              "kernTime": "8.474387ms"
            },
            "result_terms": [
              {
                "expr": "(`s`.`iid`)"
              }
            ],
            "#time_normal": "00:00.003",
            "#time_absolute": 0.003429319
          }
        ],
        "#time_normal": "00:00.013",
        "#time_absolute": 0.013991728
      },
      {
        "#operator": "Stream",
        "#stats": {
          "#itemsIn": 1824,
          "#itemsOut": 1824,
          "#phaseSwitches": 1826,
          "execTime": "1.021547ms"
        },
        "#time_normal": "00:00.001",
        "#time_absolute": 0.001021547
      }
    ],
    "#time_normal": "00:00.000",
    "#time_absolute": 0.000001293
  },
  "~versions": [
    "7.0.2-N1QL",
    "7.0.2-6703-enterprise"
  ],
  "#time_normal": "00:00.001",
  "#time_absolute": 0.001559896
}

Any help would be appreciated, thanks!

2

It is double nested array. Query should not have covered because it did not have s.iid in the index (change index ALL to DISTINCT so that it will do fetch and will not cover). It is bug (it handle one nested array only), in 7.1.0 as part of multiple array fields indexing this has been addressed.

Also Try this.

CREATE INDEX ix11 ON `test-data`(ALL ARRAY (ALL ARRAY [`c`.`tag`,s.iid] FOR c IN (`s`.`Channel`).`Characteristics` END) FOR s IN (`MINiML`).`Sample` END)`;


SELECT fltr[1] AS iid
FROM `test-data` AS t
UNNEST t.MINiML.Sample AS s
UNNEST s.Channel.Characteristics AS c
LET fltr = [`c`.`tag`,s.iid]
WHERE fltr >= ["strain"] AND fltr < [SUCCESSOR("strain")] ;
1 Like
3

Awesome it’s working, thank you very much!