Annotation of embedaddon/sqlite3/test/null.test, revision 1.1.1.1
1.1 misho 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:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Create a table and some data to work with.
21: #
22: do_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: #
40: do_test null-1.1 {
41: execsql {
42: select ifnull(a+b,99) from t1;
43: }
44: } {1 2 4 5 99 99 99}
45: do_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: #
55: do_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}
60: do_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}
65: do_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}
70: do_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}
75: do_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}
80: do_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}
85: do_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}
90: do_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: #
98: do_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: #
107: do_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: #
117: do_test null-4.1 {
118: execsql {
119: select a from t1 where b<10
120: }
121: } {1 2 3 4}
122: do_test null-4.2 {
123: execsql {
124: select a from t1 where not b>10
125: }
126: } {1 2 3 4}
127: do_test null-4.3 {
128: execsql {
129: select a from t1 where b<10 or c=1;
130: }
131: } {1 2 3 4 6}
132: do_test null-4.4 {
133: execsql {
134: select a from t1 where b<10 and c=1;
135: }
136: } {2 4}
137: do_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: #
146: do_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: #
155: # (Later:) We also take this opportunity to test the ability
156: # of an ORDER BY clause to bind to either SELECT of a UNION.
157: # The left-most SELECT is preferred. In standard SQL, only
158: # the left SELECT can be used. The ability to match an ORDER
159: # BY term to the right SELECT is an SQLite extension.
160: #
161: ifcapable compound {
162: do_test null-6.1 {
163: execsql {
164: select b from t1 union select c from t1 order by b;
165: }
166: } {{} 0 1}
167: do_test null-6.2 {
168: execsql {
169: select b from t1 union select c from t1 order by 1;
170: }
171: } {{} 0 1}
172: do_test null-6.3 {
173: execsql {
174: select b from t1 union select c from t1 order by t1.b;
175: }
176: } {{} 0 1}
177: do_test null-6.4 {
178: execsql {
179: select b from t1 union select c from t1 order by main.t1.b;
180: }
181: } {{} 0 1}
182: do_test null-6.5 {
183: catchsql {
184: select b from t1 union select c from t1 order by t1.a;
185: }
186: } {1 {1st ORDER BY term does not match any column in the result set}}
187: do_test null-6.6 {
188: catchsql {
189: select b from t1 union select c from t1 order by main.t1.a;
190: }
191: } {1 {1st ORDER BY term does not match any column in the result set}}
192: } ;# ifcapable compound
193:
194: # The UNIQUE constraint only applies to non-null values
195: #
196: ifcapable conflict {
197: do_test null-7.1 {
198: execsql {
199: create table t2(a, b unique on conflict ignore);
200: insert into t2 values(1,1);
201: insert into t2 values(2,null);
202: insert into t2 values(3,null);
203: insert into t2 values(4,1);
204: select a from t2;
205: }
206: } {1 2 3}
207: do_test null-7.2 {
208: execsql {
209: create table t3(a, b, c, unique(b,c) on conflict ignore);
210: insert into t3 values(1,1,1);
211: insert into t3 values(2,null,1);
212: insert into t3 values(3,null,1);
213: insert into t3 values(4,1,1);
214: select a from t3;
215: }
216: } {1 2 3}
217: }
218:
219: # Ticket #461 - Make sure nulls are handled correctly when doing a
220: # lookup using an index.
221: #
222: do_test null-8.1 {
223: execsql {
224: CREATE TABLE t4(x,y);
225: INSERT INTO t4 VALUES(1,11);
226: INSERT INTO t4 VALUES(2,NULL);
227: SELECT x FROM t4 WHERE y=NULL;
228: }
229: } {}
230: ifcapable subquery {
231: do_test null-8.2 {
232: execsql {
233: SELECT x FROM t4 WHERE y IN (33,NULL);
234: }
235: } {}
236: }
237: do_test null-8.3 {
238: execsql {
239: SELECT x FROM t4 WHERE y<33 ORDER BY x;
240: }
241: } {1}
242: do_test null-8.4 {
243: execsql {
244: SELECT x FROM t4 WHERE y>6 ORDER BY x;
245: }
246: } {1}
247: do_test null-8.5 {
248: execsql {
249: SELECT x FROM t4 WHERE y!=33 ORDER BY x;
250: }
251: } {1}
252: do_test null-8.11 {
253: execsql {
254: CREATE INDEX t4i1 ON t4(y);
255: SELECT x FROM t4 WHERE y=NULL;
256: }
257: } {}
258: ifcapable subquery {
259: do_test null-8.12 {
260: execsql {
261: SELECT x FROM t4 WHERE y IN (33,NULL);
262: }
263: } {}
264: }
265: do_test null-8.13 {
266: execsql {
267: SELECT x FROM t4 WHERE y<33 ORDER BY x;
268: }
269: } {1}
270: do_test null-8.14 {
271: execsql {
272: SELECT x FROM t4 WHERE y>6 ORDER BY x;
273: }
274: } {1}
275: do_test null-8.15 {
276: execsql {
277: SELECT x FROM t4 WHERE y!=33 ORDER BY x;
278: }
279: } {1}
280:
281:
282:
283: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to null.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test