This is an automated email from the ASF dual-hosted git repository.
yjhjstz pushed a commit to branch REL_2_STABLE
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/REL_2_STABLE by this push:
new f192ed98230 ORCA: fix CTE column-pruning misalignment across consumers
f192ed98230 is described below
commit f192ed982305f1dbbee871f88f6f26770d83e6d9
Author: Jianghua Yang <[email protected]>
AuthorDate: Tue Aug 4 19:17:46 2026 +0800
ORCA: fix CTE column-pruning misalignment across consumers
When a CTE has multiple consumers requiring different column subsets,
the producer's shared-scan output is pruned to the union of all
consumers' required columns (CTranslatorDXLToExpr::PruneCTEs). But each
consumer independently decided its own output columns in
CPhysicalCTEConsumer from its own per-column GetUsage(). A consumer that
considers all of its columns used (e.g. SELECT a.*) kept every column
with an identity index map, while the producer emitted only the pruned
union. The consumer then read the shared tuple by stale positions,
producing wrong results (a join key read from the wrong slot -> LEFT
JOIN yields NULLs) and "invalid attnum N for relation shareX_refY"
during EXPLAIN.
Drive the consumer's kept columns from the producer's finalized used
mask (CLogicalCTEProducer::UsedMask) -- the single source of truth --
so every consumer exposes exactly the producer's surviving columns.
When the producer was not pruned the mask is NULL and behavior is
unchanged.
Add a regression test (cte_prune_multi_consumer) covering multi-consumer
CTEs where a SELECT * consumer is referenced directly with a join key.
It asserts the EXPLAIN no longer errors and returns correct results;
the ORCA path is exercised with shared scans and matches the Postgres
planner.
(cherry picked from commit 3d1c8cdf79af159e82cf22c0f08b1f343996efe8)
---
.../src/operators/CPhysicalCTEConsumer.cpp | 23 ++-
.../regress/expected/cte_prune_multi_consumer.out | 196 +++++++++++++++++++++
.../cte_prune_multi_consumer_optimizer.out | 194 ++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/cte_prune_multi_consumer.sql | 92 ++++++++++
5 files changed, 504 insertions(+), 3 deletions(-)
diff --git a/src/backend/gporca/libgpopt/src/operators/CPhysicalCTEConsumer.cpp
b/src/backend/gporca/libgpopt/src/operators/CPhysicalCTEConsumer.cpp
index d7a51950fa4..d3ed7de5696 100644
--- a/src/backend/gporca/libgpopt/src/operators/CPhysicalCTEConsumer.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CPhysicalCTEConsumer.cpp
@@ -13,6 +13,7 @@
#include "gpos/base.h"
+#include "gpopt/base/CCTEInfo.h"
#include "gpopt/base/CCTEMap.h"
#include "gpopt/base/COptCtxt.h"
#include "gpopt/operators/CExpression.h"
@@ -45,12 +46,30 @@ CPhysicalCTEConsumer::CPhysicalCTEConsumer(CMemoryPool *mp,
ULONG id,
m_pdrgpcr = GPOS_NEW(mp) CColRefArray(mp);
m_pidxmap = GPOS_NEW(mp) ULongPtrArray(mp);
+ // ShareInputScan does not project, so the producer finalizes its
shared-scan
+ // output as the union of all consumers' required columns (see
+ // CTranslatorDXLToExpr::PruneCTEs). Therefore every consumer must
expose
+ // exactly the producer's surviving columns: it can neither read a
column the
+ // producer pruned (that would run past the shared tuple) nor decide
what to
+ // keep from its own per-column usage. Drive the consumer's kept set
from the
+ // producer's used mask, which is the single source of truth. When the
+ // producer was not pruned (mask is NULL) fall back to the previous
behavior.
+ CCTEInfo *pcteinfo = COptCtxt::PoctxtFromTLS()->Pcteinfo();
+ CLogicalCTEProducer *popProducer =
+
CLogicalCTEProducer::PopConvert(pcteinfo->PexprCTEProducer(m_id)->Pop());
+ BOOL *producer_umask = popProducer->UsedMask();
+ GPOS_ASSERT_IMP(nullptr != producer_umask,
+ popProducer->Pdrgpcr()->Size() ==
colref_size);
+
for (ULONG index = 0; index < colref_size; index++) {
CColRef *col_ref = (*colref_array)[index];
- if (col_ref->GetUsage(true, true) == CColRef::EUsed) {
+ BOOL kept = (nullptr != producer_umask)
+ ? producer_umask[index]
+ : (col_ref->GetUsage(true,
true) == CColRef::EUsed);
+ if (kept) {
m_pdrgpcr->Append(col_ref);
m_pidxmap->Append(GPOS_NEW(m_mp) ULONG(index));
- }
+ }
}
if (m_pidxmap->Size() == colref_size) {
diff --git a/src/test/regress/expected/cte_prune_multi_consumer.out
b/src/test/regress/expected/cte_prune_multi_consumer.out
new file mode 100644
index 00000000000..e59eccd3294
--- /dev/null
+++ b/src/test/regress/expected/cte_prune_multi_consumer.out
@@ -0,0 +1,196 @@
+--
+-- Multi-consumer CTE column pruning (ORCA).
+--
+-- A CTE referenced by several consumers has its shared-scan output pruned to
the
+-- union of all consumers' required columns. Because ShareInputScan does not
+-- project, every consumer must expose exactly the producer's surviving
columns.
+-- A consumer that projects a superset (e.g. SELECT *) and is referenced
directly
+-- with a join key used to keep an identity column map and read the shared
tuple
+-- by stale positions, producing wrong results (join key read from the wrong
slot
+-- -> LEFT JOIN yields NULLs) and "invalid attnum N for relation shareX_refY"
in
+-- EXPLAIN. See CPhysicalCTEConsumer. These queries assert correct results
under
+-- both optimizers.
+--
+create schema cte_mc;
+set search_path = cte_mc;
+create table policy(pr_id text, pied_id text, p_id text, amnt numeric(18,2),
+ guarantee_period text, main_risk text, premium
numeric(18,2));
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'pr_id' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create table mid(p_id text, tenant_id text, act_premium numeric(18,2));
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'p_id' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into policy values('pr1','k1','p1',300000,'1','Y',290);
+insert into mid values('p1','t1',290);
+analyze policy;
+analyze mid;
+-- The "info" consumer projects SELECT * but only p_id/amnt/main_risk are
needed
+-- outside; the producer prunes pr_id/pied_id/guarantee_period. i.amnt and
i.p_id
+-- must not come back NULL. EXPLAIN previously raised "invalid attnum".
+explain (costs off)
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where main_risk='Y' and p_id='p1'),
+ unused as (select 1 from mid)
+select i.amnt, m.tenant_id, m.p_id, i.p_id, d.p_id, d.premium, m.act_premium
+from mid m
+left join det d on d.p_id=m.p_id and d.premium=m.act_premium
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3,4;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: i.amnt, m.tenant_id, i.p_id
+ -> Sort
+ Sort Key: i.amnt, m.tenant_id, i.p_id
+ -> Hash Left Join
+ Hash Cond: (m.p_id = i.p_id)
+ -> Hash Right Join
+ Hash Cond: ((agent.p_id = m.p_id) AND
((sum(agent.premium)) = m.act_premium))
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 'p1'::text
+ -> GroupAggregate
+ Group Key: agent.p_id
+ -> Redistribute Motion 3:3 (slice3;
segments: 3)
+ Hash Key: agent.p_id
+ -> Subquery Scan on agent
+ -> Seq Scan on policy
+ Filter: (p_id = 'p1'::text)
+ -> Hash
+ -> Seq Scan on mid m
+ Filter: (p_id = 'p1'::text)
+ -> Hash
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: 'p1'::text
+ -> Subquery Scan on i
+ -> Seq Scan on policy policy_1
+ Filter: ((main_risk = 'Y'::text) AND
(p_id = 'p1'::text))
+ Optimizer: Postgres query optimizer
+(27 rows)
+
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where main_risk='Y' and p_id='p1'),
+ unused as (select 1 from mid)
+select i.amnt, m.tenant_id, m.p_id, i.p_id, d.p_id, d.premium, m.act_premium
+from mid m
+left join det d on d.p_id=m.p_id and d.premium=m.act_premium
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3,4;
+ amnt | tenant_id | p_id | p_id | p_id | premium | act_premium
+-----------+-----------+------+------+------+---------+-------------
+ 300000.00 | t1 | p1 | p1 | p1 | 290.00 | 290.00
+(1 row)
+
+-- Variant: project a different surviving column (main_risk) from the SELECT *
+-- consumer to exercise a different pruned layout.
+explain (costs off)
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where p_id='p1')
+select m.p_id, i.main_risk, i.amnt, d.premium
+from mid m
+left join det d on d.p_id=m.p_id
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: i.main_risk, i.amnt
+ -> Sort
+ Sort Key: i.main_risk, i.amnt
+ -> Hash Left Join
+ Hash Cond: (m.p_id = i.p_id)
+ -> Hash Right Join
+ Hash Cond: (d.p_id = m.p_id)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: 'p1'::text
+ -> Subquery Scan on d
+ -> GroupAggregate
+ Group Key: agent.p_id
+ -> Redistribute Motion 3:3 (slice3;
segments: 3)
+ Hash Key: agent.p_id
+ -> Subquery Scan on agent
+ -> Seq Scan on policy
+ Filter: (p_id =
'p1'::text)
+ -> Hash
+ -> Seq Scan on mid m
+ Filter: (p_id = 'p1'::text)
+ -> Hash
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: 'p1'::text
+ -> Subquery Scan on i
+ -> Seq Scan on policy policy_1
+ Filter: (p_id = 'p1'::text)
+ Optimizer: Postgres query optimizer
+(28 rows)
+
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where p_id='p1')
+select m.p_id, i.main_risk, i.amnt, d.premium
+from mid m
+left join det d on d.p_id=m.p_id
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3;
+ p_id | main_risk | amnt | premium
+------+-----------+-----------+---------
+ p1 | Y | 300000.00 | 290.00
+(1 row)
+
+-- Three consumers, each needing a different subset (join key on each).
+explain (costs off)
+with agent as (select p_id, amnt, main_risk, premium from policy)
+select x.p_id, y.amnt, z.main_risk
+from agent x
+join agent y on x.p_id=y.p_id
+join agent z on x.p_id=z.p_id
+order by 1,2,3;
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: x.p_id, y.amnt, z.main_risk
+ -> Sort
+ Sort Key: x.p_id, y.amnt, z.main_risk
+ -> Hash Join
+ Hash Cond: (x.p_id = z.p_id)
+ -> Hash Join
+ Hash Cond: (x.p_id = y.p_id)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: x.p_id
+ -> Subquery Scan on x
+ -> Seq Scan on policy
+ -> Hash
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: y.p_id
+ -> Subquery Scan on y
+ -> Seq Scan on policy policy_1
+ -> Hash
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: z.p_id
+ -> Subquery Scan on z
+ -> Seq Scan on policy policy_2
+ Optimizer: Postgres query optimizer
+(23 rows)
+
+with agent as (select p_id, amnt, main_risk, premium from policy)
+select x.p_id, y.amnt, z.main_risk
+from agent x
+join agent y on x.p_id=y.p_id
+join agent z on x.p_id=z.p_id
+order by 1,2,3;
+ p_id | amnt | main_risk
+------+-----------+-----------
+ p1 | 300000.00 | Y
+(1 row)
+
+-- start_ignore
+drop schema cte_mc cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table policy
+drop cascades to table mid
+-- end_ignore
diff --git a/src/test/regress/expected/cte_prune_multi_consumer_optimizer.out
b/src/test/regress/expected/cte_prune_multi_consumer_optimizer.out
new file mode 100644
index 00000000000..59b8b0eba3f
--- /dev/null
+++ b/src/test/regress/expected/cte_prune_multi_consumer_optimizer.out
@@ -0,0 +1,194 @@
+--
+-- Multi-consumer CTE column pruning (ORCA).
+--
+-- A CTE referenced by several consumers has its shared-scan output pruned to
the
+-- union of all consumers' required columns. Because ShareInputScan does not
+-- project, every consumer must expose exactly the producer's surviving
columns.
+-- A consumer that projects a superset (e.g. SELECT *) and is referenced
directly
+-- with a join key used to keep an identity column map and read the shared
tuple
+-- by stale positions, producing wrong results (join key read from the wrong
slot
+-- -> LEFT JOIN yields NULLs) and "invalid attnum N for relation shareX_refY"
in
+-- EXPLAIN. See CPhysicalCTEConsumer. These queries assert correct results
under
+-- both optimizers.
+--
+create schema cte_mc;
+set search_path = cte_mc;
+create table policy(pr_id text, pied_id text, p_id text, amnt numeric(18,2),
+ guarantee_period text, main_risk text, premium
numeric(18,2));
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'pr_id' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create table mid(p_id text, tenant_id text, act_premium numeric(18,2));
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named
'p_id' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into policy values('pr1','k1','p1',300000,'1','Y',290);
+insert into mid values('p1','t1',290);
+analyze policy;
+analyze mid;
+-- The "info" consumer projects SELECT * but only p_id/amnt/main_risk are
needed
+-- outside; the producer prunes pr_id/pied_id/guarantee_period. i.amnt and
i.p_id
+-- must not come back NULL. EXPLAIN previously raised "invalid attnum".
+explain (costs off)
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where main_risk='Y' and p_id='p1'),
+ unused as (select 1 from mid)
+select i.amnt, m.tenant_id, m.p_id, i.p_id, d.p_id, d.premium, m.act_premium
+from mid m
+left join det d on d.p_id=m.p_id and d.premium=m.act_premium
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3,4;
+ QUERY
PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: share0_ref3.amnt, m.tenant_id, m.p_id, share0_ref3.p_id
+ -> Sort
+ Sort Key: share0_ref3.amnt, m.tenant_id, m.p_id, share0_ref3.p_id
+ -> Sequence
+ -> Shared Scan (share slice:id 1:0)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: policy.p_id
+ -> Seq Scan on policy
+ -> Hash Left Join
+ Hash Cond: ((m.p_id = share0_ref2.p_id) AND
(m.act_premium = (sum(share0_ref2.premium))))
+ -> Hash Left Join
+ Hash Cond: (m.p_id = share0_ref3.p_id)
+ -> Seq Scan on mid m
+ Filter: (p_id = 'p1'::text)
+ -> Hash
+ -> Result
+ Filter: ((share0_ref3.main_risk =
'Y'::text) AND (share0_ref3.p_id = 'p1'::text) AND (share0_ref3.p_id =
'p1'::text))
+ -> Shared Scan (share slice:id 1:0)
+ -> Hash
+ -> Result
+ Filter: (share0_ref2.p_id = 'p1'::text)
+ -> GroupAggregate
+ Group Key: share0_ref2.p_id
+ -> Sort
+ Sort Key: share0_ref2.p_id
+ -> Shared Scan (share slice:id
1:0)
+ Optimizer: GPORCA
+(28 rows)
+
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where main_risk='Y' and p_id='p1'),
+ unused as (select 1 from mid)
+select i.amnt, m.tenant_id, m.p_id, i.p_id, d.p_id, d.premium, m.act_premium
+from mid m
+left join det d on d.p_id=m.p_id and d.premium=m.act_premium
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3,4;
+ amnt | tenant_id | p_id | p_id | p_id | premium | act_premium
+-----------+-----------+------+------+------+---------+-------------
+ 300000.00 | t1 | p1 | p1 | p1 | 290.00 | 290.00
+(1 row)
+
+-- Variant: project a different surviving column (main_risk) from the SELECT *
+-- consumer to exercise a different pruned layout.
+explain (costs off)
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where p_id='p1')
+select m.p_id, i.main_risk, i.amnt, d.premium
+from mid m
+left join det d on d.p_id=m.p_id
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: m.p_id, share0_ref3.main_risk, share0_ref3.amnt
+ -> Sort
+ Sort Key: m.p_id, share0_ref3.main_risk, share0_ref3.amnt
+ -> Sequence
+ -> Shared Scan (share slice:id 1:0)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: policy.p_id
+ -> Seq Scan on policy
+ -> Hash Left Join
+ Hash Cond: (m.p_id = share0_ref2.p_id)
+ -> Hash Left Join
+ Hash Cond: (m.p_id = share0_ref3.p_id)
+ -> Seq Scan on mid m
+ Filter: (p_id = 'p1'::text)
+ -> Hash
+ -> Result
+ Filter: ((share0_ref3.p_id =
'p1'::text) AND (share0_ref3.p_id = 'p1'::text))
+ -> Shared Scan (share slice:id 1:0)
+ -> Hash
+ -> Result
+ Filter: (share0_ref2.p_id = 'p1'::text)
+ -> GroupAggregate
+ Group Key: share0_ref2.p_id
+ -> Sort
+ Sort Key: share0_ref2.p_id
+ -> Shared Scan (share slice:id
1:0)
+ Optimizer: GPORCA
+(28 rows)
+
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where p_id='p1')
+select m.p_id, i.main_risk, i.amnt, d.premium
+from mid m
+left join det d on d.p_id=m.p_id
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3;
+ p_id | main_risk | amnt | premium
+------+-----------+-----------+---------
+ p1 | Y | 300000.00 | 290.00
+(1 row)
+
+-- Three consumers, each needing a different subset (join key on each).
+explain (costs off)
+with agent as (select p_id, amnt, main_risk, premium from policy)
+select x.p_id, y.amnt, z.main_risk
+from agent x
+join agent y on x.p_id=y.p_id
+join agent z on x.p_id=z.p_id
+order by 1,2,3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: share0_ref2.p_id, share0_ref4.amnt, share0_ref3.main_risk
+ -> Sort
+ Sort Key: share0_ref2.p_id, share0_ref4.amnt, share0_ref3.main_risk
+ -> Sequence
+ -> Shared Scan (share slice:id 1:0)
+ -> Seq Scan on policy
+ -> Hash Join
+ Hash Cond: (share0_ref4.p_id = share0_ref2.p_id)
+ -> Shared Scan (share slice:id 1:0)
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Join
+ Hash Cond: (share0_ref3.p_id =
share0_ref2.p_id)
+ -> Shared Scan (share slice:id 2:0)
+ -> Hash
+ -> Broadcast Motion 3:3
(slice3; segments: 3)
+ -> Result
+ -> Shared Scan
(share slice:id 3:0)
+ Optimizer: GPORCA
+(20 rows)
+
+with agent as (select p_id, amnt, main_risk, premium from policy)
+select x.p_id, y.amnt, z.main_risk
+from agent x
+join agent y on x.p_id=y.p_id
+join agent z on x.p_id=z.p_id
+order by 1,2,3;
+ p_id | amnt | main_risk
+------+-----------+-----------
+ p1 | 300000.00 | Y
+(1 row)
+
+-- start_ignore
+drop schema cte_mc cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table policy
+drop cascades to table mid
+-- end_ignore
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index 79eb980de74..e5551f42ecb 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -166,7 +166,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding
jsonb_jsonpath
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion
truncate alter_table sequence polymorphism rowtypes returning with xml cte_prune
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion
truncate alter_table sequence polymorphism rowtypes returning with xml
cte_prune cte_prune_multi_consumer
# large objects are not supported by GPDB
# test: largeobject
diff --git a/src/test/regress/sql/cte_prune_multi_consumer.sql
b/src/test/regress/sql/cte_prune_multi_consumer.sql
new file mode 100644
index 00000000000..6e082547348
--- /dev/null
+++ b/src/test/regress/sql/cte_prune_multi_consumer.sql
@@ -0,0 +1,92 @@
+--
+-- Multi-consumer CTE column pruning (ORCA).
+--
+-- A CTE referenced by several consumers has its shared-scan output pruned to
the
+-- union of all consumers' required columns. Because ShareInputScan does not
+-- project, every consumer must expose exactly the producer's surviving
columns.
+-- A consumer that projects a superset (e.g. SELECT *) and is referenced
directly
+-- with a join key used to keep an identity column map and read the shared
tuple
+-- by stale positions, producing wrong results (join key read from the wrong
slot
+-- -> LEFT JOIN yields NULLs) and "invalid attnum N for relation shareX_refY"
in
+-- EXPLAIN. See CPhysicalCTEConsumer. These queries assert correct results
under
+-- both optimizers.
+--
+create schema cte_mc;
+set search_path = cte_mc;
+
+create table policy(pr_id text, pied_id text, p_id text, amnt numeric(18,2),
+ guarantee_period text, main_risk text, premium
numeric(18,2));
+create table mid(p_id text, tenant_id text, act_premium numeric(18,2));
+insert into policy values('pr1','k1','p1',300000,'1','Y',290);
+insert into mid values('p1','t1',290);
+analyze policy;
+analyze mid;
+
+-- The "info" consumer projects SELECT * but only p_id/amnt/main_risk are
needed
+-- outside; the producer prunes pr_id/pied_id/guarantee_period. i.amnt and
i.p_id
+-- must not come back NULL. EXPLAIN previously raised "invalid attnum".
+explain (costs off)
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where main_risk='Y' and p_id='p1'),
+ unused as (select 1 from mid)
+select i.amnt, m.tenant_id, m.p_id, i.p_id, d.p_id, d.premium, m.act_premium
+from mid m
+left join det d on d.p_id=m.p_id and d.premium=m.act_premium
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3,4;
+
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where main_risk='Y' and p_id='p1'),
+ unused as (select 1 from mid)
+select i.amnt, m.tenant_id, m.p_id, i.p_id, d.p_id, d.premium, m.act_premium
+from mid m
+left join det d on d.p_id=m.p_id and d.premium=m.act_premium
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3,4;
+
+-- Variant: project a different surviving column (main_risk) from the SELECT *
+-- consumer to exercise a different pruned layout.
+explain (costs off)
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where p_id='p1')
+select m.p_id, i.main_risk, i.amnt, d.premium
+from mid m
+left join det d on d.p_id=m.p_id
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3;
+
+with agent as (select
pr_id,pied_id,p_id,amnt,guarantee_period,main_risk,premium from policy),
+ det as (select p_id, sum(premium) premium from agent group by p_id),
+ info as (select * from agent where p_id='p1')
+select m.p_id, i.main_risk, i.amnt, d.premium
+from mid m
+left join det d on d.p_id=m.p_id
+left join info i on m.p_id=i.p_id
+where m.p_id='p1'
+order by 1,2,3;
+
+-- Three consumers, each needing a different subset (join key on each).
+explain (costs off)
+with agent as (select p_id, amnt, main_risk, premium from policy)
+select x.p_id, y.amnt, z.main_risk
+from agent x
+join agent y on x.p_id=y.p_id
+join agent z on x.p_id=z.p_id
+order by 1,2,3;
+
+with agent as (select p_id, amnt, main_risk, premium from policy)
+select x.p_id, y.amnt, z.main_risk
+from agent x
+join agent y on x.p_id=y.p_id
+join agent z on x.p_id=z.p_id
+order by 1,2,3;
+
+-- start_ignore
+drop schema cte_mc cascade;
+-- end_ignore
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
