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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20db collate TEXT text_collate 21proc 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# 27proc 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# 37proc 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# initialized 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# 69do_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} {} 81do_test collate4-1.1.1 { 82 cksort {SELECT a FROM collate4t1 ORDER BY a} 83} {{} a A b B nosort} 84do_test collate4-1.1.2 { 85 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE} 86} {{} a A b B nosort} 87do_test collate4-1.1.3 { 88 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT} 89} {{} A B a b sort} 90do_test collate4-1.1.4 { 91 cksort {SELECT b FROM collate4t1 ORDER BY b} 92} {{} A B a b nosort} 93do_test collate4-1.1.5 { 94 cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT} 95} {{} A B a b nosort} 96do_test collate4-1.1.6 { 97 cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE, rowid} 98} {{} a A b B sort} 99 100do_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} {} 111do_test collate4-1.1.8 { 112 cksort {SELECT a FROM collate4t2 ORDER BY a} 113} {{} a B nosort} 114do_test collate4-1.1.9 { 115 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE} 116} {{} a B nosort} 117do_test collate4-1.1.10 { 118 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT} 119} {{} B a sort} 120do_test collate4-1.1.11 { 121 cksort {SELECT b FROM collate4t2 ORDER BY b} 122} {{} B a nosort} 123do_test collate4-1.1.12 { 124 cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT} 125} {{} B a nosort} 126do_test collate4-1.1.13 { 127 cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE} 128} {{} a B sort} 129 130do_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} {} 142do_test collate4-1.1.15 { 143 cksort {SELECT a FROM collate4t3 ORDER BY a} 144} {{} a B nosort} 145do_test collate4-1.1.16 { 146 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE} 147} {{} a B nosort} 148do_test collate4-1.1.17 { 149 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT} 150} {{} B a sort} 151do_test collate4-1.1.18 { 152 cksort {SELECT b FROM collate4t3 ORDER BY b} 153} {{} B a nosort} 154do_test collate4-1.1.19 { 155 cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT} 156} {{} B a nosort} 157do_test collate4-1.1.20 { 158 cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE} 159} {{} a B sort} 160 161do_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} {} 173do_test collate4-1.1.22 { 174 cksort {SELECT a FROM collate4t4 ORDER BY a, rowid} 175} {{} a A b B sort} 176do_test collate4-1.1.23 { 177 cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid} 178} {{} a A b B sort} 179do_test collate4-1.1.24 { 180 cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid} 181} {{} A B a b nosort} 182do_test collate4-1.1.25 { 183 cksort {SELECT b FROM collate4t4 ORDER BY b} 184} {{} A B a b sort} 185do_test collate4-1.1.26 { 186 cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT} 187} {{} A B a b sort} 188do_test collate4-1.1.27 { 189 cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE} 190} {{} a A b B nosort} 191 192do_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 201do_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} {} 212do_test collate4-1.2.1 { 213 cksort {SELECT a FROM collate4t1 ORDER BY a} 214} {{} A a B b nosort} 215do_test collate4-1.2.2 { 216 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase} 217} {{} A a B b nosort} 218do_test collate4-1.2.3 { 219 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text} 220} {{} A B a b sort} 221do_test collate4-1.2.4 { 222 cksort {SELECT a FROM collate4t1 ORDER BY a, b} 223} {{} A a B b nosort} 224do_test collate4-1.2.5 { 225 cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase, rowid} 226} {{} a A b B sort} 227do_test collate4-1.2.6 { 228 cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text} 229} {{} A a B b nosort} 230 231do_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} {} 243do_test collate4-1.2.8 { 244 cksort {SELECT a FROM collate4t2 ORDER BY a} 245} {{} a B nosort} 246do_test collate4-1.2.9 { 247 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase} 248} {{} a B nosort} 249do_test collate4-1.2.10 { 250 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text} 251} {{} B a sort} 252do_test collate4-1.2.11 { 253 cksort {SELECT a FROM collate4t2 ORDER BY a, b} 254} {{} a B nosort} 255do_test collate4-1.2.12 { 256 cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase} 257} {{} a B sort} 258do_test collate4-1.2.13 { 259 cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text} 260} {{} a B nosort} 261 262do_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} {} 273do_test collate4-1.2.15 { 274 cksort {SELECT a FROM collate4t3 ORDER BY a, rowid} 275} {{} a A b B sort} 276do_test collate4-1.2.16 { 277 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid} 278} {{} a A b B sort} 279do_test collate4-1.2.17 { 280 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text} 281} {{} A B a b nosort} 282do_test collate4-1.2.18 { 283 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b} 284} {{} A B a b sort} 285do_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} 288do_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} 291do_test collate4-1.2.21 { 292 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC} 293} {b a B A {} nosort} 294do_test collate4-1.2.22 { 295 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b} 296} {b a B A {} sort} 297do_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} 301do_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 306do_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# 322do_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} {} 343do_test collate4-2.1.1 { 344 count { 345 SELECT * FROM collate4t2, collate4t1 WHERE a = b; 346 } 347} {A a A A 19} 348do_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 4} 356do_test collate4-2.1.3 { 357 count { 358 SELECT * FROM collate4t2, collate4t1 WHERE b = a; 359 } 360} {A A 19} 361do_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 ORDER BY collate4t2.rowid, collate4t1.rowid 369 } 370} {A a A A 19} 371do_test collate4-2.1.5 { 372 count { 373 SELECT * FROM collate4t2, collate4t1 WHERE b = a; 374 } 375} {A A 3} 376ifcapable subquery { 377 do_test collate4-2.1.6 { 378 count { 379 SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2) 380 ORDER BY rowid 381 } 382 } {a A 10} 383 do_test collate4-2.1.7 { 384 execsql { 385 DROP INDEX collate4i1; 386 CREATE INDEX collate4i1 ON collate4t1(a); 387 } 388 count { 389 SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2) 390 ORDER BY rowid 391 } 392 } {a A 5} 393 do_test collate4-2.1.8 { 394 count { 395 SELECT a FROM collate4t1 WHERE a IN ('z', 'a'); 396 } 397 } {a A 4} 398 do_test collate4-2.1.9 { 399 execsql { 400 DROP INDEX collate4i1; 401 CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); 402 } 403 count { 404 SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid; 405 } 406 } {a A 9} 407} 408do_test collate4-2.1.10 { 409 execsql { 410 DROP TABLE collate4t1; 411 DROP TABLE collate4t2; 412 } 413} {} 414 415do_test collate4-2.2.0 { 416 execsql { 417 CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c); 418 CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT); 419 420 INSERT INTO collate4t1 VALUES('0', '0', '0'); 421 INSERT INTO collate4t1 VALUES('0', '0', '1'); 422 INSERT INTO collate4t1 VALUES('0', '1', '0'); 423 INSERT INTO collate4t1 VALUES('0', '1', '1'); 424 INSERT INTO collate4t1 VALUES('1', '0', '0'); 425 INSERT INTO collate4t1 VALUES('1', '0', '1'); 426 INSERT INTO collate4t1 VALUES('1', '1', '0'); 427 INSERT INTO collate4t1 VALUES('1', '1', '1'); 428 insert into collate4t2 SELECT * FROM collate4t1; 429 } 430} {} 431do_test collate4-2.2.1 { 432 count { 433 SELECT * FROM collate4t2 NOT INDEXED NATURAL JOIN collate4t1 NOT INDEXED; 434 } 435} {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} 436do_test collate4-2.2.1b { 437 execsql { 438 CREATE INDEX collate4i1 ON collate4t1(a, b, c); 439 } 440 count { 441 SELECT * FROM collate4t2 NATURAL JOIN collate4t1; 442 } 443} {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} 444do_test collate4-2.2.2 { 445 execsql { 446 DROP INDEX collate4i1; 447 CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text); 448 } 449 count { 450 SELECT * FROM collate4t2 NATURAL JOIN collate4t1; 451 } 452} {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} 453 454do_test collate4-2.2.10 { 455 execsql { 456 DROP TABLE collate4t1; 457 DROP TABLE collate4t2; 458 } 459} {} 460 461# 462# These tests - collate4-3.* verify that indices that implement 463# UNIQUE and PRIMARY KEY constraints operate correctly with user 464# defined collation sequences. 465# 466do_test collate4-3.0 { 467 execsql { 468 CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE); 469 } 470} {} 471do_test collate4-3.1 { 472 catchsql { 473 INSERT INTO collate4t1 VALUES('abc'); 474 INSERT INTO collate4t1 VALUES('ABC'); 475 } 476} {1 {UNIQUE constraint failed: collate4t1.a}} 477do_test collate4-3.2 { 478 execsql { 479 SELECT * FROM collate4t1; 480 } 481} {abc} 482do_test collate4-3.3 { 483 catchsql { 484 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1; 485 } 486} {1 {UNIQUE constraint failed: collate4t1.a}} 487do_test collate4-3.4 { 488 catchsql { 489 INSERT INTO collate4t1 VALUES(1); 490 UPDATE collate4t1 SET a = 'abc'; 491 } 492} {1 {UNIQUE constraint failed: collate4t1.a}} 493do_test collate4-3.5 { 494 execsql { 495 DROP TABLE collate4t1; 496 CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE); 497 } 498} {} 499do_test collate4-3.6 { 500 catchsql { 501 INSERT INTO collate4t1 VALUES('abc'); 502 INSERT INTO collate4t1 VALUES('ABC'); 503 } 504} {1 {UNIQUE constraint failed: collate4t1.a}} 505do_test collate4-3.7 { 506 execsql { 507 SELECT * FROM collate4t1; 508 } 509} {abc} 510do_test collate4-3.8 { 511 catchsql { 512 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1; 513 } 514} {1 {UNIQUE constraint failed: collate4t1.a}} 515do_test collate4-3.9 { 516 catchsql { 517 INSERT INTO collate4t1 VALUES(1); 518 UPDATE collate4t1 SET a = 'abc'; 519 } 520} {1 {UNIQUE constraint failed: collate4t1.a}} 521do_test collate4-3.10 { 522 execsql { 523 DROP TABLE collate4t1; 524 CREATE TABLE collate4t1(a); 525 CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE); 526 } 527} {} 528do_test collate4-3.11 { 529 catchsql { 530 INSERT INTO collate4t1 VALUES('abc'); 531 INSERT INTO collate4t1 VALUES('ABC'); 532 } 533} {1 {UNIQUE constraint failed: collate4t1.a}} 534do_test collate4-3.12 { 535 execsql { 536 SELECT * FROM collate4t1; 537 } 538} {abc} 539do_test collate4-3.13 { 540 catchsql { 541 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1; 542 } 543} {1 {UNIQUE constraint failed: collate4t1.a}} 544do_test collate4-3.14 { 545 catchsql { 546 INSERT INTO collate4t1 VALUES(1); 547 UPDATE collate4t1 SET a = 'abc'; 548 } 549} {1 {UNIQUE constraint failed: collate4t1.a}} 550 551do_test collate4-3.15 { 552 execsql { 553 DROP TABLE collate4t1; 554 } 555} {} 556 557# Mimic the SQLite 2 collation type NUMERIC. 558db collate numeric numeric_collate 559proc numeric_collate {lhs rhs} { 560 if {$lhs == $rhs} {return 0} 561 return [expr ($lhs>$rhs)?1:-1] 562} 563 564# 565# These tests - collate4-4.* check that min() and max() only ever 566# use indices constructed with built-in collation type numeric. 567# 568# CHANGED: min() and max() now use the collation type. If there 569# is an indice that can be used, it is used. 570# 571do_test collate4-4.0 { 572 execsql { 573 CREATE TABLE collate4t1(a COLLATE TEXT); 574 INSERT INTO collate4t1 VALUES('2'); 575 INSERT INTO collate4t1 VALUES('10'); 576 INSERT INTO collate4t1 VALUES('20'); 577 INSERT INTO collate4t1 VALUES('104'); 578 } 579} {} 580do_test collate4-4.1 { 581 count { 582 SELECT max(a) FROM collate4t1 583 } 584} {20 3} 585do_test collate4-4.2 { 586 count { 587 SELECT min(a) FROM collate4t1 588 } 589} {10 3} 590do_test collate4-4.3 { 591 # Test that the index with collation type TEXT is used. 592 execsql { 593 CREATE INDEX collate4i1 ON collate4t1(a); 594 } 595 count { 596 SELECT min(a) FROM collate4t1; 597 } 598} {10 1} 599do_test collate4-4.4 { 600 count { 601 SELECT max(a) FROM collate4t1; 602 } 603} {20 0} 604do_test collate4-4.5 { 605 # Test that the index with collation type NUMERIC is not used. 606 execsql { 607 DROP INDEX collate4i1; 608 CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC); 609 } 610 count { 611 SELECT min(a) FROM collate4t1; 612 } 613} {10 3} 614do_test collate4-4.6 { 615 count { 616 SELECT max(a) FROM collate4t1; 617 } 618} {20 3} 619do_test collate4-4.7 { 620 execsql { 621 DROP TABLE collate4t1; 622 } 623} {} 624 625# Also test the scalar min() and max() functions. 626# 627do_test collate4-4.8 { 628 execsql { 629 CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC); 630 INSERT INTO collate4t1 VALUES('11', '101'); 631 INSERT INTO collate4t1 VALUES('101', '11') 632 } 633} {} 634do_test collate4-4.9 { 635 execsql { 636 SELECT max(a, b) FROM collate4t1; 637 } 638} {11 11} 639do_test collate4-4.10 { 640 execsql { 641 SELECT max(b, a) FROM collate4t1; 642 } 643} {101 101} 644do_test collate4-4.11 { 645 execsql { 646 SELECT max(a, '101') FROM collate4t1; 647 } 648} {11 101} 649do_test collate4-4.12 { 650 execsql { 651 SELECT max('101', a) FROM collate4t1; 652 } 653} {11 101} 654do_test collate4-4.13 { 655 execsql { 656 SELECT max(b, '101') FROM collate4t1; 657 } 658} {101 101} 659do_test collate4-4.14 { 660 execsql { 661 SELECT max('101', b) FROM collate4t1; 662 } 663} {101 101} 664 665do_test collate4-4.15 { 666 execsql { 667 DROP TABLE collate4t1; 668 } 669} {} 670 671# 672# These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY 673# indices do not confuse collation sequences. 674# 675# These indices are never used for sorting in SQLite. And you can't 676# create another index on an INTEGER PRIMARY KEY column, so we don't have 677# to test that. 678# (Revised 2004-Nov-22): The ROWID can be used for sorting now. 679# 680do_test collate4-6.0 { 681 execsql { 682 CREATE TABLE collate4t1(a INTEGER PRIMARY KEY); 683 INSERT INTO collate4t1 VALUES(101); 684 INSERT INTO collate4t1 VALUES(10); 685 INSERT INTO collate4t1 VALUES(15); 686 } 687} {} 688do_test collate4-6.1 { 689 cksort { 690 SELECT * FROM collate4t1 ORDER BY 1; 691 } 692} {10 15 101 nosort} 693do_test collate4-6.2 { 694 cksort { 695 SELECT * FROM collate4t1 ORDER BY oid; 696 } 697} {10 15 101 nosort} 698do_test collate4-6.3 { 699 cksort { 700 SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT; 701 } 702} {10 101 15 sort} 703 704finish_test 705