peter-toth commented on a change in pull request #24860: [SPARK-28034][SQL] Port with.sql URL: https://github.com/apache/spark/pull/24860#discussion_r300405091
########## File path: sql/core/src/test/resources/sql-tests/results/pgSQL/with.sql.out ########## @@ -0,0 +1,486 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 55 + + +-- !query 0 +set spark.sql.crossJoin.enabled=true +-- !query 0 schema +struct<key:string,value:string> +-- !query 0 output +spark.sql.crossJoin.enabled true + + +-- !query 1 +WITH q1(x,y) AS (SELECT 1,2) +SELECT * FROM q1, q1 AS q2 +-- !query 1 schema +struct<x:int,y:int,x:int,y:int> +-- !query 1 output +1 2 1 2 + + +-- !query 2 +CREATE TABLE department ( + id INTEGER, -- department ID + parent_department INTEGER, -- upper department ID + name string -- department name +) USING parquet +-- !query 2 schema +struct<> +-- !query 2 output + + + +-- !query 3 +INSERT INTO department VALUES (0, NULL, 'ROOT') +-- !query 3 schema +struct<> +-- !query 3 output + + + +-- !query 4 +INSERT INTO department VALUES (1, 0, 'A') +-- !query 4 schema +struct<> +-- !query 4 output + + + +-- !query 5 +INSERT INTO department VALUES (2, 1, 'B') +-- !query 5 schema +struct<> +-- !query 5 output + + + +-- !query 6 +INSERT INTO department VALUES (3, 2, 'C') +-- !query 6 schema +struct<> +-- !query 6 output + + + +-- !query 7 +INSERT INTO department VALUES (4, 2, 'D') +-- !query 7 schema +struct<> +-- !query 7 output + + + +-- !query 8 +INSERT INTO department VALUES (5, 0, 'E') +-- !query 8 schema +struct<> +-- !query 8 output + + + +-- !query 9 +INSERT INTO department VALUES (6, 4, 'F') +-- !query 9 schema +struct<> +-- !query 9 output + + + +-- !query 10 +INSERT INTO department VALUES (7, 5, 'G') +-- !query 10 schema +struct<> +-- !query 10 output + + + +-- !query 11 +CREATE TABLE tree( + id INTEGER, + parent_id INTEGER +) USING parquet +-- !query 11 schema +struct<> +-- !query 11 output + + + +-- !query 12 +INSERT INTO tree +VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3), + (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11) +-- !query 12 schema +struct<> +-- !query 12 output + + + +-- !query 13 +create table graph( f int, t int, label string ) USING parquet +-- !query 13 schema +struct<> +-- !query 13 output + + + +-- !query 14 +insert into graph values + (1, 2, 'arc 1 -> 2'), + (1, 3, 'arc 1 -> 3'), + (2, 3, 'arc 2 -> 3'), + (1, 4, 'arc 1 -> 4'), + (4, 5, 'arc 4 -> 5'), + (5, 1, 'arc 5 -> 1') +-- !query 14 schema +struct<> +-- !query 14 output + + + +-- !query 15 +CREATE TABLE y (a INTEGER) USING parquet +-- !query 15 schema +struct<> +-- !query 15 output + + + +-- !query 16 +INSERT INTO y SELECT EXPLODE(SEQUENCE(1, 10)) +-- !query 16 schema +struct<> +-- !query 16 output + + + +-- !query 17 +DROP TABLE y +-- !query 17 schema +struct<> +-- !query 17 output + + + +-- !query 18 +CREATE TABLE y (a INTEGER) USING parquet +-- !query 18 schema +struct<> +-- !query 18 output + + + +-- !query 19 +INSERT INTO y SELECT EXPLODE(SEQUENCE(1, 10)) +-- !query 19 schema +struct<> +-- !query 19 output + + + +-- !query 20 +with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q +-- !query 20 schema +struct<foo:int> +-- !query 20 output +42 + + +-- !query 21 +CREATE TABLE bug6051 USING parquet AS + SELECT EXPLODE(SEQUENCE(1,3)) AS i +-- !query 21 schema +struct<> +-- !query 21 output + + + +-- !query 22 +SELECT * FROM bug6051 +-- !query 22 schema +struct<i:int> +-- !query 22 output +1 +2 +3 + + +-- !query 23 +CREATE TABLE bug6051_2 (i int) USING parquet +-- !query 23 schema +struct<> +-- !query 23 output + + + +-- !query 24 +CREATE TABLE withz USING parquet AS SELECT i AS k, CAST(i || ' v' AS string) v FROM (SELECT EXPLODE(SEQUENCE(1, 16, 3)) i) +-- !query 24 schema +struct<> +-- !query 24 output + + + +-- !query 25 +SELECT * FROM withz ORDER BY k +-- !query 25 schema +struct<k:int,v:string> +-- !query 25 output +1 1 v +4 4 v +7 7 v +10 10 v +13 13 v +16 16 v + + +-- !query 26 +DROP TABLE withz +-- !query 26 schema +struct<> +-- !query 26 output + + + +-- !query 27 +TRUNCATE TABLE y +-- !query 27 schema +struct<> +-- !query 27 output + + + +-- !query 28 +INSERT INTO y SELECT EXPLODE(SEQUENCE(1, 3)) +-- !query 28 schema +struct<> +-- !query 28 output + + + +-- !query 29 +CREATE TABLE yy (a INTEGER) USING parquet +-- !query 29 schema +struct<> +-- !query 29 output + + + +-- !query 30 +SELECT * FROM y +-- !query 30 schema +struct<a:int> +-- !query 30 output +1 +2 +3 + + +-- !query 31 +SELECT * FROM yy +-- !query 31 schema +struct<a:int> +-- !query 31 output + + + +-- !query 32 +SELECT * FROM y +-- !query 32 schema +struct<a:int> +-- !query 32 output +1 +2 +3 + + +-- !query 33 +SELECT * FROM yy +-- !query 33 schema +struct<a:int> +-- !query 33 output + + + +-- !query 34 +CREATE TABLE parent ( id int, val string ) USING parquet +-- !query 34 schema +struct<> +-- !query 34 output + + + +-- !query 35 +INSERT INTO parent VALUES ( 1, 'p1' ) +-- !query 35 schema +struct<> +-- !query 35 output + + + +-- !query 36 +SELECT * FROM parent +-- !query 36 schema +struct<id:int,val:string> +-- !query 36 output +1 p1 + + +-- !query 37 +SELECT * FROM parent +-- !query 37 schema +struct<id:int,val:string> +-- !query 37 output +1 p1 + + +-- !query 38 +create table foo (with baz) +-- !query 38 schema +struct<> +-- !query 38 output +org.apache.spark.sql.catalyst.parser.ParseException + +DataType baz is not supported.(line 1, pos 23) + +== SQL == +create table foo (with baz) +-----------------------^^^ + + +-- !query 39 +-- fail, WITH is a reserved word +create table foo (with ordinality) +-- !query 39 schema +struct<> +-- !query 39 output +org.apache.spark.sql.catalyst.parser.ParseException + +DataType ordinality is not supported.(line 2, pos 23) + +== SQL == +-- fail, WITH is a reserved word +create table foo (with ordinality) +-----------------------^^^ + + +-- !query 40 +-- fail, WITH is a reserved word +with ordinality as (select 1 as x) select * from ordinality +-- !query 40 schema +struct<x:int> +-- !query 40 output +1 + + +-- !query 41 +WITH test AS (SELECT 42) INSERT INTO test VALUES (1) +-- !query 41 schema +struct<> +-- !query 41 output +org.apache.spark.sql.AnalysisException +unresolved operator 'InsertIntoTable 'UnresolvedRelation [test], false, false; Review comment: Yes, I created https://issues.apache.org/jira/browse/SPARK-28251 ---------------------------------------------------------------- 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]
