1# 2001 September 15 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13# This file implements tests for proper treatment of the special 14# value NULL. 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Create a table and some data to work with. 21# 22do_test null-1.0 { 23 execsql { 24 begin; 25 create table t1(a,b,c); 26 insert into t1 values(1,0,0); 27 insert into t1 values(2,0,1); 28 insert into t1 values(3,1,0); 29 insert into t1 values(4,1,1); 30 insert into t1 values(5,null,0); 31 insert into t1 values(6,null,1); 32 insert into t1 values(7,null,null); 33 commit; 34 select * from t1; 35 } 36} {1 0 0 2 0 1 3 1 0 4 1 1 5 {} 0 6 {} 1 7 {} {}} 37 38# Check for how arithmetic expressions handle NULL 39# 40do_test null-1.1 { 41 execsql { 42 select ifnull(a+b,99) from t1; 43 } 44} {1 2 4 5 99 99 99} 45do_test null-1.2 { 46 execsql { 47 select ifnull(b*c,99) from t1; 48 } 49} {0 0 0 1 99 99 99} 50 51# Check to see how the CASE expression handles NULL values. The 52# first WHEN for which the test expression is TRUE is selected. 53# FALSE and UNKNOWN test expressions are skipped. 54# 55do_test null-2.1 { 56 execsql { 57 select ifnull(case when b<>0 then 1 else 0 end, 99) from t1; 58 } 59} {0 0 1 1 0 0 0} 60do_test null-2.2 { 61 execsql { 62 select ifnull(case when not b<>0 then 1 else 0 end, 99) from t1; 63 } 64} {1 1 0 0 0 0 0} 65do_test null-2.3 { 66 execsql { 67 select ifnull(case when b<>0 and c<>0 then 1 else 0 end, 99) from t1; 68 } 69} {0 0 0 1 0 0 0} 70do_test null-2.4 { 71 execsql { 72 select ifnull(case when not (b<>0 and c<>0) then 1 else 0 end, 99) from t1; 73 } 74} {1 1 1 0 1 0 0} 75do_test null-2.5 { 76 execsql { 77 select ifnull(case when b<>0 or c<>0 then 1 else 0 end, 99) from t1; 78 } 79} {0 1 1 1 0 1 0} 80do_test null-2.6 { 81 execsql { 82 select ifnull(case when not (b<>0 or c<>0) then 1 else 0 end, 99) from t1; 83 } 84} {1 0 0 0 0 0 0} 85do_test null-2.7 { 86 execsql { 87 select ifnull(case b when c then 1 else 0 end, 99) from t1; 88 } 89} {1 0 0 1 0 0 0} 90do_test null-2.8 { 91 execsql { 92 select ifnull(case c when b then 1 else 0 end, 99) from t1; 93 } 94} {1 0 0 1 0 0 0} 95 96# Check to see that NULL values are ignored in aggregate functions. 97# 98do_test null-3.1 { 99 execsql { 100 select count(*), count(b), count(c), sum(b), sum(c), 101 avg(b), avg(c), min(b), max(b) from t1; 102 } 103} {7 4 6 2 3 0.5 0.5 0 1} 104 105# The sum of zero entries is a NULL, but the total of zero entries is 0. 106# 107do_test null-3.2 { 108 execsql { 109 SELECT sum(b), total(b) FROM t1 WHERE b<0 110 } 111} {{} 0.0} 112 113# Check to see how WHERE clauses handle NULL values. A NULL value 114# is the same as UNKNOWN. The WHERE clause should only select those 115# rows that are TRUE. FALSE and UNKNOWN rows are rejected. 116# 117do_test null-4.1 { 118 execsql { 119 select a from t1 where b<10 120 } 121} {1 2 3 4} 122do_test null-4.2 { 123 execsql { 124 select a from t1 where not b>10 125 } 126} {1 2 3 4} 127do_test null-4.3 { 128 execsql { 129 select a from t1 where b<10 or c=1; 130 } 131} {1 2 3 4 6} 132do_test null-4.4 { 133 execsql { 134 select a from t1 where b<10 and c=1; 135 } 136} {2 4} 137do_test null-4.5 { 138 execsql { 139 select a from t1 where not (b<10 and c=1); 140 } 141} {1 3 5} 142 143# The DISTINCT keyword on a SELECT statement should treat NULL values 144# as distinct 145# 146do_test null-5.1 { 147 execsql { 148 select distinct b from t1 order by b; 149 } 150} {{} 0 1} 151 152# A UNION to two queries should treat NULL values 153# as distinct 154# 155ifcapable compound { 156do_test null-6.1 { 157 execsql { 158 select b from t1 union select c from t1 order by c; 159 } 160} {{} 0 1} 161} ;# ifcapable compound 162 163# The UNIQUE constraint only applies to non-null values 164# 165ifcapable conflict { 166do_test null-7.1 { 167 execsql { 168 create table t2(a, b unique on conflict ignore); 169 insert into t2 values(1,1); 170 insert into t2 values(2,null); 171 insert into t2 values(3,null); 172 insert into t2 values(4,1); 173 select a from t2; 174 } 175 } {1 2 3} 176 do_test null-7.2 { 177 execsql { 178 create table t3(a, b, c, unique(b,c) on conflict ignore); 179 insert into t3 values(1,1,1); 180 insert into t3 values(2,null,1); 181 insert into t3 values(3,null,1); 182 insert into t3 values(4,1,1); 183 select a from t3; 184 } 185 } {1 2 3} 186} 187 188# Ticket #461 - Make sure nulls are handled correctly when doing a 189# lookup using an index. 190# 191do_test null-8.1 { 192 execsql { 193 CREATE TABLE t4(x,y); 194 INSERT INTO t4 VALUES(1,11); 195 INSERT INTO t4 VALUES(2,NULL); 196 SELECT x FROM t4 WHERE y=NULL; 197 } 198} {} 199ifcapable subquery { 200 do_test null-8.2 { 201 execsql { 202 SELECT x FROM t4 WHERE y IN (33,NULL); 203 } 204 } {} 205} 206do_test null-8.3 { 207 execsql { 208 SELECT x FROM t4 WHERE y<33 ORDER BY x; 209 } 210} {1} 211do_test null-8.4 { 212 execsql { 213 SELECT x FROM t4 WHERE y>6 ORDER BY x; 214 } 215} {1} 216do_test null-8.5 { 217 execsql { 218 SELECT x FROM t4 WHERE y!=33 ORDER BY x; 219 } 220} {1} 221do_test null-8.11 { 222 execsql { 223 CREATE INDEX t4i1 ON t4(y); 224 SELECT x FROM t4 WHERE y=NULL; 225 } 226} {} 227ifcapable subquery { 228 do_test null-8.12 { 229 execsql { 230 SELECT x FROM t4 WHERE y IN (33,NULL); 231 } 232 } {} 233} 234do_test null-8.13 { 235 execsql { 236 SELECT x FROM t4 WHERE y<33 ORDER BY x; 237 } 238} {1} 239do_test null-8.14 { 240 execsql { 241 SELECT x FROM t4 WHERE y>6 ORDER BY x; 242 } 243} {1} 244do_test null-8.15 { 245 execsql { 246 SELECT x FROM t4 WHERE y!=33 ORDER BY x; 247 } 248} {1} 249 250 251 252finish_test 253