1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 8
-- !query 0
CREATE OR REPLACE TEMPORARY VIEW t1 AS SELECT * FROM VALUES
(-234), (145), (367), (975), (298)
as t1(int_col1)
-- !query 0 schema
struct<>
-- !query 0 output
-- !query 1
CREATE OR REPLACE TEMPORARY VIEW t2 AS SELECT * FROM VALUES
(-769, -244), (-800, -409), (940, 86), (-507, 304), (-367, 158)
as t2(int_col0, int_col1)
-- !query 1 schema
struct<>
-- !query 1 output
-- !query 2
SELECT
(SUM(COALESCE(t1.int_col1, t2.int_col0))),
((COALESCE(t1.int_col1, t2.int_col0)) * 2)
FROM t1
RIGHT JOIN t2
ON (t2.int_col0) = (t1.int_col1)
GROUP BY GREATEST(COALESCE(t2.int_col1, 109), COALESCE(t1.int_col1, -449)),
COALESCE(t1.int_col1, t2.int_col0)
HAVING (SUM(COALESCE(t1.int_col1, t2.int_col0)))
> ((COALESCE(t1.int_col1, t2.int_col0)) * 2)
-- !query 2 schema
struct<sum(coalesce(int_col1, int_col0)):bigint,(coalesce(int_col1, int_col0) * 2):int>
-- !query 2 output
-367 -734
-507 -1014
-769 -1538
-800 -1600
-- !query 3
CREATE OR REPLACE TEMPORARY VIEW t1 AS SELECT * FROM VALUES (97) as t1(int_col1)
-- !query 3 schema
struct<>
-- !query 3 output
-- !query 4
CREATE OR REPLACE TEMPORARY VIEW t2 AS SELECT * FROM VALUES (0) as t2(int_col1)
-- !query 4 schema
struct<>
-- !query 4 output
-- !query 5
set spark.sql.crossJoin.enabled = true
-- !query 5 schema
struct<key:string,value:string>
-- !query 5 output
spark.sql.crossJoin.enabled true
-- !query 6
SELECT *
FROM (
SELECT
COALESCE(t2.int_col1, t1.int_col1) AS int_col
FROM t1
LEFT JOIN t2 ON false
) t where (t.int_col) is not null
-- !query 6 schema
struct<int_col:int>
-- !query 6 output
97
-- !query 7
set spark.sql.crossJoin.enabled = false
-- !query 7 schema
struct<key:string,value:string>
-- !query 7 output
spark.sql.crossJoin.enabled false
|
