## 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: collate2.test,v 1.4 2005/01/21 03:12:16 danielk1977 Exp $set testdir [file dirname $argv0]source $testdir/tester.tcl## Tests are organised as follows:## collate2-1.* WHERE <expr> expressions (sqliteExprIfTrue).# collate2-2.* WHERE NOT <expr> expressions (sqliteExprIfFalse).# collate2-3.* SELECT <expr> expressions (sqliteExprCode).# collate2-4.* Precedence of collation/data types in binary comparisons# collate2-5.* JOIN syntax.## Create a collation type BACKWARDS for use in testing. This collation type# is similar to the built-in TEXT collation type except the order of# characters in each string is reversed before the comparison is performed.db collate BACKWARDS backwards_collateproc backwards_collate {a b} {  set ra {};  set rb {}  foreach c [split $a {}] { set ra $c$ra }  foreach c [split $b {}] { set rb $c$rb }  return [string compare $ra $rb]}# The following values are used in these tests:# NULL   aa ab ba bb   aA aB bA bB   Aa Ab Ba Bb   AA AB BA BB ## The collation orders for each of the tested collation types are:## BINARY:    NULL  AA AB Aa Ab  BA BB Ba Bb  aA aB aa ab  bA bB ba bb # NOCASE:    NULL  aa aA Aa AA  ab aB Ab AB  ba bA Ba BA  bb bB Bb BB # BACKWARDS: NULL  AA BA aA bA  AB BB aB bB  Aa Ba aa ba  Ab Bb ab bb ## These tests verify that the default collation type for a column is used# for comparison operators (<, >, <=, >=, =) involving that column and # an expression that is not a column with a default collation type.# # The collation sequences BINARY and NOCASE are built-in, the BACKWARDS# collation sequence is implemented by the TCL proc backwards_collate# above.#do_test collate2-1.0 {  execsql {    CREATE TABLE collate2t1(      a COLLATE BINARY,       b COLLATE NOCASE,       c COLLATE BACKWARDS    );    INSERT INTO collate2t1 VALUES( NULL, NULL, NULL );    INSERT INTO collate2t1 VALUES( 'aa', 'aa', 'aa' );    INSERT INTO collate2t1 VALUES( 'ab', 'ab', 'ab' );    INSERT INTO collate2t1 VALUES( 'ba', 'ba', 'ba' );    INSERT INTO collate2t1 VALUES( 'bb', 'bb', 'bb' );    INSERT INTO collate2t1 VALUES( 'aA', 'aA', 'aA' );    INSERT INTO collate2t1 VALUES( 'aB', 'aB', 'aB' );    INSERT INTO collate2t1 VALUES( 'bA', 'bA', 'bA' );    INSERT INTO collate2t1 VALUES( 'bB', 'bB', 'bB' );    INSERT INTO collate2t1 VALUES( 'Aa', 'Aa', 'Aa' );    INSERT INTO collate2t1 VALUES( 'Ab', 'Ab', 'Ab' );    INSERT INTO collate2t1 VALUES( 'Ba', 'Ba', 'Ba' );    INSERT INTO collate2t1 VALUES( 'Bb', 'Bb', 'Bb' );    INSERT INTO collate2t1 VALUES( 'AA', 'AA', 'AA' );    INSERT INTO collate2t1 VALUES( 'AB', 'AB', 'AB' );    INSERT INTO collate2t1 VALUES( 'BA', 'BA', 'BA' );    INSERT INTO collate2t1 VALUES( 'BB', 'BB', 'BB' );  }  if {[info exists collate_test_use_index]} {     execsql {      CREATE INDEX collate2t1_i1 ON collate2t1(a);      CREATE INDEX collate2t1_i2 ON collate2t1(b);      CREATE INDEX collate2t1_i3 ON collate2t1(c);    }  }} {}do_test collate2-1.1 {  execsql {    SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;  }} {ab bA bB ba bb}do_test collate2-1.2 {  execsql {    SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid;  }} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}do_test collate2-1.3 {  execsql {    SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;  }} {ba Ab Bb ab bb}do_test collate2-1.4 {  execsql {    SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1;  }} {AA AB Aa Ab BA BB Ba Bb aA aB}do_test collate2-1.5 {  execsql {    SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid;  }} {}do_test collate2-1.6 {  execsql {    SELECT c FROM collate2t1 WHERE c < 'aa' ORDER BY 1;  }} {AA BA aA bA AB BB aB bB Aa Ba}do_test collate2-1.7 {  execsql {    SELECT a FROM collate2t1 WHERE a = 'aa';  }} {aa}do_test collate2-1.8 {  execsql {    SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid;  }} {aa aA Aa AA}do_test collate2-1.9 {  execsql {    SELECT c FROM collate2t1 WHERE c = 'aa';  }} {aa}do_test collate2-1.10 {  execsql {    SELECT a FROM collate2t1 WHERE a >= 'aa' ORDER BY 1;  }} {aa ab bA bB ba bb}do_test collate2-1.11 {  execsql {    SELECT b FROM collate2t1 WHERE b >= 'aa' ORDER BY 1, oid;  }} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}do_test collate2-1.12 {  execsql {    SELECT c FROM collate2t1 WHERE c >= 'aa' ORDER BY 1;  }} {aa ba Ab Bb ab bb}do_test collate2-1.13 {  execsql {    SELECT a FROM collate2t1 WHERE a <= 'aa' ORDER BY 1;  }} {AA AB Aa Ab BA BB Ba Bb aA aB aa}do_test collate2-1.14 {  execsql {    SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid;  }} {aa aA Aa AA}do_test collate2-1.15 {  execsql {    SELECT c FROM collate2t1 WHERE c <= 'aa' ORDER BY 1;  }} {AA BA aA bA AB BB aB bB Aa Ba aa}do_test collate2-1.16 {  execsql {    SELECT a FROM collate2t1 WHERE a BETWEEN 'Aa' AND 'Bb' ORDER BY 1;  }} {Aa Ab BA BB Ba Bb}do_test collate2-1.17 {  execsql {    SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;  }} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}do_test collate2-1.18 {  execsql {    SELECT c FROM collate2t1 WHERE c BETWEEN 'Aa' AND 'Bb' ORDER BY 1;  }} {Aa Ba aa ba Ab Bb}do_test collate2-1.19 {  execsql {    SELECT a FROM collate2t1 WHERE       CASE a WHEN 'aa' THEN 1 ELSE 0 END        ORDER BY 1, oid;  }} {aa}do_test collate2-1.20 {  execsql {    SELECT b FROM collate2t1 WHERE       CASE b WHEN 'aa' THEN 1 ELSE 0 END        ORDER BY 1, oid;  }} {aa aA Aa AA}do_test collate2-1.21 {  execsql {    SELECT c FROM collate2t1 WHERE       CASE c WHEN 'aa' THEN 1 ELSE 0 END        ORDER BY 1, oid;  }} {aa}ifcapable subquery {  do_test collate2-1.22 {    execsql {      SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;    }  } {aa bb}  do_test collate2-1.23 {    execsql {      SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;    }  } {aa aA Aa AA bb bB Bb BB}  do_test collate2-1.24 {    execsql {      SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;    }  } {aa bb}  do_test collate2-1.25 {    execsql {      SELECT a FROM collate2t1         WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));    }  } {aa bb}  do_test collate2-1.26 {    execsql {      SELECT b FROM collate2t1         WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));    }  } {aa bb aA bB Aa Bb AA BB}  do_test collate2-1.27 {    execsql {      SELECT c FROM collate2t1         WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));    }  } {aa bb}} ;# ifcapable subquerydo_test collate2-2.1 {  execsql {    SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;  }} {AA AB Aa Ab BA BB Ba Bb aA aB aa}do_test collate2-2.2 {  execsql {    SELECT b FROM collate2t1 WHERE NOT b > 'aa' ORDER BY 1, oid;  }} {aa aA Aa AA}do_test collate2-2.3 {  execsql {    SELECT c FROM collate2t1 WHERE NOT c > 'aa' ORDER BY 1;  }} {AA BA aA bA AB BB aB bB Aa Ba aa}do_test collate2-2.4 {  execsql {    SELECT a FROM collate2t1 WHERE NOT a < 'aa' ORDER BY 1;  }} {aa ab bA bB ba bb}do_test collate2-2.5 {  execsql {    SELECT b FROM collate2t1 WHERE NOT b < 'aa' ORDER BY 1, oid;  }} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}do_test collate2-2.6 {  execsql {    SELECT c FROM collate2t1 WHERE NOT c < 'aa' ORDER BY 1;  }} {aa ba Ab Bb ab bb}do_test collate2-2.7 {  execsql {    SELECT a FROM collate2t1 WHERE NOT a = 'aa';  }} {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}do_test collate2-2.8 {  execsql {    SELECT b FROM collate2t1 WHERE NOT b = 'aa';  }} {ab ba bb aB bA bB Ab Ba Bb AB BA BB}do_test collate2-2.9 {  execsql {    SELECT c FROM collate2t1 WHERE NOT c = 'aa';  }} {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}do_test collate2-2.10 {  execsql {    SELECT a FROM collate2t1 WHERE NOT a >= 'aa' ORDER BY 1;  }} {AA AB Aa Ab BA BB Ba Bb aA aB}do_test collate2-2.11 {  execsql {    SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid;  }} {}do_test collate2-2.12 {  execsql {    SELECT c FROM collate2t1 WHERE NOT c >= 'aa' ORDER BY 1;  }} {AA BA aA bA AB BB aB bB Aa Ba}do_test collate2-2.13 {  execsql {    SELECT a FROM collate2t1 WHERE NOT a <= 'aa' ORDER BY 1;  }