#

# 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.6 2008/08/20 16:35:10 drh 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_collate

proc 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.1.1 {

  execsql {

    SELECT a FROM collate2t1 WHERE a COLLATE binary > 'aa' ORDER BY 1;

  }

} {ab bA bB ba bb}

do_test collate2-1.1.2 {

  execsql {

    SELECT a FROM collate2t1 WHERE b COLLATE binary > 'aa' ORDER BY 1;

  }

} {ab bA bB ba bb}

do_test collate2-1.1.3 {

  execsql {

    SELECT a FROM collate2t1 WHERE c COLLATE binary > '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.2.1 {

  execsql {

    SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa'

     ORDER BY 1, oid;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-1.2.2 {

  execsql {

    SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa'

     ORDER BY 1, oid;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-1.2.3 {

  execsql {

    SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa'

     ORDER BY 1, oid;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-1.2.4 {

  execsql {

    SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY +b;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-1.2.5 {

  execsql {

    SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa' ORDER BY +b;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-1.2.6 {

  execsql {

    SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa' ORDER BY +b;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-1.2.7 {

  execsql {

    SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa' ORDER BY +b;

  }

} {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.3.1 {

  execsql {

    SELECT c FROM collate2t1 WHERE a COLLATE backwards > 'aa'

    ORDER BY 1;

  }

} {ba Ab Bb ab bb}

do_test collate2-1.3.2 {

  execsql {

    SELECT c FROM collate2t1 WHERE b COLLATE backwards > 'aa'

    ORDER BY 1;

  }

} {ba Ab Bb ab bb}

do_test collate2-1.3.3 {

  execsql {

    SELECT c FROM collate2t1 WHERE c COLLATE backwards > '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.5.1 {

  execsql {

    SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY +b;

  }

} {}

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.17.1 {

  execsql {

    SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY +b;

  }

} {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 subquery

do_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;

  }

} {ab bA bB ba bb}

do_test collate2-2.14 {

  execsql {

    SELECT b FROM collate2t1 WHERE NOT b <= 'aa' ORDER BY 1, oid;

  }

} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}

do_test collate2-2.15 {

  execsql {

    SELECT c FROM collate2t1 WHERE NOT c <= 'aa' ORDER BY 1;

  }

} {ba Ab Bb ab bb}

do_test collate2-2.16 {

  execsql {

    SELECT a FROM collate2t1 WHERE a NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;

  }

} {AA AB aA aB aa ab bA bB ba bb}

do_test collate2-2.17 {

  execsql {

    SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;

  }

} {}

do_test collate2-2.18 {

  execsql {

    SELECT c FROM collate2t1 WHERE c NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;

  }

} {AA BA aA bA AB BB aB bB ab bb}

do_test collate2-2.19 {

  execsql {

    SELECT a FROM collate2t1 WHERE NOT CASE a WHEN 'aa' THEN 1 ELSE 0 END;

  }

} {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

do_test collate2-2.20 {

  execsql {

    SELECT b FROM collate2t1 WHERE NOT CASE b WHEN 'aa' THEN 1 ELSE 0 END;

  }

} {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}

do_test collate2-2.21 {

  execsql {

    SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;

  }

} {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

ifcapable subquery {

  do_test collate2-2.22 {

    execsql {

      SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');

    }

  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

  do_test collate2-2.23 {

    execsql {

      SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');

    }

  } {ab ba aB bA Ab Ba AB BA}

  do_test collate2-2.24 {

    execsql {

      SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');

    }

  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

  do_test collate2-2.25 {

    execsql {

      SELECT a FROM collate2t1 

        WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));

    }

  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

  do_test collate2-2.26 {

    execsql {

      SELECT b FROM collate2t1 

        WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));

    }

  } {ab ba aB bA Ab Ba AB BA}

  do_test collate2-2.27 {

    execsql {

      SELECT c FROM collate2t1 

        WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));

    }

  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

}

do_test collate2-3.1 {

  execsql {

    SELECT a > 'aa' FROM collate2t1;

  }

} {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}

do_test collate2-3.2 {

  execsql {

    SELECT b > 'aa' FROM collate2t1;

  }

} {{} 0 1 1 1 0 1 1 1 0 1 1 1 0 1 1 1}

do_test collate2-3.3 {

  execsql {

    SELECT c > 'aa' FROM collate2t1;

  }

} {{} 0 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}

do_test collate2-3.4 {

  execsql {

    SELECT a < 'aa' FROM collate2t1;

  }

} {{} 0 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}

do_test collate2-3.5 {

  execsql {

    SELECT b < 'aa' FROM collate2t1;

  }

} {{} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}

do_test collate2-3.6 {

  execsql {

    SELECT c < 'aa' FROM collate2t1;

  }

} {{} 0 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}

do_test collate2-3.7 {

  execsql {

    SELECT a = 'aa' FROM collate2t1;

  }

} {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}

do_test collate2-3.8 {

  execsql {

    SELECT b = 'aa' FROM collate2t1;

  }

} {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}

do_test collate2-3.9 {

  execsql {

    SELECT c = 'aa' FROM collate2t1;

  }

} {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}

do_test collate2-3.10 {

  execsql {

    SELECT a <= 'aa' FROM collate2t1;

  }

} {{} 1 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}

do_test collate2-3.11 {

  execsql {

    SELECT b <= 'aa' FROM collate2t1;

  }

} {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}

do_test collate2-3.12 {

  execsql {

    SELECT c <= 'aa' FROM collate2t1;

  }

} {{} 1 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}

do_test collate2-3.13 {

  execsql {