10c733f67Sdan# 2011 November 16 20c733f67Sdan# 30c733f67Sdan# The author disclaims copyright to this source code. In place of 40c733f67Sdan# a legal notice, here is a blessing: 50c733f67Sdan# 60c733f67Sdan# May you do good and not evil. 70c733f67Sdan# May you find forgiveness for yourself and forgive others. 80c733f67Sdan# May you share freely, never taking more than you give. 90c733f67Sdan# 100c733f67Sdan#*********************************************************************** 110c733f67Sdan# 120c733f67Sdan 130c733f67Sdanset testdir [file dirname $argv0] 140c733f67Sdansource $testdir/tester.tcl 150c733f67Sdanset testprefix whereC 160c733f67Sdan 170c733f67Sdando_execsql_test 1.0 { 180c733f67Sdan CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b INTEGER); 190c733f67Sdan 200c733f67Sdan INSERT INTO t1 VALUES(1, 1, 1); 210c733f67Sdan INSERT INTO t1 VALUES(2, 1, 1); 220c733f67Sdan INSERT INTO t1 VALUES(3, 1, 2); 230c733f67Sdan INSERT INTO t1 VALUES(4, 1, 2); 240c733f67Sdan INSERT INTO t1 VALUES(5, 1, 2); 250c733f67Sdan INSERT INTO t1 VALUES(6, 1, 3); 260c733f67Sdan INSERT INTO t1 VALUES(7, 1, 3); 270c733f67Sdan 280c733f67Sdan INSERT INTO t1 VALUES(8, 2, 1); 290c733f67Sdan INSERT INTO t1 VALUES(9, 2, 1); 300c733f67Sdan INSERT INTO t1 VALUES(10, 2, 2); 310c733f67Sdan INSERT INTO t1 VALUES(11, 2, 2); 320c733f67Sdan INSERT INTO t1 VALUES(12, 2, 2); 330c733f67Sdan INSERT INTO t1 VALUES(13, 2, 3); 340c733f67Sdan INSERT INTO t1 VALUES(14, 2, 3); 350c733f67Sdan 360c733f67Sdan INSERT INTO t1 VALUES(15, 2, 1); 370c733f67Sdan INSERT INTO t1 VALUES(16, 2, 1); 380c733f67Sdan INSERT INTO t1 VALUES(17, 2, 2); 390c733f67Sdan INSERT INTO t1 VALUES(18, 2, 2); 400c733f67Sdan INSERT INTO t1 VALUES(19, 2, 2); 410c733f67Sdan INSERT INTO t1 VALUES(20, 2, 3); 420c733f67Sdan INSERT INTO t1 VALUES(21, 2, 3); 430c733f67Sdan 440c733f67Sdan CREATE INDEX i1 ON t1(a, b); 450c733f67Sdan} 460c733f67Sdan 470c733f67Sdanforeach {tn sql res} { 480c733f67Sdan 1 "SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3" {4 5} 490c733f67Sdan 2 "SELECT i FROM t1 WHERE rowid='12'" {12} 500c733f67Sdan 3 "SELECT i FROM t1 WHERE a=1 AND b='2'" {3 4 5} 510c733f67Sdan 4 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'" {4 5} 520c733f67Sdan 5 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5" {3 4} 530c733f67Sdan 6 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12" {10 11} 540c733f67Sdan 7 "SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11" {3 4 5 10} 550c733f67Sdan 8 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12" {10 11 12} 560c733f67Sdan 9 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 11 AND 12" {11 12} 570c733f67Sdan 10 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11" {10 11} 580c733f67Sdan 11 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10" {} 590c733f67Sdan 12 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL" {} 600c733f67Sdan 13 "SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL" {} 610c733f67Sdan 14 "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5" {3 4} 62*9be18709Sdrh 15 "SELECT i FROM t1 WHERE rowid IS '12'" {12} 630c733f67Sdan} { 640c733f67Sdan do_execsql_test 1.$tn.1 $sql $res 650c733f67Sdan do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC" [lsort -integer -inc $res] 660c733f67Sdan do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec $res] 670c733f67Sdan} 680c733f67Sdan 690c733f67Sdan 700c733f67Sdanfinish_test 71