## 2001 September 15## The author disclaims copyright to this source code.  In place of# a legal notice, here is a blessing:##    May you do good and not evil.#    May you find forgiveness for yourself and forgive others.#    May you share freely, never taking more than you give.##***********************************************************************# This file implements regression tests for SQLite library.  The# focus of this script is page cache subsystem.## $Id: collate4.test,v 1.8 2005/04/01 10:47:40 drh Exp $set testdir [file dirname $argv0]source $testdir/tester.tcldb collate TEXT text_collateproc text_collate {a b} {  return [string compare $a $b]}# Do an SQL statement.  Append the search count to the end of the result.#proc count sql {  set ::sqlite_search_count 0  return [concat [execsql $sql] $::sqlite_search_count]}# This procedure executes the SQL.  Then it checks the generated program# for the SQL and appends a "nosort" to the result if the program contains the# SortCallback opcode.  If the program does not contain the SortCallback# opcode it appends "sort"#proc cksort {sql} {  set ::sqlite_sort_count 0  set data [execsql $sql]  if {$::sqlite_sort_count} {set x sort} {set x nosort}  lappend data $x  return $data}# # Test cases are organized roughly as follows:## collate4-1.*      ORDER BY.# collate4-2.*      WHERE clauses.# collate4-3.*      constraints (primary key, unique).# collate4-4.*      simple min() or max() queries.# collate4-5.*      REINDEX command# collate4-6.*      INTEGER PRIMARY KEY indices.### These tests - collate4-1.* - check that indices are correctly# selected or not selected to implement ORDER BY clauses when # user defined collation sequences are involved. ## Because these tests also exercise all the different ways indices # can be created, they also serve to verify that indices are correctly # initialised with user-defined collation sequences when they are# created.## Tests named collate4-1.1.* use indices with a single column. Tests# collate4-1.2.* use indices with two columns.#do_test collate4-1.1.0 {  execsql {    CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);    INSERT INTO collate4t1 VALUES( 'a', 'a' );    INSERT INTO collate4t1 VALUES( 'b', 'b' );    INSERT INTO collate4t1 VALUES( NULL, NULL );    INSERT INTO collate4t1 VALUES( 'B', 'B' );    INSERT INTO collate4t1 VALUES( 'A', 'A' );    CREATE INDEX collate4i1 ON collate4t1(a);    CREATE INDEX collate4i2 ON collate4t1(b);  }} {}do_test collate4-1.1.1 {  cksort {SELECT a FROM collate4t1 ORDER BY a}} {{} a A b B nosort}do_test collate4-1.1.2 {  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}} {{} a A b B nosort}do_test collate4-1.1.3 {  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}} {{} A B a b sort}do_test collate4-1.1.4 {  cksort {SELECT b FROM collate4t1 ORDER BY b}} {{} A B a b nosort}do_test collate4-1.1.5 {  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}} {{} A B a b nosort}do_test collate4-1.1.6 {  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}} {{} a A b B sort}do_test collate4-1.1.7 {  execsql {    CREATE TABLE collate4t2(      a PRIMARY KEY COLLATE NOCASE,       b UNIQUE COLLATE TEXT    );    INSERT INTO collate4t2 VALUES( 'a', 'a' );    INSERT INTO collate4t2 VALUES( NULL, NULL );    INSERT INTO collate4t2 VALUES( 'B', 'B' );  }} {}do_test collate4-1.1.8 {  cksort {SELECT a FROM collate4t2 ORDER BY a}} {{} a B nosort}do_test collate4-1.1.9 {  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}} {{} a B nosort}do_test collate4-1.1.10 {  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}} {{} B a sort}do_test collate4-1.1.11 {  cksort {SELECT b FROM collate4t2 ORDER BY b}} {{} B a nosort}do_test collate4-1.1.12 {  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}} {{} B a nosort}do_test collate4-1.1.13 {  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}} {{} a B sort}do_test collate4-1.1.14 {  execsql {    CREATE TABLE collate4t3(      b COLLATE TEXT,        a COLLATE NOCASE,       UNIQUE(a), PRIMARY KEY(b)    );    INSERT INTO collate4t3 VALUES( 'a', 'a' );    INSERT INTO collate4t3 VALUES( NULL, NULL );    INSERT INTO collate4t3 VALUES( 'B', 'B' );  }} {}do_test collate4-1.1.15 {  cksort {SELECT a FROM collate4t3 ORDER BY a}} {{} a B nosort}do_test collate4-1.1.16 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}} {{} a B nosort}do_test collate4-1.1.17 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}} {{} B a sort}do_test collate4-1.1.18 {  cksort {SELECT b FROM collate4t3 ORDER BY b}} {{} B a nosort}do_test collate4-1.1.19 {  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}} {{} B a nosort}do_test collate4-1.1.20 {  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}} {{} a B sort}do_test collate4-1.1.21 {  execsql {    CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT);    INSERT INTO collate4t4 VALUES( 'a', 'a' );    INSERT INTO collate4t4 VALUES( 'b', 'b' );    INSERT INTO collate4t4 VALUES( NULL, NULL );    INSERT INTO collate4t4 VALUES( 'B', 'B' );    INSERT INTO collate4t4 VALUES( 'A', 'A' );    CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);    CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);  }} {}do_test collate4-1.1.22 {  cksort {SELECT a FROM collate4t4 ORDER BY a}} {{} a A b B sort}do_test collate4-1.1.23 {  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}} {{} a A b B sort}do_test collate4-1.1.24 {  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}} {{} A B a b nosort}do_test collate4-1.1.25 {  cksort {SELECT b FROM collate4t4 ORDER BY b}} {{} A B a b sort}do_test collate4-1.1.26 {  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}} {{} A B a b sort}do_test collate4-1.1.27 {  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}} {{} a A b B nosort}do_test collate4-1.1.30 {  execsql {    DROP TABLE collate4t1;    DROP TABLE collate4t2;    DROP TABLE collate4t3;    DROP TABLE collate4t4;  }} {}do_test collate4-1.2.0 {  execsql {    CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);    INSERT INTO collate4t1 VALUES( 'a', 'a' );    INSERT INTO collate4t1 VALUES( 'b', 'b' );    INSERT INTO collate4t1 VALUES( NULL, NULL );    INSERT INTO collate4t1 VALUES( 'B', 'B' );    INSERT INTO collate4t1 VALUES( 'A', 'A' );    CREATE INDEX collate4i1 ON collate4t1(a, b);  }} {}do_test collate4-1.2.1 {  cksort {SELECT a FROM collate4t1 ORDER BY a}} {{} A a B b nosort}do_test collate4-1.2.2 {  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}} {{} A a B b nosort}do_test collate4-1.2.3 {  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}} {{} A B a b sort}do_test collate4-1.2.4 {  cksort {SELECT a FROM collate4t1 ORDER BY a, b}} {{} A a B b nosort}do_test collate4-1.2.5 {  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}} {{} a A b B sort}do_test collate4-1.2.6 {  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}} {{} A a B b nosort}do_test collate4-1.2.7 {  execsql {    CREATE TABLE collate4t2(      a COLLATE NOCASE,       b COLLATE TEXT,       PRIMARY KEY(a, b)    );    INSERT INTO collate4t2 VALUES( 'a', 'a' );    INSERT INTO collate4t2 VALUES( NULL, NULL );    INSERT INTO collate4t2 VALUES( 'B', 'B' );  }} {}do_test collate4-1.2.8 {  cksort {SELECT a FROM collate4t2 ORDER BY a}} {{} a B nosort}do_test collate4-1.2.9 {  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}} {{} a B nosort}do_test collate4-1.2.10 {  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}} {{} B a sort}do_test collate4-1.2.11 {  cksort {SELECT a FROM collate4t2 ORDER BY a, b}} {{} a B nosort}do_test collate4-1.2.12 {  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}} {{} a B sort}do_test collate4-1.2.13 {  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}} {{} a B nosort}do_test collate4-1.2.14 {  execsql {    CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);    INSERT INTO collate4t3 VALUES( 'a', 'a' );    INSERT INTO collate4t3 VALUES( 'b', 'b' );    INSERT INTO collate4t3 VALUES( NULL, NULL );    INSERT INTO collate4t3 VALUES( 'B', 'B' );    INSERT INTO collate4t3 VALUES( 'A', 'A' );    CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);  }} {}do_test collate4-1.2.15 {  cksort {SELECT a FROM collate4t3 ORDER BY a}} {{} a A b B sort}do_test collate4-1.2.16 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}} {{} a A b B sort}do_test collate4-1.2.17 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}} {{} A B a b nosort}do_test collate4-1.2.18 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}} {{} A B a b sort}do_test collate4-1.2.19 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}} {{} A B a b nosort}do_test collate4-1.2.20 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text}} {{} A B a b sort}do_test collate4-1.2.21 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}} {b a B A {} nosort}do_test collate4-1.2.22 {  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}} {b a B A {} sort}do_test collate4-1.2.23 {  cksort {SELECT a FROM collate4t3             ORDER BY a COLLATE text DESC, b COLLATE nocase}} {b a B A {} sort}do_test collate4-1.2.24 {  cksort {SELECT a FROM collate4t3             ORDER BY a COLLATE text DESC, b COLLATE nocase DESC}} {b a B A {} nosort}do_test collate4-1.2.25 {  execsql {    DROP TABLE collate4t1;    DROP TABLE collate4t2;    DROP TABLE collate4t3;  }} {}## These tests - collate4-2.* - check that indices are correctly# selected or not selected to implement WHERE clauses when user # defined collation sequences are involved. ## Indices may optimise WHERE clauses using <, >, <=, >=, = or IN# operators.#do_test collate4-2.1.0 {  execsql {    CREATE TABLE collate4t1(a COLLATE NOCASE);    CREATE TABLE collate4t2(b COLLATE TEXT);    INSERT INTO collate4t1 VALUES('a');    INSERT INTO collate4t1 VALUES('A');    INSERT INTO collate4t1 VALUES('b');    INSERT INTO collate4t1 VALUES('B');    INSERT INTO collate4t1 VALUES('c');    INSERT INTO collate4t1 VALUES('C');    INSERT INTO collate4t1 VALUES('d');    INSERT INTO collate4t1 VALUES('D');    INSERT INTO collate4t1 VALUES('e');    INSERT INTO collate4t1 VALUES('D');    INSERT INTO collate4t2 VALUES('A');    INSERT INTO collate4t2 VALUES('Z');  }} {}do_test collate4-2.1.1 {  count {    SELECT * FROM collate4t2, collate4t1 WHERE a = b;  }} {A a A A 19}do_test collate4-2.1.2 {  execsql {    CREATE INDEX collate4i1 ON collate4t1(a);  }  count {