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     ! 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