[GitHub] [spark] dongjoon-hyun commented on a change in pull request #24860: [SPARK-28034][SQL][TEST] Port with.sql

[GitBox <gi...@apache.org>]

dongjoon-hyun commented on a change in pull request #24860: [SPARK-28034][SQL][TEST] Port with.sql
URL: https://github.com/apache/spark/pull/24860#discussion_r302257988
 
 

 ##########
 File path: sql/core/src/test/resources/sql-tests/results/pgSQL/with.sql.out
 ##########
 @@ -0,0 +1,479 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 52
+
+
+-- !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
+SELECT count(*) FROM (
+  WITH q1(x) AS (SELECT rand() FROM (SELECT EXPLODE(SEQUENCE(1, 5))))
+    SELECT * FROM q1
+  UNION
+    SELECT * FROM q1
+) ss
+-- !query 2 schema
+struct<count(1):bigint>
+-- !query 2 output
+10
+
+
+-- !query 3
+CREATE TABLE department (
+	id INTEGER,  -- department ID
+	parent_department INTEGER, -- upper department ID
+	name string -- department name
+) USING parquet
+-- !query 3 schema
+struct<>
+-- !query 3 output
+
+
+
+-- !query 4
+INSERT INTO department VALUES (0, NULL, 'ROOT')
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+INSERT INTO department VALUES (1, 0, 'A')
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+INSERT INTO department VALUES (2, 1, 'B')
+-- !query 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+INSERT INTO department VALUES (3, 2, 'C')
+-- !query 7 schema
+struct<>
+-- !query 7 output
+
+
+
+-- !query 8
+INSERT INTO department VALUES (4, 2, 'D')
+-- !query 8 schema
+struct<>
+-- !query 8 output
+
+
+
+-- !query 9
+INSERT INTO department VALUES (5, 0, 'E')
+-- !query 9 schema
+struct<>
+-- !query 9 output
+
+
+
+-- !query 10
+INSERT INTO department VALUES (6, 4, 'F')
+-- !query 10 schema
+struct<>
+-- !query 10 output
+
+
+
+-- !query 11
+INSERT INTO department VALUES (7, 5, 'G')
+-- !query 11 schema
+struct<>
+-- !query 11 output
+
+
+
+-- !query 12
+CREATE TABLE tree(
+    id INTEGER,
+    parent_id INTEGER
+) USING parquet
+-- !query 12 schema
+struct<>
+-- !query 12 output
+
+
+
+-- !query 13
+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 13 schema
+struct<>
+-- !query 13 output
+
+
+
+-- !query 14
+create table graph( f int, t int, label string ) USING parquet
+-- !query 14 schema
+struct<>
+-- !query 14 output
+
+
+
+-- !query 15
+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 15 schema
+struct<>
+-- !query 15 output
+
+
+
+-- !query 16
+CREATE TABLE y (a INTEGER) USING parquet
+-- !query 16 schema
+struct<>
+-- !query 16 output
+
+
+
+-- !query 17
+INSERT INTO y SELECT EXPLODE(SEQUENCE(1, 10))
+-- !query 17 schema
+struct<>
+-- !query 17 output
+
+
+
+-- !query 18
+DROP TABLE y
+-- !query 18 schema
+struct<>
+-- !query 18 output
+
+
+
+-- !query 19
+CREATE TABLE y (a INTEGER) USING parquet
+-- !query 19 schema
+struct<>
+-- !query 19 output
+
+
+
+-- !query 20
+INSERT INTO y SELECT EXPLODE(SEQUENCE(1, 10))
+-- !query 20 schema
+struct<>
+-- !query 20 output
+
+
+
+-- !query 21
+with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q
+-- !query 21 schema
+struct<foo:int>
+-- !query 21 output
+42
+
+
+-- !query 22
+WITH outermost(x) AS (
+  SELECT 1
+  UNION (WITH innermost as (SELECT 2)
+         SELECT * FROM innermost
+         UNION SELECT 3)
+)
+SELECT * FROM outermost ORDER BY 1
+-- !query 22 schema
+struct<x:int>
+-- !query 22 output
+1
+2
+3
+
+
+-- !query 23
+WITH outermost(x) AS (
+  SELECT 1
+  UNION (WITH innermost as (SELECT 2)
+         SELECT * FROM outermost  -- fail
+         UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost ORDER BY 1
+-- !query 23 schema
+struct<>
+-- !query 23 output
+org.apache.spark.sql.AnalysisException
+Table or view not found: outermost; line 4 pos 23
+
+
+-- !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)
+-----------------------^^^
 
 Review comment:
   With `set spark.sql.parser.ansi.enabled=true`, this will raise the following.
   ```
   Error in query:
   no viable alternative at input 'with'(line 1, pos 18)
   
   == SQL ==
   create table foo (with baz)
   ------------------^^^
   ```

----------------------------------------------------------------
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:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org