| # 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.22 2008/08/04 03:51:24 danielk1977 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 {1<<$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}} |
| |
| # Ticket #1821 |
| # |
| # Type affinity applied to the right-hand side of an IN operator. |
| # |
| do_test in-11.1 { |
| execsql { |
| CREATE TABLE t6(a,b NUMERIC); |
| INSERT INTO t6 VALUES(1,2); |
| INSERT INTO t6 VALUES(2,3); |
| SELECT * FROM t6 WHERE b IN (2); |
| } |
| } {1 2} |
| do_test in-11.2 { |
| # The '2' should be coerced into 2 because t6.b is NUMERIC |
| execsql { |
| SELECT * FROM t6 WHERE b IN ('2'); |
| } |
| } {1 2} |
| do_test in-11.3 { |
| # No coercion should occur here because of the unary + before b. |
| execsql { |
| SELECT * FROM t6 WHERE +b IN ('2'); |
| } |
| } {} |
| do_test in-11.4 { |
| # No coercion because column a as affinity NONE |
| execsql { |
| SELECT * FROM t6 WHERE a IN ('2'); |
| } |
| } {} |
| do_test in-11.5 { |
| execsql { |
| SELECT * FROM t6 WHERE a IN (2); |
| } |
| } {2 3} |
| do_test in-11.6 { |
| # No coercion because column a as affinity NONE |
| execsql { |
| SELECT * FROM t6 WHERE +a IN ('2'); |
| } |
| } {} |
| |
| # Test error conditions with expressions of the form IN(<compound select>). |
| # |
| ifcapable compound { |
| do_test in-12.1 { |
| execsql { |
| CREATE TABLE t2(a, b, c); |
| CREATE TABLE t3(a, b, c); |
| } |
| } {} |
| do_test in-12.2 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 UNION ALL SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.3 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 UNION SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.4 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 EXCEPT SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.5 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 INTERSECT SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.6 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 UNION ALL SELECT a FROM t2 |
| ); |
| } |
| } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} |
| do_test in-12.7 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 UNION SELECT a FROM t2 |
| ); |
| } |
| } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
| do_test in-12.8 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 EXCEPT SELECT a FROM t2 |
| ); |
| } |
| } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} |
| do_test in-12.9 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a, b FROM t3 INTERSECT SELECT a FROM t2 |
| ); |
| } |
| } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} |
| } |
| |
| do_test in-12.10 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a FROM t3 UNION ALL SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.11 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a FROM t3 UNION SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.12 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a FROM t3 EXCEPT SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| do_test in-12.13 { |
| catchsql { |
| SELECT * FROM t2 WHERE a IN ( |
| SELECT a FROM t3 INTERSECT SELECT a, b FROM t2 |
| ); |
| } |
| } {1 {only a single result allowed for a SELECT that is part of an expression}} |
| |
| |
| #------------------------------------------------------------------------ |
| # The following tests check that NULL is handled correctly when it |
| # appears as part of a set of values on the right-hand side of an |
| # IN or NOT IN operator. |
| # |
| # When it appears in such a set, NULL is handled as an "unknown value". |
| # If, because of the unknown value in the set, the result of the expression |
| # cannot be determined, then it itself evaluates to NULL. |
| # |
| |
| # Warm body test to demonstrate the principles being tested: |
| # |
| do_test in-13.1 { |
| db nullvalue "null" |
| execsql { SELECT |
| 1 IN (NULL, 1, 2), -- The value 1 is a member of the set, return true. |
| 3 IN (NULL, 1, 2), -- Ambiguous, return NULL. |
| 1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false. |
| 3 NOT IN (NULL, 1, 2) -- Ambiguous, return NULL. |
| } |
| } {1 null 0 null} |
| |
| do_test in-13.2 { |
| execsql { |
| CREATE TABLE t7(a, b, c NOT NULL); |
| INSERT INTO t7 VALUES(1, 1, 1); |
| INSERT INTO t7 VALUES(2, 2, 2); |
| INSERT INTO t7 VALUES(3, 3, 3); |
| INSERT INTO t7 VALUES(NULL, 4, 4); |
| INSERT INTO t7 VALUES(NULL, 5, 5); |
| } |
| } {} |
| |
| do_test in-13.3 { |
| execsql { SELECT 2 IN (SELECT a FROM t7) } |
| } {1} |
| do_test in-13.4 { |
| execsql { SELECT 6 IN (SELECT a FROM t7) } |
| } {null} |
| |
| do_test in-13.5 { |
| execsql { SELECT 2 IN (SELECT b FROM t7) } |
| } {1} |
| do_test in-13.6 { |
| execsql { SELECT 6 IN (SELECT b FROM t7) } |
| } {0} |
| |
| do_test in-13.7 { |
| execsql { SELECT 2 IN (SELECT c FROM t7) } |
| } {1} |
| do_test in-13.8 { |
| execsql { SELECT 6 IN (SELECT c FROM t7) } |
| } {0} |
| |
| do_test in-13.9 { |
| execsql { |
| SELECT |
| 2 NOT IN (SELECT a FROM t7), |
| 6 NOT IN (SELECT a FROM t7), |
| 2 NOT IN (SELECT b FROM t7), |
| 6 NOT IN (SELECT b FROM t7), |
| 2 NOT IN (SELECT c FROM t7), |
| 6 NOT IN (SELECT c FROM t7) |
| } |
| } {0 null 0 1 0 1} |
| |
| do_test in-13.10 { |
| execsql { |
| SELECT b IN ( |
| SELECT inside.a |
| FROM t7 AS inside |
| WHERE inside.b BETWEEN outside.b+1 AND outside.b+2 |
| ) |
| FROM t7 AS outside ORDER BY b; |
| } |
| } {0 null null null 0} |
| |
| do_test in-13.11 { |
| execsql { |
| SELECT b NOT IN ( |
| SELECT inside.a |
| FROM t7 AS inside |
| WHERE inside.b BETWEEN outside.b+1 AND outside.b+2 |
| ) |
| FROM t7 AS outside ORDER BY b; |
| } |
| } {1 null null null 1} |
| |
| do_test in-13.12 { |
| execsql { |
| CREATE INDEX i1 ON t7(a); |
| CREATE INDEX i2 ON t7(b); |
| CREATE INDEX i3 ON t7(c); |
| } |
| execsql { |
| SELECT |
| 2 IN (SELECT a FROM t7), |
| 6 IN (SELECT a FROM t7), |
| 2 IN (SELECT b FROM t7), |
| 6 IN (SELECT b FROM t7), |
| 2 IN (SELECT c FROM t7), |
| 6 IN (SELECT c FROM t7) |
| } |
| } {1 null 1 0 1 0} |
| |
| do_test in-13.13 { |
| execsql { |
| SELECT |
| 2 NOT IN (SELECT a FROM t7), |
| 6 NOT IN (SELECT a FROM t7), |
| 2 NOT IN (SELECT b FROM t7), |
| 6 NOT IN (SELECT b FROM t7), |
| 2 NOT IN (SELECT c FROM t7), |
| 6 NOT IN (SELECT c FROM t7) |
| } |
| } {0 null 0 1 0 1} |
| |
| do_test in-13.14 { |
| execsql { |
| BEGIN TRANSACTION; |
| CREATE TABLE a(id INTEGER); |
| INSERT INTO a VALUES(1); |
| INSERT INTO a VALUES(2); |
| INSERT INTO a VALUES(3); |
| CREATE TABLE b(id INTEGER); |
| INSERT INTO b VALUES(NULL); |
| INSERT INTO b VALUES(3); |
| INSERT INTO b VALUES(4); |
| INSERT INTO b VALUES(5); |
| COMMIT; |
| SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); |
| } |
| } {} |
| do_test in-13.14 { |
| execsql { |
| CREATE INDEX i5 ON b(id); |
| SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); |
| } |
| } {} |
| |
| |
| do_test in-13.X { |
| db nullvalue "" |
| } {} |
| |
| finish_test |