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 cf4f56aaf76 Fix readable CTE with SELECT INTO clause.
cf4f56aaf76 is described below
commit cf4f56aaf76f8fb6c2c13bb406f110a11c9f31b0
Author: hanwei <[email protected]>
AuthorDate: Fri Oct 17 10:46:59 2025 +0800
Fix readable CTE with SELECT INTO clause.
In https://github.com/apache/cloudberry/pull/1215 bugfix, it add a check
for writable CTE with SELECT INTO clause. It will invoke
`transformWithClause` twice, even it's readable CTE. But Cloudberry
currently only support one WITH clause per query level, so it will lead
to crash.
So we can give up invoking `transformWithClause` function when check and
iterate through each CTE to verify if it is writable.
At the same time, we add comprehensive test cases to validate.
---
src/backend/parser/analyze.c | 38 +++++++++------
src/test/regress/expected/with.out | 73 ++++++++++++++++++++++++++++
src/test/regress/expected/with_optimizer.out | 73 ++++++++++++++++++++++++++++
src/test/regress/sql/with.sql | 49 +++++++++++++++++++
4 files changed, 218 insertions(+), 15 deletions(-)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 552d2e1aa28..d8091c6c4f4 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -343,21 +343,29 @@ transformOptionalSelectInto(ParseState *pstate, Node
*parseTree)
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.")));
+ ListCell *lc;
+ foreach(lc, stmt->withClause->ctes)
+ {
+ CommonTableExpr *cte = (CommonTableExpr
*) lfirst(lc);
+ if (!IsA(cte->ctequery, SelectStmt))
+ {
+ /* must be a data-modifying
statement */
+ Assert(IsA(cte->ctequery,
InsertStmt) ||
+ IsA(cte->ctequery,
UpdateStmt) ||
+ IsA(cte->ctequery,
DeleteStmt));
+
+ /*
+ * 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.
+ */
+ 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 ca8771a01fc..6c643fec8d4 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -3264,3 +3264,76 @@ 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;
+--
+-- readable CTE with SELECT INTO clause.
+--
+WITH sel AS (
+ SELECT 1 as a
+)
+SELECT a INTO t_r_cte FROM sel;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_r_cte;
+ a
+---
+ 1
+(1 row)
+
+DROP TABLE t_r_cte;
+--
+-- Multiple readable CTEs with SELECT INTO
+--
+WITH cte1 AS (SELECT 1 as a),
+ cte2 AS (SELECT 2 as b)
+SELECT a, b INTO t_multi_r_cte FROM cte1, cte2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_multi_r_cte;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+DROP TABLE t_multi_r_cte;
+--
+-- Nested SELECT in CTE with SELECT INTO
+--
+WITH nested_cte AS (
+ SELECT * FROM (SELECT 100 as val, 'test'::text as name) subq
+)
+SELECT val, name INTO t_nested_r_cte FROM nested_cte;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_nested_r_cte;
+ val | name
+-----+------
+ 100 | test
+(1 row)
+
+DROP TABLE t_nested_r_cte;
+--
+-- CTE with JOIN and SELECT INTO
+--
+WITH cte1 AS (SELECT 1 as id, 'foo' as val),
+ cte2 AS (SELECT 1 as id, 'bar' as val)
+SELECT cte1.id, cte1.val as val1, cte2.val as val2
+INTO t_join_r_cte
+FROM cte1 JOIN cte2 ON cte1.id = cte2.id;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_join_r_cte;
+ id | val1 | val2
+----+------+------
+ 1 | foo | bar
+(1 row)
+
+DROP TABLE t_join_r_cte;
+--
+-- Verify mixed readable and writable CTEs blocked (negative test)
+--
+CREATE TABLE t_mixed_test (a int);
+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.
+WITH read_cte AS (SELECT 1 as x),
+ write_cte AS (INSERT INTO t_mixed_test VALUES (1) RETURNING *)
+SELECT x INTO t_should_fail2 FROM read_cte; -- should fail
+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_mixed_test;
diff --git a/src/test/regress/expected/with_optimizer.out
b/src/test/regress/expected/with_optimizer.out
index ca81f919f2a..76404cfde44 100644
--- a/src/test/regress/expected/with_optimizer.out
+++ b/src/test/regress/expected/with_optimizer.out
@@ -3284,3 +3284,76 @@ 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;
+--
+-- readable CTE with SELECT INTO clause.
+--
+WITH sel AS (
+ SELECT 1 as a
+)
+SELECT a INTO t_r_cte FROM sel;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_r_cte;
+ a
+---
+ 1
+(1 row)
+
+DROP TABLE t_r_cte;
+--
+-- Multiple readable CTEs with SELECT INTO
+--
+WITH cte1 AS (SELECT 1 as a),
+ cte2 AS (SELECT 2 as b)
+SELECT a, b INTO t_multi_r_cte FROM cte1, cte2;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_multi_r_cte;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+DROP TABLE t_multi_r_cte;
+--
+-- Nested SELECT in CTE with SELECT INTO
+--
+WITH nested_cte AS (
+ SELECT * FROM (SELECT 100 as val, 'test'::text as name) subq
+)
+SELECT val, name INTO t_nested_r_cte FROM nested_cte;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_nested_r_cte;
+ val | name
+-----+------
+ 100 | test
+(1 row)
+
+DROP TABLE t_nested_r_cte;
+--
+-- CTE with JOIN and SELECT INTO
+--
+WITH cte1 AS (SELECT 1 as id, 'foo' as val),
+ cte2 AS (SELECT 1 as id, 'bar' as val)
+SELECT cte1.id, cte1.val as val1, cte2.val as val2
+INTO t_join_r_cte
+FROM cte1 JOIN cte2 ON cte1.id = cte2.id;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+SELECT * FROM t_join_r_cte;
+ id | val1 | val2
+----+------+------
+ 1 | foo | bar
+(1 row)
+
+DROP TABLE t_join_r_cte;
+--
+-- Verify mixed readable and writable CTEs blocked (negative test)
+--
+CREATE TABLE t_mixed_test (a int);
+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.
+WITH read_cte AS (SELECT 1 as x),
+ write_cte AS (INSERT INTO t_mixed_test VALUES (1) RETURNING *)
+SELECT x INTO t_should_fail2 FROM read_cte; -- should fail
+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_mixed_test;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index b211e3eceed..0b3da6cd98c 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1594,3 +1594,52 @@ EXPLAIN(COSTS OFF, VERBOSE) WITH ins AS (
)
SELECT sum(a) INTO t_w_cte_relp_1 FROM ins;
DROP TABLE t_w_cte_relp;
+
+--
+-- readable CTE with SELECT INTO clause.
+--
+WITH sel AS (
+ SELECT 1 as a
+)
+SELECT a INTO t_r_cte FROM sel;
+SELECT * FROM t_r_cte;
+DROP TABLE t_r_cte;
+
+--
+-- Multiple readable CTEs with SELECT INTO
+--
+WITH cte1 AS (SELECT 1 as a),
+ cte2 AS (SELECT 2 as b)
+SELECT a, b INTO t_multi_r_cte FROM cte1, cte2;
+SELECT * FROM t_multi_r_cte;
+DROP TABLE t_multi_r_cte;
+
+--
+-- Nested SELECT in CTE with SELECT INTO
+--
+WITH nested_cte AS (
+ SELECT * FROM (SELECT 100 as val, 'test'::text as name) subq
+)
+SELECT val, name INTO t_nested_r_cte FROM nested_cte;
+SELECT * FROM t_nested_r_cte;
+DROP TABLE t_nested_r_cte;
+
+--
+-- CTE with JOIN and SELECT INTO
+--
+WITH cte1 AS (SELECT 1 as id, 'foo' as val),
+ cte2 AS (SELECT 1 as id, 'bar' as val)
+SELECT cte1.id, cte1.val as val1, cte2.val as val2
+INTO t_join_r_cte
+FROM cte1 JOIN cte2 ON cte1.id = cte2.id;
+SELECT * FROM t_join_r_cte;
+DROP TABLE t_join_r_cte;
+
+--
+-- Verify mixed readable and writable CTEs blocked (negative test)
+--
+CREATE TABLE t_mixed_test (a int);
+WITH read_cte AS (SELECT 1 as x),
+ write_cte AS (INSERT INTO t_mixed_test VALUES (1) RETURNING *)
+SELECT x INTO t_should_fail2 FROM read_cte; -- should fail
+DROP TABLE t_mixed_test;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
