test/collate4.test

   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 # 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 #
  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, rowid}
  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, rowid}
 175 } {{} a A b B sort}
 176 do_test collate4-1.1.23 {
 177   cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid}
 178 } {{} a A b B sort}
 179 do_test collate4-1.1.24 {
 180   cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid}
 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, rowid}
 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, rowid}
 275 } {{} a A b B sort}
 276 do_test collate4-1.2.16 {
 277   cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid}
 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 4}
 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      ORDER BY collate4t2.rowid, collate4t1.rowid
 369   }
 370 } {A a A A 19}
 371 do_test collate4-2.1.5 {
 372   count {
 373     SELECT * FROM collate4t2, collate4t1 WHERE b = a;
 374   }
 375 } {A A 3}
 376 ifcapable 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 }
 408 do_test collate4-2.1.10 {
 409   execsql {
 410     DROP TABLE collate4t1;
 411     DROP TABLE collate4t2;
 412   }
 413 } {}
 414
 415 do_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 } {}
 431 do_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}
 436 do_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}
 444 do_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
 454 do_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 #
 466 do_test collate4-3.0 {
 467   execsql {
 468     CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
 469   }
 470 } {}
 471 do_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}}
 477 do_test collate4-3.2 {
 478   execsql {
 479     SELECT * FROM collate4t1;
 480   }
 481 } {abc}
 482 do_test collate4-3.3 {
 483   catchsql {
 484     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
 485   }
 486 } {1 {UNIQUE constraint failed: collate4t1.a}}
 487 do_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}}
 493 do_test collate4-3.5 {
 494   execsql {
 495     DROP TABLE collate4t1;
 496     CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
 497   }
 498 } {}
 499 do_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}}
 505 do_test collate4-3.7 {
 506   execsql {
 507     SELECT * FROM collate4t1;
 508   }
 509 } {abc}
 510 do_test collate4-3.8 {
 511   catchsql {
 512     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
 513   }
 514 } {1 {UNIQUE constraint failed: collate4t1.a}}
 515 do_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}}
 521 do_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 } {}
 528 do_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}}
 534 do_test collate4-3.12 {
 535   execsql {
 536     SELECT * FROM collate4t1;
 537   }
 538 } {abc}
 539 do_test collate4-3.13 {
 540   catchsql {
 541     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
 542   }
 543 } {1 {UNIQUE constraint failed: collate4t1.a}}
 544 do_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
 551 do_test collate4-3.15 {
 552   execsql {
 553     DROP TABLE collate4t1;
 554   }
 555 } {}
 556
 557 # Mimic the SQLite 2 collation type NUMERIC.
 558 db collate numeric numeric_collate
 559 proc 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 #
 571 do_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 } {}
 580 do_test collate4-4.1 {
 581   count {
 582     SELECT max(a) FROM collate4t1
 583   }
 584 } {20 3}
 585 do_test collate4-4.2 {
 586   count {
 587     SELECT min(a) FROM collate4t1
 588   }
 589 } {10 3}
 590 do_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}
 599 do_test collate4-4.4 {
 600   count {
 601     SELECT max(a) FROM collate4t1;
 602   }
 603 } {20 0}
 604 do_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}
 614 do_test collate4-4.6 {
 615   count {
 616     SELECT max(a) FROM collate4t1;
 617   }
 618 } {20 3}
 619 do_test collate4-4.7 {
 620   execsql {
 621     DROP TABLE collate4t1;
 622   }
 623 } {}
 624
 625 # Also test the scalar min() and max() functions.
 626 #
 627 do_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 } {}
 634 do_test collate4-4.9 {
 635   execsql {
 636     SELECT max(a, b) FROM collate4t1;
 637   }
 638 } {11 11}
 639 do_test collate4-4.10 {
 640   execsql {
 641     SELECT max(b, a) FROM collate4t1;
 642   }
 643 } {101 101}
 644 do_test collate4-4.11 {
 645   execsql {
 646     SELECT max(a, '101') FROM collate4t1;
 647   }
 648 } {11 101}
 649 do_test collate4-4.12 {
 650   execsql {
 651     SELECT max('101', a) FROM collate4t1;
 652   }
 653 } {11 101}
 654 do_test collate4-4.13 {
 655   execsql {
 656     SELECT max(b, '101') FROM collate4t1;
 657   }
 658 } {101 101}
 659 do_test collate4-4.14 {
 660   execsql {
 661     SELECT max('101', b) FROM collate4t1;
 662   }
 663 } {101 101}
 664
 665 do_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 #
 680 do_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 } {}
 688 do_test collate4-6.1 {
 689   cksort {
 690     SELECT * FROM collate4t1 ORDER BY 1;
 691   }
 692 } {10 15 101 nosort}
 693 do_test collate4-6.2 {
 694   cksort {
 695     SELECT * FROM collate4t1 ORDER BY oid;
 696   }
 697 } {10 15 101 nosort}
 698 do_test collate4-6.3 {
 699   cksort {
 700     SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
 701   }
 702 } {10 101 15 sort}
 703
 704 finish_test