[BP to 7.1.6-MP1 & 7.2.3-MP1]Subqueries not stopping and uses all resource

Not sure if we able to fix this extensive changes.

Nested CTE queries comes in same category. i.e top level cte terminates. But inside CTE expensive currently no mechanism pass those stop/cancel deep recursively

repro:

CREATE INDEX ix1 ON default(c1);

INSERT INTO default  (KEY _k, VALUE _v) SELECT  "k0"||TO_STR(d) AS _k , {"c1":d, "c2":2*d, "c3":3*d} AS _v FROM ARRAY_RANGE(1,1000) AS d;

\set -timeout "5s";

WITH aa AS (WITH a1 AS (SELECT RAW COUNT(l.c2)

                        FROM default AS l

                        JOIN default AS r ON l.c1 < r.c1

                        JOIN default r1 ON r.c1 < r1.c1

                        WHERE l.c1 >= 0)

            SELECT a1)

SELECT aa;

Above query uses Subquery in WITH

This expensive query of 1K JOIN 1K JOIN 1K i.e 1Billion, All uses NL

top level query terminated by timeout 5s or canceled or deleted from active request

pre 7.6.0 there is no way can terminate subquery evaluation until it finished or error out. This causes huge resource consumption.

After it timedout or 5s watch CPU elevated long long time , also look Index it requests keep happening (UI ==> Index tab===>look ix1). 

NOTE: r, r1 uses covering so you will not see fetch count.

This has been handled (fixed by) MB-33983, MB-54277  introduce per operator context to be able to stop expression evaluations and suspend calling operator

https://review.couchbase.org/c/query/+/182328

FYI: In 7.6 subqueries stopped and CPU is normal/no index scan. 

In 7.6 following seems work fine.

CREATE OR REPLACE FUNCTION f5() {

(WITH aa AS (WITH a1 AS (SELECT RAW COUNT(l.c2)

                        FROM default AS l

                        JOIN default AS r ON l.c1 < r.c1

                        JOIN default r1 ON r.c1 < r1.c1

                        WHERE l.c1 >= 0)

            SELECT a1)

SELECT aa )

};

\set -timeout "30s";

SELECT f5();