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

N1QL: Covered query with meta().type in where clause not giving correct results

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Fixed
    • Critical
    • 5.0.0
    • 4.5.0
    • query
    • 4.5.0-1848

    Description

      Index created:
      CREATE INDEX `idxcover` ON `default`((meta(`default`).`type`),`name`)

      select query:
      explain SELECT count(meta(default).type) FROM default where meta(default).type = "json" ;'
      this uses the right index:

      [
      {
      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "covers": [
      "cover ((meta(`default`).`type`))",
      "cover ((`default`.`name`))",
      "cover ((meta(`default`).`id`))"
      ],
      "index": "idxcover",
      "keyspace": "default",
      "namespace": "default",
      "spans": [
      {
      "Range":

      { "High": [ "successor(\"json\")" ], "Inclusion": 1, "Low": [ "\"json\"" ] }

      }
      ],
      "using": "gsi"
      },
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [

      { "#operator": "Filter", "condition": "(cover ((meta(`default`).`type`)) = \"json\")" }

      ,

      { "#operator": "InitialGroup", "aggregates": [ "count(cover ((meta(`default`).`type`)))" ], "group_keys": [] }

      ]
      }
      },

      { "#operator": "IntermediateGroup", "aggregates": [ "count(cover ((meta(`default`).`type`)))" ], "group_keys": [] }

      ,

      { "#operator": "FinalGroup", "aggregates": [ "count(cover ((meta(`default`).`type`)))" ], "group_keys": [] }

      ,
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "InitialProject",
      "result_terms": [

      { "expr": "count(cover ((meta(`default`).`type`)))" }

      ]
      },

      { "#operator": "FinalProject" }

      ]
      }
      }
      ]
      },
      "text": "SELECT count(meta(default).type) FROM default where meta(default).type = \"json\""
      }
      ]

      Result of
      SELECT count(meta(default).type) FROM default where meta(default).type = "json" ;

      [

      { "$1": 0 }

      ]

      Though same query gives correct result with primary index:
      SELECT meta(default).type FROM default use index(`#primary`) where meta(default).type = "json" ;

      gives 1680 documents.

      Attachments

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

        Activity

          People

            Prerna.Manaktala Prerna Manaktala (Inactive)
            Prerna.Manaktala Prerna Manaktala (Inactive)
            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