gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/pushdown.test (about) 1 # 2017 April 29 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 set testdir [file dirname $argv0] 13 source $testdir/tester.tcl 14 set testprefix pushdown 15 16 do_execsql_test 1.0 { 17 CREATE TABLE t1(a, b, c); 18 INSERT INTO t1 VALUES(1, 'b1', 'c1'); 19 INSERT INTO t1 VALUES(2, 'b2', 'c2'); 20 INSERT INTO t1 VALUES(3, 'b3', 'c3'); 21 INSERT INTO t1 VALUES(4, 'b4', 'c4'); 22 CREATE INDEX i1 ON t1(a, c); 23 } 24 25 proc f {val} { 26 lappend ::L $val 27 return 0 28 } 29 db func f f 30 31 do_test 1.1 { 32 set L [list] 33 execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) } 34 set L 35 } {c2} 36 37 do_test 1.2 { 38 set L [list] 39 execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) } 40 set L 41 } {c3} 42 43 do_execsql_test 1.3 { 44 DROP INDEX i1; 45 CREATE INDEX i1 ON t1(a, b); 46 } 47 do_test 1.4 { 48 set L [list] 49 execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) } 50 set L 51 } {b2} 52 53 do_test 1.5 { 54 set L [list] 55 execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) } 56 set L 57 } {b3} 58 59 #----------------------------------------------- 60 61 do_execsql_test 2.0 { 62 CREATE TABLE u1(a, b, c); 63 CREATE TABLE u2(x, y, z); 64 65 INSERT INTO u1 VALUES('a1', 'b1', 'c1'); 66 INSERT INTO u2 VALUES('a1', 'b1', 'c1'); 67 } 68 69 do_test 2.1 { 70 set L [list] 71 execsql { 72 SELECT * FROM u1 WHERE f('one')=123 AND 123=( 73 SELECT x FROM u2 WHERE x=a AND f('two') 74 ) 75 } 76 set L 77 } {one} 78 79 do_test 2.2 { 80 set L [list] 81 execsql { 82 SELECT * FROM u1 WHERE 123=( 83 SELECT x FROM u2 WHERE x=a AND f('two') 84 ) AND f('three')=123 85 } 86 set L 87 } {three} 88 89 90 91 finish_test