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