3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
13 # This file implements tests for proper treatment of the special
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Create a table and some data to work with.
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);
36 } {1 0 0 2 0 1 3 1 0 4 1 1 5 {} 0 6 {} 1 7 {} {}}
38 # Check for how arithmetic expressions handle NULL
42 select ifnull(a+b,99) from t1;
47 select ifnull(b*c,99) from t1;
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.
57 select ifnull(case when b<>0 then 1 else 0 end, 99) from t1;
62 select ifnull(case when not b<>0 then 1 else 0 end, 99) from t1;
67 select ifnull(case when b<>0 and c<>0 then 1 else 0 end, 99) from t1;
72 select ifnull(case when not (b<>0 and c<>0) then 1 else 0 end, 99) from t1;
77 select ifnull(case when b<>0 or c<>0 then 1 else 0 end, 99) from t1;
82 select ifnull(case when not (b<>0 or c<>0) then 1 else 0 end, 99) from t1;
87 select ifnull(case b when c then 1 else 0 end, 99) from t1;
92 select ifnull(case c when b then 1 else 0 end, 99) from t1;
96 # Check to see that NULL values are ignored in aggregate functions.
100 select count(*), count(b), count(c), sum(b), sum(c),
101 avg(b), avg(c), min(b), max(b) from t1;
103 } {7 4 6 2.0 3.0 0.5 0.5 0 1}
105 # Check to see how WHERE clauses handle NULL values. A NULL value
106 # is the same as UNKNOWN. The WHERE clause should only select those
107 # rows that are TRUE. FALSE and UNKNOWN rows are rejected.
111 select a from t1 where b<10
116 select a from t1 where not b>10
121 select a from t1 where b<10 or c=1;
126 select a from t1 where b<10 and c=1;
131 select a from t1 where not (b<10 and c=1);
135 # The DISTINCT keyword on a SELECT statement should treat NULL values
140 select distinct b from t1 order by b;
144 # A UNION to two queries should treat NULL values
150 select b from t1 union select c from t1 order by c;
153 } ;# ifcapable compound
155 # The UNIQUE constraint only applies to non-null values
159 create table t2(a, b unique on conflict ignore);
160 insert into t2 values(1,1);
161 insert into t2 values(2,null);
162 insert into t2 values(3,null);
163 insert into t2 values(4,1);
169 create table t3(a, b, c, unique(b,c) on conflict ignore);
170 insert into t3 values(1,1,1);
171 insert into t3 values(2,null,1);
172 insert into t3 values(3,null,1);
173 insert into t3 values(4,1,1);
178 # Ticket #461 - Make sure nulls are handled correctly when doing a
179 # lookup using an index.
183 CREATE TABLE t4(x,y);
184 INSERT INTO t4 VALUES(1,11);
185 INSERT INTO t4 VALUES(2,NULL);
186 SELECT x FROM t4 WHERE y=NULL;
192 SELECT x FROM t4 WHERE y IN (33,NULL);
198 SELECT x FROM t4 WHERE y<33 ORDER BY x;
203 SELECT x FROM t4 WHERE y>6 ORDER BY x;
208 SELECT x FROM t4 WHERE y!=33 ORDER BY x;
213 CREATE INDEX t4i1 ON t4(y);
214 SELECT x FROM t4 WHERE y=NULL;
220 SELECT x FROM t4 WHERE y IN (33,NULL);
226 SELECT x FROM t4 WHERE y<33 ORDER BY x;
231 SELECT x FROM t4 WHERE y>6 ORDER BY x;
236 SELECT x FROM t4 WHERE y!=33 ORDER BY x;