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