Meta().cas index issue

Couchbase Server
1

Having an issue when trying to query by meta().cas.

If I do the following query:
SELECT d.* FROM bucket_name d WHERE meta().cas < some_cas_number limit 2;

And all I have is the primary index, it works fine and I get results.

If I do the same query after making the index:
CREATE INDEX meta_cas_index ON bucket_name(meta().cas);

I never get results when I query using meta().cas.

Looking at the explain, query is using the next index that I created.

Unsure what is wrong with the index that I created.

2

CAS is int64 , JSON number (float64) high values might loose precision
You might hitting. MB-24464, MB-29393

3

Yeah, it is definitely matching MB-29393 very closely. Queries work with primary index only but only using primary index is choking at a large number of documents.

With an index created, nothing gets returned when I use meta().cas in the query.

SELECT d.* FROM bucket_name d WHERE meta().cas != some_cas_number limit 2;

4

Can you post the EXPLAIN. Not sure about your use case for doing filter on CAS. CAS changes each time mutate document and it random.

!= should at least give the results. Can u check UI and see how many entries in the index.
try covered query.
SELECT META(d).id, META(d).cas
FROM bucket_name d WHERE meta(d).cas != some_cas_number limit 2;

5

Sure:

{
    "requestID": "997c9aa7-6d64-497b-9dcd-8a6270cb65f3",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "DistinctScan",
                                "scan": {
                                    "#operator": "IndexScan",
                                    "index": "migration_meta_cas_index",
                                    "index_id": "ebcd6cf4b9c3756",
                                    "keyspace": "bucket_name",
                                    "limit": "100",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "Range": {
                                                "High": [
                                                    "0"
                                                ],
                                                "Inclusion": 0,
                                                "Low": [
                                                    "null"
                                                ]
                                            }
                                        },
                                        {
                                            "Range": {
                                                "Inclusion": 0,
                                                "Low": [
                                                    "0"
                                                ]
                                            }
                                        }
                                    ],
                                    "using": "gsi"
                                }
                            },
                            {
                                "#operator": "Fetch",
                                "as": "d",
                                "keyspace": "bucket_name",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "Filter",
                                            "condition": "(not ((meta(`d`).`cas`) = 0))"
                                        },
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "`d`",
                                                    "star": true
                                                }
                                            ]
                                        },
                                        {
                                            "#operator": "FinalProject"
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    {
                        "#operator": "Limit",
                        "expr": "100"
                    }
                ]
            },
            "text": "SELECT d.* FROM `bucket_name` d WHERE meta().cas != 0 limit 100"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.921423ms",
        "executionTime": "2.874206ms",
        "resultCount": 1,
        "resultSize": 3730
    }
}

In terms of use-case, I am keeping track of the last checked meta().cas and do something like:
SELECT d.* FROM bucket_name where meta().cas > “last cas number” to find all the updated and added documents since we last checked.

Can u check UI and see how many entries in the index

Sorry, unfamiliar with the UI. How would I do this?

We are using Couchbase Server 4.5 if this is relevant.

6

CB 4.5 relevant. Before CB 4.6 create index on META().cas is allowed with out really indexing. MB-16772. In 4.6 it is fixed by giving error when META().cas is indexed.
In CB 5.0.0 started full support of META().cas in index and query MB-21971

You required 5.0.0 or higher

7

Awesome. That explains a lot.

Thank you very much!