sql-tests/inputs/null-handling.sql

0001 -- Create a test table with data
0002 create table t1(a int, b int, c int) using parquet;
0003 insert into t1 values(1,0,0);
0004 insert into t1 values(2,0,1);
0005 insert into t1 values(3,1,0);
0006 insert into t1 values(4,1,1);
0007 insert into t1 values(5,null,0);
0008 insert into t1 values(6,null,1);
0009 insert into t1 values(7,null,null);
0010
0011 -- Adding anything to null gives null
0012 select a, b+c from t1;
0013
0014 -- Multiplying null by zero gives null
0015 select a+10, b*0 from t1;
0016
0017 -- nulls are NOT distinct in SELECT DISTINCT
0018 select distinct b from t1;
0019
0020 -- nulls are NOT distinct in UNION
0021 select b from t1 union select b from t1;
0022
0023 -- CASE WHEN null THEN 1 ELSE 0 END is 0
0024 select a+20, case b when c then 1 else 0 end from t1;
0025 select a+30, case c when b then 1 else 0 end from t1;
0026 select a+40, case when b<>0 then 1 else 0 end from t1;
0027 select a+50, case when not b<>0 then 1 else 0 end from t1;
0028 select a+60, case when b<>0 and c<>0 then 1 else 0 end from t1;
0029
0030 -- "not (null AND false)" is true
0031 select a+70, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
0032
0033 -- "null OR true" is true
0034 select a+80, case when b<>0 or c<>0 then 1 else 0 end from t1;
0035 select a+90, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
0036
0037 -- null with aggregate operators
0038 select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;
0039
0040 -- Check the behavior of NULLs in WHERE clauses
0041 select a+100 from t1 where b<10;
0042 select a+110 from t1 where not b>10;
0043 select a+120 from t1 where b<10 OR c=1;
0044 select a+130 from t1 where b<10 AND c=1;
0045 select a+140 from t1 where not (b<10 AND c=1);
0046 select a+150 from t1 where not (c=1 AND b<10);
0047
0048 drop table t1;