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