avamingli commented on code in PR #1401:
URL: https://github.com/apache/cloudberry/pull/1401#discussion_r2443836206
##########
src/test/regress/sql/with.sql:
##########
@@ -1594,3 +1594,62 @@ 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 writable CTE still blocked with SELECT INTO (negative test)
+--
+CREATE TABLE t_writeable_test (a int);
+WITH write_cte AS (
+ INSERT INTO t_writeable_test VALUES (1) RETURNING *
+)
+SELECT a INTO t_should_fail FROM write_cte; -- should fail
+DROP TABLE t_writeable_test;
Review Comment:
Aren't these (test) cases duplicated with the existing ones?
ex:
```sql
--
-- 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;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
