# 2001 September 15## 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.##***********************************************************************# This file implements regression tests for SQLite library.  The# focus of this file is testing the IN and BETWEEN operator.## $Id: in.test,v 1.15 2006/01/30 14:36:59 drh Exp $set testdir [file dirname $argv0]source $testdir/tester.tcl# Generate the test data we will need for the first squences of tests.#do_test in-1.0 {  execsql {    BEGIN;    CREATE TABLE t1(a int, b int);  }  for {set i 1} {$i<=10} {incr i} {    execsql "INSERT INTO t1 VALUES($i,[expr {int(pow(2,$i))}])"  }  execsql {    COMMIT;    SELECT count(*) FROM t1;  }} {10}# Do basic testing of BETWEEN.#do_test in-1.1 {  execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}} {4 5}do_test in-1.2 {  execsql {SELECT a FROM t1 WHERE b NOT BETWEEN 10 AND 50 ORDER BY a}} {1 2 3 6 7 8 9 10}do_test in-1.3 {  execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 ORDER BY a}} {1 2 3 4}do_test in-1.4 {  execsql {SELECT a FROM t1 WHERE b NOT BETWEEN a AND a*5 ORDER BY a}} {5 6 7 8 9 10}do_test in-1.6 {  execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}} {1 2 3 4 9}do_test in-1.7 {  execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}} {101 102 103 4 5 6 7 8 9 10}# The rest of this file concentrates on testing the IN operator.# Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY # (because the IN operator is unavailable).#ifcapable !subquery {  finish_test  return}# Testing of the IN operator using static lists on the right-hand side.#do_test in-2.1 {  execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}} {3 4 5}do_test in-2.2 {  execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) ORDER BY a}} {1 2 6 7 8 9 10}do_test in-2.3 {  execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) OR b=512 ORDER BY a}} {3 4 5 9}do_test in-2.4 {  execsql {SELECT a FROM t1 WHERE b NOT IN (8,12,16,24,32) OR b=512 ORDER BY a}} {1 2 6 7 8 9 10}do_test in-2.5 {  execsql {SELECT a+100*(b IN (8,16,24)) FROM t1 ORDER BY b}} {1 2 103 104 5 6 7 8 9 10}do_test in-2.6 {  execsql {SELECT a FROM t1 WHERE b IN (b+8,64)}} {6}do_test in-2.7 {  execsql {SELECT a FROM t1 WHERE b IN (max(5,10,b),20)}} {4 5 6 7 8 9 10}do_test in-2.8 {  execsql {SELECT a FROM t1 WHERE b IN (8*2,64/2) ORDER BY b}} {4 5}do_test in-2.9 {  execsql {SELECT a FROM t1 WHERE b IN (max(5,10),20)}} {}do_test in-2.10 {  execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}} {}do_test in-2.11 {  set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]  lappend v $msg} {1 {no such column: c}}# Testing the IN operator where the right-hand side is a SELECT#do_test in-3.1 {  execsql {    SELECT a FROM t1    WHERE b IN (SELECT b FROM t1 WHERE a<5)    ORDER BY a  }} {1 2 3 4}do_test in-3.2 {  execsql {    SELECT a FROM t1    WHERE b IN (SELECT b FROM t1 WHERE a<5) OR b==512    ORDER BY a  }} {1 2 3 4 9}do_test in-3.3 {  execsql {    SELECT a + 100*(b IN (SELECT b FROM t1 WHERE a<5)) FROM t1 ORDER BY b  }} {101 102 103 104 5 6 7 8 9 10}# Make sure the UPDATE and DELETE commands work with IN-SELECT#do_test in-4.1 {  execsql {    UPDATE t1 SET b=b*2     WHERE b IN (SELECT b FROM t1 WHERE a>8)  }  execsql {SELECT b FROM t1 ORDER BY b}} {2 4 8 16 32 64 128 256 1024 2048}do_test in-4.2 {  execsql {    DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)  }  execsql {SELECT a FROM t1 ORDER BY a}} {1 2 3 4 5 6 7 8}do_test in-4.3 {  execsql {    DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)  }  execsql {SELECT a FROM t1 ORDER BY a}} {5 6 7 8}# Do an IN with a constant RHS but where the RHS has many, many# elements.  We need to test that collisions in the hash table# are resolved properly.#do_test in-5.1 {  execsql {    INSERT INTO t1 VALUES('hello', 'world');    SELECT * FROM t1    WHERE a IN (       'Do','an','IN','with','a','constant','RHS','but','where','the',       'has','many','elements','We','need','to','test','that',       'collisions','hash','table','are','resolved','properly',       'This','in-set','contains','thirty','one','entries','hello');  }} {hello world}# Make sure the IN operator works with INTEGER PRIMARY KEY fields.#do_test in-6.1 {  execsql {    CREATE TABLE ta(a INTEGER PRIMARY KEY, b);    INSERT INTO ta VALUES(1,1);    INSERT INTO ta VALUES(2,2);    INSERT INTO ta VALUES(3,3);    INSERT INTO ta VALUES(4,4);    INSERT INTO ta VALUES(6,6);    INSERT INTO ta VALUES(8,8);    INSERT INTO ta VALUES(10,       'This is a key that is long enough to require a malloc in the VDBE');    SELECT * FROM ta WHERE a<10;  }} {1 1 2 2 3 3 4 4 6 6 8 8}do_test in-6.2 {  execsql {    CREATE TABLE tb(a INTEGER PRIMARY KEY, b);    INSERT INTO tb VALUES(1,1);    INSERT INTO tb VALUES(2,2);    INSERT INTO tb VALUES(3,3);    INSERT INTO tb VALUES(5,5);    INSERT INTO tb VALUES(7,7);    INSERT INTO tb VALUES(9,9);    INSERT INTO tb VALUES(11,       'This is a key that is long enough to require a malloc in the VDBE');    SELECT * FROM tb WHERE a<10;  }} {1 1 2 2 3 3 5 5 7 7 9 9}do_test in-6.3 {  execsql {    SELECT a FROM ta WHERE b IN (SELECT a FROM tb);  }} {1 2 3}do_test in-6.4 {  execsql {    SELECT a FROM ta WHERE b NOT IN (SELECT a FROM tb);  }} {4 6 8 10}do_test in-6.5 {  execsql {    SELECT a FROM ta WHERE b IN (SELECT b FROM tb);  }} {1 2 3 10}do_test in-6.6 {  execsql {    SELECT a FROM ta WHERE b NOT IN (SELECT b FROM tb);  }} {4 6 8}do_test in-6.7 {  execsql {    SELECT a FROM ta WHERE a IN (SELECT a FROM tb);  }} {1 2 3}do_test in-6.8 {  execsql {    SELECT a FROM ta WHERE a NOT IN (SELECT a FROM tb);  }} {4 6 8 10}do_test in-6.9 {  execsql {    SELECT a FROM ta WHERE a IN (SELECT b FROM tb);  }} {1 2 3}do_test in-6.10 {  execsql {    SELECT a FROM ta WHERE a NOT IN (SELECT b FROM tb);  }} {4 6 8 10}# Tests of IN operator against empty sets.  (Ticket #185)#do_test in-7.1 {  execsql {    SELECT a FROM t1 WHERE a IN ();  }} {}do_test in-7.2 {  execsql {    SELECT a FROM t1 WHERE a IN (5);  }} {5}do_test in-7.3 {  execsql {    SELECT a FROM t1 WHERE a NOT IN () ORDER BY a;  }} {5 6 7 8 hello}do_test in-7.4 {  execsql {    SELECT a FROM t1 WHERE a IN (5) AND b IN ();  }} {}do_test in-7.5 {  execsql {    SELECT a FROM t1 WHERE a IN (5) AND b NOT IN ();  }} {5}do_test in-7.6 {  execsql {    SELECT a FROM ta WHERE a IN ();  }} {}do_test in-7.7 {  execsql {    SELECT a FROM ta WHERE a NOT IN ();  }} {1 2 3 4 6 8 10}do_test in-8.1 {  execsql {    SELECT b FROM t1 WHERE a IN ('hello','there')  }} {world}do_test in-8.2 {  execsql {    SELECT b FROM t1 WHERE a IN ("hello",'there')  }} {world}# Test constructs of the form:  expr IN tablename#do_test in-9.1 {  execsql {    CREATE TABLE t4 AS SELECT a FROM tb;    SELECT * FROM t4;      }} {1 2 3 5 7 9 11}do_test in-9.2 {  execsql {    SELECT b FROM t1 WHERE a IN t4;  }} {32 128}do_test in-9.3 {  execsql {    SELECT b FROM t1 WHERE a NOT IN t4;  }} {64 256 world}do_test in-9.4 {  catchsql {    SELECT b FROM t1 WHERE a NOT IN tb;  }} {1 {only a single result allowed for a SELECT that is part of an expression}}# IN clauses in CHECK constraints.  Ticket #1645#do_test in-10.1 {  execsql {    CREATE TABLE t5(      a INTEGER,      CHECK( a IN (111,222,333) )    );    INSERT INTO t5 VALUES(111);    SELECT * FROM t5;  }} {111}do_test in-10.2 {  catchsql {    INSERT INTO t5 VALUES(4);  }} {1 {constraint failed}}finish_test