[MB-65467] Unnest query gives exceeded memory quota – Couchbase Support

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

Summary

During query execution, the Query Service tracks the memory used for each document created by UNNEST. If a filter condition removes some of those documents, they are not removed from the tracked memory running value. This leads to high reported memory usage which may cause query failures, even though the actual memory used is lower.

Symptoms

Queries containing UNNEST fail due to Request has exceeded memory quota:

        {
            "code": 5500,
            "msg": "Request has exceeded memory quota"
        }

Triggers

Query statement contains UNNEST.

Verification

Verify whether the query statement contains an UNNEST
Example used to reproduce the issue:

SELECT DISTINCT u1.*
FROM default AS d USE KEYS "k01"
UNNEST d.a1 AS u1
UNNEST u1.sa1 AS u2
WHERE u2.id = 300 AND u2.type = "doc";

Verify whether the query response contains Request has exceeded memory quota, eg:

{
    "requestID": "c1ad3dc5-4876-476a-90de-7553c3cfc861",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "sa1": [
            {
                "id": 300,
                "type": "doc"
            }
        ]
    }
    ],
    "errors": [
        {
            "code": 5500,
            "msg": "Request has exceeded memory quota"
        }
    ],
    "status": "fatal",
    "metrics": {
        "elapsedTime": "20.583569ms",
        "executionTime": "20.450423ms",
        "resultCount": 1,
        "resultSize": 119,
        "serviceLoad": 3,
        "usedMemory": 1049391882,
        "errorCount": 1
    }
}

Review query plan for affected statement (e.g., from Plan Text or completed requests).
Verify the query plan for the affected statement contains an UNNEST operator with a filter condition:

            "#operator": "Unnest",
            "#stats": {
              "#heartbeatYields": 58,
              "#itemsIn": 11561,
              "#itemsOut": 970,
              "#phaseSwitches": 25066,
              "execTime": "63.620853ms",
              "kernTime": "4.097050382s",
              "usedMemory": 83442399
            },
            "as": "atl",
            "expr": "((`obj`.`val`).`value`)",
            "filter": "(date_format_str((`atl`.`updatedOn`), \"1111-11-11\") = \"2025-11-06\")"
          },

Workarounds

Upgrade to a version of Couchbase Server containing the fix (7.6.6+)
Rewrite queries to avoid using UNNEST.

Related to

Summary

Symptoms

Triggers

Verification

Workarounds

Related articles