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