gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/collate8.test (about)

     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  # 2015-02-09:  Added tests to make sure COLLATE passes through function
    17  # calls.  Ticket [ca0d20b6cdddec5e81b8d66f89c46a5583b5f6f6].
    18  #
    19  
    20  set testdir [file dirname $argv0]
    21  source $testdir/tester.tcl
    22  
    23  do_test collate8-1.1 {
    24    execsql {
    25      CREATE TABLE t1(a TEXT COLLATE nocase);
    26      INSERT INTO t1 VALUES('aaa');
    27      INSERT INTO t1 VALUES('BBB');
    28      INSERT INTO t1 VALUES('ccc');
    29      INSERT INTO t1 VALUES('DDD');
    30      SELECT a FROM t1 ORDER BY a;
    31    }
    32  } {aaa BBB ccc DDD}
    33  do_test collate8-1.2 {
    34    execsql {
    35      SELECT rowid FROM t1 WHERE a<'ccc' ORDER BY 1
    36    }
    37  } {1 2}
    38  do_test collate8-1.3 {
    39    execsql {
    40      SELECT rowid FROM t1 WHERE a<'ccc' COLLATE binary ORDER BY 1
    41    }
    42  } {1 2 4}
    43  do_test collate8-1.4 {
    44    execsql {
    45      SELECT rowid FROM t1 WHERE +a<'ccc' ORDER BY 1
    46    }
    47  } {1 2}
    48  do_test collate8-1.5 {
    49    execsql {
    50      SELECT a FROM t1 ORDER BY +a
    51    }
    52  } {aaa BBB ccc DDD}
    53  do_test collate8-1.11 {
    54    execsql {
    55      SELECT a AS x FROM t1 ORDER BY "x";
    56    }
    57  } {aaa BBB ccc DDD}
    58  do_test collate8-1.12 {
    59    execsql {
    60      SELECT a AS x FROM t1 WHERE x<'ccc' ORDER BY 1
    61    }
    62  } {aaa BBB}
    63  do_test collate8-1.13 {
    64    execsql {
    65      SELECT a AS x FROM t1 WHERE x<'ccc' COLLATE binary ORDER BY [x]
    66    }
    67  } {aaa BBB DDD}
    68  do_test collate8-1.14 {
    69    execsql {
    70      SELECT a AS x FROM t1 WHERE +x<'ccc' ORDER BY 1
    71    }
    72  } {aaa BBB}
    73  do_test collate8-1.15 {
    74    execsql {
    75      SELECT a AS x FROM t1 ORDER BY +x
    76    }
    77  } {aaa BBB ccc DDD}
    78  
    79  
    80  # When a result-set column is aliased into a WHERE clause, make sure the
    81  # collating sequence logic works correctly.
    82  #
    83  do_test collate8-2.1 {
    84    execsql {
    85      CREATE TABLE t2(a);
    86      INSERT INTO t2 VALUES('abc');
    87      INSERT INTO t2 VALUES('ABC');
    88      SELECT a AS x FROM t2 WHERE x='abc';
    89    }
    90  } {abc}
    91  do_test collate8-2.2 {
    92    execsql {
    93      SELECT a AS x FROM t2 WHERE x='abc' COLLATE nocase;
    94    }
    95  } {abc ABC}
    96  do_test collate8-2.3 {
    97    execsql {
    98      SELECT a AS x FROM t2 WHERE (x COLLATE nocase)='abc';
    99    }
   100  } {abc ABC}
   101  do_test collate8-2.4 {
   102    execsql {
   103      SELECT a COLLATE nocase AS x FROM t2 WHERE x='abc';
   104    }
   105  } {abc ABC}
   106  do_test collate8-2.5 {
   107    execsql {
   108      SELECT a COLLATE nocase AS x FROM t2 WHERE (x COLLATE binary)='abc';
   109    }
   110  } {abc}
   111  do_test collate8-2.6 {
   112    execsql {
   113      SELECT a COLLATE nocase AS x FROM t2 WHERE x='abc' COLLATE binary;
   114    }
   115  } {abc ABC}
   116  do_test collate8-2.7 {
   117    execsql {
   118      SELECT * FROM t2 WHERE (a COLLATE nocase)='abc' COLLATE binary;
   119    }
   120  } {abc ABC}
   121  do_test collate8-2.8 {
   122    execsql {
   123      SELECT a COLLATE nocase AS x FROM t2 WHERE 'abc'=x COLLATE binary;
   124    }
   125  } {abc}
   126  
   127  # Make sure the COLLATE operator perculates up through function calls
   128  # and other Expr structures that use the Expr.x.pList field.
   129  #
   130  do_execsql_test collate8-3.1 {
   131    SELECT 'abc'==('ABC'||'') COLLATE nocase;
   132    SELECT 'abc'==('ABC'||'' COLLATE nocase);
   133    SELECT 'abc'==('ABC'||('' COLLATE nocase));
   134    SELECT 'abc'==('ABC'||upper('' COLLATE nocase));
   135  } {1 1 1 1}
   136  do_execsql_test collate8-3.2 {
   137    SELECT 'abc'==('ABC'||max('' COLLATE nocase,'' COLLATE binary));
   138  } {1}
   139  
   140  # The COLLATE binary is on the left and so takes precedence
   141  do_execsql_test collate8-3.3 {
   142    SELECT 'abc'==('ABC'||max('' COLLATE binary,'' COLLATE nocase));
   143  } {0}
   144  
   145  do_execsql_test collate8-3.4 {
   146    SELECT 'abc'==('ABC'||CASE WHEN 1-1=2 THEN '' COLLATE nocase
   147                                          ELSE '' COLLATE binary END);
   148    SELECT 'abc'==('ABC'||CASE WHEN 1+1=2 THEN '' COLLATE nocase
   149                                          ELSE '' COLLATE binary END);
   150  } {1 1}
   151  do_execsql_test collate8-3.5 {
   152    SELECT 'abc'==('ABC'||CASE WHEN 1=2 THEN '' COLLATE binary
   153                                        ELSE '' COLLATE nocase END);
   154  } {0}
   155  
   156  
   157  finish_test