peter-toth commented on a change in pull request #24831: [SPARK-19799][SQL] Support WITH clause in subqueries URL: https://github.com/apache/spark/pull/24831#discussion_r299875656
########## File path: sql/core/src/test/resources/sql-tests/results/cte.sql.out ########## @@ -98,16 +98,202 @@ struct<x:int> -- !query 8 -DROP VIEW IF EXISTS t +WITH t as ( + WITH t2 AS (SELECT 1) + SELECT * FROM t2 +) +SELECT * FROM t -- !query 8 schema -struct<> +struct<1:int> -- !query 8 output - +1 -- !query 9 -DROP VIEW IF EXISTS t2 +SELECT max(c) FROM ( + WITH t(c) AS (SELECT 1) + SELECT * FROM t +) -- !query 9 schema -struct<> +struct<max(c):int> -- !query 9 output +1 + + +-- !query 10 +SELECT ( + WITH t AS (SELECT 1) + SELECT * FROM t +) +-- !query 10 schema +struct<scalarsubquery():int> +-- !query 10 output +1 + + +-- !query 11 +WITH + t AS (SELECT 1), + t2 AS ( + WITH t AS (SELECT 2) + SELECT * FROM t + ) +SELECT * FROM t2 +-- !query 11 schema +struct<1:int> +-- !query 11 output +1 + + +-- !query 12 +WITH + t(c) AS (SELECT 1), + t2 AS ( + SELECT ( + SELECT max(c) FROM ( + WITH t(c) AS (SELECT 2) + SELECT * FROM t + ) + ) + ) +SELECT * FROM t2 +-- !query 12 schema +struct<scalarsubquery():int> +-- !query 12 output +1 + + +-- !query 13 +WITH + t AS (SELECT 1), + t2 AS ( + WITH t AS (SELECT 2), + t2 AS ( + WITH t AS (SELECT 3) + SELECT * FROM t + ) + SELECT * FROM t2 + ) +SELECT * FROM t2 +-- !query 13 schema +struct<2:int> +-- !query 13 output +2 + + +-- !query 14 +WITH t(c) AS (SELECT 1) +SELECT max(c) FROM ( + WITH t(c) AS (SELECT 2) + SELECT * FROM t +) +-- !query 14 schema +struct<max(c):int> +-- !query 14 output +2 + + +-- !query 15 +WITH t(c) AS (SELECT 1) +SELECT sum(c) FROM ( + SELECT max(c) AS c FROM ( + WITH t(c) AS (SELECT 2) + SELECT * FROM t + ) +) +-- !query 15 schema +struct<sum(c):bigint> +-- !query 15 output +2 + + +-- !query 16 +WITH t(c) AS (SELECT 1) +SELECT sum(c) FROM ( + WITH t(c) AS (SELECT 2) + SELECT max(c) AS c FROM ( + WITH t(c) AS (SELECT 3) + SELECT * FROM t + ) +) +-- !query 16 schema +struct<sum(c):bigint> +-- !query 16 output +3 + + +-- !query 17 +WITH t AS (SELECT 1) +SELECT ( + WITH t AS (SELECT 2) + SELECT * FROM t +) +-- !query 17 schema +struct<scalarsubquery():int> +-- !query 17 output +1 + + +-- !query 18 +WITH t AS (SELECT 1) +SELECT ( + SELECT ( + WITH t AS (SELECT 2) + SELECT * FROM t + ) +) +-- !query 18 schema +struct<scalarsubquery():int> +-- !query 18 output +1 + + +-- !query 19 +WITH t AS (SELECT 1) +SELECT ( + WITH t AS (SELECT 2) + SELECT ( + WITH t AS (SELECT 3) + SELECT * FROM t + ) +) +-- !query 19 schema +struct<scalarsubquery():int> +-- !query 19 output +1 + + +-- !query 20 +WITH r AS (SELECT * FROM r) +SELECT * FROM r +-- !query 20 schema +struct<> +-- !query 20 output +org.apache.spark.sql.AnalysisException Review comment: I have a WIP PR open https://github.com/apache/spark/pull/23531 that would add support for recursive queries (and subqueries and subquery expressions too). But these queries lack the `RECURSIVE` keyword and using an outer recursive reference in a subquery is not allowed (next query) according to the SQL standard so these will never become valid. But, this PR should be accepted first then could come https://github.com/apache/spark/pull/25029 and https://github.com/apache/spark/pull/23531 Actually I think I'm removing the test `WITH r AS (SELECT * FROM r) SELECT * FROM r;` because there is already a similar one in `cte.sql` and moving the `WITH r AS (SELECT (SELECT * FROM r)) SELECT * FROM r;` next to the existing one. ---------------------------------------------------------------- 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: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
