Annotation of embedaddon/sqlite3/test/where4.test, revision 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