uros-b commented on code in PR #59917:
URL: https://github.com/apache/spark/pull/59917#discussion_r4423563581
##########
sql/core/src/test/resources/sql-tests/inputs/cte-recursion.sql:
##########
@@ -832,3 +832,297 @@ WITH RECURSIVE t AS (
WHERE n <> m
)
SELECT * FROM t;
+
+-- Recursive CTE self-reference under INNER JOIN ... USING
+WITH RECURSIVE r(level, data) AS (
+ VALUES (0, 0)
+ UNION ALL
+ SELECT level + 1, data
+ FROM r INNER JOIN (VALUES (0)) AS t(data) USING (data)
+ WHERE level < 2
+)
+SELECT * FROM r;
+
+-- Recursive CTE self-reference under NATURAL INNER JOIN
+WITH RECURSIVE r(level, data) AS (
+ VALUES (0, 0)
+ UNION ALL
+ SELECT level + 1, data
+ FROM r NATURAL INNER JOIN (VALUES (0)) AS t(data)
+ WHERE level < 2
+)
+SELECT * FROM r;
+
+-- Recursive CTE self-reference on the right side of INNER JOIN ... USING and
NATURAL INNER JOIN
+WITH RECURSIVE r(level, data) AS (
+ VALUES (0, 0)
+ UNION ALL
+ SELECT level + 1, data
+ FROM (VALUES (0)) AS t(data) INNER JOIN r USING (data)
+ WHERE level < 2
+)
+SELECT * FROM r;
+
+WITH RECURSIVE r(level, data) AS (
+ VALUES (0, 0)
+ UNION ALL
+ SELECT level + 1, data
+ FROM (VALUES (0)) AS t(data) NATURAL INNER JOIN r
+ WHERE level < 2
+)
+SELECT * FROM r;
+
+-- The other side's non-key columns stay visible after the USING join
+WITH RECURSIVE r(level, data) AS (
+ VALUES (0, 0)
+ UNION ALL
+ SELECT level + 1, data
+ FROM r INNER JOIN (VALUES (0, 'x')) AS t(data, tag) USING (data)
+ WHERE level < 2 AND tag = 'x'
+)
+SELECT * FROM r;
+
+-- Recursive CTE self-reference under outer joins with USING and NATURAL:
allowed only on the
+-- preserved side of a left outer join.
Review Comment:
+1 with the comment above
--
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]
