xref: /sqlite-3.40.0/test/descidx2.test (revision 66c48907) 
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: descidx2.test,v 1.5 2008/03/19 00:21:31 drh Exp $
15#
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Do not use a codec for tests in this file, as the database file is
21# manipulated directly using tcl scripts (using the [hexio_write] command).
22#
23do_not_use_codec
24
25
26#db eval {PRAGMA legacy_file_format=OFF}
27sqlite3_db_config db LEGACY_FILE_FORMAT 0
28
29# This procedure sets the value of the file-format in file 'test.db'
30# to $newval. Also, the schema cookie is incremented.
31#
32proc set_file_format {newval} {
33  hexio_write test.db 44 [hexio_render_int32 $newval]
34  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
35  incr schemacookie
36  hexio_write test.db 40 [hexio_render_int32 $schemacookie]
37  return {}
38}
39
40# This procedure returns the value of the file-format in file 'test.db'.
41#
42proc get_file_format {{fname test.db}} {
43  return [hexio_get_int [hexio_read $fname 44 4]]
44}
45
46
47# Verify that the file format starts as 4
48#
49do_test descidx2-1.1 {
50  execsql {
51    CREATE TABLE t1(a,b);
52    CREATE INDEX i1 ON t1(b ASC);
53  }
54  get_file_format
55} {4}
56do_test descidx2-1.2 {
57  execsql {
58    CREATE INDEX i2 ON t1(a DESC);
59  }
60  get_file_format
61} {4}
62
63# Before adding any information to the database, set the file format
64# back to three.  Then close and reopen the database.  With the file
65# format set to three, SQLite should ignore the DESC argument on the
66# index.
67#
68do_test descidx2-2.0 {
69  set_file_format 3
70  db close
71  sqlite3 db test.db
72  get_file_format
73} {3}
74
75# Put some information in the table and verify that the DESC
76# on the index is ignored.
77#
78do_test descidx2-2.1 {
79  execsql {
80    INSERT INTO t1 VALUES(1,1);
81    INSERT INTO t1 VALUES(2,2);
82    INSERT INTO t1 SELECT a+2, a+2 FROM t1;
83    INSERT INTO t1 SELECT a+4, a+4 FROM t1;
84    SELECT b FROM t1 WHERE a>3 AND a<7;
85  }
86} {4 5 6}
87do_test descidx2-2.2 {
88  execsql {
89    SELECT a FROM t1 WHERE b>3 AND b<7;
90  }
91} {4 5 6}
92do_test descidx2-2.3 {
93  execsql {
94    SELECT b FROM t1 WHERE a>=3 AND a<7;
95  }
96} {3 4 5 6}
97do_test descidx2-2.4 {
98  execsql {
99    SELECT b FROM t1 WHERE a>3 AND a<=7;
100  }
101} {4 5 6 7}
102do_test descidx2-2.5 {
103  execsql {
104    SELECT b FROM t1 WHERE a>=3 AND a<=7;
105  }
106} {3 4 5 6 7}
107do_test descidx2-2.6 {
108  execsql {
109    SELECT a FROM t1 WHERE b>=3 AND b<=7;
110  }
111} {3 4 5 6 7}
112
113# This procedure executes the SQL.  Then it checks to see if the OP_Sort
114# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
115# to the result.  If no OP_Sort happened, then "nosort" is appended.
116#
117# This procedure is used to check to make sure sorting is or is not
118# occurring as expected.
119#
120proc cksort {sql} {
121  set ::sqlite_sort_count 0
122  set data [execsql $sql]
123  if {$::sqlite_sort_count} {set x sort} {set x nosort}
124  lappend data $x
125  return $data
126}
127
128# Test sorting using a descending index.
129#
130do_test descidx2-3.1 {
131  cksort {SELECT a FROM t1 ORDER BY a}
132} {1 2 3 4 5 6 7 8 nosort}
133do_test descidx2-3.2 {
134  cksort {SELECT a FROM t1 ORDER BY a ASC}
135} {1 2 3 4 5 6 7 8 nosort}
136do_test descidx2-3.3 {
137  cksort {SELECT a FROM t1 ORDER BY a DESC}
138} {8 7 6 5 4 3 2 1 nosort}
139do_test descidx2-3.4 {
140  cksort {SELECT b FROM t1 ORDER BY a}
141} {1 2 3 4 5 6 7 8 nosort}
142do_test descidx2-3.5 {
143  cksort {SELECT b FROM t1 ORDER BY a ASC}
144} {1 2 3 4 5 6 7 8 nosort}
145do_test descidx2-3.6 {
146  cksort {SELECT b FROM t1 ORDER BY a DESC}
147} {8 7 6 5 4 3 2 1 nosort}
148do_test descidx2-3.7 {
149  cksort {SELECT a FROM t1 ORDER BY b}
150} {1 2 3 4 5 6 7 8 nosort}
151do_test descidx2-3.8 {
152  cksort {SELECT a FROM t1 ORDER BY b ASC}
153} {1 2 3 4 5 6 7 8 nosort}
154do_test descidx2-3.9 {
155  cksort {SELECT a FROM t1 ORDER BY b DESC}
156} {8 7 6 5 4 3 2 1 nosort}
157do_test descidx2-3.10 {
158  cksort {SELECT b FROM t1 ORDER BY b}
159} {1 2 3 4 5 6 7 8 nosort}
160do_test descidx2-3.11 {
161  cksort {SELECT b FROM t1 ORDER BY b ASC}
162} {1 2 3 4 5 6 7 8 nosort}
163do_test descidx2-3.12 {
164  cksort {SELECT b FROM t1 ORDER BY b DESC}
165} {8 7 6 5 4 3 2 1 nosort}
166
167do_test descidx2-3.21 {
168  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
169} {4 5 6 7 nosort}
170do_test descidx2-3.22 {
171  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
172} {4 5 6 7 nosort}
173do_test descidx2-3.23 {
174  cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
175} {7 6 5 4 nosort}
176do_test descidx2-3.24 {
177  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
178} {4 5 6 7 nosort}
179do_test descidx2-3.25 {
180  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
181} {4 5 6 7 nosort}
182do_test descidx2-3.26 {
183  cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
184} {7 6 5 4 nosort}
185
186finish_test
187