gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/index9.test (about) 1 # 2017 Jun 24 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 # Test that partial indexes work with bound variables. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix index9 18 19 proc sqluses {sql} { 20 array unset ::T 21 uplevel [list db eval "EXPLAIN $sql" a { 22 if {$a(opcode)=="OpenRead"} { set ::T($a(p2)) 1 } 23 }] 24 25 set in [join [array names ::T] ,] 26 db eval "SELECT name FROM sqlite_master WHERE rootpage IN ($in) ORDER BY 1" 27 } 28 29 proc do_sqluses_test {tn sql objects} { 30 uplevel [list do_test $tn [list sqluses $sql] $objects] 31 } 32 33 do_execsql_test 1.0 { 34 CREATE TABLE t1(x, y); 35 CREATE INDEX t1x ON t1(x) WHERE y=45; 36 } 37 unset -nocomplain a 38 set y [expr 45] 39 do_sqluses_test 1.1 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1 t1x} 40 set y [expr 45.1] 41 do_sqluses_test 1.2 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 42 set y [expr 44] 43 do_sqluses_test 1.3 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 44 unset -nocomplain y 45 do_sqluses_test 1.4 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 46 set y [string range "45" 0 end] 47 do_sqluses_test 1.5 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 48 49 do_execsql_test 2.0 { 50 CREATE INDEX t1x2 ON t1(x) WHERE y=-20111000111 51 } 52 do_sqluses_test 2.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 53 set y [expr -20111000111] 54 do_sqluses_test 2.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x2} 55 set y [expr -20111000110] 56 do_sqluses_test 2.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 57 set y [expr -20111000112] 58 do_sqluses_test 2.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 59 60 do_execsql_test 3.0 { 61 CREATE INDEX t1x3 ON t1(x) WHERE y=9223372036854775807 62 } 63 set y [expr 9223372036854775807] 64 do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x3} 65 set y [expr 9223372036854775808] 66 do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 67 set y [expr 9223372036854775806] 68 do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 69 db cache flush 70 sqlite3_db_config db QPSG 1 71 set y [expr 9223372036854775807] 72 do_sqluses_test 3.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 73 set y [expr 9223372036854775808] 74 do_sqluses_test 3.5 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 75 sqlite3_db_config db QPSG 0 76 db cache flush 77 78 79 do_execsql_test 4.0 { 80 CREATE INDEX t1x4 ON t1(x) WHERE y=-9223372036854775808 81 } 82 set y [expr -9223372036854775808] 83 do_sqluses_test 4.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x4} 84 set y [expr -9223372036854775807] 85 do_sqluses_test 4.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 86 set y [expr -9223372036854775809] 87 do_sqluses_test 4.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 88 set y [expr -9223372036854775808] 89 do_sqluses_test 4.4 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1 t1x4} 90 db cache flush 91 sqlite3_db_config db QPSG 1 92 do_sqluses_test 4.5 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1} 93 sqlite3_db_config db QPSG 0 94 db cache flush 95 96 finish_test