This is an automated email from the ASF dual-hosted git repository.
leborchuk pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new f43741b804c orca: fallback to Postgres optimizer on cross-slice
replicated CTE Consumer (#1719)
f43741b804c is described below
commit f43741b804c07775ed06d267e3b6911064b23971
Author: Alena Rybakina <[email protected]>
AuthorDate: Tue May 12 13:13:00 2026 +0300
orca: fallback to Postgres optimizer on cross-slice replicated CTE Consumer
(#1719)
* orca: fallback to Postgres optimizer on cross-slice replicated CTE
Consumer.
Inspired by greengage 51fe92e: before Expr->DXL translation,
walk the physical tree and track which slice each CTE Producer
and Consumer lives on. If a Consumer is on a different slice
than its Producer and the Producer's distribution is replicated,
force a fallback to the Postgres optimizer.
The replicated filter is essential: ordinary cross-slice CTE plans
(non-replicated Producer with Gather/Redistribute Consumer) are a
normal ORCA pattern and must not trigger fallback.
51fe92e doesn't trigger when a CTE over a replicated table is
referenced from a scalar subquery, so the query hangs. This commit
replaces the single-point check with a whole-tree walker that
catches both cases.
Tests: shared_scan adds a scalar-subquery reproducer guarded by
statement_timeout. qp_orca_fallback adds two cases over a replicated
CTE: a scalar-subquery form that triggers the walker (the hang case
51fe92e missed -- fallback to Postgres), and the original 51fe92e JOIN
form where ORCA emits a safe plan with a One-Time Filter
(gp_execution_segment() = N) and the walker correctly stays silent
(guards against false positives).
(cherry picked from commit
https://github.com/open-gpdb/gpdb/commit/3a9aebfe3afb27dd060c16b5cfa7932e7a2ed43f)
---
.../gporca/libgpopt/include/gpopt/base/CUtils.h | 3 +
src/backend/gporca/libgpopt/src/base/CUtils.cpp | 124 +++++++++++++++++++++
.../src/translate/CTranslatorExprToDXL.cpp | 26 +++++
src/test/regress/expected/qp_orca_fallback.out | 86 ++++++++++++++
.../expected/qp_orca_fallback_optimizer.out | 99 ++++++++++++++++
src/test/regress/expected/shared_scan.out | 33 ++++++
.../regress/expected/shared_scan_optimizer.out | 28 +++++
src/test/regress/sql/qp_orca_fallback.sql | 47 ++++++++
src/test/regress/sql/shared_scan.sql | 28 +++++
9 files changed, 474 insertions(+)
diff --git a/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
b/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
index 1e873e14847..0aded041e00 100644
--- a/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
+++ b/src/backend/gporca/libgpopt/include/gpopt/base/CUtils.h
@@ -1026,6 +1026,9 @@ public:
static CTableDescriptorHashSet *RemoveDuplicateMdids(
CMemoryPool *mp, CTableDescriptorHashSet *tabdescs);
+ static BOOL FHasCrossSliceReplicatedCTEConsumer(CMemoryPool *mp,
+
CExpression *pexpr);
+
static CExpression *ReplaceColrefWithProjectExpr(CMemoryPool *mp,
CExpression *pexpr,
CColRef *pcolref,
diff --git a/src/backend/gporca/libgpopt/src/base/CUtils.cpp
b/src/backend/gporca/libgpopt/src/base/CUtils.cpp
index b5f847b817a..4776c602fcd 100644
--- a/src/backend/gporca/libgpopt/src/base/CUtils.cpp
+++ b/src/backend/gporca/libgpopt/src/base/CUtils.cpp
@@ -978,6 +978,130 @@ CUtils::FHasCTEAnchor(CExpression *pexpr)
return false;
}
+// True if the distribution is replicated-like.
+static BOOL
+FReplicatedLikeDistribution(CDistributionSpec::EDistributionType edt)
+{
+ return (CDistributionSpec::EdtStrictReplicated == edt ||
+ CDistributionSpec::EdtTaintedReplicated == edt ||
+ CDistributionSpec::EdtUniversal == edt);
+}
+
+struct SCTEInfo
+{
+ ULONG cteId;
+ ULONG sliceId;
+
+ SCTEInfo(ULONG cte_id, ULONG slice_id) : cteId(cte_id),
sliceId(slice_id)
+ {
+ }
+};
+
+typedef CDynamicPtrArray<SCTEInfo, CleanupDelete<SCTEInfo> > CTEInfoArray;
+
+// Walk the physical tree, recording the slice id of every replicated
+// CTE Producer and every CTE Consumer. Slices are delimited by Motion
+// nodes: each non-scalar child of a Motion lives in a fresh slice --
+// same motId-stack idea as in apply_shareinput_xslice.
+static void
+CollectCTESlices(CMemoryPool *mp, CExpression *pexpr, ULONG curSlice,
+ ULONG *pNextSlice, CTEInfoArray *prodInfos,
+ CTEInfoArray *consInfos)
+{
+ GPOS_CHECK_STACK_SIZE;
+ GPOS_ASSERT(nullptr != pexpr);
+
+ COperator *pop = pexpr->Pop();
+
+ if (COperator::EopPhysicalCTEProducer == pop->Eopid())
+ {
+ // Producer's distribution comes from its only child -- inspect
+ // it there. Skip non-replicated Producers; they cannot trigger
+ // the cross-slice issue we are checking for.
+ GPOS_ASSERT(1 == pexpr->Arity());
+ CExpression *pexprChild = (*pexpr)[0];
+ CDrvdPropPlan *pdpplan =
+ CDrvdPropPlan::Pdpplan(pexprChild->PdpDerive());
+
+ if (FReplicatedLikeDistribution(pdpplan->Pds()->Edt()))
+ {
+ prodInfos->Append(GPOS_NEW(mp) SCTEInfo(
+
CPhysicalCTEProducer::PopConvert(pop)->UlCTEId(), curSlice));
+ }
+ }
+ else if (COperator::EopPhysicalCTEConsumer == pop->Eopid())
+ {
+ // Consumer is a leaf -- record (cteId, curSlice) and let the
+ // caller decide later, once the whole tree has been walked.
+ consInfos->Append(GPOS_NEW(mp) SCTEInfo(
+ CPhysicalCTEConsumer::PopConvert(pop)->UlCTEId(),
curSlice));
+ }
+
+ BOOL isMotion = CUtils::FPhysicalMotion(pop);
+
+ for (ULONG ul = 0; ul < pexpr->Arity(); ul++)
+ {
+ CExpression *pexprChild = (*pexpr)[ul];
+
+ if (pexprChild->Pop()->FScalar())
+ {
+ continue;
+ }
+
+ ULONG childSlice = curSlice;
+ if (isMotion)
+ {
+ (*pNextSlice)++;
+ childSlice = *pNextSlice;
+ }
+
+ CollectCTESlices(mp, pexprChild, childSlice, pNextSlice,
prodInfos,
+ consInfos);
+ }
+}
+
+static BOOL
+FFoundCrossSlice(const CTEInfoArray *consInfos, const CTEInfoArray *prodInfos)
+{
+ for (ULONG ic = 0; ic < consInfos->Size(); ic++)
+ {
+ SCTEInfo *cons = (*consInfos)[ic];
+
+ for (ULONG ip = 0; ip < prodInfos->Size(); ip++)
+ {
+ SCTEInfo *prod = (*prodInfos)[ip];
+ if (prod->cteId == cons->cteId && prod->sliceId !=
cons->sliceId)
+ {
+ return true;
+ }
+ }
+ }
+ return false;
+}
+
+BOOL
+CUtils::FHasCrossSliceReplicatedCTEConsumer(CMemoryPool *mp, CExpression
*pexpr)
+{
+ if (NULL == pexpr)
+ {
+ return false;
+ }
+
+ CTEInfoArray *prodInfos = GPOS_NEW(mp) CTEInfoArray(mp);
+ CTEInfoArray *consInfos = GPOS_NEW(mp) CTEInfoArray(mp);
+ ULONG nextSlice = 0;
+
+ CollectCTESlices(mp, pexpr, 0 /*curSlice*/, &nextSlice, prodInfos,
+ consInfos);
+
+ BOOL cross = FFoundCrossSlice(consInfos, prodInfos);
+
+ prodInfos->Release();
+ consInfos->Release();
+
+ return cross;
+}
+
//---------------------------------------------------------------------------
// @class:
// CUtils::FHasSubqueryOrApply
diff --git a/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp
b/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp
index 6119e2ba71f..6bc89993008 100644
--- a/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp
+++ b/src/backend/gporca/libgpopt/src/translate/CTranslatorExprToDXL.cpp
@@ -265,6 +265,20 @@ CTranslatorExprToDXL::PdxlnTranslate(CExpression *pexpr,
GPOS_ASSERT(nullptr == m_pdpplan);
+ // Walk the physical tree and detect a CTE Consumer placed on a
+ // different slice than its Producer when the Producer's output is
+ // replicated-like (StrictReplicated/TaintedReplicated/Universal).
+ // Fall back to the Postgres optimizer if it is detected because
+ // it breaks Producer-Consumer locality and can hang the
+ // query at execution.
+ if (CUtils::FHasCrossSliceReplicatedCTEConsumer(m_mp, pexpr))
+ {
+ GPOS_RAISE(
+ gpopt::ExmaDXL, gpopt::ExmiExpr2DXLUnsupportedFeature,
+ GPOS_WSZ_LIT(
+ "CTE Consumer placed on a different slice than
its replicated Producer"));
+ }
+
m_pdpplan = CDrvdPropPlan::Pdpplan(pexpr->PdpDerive());
m_pdpplan->AddRef();
@@ -4250,6 +4264,18 @@ CTranslatorExprToDXL::BuildScalarSubplans(
{
const ULONG size = pdrgpcrInner->Size();
+ // Fallback to Postgres optimizer if the SubPlan's inner expression
contains a
+ // CTE Consumer placed on a different slice than its replicated
Producer.
+ // Such a Consumer becomes a cross-slice Shared Scan reader without a
local
+ // Producer, which can hang the query or fail at execution time.
+ if (CUtils::FHasCrossSliceReplicatedCTEConsumer(m_mp, pexprInner))
+ {
+ GPOS_RAISE(
+ gpopt::ExmaDXL, gpopt::ExmiExpr2DXLUnsupportedFeature,
+ GPOS_WSZ_LIT(
+ "CTE Consumer placed on a different slice than
its replicated Producer"));
+ }
+
CDXLNodeArray *pdrgpdxlnInner = GPOS_NEW(m_mp) CDXLNodeArray(m_mp);
for (ULONG ul = 0; ul < size; ul++)
{
diff --git a/src/test/regress/expected/qp_orca_fallback.out
b/src/test/regress/expected/qp_orca_fallback.out
index 37378ef698f..e8e42370758 100644
--- a/src/test/regress/expected/qp_orca_fallback.out
+++ b/src/test/regress/expected/qp_orca_fallback.out
@@ -293,6 +293,92 @@ SELECT * FROM jsonb_array_elements('["b", "a"]'::jsonb)
WITH ORDINALITY;
"a" | 2
(2 rows)
+-- The walker that detects a CTE Consumer on a different slice than its
+-- replicated Producer. Without it ORCA would emit a plan with cross-slice
+-- replicated CTE Consumers that hangs at execution.
+-- start_ignore
+DROP TABLE IF EXISTS tbl1, tbl2;
+NOTICE: table "tbl1" does not exist, skipping
+NOTICE: table "tbl2" does not exist, skipping
+-- end_ignore
+CREATE TABLE tbl2 (id numeric, refrcode varchar(255), referenceid numeric)
+DISTRIBUTED REPLICATED;
+CREATE TABLE tbl1 (id bigserial, iscalctrg varchar(15) NOT NULL,
+ iscalcdetail varchar(15))
+DISTRIBUTED REPLICATED;
+-- start_ignore
+INSERT INTO tbl2 SELECT i, 'A'||(i%5), 101991
+ FROM generate_series(1, 50000) i;
+INSERT INTO tbl1 (iscalctrg, iscalcdetail)
+ SELECT 'A'||(i%5), 'A'||(i%7) FROM generate_series(1, 50000) i;
+ANALYZE tbl1;
+ANALYZE tbl2;
+-- end_ignore
+-- Case 1: walker triggers fallback. With scalar subqueries on the CTE
+-- ORCA produces a plan whose CTE Producer is replicated and Consumers
+-- live on a different slice -- the walker raises ExmiExpr2DXLUnsupported
+-- and trace_fallback DETAIL says "CTE Consumer placed on a different
+-- slice than its replicated Producer".
+EXPLAIN (COSTS OFF)
+WITH t2 AS (SELECT id, refrcode FROM tbl2 WHERE referenceid = 101991)
+SELECT p.iscalctrg,
+ (SELECT refrcode FROM t2 WHERE refrcode = p.iscalctrg LIMIT 1) AS r,
+ (SELECT refrcode FROM t2 WHERE refrcode = p.iscalcdetail LIMIT 1) AS r1
+FROM tbl1 p
+LIMIT 1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Gather Motion 1:1 (slice1; segments: 1)
+ -> Limit
+ -> Seq Scan on tbl1 p
+ SubPlan 1
+ -> Limit
+ -> Result
+ Filter: ((tbl2.refrcode)::text =
(p.iscalctrg)::text)
+ -> Materialize
+ -> Seq Scan on tbl2
+ Filter: (referenceid =
'101991'::numeric)
+ SubPlan 2
+ -> Limit
+ -> Result
+ Filter: ((tbl2_1.refrcode)::text =
(p.iscalcdetail)::text)
+ -> Materialize
+ -> Seq Scan on tbl2 tbl2_1
+ Filter: (referenceid =
'101991'::numeric)
+ Optimizer: Postgres query optimizer
+(18 rows)
+
+-- Case 2: walker correctly stays silent. The same CTE referenced from a
+-- JOIN: ORCA pins the Producer body to a single segment with a One-Time
+-- Filter (gp_execution_segment() = N), so the Producer's child
+-- distribution is EdtSingleton, not replicated -- the walker skips it.
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM tbl1),
+ t2 AS (SELECT id, refrcode FROM tbl2 WHERE referenceid = 101991)
+SELECT p.* FROM t1 p
+ JOIN t2 r ON p.iscalctrg = r.refrcode
+ JOIN t2 r1 ON p.iscalcdetail = r1.refrcode
+LIMIT 1;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Gather Motion 1:1 (slice1; segments: 1)
+ -> Limit
+ -> Hash Join
+ Hash Cond: ((tbl1.iscalcdetail)::text = (r1.refrcode)::text)
+ -> Hash Join
+ Hash Cond: ((tbl2.refrcode)::text =
(tbl1.iscalctrg)::text)
+ -> Seq Scan on tbl2
+ Filter: (referenceid = '101991'::numeric)
+ -> Hash
+ -> Seq Scan on tbl1
+ -> Hash
+ -> Subquery Scan on r1
+ -> Seq Scan on tbl2 tbl2_1
+ Filter: (referenceid = '101991'::numeric)
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+DROP TABLE tbl1, tbl2;
-- start_ignore
-- FIXME: gpcheckcat fails due to mismatching distribution policy if this
table isn't dropped
-- Keep this table around once this is fixed
diff --git a/src/test/regress/expected/qp_orca_fallback_optimizer.out
b/src/test/regress/expected/qp_orca_fallback_optimizer.out
index ad2eeabd1e5..63c2cc0e1a8 100644
--- a/src/test/regress/expected/qp_orca_fallback_optimizer.out
+++ b/src/test/regress/expected/qp_orca_fallback_optimizer.out
@@ -353,6 +353,105 @@ DETAIL: Falling back to Postgres-based planner because
GPORCA does not support
"a" | 2
(2 rows)
+-- The walker that detects a CTE Consumer on a different slice than its
+-- replicated Producer. Without it ORCA would emit a plan with cross-slice
+-- replicated CTE Consumers that hangs at execution.
+-- start_ignore
+DROP TABLE IF EXISTS tbl1, tbl2;
+NOTICE: table "tbl1" does not exist, skipping
+NOTICE: table "tbl2" does not exist, skipping
+-- end_ignore
+CREATE TABLE tbl2 (id numeric, refrcode varchar(255), referenceid numeric)
+DISTRIBUTED REPLICATED;
+CREATE TABLE tbl1 (id bigserial, iscalctrg varchar(15) NOT NULL,
+ iscalcdetail varchar(15))
+DISTRIBUTED REPLICATED;
+-- start_ignore
+INSERT INTO tbl2 SELECT i, 'A'||(i%5), 101991
+ FROM generate_series(1, 50000) i;
+INSERT INTO tbl1 (iscalctrg, iscalcdetail)
+ SELECT 'A'||(i%5), 'A'||(i%7) FROM generate_series(1, 50000) i;
+ANALYZE tbl1;
+ANALYZE tbl2;
+-- end_ignore
+-- Case 1: walker triggers fallback. With scalar subqueries on the CTE
+-- ORCA produces a plan whose CTE Producer is replicated and Consumers
+-- live on a different slice -- the walker raises ExmiExpr2DXLUnsupported
+-- and trace_fallback DETAIL says "CTE Consumer placed on a different
+-- slice than its replicated Producer".
+EXPLAIN (COSTS OFF)
+WITH t2 AS (SELECT id, refrcode FROM tbl2 WHERE referenceid = 101991)
+SELECT p.iscalctrg,
+ (SELECT refrcode FROM t2 WHERE refrcode = p.iscalctrg LIMIT 1) AS r,
+ (SELECT refrcode FROM t2 WHERE refrcode = p.iscalcdetail LIMIT 1) AS r1
+FROM tbl1 p
+LIMIT 1;
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: CTE Consumer placed on a different slice than
its replicated Producer
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Gather Motion 1:1 (slice1; segments: 1)
+ -> Limit
+ -> Seq Scan on tbl1 p
+ SubPlan 1
+ -> Limit
+ -> Result
+ Filter: ((tbl2.refrcode)::text =
(p.iscalctrg)::text)
+ -> Materialize
+ -> Seq Scan on tbl2
+ Filter: (referenceid =
'101991'::numeric)
+ SubPlan 2
+ -> Limit
+ -> Result
+ Filter: ((tbl2_1.refrcode)::text =
(p.iscalcdetail)::text)
+ -> Materialize
+ -> Seq Scan on tbl2 tbl2_1
+ Filter: (referenceid =
'101991'::numeric)
+ Optimizer: Postgres query optimizer
+(18 rows)
+
+-- Case 2: walker correctly stays silent. The same CTE referenced from a
+-- JOIN: ORCA pins the Producer body to a single segment with a One-Time
+-- Filter (gp_execution_segment() = N), so the Producer's child
+-- distribution is EdtSingleton, not replicated -- the walker skips it.
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM tbl1),
+ t2 AS (SELECT id, refrcode FROM tbl2 WHERE referenceid = 101991)
+SELECT p.* FROM t1 p
+ JOIN t2 r ON p.iscalctrg = r.refrcode
+ JOIN t2 r1 ON p.iscalcdetail = r1.refrcode
+LIMIT 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Sequence
+ -> Shared Scan (share slice:id 1:1)
+ -> Result
+ Filter: (tbl2.referenceid = '101991'::numeric)
+ -> Result
+ One-Time Filter: (gp_execution_segment() = 1)
+ -> Seq Scan on tbl2
+ -> Redistribute Motion 1:3 (slice2)
+ -> Limit
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Limit
+ -> Hash Join
+ Hash Cond: ((tbl1.iscalctrg)::text =
(share1_ref2.refrcode)::text)
+ -> Hash Join
+ Hash Cond:
((tbl1.iscalcdetail)::text = (share1_ref3.refrcode)::text)
+ -> Result
+ -> Seq Scan on tbl1
+ -> Hash
+ -> Redistribute Motion 3:3
(slice4; segments: 3)
+ Hash Key:
share1_ref3.refrcode
+ -> Shared Scan
(share slice:id 4:1)
+ -> Hash
+ -> Broadcast Motion 3:3
(slice5; segments: 3)
+ -> Shared Scan (share
slice:id 5:1)
+ Optimizer: GPORCA
+(26 rows)
+
+DROP TABLE tbl1, tbl2;
-- start_ignore
-- FIXME: gpcheckcat fails due to mismatching distribution policy if this
table isn't dropped
-- Keep this table around once this is fixed
diff --git a/src/test/regress/expected/shared_scan.out
b/src/test/regress/expected/shared_scan.out
index 2673aff0f8c..83a3815fb0e 100644
--- a/src/test/regress/expected/shared_scan.out
+++ b/src/test/regress/expected/shared_scan.out
@@ -234,3 +234,36 @@ where
Optimizer: Postgres query optimizer
(37 rows)
+-- ORCA should fallback when a CTE over a replicated table is referenced
+-- from multiple scalar subqueries.
+-- ss_t1 needs enough rows (40000) to push ORCA to the cross-slice plan;
+-- with fewer rows the bug does not manifest and the test would silently
+-- pass even without the fix.
+-- start_ignore
+DROP TABLE IF EXISTS ss_t1, ss_t2;
+NOTICE: table "ss_t1" does not exist, skipping
+NOTICE: table "ss_t2" does not exist, skipping
+-- end_ignore
+CREATE TABLE ss_t1 AS
+ SELECT generate_series(1, 40000) id
+ DISTRIBUTED BY (id);
+CREATE TABLE ss_t2 AS
+ SELECT * FROM (VALUES (1, 10), (2, 20)) AS v(id, v)
+ DISTRIBUTED REPLICATED;
+ANALYZE ss_t1;
+ANALYZE ss_t2;
+SET statement_timeout = '15s';
+WITH
+ cte1 AS (SELECT v FROM ss_t2 WHERE id = 1),
+ cte2 AS (SELECT v FROM ss_t2 WHERE id = 2)
+ SELECT (SELECT v FROM cte1) + (SELECT v FROM cte2) +
+ (SELECT v FROM cte1) + (SELECT v FROM cte2) AS result
+ FROM ss_t1
+ LIMIT 1;
+ result
+--------
+ 60
+(1 row)
+
+RESET statement_timeout;
+DROP TABLE ss_t1, ss_t2;
diff --git a/src/test/regress/expected/shared_scan_optimizer.out
b/src/test/regress/expected/shared_scan_optimizer.out
index 57ec36089d2..e71dfafe035 100644
--- a/src/test/regress/expected/shared_scan_optimizer.out
+++ b/src/test/regress/expected/shared_scan_optimizer.out
@@ -242,3 +242,31 @@ where
Optimizer: Postgres query optimizer
(37 rows)
+-- ORCA should fallback when a CTE over a replicated table is referenced
+-- from multiple scalar subqueries.
+-- ss_t1 needs enough rows (40000) to push ORCA to the cross-slice plan;
+-- with fewer rows the bug does not manifest and the test would silently
+-- pass even without the fix.
+CREATE TABLE ss_t1 AS
+ SELECT generate_series(1, 40000) id
+ DISTRIBUTED BY (id);
+CREATE TABLE ss_t2 AS
+ SELECT * FROM (VALUES (1, 10), (2, 20)) AS v(id, v)
+ DISTRIBUTED REPLICATED;
+ANALYZE ss_t1;
+ANALYZE ss_t2;
+SET statement_timeout = '15s';
+WITH
+ cte1 AS (SELECT v FROM ss_t2 WHERE id = 1),
+ cte2 AS (SELECT v FROM ss_t2 WHERE id = 2)
+ SELECT (SELECT v FROM cte1) + (SELECT v FROM cte2) +
+ (SELECT v FROM cte1) + (SELECT v FROM cte2) AS result
+ FROM ss_t1
+ LIMIT 1;
+ result
+--------
+ 60
+(1 row)
+
+RESET statement_timeout;
+DROP TABLE ss_t1, ss_t2;
diff --git a/src/test/regress/sql/qp_orca_fallback.sql
b/src/test/regress/sql/qp_orca_fallback.sql
index 42ad5eef3b9..ecb892c2aa7 100644
--- a/src/test/regress/sql/qp_orca_fallback.sql
+++ b/src/test/regress/sql/qp_orca_fallback.sql
@@ -121,6 +121,53 @@ select array_agg(a order by b)
-- Orca should fallback if a function in 'from' clause uses 'WITH ORDINALITY'
SELECT * FROM jsonb_array_elements('["b", "a"]'::jsonb) WITH ORDINALITY;
+-- The walker that detects a CTE Consumer on a different slice than its
+-- replicated Producer. Without it ORCA would emit a plan with cross-slice
+-- replicated CTE Consumers that hangs at execution.
+-- start_ignore
+DROP TABLE IF EXISTS tbl1, tbl2;
+-- end_ignore
+CREATE TABLE tbl2 (id numeric, refrcode varchar(255), referenceid numeric)
+DISTRIBUTED REPLICATED;
+CREATE TABLE tbl1 (id bigserial, iscalctrg varchar(15) NOT NULL,
+ iscalcdetail varchar(15))
+DISTRIBUTED REPLICATED;
+-- start_ignore
+INSERT INTO tbl2 SELECT i, 'A'||(i%5), 101991
+ FROM generate_series(1, 50000) i;
+INSERT INTO tbl1 (iscalctrg, iscalcdetail)
+ SELECT 'A'||(i%5), 'A'||(i%7) FROM generate_series(1, 50000) i;
+ANALYZE tbl1;
+ANALYZE tbl2;
+-- end_ignore
+
+-- Case 1: walker triggers fallback. With scalar subqueries on the CTE
+-- ORCA produces a plan whose CTE Producer is replicated and Consumers
+-- live on a different slice -- the walker raises ExmiExpr2DXLUnsupported
+-- and trace_fallback DETAIL says "CTE Consumer placed on a different
+-- slice than its replicated Producer".
+EXPLAIN (COSTS OFF)
+WITH t2 AS (SELECT id, refrcode FROM tbl2 WHERE referenceid = 101991)
+SELECT p.iscalctrg,
+ (SELECT refrcode FROM t2 WHERE refrcode = p.iscalctrg LIMIT 1) AS r,
+ (SELECT refrcode FROM t2 WHERE refrcode = p.iscalcdetail LIMIT 1) AS r1
+FROM tbl1 p
+LIMIT 1;
+
+-- Case 2: walker correctly stays silent. The same CTE referenced from a
+-- JOIN: ORCA pins the Producer body to a single segment with a One-Time
+-- Filter (gp_execution_segment() = N), so the Producer's child
+-- distribution is EdtSingleton, not replicated -- the walker skips it.
+EXPLAIN (COSTS OFF)
+WITH t1 AS (SELECT * FROM tbl1),
+ t2 AS (SELECT id, refrcode FROM tbl2 WHERE referenceid = 101991)
+SELECT p.* FROM t1 p
+ JOIN t2 r ON p.iscalctrg = r.refrcode
+ JOIN t2 r1 ON p.iscalcdetail = r1.refrcode
+LIMIT 1;
+
+DROP TABLE tbl1, tbl2;
+
-- start_ignore
-- FIXME: gpcheckcat fails due to mismatching distribution policy if this
table isn't dropped
-- Keep this table around once this is fixed
diff --git a/src/test/regress/sql/shared_scan.sql
b/src/test/regress/sql/shared_scan.sql
index 7234cef6e4a..80b4a1d52c7 100644
--- a/src/test/regress/sql/shared_scan.sql
+++ b/src/test/regress/sql/shared_scan.sql
@@ -120,3 +120,31 @@ where
(data_hour = date_trunc('day',data_hour) and stat.schema_name || '.'
||stat.table_name not in (select table_nm_23 from tbls_daily_report_23))
and (stat.schema_name || '.' ||stat.table_name not in (select
table_nm_onl_act from tbls_w_onl_actl_data))
or (stat.schema_name || '.' ||stat.table_name in (select
table_nm_onl_act from tbls_w_onl_actl_data));
+
+-- ORCA should fallback when a CTE over a replicated table is referenced
+-- from multiple scalar subqueries.
+-- ss_t1 needs enough rows (40000) to push ORCA to the cross-slice plan;
+-- with fewer rows the bug does not manifest and the test would silently
+-- pass even without the fix.
+-- start_ignore
+DROP TABLE IF EXISTS ss_t1, ss_t2;
+-- end_ignore
+CREATE TABLE ss_t1 AS
+ SELECT generate_series(1, 40000) id
+ DISTRIBUTED BY (id);
+CREATE TABLE ss_t2 AS
+ SELECT * FROM (VALUES (1, 10), (2, 20)) AS v(id, v)
+ DISTRIBUTED REPLICATED;
+ANALYZE ss_t1;
+ANALYZE ss_t2;
+
+SET statement_timeout = '15s';
+WITH
+ cte1 AS (SELECT v FROM ss_t2 WHERE id = 1),
+ cte2 AS (SELECT v FROM ss_t2 WHERE id = 2)
+ SELECT (SELECT v FROM cte1) + (SELECT v FROM cte2) +
+ (SELECT v FROM cte1) + (SELECT v FROM cte2) AS result
+ FROM ss_t1
+ LIMIT 1;
+RESET statement_timeout;
+DROP TABLE ss_t1, ss_t2;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
