peter-toth commented on a change in pull request #23531: [SPARK-24497][SQL]
Support recursive SQL query
URL: https://github.com/apache/spark/pull/23531#discussion_r249968739
##########
File path: sql/core/src/test/resources/sql-tests/inputs/recursion.sql
##########
@@ -0,0 +1,291 @@
+-- List of configuration the test suite is run against:
Review comment:
I've merged the recursion related tests cases. Some of them required minor
changes to get it working, some can't be merged because Spark SQL lacks these
features:
1. Column alias in CTE declaration (not related to recursion)
```
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n + 1 FROM t WHERE n < 100
)
SELECT SUM(n) FROM t
```
2. RECURSIVE VIEW statements:
```
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
UNION ALL
SELECT n + 1 FROM nums WHERE n < 5
```
3. UNION combinator in CTE (this PR supports UNION ALL only)
```
-- This is an infinite loop with UNION ALL, but not with UNION
WITH RECURSIVE t(n) AS (
SELECT 1
UNION
SELECT 10 - n FROM t
)
SELECT * FROM t
```
~~4. LIMIT pushdown~~, added to PR
```
-- This'd be an infinite loop, but outside query reads only as much as needed
WITH RECURSIVE t AS (
VALUES (1) AS T(n)
UNION ALL
SELECT n + 1 FROM t
)
SELECT * FROM t LIMIT 10
```
5. Different type of anchor and recursive term
```
WITH RECURSIVE t AS (
SELECT '7' AS n
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t
```
6. WITH in subquery (not related to recursion)
```
SELECT count(*) FROM (
WITH RECURSIVE t AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM t WHERE n < 500
)
SELECT * FROM t
) AS t
WHERE n < (
SELECT count(*) FROM (
WITH RECURSIVE t AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM t WHERE n < 100
)
SELECT * FROM t WHERE n < 50000
) AS t WHERE n < 100
)
```
7. Forward reference (we don't have to deal with loops at least)
```
WITH RECURSIVE
x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
y(id) AS (values (1)
)
SELECT * FROM x
```
I left a few TODOs in the PR, will fix them soon.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
