maropu commented on a change in pull request #23531: [SPARK-24497][SQL] Support recursive SQL query URL: https://github.com/apache/spark/pull/23531#discussion_r307092786
########## File path: sql/core/src/test/resources/sql-tests/results/cte.sql.out ########## @@ -328,16 +328,891 @@ struct<scalarsubquery():int> -- !query 25 -DROP VIEW IF EXISTS t +WITH r(level) AS ( + VALUES (0) + UNION ALL + SELECT level + 1 FROM r WHERE level < 10 +) +SELECT * FROM r -- !query 25 schema struct<> -- !query 25 output - +org.apache.spark.sql.AnalysisException +Table or view not found: r; line 4 pos 24 -- !query 26 -DROP VIEW IF EXISTS t2 +WITH RECURSIVE r(level) AS ( + VALUES (0) + UNION ALL + SELECT level + 1 FROM r WHERE level < 10 +) +SELECT * FROM r -- !query 26 schema -struct<> +struct<level:int> -- !query 26 output +0 +1 +10 +2 +3 +4 +5 +6 +7 +8 +9 + + +-- !query 27 +WITH RECURSIVE r(level) AS ( + VALUES (0) + UNION ALL + SELECT level + 1 FROM r +) +SELECT * FROM r +-- !query 27 schema +struct<> +-- !query 27 output +org.apache.spark.SparkException +Recursion level limit 100 reached but query has not exhausted, try increasing spark.sql.cte.recursion.level.limit + + +-- !query 28 +WITH RECURSIVE r(level) AS ( + VALUES (0) + UNION ALL + SELECT level + 1 FROM r +) +SELECT * FROM r LIMIT 10 +-- !query 28 schema +struct<level:int> +-- !query 28 output +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 + + +-- !query 29 +WITH RECURSIVE r(level) AS ( + VALUES (0) + UNION ALL + SELECT level + 1 FROM r +) +SELECT level, level FROM r LIMIT 10 +-- !query 29 schema +struct<level:int,level:int> +-- !query 29 output +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 + + +-- !query 30 +WITH RECURSIVE r(level) AS ( + VALUES (0) + UNION ALL + SELECT level + 1 FROM r +) +SELECT level, level FROM r ORDER BY level LIMIT 10 +-- !query 30 schema +struct<> +-- !query 30 output +org.apache.spark.SparkException +Recursion level limit 100 reached but query has not exhausted, try increasing spark.sql.cte.recursion.level.limit + + +-- !query 31 +WITH RECURSIVE r(c) AS ( + SELECT 'a' + UNION ALL + SELECT c || ' b' FROM r WHERE LENGTH(c) < 10 +) +SELECT * FROM r +-- !query 31 schema +struct<c:string> +-- !query 31 output +a +a b +a b b +a b b b +a b b b b +a b b b b b + + +-- !query 32 +WITH RECURSIVE r(level) AS ( + SELECT level + 1 FROM r WHERE level < 10 + UNION ALL + VALUES (0) +) +SELECT * FROM r +-- !query 32 schema +struct<level:int> +-- !query 32 output +0 +1 +10 +2 +3 +4 +5 +6 +7 +8 +9 + + +-- !query 33 +WITH RECURSIVE r(level, data) AS ( + VALUES (0, 'A') + UNION ALL + VALUES (0, 'B') + UNION ALL + SELECT level + 1, data || 'C' FROM r WHERE level < 3 +) +SELECT * FROM r +-- !query 33 schema +struct<level:int,data:string> +-- !query 33 output +0 A +0 B +1 AC +1 BC +2 ACC +2 BCC +3 ACCC +3 BCCC + + +-- !query 34 +WITH RECURSIVE r(level, data) AS ( + VALUES (0, 'A') + UNION ALL + SELECT level + 1, data || 'B' FROM r WHERE level < 2 + UNION ALL + SELECT level + 1, data || 'C' FROM r WHERE level < 3 +) +SELECT * FROM r Review comment: ``` psql:with-recursive.sql:86: ERROR: recursive reference to query "r" must not appear within its non-recursive term LINE 4: SELECT level + 1, data || 'B' FROM r WHERE level < 2 ``` ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org