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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix index9 18 19proc 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 29proc do_sqluses_test {tn sql objects} { 30 uplevel [list do_test $tn [list sqluses $sql] $objects] 31} 32 33do_execsql_test 1.0 { 34 CREATE TABLE t1(x, y); 35 CREATE INDEX t1x ON t1(x) WHERE y=45; 36} 37unset -nocomplain a 38set y [expr 45] 39do_sqluses_test 1.1 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1 t1x} 40set y [expr 45.1] 41do_sqluses_test 1.2 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 42set y [expr 44] 43do_sqluses_test 1.3 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 44unset -nocomplain y 45do_sqluses_test 1.4 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 46set y [string range "45" 0 end] 47do_sqluses_test 1.5 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 48 49do_execsql_test 2.0 { 50 CREATE INDEX t1x2 ON t1(x) WHERE y=-20111000111 51} 52do_sqluses_test 2.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 53set y [expr -20111000111] 54do_sqluses_test 2.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x2} 55set y [expr -20111000110] 56do_sqluses_test 2.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 57set y [expr -20111000112] 58do_sqluses_test 2.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 59 60do_execsql_test 3.0 { 61 CREATE INDEX t1x3 ON t1(x) WHERE y=9223372036854775807 62} 63set y [expr 9223372036854775807] 64do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x3} 65set y [expr 9223372036854775808] 66do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 67set y [expr 9223372036854775806] 68do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 69db cache flush 70sqlite3_db_config db QPSG 1 71set y [expr 9223372036854775807] 72do_sqluses_test 3.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 73set y [expr 9223372036854775808] 74do_sqluses_test 3.5 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 75sqlite3_db_config db QPSG 0 76db cache flush 77 78 79do_execsql_test 4.0 { 80 CREATE INDEX t1x4 ON t1(x) WHERE y=-9223372036854775808 81} 82set y [expr -9223372036854775808] 83do_sqluses_test 4.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x4} 84set y [expr -9223372036854775807] 85do_sqluses_test 4.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 86set y [expr -9223372036854775809] 87do_sqluses_test 4.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 88set y [expr -9223372036854775808] 89do_sqluses_test 4.4 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1 t1x4} 90db cache flush 91sqlite3_db_config db QPSG 1 92do_sqluses_test 4.5 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1} 93sqlite3_db_config db QPSG 0 94db cache flush 95 96finish_test 97