Correlated subquery over a UNION ALL of EXECUTE ON COORDINATOR and EXECUTE ON ALL SEGMENTS functions fails with "illegal rescan of motion node"

Aperta
  • Qualcosa non va?
#2,257 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 2 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
55/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
c, postgresql, sql
Ambito
backend, databases, distributed-systems

Direzione di ricerca

Start by tracing the PROEXECLOCATION_ALL_SEGMENTS check in pathnode.c and how function scans under an Append are handled for correlated subqueries. Reproduce with the SQL setup in the issue, using both planner modes. Done when the query is rejected at plan time with the existing error or the segment branch is safely materialized so multiple outer rows work.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Apache Cloudberry version

Reproduced on both main (c781604c5ba) and REL_2_STABLE (11e858bc127), with ORCA and with optimizer = off.

What happened

When an EXECUTE ON ALL SEGMENTS function is used on its own inside a subquery that references the outer query, the planner rejects it with a clear error:

ERROR:  cannot execute EXECUTE ON ALL SEGMENTS function in a subquery with arguments from outer query

But if the same function is combined with an EXECUTE ON COORDINATOR function through UNION ALL (for example, in a view), that check no longer fires. The plan is built anyway, and as soon as the outer query produces more than one row, execution fails with an internal error:

ERROR:  illegal rescan of motion node: invalid plan (nodeMotion.c:1368)
HINT:  Likely caused by bad NL-join, try setting enable_nestloop to off

The hint is misleading: the query has nothing to do with a nested-loop join.

How to reproduce
CREATE FUNCTION f_coord() RETURNS SETOF int AS $$ SELECT 1 $$
  LANGUAGE sql EXECUTE ON COORDINATOR;
CREATE FUNCTION f_segs()  RETURNS SETOF int AS $$ SELECT 1 $$
  LANGUAGE sql EXECUTE ON ALL SEGMENTS;
CREATE VIEW v AS
  SELECT * FROM f_coord() x UNION ALL SELECT * FROM f_segs() x;

CREATE TABLE t (id int) DISTRIBUTED REPLICATED;
INSERT INTO t VALUES (1), (2);

-- 1. Segment function alone: correctly rejected at plan time
SELECT id, (SELECT count(*) FROM f_segs() x WHERE x = t.id) FROM t;
-- ERROR:  cannot execute EXECUTE ON ALL SEGMENTS function in a subquery with arguments from outer query

-- 2. Wrapped in UNION ALL: the check is bypassed and execution fails
SELECT id, (SELECT count(*) FROM v WHERE x = t.id) FROM t;
-- ERROR:  illegal rescan of motion node: invalid plan (nodeMotion.c:1368)

-- 3. With a single outer row no rescan is needed, and it succeeds
SELECT id, (SELECT count(*) FROM v WHERE x = t.id) FROM t WHERE id = 1;

-- 4. The equivalent join works
SELECT t.id, count(v.x) FROM t LEFT JOIN v ON v.x = t.id GROUP BY 1;

The LATERAL form fails the same way as query 2.

Plan for query 2 (optimizer = off):

 Gather Motion 1:1  (slice1; segments: 1)
   ->  Seq Scan on t
         SubPlan 1
           ->  Aggregate
                 ->  Result
                       Filter: (x.x = t.id)
                       ->  Append
                             ->  Function Scan on f_coord x
                             ->  Gather Motion 3:1  (slice2; segments: 3)
                                   ->  Function Scan on f_segs x_1

The correlated SubPlan contains a Gather Motion. Every additional outer row rescans the SubPlan, and a Motion cannot be rescanned.

What you think should happen instead

Either of these:

  • reject the query at plan time with the same cannot execute ... in a subquery with arguments from outer query error that the bare segment function gets; or
  • materialize that branch inside the SubPlan so the query runs correctly.

The built-in gp_stat_activity view has the same "segments UNION ALL coordinator" shape, but its segment side uses gp_dist_random(), and it works correctly in the same kind of correlated subquery. So the semantics themselves are supportable.

Anything else

Our guess, not yet confirmed by reading the code closely: the PROEXECLOCATION_ALL_SEGMENTS check in pathnode.c only applies when the function scan is the direct body of the subquery, and is missed when the function scan sits under an Append.

Combining a coordinator-side function and a segment-side function with UNION ALL is a common way for extensions to expose cluster-wide statistics. With such a view, a routine monitoring query that looks up stats for each row of pg_class hits this error.

Lingua principale
C
Stelle
1.4k
Fork
268
Merge medio
4g 3h
PR unite (30g)
78

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di apache/cloudberry

Tutte le issue di apache/cloudberry

Issue simili

Altre issue su C

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.