1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/between.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,113 @@
1.4 +# 2005 July 28
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the use of indices in WHERE clauses
1.16 +# when the WHERE clause contains the BETWEEN operator.
1.17 +#
1.18 +# $Id: between.test,v 1.2 2006/01/17 09:35:02 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Build some test data
1.24 +#
1.25 +do_test between-1.0 {
1.26 + execsql {
1.27 + BEGIN;
1.28 + CREATE TABLE t1(w int, x int, y int, z int);
1.29 + }
1.30 + for {set i 1} {$i<=100} {incr i} {
1.31 + set w $i
1.32 + set x [expr {int(log($i)/log(2))}]
1.33 + set y [expr {$i*$i + 2*$i + 1}]
1.34 + set z [expr {$x+$y}]
1.35 + ifcapable tclvar {
1.36 + # Random unplanned test of the $varname variable syntax.
1.37 + execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
1.38 + } else {
1.39 + # If the $varname syntax is not available, use the regular variable
1.40 + # declaration syntax.
1.41 + execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
1.42 + }
1.43 + }
1.44 + execsql {
1.45 + CREATE UNIQUE INDEX i1w ON t1(w);
1.46 + CREATE INDEX i1xy ON t1(x,y);
1.47 + CREATE INDEX i1zyx ON t1(z,y,x);
1.48 + COMMIT;
1.49 + }
1.50 +} {}
1.51 +
1.52 +# This procedure executes the SQL. Then it appends to the result the
1.53 +# "sort" or "nosort" keyword depending on whether or not any sorting
1.54 +# is done. Then it appends the ::sqlite_query_plan variable.
1.55 +#
1.56 +proc queryplan {sql} {
1.57 + set ::sqlite_sort_count 0
1.58 + set data [execsql $sql]
1.59 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.60 + lappend data $x
1.61 + return [concat $data $::sqlite_query_plan]
1.62 +}
1.63 +
1.64 +do_test between-1.1.1 {
1.65 + queryplan {
1.66 + SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
1.67 + }
1.68 +} {5 2 36 38 6 2 49 51 sort t1 i1w}
1.69 +do_test between-1.1.2 {
1.70 + queryplan {
1.71 + SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
1.72 + }
1.73 +} {5 2 36 38 6 2 49 51 sort t1 {}}
1.74 +do_test between-1.2.1 {
1.75 + queryplan {
1.76 + SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
1.77 + }
1.78 +} {5 2 36 38 6 2 49 51 sort t1 i1w}
1.79 +do_test between-1.2.2 {
1.80 + queryplan {
1.81 + SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
1.82 + }
1.83 +} {5 2 36 38 6 2 49 51 sort t1 {}}
1.84 +do_test between-1.3.1 {
1.85 + queryplan {
1.86 + SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
1.87 + }
1.88 +} {5 2 36 38 6 2 49 51 sort t1 i1w}
1.89 +do_test between-1.3.2 {
1.90 + queryplan {
1.91 + SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
1.92 + }
1.93 +} {5 2 36 38 6 2 49 51 sort t1 {}}
1.94 +do_test between-1.4 {
1.95 + queryplan {
1.96 + SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
1.97 + }
1.98 +} {5 2 36 38 6 2 49 51 sort t1 {}}
1.99 +do_test between-1.5.1 {
1.100 + queryplan {
1.101 + SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
1.102 + }
1.103 +} {4 2 25 27 sort t1 i1zyx}
1.104 +do_test between-1.5.2 {
1.105 + queryplan {
1.106 + SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
1.107 + }
1.108 +} {4 2 25 27 sort t1 i1zyx}
1.109 +do_test between-1.5.3 {
1.110 + queryplan {
1.111 + SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
1.112 + }
1.113 +} {4 2 25 27 sort t1 {}}
1.114 +
1.115 +
1.116 +finish_test