[MB-63414] Query may hit memory quota error with non-covered array index

Product: Capella
Component: Query-Service
Issue Link: MB-63414
Affects Versions: 7.2.x, 7.6.x
Fix Versions: 7.2.6, 7.6.4, 8.0.0

Summary

• In Couchbase Server versions 7.2.x and 7.6.x, certain N1QL queries that use non-covered composite or array indexes may fail with the Request has exceeded memory quota error.
• This occurs even when the query itself is simple and expected to use limited memory.
• The issue was traced to how the query service (cbq-engine) component manages in-memory buffers during index scans involving UNNEST or array expressions when a memory quota is configured for the session or query.

Symptoms 

• Queries that involve UNNEST or array fields fail intermittently or consistently with:

  code: 5500
  msg: "Request has exceeded memory quota"

• cbq-engine shows increased memory usage before the failure.
• Queries that do not use UNNEST or array expressions (or that are covered by an index) succeed normally.

Triggers

This issue is triggered when all of the following are true:

1. The memory quota parameter is explicitly set at the query or session level (either via SDK or Query Workbench). For example:

   "memory_quota": 268435456  

2. The query uses:
   • A non-covered array index (i.e., the index does not include all fields referenced in the query), or
   • A composite index involving array fields, or
   • A UNNEST operation over array attributes.
3. Couchbase Server version 7.2.x and 7.6.x

Under these conditions, cbq-engine can incorrectly overestimate memory use during index scan evaluation and prematurely abort the query with a memory quota exceeded error.  

Verification

1. Verify Couchbase Server version used.
2. Look for queries that:
   1. Use UNNEST or array fields.
   2. Are non-covered by the indexes used (i.e., the query references fields not included in the index)
   3. Involve composite or array indexes.
3. Look in the query parameters or SDK session for memory_quota being set.
4. Error message: "code": 5500 "msg": "Request has exceeded memory quota"

Caution: if memory_quota is 0 or unset, the error may be due to actual high memory usage, not this issue.

Workarounds

Until the fix is deployed, users can apply one of the following mitigations:

1. Disable memory quota for affected queries:

   SET query_context "default1.scope_0";
   SET memory_quota = 0;

   or disable it in the SDK by not specifying memory_quota in the query parameters.

2. Use a covered index if possible — ensure that all fields in the query are included in the index definition.
3. Rewrite the query to avoid UNNEST if the same result can be achieved via array functions or subqueries.