This is an automated email from the ASF dual-hosted git repository.
yjhjstz 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 977f63daf77 [ORCA] Narrow CTE Producer columns in
CXformGbAggWithMDQA2Join
977f63daf77 is described below
commit 977f63daf7727351eee5945576cde8414ba793c1
Author: Jianghua Yang <[email protected]>
AuthorDate: Fri Aug 21 00:36:30 2026 +0800
[ORCA] Narrow CTE Producer columns in CXformGbAggWithMDQA2Join
When rewriting a GbAgg with multiple distinct-qualified aggregates
(MDQAs) into a join of single-DQA aggregates, the transform declared
every column the child expression could produce on the CTE Producer.
On the partitioned-table path the over-declared columns included
unreferenced and system columns whose usage state is EUnknown/EUnused,
which fails the assertion "col_ref->GetUsage() != CColRef::EUnknown"
in CPhysicalCTEProducer::PcrsRequired on assert builds and crashes
with SIGSEGV in MakeDXLTableDescr on release builds:
CREATE TABLE mdqa_part(a int, b int, c int, d int)
DISTRIBUTED BY (a)
PARTITION BY RANGE(a) (START (0) END (100) EVERY (50),
DEFAULT PARTITION other);
SET optimizer_enable_multiple_distinct_aggs = on;
SELECT a, count(DISTINCT b), count(DISTINCT c)
FROM mdqa_part WHERE b BETWEEN 0 AND 2 GROUP BY a;
Fix by restricting the CTE Producer/Consumer columns to the columns
the GbAgg actually needs: group-by keys plus aggregate-argument
references, clipped to what the child can produce. Besides fixing the
crash, this also simplifies MDQA plans on non-partitioned tables: the
narrowed producer no longer carries unused and system columns, and
ORCA can now place a single Redistribute Motion below the producer
instead of one per consumer.
Ported from warehouse-pg/warehouse-pg#149, minidump changes omitted.
---
.../src/test/regress/expected/gp_dqa_optimizer.out | 37 ++--
.../src/xforms/CXformGbAggWithMDQA2Join.cpp | 13 +-
src/test/regress/expected/gp_dqa.out | 113 ++++++++++++
src/test/regress/expected/gp_dqa_optimizer.out | 191 ++++++++++++++++-----
src/test/regress/sql/gp_dqa.sql | 28 +++
5 files changed, 312 insertions(+), 70 deletions(-)
diff --git a/contrib/pax_storage/src/test/regress/expected/gp_dqa_optimizer.out
b/contrib/pax_storage/src/test/regress/expected/gp_dqa_optimizer.out
index ad85ad9f14a..2d2e3a87da2 100644
--- a/contrib/pax_storage/src/test/regress/expected/gp_dqa_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/gp_dqa_optimizer.out
@@ -3251,16 +3251,19 @@ DETAIL: Falling back to Postgres-based planner because
GPORCA does not support
set optimizer_enable_multiple_distinct_aggs=on;
explain (verbose on, costs off) select count(distinct a), count(distinct b)
from dqa_f4 group by c;
-
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b))
-> Sequence
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT
share0_ref2.b))
-> Shared Scan (share slice:id 1:0)
- Output: share0_ref1.a, share0_ref1.b, share0_ref1.c,
share0_ref1.ctid, share0_ref1.tableoid, share0_ref1.gp_segment_id,
share0_ref1.gp_foreign_server
- -> Seq Scan on public.dqa_f4
- Output: dqa_f4.a, dqa_f4.b, dqa_f4.c, dqa_f4.ctid,
dqa_f4.tableoid, dqa_f4.gp_segment_id, dqa_f4.gp_foreign_server
+ Output: share0_ref1.a, share0_ref1.b, share0_ref1.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: dqa_f4.a, dqa_f4.b, dqa_f4.c
+ Hash Key: dqa_f4.c
+ -> Seq Scan on public.dqa_f4
+ Output: dqa_f4.a, dqa_f4.b, dqa_f4.c
-> Hash Join
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT
share0_ref2.b))
Hash Cond: (NOT (share0_ref3.c IS DISTINCT FROM share0_ref2.c))
@@ -3268,33 +3271,23 @@ explain (verbose on, costs off) select count(distinct
a), count(distinct b) from
Output: count(DISTINCT share0_ref3.a), share0_ref3.c
Group Key: share0_ref3.c
-> Sort
- Output: share0_ref3.a, share0_ref3.c
+ Output: share0_ref3.a, share0_ref3.b, share0_ref3.c
Sort Key: share0_ref3.c
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Output: share0_ref3.a, share0_ref3.c
- Hash Key: share0_ref3.c
- -> Result
- Output: share0_ref3.a, share0_ref3.c
- -> Shared Scan (share slice:id 2:0)
- Output: share0_ref3.a,
share0_ref3.b, share0_ref3.c
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref3.a, share0_ref3.b,
share0_ref3.c
-> Hash
Output: (count(DISTINCT share0_ref2.b)), share0_ref2.c
-> GroupAggregate
Output: count(DISTINCT share0_ref2.b), share0_ref2.c
Group Key: share0_ref2.c
-> Sort
- Output: share0_ref2.b, share0_ref2.c
+ Output: share0_ref2.a, share0_ref2.b,
share0_ref2.c
Sort Key: share0_ref2.c
- -> Redistribute Motion 3:3 (slice3;
segments: 3)
- Output: share0_ref2.b, share0_ref2.c
- Hash Key: share0_ref2.c
- -> Result
- Output: share0_ref2.b,
share0_ref2.c
- -> Shared Scan (share slice:id
3:0)
- Output: share0_ref2.a,
share0_ref2.b, share0_ref2.c
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref2.a, share0_ref2.b,
share0_ref2.c
Settings: optimizer = 'on', gp_motion_cost_per_row = '2', enable_hashagg =
'on', enable_groupagg = 'off'
Optimizer: GPORCA
-(41 rows)
+(34 rows)
select count(distinct a), count(distinct b) from dqa_f4 group by c;
count | count
diff --git
a/src/backend/gporca/libgpopt/src/xforms/CXformGbAggWithMDQA2Join.cpp
b/src/backend/gporca/libgpopt/src/xforms/CXformGbAggWithMDQA2Join.cpp
index 0ca823a499c..e0a112f29b3 100644
--- a/src/backend/gporca/libgpopt/src/xforms/CXformGbAggWithMDQA2Join.cpp
+++ b/src/backend/gporca/libgpopt/src/xforms/CXformGbAggWithMDQA2Join.cpp
@@ -98,10 +98,21 @@ CXformGbAggWithMDQA2Join::PexprMDQAs2Join(CMemoryPool *mp,
CExpression *pexpr)
GPOS_ASSERT((*pexpr)[1]->DeriveHasMultipleDistinctAggs());
// extract components
+ CLogicalGbAgg *popGbAgg = CLogicalGbAgg::PopConvert(pexpr->Pop());
CExpression *pexprChild = (*pexpr)[0];
+ CExpression *pexprAggList = (*pexpr)[1]; // GbAgg's scalar child
+ // Compute the columns actually needed by the GbAgg:
+ // group-by keys + aggregate-argument references
+ // Clip to what the child can provide.
CColRefSet *pcrsChildOutput = pexprChild->DeriveOutputColumns();
- CColRefArray *pdrgpcrChildOutput = pcrsChildOutput->Pdrgpcr(mp);
+ CColRefSet *pcrsAggListRefs = pexprAggList->DeriveUsedColumns();
// cols read by aggregate args
+ CColRefSet *pcrsUsed = GPOS_NEW(mp) CColRefSet(mp);
+ pcrsUsed->Include(popGbAgg->Pdrgpcr()); // group-by keys
+ pcrsUsed->Union(pcrsAggListRefs); // agg-list refs
+ pcrsUsed->Intersection(pcrsChildOutput); // clip to producible
+ CColRefArray *pdrgpcrChildOutput = pcrsUsed->Pdrgpcr(mp);
+ pcrsUsed->Release();
// create a CTE producer based on child expression
CCTEInfo *pcteinfo = COptCtxt::PoctxtFromTLS()->Pcteinfo();
diff --git a/src/test/regress/expected/gp_dqa.out
b/src/test/regress/expected/gp_dqa.out
index c2422e09450..2488462f3e3 100644
--- a/src/test/regress/expected/gp_dqa.out
+++ b/src/test/regress/expected/gp_dqa.out
@@ -3115,3 +3115,116 @@ select count(distinct a), count(distinct b) from dqa_f5
group by c;
reset optimizer_enable_multiple_distinct_aggs;
drop table dqa_f4;
drop table dqa_f5;
+-- Test MDQA on a partitioned table.
+-- Pre-fix, CXformGbAggWithMDQA2Join declared every column the child could
+-- produce on the CTE Producer, so unused and system columns with EUnknown
+-- usage leaked into the producer's column list and crashed ORCA on the
+-- partitioned-table path (failed assertion "col_ref->GetUsage() !=
+-- CColRef::EUnknown" in assert builds, SIGSEGV in release builds).
+set optimizer_enable_multiple_distinct_aggs=on;
+create table mdqa_part(a int, b int, c int, d int)
+distributed by (a)
+partition by range(a) (
+ start (0) end (100) every (50),
+ default partition other
+);
+insert into mdqa_part select i, i%5, i%3, i%7 from generate_series(1, 100) i;
+create index mdqa_part_b_idx on mdqa_part(b);
+analyze mdqa_part;
+explain (costs off)
+select a, count(distinct b) as cnt_b, count(distinct c) as cnt_c
+from mdqa_part where b between 0 and 2 group by a;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Finalize HashAggregate
+ Group Key: mdqa_part.a
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial HashAggregate
+ Group Key: mdqa_part.a
+ -> HashAggregate
+ Group Key: (AggExprId), mdqa_part.b, mdqa_part.c,
mdqa_part.a
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: mdqa_part.a, mdqa_part.b, mdqa_part.c,
(AggExprId)
+ -> Streaming HashAggregate
+ Group Key: AggExprId, mdqa_part.b,
mdqa_part.c, mdqa_part.a
+ -> TupleSplit
+ Split by Col: (mdqa_part.b),
(mdqa_part.c)
+ Group Key: mdqa_part.a
+ -> Append
+ -> Seq Scan on mdqa_part_1_prt_2
mdqa_part_1
+ Filter: ((b >= 0) AND (b <=
2))
+ -> Seq Scan on mdqa_part_1_prt_3
mdqa_part_2
+ Filter: ((b >= 0) AND (b <=
2))
+ -> Seq Scan on
mdqa_part_1_prt_other mdqa_part_3
+ Filter: ((b >= 0) AND (b <=
2))
+ Optimizer: Postgres query optimizer
+(22 rows)
+
+select a, count(distinct b) as cnt_b, count(distinct c) as cnt_c
+from mdqa_part where b between 0 and 2 group by a;
+ a | cnt_b | cnt_c
+-----+-------+-------
+ 87 | 1 | 1
+ 71 | 1 | 1
+ 51 | 1 | 1
+ 96 | 1 | 1
+ 52 | 1 | 1
+ 80 | 1 | 1
+ 70 | 1 | 1
+ 67 | 1 | 1
+ 90 | 1 | 1
+ 10 | 1 | 1
+ 35 | 1 | 1
+ 45 | 1 | 1
+ 6 | 1 | 1
+ 86 | 1 | 1
+ 92 | 1 | 1
+ 36 | 1 | 1
+ 31 | 1 | 1
+ 50 | 1 | 1
+ 60 | 1 | 1
+ 97 | 1 | 1
+ 66 | 1 | 1
+ 22 | 1 | 1
+ 65 | 1 | 1
+ 2 | 1 | 1
+ 16 | 1 | 1
+ 62 | 1 | 1
+ 75 | 1 | 1
+ 11 | 1 | 1
+ 42 | 1 | 1
+ 82 | 1 | 1
+ 41 | 1 | 1
+ 46 | 1 | 1
+ 40 | 1 | 1
+ 32 | 1 | 1
+ 7 | 1 | 1
+ 100 | 1 | 1
+ 15 | 1 | 1
+ 26 | 1 | 1
+ 12 | 1 | 1
+ 85 | 1 | 1
+ 72 | 1 | 1
+ 95 | 1 | 1
+ 57 | 1 | 1
+ 81 | 1 | 1
+ 61 | 1 | 1
+ 77 | 1 | 1
+ 25 | 1 | 1
+ 30 | 1 | 1
+ 21 | 1 | 1
+ 47 | 1 | 1
+ 17 | 1 | 1
+ 37 | 1 | 1
+ 20 | 1 | 1
+ 1 | 1 | 1
+ 76 | 1 | 1
+ 5 | 1 | 1
+ 55 | 1 | 1
+ 27 | 1 | 1
+ 56 | 1 | 1
+ 91 | 1 | 1
+(60 rows)
+
+reset optimizer_enable_multiple_distinct_aggs;
+drop table mdqa_part;
diff --git a/src/test/regress/expected/gp_dqa_optimizer.out
b/src/test/regress/expected/gp_dqa_optimizer.out
index 5307bd0f909..d49080ac3d5 100644
--- a/src/test/regress/expected/gp_dqa_optimizer.out
+++ b/src/test/regress/expected/gp_dqa_optimizer.out
@@ -3251,16 +3251,19 @@ DETAIL: Falling back to Postgres-based planner because
GPORCA does not support
set optimizer_enable_multiple_distinct_aggs=on;
explain (verbose on, costs off) select count(distinct a), count(distinct b)
from dqa_f4 group by c;
-
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b))
-> Sequence
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT
share0_ref2.b))
-> Shared Scan (share slice:id 1:0)
- Output: share0_ref1.a, share0_ref1.b, share0_ref1.c,
share0_ref1.ctid, share0_ref1.xmin, share0_ref1.cmin, share0_ref1.xmax,
share0_ref1.cmax, share0_ref1.tableoid, share0_ref1.gp_segment_id,
share0_ref1.gp_foreign_server
- -> Seq Scan on public.dqa_f4
- Output: dqa_f4.a, dqa_f4.b, dqa_f4.c, dqa_f4.ctid,
dqa_f4.xmin, dqa_f4.cmin, dqa_f4.xmax, dqa_f4.cmax, dqa_f4.tableoid,
dqa_f4.gp_segment_id, dqa_f4.gp_foreign_server
+ Output: share0_ref1.a, share0_ref1.b, share0_ref1.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: dqa_f4.a, dqa_f4.b, dqa_f4.c
+ Hash Key: dqa_f4.c
+ -> Seq Scan on public.dqa_f4
+ Output: dqa_f4.a, dqa_f4.b, dqa_f4.c
-> Hash Join
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT
share0_ref2.b))
Hash Cond: (NOT (share0_ref3.c IS DISTINCT FROM share0_ref2.c))
@@ -3268,33 +3271,23 @@ explain (verbose on, costs off) select count(distinct
a), count(distinct b) from
Output: count(DISTINCT share0_ref3.a), share0_ref3.c
Group Key: share0_ref3.c
-> Sort
- Output: share0_ref3.a, share0_ref3.c
+ Output: share0_ref3.a, share0_ref3.b, share0_ref3.c
Sort Key: share0_ref3.c
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Output: share0_ref3.a, share0_ref3.c
- Hash Key: share0_ref3.c
- -> Result
- Output: share0_ref3.a, share0_ref3.c
- -> Shared Scan (share slice:id 2:0)
- Output: share0_ref3.a,
share0_ref3.b, share0_ref3.c
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref3.a, share0_ref3.b,
share0_ref3.c
-> Hash
Output: (count(DISTINCT share0_ref2.b)), share0_ref2.c
-> GroupAggregate
Output: count(DISTINCT share0_ref2.b), share0_ref2.c
Group Key: share0_ref2.c
-> Sort
- Output: share0_ref2.b, share0_ref2.c
+ Output: share0_ref2.a, share0_ref2.b,
share0_ref2.c
Sort Key: share0_ref2.c
- -> Redistribute Motion 3:3 (slice3;
segments: 3)
- Output: share0_ref2.b, share0_ref2.c
- Hash Key: share0_ref2.c
- -> Result
- Output: share0_ref2.b,
share0_ref2.c
- -> Shared Scan (share slice:id
3:0)
- Output: share0_ref2.a,
share0_ref2.b, share0_ref2.c
- Settings: enable_groupagg = 'off', enable_hashagg = 'on', enable_parallel =
'off', gp_motion_cost_per_row = '2', optimizer = 'on'
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref2.a, share0_ref2.b,
share0_ref2.c
+ Settings: gp_motion_cost_per_row = '2', enable_hashagg = 'on',
enable_groupagg = 'off'
Optimizer: GPORCA
-(41 rows)
+(34 rows)
select count(distinct a), count(distinct b) from dqa_f4 group by c;
count | count
@@ -3312,16 +3305,19 @@ insert into dqa_f5 values(null, null, null);
insert into dqa_f5 values(1, 1, 1);
insert into dqa_f5 values(2, 2, 2);
explain (verbose on, costs off) select count(distinct a), count(distinct b)
from dqa_f5 group by c;
-
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT share0_ref2.b))
-> Sequence
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT
share0_ref2.b))
-> Shared Scan (share slice:id 1:0)
- Output: share0_ref1.a, share0_ref1.b, share0_ref1.c,
share0_ref1.ctid, share0_ref1.tableoid, share0_ref1.gp_segment_id,
share0_ref1.gp_foreign_server
- -> Seq Scan on public.dqa_f5
- Output: dqa_f5.a, dqa_f5.b, dqa_f5.c, dqa_f5.ctid,
dqa_f5.tableoid, dqa_f5.gp_segment_id, dqa_f5.gp_foreign_server
+ Output: share0_ref1.a, share0_ref1.b, share0_ref1.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: dqa_f5.a, dqa_f5.b, dqa_f5.c
+ Hash Key: dqa_f5.c
+ -> Seq Scan on public.dqa_f5
+ Output: dqa_f5.a, dqa_f5.b, dqa_f5.c
-> Hash Join
Output: (count(DISTINCT share0_ref3.a)), (count(DISTINCT
share0_ref2.b))
Hash Cond: (NOT (share0_ref3.c IS DISTINCT FROM share0_ref2.c))
@@ -3329,42 +3325,143 @@ explain (verbose on, costs off) select count(distinct
a), count(distinct b) from
Output: count(DISTINCT share0_ref3.a), share0_ref3.c
Group Key: share0_ref3.c
-> Sort
- Output: share0_ref3.a, share0_ref3.c
+ Output: share0_ref3.a, share0_ref3.b, share0_ref3.c
Sort Key: share0_ref3.c
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Output: share0_ref3.a, share0_ref3.c
- Hash Key: share0_ref3.c
- -> Result
- Output: share0_ref3.a, share0_ref3.c
- -> Shared Scan (share slice:id 2:0)
- Output: share0_ref3.a,
share0_ref3.b, share0_ref3.c
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref3.a, share0_ref3.b,
share0_ref3.c
-> Hash
Output: (count(DISTINCT share0_ref2.b)), share0_ref2.c
-> GroupAggregate
Output: count(DISTINCT share0_ref2.b), share0_ref2.c
Group Key: share0_ref2.c
-> Sort
- Output: share0_ref2.b, share0_ref2.c
+ Output: share0_ref2.a, share0_ref2.b,
share0_ref2.c
Sort Key: share0_ref2.c
- -> Redistribute Motion 3:3 (slice3;
segments: 3)
- Output: share0_ref2.b, share0_ref2.c
- Hash Key: share0_ref2.c
- -> Result
- Output: share0_ref2.b,
share0_ref2.c
- -> Shared Scan (share slice:id
3:0)
- Output: share0_ref2.a,
share0_ref2.b, share0_ref2.c
- Settings: enable_groupagg = 'off', enable_hashagg = 'on',
gp_motion_cost_per_row = '2'
+ -> Shared Scan (share slice:id 1:0)
+ Output: share0_ref2.a, share0_ref2.b,
share0_ref2.c
+ Settings: gp_motion_cost_per_row = '2', enable_hashagg = 'on',
enable_groupagg = 'off'
Optimizer: GPORCA
-(41 rows)
+(34 rows)
select count(distinct a), count(distinct b) from dqa_f5 group by c;
count | count
-------+-------
1 | 1
- 0 | 0
1 | 1
+ 0 | 0
(3 rows)
reset optimizer_enable_multiple_distinct_aggs;
drop table dqa_f4;
drop table dqa_f5;
+-- Test MDQA on a partitioned table.
+-- Pre-fix, CXformGbAggWithMDQA2Join declared every column the child could
+-- produce on the CTE Producer, so unused and system columns with EUnknown
+-- usage leaked into the producer's column list and crashed ORCA on the
+-- partitioned-table path (failed assertion "col_ref->GetUsage() !=
+-- CColRef::EUnknown" in assert builds, SIGSEGV in release builds).
+set optimizer_enable_multiple_distinct_aggs=on;
+create table mdqa_part(a int, b int, c int, d int)
+distributed by (a)
+partition by range(a) (
+ start (0) end (100) every (50),
+ default partition other
+);
+insert into mdqa_part select i, i%5, i%3, i%7 from generate_series(1, 100) i;
+create index mdqa_part_b_idx on mdqa_part(b);
+analyze mdqa_part;
+explain (costs off)
+select a, count(distinct b) as cnt_b, count(distinct c) as cnt_c
+from mdqa_part where b between 0 and 2 group by a;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ -> Sequence
+ -> Shared Scan (share slice:id 1:0)
+ -> Dynamic Index Scan on mdqa_part_b_idx on mdqa_part
+ Index Cond: ((b >= 0) AND (b <= 2))
+ Number of partitions to scan: 3 (out of 3)
+ -> Hash Join
+ Hash Cond: (NOT (share0_ref3.a IS DISTINCT FROM share0_ref2.a))
+ -> GroupAggregate
+ Group Key: share0_ref3.a
+ -> Sort
+ Sort Key: share0_ref3.a
+ -> Shared Scan (share slice:id 1:0)
+ -> Hash
+ -> GroupAggregate
+ Group Key: share0_ref2.a
+ -> Sort
+ Sort Key: share0_ref2.a
+ -> Shared Scan (share slice:id 1:0)
+ Optimizer: GPORCA
+(20 rows)
+
+select a, count(distinct b) as cnt_b, count(distinct c) as cnt_c
+from mdqa_part where b between 0 and 2 group by a;
+ a | cnt_b | cnt_c
+-----+-------+-------
+ 2 | 1 | 1
+ 7 | 1 | 1
+ 16 | 1 | 1
+ 22 | 1 | 1
+ 27 | 1 | 1
+ 37 | 1 | 1
+ 41 | 1 | 1
+ 42 | 1 | 1
+ 45 | 1 | 1
+ 51 | 1 | 1
+ 55 | 1 | 1
+ 60 | 1 | 1
+ 65 | 1 | 1
+ 66 | 1 | 1
+ 70 | 1 | 1
+ 75 | 1 | 1
+ 77 | 1 | 1
+ 80 | 1 | 1
+ 81 | 1 | 1
+ 90 | 1 | 1
+ 92 | 1 | 1
+ 97 | 1 | 1
+ 5 | 1 | 1
+ 6 | 1 | 1
+ 10 | 1 | 1
+ 11 | 1 | 1
+ 17 | 1 | 1
+ 21 | 1 | 1
+ 25 | 1 | 1
+ 32 | 1 | 1
+ 52 | 1 | 1
+ 56 | 1 | 1
+ 62 | 1 | 1
+ 67 | 1 | 1
+ 82 | 1 | 1
+ 85 | 1 | 1
+ 96 | 1 | 1
+ 100 | 1 | 1
+ 1 | 1 | 1
+ 12 | 1 | 1
+ 15 | 1 | 1
+ 20 | 1 | 1
+ 26 | 1 | 1
+ 30 | 1 | 1
+ 31 | 1 | 1
+ 35 | 1 | 1
+ 36 | 1 | 1
+ 40 | 1 | 1
+ 46 | 1 | 1
+ 47 | 1 | 1
+ 50 | 1 | 1
+ 57 | 1 | 1
+ 61 | 1 | 1
+ 71 | 1 | 1
+ 72 | 1 | 1
+ 76 | 1 | 1
+ 86 | 1 | 1
+ 87 | 1 | 1
+ 91 | 1 | 1
+ 95 | 1 | 1
+(60 rows)
+
+reset optimizer_enable_multiple_distinct_aggs;
+drop table mdqa_part;
diff --git a/src/test/regress/sql/gp_dqa.sql b/src/test/regress/sql/gp_dqa.sql
index fc243d74e5e..6ad414127f3 100644
--- a/src/test/regress/sql/gp_dqa.sql
+++ b/src/test/regress/sql/gp_dqa.sql
@@ -616,3 +616,31 @@ select count(distinct a), count(distinct b) from dqa_f5
group by c;
reset optimizer_enable_multiple_distinct_aggs;
drop table dqa_f4;
drop table dqa_f5;
+
+-- Test MDQA on a partitioned table.
+-- Pre-fix, CXformGbAggWithMDQA2Join declared every column the child could
+-- produce on the CTE Producer, so unused and system columns with EUnknown
+-- usage leaked into the producer's column list and crashed ORCA on the
+-- partitioned-table path (failed assertion "col_ref->GetUsage() !=
+-- CColRef::EUnknown" in assert builds, SIGSEGV in release builds).
+set optimizer_enable_multiple_distinct_aggs=on;
+
+create table mdqa_part(a int, b int, c int, d int)
+distributed by (a)
+partition by range(a) (
+ start (0) end (100) every (50),
+ default partition other
+);
+insert into mdqa_part select i, i%5, i%3, i%7 from generate_series(1, 100) i;
+create index mdqa_part_b_idx on mdqa_part(b);
+analyze mdqa_part;
+
+explain (costs off)
+select a, count(distinct b) as cnt_b, count(distinct c) as cnt_c
+from mdqa_part where b between 0 and 2 group by a;
+
+select a, count(distinct b) as cnt_b, count(distinct c) as cnt_c
+from mdqa_part where b between 0 and 2 group by a;
+
+reset optimizer_enable_multiple_distinct_aggs;
+drop table mdqa_part;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
