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