uros-b commented on code in PR #59917:
URL: https://github.com/apache/spark/pull/59917#discussion_r4423565098
##########
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:
“allowed only on the preserved side of a left outer join” is incomplete.
checkIfSelfReferenceIsPlacedCorrectly also allows the self-ref on the right of
RightOuter. This file already has that ON-join case (“recursive reference is
allowed on right side of a right outer join”). USING/NATURAL lower to a plain
Join of that type, so FROM (VALUES (0)) AS t(data) RIGHT OUTER JOIN r USING
(data) should succeed. The new block has no right-outer USING/NATURAL case.
--
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]
