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: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 db collate TEXT text_collate 21 proc text_collate {a b} { 22 return [string compare $a $b] 23 } 24 25 # Do an SQL statement. Append the search count to the end of the result. 26 # 27 proc count sql { 28 set ::sqlite_search_count 0 29 return [concat [execsql $sql] $::sqlite_search_count] 30 } 31 32 # This procedure executes the SQL. Then it checks the generated program 33 # for the SQL and appends a "nosort" to the result if the program contains the 34 # SortCallback opcode. If the program does not contain the SortCallback 35 # opcode it appends "sort" 36 # 37 proc cksort {sql} { 38 set ::sqlite_sort_count 0 39 set data [execsql $sql] 40 if {$::sqlite_sort_count} {set x sort} {set x nosort} 41 lappend data $x 42 return $data 43 } 44 45 # 46 # Test cases are organized roughly as follows: 47 # 48 # collate4-1.* ORDER BY. 49 # collate4-2.* WHERE clauses. 50 # collate4-3.* constraints (primary key, unique). 51 # collate4-4.* simple min() or max() queries. 52 # collate4-5.* REINDEX command 53 # collate4-6.* INTEGER PRIMARY KEY indices. 54 # 55 56 # 57 # These tests - collate4-1.* - check that indices are correctly 58 # selected or not selected to implement ORDER BY clauses when 59 # user defined collation sequences are involved. 60 # 61 # Because these tests also exercise all the different ways indices 62 # can be created, they also serve to verify that indices are correctly 63 # initialised with user-defined collation sequences when they are 64 # created. 65 # 66 # Tests named collate4-1.1.* use indices with a single column. Tests 67 # collate4-1.2.* use indices with two columns. 68 # 69 do_test collate4-1.1.0 { 70 execsql { 71 CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT); 72 INSERT INTO collate4t1 VALUES( 'a', 'a' ); 73 INSERT INTO collate4t1 VALUES( 'b', 'b' ); 74 INSERT INTO collate4t1 VALUES( NULL, NULL ); 75 INSERT INTO collate4t1 VALUES( 'B', 'B' ); 76 INSERT INTO collate4t1 VALUES( 'A', 'A' ); 77 CREATE INDEX collate4i1 ON collate4t1(a); 78 CREATE INDEX collate4i2 ON collate4t1(b); 79 } 80 } {} 81 do_test collate4-1.1.1 { 82 cksort {SELECT a FROM collate4t1 ORDER BY a} 83 } {{} a A b B nosort} 84 do_test collate4-1.1.2 { 85 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE} 86 } {{} a A b B nosort} 87 do_test collate4-1.1.3 { 88 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT} 89 } {{} A B a b sort} 90 do_test collate4-1.1.4 { 91 cksort {SELECT b FROM collate4t1 ORDER BY b} 92 } {{} A B a b nosort} 93 do_test collate4-1.1.5 { 94 cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} 95 } {{} A B a b nosort} 96 do_test collate4-1.1.6 { 97 cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE} 98 } {{} a A b B sort} 99 100 do_test collate4-1.1.7 { 101 execsql { 102 CREATE TABLE collate4t2( 103 a PRIMARY KEY COLLATE NOCASE, 104 b UNIQUE COLLATE TEXT 105 ); 106 INSERT INTO collate4t2 VALUES( 'a', 'a' ); 107 INSERT INTO collate4t2 VALUES( NULL, NULL ); 108 INSERT INTO collate4t2 VALUES( 'B', 'B' ); 109 } 110 } {} 111 do_test collate4-1.1.8 { 112 cksort {SELECT a FROM collate4t2 ORDER BY a} 113 } {{} a B nosort} 114 do_test collate4-1.1.9 { 115 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE} 116 } {{} a B nosort} 117 do_test collate4-1.1.10 { 118 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT} 119 } {{} B a sort} 120 do_test collate4-1.1.11 { 121 cksort {SELECT b FROM collate4t2 ORDER BY b} 122 } {{} B a nosort} 123 do_test collate4-1.1.12 { 124 cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT} 125 } {{} B a nosort} 126 do_test collate4-1.1.13 { 127 cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE} 128 } {{} a B sort} 129 130 do_test collate4-1.1.14 { 131 execsql { 132 CREATE TABLE collate4t3( 133 b COLLATE TEXT, 134 a COLLATE NOCASE, 135 UNIQUE(a), PRIMARY KEY(b) 136 ); 137 INSERT INTO collate4t3 VALUES( 'a', 'a' ); 138 INSERT INTO collate4t3 VALUES( NULL, NULL ); 139 INSERT INTO collate4t3 VALUES( 'B', 'B' ); 140 } 141 } {} 142 do_test collate4-1.1.15 { 143 cksort {SELECT a FROM collate4t3 ORDER BY a} 144 } {{} a B nosort} 145 do_test collate4-1.1.16 { 146 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE} 147 } {{} a B nosort} 148 do_test collate4-1.1.17 { 149 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT} 150 } {{} B a sort} 151 do_test collate4-1.1.18 { 152 cksort {SELECT b FROM collate4t3 ORDER BY b} 153 } {{} B a nosort} 154 do_test collate4-1.1.19 { 155 cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT} 156 } {{} B a nosort} 157 do_test collate4-1.1.20 { 158 cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE} 159 } {{} a B sort} 160 161 do_test collate4-1.1.21 { 162 execsql { 163 CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT); 164 INSERT INTO collate4t4 VALUES( 'a', 'a' ); 165 INSERT INTO collate4t4 VALUES( 'b', 'b' ); 166 INSERT INTO collate4t4 VALUES( NULL, NULL ); 167 INSERT INTO collate4t4 VALUES( 'B', 'B' ); 168 INSERT INTO collate4t4 VALUES( 'A', 'A' ); 169 CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT); 170 CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE); 171 } 172 } {} 173 do_test collate4-1.1.22 { 174 cksort {SELECT a FROM collate4t4 ORDER BY a} 175 } {{} a A b B sort} 176 do_test collate4-1.1.23 { 177 cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE} 178 } {{} a A b B sort} 179 do_test collate4-1.1.24 { 180 cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT} 181 } {{} A B a b nosort} 182 do_test collate4-1.1.25 { 183 cksort {SELECT b FROM collate4t4 ORDER BY b} 184 } {{} A B a b sort} 185 do_test collate4-1.1.26 { 186 cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT} 187 } {{} A B a b sort} 188 do_test collate4-1.1.27 { 189 cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE} 190 } {{} a A b B nosort} 191 192 do_test collate4-1.1.30 { 193 execsql { 194 DROP TABLE collate4t1; 195 DROP TABLE collate4t2; 196 DROP TABLE collate4t3; 197 DROP TABLE collate4t4; 198 } 199 } {} 200 201 do_test collate4-1.2.0 { 202 execsql { 203 CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT); 204 INSERT INTO collate4t1 VALUES( 'a', 'a' ); 205 INSERT INTO collate4t1 VALUES( 'b', 'b' ); 206 INSERT INTO collate4t1 VALUES( NULL, NULL ); 207 INSERT INTO collate4t1 VALUES( 'B', 'B' ); 208 INSERT INTO collate4t1 VALUES( 'A', 'A' ); 209 CREATE INDEX collate4i1 ON collate4t1(a, b); 210 } 211 } {} 212 do_test collate4-1.2.1 { 213 cksort {SELECT a FROM collate4t1 ORDER BY a} 214 } {{} A a B b nosort} 215 do_test collate4-1.2.2 { 216 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase} 217 } {{} A a B b nosort} 218 do_test collate4-1.2.3 { 219 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text} 220 } {{} A B a b sort} 221 do_test collate4-1.2.4 { 222 cksort {SELECT a FROM collate4t1 ORDER BY a, b} 223 } {{} A a B b nosort} 224 do_test collate4-1.2.5 { 225 cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase} 226 } {{} a A b B sort} 227 do_test collate4-1.2.6 { 228 cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text} 229 } {{} A a B b nosort} 230 231 do_test collate4-1.2.7 { 232 execsql { 233 CREATE TABLE collate4t2( 234 a COLLATE NOCASE, 235 b COLLATE TEXT, 236 PRIMARY KEY(a, b) 237 ); 238 INSERT INTO collate4t2 VALUES( 'a', 'a' ); 239 INSERT INTO collate4t2 VALUES( NULL, NULL ); 240 INSERT INTO collate4t2 VALUES( 'B', 'B' ); 241 } 242 } {} 243 do_test collate4-1.2.8 { 244 cksort {SELECT a FROM collate4t2 ORDER BY a} 245 } {{} a B nosort} 246 do_test collate4-1.2.9 { 247 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase} 248 } {{} a B nosort} 249 do_test collate4-1.2.10 { 250 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text} 251 } {{} B a sort} 252 do_test collate4-1.2.11 { 253 cksort {SELECT a FROM collate4t2 ORDER BY a, b} 254 } {{} a B nosort} 255 do_test collate4-1.2.12 { 256 cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase} 257 } {{} a B sort} 258 do_test collate4-1.2.13 { 259 cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text} 260 } {{} a B nosort} 261 262 do_test collate4-1.2.14 { 263 execsql { 264 CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT); 265 INSERT INTO collate4t3 VALUES( 'a', 'a' ); 266 INSERT INTO collate4t3 VALUES( 'b', 'b' ); 267 INSERT INTO collate4t3 VALUES( NULL, NULL ); 268 INSERT INTO collate4t3 VALUES( 'B', 'B' ); 269 INSERT INTO collate4t3 VALUES( 'A', 'A' ); 270 CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE); 271 } 272 } {} 273 do_test collate4-1.2.15 { 274 cksort {SELECT a FROM collate4t3 ORDER BY a} 275 } {{} a A b B sort} 276 do_test collate4-1.2.16 { 277 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase} 278 } {{} a A b B sort} 279 do_test collate4-1.2.17 { 280 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text} 281 } {{} A B a b nosort} 282 do_test collate4-1.2.18 { 283 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b} 284 } {{} A B a b sort} 285 do_test collate4-1.2.19 { 286 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase} 287 } {{} A B a b nosort} 288 do_test collate4-1.2.20 { 289 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text} 290 } {{} A B a b sort} 291 do_test collate4-1.2.21 { 292 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC} 293 } {b a B A {} nosort} 294 do_test collate4-1.2.22 { 295 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b} 296 } {b a B A {} sort} 297 do_test collate4-1.2.23 { 298 cksort {SELECT a FROM collate4t3 299 ORDER BY a COLLATE text DESC, b COLLATE nocase} 300 } {b a B A {} sort} 301 do_test collate4-1.2.24 { 302 cksort {SELECT a FROM collate4t3 303 ORDER BY a COLLATE text DESC, b COLLATE nocase DESC} 304 } {b a B A {} nosort} 305 306 do_test collate4-1.2.25 { 307 execsql { 308 DROP TABLE collate4t1; 309 DROP TABLE collate4t2; 310 DROP TABLE collate4t3; 311 } 312 } {} 313 314 # 315 # These tests - collate4-2.* - check that indices are correctly 316 # selected or not selected to implement WHERE clauses when user 317 # defined collation sequences are involved. 318 # 319 # Indices may optimise WHERE clauses using <, >, <=, >=, = or IN 320 # operators. 321 # 322 do_test collate4-2.1.0 { 323 execsql { 324 PRAGMA automatic_index=OFF; 325 CREATE TABLE collate4t1(a COLLATE NOCASE); 326 CREATE TABLE collate4t2(b COLLATE TEXT); 327 328 INSERT INTO collate4t1 VALUES('a'); 329 INSERT INTO collate4t1 VALUES('A'); 330 INSERT INTO collate4t1 VALUES('b'); 331 INSERT INTO collate4t1 VALUES('B'); 332 INSERT INTO collate4t1 VALUES('c'); 333 INSERT INTO collate4t1 VALUES('C'); 334 INSERT INTO collate4t1 VALUES('d'); 335 INSERT INTO collate4t1 VALUES('D'); 336 INSERT INTO collate4t1 VALUES('e'); 337 INSERT INTO collate4t1 VALUES('D'); 338 339 INSERT INTO collate4t2 VALUES('A'); 340 INSERT INTO collate4t2 VALUES('Z'); 341 } 342 } {} 343 do_test collate4-2.1.1 { 344 count { 345 SELECT * FROM collate4t2, collate4t1 WHERE a = b; 346 } 347 } {A a A A 19} 348 do_test collate4-2.1.2 { 349 execsql { 350 CREATE INDEX collate4i1 ON collate4t1(a); 351 } 352 count { 353 SELECT * FROM collate4t2, collate4t1 WHERE a = b; 354 } 355 } {A a A A 5} 356 do_test collate4-2.1.3 { 357 count { 358 SELECT * FROM collate4t2, collate4t1 WHERE b = a; 359 } 360 } {A A 19} 361 do_test collate4-2.1.4 { 362 execsql { 363 DROP INDEX collate4i1; 364 CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); 365 } 366 count { 367 SELECT * FROM collate4t2, collate4t1 WHERE a = b; 368 } 369 } {A a A A 19} 370 do_test collate4-2.1.5 { 371 count { 372 SELECT * FROM collate4t2, collate4t1 WHERE b = a; 373 } 374 } {A A 4} 375 ifcapable subquery { 376 do_test collate4-2.1.6 { 377 count { 378 SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2); 379 } 380 } {a A 10} 381 do_test collate4-2.1.7 { 382 execsql { 383 DROP INDEX collate4i1; 384 CREATE INDEX collate4i1 ON collate4t1(a); 385 } 386 count { 387 SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2); 388 } 389 } {a A 6} 390 do_test collate4-2.1.8 { 391 count { 392 SELECT a FROM collate4t1 WHERE a IN ('z', 'a'); 393 } 394 } {a A 5} 395 do_test collate4-2.1.9 { 396 execsql { 397 DROP INDEX collate4i1; 398 CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); 399 } 400 count { 401 SELECT a FROM collate4t1 WHERE a IN ('z', 'a'); 402 } 403 } {a A 9} 404 } 405 do_test collate4-2.1.10 { 406 execsql { 407 DROP TABLE collate4t1; 408 DROP TABLE collate4t2; 409 } 410 } {} 411 412 do_test collate4-2.2.0 { 413 execsql { 414 CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c); 415 CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT); 416 417 INSERT INTO collate4t1 VALUES('0', '0', '0'); 418 INSERT INTO collate4t1 VALUES('0', '0', '1'); 419 INSERT INTO collate4t1 VALUES('0', '1', '0'); 420 INSERT INTO collate4t1 VALUES('0', '1', '1'); 421 INSERT INTO collate4t1 VALUES('1', '0', '0'); 422 INSERT INTO collate4t1 VALUES('1', '0', '1'); 423 INSERT INTO collate4t1 VALUES('1', '1', '0'); 424 INSERT INTO collate4t1 VALUES('1', '1', '1'); 425 insert into collate4t2 SELECT * FROM collate4t1; 426 } 427 } {} 428 do_test collate4-2.2.1 { 429 count { 430 SELECT * FROM collate4t2 NOT INDEXED NATURAL JOIN collate4t1 NOT INDEXED; 431 } 432 } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 63} 433 do_test collate4-2.2.1b { 434 execsql { 435 CREATE INDEX collate4i1 ON collate4t1(a, b, c); 436 } 437 count { 438 SELECT * FROM collate4t2 NATURAL JOIN collate4t1; 439 } 440 } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 29} 441 do_test collate4-2.2.2 { 442 execsql { 443 DROP INDEX collate4i1; 444 CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text); 445 } 446 count { 447 SELECT * FROM collate4t2 NATURAL JOIN collate4t1; 448 } 449 } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 22} 450 451 do_test collate4-2.2.10 { 452 execsql { 453 DROP TABLE collate4t1; 454 DROP TABLE collate4t2; 455 } 456 } {} 457 458 # 459 # These tests - collate4-3.* verify that indices that implement 460 # UNIQUE and PRIMARY KEY constraints operate correctly with user 461 # defined collation sequences. 462 # 463 do_test collate4-3.0 { 464 execsql { 465 CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE); 466 } 467 } {} 468 do_test collate4-3.1 { 469 catchsql { 470 INSERT INTO collate4t1 VALUES('abc'); 471 INSERT INTO collate4t1 VALUES('ABC'); 472 } 473 } {1 {column a is not unique}} 474 do_test collate4-3.2 { 475 execsql { 476 SELECT * FROM collate4t1; 477 } 478 } {abc} 479 do_test collate4-3.3 { 480 catchsql { 481 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1; 482 } 483 } {1 {column a is not unique}} 484 do_test collate4-3.4 { 485 catchsql { 486 INSERT INTO collate4t1 VALUES(1); 487 UPDATE collate4t1 SET a = 'abc'; 488 } 489 } {1 {column a is not unique}} 490 do_test collate4-3.5 { 491 execsql { 492 DROP TABLE collate4t1; 493 CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE); 494 } 495 } {} 496 do_test collate4-3.6 { 497 catchsql { 498 INSERT INTO collate4t1 VALUES('abc'); 499 INSERT INTO collate4t1 VALUES('ABC'); 500 } 501 } {1 {column a is not unique}} 502 do_test collate4-3.7 { 503 execsql { 504 SELECT * FROM collate4t1; 505 } 506 } {abc} 507 do_test collate4-3.8 { 508 catchsql { 509 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1; 510 } 511 } {1 {column a is not unique}} 512 do_test collate4-3.9 { 513 catchsql { 514 INSERT INTO collate4t1 VALUES(1); 515 UPDATE collate4t1 SET a = 'abc'; 516 } 517 } {1 {column a is not unique}} 518 do_test collate4-3.10 { 519 execsql { 520 DROP TABLE collate4t1; 521 CREATE TABLE collate4t1(a); 522 CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE); 523 } 524 } {} 525 do_test collate4-3.11 { 526 catchsql { 527 INSERT INTO collate4t1 VALUES('abc'); 528 INSERT INTO collate4t1 VALUES('ABC'); 529 } 530 } {1 {column a is not unique}} 531 do_test collate4-3.12 { 532 execsql { 533 SELECT * FROM collate4t1; 534 } 535 } {abc} 536 do_test collate4-3.13 { 537 catchsql { 538 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1; 539 } 540 } {1 {column a is not unique}} 541 do_test collate4-3.14 { 542 catchsql { 543 INSERT INTO collate4t1 VALUES(1); 544 UPDATE collate4t1 SET a = 'abc'; 545 } 546 } {1 {column a is not unique}} 547 548 do_test collate4-3.15 { 549 execsql { 550 DROP TABLE collate4t1; 551 } 552 } {} 553 554 # Mimic the SQLite 2 collation type NUMERIC. 555 db collate numeric numeric_collate 556 proc numeric_collate {lhs rhs} { 557 if {$lhs == $rhs} {return 0} 558 return [expr ($lhs>$rhs)?1:-1] 559 } 560 561 # 562 # These tests - collate4-4.* check that min() and max() only ever 563 # use indices constructed with built-in collation type numeric. 564 # 565 # CHANGED: min() and max() now use the collation type. If there 566 # is an indice that can be used, it is used. 567 # 568 do_test collate4-4.0 { 569 execsql { 570 CREATE TABLE collate4t1(a COLLATE TEXT); 571 INSERT INTO collate4t1 VALUES('2'); 572 INSERT INTO collate4t1 VALUES('10'); 573 INSERT INTO collate4t1 VALUES('20'); 574 INSERT INTO collate4t1 VALUES('104'); 575 } 576 } {} 577 do_test collate4-4.1 { 578 count { 579 SELECT max(a) FROM collate4t1 580 } 581 } {20 3} 582 do_test collate4-4.2 { 583 count { 584 SELECT min(a) FROM collate4t1 585 } 586 } {10 3} 587 do_test collate4-4.3 { 588 # Test that the index with collation type TEXT is used. 589 execsql { 590 CREATE INDEX collate4i1 ON collate4t1(a); 591 } 592 count { 593 SELECT min(a) FROM collate4t1; 594 } 595 } {10 1} 596 do_test collate4-4.4 { 597 count { 598 SELECT max(a) FROM collate4t1; 599 } 600 } {20 0} 601 do_test collate4-4.5 { 602 # Test that the index with collation type NUMERIC is not used. 603 execsql { 604 DROP INDEX collate4i1; 605 CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC); 606 } 607 count { 608 SELECT min(a) FROM collate4t1; 609 } 610 } {10 3} 611 do_test collate4-4.6 { 612 count { 613 SELECT max(a) FROM collate4t1; 614 } 615 } {20 3} 616 do_test collate4-4.7 { 617 execsql { 618 DROP TABLE collate4t1; 619 } 620 } {} 621 622 # Also test the scalar min() and max() functions. 623 # 624 do_test collate4-4.8 { 625 execsql { 626 CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC); 627 INSERT INTO collate4t1 VALUES('11', '101'); 628 INSERT INTO collate4t1 VALUES('101', '11') 629 } 630 } {} 631 do_test collate4-4.9 { 632 execsql { 633 SELECT max(a, b) FROM collate4t1; 634 } 635 } {11 11} 636 do_test collate4-4.10 { 637 execsql { 638 SELECT max(b, a) FROM collate4t1; 639 } 640 } {101 101} 641 do_test collate4-4.11 { 642 execsql { 643 SELECT max(a, '101') FROM collate4t1; 644 } 645 } {11 101} 646 do_test collate4-4.12 { 647 execsql { 648 SELECT max('101', a) FROM collate4t1; 649 } 650 } {11 101} 651 do_test collate4-4.13 { 652 execsql { 653 SELECT max(b, '101') FROM collate4t1; 654 } 655 } {101 101} 656 do_test collate4-4.14 { 657 execsql { 658 SELECT max('101', b) FROM collate4t1; 659 } 660 } {101 101} 661 662 do_test collate4-4.15 { 663 execsql { 664 DROP TABLE collate4t1; 665 } 666 } {} 667 668 # 669 # These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY 670 # indices do not confuse collation sequences. 671 # 672 # These indices are never used for sorting in SQLite. And you can't 673 # create another index on an INTEGER PRIMARY KEY column, so we don't have 674 # to test that. 675 # (Revised 2004-Nov-22): The ROWID can be used for sorting now. 676 # 677 do_test collate4-6.0 { 678 execsql { 679 CREATE TABLE collate4t1(a INTEGER PRIMARY KEY); 680 INSERT INTO collate4t1 VALUES(101); 681 INSERT INTO collate4t1 VALUES(10); 682 INSERT INTO collate4t1 VALUES(15); 683 } 684 } {} 685 do_test collate4-6.1 { 686 cksort { 687 SELECT * FROM collate4t1 ORDER BY 1; 688 } 689 } {10 15 101 nosort} 690 do_test collate4-6.2 { 691 cksort { 692 SELECT * FROM collate4t1 ORDER BY oid; 693 } 694 } {10 15 101 nosort} 695 do_test collate4-6.3 { 696 cksort { 697 SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT; 698 } 699 } {10 101 15 sort} 700 701 finish_test 702
