xref: /sqlite-3.40.0/test/descidx1.test (revision d28bcb30) 
1# 2005 December 21
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 script is descending indices.
13#
14# $Id: descidx1.test,v 1.1 2005/12/21 14:43:12 drh Exp $
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# This procedure sets the value of the file-format in file 'test.db'
21# to $newval. Also, the schema cookie is incremented.
22#
23proc set_file_format {newval} {
24  set bt [btree_open test.db 10 0]
25  btree_begin_transaction $bt
26  set meta [btree_get_meta $bt]
27  lset meta 2 $newval                    ;# File format
28  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
29  eval "btree_update_meta $bt $meta"
30  btree_commit $bt
31  btree_close $bt
32}
33
34# This procedure returns the value of the file-format in file 'test.db'.
35#
36proc get_file_format {{fname test.db}} {
37  set bt [btree_open $fname 10 0]
38  set meta [btree_get_meta $bt]
39  btree_close $bt
40  lindex $meta 2
41}
42
43# Verify that the file format jumps to (at least) 4 as soon as a
44# descending index is created.
45#
46do_test descidx1-1.1 {
47  execsql {
48    CREATE TABLE t1(a,b);
49    CREATE INDEX i1 ON t1(b ASC);
50  }
51  get_file_format
52} {1}
53do_test descidx1-1.2 {
54  execsql {
55    CREATE INDEX i2 ON t1(a DESC);
56  }
57  get_file_format
58} {4}
59
60# Put some information in the table and verify that the descending
61# index actually works.
62#
63do_test descidx1-2.1 {
64  execsql {
65    INSERT INTO t1 VALUES(1,1);
66    INSERT INTO t1 VALUES(2,2);
67    INSERT INTO t1 SELECT a+2, a+2 FROM t1;
68    INSERT INTO t1 SELECT a+4, a+4 FROM t1;
69    SELECT b FROM t1 WHERE a>3 AND a<7;
70  }
71} {6 5 4}
72do_test descidx1-2.2 {
73  execsql {
74    SELECT a FROM t1 WHERE b>3 AND b<7;
75  }
76} {4 5 6}
77do_test descidx1-2.3 {
78  execsql {
79    SELECT b FROM t1 WHERE a>=3 AND a<7;
80  }
81} {6 5 4 3}
82do_test descidx1-2.4 {
83  execsql {
84    SELECT b FROM t1 WHERE a>3 AND a<=7;
85  }
86} {7 6 5 4}
87do_test descidx1-2.5 {
88  execsql {
89    SELECT b FROM t1 WHERE a>=3 AND a<=7;
90  }
91} {7 6 5 4 3}
92do_test descidx1-2.6 {
93  execsql {
94    SELECT a FROM t1 WHERE b>=3 AND b<=7;
95  }
96} {3 4 5 6 7}
97
98# This procedure executes the SQL.  Then it checks to see if the OP_Sort
99# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
100# to the result.  If no OP_Sort happened, then "nosort" is appended.
101#
102# This procedure is used to check to make sure sorting is or is not
103# occurring as expected.
104#
105proc cksort {sql} {
106  set ::sqlite_sort_count 0
107  set data [execsql $sql]
108  if {$::sqlite_sort_count} {set x sort} {set x nosort}
109  lappend data $x
110  return $data
111}
112
113# Test sorting using a descending index.
114#
115do_test descidx1-3.1 {
116  cksort {SELECT a FROM t1 ORDER BY a}
117} {1 2 3 4 5 6 7 8 nosort}
118do_test descidx1-3.2 {
119  cksort {SELECT a FROM t1 ORDER BY a ASC}
120} {1 2 3 4 5 6 7 8 nosort}
121do_test descidx1-3.3 {
122  cksort {SELECT a FROM t1 ORDER BY a DESC}
123} {8 7 6 5 4 3 2 1 nosort}
124do_test descidx1-3.4 {
125  cksort {SELECT b FROM t1 ORDER BY a}
126} {1 2 3 4 5 6 7 8 nosort}
127do_test descidx1-3.5 {
128  cksort {SELECT b FROM t1 ORDER BY a ASC}
129} {1 2 3 4 5 6 7 8 nosort}
130do_test descidx1-3.6 {
131  cksort {SELECT b FROM t1 ORDER BY a DESC}
132} {8 7 6 5 4 3 2 1 nosort}
133do_test descidx1-3.7 {
134  cksort {SELECT a FROM t1 ORDER BY b}
135} {1 2 3 4 5 6 7 8 nosort}
136do_test descidx1-3.8 {
137  cksort {SELECT a FROM t1 ORDER BY b ASC}
138} {1 2 3 4 5 6 7 8 nosort}
139do_test descidx1-3.9 {
140  cksort {SELECT a FROM t1 ORDER BY b DESC}
141} {8 7 6 5 4 3 2 1 nosort}
142do_test descidx1-3.10 {
143  cksort {SELECT b FROM t1 ORDER BY b}
144} {1 2 3 4 5 6 7 8 nosort}
145do_test descidx1-3.11 {
146  cksort {SELECT b FROM t1 ORDER BY b ASC}
147} {1 2 3 4 5 6 7 8 nosort}
148do_test descidx1-3.12 {
149  cksort {SELECT b FROM t1 ORDER BY b DESC}
150} {8 7 6 5 4 3 2 1 nosort}
151
152do_test descidx1-3.21 {
153  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
154} {4 5 6 7 nosort}
155do_test descidx1-3.22 {
156  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
157} {4 5 6 7 nosort}
158do_test descidx1-3.23 {
159  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
160} {7 6 5 4 nosort}
161do_test descidx1-3.24 {
162  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
163} {4 5 6 7 nosort}
164do_test descidx1-3.25 {
165  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
166} {4 5 6 7 nosort}
167do_test descidx1-3.26 {
168  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
169} {7 6 5 4 nosort}
170
171finish_test
172