github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/between.test (about)

     1  # 2005 July 28
     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  # This file implements regression tests for SQLite library.  The
    12  # focus of this file is testing the use of indices in WHERE clauses
    13  # when the WHERE clause contains the BETWEEN operator.
    14  #
    15  # $Id: between.test,v 1.2 2006/01/17 09:35:02 danielk1977 Exp $
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  # Build some test data
    21  #
    22  do_test between-1.0 {
    23    execsql {
    24      BEGIN;
    25      CREATE TABLE t1(w int, x int, y int, z int);
    26    }
    27    for {set i 1} {$i<=100} {incr i} {
    28      set w $i
    29      set x [expr {int(log($i)/log(2))}]
    30      set y [expr {$i*$i + 2*$i + 1}]
    31      set z [expr {$x+$y}]
    32      ifcapable tclvar {
    33        # Random unplanned test of the $varname variable syntax.
    34        execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
    35      } else {
    36        # If the $varname syntax is not available, use the regular variable
    37        # declaration syntax.
    38        execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
    39      }
    40    }
    41    execsql {
    42      CREATE UNIQUE INDEX i1w ON t1(w);
    43      CREATE INDEX i1xy ON t1(x,y);
    44      CREATE INDEX i1zyx ON t1(z,y,x);
    45      COMMIT;
    46    }
    47  } {}
    48  
    49  # This procedure executes the SQL.  Then it appends to the result the
    50  # "sort" or "nosort" keyword depending on whether or not any sorting
    51  # is done.  Then it appends the names of the table and index used.
    52  #
    53  proc queryplan {sql} {
    54    set ::sqlite_sort_count 0
    55    set data [execsql $sql]
    56    if {$::sqlite_sort_count} {set x sort} {set x nosort}
    57    lappend data $x
    58    set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
    59    # puts eqp=$eqp
    60    foreach {a b c x} $eqp {
    61      if {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+) USING.* INDEX (\w+)\y} \
    62          $x all ss as tab idx]} {
    63        lappend data $tab $idx
    64      } elseif {[regexp {(SCAN|SEARCH) (\w+ AS )?(\w+)\y} $x all ss as tab]} {
    65        lappend data $tab *
    66      }
    67    }
    68    return $data   
    69  }
    70  
    71  do_test between-1.1.1 {
    72    queryplan {
    73      SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
    74    }
    75  } {5 2 36 38 6 2 49 51 sort t1 i1w}
    76  do_test between-1.1.2 {
    77    queryplan {
    78      SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
    79    }
    80  } {5 2 36 38 6 2 49 51 sort t1 *}
    81  do_test between-1.2.1 {
    82    queryplan {
    83      SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
    84    }
    85  } {5 2 36 38 6 2 49 51 sort t1 i1w}
    86  do_test between-1.2.2 {
    87    queryplan {
    88      SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
    89    }
    90  } {5 2 36 38 6 2 49 51 sort t1 *}
    91  do_test between-1.3.1 {
    92    queryplan {
    93      SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
    94    }
    95  } {5 2 36 38 6 2 49 51 sort t1 i1w}
    96  do_test between-1.3.2 {
    97    queryplan {
    98      SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
    99    }
   100  } {5 2 36 38 6 2 49 51 sort t1 *}
   101  do_test between-1.4 {
   102    queryplan {
   103      SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
   104    }
   105  } {5 2 36 38 6 2 49 51 sort t1 *}
   106  do_test between-1.5.1 {
   107    queryplan {
   108      SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
   109    }
   110  } {4 2 25 27 sort t1 i1zyx}
   111  do_test between-1.5.2 {
   112    queryplan {
   113      SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
   114    }
   115  } {4 2 25 27 sort t1 i1zyx}
   116  do_test between-1.5.3 {
   117    queryplan {
   118      SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
   119    }
   120  } {4 2 25 27 sort t1 *}
   121  
   122  #-------------------------------------------------------------------------
   123  reset_db
   124  do_execsql_test between-2.0 {
   125    CREATE TABLE t1(x TEXT, y TEXT COLLATE nocase);
   126    INSERT INTO t1 VALUES('0', 'abc');
   127  }
   128  
   129  foreach {tn expr res} {
   130    1 "x                BETWEEN 1 AND '5'" 0
   131    2 "x COLLATE binary BETWEEN 1 AND '5'" 0
   132    3 "x COLLATE nocase BETWEEN 1 AND '5'" 0
   133  
   134    4 "y                  BETWEEN 'A' AND 'B'" 1
   135    5 "y COLLATE nocase   BETWEEN 'A' AND 'B'" 1
   136    6 "y COLLATE binary   BETWEEN 'A' AND 'B'" 0
   137    7 "(y COLLATE binary) BETWEEN 'A' AND 'B'" 0
   138  } {
   139    set sql "SELECT $expr FROM t1"
   140    do_execsql_test between-2.1.$tn $sql $res
   141  }
   142  
   143  finish_test