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:
```
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n + 1 FROM t WHERE n < 100
)
SELECT SUM(n) FROM t
```
- not related to recursion
- this is a good feature and can be addressed in a follow up PR
2. CREATE RECURSIVE VIEW statements
```
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
UNION ALL
SELECT n + 1 FROM nums WHERE n < 5
```
- `CREATE VIEW ... AS WITH RECURSIVE ...` form does work instead
- can be addressed in a follow up PR if required
3. UNION combinator in CTE
```
-- 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
```
- this PR supports UNION ALL only
- the same effect is possible with UNION ALL but requires helper columns
- I think this is a nice feature, can be addressed in a follow up PR
4. ~~LIMIT pushdown~~
```
-- 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
```
- added to this PR with some limitations
5. Different type of anchor and recursive terms
```
WITH RECURSIVE t AS (
SELECT '7' AS n
UNION ALL
SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t
```
- I don't think this is important in this PR, can be added in a follow
up if required
6. WITH in subquery
```
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
)
```
- not related to recursion
- WITH can be moved to the front to get it working
- I think it can be addressed in a follow up PR
7. Forward reference
```
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 don't think this is important, can be added in a follow up if
required
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]
