1 #

     2 # 2007 June 20

     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 making sure collations pass through the

    14 # unary + operator.

    15 #

    16 # $Id: collate8.test,v 1.2 2008/08/25 12:14:09 drh Exp $

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 do_test collate8-1.1 {

    22   execsql {

    23     CREATE TABLE t1(a TEXT COLLATE nocase);

    24     INSERT INTO t1 VALUES('aaa');

    25     INSERT INTO t1 VALUES('BBB');

    26     INSERT INTO t1 VALUES('ccc');

    27     INSERT INTO t1 VALUES('DDD');

    28     SELECT a FROM t1 ORDER BY a;

    29   }

    30 } {aaa BBB ccc DDD}

    31 do_test collate8-1.2 {

    32   execsql {

    33     SELECT rowid FROM t1 WHERE a<'ccc' ORDER BY 1

    34   }

    35 } {1 2}

    36 do_test collate8-1.3 {

    37   execsql {

    38     SELECT rowid FROM t1 WHERE a<'ccc' COLLATE binary ORDER BY 1

    39   }

    40 } {1 2 4}

    41 do_test collate8-1.4 {

    42   execsql {

    43     SELECT rowid FROM t1 WHERE +a<'ccc' ORDER BY 1

    44   }

    45 } {1 2}

    46 do_test collate8-1.5 {

    47   execsql {

    48     SELECT a FROM t1 ORDER BY +a

    49   }

    50 } {aaa BBB ccc DDD}

    51 do_test collate8-1.11 {

    52   execsql {

    53     SELECT a AS x FROM t1 ORDER BY "x";

    54   }

    55 } {aaa BBB ccc DDD}

    56 do_test collate8-1.12 {

    57   execsql {

    58     SELECT a AS x FROM t1 WHERE x<'ccc' ORDER BY 1

    59   }

    60 } {aaa BBB}

    61 do_test collate8-1.13 {

    62   execsql {

    63     SELECT a AS x FROM t1 WHERE x<'ccc' COLLATE binary ORDER BY [x]

    64   }

    65 } {aaa BBB DDD}

    66 do_test collate8-1.14 {

    67   execsql {

    68     SELECT a AS x FROM t1 WHERE +x<'ccc' ORDER BY 1

    69   }

    70 } {aaa BBB}

    71 do_test collate8-1.15 {

    72   execsql {

    73     SELECT a AS x FROM t1 ORDER BY +x

    74   }

    75 } {aaa BBB ccc DDD}

    76 

    77 

    78 # When a result-set column is aliased into a WHERE clause, make sure the

    79 # collating sequence logic works correctly.

    80 #

    81 do_test collate8-2.1 {

    82   execsql {

    83     CREATE TABLE t2(a);

    84     INSERT INTO t2 VALUES('abc');

    85     INSERT INTO t2 VALUES('ABC');

    86     SELECT a AS x FROM t2 WHERE x='abc';

    87   }

    88 } {abc}

    89 do_test collate8-2.2 {

    90   execsql {

    91     SELECT a AS x FROM t2 WHERE x='abc' COLLATE nocase;

    92   }

    93 } {abc ABC}

    94 do_test collate8-2.3 {

    95   execsql {

    96     SELECT a AS x FROM t2 WHERE (x COLLATE nocase)='abc';

    97   }

    98 } {abc ABC}

    99 do_test collate8-2.4 {

   100   execsql {

   101     SELECT a COLLATE nocase AS x FROM t2 WHERE x='abc';

   102   }

   103 } {abc ABC}

   104 do_test collate8-2.5 {

   105   execsql {

   106     SELECT a COLLATE nocase AS x FROM t2 WHERE (x COLLATE binary)='abc';

   107   }

   108 } {abc}

   109 do_test collate8-2.6 {

   110   execsql {

   111     SELECT a COLLATE nocase AS x FROM t2 WHERE x='abc' COLLATE binary;

   112   }

   113 } {abc ABC}

   114 do_test collate8-2.7 {

   115   execsql {

   116     SELECT * FROM t2 WHERE (a COLLATE nocase)='abc' COLLATE binary;

   117   }

   118 } {abc ABC}

   119 do_test collate8-2.8 {

   120   execsql {

   121     SELECT a COLLATE nocase AS x FROM t2 WHERE 'abc'=x COLLATE binary;

   122   }

   123 } {abc}

   124 

   125 finish_test