Prepared Queries With COUNT(*) Failing

SQL++
21

Yes. Just confirmed:

@couchbase01 /opt/couchbase/bin # ./couchbase-server -v
Couchbase Server 5.0.0-2564 (EE)

@couchbase02 ~ # /opt/couchbase/bin/couchbase-server -v
Couchbase Server 5.0.0-2564 (EE)

@couchbase03 ~ # /opt/couchbase/bin/couchbase-server -v
Couchbase Server 5.0.0-2564 (EE)

image.png1179×287 42 KB

22

Ta! Let me go through each operator’s code, and I’ll get back…

23

One more thing:

I made a small mistake with the query. I wrote datastoreid = $1 (wrong) instead of datastore_id = $1 (correct).
Thus the query was not using the index I expected thus the plan is a bit different.

Here is the correct query execution plan using the index.

But: the problem mentioned is still the same. Sometimes the prepared statement is not found.

PREPARE folder3 FROM SELECT COUNT(*) from datastoreitems WHERE datastore_id = $ds AND folder = $folder;
{
    "requestID": "9a8ddf43-0f0b-4c8a-bc98-7fa60f66f6fd",
    "signature": "json",
    "results": [
        {
            "encoded_plan": "H4sIAAAAAAAA/5yTUW/TPhTFv4p1/n1oJ2v6rxNMGPFQlSCQxla1RTxMVevZt6khsYPtlMIUPjty0m6UDQnx5tz7y/HJPTd3IKucJr2sCmkhAA4rS4LA2hWa/Dk4XEVeRuch7vDfwwNm9KUmqwgcP9TGFNqThbg5hkZ13Dhvvieq8mZrCsopJKlLE2KLz6XPKUJA01rWRRRaRhmi82QilQEcE2+2EGcN/3v4olk0B1//ZPyd1bQbu9rGmZJ2CA7ltuQDxE13Yv3+6vjy1elDYWn0ajA4vPUk2034mCopykfo4HTVqS04TLJ1sLe8B7HvLI1O2dFQPbvV6/+H8uLFc70Gx2f6FiqpUrKPJpYSv292YwVHqKQN7VhoJ1WEiL4mDi9tTm15Y/INBHo6tLerog7GWYhzjsJ93XdSZgew++A/wftus2gWHHUwNodAHkwr8XQyE+8+kUpmPYW6iMtIvjx4rhKrEtY/GbSyze9Ks+hJlmhXJZjcylh7StvSO4OArcvbZIgj0i6t3GSaTUbTjO1/DfZmev2ezbLLbDxn4+sPV/P+yYCtvSvZ8YjZx7fZNGO/7gZ7xXo6sNHV671aKnSnl2h+BgAA//8w4S/XlwMAAA==",
            "name": "folder3",
            "operator": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Authorize",
                        "privileges": {
                            "List": [
                                {
                                    "Priv": 1,
                                    "Target": "default:datastoreitems"
                                },
                                {
                                    "Priv": 7,
                                    "Target": "default:datastoreitems"
                                }
                            ]
                        },
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "IndexCountScan2",
                                    "covers": [
                                        "cover ((`datastoreitems`.`datastore_id`))",
                                        "cover ((`datastoreitems`.`folder`))",
                                        "cover ((meta(`datastoreitems`).`id`))"
                                    ],
                                    "index": "Index_datastore",
                                    "index_id": "fe2c5bdf02a796df",
                                    "keyspace": "datastoreitems",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "exact": true,
                                            "range": [
                                                {
                                                    "high": "$ds",
                                                    "inclusion": 3,
                                                    "low": "$ds"
                                                },
                                                {
                                                    "high": "$folder",
                                                    "inclusion": 3,
                                                    "low": "$folder"
                                                }
                                            ]
                                        }
                                    ],
                                    "using": "gsi"
                                },
                                {
                                    "#operator": "IndexCountProject",
                                    "result_terms": [
                                        {
                                            "expr": "count(*)"
                                        }
                                    ]
                                }
                            ]
                        }
                    },
                    {
                        "#operator": "Stream"
                    }
                ]
            },
            "signature": {
                "$1": "number"
            },
            "text": "PREPARE folder3 FROM SELECT COUNT(*) from datastoreitems WHERE datastore_id = $ds AND folder = $folder;"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.326416ms",
        "executionTime": "2.922082ms",
        "resultCount": 1,
        "resultSize": 4002
    }
}



cbq> EXECUTE folder3;
{
    "requestID": "decf23e8-4c03-40e6-9073-d5face4eb9fd",
    "errors": [
        {
            "code": 4040,
            "msg": "No such prepared statement: folder3"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "381.116µs",
        "executionTime": "348.918µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}
cbq> EXECUTE folder3;
{
    "requestID": "04ad8ba7-926e-4b83-8245-4f07610970e5",
    "signature": null,
    "results": [
        {
            "$1": 426108
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "293.344787ms",
        "executionTime": "293.33742ms",
        "resultCount": 1,
        "resultSize": 36
    }
}
24

Hi @synesty,

cbq shell by default connect to cluster 8091 port and the queries will be directed to different query nodes (@isha please confirm) , but the prepared statement may not be present on other nodes. So 4040 error is expected. not sure why you are getting 4070 that needs to be investigated.
To avoid 4040 with cbq shell you have two options.

  1. as part of the execute statement supply encoded_plan that gave during prepare.
  2. connect cbq shell using -e option with 8093 port of one of the query node so that all queries are directed to that query node.

Also can you try the your SDK program with corrected query/index adhoc false

25

Yes i did. The problem remains. Our query was already using the correct index. (the wrong index was just a problem (typo) when I collected debugging information for this forum post using cbq).

26

Hi @synesty,

You found a Bug. https://issues.couchbase.com/browse/MB-24623. This will be fixed in up coming refresh.

1 Like
27

@vsr1 What exactly would be the “refresh”? Beta or next Developer-Build? Do you have a rough time? We would like to install it on our cluster and test it.

Thanks

28

It will be in next 5.0.0-Beta refresh. Tentatively end of June.

1 Like