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