1 # 2 # 2001 September 15 3 # 4 # The author disclaims copyright to this source code. In place of 5 # a legal notice, here is a blessing: 6 # 7 # May you do good and not evil. 8 # May you find forgiveness for yourself and forgive others. 9 # May you share freely, never taking more than you give. 10 # 11 #*********************************************************************** 12 # This file implements regression tests for SQLite library. The 13 # focus of this script is page cache subsystem. 14 # 15 # $Id: collate1.test,v 1.5 2007/02/01 23:02:46 drh Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # 21 # Tests are roughly organised as follows: 22 # 23 # collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause. 24 # collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause. 25 # collate1-3.* - ORDER BY using a default collation type. Also that an 26 # explict collate type overrides a default collate type. 27 # collate1-4.* - ORDER BY using a data type. 28 # 29 30 # 31 # Collation type 'HEX'. If an argument can be interpreted as a hexadecimal 32 # number, then it is converted to one before the comparison is performed. 33 # Numbers are less than other strings. If neither argument is a number, 34 # [string compare] is used. 35 # 36 db collate HEX hex_collate 37 proc hex_collate {lhs rhs} { 38 set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs] 39 set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs] 40 if {$lhs_ishex && $rhs_ishex} { 41 set lhsx [scan $lhs %x] 42 set rhsx [scan $rhs %x] 43 if {$lhs < $rhs} {return -1} 44 if {$lhs == $rhs} {return 0} 45 if {$lhs > $rhs} {return 1} 46 } 47 if {$lhs_ishex} { 48 return -1; 49 } 50 if {$rhs_ishex} { 51 return 1; 52 } 53 return [string compare $lhs $rhs] 54 } 55 db function hex {format 0x%X} 56 57 # Mimic the SQLite 2 collation type NUMERIC. 58 db collate numeric numeric_collate 59 proc numeric_collate {lhs rhs} { 60 if {$lhs == $rhs} {return 0} 61 return [expr ($lhs>$rhs)?1:-1] 62 } 63 64 do_test collate1-1.0 { 65 execsql { 66 CREATE TABLE collate1t1(c1, c2); 67 INSERT INTO collate1t1 VALUES(45, hex(45)); 68 INSERT INTO collate1t1 VALUES(NULL, NULL); 69 INSERT INTO collate1t1 VALUES(281, hex(281)); 70 } 71 } {} 72 do_test collate1-1.1 { 73 execsql { 74 SELECT c2 FROM collate1t1 ORDER BY 1; 75 } 76 } {{} 0x119 0x2D} 77 do_test collate1-1.2 { 78 execsql { 79 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex; 80 } 81 } {{} 0x2D 0x119} 82 do_test collate1-1.3 { 83 execsql { 84 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC; 85 } 86 } {0x119 0x2D {}} 87 do_test collate1-1.4 { 88 execsql { 89 SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC; 90 } 91 } {{} 0x2D 0x119} 92 do_test collate1-1.5 { 93 execsql { 94 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 95 } 96 } {{} 0x2D 0x119} 97 do_test collate1-1.6 { 98 execsql { 99 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC 100 } 101 } {{} 0x2D 0x119} 102 do_test collate1-1.7 { 103 execsql { 104 SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC 105 } 106 } {0x119 0x2D {}} 107 do_test collate1-1.99 { 108 execsql { 109 DROP TABLE collate1t1; 110 } 111 } {} 112 113 do_test collate1-2.0 { 114 execsql { 115 CREATE TABLE collate1t1(c1, c2); 116 INSERT INTO collate1t1 VALUES('5', '0x11'); 117 INSERT INTO collate1t1 VALUES('5', '0xA'); 118 INSERT INTO collate1t1 VALUES(NULL, NULL); 119 INSERT INTO collate1t1 VALUES('7', '0xA'); 120 INSERT INTO collate1t1 VALUES('11', '0x11'); 121 INSERT INTO collate1t1 VALUES('11', '0x101'); 122 } 123 } {} 124 do_test collate1-2.2 { 125 execsql { 126 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex; 127 } 128 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} 129 do_test collate1-2.3 { 130 execsql { 131 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex; 132 } 133 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} 134 do_test collate1-2.4 { 135 execsql { 136 SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex; 137 } 138 } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}} 139 do_test collate1-2.5 { 140 execsql { 141 SELECT c1, c2 FROM collate1t1 142 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC; 143 } 144 } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}} 145 do_test collate1-2.6 { 146 execsql { 147 SELECT c1, c2 FROM collate1t1 148 ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC; 149 } 150 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} 151 do_test collate1-2.12.1 { 152 execsql { 153 SELECT c1 COLLATE numeric, c2 FROM collate1t1 154 ORDER BY 1, 2 COLLATE hex; 155 } 156 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} 157 do_test collate1-2.12.2 { 158 execsql { 159 SELECT c1 COLLATE hex, c2 FROM collate1t1 160 ORDER BY 1 COLLATE numeric, 2 COLLATE hex; 161 } 162 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} 163 do_test collate1-2.12.3 { 164 execsql { 165 SELECT c1, c2 COLLATE hex FROM collate1t1 166 ORDER BY 1 COLLATE numeric, 2; 167 } 168 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} 169 do_test collate1-2.12.4 { 170 execsql { 171 SELECT c1 COLLATE numeric, c2 COLLATE hex 172 FROM collate1t1 173 ORDER BY 1, 2; 174 } 175 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} 176 do_test collate1-2.13 { 177 execsql { 178 SELECT c1 COLLATE binary, c2 COLLATE hex 179 FROM collate1t1 180 ORDER BY 1, 2; 181 } 182 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} 183 do_test collate1-2.14 { 184 execsql { 185 SELECT c1, c2 186 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex; 187 } 188 } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}} 189 do_test collate1-2.15 { 190 execsql { 191 SELECT c1 COLLATE binary, c2 COLLATE hex 192 FROM collate1t1 193 ORDER BY 1 DESC, 2 DESC; 194 } 195 } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}} 196 do_test collate1-2.16 { 197 execsql { 198 SELECT c1 COLLATE hex, c2 COLLATE binary 199 FROM collate1t1 200 ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC; 201 } 202 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} 203 do_test collate1-2.99 { 204 execsql { 205 DROP TABLE collate1t1; 206 } 207 } {} 208 209 # 210 # These tests ensure that the default collation type for a column is used 211 # by an ORDER BY clause correctly. The focus is all the different ways 212 # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc. 213 # 214 do_test collate1-3.0 { 215 execsql { 216 CREATE TABLE collate1t1(a COLLATE hex, b); 217 INSERT INTO collate1t1 VALUES( '0x5', 5 ); 218 INSERT INTO collate1t1 VALUES( '1', 1 ); 219 INSERT INTO collate1t1 VALUES( '0x45', 69 ); 220 INSERT INTO collate1t1 VALUES( NULL, NULL ); 221 SELECT * FROM collate1t1 ORDER BY a; 222 } 223 } {{} {} 1 1 0x5 5 0x45 69} 224 225 do_test collate1-3.1 { 226 execsql { 227 SELECT * FROM collate1t1 ORDER BY 1; 228 } 229 } {{} {} 1 1 0x5 5 0x45 69} 230 do_test collate1-3.2 { 231 execsql { 232 SELECT * FROM collate1t1 ORDER BY collate1t1.a; 233 } 234 } {{} {} 1 1 0x5 5 0x45 69} 235 do_test collate1-3.3 { 236 execsql { 237 SELECT * FROM collate1t1 ORDER BY main.collate1t1.a; 238 } 239 } {{} {} 1 1 0x5 5 0x45 69} 240 do_test collate1-3.4 { 241 execsql { 242 SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1; 243 } 244 } {{} {} 1 1 0x5 5 0x45 69} 245 do_test collate1-3.5 { 246 execsql { 247 SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary; 248 } 249 } {{} {} 0x45 69 0x5 5 1 1} 250 do_test collate1-3.5.1 { 251 execsql { 252 SELECT a COLLATE binary as c1, b as c2 253 FROM collate1t1 ORDER BY c1; 254 } 255 } {{} {} 0x45 69 0x5 5 1 1} 256 do_test collate1-3.6 { 257 execsql { 258 DROP TABLE collate1t1; 259 } 260 } {} 261 262 # Update for SQLite version 3. The collate1-4.* test cases were written 263 # before manifest types were introduced. The following test cases still 264 # work, due to the 'affinity' mechanism, but they don't prove anything 265 # about collation sequences. 266 # 267 do_test collate1-4.0 { 268 execsql { 269 CREATE TABLE collate1t1(c1 numeric, c2 text); 270 INSERT INTO collate1t1 VALUES(1, 1); 271 INSERT INTO collate1t1 VALUES(12, 12); 272 INSERT INTO collate1t1 VALUES(NULL, NULL); 273 INSERT INTO collate1t1 VALUES(101, 101); 274 } 275 } {} 276 do_test collate1-4.1 { 277 execsql { 278 SELECT c1 FROM collate1t1 ORDER BY 1; 279 } 280 } {{} 1 12 101} 281 do_test collate1-4.2 { 282 execsql { 283 SELECT c2 FROM collate1t1 ORDER BY 1; 284 } 285 } {{} 1 101 12} 286 do_test collate1-4.3 { 287 execsql { 288 SELECT c2+0 FROM collate1t1 ORDER BY 1; 289 } 290 } {{} 1 12 101} 291 do_test collate1-4.4 { 292 execsql { 293 SELECT c1||'' FROM collate1t1 ORDER BY 1; 294 } 295 } {{} 1 101 12} 296 do_test collate1-4.4.1 { 297 execsql { 298 SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1; 299 } 300 } {{} 1 12 101} 301 do_test collate1-4.5 { 302 execsql { 303 DROP TABLE collate1t1; 304 } 305 } {} 306 307 finish_test 308