1 # 2008 September 1 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 # 12 # $Id: in4.test,v 1.4 2009/06/05 17:09:12 drh Exp $ 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 17 do_test in4-1.1 { 18 execsql { 19 CREATE TABLE t1(a, b); 20 CREATE INDEX i1 ON t1(a); 21 } 22 } {} 23 do_test in4-1.2 { 24 execsql { 25 SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 26 } 27 } {} 28 do_test in4-1.3 { 29 execsql { 30 INSERT INTO t1 VALUES('aaa', 1); 31 INSERT INTO t1 VALUES('ddd', 2); 32 INSERT INTO t1 VALUES('ccc', 3); 33 INSERT INTO t1 VALUES('eee', 4); 34 SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 35 } 36 } {1 3} 37 do_test in4-1.4 { 38 execsql { 39 SELECT a FROM t1 WHERE rowid IN (1, 3); 40 } 41 } {aaa ccc} 42 do_test in4-1.5 { 43 execsql { 44 SELECT a FROM t1 WHERE rowid IN (); 45 } 46 } {} 47 do_test in4-1.6 { 48 execsql { 49 SELECT a FROM t1 WHERE a IN ('ddd'); 50 } 51 } {ddd} 52 53 do_test in4-2.1 { 54 execsql { 55 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); 56 INSERT INTO t2 VALUES(-1, '-one'); 57 INSERT INTO t2 VALUES(0, 'zero'); 58 INSERT INTO t2 VALUES(1, 'one'); 59 INSERT INTO t2 VALUES(2, 'two'); 60 INSERT INTO t2 VALUES(3, 'three'); 61 } 62 } {} 63 64 do_test in4-2.2 { 65 execsql { SELECT b FROM t2 WHERE a IN (0, 2) } 66 } {zero two} 67 68 do_test in4-2.3 { 69 execsql { SELECT b FROM t2 WHERE a IN (2, 0) } 70 } {zero two} 71 72 do_test in4-2.4 { 73 execsql { SELECT b FROM t2 WHERE a IN (2, -1) } 74 } {-one two} 75 76 do_test in4-2.5 { 77 execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) } 78 } {three} 79 80 do_test in4-2.6 { 81 execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) } 82 } {one} 83 84 do_test in4-2.7 { 85 execsql { SELECT b FROM t2 WHERE a IN ('1', '2') } 86 } {one two} 87 88 do_test in4-2.8 { 89 execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') } 90 } {two} 91 92 # The following block of tests test expressions of the form: 93 # 94 # <expr> IN () 95 # 96 # i.e. IN expressions with a literal empty set. 97 # 98 # This has led to crashes on more than one occasion. Test case in4-3.2 99 # was added in reponse to a bug reported on the mailing list on 11/7/2008. 100 # See also tickets #3602 and #185. 101 # 102 do_test in4-3.1 { 103 execsql { 104 DROP TABLE IF EXISTS t1; 105 DROP TABLE IF EXISTS t2; 106 CREATE TABLE t1(x, id); 107 CREATE TABLE t2(x, id); 108 INSERT INTO t1 VALUES(NULL, NULL); 109 INSERT INTO t1 VALUES(0, NULL); 110 INSERT INTO t1 VALUES(1, 3); 111 INSERT INTO t1 VALUES(2, 4); 112 INSERT INTO t1 VALUES(3, 5); 113 INSERT INTO t1 VALUES(4, 6); 114 INSERT INTO t2 VALUES(0, NULL); 115 INSERT INTO t2 VALUES(4, 1); 116 INSERT INTO t2 VALUES(NULL, 1); 117 INSERT INTO t2 VALUES(NULL, NULL); 118 } 119 } {} 120 do_test in4-3.2 { 121 execsql { 122 SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1) 123 } 124 } {} 125 do_test in4-3.3 { 126 execsql { 127 CREATE TABLE t3(x, y, z); 128 CREATE INDEX t3i1 ON t3(x, y); 129 INSERT INTO t3 VALUES(1, 1, 1); 130 INSERT INTO t3 VALUES(10, 10, 10); 131 } 132 execsql { SELECT * FROM t3 WHERE x IN () } 133 } {} 134 do_test in4-3.4 { 135 execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () } 136 } {} 137 do_test in4-3.5 { 138 execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 } 139 } {} 140 do_test in4-3.6 { 141 execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 } 142 } {10 10 10} 143 do_test in4-3.7 { 144 execsql { SELECT * FROM t3 WHERE y IN () } 145 } {} 146 do_test in4-3.8 { 147 execsql { SELECT x IN() AS a FROM t3 WHERE a } 148 } {} 149 do_test in4-3.9 { 150 execsql { SELECT x IN() AS a FROM t3 WHERE NOT a } 151 } {0 0} 152 do_test in4-3.10 { 153 execsql { SELECT * FROM t3 WHERE oid IN () } 154 } {} 155 do_test in4-3.11 { 156 execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()} 157 } {1 1 1} 158 do_test in4-3.12 { 159 execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} 160 } {} 161 162 finish_test 163
