Annotation of embedaddon/sqlite3/test/where4.test, revision 1.1.1.1
1.1 misho 1: # 2006 October 27
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. The
12: # focus of this file is testing the use of indices in WHERE clauses.
13: # This file was created when support for optimizing IS NULL phrases
14: # was added. And so the principle purpose of this file is to test
15: # that IS NULL phrases are correctly optimized. But you can never
16: # have too many tests, so some other tests are thrown in as well.
17: #
18: # $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $
19:
20: set testdir [file dirname $argv0]
21: source $testdir/tester.tcl
22:
23: ifcapable !tclvar||!bloblit {
24: finish_test
25: return
26: }
27:
28: # Build some test data
29: #
30: do_test where4-1.0 {
31: execsql {
32: CREATE TABLE t1(w, x, y);
33: CREATE INDEX i1wxy ON t1(w,x,y);
34: INSERT INTO t1 VALUES(1,2,3);
35: INSERT INTO t1 VALUES(1,NULL,3);
36: INSERT INTO t1 VALUES('a','b','c');
37: INSERT INTO t1 VALUES('a',NULL,'c');
38: INSERT INTO t1 VALUES(X'78',x'79',x'7a');
39: INSERT INTO t1 VALUES(X'78',NULL,X'7A');
40: INSERT INTO t1 VALUES(NULL,NULL,NULL);
41: SELECT count(*) FROM t1;
42: }
43: } {7}
44:
45: # Do an SQL statement. Append the search count to the end of the result.
46: #
47: proc count sql {
48: set ::sqlite_search_count 0
49: return [concat [execsql $sql] $::sqlite_search_count]
50: }
51:
52: # Verify that queries use an index. We are using the special variable
53: # "sqlite_search_count" which tallys the number of executions of MoveTo
54: # and Next operators in the VDBE. By verifing that the search count is
55: # small we can be assured that indices are being used properly.
56: #
57: do_test where4-1.1 {
58: count {SELECT rowid FROM t1 WHERE w IS NULL}
59: } {7 2}
60: do_test where4-1.2 {
61: count {SELECT rowid FROM t1 WHERE +w IS NULL}
62: } {7 6}
63: do_test where4-1.3 {
64: count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
65: } {2 2}
66: do_test where4-1.4 {
67: count {SELECT rowid FROM t1 WHERE w=1 AND +x IS NULL}
68: } {2 3}
69: do_test where4-1.5 {
70: count {SELECT rowid FROM t1 WHERE w=1 AND x>0}
71: } {1 2}
72: do_test where4-1.6 {
73: count {SELECT rowid FROM t1 WHERE w=1 AND x<9}
74: } {1 3}
75: do_test where4-1.7 {
76: count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y=3}
77: } {2 2}
78: do_test where4-1.8 {
79: count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y>2}
80: } {2 2}
81: do_test where4-1.9 {
82: count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'}
83: } {4 2}
84: do_test where4-1.10 {
85: count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL}
86: } {6 2}
87: do_test where4-1.11 {
88: count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123}
89: } {1}
90: do_test where4-1.12 {
91: count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'}
92: } {6 2}
93: do_test where4-1.13 {
94: count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL}
95: } {7 2}
96: do_test where4-1.14 {
97: count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y IS NULL}
98: } {7 2}
99: do_test where4-1.15 {
100: count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y<0}
101: } {2}
102: do_test where4-1.16 {
103: count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y>=0}
104: } {1}
105:
106: do_test where4-2.1 {
107: execsql {SELECT rowid FROM t1 ORDER BY w, x, y}
108: } {7 2 1 4 3 6 5}
109: do_test where4-2.2 {
110: execsql {SELECT rowid FROM t1 ORDER BY w DESC, x, y}
111: } {6 5 4 3 2 1 7}
112: do_test where4-2.3 {
113: execsql {SELECT rowid FROM t1 ORDER BY w, x DESC, y}
114: } {7 1 2 3 4 5 6}
115:
116:
117: # Ticket #2177
118: #
119: # Suppose you have a left join where the right table of the left
120: # join (the one that can be NULL) has an index on two columns.
121: # The first indexed column is used in the ON clause of the join.
122: # The second indexed column is used in the WHERE clause with an IS NULL
123: # constraint. It is not allowed to use the IS NULL optimization to
124: # optimize the query because the second column might be NULL because
125: # the right table did not match - something the index does not know
126: # about.
127: #
128: do_test where4-3.1 {
129: execsql {
130: CREATE TABLE t2(a);
131: INSERT INTO t2 VALUES(1);
132: INSERT INTO t2 VALUES(2);
133: INSERT INTO t2 VALUES(3);
134: CREATE TABLE t3(x,y,UNIQUE(x,y));
135: INSERT INTO t3 VALUES(1,11);
136: INSERT INTO t3 VALUES(2,NULL);
137:
138: SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
139: }
140: } {2 2 {} 3 {} {}}
141: do_test where4-3.2 {
142: execsql {
143: SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
144: }
145: } {2 2 {} 3 {} {}}
146:
147: # Ticket #2189. Probably the same bug as #2177.
148: #
149: do_test where4-4.1 {
150: execsql {
151: CREATE TABLE test(col1 TEXT PRIMARY KEY);
152: INSERT INTO test(col1) values('a');
153: INSERT INTO test(col1) values('b');
154: INSERT INTO test(col1) values('c');
155: CREATE TABLE test2(col1 TEXT PRIMARY KEY);
156: INSERT INTO test2(col1) values('a');
157: INSERT INTO test2(col1) values('b');
158: INSERT INTO test2(col1) values('c');
159: SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
160: WHERE +t2.col1 IS NULL;
161: }
162: } {}
163: do_test where4-4.2 {
164: execsql {
165: SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
166: WHERE t2.col1 IS NULL;
167: }
168: } {}
169: do_test where4-4.3 {
170: execsql {
171: SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
172: WHERE +t1.col1 IS NULL;
173: }
174: } {}
175: do_test where4-4.4 {
176: execsql {
177: SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1
178: WHERE t1.col1 IS NULL;
179: }
180: } {}
181:
182: # Ticket #2273. Problems with IN operators and NULLs.
183: #
184: ifcapable subquery {
185: do_test where4-5.1 {
186: execsql {
187: CREATE TABLE t4(x,y,z,PRIMARY KEY(x,y));
188: }
189: execsql {
190: SELECT *
191: FROM t2 LEFT JOIN t4 b1
192: LEFT JOIN t4 b2 ON b2.x=b1.x AND b2.y IN (b1.y);
193: }
194: } {1 {} {} {} {} {} {} 2 {} {} {} {} {} {} 3 {} {} {} {} {} {}}
195: do_test where4-5.2 {
196: execsql {
197: INSERT INTO t4 VALUES(1,1,11);
198: INSERT INTO t4 VALUES(1,2,12);
199: INSERT INTO t4 VALUES(1,3,13);
200: INSERT INTO t4 VALUES(2,2,22);
201: SELECT rowid FROM t4 WHERE x IN (1,9,2,5) AND y IN (1,3,NULL,2) AND z!=13;
202: }
203: } {1 2 4}
204: do_test where4-5.3 {
205: execsql {
206: SELECT rowid FROM t4 WHERE x IN (1,9,NULL,2) AND y IN (1,3,2) AND z!=13;
207: }
208: } {1 2 4}
209: do_test where4-6.1 {
210: execsql {
211: CREATE TABLE t5(a,b,c,d,e,f,UNIQUE(a,b,c,d,e,f));
212: INSERT INTO t5 VALUES(1,1,1,1,1,11111);
213: INSERT INTO t5 VALUES(2,2,2,2,2,22222);
214: INSERT INTO t5 VALUES(1,2,3,4,5,12345);
215: INSERT INTO t5 VALUES(2,3,4,5,6,23456);
216: }
217: execsql {
218: SELECT rowid FROM t5
219: WHERE a IN (1,9,2) AND b=2 AND c IN (1,2,3,4) AND d>0
220: }
221: } {3 2}
222: do_test where4-6.2 {
223: execsql {
224: SELECT rowid FROM t5
225: WHERE a IN (1,NULL,2) AND b=2 AND c IN (1,2,3,4) AND d>0
226: }
227: } {3 2}
228: do_test where4-7.1 {
229: execsql {
230: CREATE TABLE t6(y,z,PRIMARY KEY(y,z));
231: }
232: execsql {
233: SELECT * FROM t6 WHERE y=NULL AND z IN ('hello');
234: }
235: } {}
236:
237: integrity_check {where4-99.0}
238:
239: do_test where4-7.1 {
240: execsql {
241: BEGIN;
242: CREATE TABLE t8(a, b, c, d);
243: CREATE INDEX t8_i ON t8(a, b, c);
244: CREATE TABLE t7(i);
245:
246: INSERT INTO t7 VALUES(1);
247: INSERT INTO t7 SELECT i*2 FROM t7;
248: INSERT INTO t7 SELECT i*2 FROM t7;
249: INSERT INTO t7 SELECT i*2 FROM t7;
250: INSERT INTO t7 SELECT i*2 FROM t7;
251: INSERT INTO t7 SELECT i*2 FROM t7;
252: INSERT INTO t7 SELECT i*2 FROM t7;
253:
254: COMMIT;
255: }
256: } {}
257:
258: # At one point the sub-select inside the aggregate sum() function in the
259: # following query was leaking a couple of stack entries. This query
260: # runs the SELECT in a loop enough times that an assert() fails. Or rather,
261: # did fail before the bug was fixed.
262: #
263: do_test where4-7.2 {
264: execsql {
265: SELECT sum((
266: SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
267: )) FROM t7;
268: }
269: } {{}}
270:
271: }; #ifcapable subquery
272:
273: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to where4.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