Query gives duplicate result items for same document

SQL++
1

Hi!

We have an index that is indexed on meta().id where the
document key has a specific prefix, typically like:

CREATE INDEX my_index ON mybucket (meta().id, fieldA, fieldB) WHERE meta().id LIKE "A:%"

We query this index occasionally with a query like:

SELECT meta().id, fieldA, fieldB FROM mybucket WHERE meta().id LIKE "A:%"

At one point in time, in one incarnation of an installation we experienced an
unexpected result using the above query giving us at least two items in the
result set having the same meta().id but, at least, one of the other keys had
different values. Leading us to believe that maybe there was an (for us) unwanted
entry somehow remaining in the couchbase index.

To clarify, the query response contained something like:

[
  ...
  {
    "id": "A:abc",
    "fieldA": "spookie",
    "fieldB": 0
  },
  {
    "id": "A:abc",
    "fieldA": "spookie",
    "fieldB": 5
  }
]

We have only encountered this behaviour once that we know of and we have not
been able to recreate it since.

Couchbase 5.0.1 community edition, one node and one bucket. We have other
indexes, however non that includes any of the documents also in the index above.

Does anyone know if this is even possible and if so under which circumstances
would/could this state appear?

2

Post the explain and also try with latest versions.
Check what index it used and see if any mutations are pending. If required drop that index and re-create.
This query should have used primary index and this should not have possible.

cc @deepkaran.salooja

3

Thanks for your reply! As I said we have not been able to recreate this state again, but we need to know if it is something that we should account for happening, but it seems like we shouldn’t have to then. The couchbase installation where we encountered this is long gone i’m afraid, but now we know that if it happens again there is something fishy going on… (And then we will see if any mutations are pending and dig into the logs e.t.c.)

We do not make use of a primary index by the way.

Thanks again!

The query explain:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "covers": [
          "cover ((meta(`mybucket`).`id`))",
          "cover ((`mybucket`.`fieldA`))",
          "cover ((`mybucket`.`fieldB`))",
          "cover ((meta(`mybucket`).`id`))"
        ],
        "filter_covers": {
          "cover ((\"A:\" <= (meta(`mybucket`).`id`)))": true,
          "cover (((meta(`mybucket`).`id`) < \"A;\"))": true,
          "cover (((meta(`mybucket`).`id`) like \"A:%\"))": true
        },
        "index": "my_index",
        "index_id": "4959751c38c88e92",
        "index_projection": {
          "entry_keys": [
            1,
            2
          ],
          "primary_key": true
        },
        "keyspace": "mybucket",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"A;\"",
                "inclusion": 1,
                "low": "\"A:\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "cover (((meta(`mybucket`).`id`) like \"A:%\"))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "cover ((meta(`mybucket`).`id`))"
                },
                {
                  "expr": "cover ((`mybucket`.`fieldA`))"
                },
                {
                  "expr": "cover ((`mybucket`.`fieldB`))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT meta().id, fieldA, fieldB\nFROM mybucket\nWHERE meta().id LIKE 'A:%';"
}
4

Normally this shouldn’t have happened.