Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/19653#discussion_r148903491
--- Diff:
sql/core/src/test/resources/sql-tests/results/null-handling.sql.out ---
@@ -0,0 +1,305 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 28
+
+
+-- !query 0
+create table t1(a int, b int, c int) using parquet
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+insert into t1 values(1,0,0)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+insert into t1 values(2,0,1)
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+insert into t1 values(3,1,0)
+-- !query 3 schema
+struct<>
+-- !query 3 output
+
+
+
+-- !query 4
+insert into t1 values(4,1,1)
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+insert into t1 values(5,null,0)
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+insert into t1 values(6,null,1)
+-- !query 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+insert into t1 values(7,null,null)
+-- !query 7 schema
+struct<>
+-- !query 7 output
+
+
+
+-- !query 8
+select a, b+c from t1
+-- !query 8 schema
+struct<a:int,(b + c):int>
+-- !query 8 output
+1 0
+2 1
+3 1
+4 2
+5 NULL
+6 NULL
+7 NULL
+
+
+-- !query 9
+select a+10, b*0 from t1
+-- !query 9 schema
+struct<(a + 10):int,(b * 0):int>
+-- !query 9 output
+11 0
+12 0
+13 0
+14 0
+15 NULL
+16 NULL
+17 NULL
+
+
+-- !query 10
+select distinct b from t1
+-- !query 10 schema
+struct<b:int>
+-- !query 10 output
+0
+1
+NULL
+
+
+-- !query 11
+select b from t1 union select b from t1
+-- !query 11 schema
+struct<b:int>
+-- !query 11 output
+0
+1
+NULL
+
+
+-- !query 12
+select a+20, case b when c then 1 else 0 end from t1
+-- !query 12 schema
+struct<(a + 20):int,CASE WHEN (b = c) THEN 1 ELSE 0 END:int>
+-- !query 12 output
+21 1
+22 0
+23 0
+24 1
+25 0
+26 0
+27 0
+
+
+-- !query 13
+select a+30, case c when b then 1 else 0 end from t1
+-- !query 13 schema
+struct<(a + 30):int,CASE WHEN (c = b) THEN 1 ELSE 0 END:int>
+-- !query 13 output
+31 1
+32 0
+33 0
+34 1
+35 0
+36 0
+37 0
+
+
+-- !query 14
+select a+40, case when b<>0 then 1 else 0 end from t1
+-- !query 14 schema
+struct<(a + 40):int,CASE WHEN (NOT (b = 0)) THEN 1 ELSE 0 END:int>
+-- !query 14 output
+41 0
+42 0
+43 1
+44 1
+45 0
+46 0
+47 0
+
+
+-- !query 15
+select a+50, case when not b<>0 then 1 else 0 end from t1
+-- !query 15 schema
+struct<(a + 50):int,CASE WHEN (NOT (NOT (b = 0))) THEN 1 ELSE 0 END:int>
+-- !query 15 output
+51 1
+52 1
+53 0
+54 0
+55 0
+56 0
+57 0
+
+
+-- !query 16
+select a+60, case when b<>0 and c<>0 then 1 else 0 end from t1
+-- !query 16 schema
+struct<(a + 60):int,CASE WHEN ((NOT (b = 0)) AND (NOT (c = 0))) THEN 1
ELSE 0 END:int>
+-- !query 16 output
+61 0
+62 0
+63 0
+64 1
+65 0
+66 0
+67 0
+
+
+-- !query 17
+select a+70, case when not (b<>0 and c<>0) then 1 else 0 end from t1
+-- !query 17 schema
+struct<(a + 70):int,CASE WHEN (NOT ((NOT (b = 0)) AND (NOT (c = 0)))) THEN
1 ELSE 0 END:int>
+-- !query 17 output
+71 1
+72 1
+73 1
+74 0
+75 1
+76 0
+77 0
+
+
+-- !query 18
+select a+80, case when b<>0 or c<>0 then 1 else 0 end from t1
+-- !query 18 schema
+struct<(a + 80):int,CASE WHEN ((NOT (b = 0)) OR (NOT (c = 0))) THEN 1 ELSE
0 END:int>
+-- !query 18 output
+81 0
+82 1
+83 1
+84 1
+85 0
+86 1
+87 0
+
+
+-- !query 19
+select a+90, case when not (b<>0 or c<>0) then 1 else 0 end from t1
+-- !query 19 schema
+struct<(a + 90):int,CASE WHEN (NOT ((NOT (b = 0)) OR (NOT (c = 0)))) THEN
1 ELSE 0 END:int>
+-- !query 19 output
+91 1
+92 0
+93 0
+94 0
+95 0
+96 0
+97 0
+
+
+-- !query 20
+select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1
+-- !query 20 schema
+struct<count(1):bigint,count(b):bigint,sum(b):bigint,avg(b):double,min(b):int,max(b):int>
+-- !query 20 output
+7 4 2 0.5 0 1
+
+
+-- !query 21
+select a+100 from t1 where b<10
+-- !query 21 schema
+struct<(a + 100):int>
+-- !query 21 output
+101
+102
+103
+104
+
+
+-- !query 22
+select a+110 from t1 where not b>10
+-- !query 22 schema
+struct<(a + 110):int>
+-- !query 22 output
+111
+112
+113
+114
+
+
+-- !query 23
+select a+120 from t1 where b<10 OR c=1
+-- !query 23 schema
+struct<(a + 120):int>
+-- !query 23 output
+121
+122
+123
+124
+126
+
+
+-- !query 24
+select a+130 from t1 where b<10 AND c=1
+-- !query 24 schema
+struct<(a + 130):int>
+-- !query 24 output
+132
+134
+
+
+-- !query 25
+select a+140 from t1 where not (b<10 AND c=1)
+-- !query 25 schema
+struct<(a + 140):int>
+-- !query 25 output
+141
+143
+145
+
+
+-- !query 26
+select a+150 from t1 where not (c=1 AND b<10)
+-- !query 26 schema
+struct<(a + 150):int>
+-- !query 26 output
+151
+153
+155
+
+
+-- !query 27
+drop table t1
+-- !query 27 schema
+struct<>
+-- !query 27 output
+
--- End diff --
```SQL
Table created.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
1 row(s) inserted.
Result Set 1
A B+C
1 0
2 1
3 1
4 2
5 -
6 -
7 -
7 rows selected.
Result Set 2
A+10 B*0
11 0
12 0
13 0
14 0
15 -
16 -
17 -
7 rows selected.
Result Set 3
B
1
-
0
3 rows selected.
Result Set 4
B
0
1
-
3 rows selected.
Result Set 5
A+20 CASEBWHENCTHEN1ELSE0END
21 1
22 0
23 0
24 1
25 0
26 0
27 0
7 rows selected.
Result Set 6
A+30 CASECWHENBTHEN1ELSE0END
31 1
32 0
33 0
34 1
35 0
36 0
37 0
7 rows selected.
Result Set 7
A+40 CASEWHENB<>0THEN1ELSE0END
41 0
42 0
43 1
44 1
45 0
46 0
47 0
7 rows selected.
Result Set 8
A+50 CASEWHENNOTB<>0THEN1ELSE0END
51 1
52 1
53 0
54 0
55 0
56 0
57 0
7 rows selected.
Result Set 9
A+60 CASEWHENB<>0ANDC<>0THEN1ELSE0END
61 0
62 0
63 0
64 1
65 0
66 0
67 0
7 rows selected.
Result Set 10
A+70 CASEWHENNOT(B<>0ANDC<>0)THEN1ELSE0END
71 1
72 1
73 1
74 0
75 1
76 0
77 0
7 rows selected.
Result Set 11
A+80 CASEWHENB<>0ORC<>0THEN1ELSE0END
81 0
82 1
83 1
84 1
85 0
86 1
87 0
7 rows selected.
Result Set 12
A+90 CASEWHENNOT(B<>0ORC<>0)THEN1ELSE0END
91 1
92 0
93 0
94 0
95 0
96 0
97 0
7 rows selected.
Result Set 13
COUNT(*) COUNT(B) SUM(B) AVG(B) MIN(B) MAX(B)
7 4 2 .5 0 1
Result Set 14
A+100
101
102
103
104
4 rows selected.
Result Set 15
A+110
111
112
113
114
4 rows selected.
Result Set 16
A+120
121
122
123
124
126
5 rows selected.
Result Set 17
A+130
132
134
2 rows selected.
Result Set 18
A+140
141
143
145
3 rows selected.
Result Set 19
A+150
151
153
155
3 rows selected.
Table dropped.
```
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
