1 # 2 # 2001 September 15 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 file is testing DISTINCT, UNION, INTERSECT and EXCEPT 14 # SELECT statements that use user-defined collation sequences. Also 15 # GROUP BY clauses that use user-defined collation sequences. 16 # 17 # $Id: collate5.test,v 1.7 2008/09/16 11:58:20 drh Exp $ 18 19 set testdir [file dirname $argv0] 20 source $testdir/tester.tcl 21 22 23 # 24 # Tests are organised as follows: 25 # collate5-1.* - DISTINCT 26 # collate5-2.* - Compound SELECT 27 # collate5-3.* - ORDER BY on compound SELECT 28 # collate5-4.* - GROUP BY 29 30 # Create the collation sequence 'TEXT', purely for asthetic reasons. The 31 # test cases in this script could just as easily use BINARY. 32 db collate TEXT [list string compare] 33 34 # Mimic the SQLite 2 collation type NUMERIC. 35 db collate numeric numeric_collate 36 proc numeric_collate {lhs rhs} { 37 if {$lhs == $rhs} {return 0} 38 return [expr ($lhs>$rhs)?1:-1] 39 } 40 41 # 42 # These tests - collate5-1.* - focus on the DISTINCT keyword. 43 # 44 do_test collate5-1.0 { 45 execsql { 46 CREATE TABLE collate5t1(a COLLATE nocase, b COLLATE text); 47 48 INSERT INTO collate5t1 VALUES('a', 'apple'); 49 INSERT INTO collate5t1 VALUES('A', 'Apple'); 50 INSERT INTO collate5t1 VALUES('b', 'banana'); 51 INSERT INTO collate5t1 VALUES('B', 'banana'); 52 INSERT INTO collate5t1 VALUES('n', NULL); 53 INSERT INTO collate5t1 VALUES('N', NULL); 54 } 55 } {} 56 do_test collate5-1.1 { 57 execsql { 58 SELECT DISTINCT a FROM collate5t1; 59 } 60 } {A B N} 61 do_test collate5-1.2 { 62 execsql { 63 SELECT DISTINCT b FROM collate5t1; 64 } 65 } {{} Apple apple banana} 66 do_test collate5-1.3 { 67 execsql { 68 SELECT DISTINCT a, b FROM collate5t1; 69 } 70 } {A Apple a apple B banana N {}} 71 72 # Ticket #3376 73 # 74 do_test collate5-1.11 { 75 execsql { 76 CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY); 77 INSERT INTO tkt3376 VALUES('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'); 78 INSERT INTO tkt3376 VALUES('ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789'); 79 SELECT DISTINCT a FROM tkt3376; 80 } 81 } {abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789} 82 do_test collate5-1.12 { 83 sqlite3 db2 :memory: 84 db2 eval { 85 PRAGMA encoding=UTF16le; 86 CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY); 87 INSERT INTO tkt3376 VALUES('abc'); 88 INSERT INTO tkt3376 VALUES('ABX'); 89 SELECT DISTINCT a FROM tkt3376; 90 } 91 } {abc ABX} 92 catch {db2 close} 93 94 # The remainder of this file tests compound SELECT statements. 95 # Omit it if the library is compiled such that they are omitted. 96 # 97 ifcapable !compound { 98 finish_test 99 return 100 } 101 102 # 103 # Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT 104 # queries that use user-defined collation sequences. 105 # 106 # collate5-2.1.* - UNION 107 # collate5-2.2.* - INTERSECT 108 # collate5-2.3.* - EXCEPT 109 # 110 do_test collate5-2.0 { 111 execsql { 112 CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase); 113 114 INSERT INTO collate5t2 VALUES('a', 'apple'); 115 INSERT INTO collate5t2 VALUES('A', 'apple'); 116 INSERT INTO collate5t2 VALUES('b', 'banana'); 117 INSERT INTO collate5t2 VALUES('B', 'Banana'); 118 } 119 } {} 120 121 do_test collate5-2.1.1 { 122 execsql { 123 SELECT a FROM collate5t1 UNION select a FROM collate5t2; 124 } 125 } {A B N} 126 do_test collate5-2.1.2 { 127 execsql { 128 SELECT a FROM collate5t2 UNION select a FROM collate5t1; 129 } 130 } {A B N a b n} 131 do_test collate5-2.1.3 { 132 execsql { 133 SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2; 134 } 135 } {A Apple A apple B Banana b banana N {}} 136 do_test collate5-2.1.4 { 137 execsql { 138 SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1; 139 } 140 } {A Apple B banana N {} a apple b banana n {}} 141 142 do_test collate5-2.2.1 { 143 execsql { 144 SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2; 145 } 146 } {N} 147 do_test collate5-2.2.2 { 148 execsql { 149 SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a'; 150 } 151 } {A a} 152 do_test collate5-2.2.3 { 153 execsql { 154 SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2; 155 } 156 } {A Apple N {}} 157 do_test collate5-2.2.4 { 158 execsql { 159 SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1 160 where a != 'a'; 161 } 162 } {A apple a apple} 163 164 do_test collate5-2.3.1 { 165 execsql { 166 SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2; 167 } 168 } {A B} 169 do_test collate5-2.3.2 { 170 execsql { 171 SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a'; 172 } 173 } {B b} 174 do_test collate5-2.3.3 { 175 execsql { 176 SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2; 177 } 178 } {a apple B banana} 179 do_test collate5-2.3.4 { 180 execsql { 181 SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1; 182 } 183 } {A apple B Banana a apple b banana} 184 185 # 186 # This test ensures performs a UNION operation with a bunch of different 187 # length records. The goal is to test that the logic that compares records 188 # for the compound SELECT operators works with record lengths that lie 189 # either side of the troublesome 256 and 65536 byte marks. 190 # 191 set ::lens [list \ 192 0 1 2 3 4 5 6 7 8 9 \ 193 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \ 194 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \ 195 65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \ 196 65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \ 197 65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ] 198 do_test collate5-2.4.0 { 199 execsql { 200 BEGIN; 201 CREATE TABLE collate5t3(a, b); 202 } 203 foreach ii $::lens { 204 execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');" 205 } 206 expr [llength [execsql { 207 COMMIT; 208 SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3; 209 }]] / 2 210 } [llength $::lens] 211 do_test collate5-2.4.1 { 212 execsql {DROP TABLE collate5t3;} 213 } {} 214 unset ::lens 215 216 # 217 # These tests - collate5-3.* - focus on compound SELECT queries that 218 # feature ORDER BY clauses. 219 # 220 do_test collate5-3.0 { 221 execsql { 222 SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1; 223 } 224 } {a A a A b B b B n N} 225 do_test collate5-3.1 { 226 execsql { 227 SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1; 228 } 229 } {A A B B N a a b b n} 230 do_test collate5-3.2 { 231 execsql { 232 SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 233 ORDER BY 1 COLLATE TEXT; 234 } 235 } {A A B B N a a b b n} 236 237 do_test collate5-3.3 { 238 execsql { 239 CREATE TABLE collate5t_cn(a COLLATE NUMERIC); 240 CREATE TABLE collate5t_ct(a COLLATE TEXT); 241 INSERT INTO collate5t_cn VALUES('1'); 242 INSERT INTO collate5t_cn VALUES('11'); 243 INSERT INTO collate5t_cn VALUES('101'); 244 INSERT INTO collate5t_ct SELECT * FROM collate5t_cn; 245 } 246 } {} 247 do_test collate5-3.4 { 248 execsql { 249 SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1; 250 } 251 } {1 11 101} 252 do_test collate5-3.5 { 253 execsql { 254 SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1; 255 } 256 } {1 101 11} 257 258 do_test collate5-3.20 { 259 execsql { 260 DROP TABLE collate5t_cn; 261 DROP TABLE collate5t_ct; 262 DROP TABLE collate5t1; 263 DROP TABLE collate5t2; 264 } 265 } {} 266 267 do_test collate5-4.0 { 268 execsql { 269 CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC); 270 INSERT INTO collate5t1 VALUES('a', '1'); 271 INSERT INTO collate5t1 VALUES('A', '1.0'); 272 INSERT INTO collate5t1 VALUES('b', '2'); 273 INSERT INTO collate5t1 VALUES('B', '3'); 274 } 275 } {} 276 do_test collate5-4.1 { 277 string tolower [execsql { 278 SELECT a, count(*) FROM collate5t1 GROUP BY a; 279 }] 280 } {a 2 b 2} 281 do_test collate5-4.2 { 282 execsql { 283 SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b; 284 } 285 } {A 1.0 2 b 2 1 B 3 1} 286 do_test collate5-4.3 { 287 execsql { 288 DROP TABLE collate5t1; 289 } 290 } {} 291 292 finish_test 293
