[MB-62631] "User does not have credentials to use sequential scans...." error from ADVISE

Product: Couchbase Server
Component: Query-Service
Issue Link: MB-62631
Affects Version: 7.6.x
Fix Versions: 7.6.4, 8.0.0

Summary

• For some queries, the Query Service would incorrectly select a sequential scan even if a suitable index exists on the affected Couchbase Server versions.
• As a result, the user may encounter an unexpected User does not have credentials to use sequential scans error.
• This issue has been observed particularly with ADVISE statements and queries involving JOIN operations.

Symptoms 

• Queries unexpectedly fail with the User does not have credentials to use sequential scans error.

{
    "code": 13014,
    "msg": "User does not have credentials to use sequential scans. Add role query_use_sequential_scans on default:default.s3.c2 to allow the statement to run.",
    "reason": {
        "missing_role": "query_use_sequential_scans",
        "path": "default:default.s3.c2",
        "recommended_action": "Run the index advisor and create an appropriate index, or check that your expected index is online."
    }
}

Triggers

• An issue has been observed if the query contains an ADVISE statement or a JOIN.

Verification

Try one of the 3 possible workarounds and verify whether this resolves the issue.

Workarounds

There are 3 possible workarounds.

1. (Recommended) Add the ORDERED hint to ensure the joins are executed in the order they’re written.
   For example, rewrite the following:

SELECT META(col1).id key1, META(col2).id key2
FROM test.`_default`.col1 
   LEFT JOIN test.`_default`.col2 ON col1.billRun.id = col2.id
WHERE col1.billRun.id = "1"

as

SELECT /*+ ORDERED */ META(col1).id key1, META(col2).id key2
FROM test.`_default`.col1 
   LEFT JOIN test.`_default`.col2 ON col1.billRun.id = col2.id
WHERE col1.billRun.id = "1"

2. Disable join enumeration using the N1QL Feature Controller.

Take the current value of the N1QL Feature Controller from the UI:

The Join enumeration bit (N1QL_JOIN_ENUMERATION) is 0x0000000400 (hex).

To find the new value, combine the current controller value with the join enumeration bit (0x0000000400):

echo $(( <current-value> | 0x0000000400 ))

Eg:

echo $(( 76 | 0x0000000400 ))
1100

If the default value of 76 is present, the new value should be 1100.

Set the N1QL Feature Controller to the new value to disable the specified setting (N1QL_JOIN_ENUMERATION). Now run the impacted query.

Join enumeration is the process by which the query planner determines the best way to execute join operations in a query. It involves deciding the order of joins, which indexes to use, and how to combine results from different data sources. Join enumeration is the process by which the query planner determines the best way to execute join operations in a query. It involves deciding the order of joins, which indexes to use, and how to combine results from different data sources. When we disable join enumeration, we instruct the query engine not to perform its default optimizations for join operations. This can lead to the following outcomes:

• Control Over Execution: It can provide more predictable performance characteristics, as the execution plan won’t change based on query engine optimizations.
• Debugging: Disabling join enumeration can be useful for debugging complex queries to understand how they are executed without the influence of the query planner's optimizations.

N1QL_JOIN_ENUMERATION refers to a specific query engine setting that controls how join operations are handled during query execution.

3. Disable CBO (cost-based optimisation).