# 2008 September 1 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # $Id: in4.test,v 1.3 2009/01/24 09:56:15 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test in4-1.1 { execsql { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); } } {} do_test in4-1.2 { execsql { SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); } } {} do_test in4-1.3 { execsql { INSERT INTO t1 VALUES('aaa', 1); INSERT INTO t1 VALUES('ddd', 2); INSERT INTO t1 VALUES('ccc', 3); INSERT INTO t1 VALUES('eee', 4); SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc'); } } {1 3} do_test in4-1.4 { execsql { SELECT a FROM t1 WHERE rowid IN (1, 3); } } {aaa ccc} do_test in4-1.5 { execsql { SELECT a FROM t1 WHERE rowid IN (); } } {} do_test in4-1.6 { execsql { SELECT a FROM t1 WHERE a IN ('ddd'); } } {ddd} do_test in4-2.1 { execsql { CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); INSERT INTO t2 VALUES(-1, '-one'); INSERT INTO t2 VALUES(0, 'zero'); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(2, 'two'); INSERT INTO t2 VALUES(3, 'three'); } } {} do_test in4-2.2 { execsql { SELECT b FROM t2 WHERE a IN (0, 2) } } {zero two} do_test in4-2.3 { execsql { SELECT b FROM t2 WHERE a IN (2, 0) } } {zero two} do_test in4-2.4 { execsql { SELECT b FROM t2 WHERE a IN (2, -1) } } {-one two} do_test in4-2.5 { execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) } } {three} do_test in4-2.6 { execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) } } {one} do_test in4-2.7 { execsql { SELECT b FROM t2 WHERE a IN ('1', '2') } } {one two} do_test in4-2.8 { execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') } } {two} # The following block of tests test expressions of the form: # # IN () # # i.e. IN expressions with a literal empty set. # # This has led to crashes on more than one occasion. Test case in4-3.2 # was added in reponse to a bug reported on the mailing list on 11/7/2008. # See also tickets #3602 and #185. # do_test in4-3.1 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(x, id); CREATE TABLE t2(x, id); INSERT INTO t1 VALUES(NULL, NULL); INSERT INTO t1 VALUES(0, NULL); INSERT INTO t1 VALUES(1, 3); INSERT INTO t1 VALUES(2, 4); INSERT INTO t1 VALUES(3, 5); INSERT INTO t1 VALUES(4, 6); INSERT INTO t2 VALUES(0, NULL); INSERT INTO t2 VALUES(4, 1); INSERT INTO t2 VALUES(NULL, 1); INSERT INTO t2 VALUES(NULL, NULL); } } {} do_test in4-3.2 { execsql { SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1) } } {} do_test in4-3.3 { execsql { CREATE TABLE t3(x, y, z); CREATE INDEX t3i1 ON t3(x, y); INSERT INTO t3 VALUES(1, 1, 1); INSERT INTO t3 VALUES(10, 10, 10); } execsql { SELECT * FROM t3 WHERE x IN () } } {} do_test in4-3.4 { execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () } } {} do_test in4-3.5 { execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 } } {} do_test in4-3.6 { execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 } } {10 10 10} do_test in4-3.7 { execsql { SELECT * FROM t3 WHERE y IN () } } {} do_test in4-3.8 { execsql { SELECT x IN() AS a FROM t3 WHERE a } } {} do_test in4-3.9 { execsql { SELECT x IN() AS a FROM t3 WHERE NOT a } } {0 0} do_test in4-3.10 { execsql { SELECT * FROM t3 WHERE oid IN () } } {} do_test in4-3.11 { execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()} } {1 1 1} do_test in4-3.12 { execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()} } {} finish_test