xref: /sqlite-3.40.0/test/between.test (revision 74217cc0) 
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.1 2005/07/28 23:12:08 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Build some test data
21#
22do_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    execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
33  }
34  execsql {
35    CREATE UNIQUE INDEX i1w ON t1(w);
36    CREATE INDEX i1xy ON t1(x,y);
37    CREATE INDEX i1zyx ON t1(z,y,x);
38    COMMIT;
39  }
40} {}
41
42# This procedure executes the SQL.  Then it appends to the result the
43# "sort" or "nosort" keyword depending on whether or not any sorting
44# is done.  Then it appends the ::sqlite_query_plan variable.
45#
46proc queryplan {sql} {
47  set ::sqlite_sort_count 0
48  set data [execsql $sql]
49  if {$::sqlite_sort_count} {set x sort} {set x nosort}
50  lappend data $x
51  return [concat $data $::sqlite_query_plan]
52}
53
54do_test between-1.1.1 {
55  queryplan {
56    SELECT * FROM t1 WHERE w BETWEEN 5 AND 6 ORDER BY +w
57  }
58} {5 2 36 38 6 2 49 51 sort t1 i1w}
59do_test between-1.1.2 {
60  queryplan {
61    SELECT * FROM t1 WHERE +w BETWEEN 5 AND 6 ORDER BY +w
62  }
63} {5 2 36 38 6 2 49 51 sort t1 {}}
64do_test between-1.2.1 {
65  queryplan {
66    SELECT * FROM t1 WHERE w BETWEEN 5 AND 65-y ORDER BY +w
67  }
68} {5 2 36 38 6 2 49 51 sort t1 i1w}
69do_test between-1.2.2 {
70  queryplan {
71    SELECT * FROM t1 WHERE +w BETWEEN 5 AND 65-y ORDER BY +w
72  }
73} {5 2 36 38 6 2 49 51 sort t1 {}}
74do_test between-1.3.1 {
75  queryplan {
76    SELECT * FROM t1 WHERE w BETWEEN 41-y AND 6 ORDER BY +w
77  }
78} {5 2 36 38 6 2 49 51 sort t1 i1w}
79do_test between-1.3.2 {
80  queryplan {
81    SELECT * FROM t1 WHERE +w BETWEEN 41-y AND 6 ORDER BY +w
82  }
83} {5 2 36 38 6 2 49 51 sort t1 {}}
84do_test between-1.4 {
85  queryplan {
86    SELECT * FROM t1 WHERE w BETWEEN 41-y AND 65-y ORDER BY +w
87  }
88} {5 2 36 38 6 2 49 51 sort t1 {}}
89do_test between-1.5.1 {
90  queryplan {
91    SELECT * FROM t1 WHERE 26 BETWEEN y AND z ORDER BY +w
92  }
93} {4 2 25 27 sort t1 i1zyx}
94do_test between-1.5.2 {
95  queryplan {
96    SELECT * FROM t1 WHERE 26 BETWEEN +y AND z ORDER BY +w
97  }
98} {4 2 25 27 sort t1 i1zyx}
99do_test between-1.5.3 {
100  queryplan {
101    SELECT * FROM t1 WHERE 26 BETWEEN y AND +z ORDER BY +w
102  }
103} {4 2 25 27 sort t1 {}}
104
105
106finish_test
107