Return to between.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/between.test, revision 1.1.1.1

1.1       misho       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 ::sqlite_query_plan variable.
                     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:   return [concat $data $::sqlite_query_plan]
                     59: }
                     60: 
                     61: do_test between-1.1.1 {
                     62:   queryplan {
                     63:     SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
                     64:   }
                     65: } {5 2 36 38 6 2 49 51 sort t1 i1w}
                     66: do_test between-1.1.2 {
                     67:   queryplan {
                     68:     SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
                     69:   }
                     70: } {5 2 36 38 6 2 49 51 sort t1 {}}
                     71: do_test between-1.2.1 {
                     72:   queryplan {
                     73:     SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
                     74:   }
                     75: } {5 2 36 38 6 2 49 51 sort t1 i1w}
                     76: do_test between-1.2.2 {
                     77:   queryplan {
                     78:     SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
                     79:   }
                     80: } {5 2 36 38 6 2 49 51 sort t1 {}}
                     81: do_test between-1.3.1 {
                     82:   queryplan {
                     83:     SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
                     84:   }
                     85: } {5 2 36 38 6 2 49 51 sort t1 i1w}
                     86: do_test between-1.3.2 {
                     87:   queryplan {
                     88:     SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
                     89:   }
                     90: } {5 2 36 38 6 2 49 51 sort t1 {}}
                     91: do_test between-1.4 {
                     92:   queryplan {
                     93:     SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
                     94:   }
                     95: } {5 2 36 38 6 2 49 51 sort t1 {}}
                     96: do_test between-1.5.1 {
                     97:   queryplan {
                     98:     SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
                     99:   }
                    100: } {4 2 25 27 sort t1 i1zyx}
                    101: do_test between-1.5.2 {
                    102:   queryplan {
                    103:     SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
                    104:   }
                    105: } {4 2 25 27 sort t1 i1zyx}
                    106: do_test between-1.5.3 {
                    107:   queryplan {
                    108:     SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
                    109:   }
                    110: } {4 2 25 27 sort t1 {}}
                    111: 
                    112: 
                    113: finish_test