gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/collate1.test (about) 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 testing collation sequences. 14 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set testprefix collate1 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 # A problem reported on the mailing list: A CREATE TABLE statement 308 # is allowed to have two or more COLLATE clauses on the same column. 309 # That probably ought to be an error, but we allow it for backwards 310 # compatibility. Just make sure it works and doesn't leak memory. 311 # 312 do_test collate1-5.1 { 313 execsql { 314 CREATE TABLE c5( 315 id INTEGER PRIMARY KEY, 316 a TEXT COLLATE binary COLLATE nocase COLLATE rtrim, 317 b TEXT COLLATE nocase COLLATE binary, 318 c TEXT COLLATE rtrim COLLATE binary COLLATE rtrim COLLATE nocase 319 ); 320 INSERT INTO c5 VALUES(1, 'abc','abc','abc'); 321 INSERT INTO c5 VALUES(2, 'abc ','ABC','ABC'); 322 SELECT id FROM c5 WHERE a='abc' ORDER BY id; 323 } 324 } {1 2} 325 do_test collate1-5.2 { 326 execsql { 327 SELECT id FROM c5 WHERE b='abc' ORDER BY id; 328 } 329 } {1} 330 do_test collate1-5.3 { 331 execsql { 332 SELECT id FROM c5 WHERE c='abc' ORDER BY id; 333 } 334 } {1 2} 335 336 337 338 #------------------------------------------------------------------------- 339 # Fix problems with handling collation sequences named '"""'. 340 # 341 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1 342 do_execsql_test 6.1 { 343 SELECT """"""""; 344 } {\"\"\"} 345 346 do_catchsql_test 6.2 { 347 CREATE TABLE x1(a); 348 SELECT a FROM x1 ORDER BY a COLLATE """"""""; 349 } {1 {no such collation sequence: """}} 350 351 do_catchsql_test 6.3 { 352 SELECT a FROM x1 ORDER BY 1 COLLATE """"""""; 353 } {1 {no such collation sequence: """}} 354 355 do_catchsql_test 6.4 { 356 SELECT 0 UNION SELECT 0 ORDER BY 1 COLLATE """"""""; 357 } {1 {no such collation sequence: """}} 358 359 db collate {"""} [list string compare -nocase] 360 361 do_execsql_test 6.5 { 362 PRAGMA foreign_keys = ON; 363 CREATE TABLE p1(a PRIMARY KEY COLLATE '"""'); 364 CREATE TABLE c1(x, y REFERENCES p1); 365 } {} 366 367 do_execsql_test 6.6 { 368 INSERT INTO p1 VALUES('abc'); 369 INSERT INTO c1 VALUES(1, 'ABC'); 370 } 371 372 ifcapable foreignkey { 373 do_catchsql_test 6.7 { 374 DELETE FROM p1 WHERE rowid = 1 375 } {1 {FOREIGN KEY constraint failed}} 376 } 377 378 do_execsql_test 6.8 { 379 INSERT INTO p1 VALUES('abb'); 380 INSERT INTO p1 VALUES('wxz'); 381 INSERT INTO p1 VALUES('wxy'); 382 383 INSERT INTO c1 VALUES(2, 'abb'); 384 INSERT INTO c1 VALUES(3, 'wxz'); 385 INSERT INTO c1 VALUES(4, 'WXY'); 386 SELECT x, y FROM c1 ORDER BY y COLLATE """"""""; 387 } {2 abb 1 ABC 4 WXY 3 wxz} 388 389 # 2015-04-15: Nested COLLATE operators 390 # 391 do_execsql_test 7.0 { 392 SELECT 'abc' UNION ALL SELECT 'DEF' 393 ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE nocase; 394 } {abc DEF} 395 do_execsql_test 7.1 { 396 SELECT 'abc' UNION ALL SELECT 'DEF' 397 ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE binary; 398 } {DEF abc} 399 do_execsql_test 7.2 { 400 SELECT 'abc' UNION ALL SELECT 'DEF' 401 ORDER BY 1 COLLATE binary COLLATE binary COLLATE binary COLLATE nocase; 402 } {abc DEF} 403 404 # 2019-06-14 405 # https://sqlite.org/src/info/f1580ba1b574e9e9 406 # 407 do_execsql_test 8.0 { 408 SELECT ' ' > char(20) COLLATE rtrim; 409 } 0 410 do_execsql_test 8.1 { 411 SELECT '' < char(20) COLLATE rtrim; 412 } 1 413 do_execsql_test 8.2 { 414 DROP TABLE IF EXISTS t0; 415 CREATE TABLE t0(c0 COLLATE RTRIM, c1 BLOB UNIQUE, 416 PRIMARY KEY (c0, c1)) WITHOUT ROWID; 417 INSERT INTO t0 VALUES (123, 3), (' ', 1), (' ', 2), ('', 4); 418 SELECT * FROM t0 WHERE c1 = 1; 419 } {{ } 1} 420 421 # 2019-10-09 422 # ALWAYS() macro fails following OOM 423 # Problem detected by dbsqlfuzz. 424 # 425 do_execsql_test 9.0 { 426 CREATE TABLE t1(a, b); 427 CREATE TABLE t2(c, d); 428 } 429 430 do_faultsim_test 9.1 -faults oom* -body { 431 execsql { 432 SELECT * FROM ( 433 SELECT b COLLATE nocase IN (SELECT c FROM t2) FROM t1 434 ); 435 } 436 } -test { 437 faultsim_test_result {0 {}} 438 } 439 440 # 2020-01-03 dbsqlfuzz find 441 # 442 reset_db 443 do_catchsql_test 10.0 { 444 CREATE TABLE t1(a INTEGER PRIMARY KEY,b); 445 INSERT INTO t1 VALUES(0,NULL); 446 CREATE TABLE t2(x UNIQUE); 447 CREATE VIEW v1a(z,y) AS SELECT x COLLATE x FROM t2; 448 SELECT a,b,z,y,'' FROM t1 JOIN v1a ON b IS NOT FALSE; 449 } {1 {no such collation sequence: x}} 450 451 452 finish_test