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} 37set y [expr 45] 38do_sqluses_test 1.1 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1 t1x} 39set y [expr 45.1] 40do_sqluses_test 1.2 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 41set y [expr 44] 42do_sqluses_test 1.3 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 43unset -nocomplain y 44do_sqluses_test 1.4 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 45set y [string range "45" 0 end] 46do_sqluses_test 1.5 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} 47 48do_execsql_test 2.0 { 49 CREATE INDEX t1x2 ON t1(x) WHERE y=-20111000111 50} 51do_sqluses_test 2.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 52set y [expr -20111000111] 53do_sqluses_test 2.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x2} 54set y [expr -20111000110] 55do_sqluses_test 2.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 56set y [expr -20111000112] 57do_sqluses_test 2.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 58 59do_execsql_test 3.0 { 60 CREATE INDEX t1x3 ON t1(x) WHERE y=9223372036854775807 61} 62set y [expr 9223372036854775807] 63do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x3} 64set y [expr 9223372036854775808] 65do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 66set y [expr 9223372036854775806] 67do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 68db cache flush 69sqlite3_db_config db QPSG 1 70set y [expr 9223372036854775807] 71do_sqluses_test 3.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 72set y [expr 9223372036854775808] 73do_sqluses_test 3.5 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 74sqlite3_db_config db QPSG 0 75db cache flush 76 77 78do_execsql_test 4.0 { 79 CREATE INDEX t1x4 ON t1(x) WHERE y=-9223372036854775808 80} 81set y [expr -9223372036854775808] 82do_sqluses_test 4.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x4} 83set y [expr -9223372036854775807] 84do_sqluses_test 4.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 85set y [expr -9223372036854775809] 86do_sqluses_test 4.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} 87set y [expr -9223372036854775808] 88do_sqluses_test 4.4 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1 t1x4} 89db cache flush 90sqlite3_db_config db QPSG 1 91do_sqluses_test 4.5 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1} 92sqlite3_db_config db QPSG 0 93db cache flush 94 95finish_test 96