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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16 17do_test in4-1.1 { 18 execsql { 19 CREATE TABLE t1(a, b); 20 CREATE INDEX i1 ON t1(a); 21 } 22} {} 23do_test in4-1.2 { 24 execsql { 25 SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); 26 } 27} {} 28do_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} 37do_test in4-1.4 { 38 execsql { 39 SELECT a FROM t1 WHERE rowid IN (1, 3); 40 } 41} {aaa ccc} 42do_test in4-1.5 { 43 execsql { 44 SELECT a FROM t1 WHERE rowid IN (); 45 } 46} {} 47do_test in4-1.6 { 48 execsql { 49 SELECT a FROM t1 WHERE a IN ('ddd'); 50 } 51} {ddd} 52 53do_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 64do_test in4-2.2 { 65 execsql { SELECT b FROM t2 WHERE a IN (0, 2) } 66} {zero two} 67 68do_test in4-2.3 { 69 execsql { SELECT b FROM t2 WHERE a IN (2, 0) } 70} {zero two} 71 72do_test in4-2.4 { 73 execsql { SELECT b FROM t2 WHERE a IN (2, -1) } 74} {-one two} 75 76do_test in4-2.5 { 77 execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) } 78} {three} 79 80do_test in4-2.6 { 81 execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) } 82} {one} 83 84do_test in4-2.7 { 85 execsql { SELECT b FROM t2 WHERE a IN ('1', '2') } 86} {one two} 87 88do_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# 102do_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} {} 120do_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} {} 125do_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} {} 134do_test in4-3.4 { 135 execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () } 136} {} 137do_test in4-3.5 { 138 execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 } 139} {} 140do_test in4-3.6 { 141 execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 } 142} {10 10 10} 143do_test in4-3.7 { 144 execsql { SELECT * FROM t3 WHERE y IN () } 145} {} 146do_test in4-3.8 { 147 execsql { SELECT x IN() AS a FROM t3 WHERE a } 148} {} 149do_test in4-3.9 { 150 execsql { SELECT x IN() AS a FROM t3 WHERE NOT a } 151} {0 0} 152do_test in4-3.10 { 153 execsql { SELECT * FROM t3 WHERE oid IN () } 154} {} 155do_test in4-3.11 { 156 execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()} 157} {1 1 1} 158do_test in4-3.12 { 159 execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} 160} {} 161 162finish_test 163