Home | History | Annotate | Download | only in test 
      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
    126