This is an automated email from the ASF dual-hosted git repository.
maxyang 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 3f589f6159c Forbid writable CTE with SELECT INTO clause.
3f589f6159c is described below
commit 3f589f6159c01bbfcf656c034cd6a75fa4246b3e
Author: Zhang Mingli <[email protected]>
AuthorDate: Mon Jul 7 13:41:04 2025 +0800
Forbid writable CTE with SELECT INTO clause.
Fix issue: https://github.com/apache/cloudberry/issues/1214
Cloudberry currently only support CTEs with one writable clause,
SELECT INTO caluse with a writable CTE should also be forbidden
as it will create a new table with data inserted.
Authored-by: Zhang Mingli [email protected]
---
src/backend/parser/analyze.c | 19 +++++++++++++++++++
src/test/regress/expected/with.out | 25 +++++++++++++++++++++++++
src/test/regress/expected/with_optimizer.out | 25 +++++++++++++++++++++++++
src/test/regress/sql/with.sql | 19 +++++++++++++++++++
4 files changed, 88 insertions(+)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 8dd38d75e84..552d2e1aa28 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -340,6 +340,25 @@ transformOptionalSelectInto(ParseState *pstate, Node
*parseTree)
stmt->intoClause = NULL;
parseTree = (Node *) ctas;
+
+ if (stmt->withClause)
+ {
+ /*
+ * Just transform to check p_hasModifyingCTE,
cte list will be transformed inside SELECT stmt.
+ */
+ transformWithClause(pstate, stmt->withClause);
+ /*
+ * Since Cloudberry currently only support a
single writer gang, only one
+ * writable clause is permitted per CTE. Once
we get flexible gangs
+ * with more than one writer gang we can lift
this restriction.
+ */
+ if (pstate->p_hasModifyingCTE)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("writable CTE
queries cannot be used with writable queries"),
+ errdetail("Apache
Cloudberry currently only support CTEs with one writable clause, called in a
non-writable context."),
+ errhint("Rewrite the
query to only include one writable clause.")));
+ }
}
}
diff --git a/src/test/regress/expected/with.out
b/src/test/regress/expected/with.out
index 90ed303b7de..ca8771a01fc 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -3239,3 +3239,28 @@ select * from with_test;
(1 row)
drop table with_test;
+--
+-- writable CTE with SELECT INTO clause.
+--
+CREATE TABLE t_w_cte(a integer);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
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.
+EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
+ INSERT INTO t_w_cte(a) VALUES (1), (2), (3)
+ RETURNING a
+)
+SELECT sum(a) INTO t_w_cte_1 FROM ins;
+ERROR: writable CTE queries cannot be used with writable queries
+DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
+HINT: Rewrite the query to only include one writable clause.
+DROP TABLE t_w_cte;
+CREATE TABLE t_w_cte_relp(a integer) distributed replicated;
+EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
+ INSERT INTO t_w_cte_relp(a) VALUES (1), (2), (3)
+ RETURNING a
+)
+SELECT sum(a) INTO t_w_cte_relp_1 FROM ins;
+ERROR: writable CTE queries cannot be used with writable queries
+DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
+HINT: Rewrite the query to only include one writable clause.
+DROP TABLE t_w_cte_relp;
diff --git a/src/test/regress/expected/with_optimizer.out
b/src/test/regress/expected/with_optimizer.out
index acd06e1f4ba..ca81f919f2a 100644
--- a/src/test/regress/expected/with_optimizer.out
+++ b/src/test/regress/expected/with_optimizer.out
@@ -3259,3 +3259,28 @@ select * from with_test;
(1 row)
drop table with_test;
+--
+-- writable CTE with SELECT INTO clause.
+--
+CREATE TABLE t_w_cte(a integer);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
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.
+EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
+ INSERT INTO t_w_cte(a) VALUES (1), (2), (3)
+ RETURNING a
+)
+SELECT sum(a) INTO t_w_cte_1 FROM ins;
+ERROR: writable CTE queries cannot be used with writable queries
+DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
+HINT: Rewrite the query to only include one writable clause.
+DROP TABLE t_w_cte;
+CREATE TABLE t_w_cte_relp(a integer) distributed replicated;
+EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
+ INSERT INTO t_w_cte_relp(a) VALUES (1), (2), (3)
+ RETURNING a
+)
+SELECT sum(a) INTO t_w_cte_relp_1 FROM ins;
+ERROR: writable CTE queries cannot be used with writable queries
+DETAIL: Apache Cloudberry currently only support CTEs with one writable
clause, called in a non-writable context.
+HINT: Rewrite the query to only include one writable clause.
+DROP TABLE t_w_cte_relp;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index ca071d01a41..b211e3eceed 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1575,3 +1575,22 @@ create temp table with_test (i int);
with with_test as (select 42) insert into with_test select * from with_test;
select * from with_test;
drop table with_test;
+
+--
+-- writable CTE with SELECT INTO clause.
+--
+CREATE TABLE t_w_cte(a integer);
+EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
+ INSERT INTO t_w_cte(a) VALUES (1), (2), (3)
+ RETURNING a
+)
+SELECT sum(a) INTO t_w_cte_1 FROM ins;
+DROP TABLE t_w_cte;
+
+CREATE TABLE t_w_cte_relp(a integer) distributed replicated;
+EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
+ INSERT INTO t_w_cte_relp(a) VALUES (1), (2), (3)
+ RETURNING a
+)
+SELECT sum(a) INTO t_w_cte_relp_1 FROM ins;
+DROP TABLE t_w_cte_relp;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
