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.4 2006/01/12 01:56:44 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 starts as 4. 44# 45do_test descidx1-1.1 { 46 execsql { 47 CREATE TABLE t1(a,b); 48 CREATE INDEX i1 ON t1(b ASC); 49 } 50 get_file_format 51} {4} 52do_test descidx1-1.2 { 53 execsql { 54 CREATE INDEX i2 ON t1(a DESC); 55 } 56 get_file_format 57} {4} 58 59# Put some information in the table and verify that the descending 60# index actually works. 61# 62do_test descidx1-2.1 { 63 execsql { 64 INSERT INTO t1 VALUES(1,1); 65 INSERT INTO t1 VALUES(2,2); 66 INSERT INTO t1 SELECT a+2, a+2 FROM t1; 67 INSERT INTO t1 SELECT a+4, a+4 FROM t1; 68 SELECT b FROM t1 WHERE a>3 AND a<7; 69 } 70} {6 5 4} 71do_test descidx1-2.2 { 72 execsql { 73 SELECT a FROM t1 WHERE b>3 AND b<7; 74 } 75} {4 5 6} 76do_test descidx1-2.3 { 77 execsql { 78 SELECT b FROM t1 WHERE a>=3 AND a<7; 79 } 80} {6 5 4 3} 81do_test descidx1-2.4 { 82 execsql { 83 SELECT b FROM t1 WHERE a>3 AND a<=7; 84 } 85} {7 6 5 4} 86do_test descidx1-2.5 { 87 execsql { 88 SELECT b FROM t1 WHERE a>=3 AND a<=7; 89 } 90} {7 6 5 4 3} 91do_test descidx1-2.6 { 92 execsql { 93 SELECT a FROM t1 WHERE b>=3 AND b<=7; 94 } 95} {3 4 5 6 7} 96 97# This procedure executes the SQL. Then it checks to see if the OP_Sort 98# opcode was executed. If an OP_Sort did occur, then "sort" is appended 99# to the result. If no OP_Sort happened, then "nosort" is appended. 100# 101# This procedure is used to check to make sure sorting is or is not 102# occurring as expected. 103# 104proc cksort {sql} { 105 set ::sqlite_sort_count 0 106 set data [execsql $sql] 107 if {$::sqlite_sort_count} {set x sort} {set x nosort} 108 lappend data $x 109 return $data 110} 111 112# Test sorting using a descending index. 113# 114do_test descidx1-3.1 { 115 cksort {SELECT a FROM t1 ORDER BY a} 116} {1 2 3 4 5 6 7 8 nosort} 117do_test descidx1-3.2 { 118 cksort {SELECT a FROM t1 ORDER BY a ASC} 119} {1 2 3 4 5 6 7 8 nosort} 120do_test descidx1-3.3 { 121 cksort {SELECT a FROM t1 ORDER BY a DESC} 122} {8 7 6 5 4 3 2 1 nosort} 123do_test descidx1-3.4 { 124 cksort {SELECT b FROM t1 ORDER BY a} 125} {1 2 3 4 5 6 7 8 nosort} 126do_test descidx1-3.5 { 127 cksort {SELECT b FROM t1 ORDER BY a ASC} 128} {1 2 3 4 5 6 7 8 nosort} 129do_test descidx1-3.6 { 130 cksort {SELECT b FROM t1 ORDER BY a DESC} 131} {8 7 6 5 4 3 2 1 nosort} 132do_test descidx1-3.7 { 133 cksort {SELECT a FROM t1 ORDER BY b} 134} {1 2 3 4 5 6 7 8 nosort} 135do_test descidx1-3.8 { 136 cksort {SELECT a FROM t1 ORDER BY b ASC} 137} {1 2 3 4 5 6 7 8 nosort} 138do_test descidx1-3.9 { 139 cksort {SELECT a FROM t1 ORDER BY b DESC} 140} {8 7 6 5 4 3 2 1 nosort} 141do_test descidx1-3.10 { 142 cksort {SELECT b FROM t1 ORDER BY b} 143} {1 2 3 4 5 6 7 8 nosort} 144do_test descidx1-3.11 { 145 cksort {SELECT b FROM t1 ORDER BY b ASC} 146} {1 2 3 4 5 6 7 8 nosort} 147do_test descidx1-3.12 { 148 cksort {SELECT b FROM t1 ORDER BY b DESC} 149} {8 7 6 5 4 3 2 1 nosort} 150 151do_test descidx1-3.21 { 152 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a} 153} {4 5 6 7 nosort} 154do_test descidx1-3.22 { 155 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC} 156} {4 5 6 7 nosort} 157do_test descidx1-3.23 { 158 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC} 159} {7 6 5 4 nosort} 160do_test descidx1-3.24 { 161 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a} 162} {4 5 6 7 nosort} 163do_test descidx1-3.25 { 164 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC} 165} {4 5 6 7 nosort} 166do_test descidx1-3.26 { 167 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC} 168} {7 6 5 4 nosort} 169 170# Create a table with indices that are descending on some terms and 171# ascending on others. 172# 173do_test descidx1-4.1 { 174 execsql { 175 CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL); 176 CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC); 177 CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC); 178 INSERT INTO t2 VALUES(1,'one',x'31',1.0); 179 INSERT INTO t2 VALUES(2,'two',x'3232',2.0); 180 INSERT INTO t2 VALUES(3,'three',x'333333',3.0); 181 INSERT INTO t2 VALUES(4,'four',x'34343434',4.0); 182 INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0); 183 INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0); 184 INSERT INTO t2 VALUES(2,'two',x'323232',2.1); 185 INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2); 186 INSERT INTO t2 VALUES(2,NULL,NULL,2.3); 187 SELECT count(*) FROM t2; 188 } 189} {9} 190do_test descidx1-4.2 { 191 execsql { 192 SELECT d FROM t2 ORDER BY a; 193 } 194} {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0} 195do_test descidx1-4.3 { 196 execsql { 197 SELECT d FROM t2 WHERE a>=2; 198 } 199} {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0} 200do_test descidx1-4.4 { 201 execsql { 202 SELECT d FROM t2 WHERE a>2; 203 } 204} {3.0 4.0 5.0 6.0} 205do_test descidx1-4.5 { 206 execsql { 207 SELECT d FROM t2 WHERE a=2 AND b>'two'; 208 } 209} {2.2} 210do_test descidx1-4.6 { 211 execsql { 212 SELECT d FROM t2 WHERE a=2 AND b>='two'; 213 } 214} {2.2 2.0 2.1} 215do_test descidx1-4.7 { 216 execsql { 217 SELECT d FROM t2 WHERE a=2 AND b<'two'; 218 } 219} {} 220do_test descidx1-4.8 { 221 execsql { 222 SELECT d FROM t2 WHERE a=2 AND b<='two'; 223 } 224} {2.0 2.1} 225 226do_test descidx1-5.1 { 227 execsql { 228 CREATE TABLE t3(a,b,c,d); 229 CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC); 230 INSERT INTO t3 VALUES(0,0,0,0); 231 INSERT INTO t3 VALUES(0,0,0,1); 232 INSERT INTO t3 VALUES(0,0,1,0); 233 INSERT INTO t3 VALUES(0,0,1,1); 234 INSERT INTO t3 VALUES(0,1,0,0); 235 INSERT INTO t3 VALUES(0,1,0,1); 236 INSERT INTO t3 VALUES(0,1,1,0); 237 INSERT INTO t3 VALUES(0,1,1,1); 238 INSERT INTO t3 VALUES(1,0,0,0); 239 INSERT INTO t3 VALUES(1,0,0,1); 240 INSERT INTO t3 VALUES(1,0,1,0); 241 INSERT INTO t3 VALUES(1,0,1,1); 242 INSERT INTO t3 VALUES(1,1,0,0); 243 INSERT INTO t3 VALUES(1,1,0,1); 244 INSERT INTO t3 VALUES(1,1,1,0); 245 INSERT INTO t3 VALUES(1,1,1,1); 246 SELECT count(*) FROM t3; 247 } 248} {16} 249do_test descidx1-5.2 { 250 cksort { 251 SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d; 252 } 253} {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort} 254do_test descidx1-5.3 { 255 cksort { 256 SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC; 257 } 258} {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort} 259do_test descidx1-5.4 { 260 cksort { 261 SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC; 262 } 263} {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort} 264do_test descidx1-5.5 { 265 cksort { 266 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC 267 } 268} {101 100 111 110 001 000 011 010 nosort} 269do_test descidx1-5.6 { 270 cksort { 271 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC 272 } 273} {010 011 000 001 110 111 100 101 nosort} 274do_test descidx1-5.7 { 275 cksort { 276 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC 277 } 278} {011 010 001 000 111 110 101 100 sort} 279do_test descidx1-5.8 { 280 cksort { 281 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC 282 } 283} {000 001 010 011 100 101 110 111 sort} 284do_test descidx1-5.9 { 285 cksort { 286 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC 287 } 288} {110 111 100 101 010 011 000 001 sort} 289 290# Test the legacy_file_format pragma here because we have access to 291# the get_file_format command. 292# 293do_test descidx1-6.1 { 294 db close 295 file delete -force test.db test.db-journal 296 sqlite3 db test.db 297 execsql {PRAGMA legacy_file_format} 298} {0} 299do_test descidx1-6.2 { 300 execsql {PRAGMA legacy_file_format=YES} 301 execsql {PRAGMA legacy_file_format} 302} {1} 303do_test descidx1-6.3 { 304 execsql { 305 CREATE TABLE t1(a,b,c); 306 } 307 get_file_format 308} {1} 309 310 311finish_test 312