This is an automated email from the ASF dual-hosted git repository.
avamingli 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 54fc80faa33 Fix writable CTE on replicated tables EXCEPT partition
tables.
54fc80faa33 is described below
commit 54fc80faa339446e563e8f55150faa509aafdd37
Author: Zhang Mingli <avamin...@gmail.com>
AuthorDate: Wed Aug 6 17:07:38 2025 +0800
Fix writable CTE on replicated tables EXCEPT partition tables.
Fix issue: https://github.com/apache/cloudberry/issues/1240
Replicated locus could EXCEPT Partitioned locus when there is
writable CTE on replicated tables.
We could make them SingleQE or Entry to do the set operation.
with result as (update r_1240 set a = a +1 where a <
5 returning *) select * from result except select * from p1_1240;
QUERY PLAN
---------------------------------------------------------------
HashSetOp Except
-> Append
-> Explicit Gather Motion 3:1 (slice1; segments: 3)
-> Subquery Scan on "*SELECT* 1"
-> Update on r_1240
-> Seq Scan on r_1240
Filter: (a < 5)
-> Gather Motion 3:1 (slice2; segments: 3)
-> Subquery Scan on "*SELECT* 2"
-> Seq Scan on p1_1240
Optimizer: Postgres-based planner
(11 rows)
Authored-by: Zhang Mingli avamin...@gmail.com
---
src/backend/cdb/cdbsetop.c | 3 +-
src/test/regress/expected/union_gp.out | 32 +++++++++++++++++++++
src/test/regress/expected/union_gp_optimizer.out | 36 ++++++++++++++++++++++++
src/test/regress/sql/union_gp.sql | 12 ++++++++
4 files changed, 82 insertions(+), 1 deletion(-)
diff --git a/src/backend/cdb/cdbsetop.c b/src/backend/cdb/cdbsetop.c
index 8be3341c9cb..3b3380704b0 100644
--- a/src/backend/cdb/cdbsetop.c
+++ b/src/backend/cdb/cdbsetop.c
@@ -88,6 +88,7 @@ choose_setop_type(List *pathlist, List *tlist_list)
break;
case CdbLocusType_Replicated:
+ ok_partitioned = false;
break;
case CdbLocusType_ReplicatedWorkers:
@@ -231,6 +232,7 @@ adjust_setop_arguments(PlannerInfo *root, List *pathlist,
List *tlist_list, GpSe
case CdbLocusType_HashedWorkers:
case CdbLocusType_HashedOJ:
case CdbLocusType_Strewn:
+ case CdbLocusType_Replicated:
/* Gather to QE. No need to
keep ordering. */
CdbPathLocus_MakeSingleQE(&locus, getgpsegmentCount());
adjusted_path =
cdbpath_create_motion_path(root, subpath, NULL, false,
@@ -254,7 +256,6 @@ adjust_setop_arguments(PlannerInfo *root, List *pathlist,
List *tlist_list, GpSe
case CdbLocusType_Entry:
case CdbLocusType_Null:
- case CdbLocusType_Replicated:
case CdbLocusType_ReplicatedWorkers:
case CdbLocusType_End:
ereport(ERROR,
(errcode(ERRCODE_INTERNAL_ERROR),
diff --git a/src/test/regress/expected/union_gp.out
b/src/test/regress/expected/union_gp.out
index 1cd6c47252c..5bdae3e887c 100644
--- a/src/test/regress/expected/union_gp.out
+++ b/src/test/regress/expected/union_gp.out
@@ -2310,6 +2310,38 @@ select json_data,id from my_table where
json_data->>'age' = '30' union all sele
set optimizer_parallel_union to off;
drop table if exists my_table;
--
+-- test Github issue https://github.com/apache/cloudberry/issues/1240
+--
+create table r_1240(a int) distributed replicated;
+insert into r_1240 select generate_series(1,10);
+create table p1_1240(a int) distributed by (a);
+insert into p1_1240 select generate_series(1,3);
+explain(costs off) with result as (update r_1240 set a = a +1 where a < 5
returning *) select * from result except select * from p1_1240;
+ QUERY PLAN
+---------------------------------------------------------------
+ HashSetOp Except
+ -> Append
+ -> Explicit Gather Motion 3:1 (slice1; segments: 3)
+ -> Subquery Scan on "*SELECT* 1"
+ -> Update on r_1240
+ -> Seq Scan on r_1240
+ Filter: (a < 5)
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Subquery Scan on "*SELECT* 2"
+ -> Seq Scan on p1_1240
+ Optimizer: Postgres-based planner
+(11 rows)
+
+with result as (update r_1240 set a = a +1 where a < 5 returning *) select *
from result except select * from p1_1240;
+ a
+---
+ 5
+ 4
+(2 rows)
+
+drop table r_1240;
+drop table p1_1240;
+--
-- Clean up
--
DROP TABLE IF EXISTS T_a1 CASCADE;
diff --git a/src/test/regress/expected/union_gp_optimizer.out
b/src/test/regress/expected/union_gp_optimizer.out
index 8b6e66c9d0e..8ff8655591d 100644
--- a/src/test/regress/expected/union_gp_optimizer.out
+++ b/src/test/regress/expected/union_gp_optimizer.out
@@ -2439,6 +2439,42 @@ select json_data,id from my_table where
json_data->>'age' = '30' union all sele
set optimizer_parallel_union to off;
drop table if exists my_table;
--
+-- test Github issue https://github.com/apache/cloudberry/issues/1240
+--
+create table r_1240(a int) distributed replicated;
+insert into r_1240 select generate_series(1,10);
+create table p1_1240(a int) distributed by (a);
+insert into p1_1240 select generate_series(1,3);
+explain(costs off) with result as (update r_1240 set a = a +1 where a < 5
returning *) select * from result except select * from p1_1240;
+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: RETURNING clause
+ QUERY PLAN
+---------------------------------------------------------------
+ HashSetOp Except
+ -> Append
+ -> Explicit Gather Motion 3:1 (slice1; segments: 3)
+ -> Subquery Scan on "*SELECT* 1"
+ -> Update on r_1240
+ -> Seq Scan on r_1240
+ Filter: (a < 5)
+ -> Gather Motion 3:1 (slice2; segments: 3)
+ -> Subquery Scan on "*SELECT* 2"
+ -> Seq Scan on p1_1240
+ Optimizer: Postgres-based planner
+(11 rows)
+
+with result as (update r_1240 set a = a +1 where a < 5 returning *) select *
from result except select * from p1_1240;
+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: RETURNING clause
+ a
+---
+ 5
+ 4
+(2 rows)
+
+drop table r_1240;
+drop table p1_1240;
+--
-- Clean up
--
DROP TABLE IF EXISTS T_a1 CASCADE;
diff --git a/src/test/regress/sql/union_gp.sql
b/src/test/regress/sql/union_gp.sql
index 124ac877836..e7cac952704 100644
--- a/src/test/regress/sql/union_gp.sql
+++ b/src/test/regress/sql/union_gp.sql
@@ -708,6 +708,18 @@ select json_data,id from my_table where json_data->>'age'
= '30' union all sele
set optimizer_parallel_union to off;
drop table if exists my_table;
+--
+-- test Github issue https://github.com/apache/cloudberry/issues/1240
+--
+create table r_1240(a int) distributed replicated;
+insert into r_1240 select generate_series(1,10);
+create table p1_1240(a int) distributed by (a);
+insert into p1_1240 select generate_series(1,3);
+explain(costs off) with result as (update r_1240 set a = a +1 where a < 5
returning *) select * from result except select * from p1_1240;
+with result as (update r_1240 set a = a +1 where a < 5 returning *) select *
from result except select * from p1_1240;
+drop table r_1240;
+drop table p1_1240;
+
--
-- Clean up
--
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@cloudberry.apache.org
For additional commands, e-mail: commits-h...@cloudberry.apache.org
