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.5 2006/01/16 16:24:25 danielk1977 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# 173ifcapable bloblit { 174 do_test descidx1-4.1 { 175 execsql { 176 CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL); 177 CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC); 178 CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC); 179 INSERT INTO t2 VALUES(1,'one',x'31',1.0); 180 INSERT INTO t2 VALUES(2,'two',x'3232',2.0); 181 INSERT INTO t2 VALUES(3,'three',x'333333',3.0); 182 INSERT INTO t2 VALUES(4,'four',x'34343434',4.0); 183 INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0); 184 INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0); 185 INSERT INTO t2 VALUES(2,'two',x'323232',2.1); 186 INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2); 187 INSERT INTO t2 VALUES(2,NULL,NULL,2.3); 188 SELECT count(*) FROM t2; 189 } 190 } {9} 191 do_test descidx1-4.2 { 192 execsql { 193 SELECT d FROM t2 ORDER BY a; 194 } 195 } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0} 196 do_test descidx1-4.3 { 197 execsql { 198 SELECT d FROM t2 WHERE a>=2; 199 } 200 } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0} 201 do_test descidx1-4.4 { 202 execsql { 203 SELECT d FROM t2 WHERE a>2; 204 } 205 } {3.0 4.0 5.0 6.0} 206 do_test descidx1-4.5 { 207 execsql { 208 SELECT d FROM t2 WHERE a=2 AND b>'two'; 209 } 210 } {2.2} 211 do_test descidx1-4.6 { 212 execsql { 213 SELECT d FROM t2 WHERE a=2 AND b>='two'; 214 } 215 } {2.2 2.0 2.1} 216 do_test descidx1-4.7 { 217 execsql { 218 SELECT d FROM t2 WHERE a=2 AND b<'two'; 219 } 220 } {} 221 do_test descidx1-4.8 { 222 execsql { 223 SELECT d FROM t2 WHERE a=2 AND b<='two'; 224 } 225 } {2.0 2.1} 226} 227 228do_test descidx1-5.1 { 229 execsql { 230 CREATE TABLE t3(a,b,c,d); 231 CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC); 232 INSERT INTO t3 VALUES(0,0,0,0); 233 INSERT INTO t3 VALUES(0,0,0,1); 234 INSERT INTO t3 VALUES(0,0,1,0); 235 INSERT INTO t3 VALUES(0,0,1,1); 236 INSERT INTO t3 VALUES(0,1,0,0); 237 INSERT INTO t3 VALUES(0,1,0,1); 238 INSERT INTO t3 VALUES(0,1,1,0); 239 INSERT INTO t3 VALUES(0,1,1,1); 240 INSERT INTO t3 VALUES(1,0,0,0); 241 INSERT INTO t3 VALUES(1,0,0,1); 242 INSERT INTO t3 VALUES(1,0,1,0); 243 INSERT INTO t3 VALUES(1,0,1,1); 244 INSERT INTO t3 VALUES(1,1,0,0); 245 INSERT INTO t3 VALUES(1,1,0,1); 246 INSERT INTO t3 VALUES(1,1,1,0); 247 INSERT INTO t3 VALUES(1,1,1,1); 248 SELECT count(*) FROM t3; 249 } 250} {16} 251do_test descidx1-5.2 { 252 cksort { 253 SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d; 254 } 255} {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort} 256do_test descidx1-5.3 { 257 cksort { 258 SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC; 259 } 260} {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort} 261do_test descidx1-5.4 { 262 cksort { 263 SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC; 264 } 265} {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort} 266do_test descidx1-5.5 { 267 cksort { 268 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC 269 } 270} {101 100 111 110 001 000 011 010 nosort} 271do_test descidx1-5.6 { 272 cksort { 273 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC 274 } 275} {010 011 000 001 110 111 100 101 nosort} 276do_test descidx1-5.7 { 277 cksort { 278 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC 279 } 280} {011 010 001 000 111 110 101 100 sort} 281do_test descidx1-5.8 { 282 cksort { 283 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC 284 } 285} {000 001 010 011 100 101 110 111 sort} 286do_test descidx1-5.9 { 287 cksort { 288 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC 289 } 290} {110 111 100 101 010 011 000 001 sort} 291 292# Test the legacy_file_format pragma here because we have access to 293# the get_file_format command. 294# 295do_test descidx1-6.1 { 296 db close 297 file delete -force test.db test.db-journal 298 sqlite3 db test.db 299 execsql {PRAGMA legacy_file_format} 300} {0} 301do_test descidx1-6.2 { 302 execsql {PRAGMA legacy_file_format=YES} 303 execsql {PRAGMA legacy_file_format} 304} {1} 305do_test descidx1-6.3 { 306 execsql { 307 CREATE TABLE t1(a,b,c); 308 } 309 get_file_format 310} {1} 311 312 313finish_test 314