danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 1 | # |
danielk1977 | dc1bdc4 | 2004-06-11 10:51:27 +0000 | [diff] [blame] | 2 | # 2001 September 15 |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 3 | # |
danielk1977 | dc1bdc4 | 2004-06-11 10:51:27 +0000 | [diff] [blame] | 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 | #*********************************************************************** |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 12 | # This file implements regression tests for SQLite library. The |
dan | 80103fc | 2015-03-20 08:43:59 +0000 | [diff] [blame] | 13 | # focus of this script is testing collation sequences. |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 14 | # |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 15 | |
| 16 | set testdir [file dirname $argv0] |
| 17 | source $testdir/tester.tcl |
dan | 80103fc | 2015-03-20 08:43:59 +0000 | [diff] [blame] | 18 | set testprefix collate1 |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 19 | |
| 20 | # |
| 21 | # Tests are roughly organised as follows: |
| 22 | # |
| 23 | # collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause. |
| 24 | # collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause. |
| 25 | # collate1-3.* - ORDER BY using a default collation type. Also that an |
| 26 | # explict collate type overrides a default collate type. |
| 27 | # collate1-4.* - ORDER BY using a data type. |
| 28 | # |
| 29 | |
| 30 | # |
| 31 | # Collation type 'HEX'. If an argument can be interpreted as a hexadecimal |
| 32 | # number, then it is converted to one before the comparison is performed. |
| 33 | # Numbers are less than other strings. If neither argument is a number, |
| 34 | # [string compare] is used. |
| 35 | # |
| 36 | db collate HEX hex_collate |
| 37 | proc hex_collate {lhs rhs} { |
| 38 | set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs] |
| 39 | set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs] |
| 40 | if {$lhs_ishex && $rhs_ishex} { |
| 41 | set lhsx [scan $lhs %x] |
| 42 | set rhsx [scan $rhs %x] |
| 43 | if {$lhs < $rhs} {return -1} |
| 44 | if {$lhs == $rhs} {return 0} |
| 45 | if {$lhs > $rhs} {return 1} |
| 46 | } |
| 47 | if {$lhs_ishex} { |
| 48 | return -1; |
| 49 | } |
| 50 | if {$rhs_ishex} { |
| 51 | return 1; |
| 52 | } |
| 53 | return [string compare $lhs $rhs] |
| 54 | } |
| 55 | db function hex {format 0x%X} |
| 56 | |
| 57 | # Mimic the SQLite 2 collation type NUMERIC. |
| 58 | db collate numeric numeric_collate |
| 59 | proc numeric_collate {lhs rhs} { |
| 60 | if {$lhs == $rhs} {return 0} |
| 61 | return [expr ($lhs>$rhs)?1:-1] |
| 62 | } |
| 63 | |
| 64 | do_test collate1-1.0 { |
| 65 | execsql { |
| 66 | CREATE TABLE collate1t1(c1, c2); |
| 67 | INSERT INTO collate1t1 VALUES(45, hex(45)); |
| 68 | INSERT INTO collate1t1 VALUES(NULL, NULL); |
| 69 | INSERT INTO collate1t1 VALUES(281, hex(281)); |
| 70 | } |
| 71 | } {} |
| 72 | do_test collate1-1.1 { |
| 73 | execsql { |
| 74 | SELECT c2 FROM collate1t1 ORDER BY 1; |
| 75 | } |
| 76 | } {{} 0x119 0x2D} |
| 77 | do_test collate1-1.2 { |
| 78 | execsql { |
| 79 | SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex; |
| 80 | } |
| 81 | } {{} 0x2D 0x119} |
| 82 | do_test collate1-1.3 { |
| 83 | execsql { |
| 84 | SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC; |
| 85 | } |
| 86 | } {0x119 0x2D {}} |
| 87 | do_test collate1-1.4 { |
| 88 | execsql { |
| 89 | SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC; |
| 90 | } |
| 91 | } {{} 0x2D 0x119} |
| 92 | do_test collate1-1.5 { |
| 93 | execsql { |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 94 | SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 |
| 95 | } |
| 96 | } {{} 0x2D 0x119} |
| 97 | do_test collate1-1.6 { |
| 98 | execsql { |
| 99 | SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC |
| 100 | } |
| 101 | } {{} 0x2D 0x119} |
| 102 | do_test collate1-1.7 { |
| 103 | execsql { |
| 104 | SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC |
| 105 | } |
| 106 | } {0x119 0x2D {}} |
| 107 | do_test collate1-1.99 { |
| 108 | execsql { |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 109 | DROP TABLE collate1t1; |
| 110 | } |
| 111 | } {} |
| 112 | |
| 113 | do_test collate1-2.0 { |
| 114 | execsql { |
| 115 | CREATE TABLE collate1t1(c1, c2); |
| 116 | INSERT INTO collate1t1 VALUES('5', '0x11'); |
| 117 | INSERT INTO collate1t1 VALUES('5', '0xA'); |
| 118 | INSERT INTO collate1t1 VALUES(NULL, NULL); |
| 119 | INSERT INTO collate1t1 VALUES('7', '0xA'); |
| 120 | INSERT INTO collate1t1 VALUES('11', '0x11'); |
| 121 | INSERT INTO collate1t1 VALUES('11', '0x101'); |
| 122 | } |
| 123 | } {} |
| 124 | do_test collate1-2.2 { |
| 125 | execsql { |
| 126 | SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex; |
| 127 | } |
| 128 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 129 | do_test collate1-2.3 { |
| 130 | execsql { |
| 131 | SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex; |
| 132 | } |
| 133 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
| 134 | do_test collate1-2.4 { |
| 135 | execsql { |
| 136 | SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex; |
| 137 | } |
| 138 | } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}} |
| 139 | do_test collate1-2.5 { |
| 140 | execsql { |
| 141 | SELECT c1, c2 FROM collate1t1 |
| 142 | ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC; |
| 143 | } |
| 144 | } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}} |
| 145 | do_test collate1-2.6 { |
| 146 | execsql { |
| 147 | SELECT c1, c2 FROM collate1t1 |
| 148 | ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC; |
| 149 | } |
| 150 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 151 | do_test collate1-2.12.1 { |
| 152 | execsql { |
| 153 | SELECT c1 COLLATE numeric, c2 FROM collate1t1 |
| 154 | ORDER BY 1, 2 COLLATE hex; |
| 155 | } |
| 156 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 157 | do_test collate1-2.12.2 { |
| 158 | execsql { |
| 159 | SELECT c1 COLLATE hex, c2 FROM collate1t1 |
| 160 | ORDER BY 1 COLLATE numeric, 2 COLLATE hex; |
| 161 | } |
| 162 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 163 | do_test collate1-2.12.3 { |
| 164 | execsql { |
| 165 | SELECT c1, c2 COLLATE hex FROM collate1t1 |
| 166 | ORDER BY 1 COLLATE numeric, 2; |
| 167 | } |
| 168 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 169 | do_test collate1-2.12.4 { |
| 170 | execsql { |
| 171 | SELECT c1 COLLATE numeric, c2 COLLATE hex |
| 172 | FROM collate1t1 |
| 173 | ORDER BY 1, 2; |
| 174 | } |
| 175 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 176 | do_test collate1-2.13 { |
| 177 | execsql { |
| 178 | SELECT c1 COLLATE binary, c2 COLLATE hex |
| 179 | FROM collate1t1 |
| 180 | ORDER BY 1, 2; |
| 181 | } |
| 182 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
| 183 | do_test collate1-2.14 { |
| 184 | execsql { |
| 185 | SELECT c1, c2 |
| 186 | FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex; |
| 187 | } |
| 188 | } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}} |
| 189 | do_test collate1-2.15 { |
| 190 | execsql { |
| 191 | SELECT c1 COLLATE binary, c2 COLLATE hex |
| 192 | FROM collate1t1 |
| 193 | ORDER BY 1 DESC, 2 DESC; |
| 194 | } |
| 195 | } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}} |
| 196 | do_test collate1-2.16 { |
| 197 | execsql { |
| 198 | SELECT c1 COLLATE hex, c2 COLLATE binary |
| 199 | FROM collate1t1 |
| 200 | ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC; |
| 201 | } |
| 202 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
| 203 | do_test collate1-2.99 { |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 204 | execsql { |
| 205 | DROP TABLE collate1t1; |
| 206 | } |
| 207 | } {} |
| 208 | |
| 209 | # |
| 210 | # These tests ensure that the default collation type for a column is used |
| 211 | # by an ORDER BY clause correctly. The focus is all the different ways |
| 212 | # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc. |
| 213 | # |
| 214 | do_test collate1-3.0 { |
| 215 | execsql { |
| 216 | CREATE TABLE collate1t1(a COLLATE hex, b); |
| 217 | INSERT INTO collate1t1 VALUES( '0x5', 5 ); |
| 218 | INSERT INTO collate1t1 VALUES( '1', 1 ); |
| 219 | INSERT INTO collate1t1 VALUES( '0x45', 69 ); |
| 220 | INSERT INTO collate1t1 VALUES( NULL, NULL ); |
| 221 | SELECT * FROM collate1t1 ORDER BY a; |
| 222 | } |
| 223 | } {{} {} 1 1 0x5 5 0x45 69} |
| 224 | |
| 225 | do_test collate1-3.1 { |
| 226 | execsql { |
| 227 | SELECT * FROM collate1t1 ORDER BY 1; |
| 228 | } |
| 229 | } {{} {} 1 1 0x5 5 0x45 69} |
| 230 | do_test collate1-3.2 { |
| 231 | execsql { |
| 232 | SELECT * FROM collate1t1 ORDER BY collate1t1.a; |
| 233 | } |
| 234 | } {{} {} 1 1 0x5 5 0x45 69} |
| 235 | do_test collate1-3.3 { |
| 236 | execsql { |
| 237 | SELECT * FROM collate1t1 ORDER BY main.collate1t1.a; |
| 238 | } |
| 239 | } {{} {} 1 1 0x5 5 0x45 69} |
| 240 | do_test collate1-3.4 { |
| 241 | execsql { |
| 242 | SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1; |
| 243 | } |
| 244 | } {{} {} 1 1 0x5 5 0x45 69} |
| 245 | do_test collate1-3.5 { |
| 246 | execsql { |
| 247 | SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary; |
| 248 | } |
| 249 | } {{} {} 0x45 69 0x5 5 1 1} |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 250 | do_test collate1-3.5.1 { |
| 251 | execsql { |
| 252 | SELECT a COLLATE binary as c1, b as c2 |
| 253 | FROM collate1t1 ORDER BY c1; |
| 254 | } |
| 255 | } {{} {} 0x45 69 0x5 5 1 1} |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 256 | do_test collate1-3.6 { |
| 257 | execsql { |
| 258 | DROP TABLE collate1t1; |
| 259 | } |
| 260 | } {} |
| 261 | |
| 262 | # Update for SQLite version 3. The collate1-4.* test cases were written |
| 263 | # before manifest types were introduced. The following test cases still |
| 264 | # work, due to the 'affinity' mechanism, but they don't prove anything |
| 265 | # about collation sequences. |
| 266 | # |
| 267 | do_test collate1-4.0 { |
| 268 | execsql { |
| 269 | CREATE TABLE collate1t1(c1 numeric, c2 text); |
| 270 | INSERT INTO collate1t1 VALUES(1, 1); |
| 271 | INSERT INTO collate1t1 VALUES(12, 12); |
| 272 | INSERT INTO collate1t1 VALUES(NULL, NULL); |
| 273 | INSERT INTO collate1t1 VALUES(101, 101); |
| 274 | } |
| 275 | } {} |
| 276 | do_test collate1-4.1 { |
| 277 | execsql { |
| 278 | SELECT c1 FROM collate1t1 ORDER BY 1; |
| 279 | } |
| 280 | } {{} 1 12 101} |
| 281 | do_test collate1-4.2 { |
| 282 | execsql { |
| 283 | SELECT c2 FROM collate1t1 ORDER BY 1; |
| 284 | } |
| 285 | } {{} 1 101 12} |
| 286 | do_test collate1-4.3 { |
| 287 | execsql { |
| 288 | SELECT c2+0 FROM collate1t1 ORDER BY 1; |
| 289 | } |
drh | 8df447f | 2005-11-01 15:48:24 +0000 | [diff] [blame] | 290 | } {{} 1 12 101} |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 291 | do_test collate1-4.4 { |
| 292 | execsql { |
| 293 | SELECT c1||'' FROM collate1t1 ORDER BY 1; |
| 294 | } |
| 295 | } {{} 1 101 12} |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 296 | do_test collate1-4.4.1 { |
| 297 | execsql { |
| 298 | SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1; |
| 299 | } |
| 300 | } {{} 1 12 101} |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 301 | do_test collate1-4.5 { |
| 302 | execsql { |
| 303 | DROP TABLE collate1t1; |
| 304 | } |
| 305 | } {} |
| 306 | |
drh | 0908186 | 2013-06-09 20:16:26 +0000 | [diff] [blame] | 307 | # A problem reported on the mailing list: A CREATE TABLE statement |
| 308 | # is allowed to have two or more COLLATE clauses on the same column. |
| 309 | # That probably ought to be an error, but we allow it for backwards |
| 310 | # compatibility. Just make sure it works and doesn't leak memory. |
| 311 | # |
| 312 | do_test collate1-5.1 { |
| 313 | execsql { |
| 314 | CREATE TABLE c5( |
| 315 | id INTEGER PRIMARY KEY, |
| 316 | a TEXT COLLATE binary COLLATE nocase COLLATE rtrim, |
| 317 | b TEXT COLLATE nocase COLLATE binary, |
| 318 | c TEXT COLLATE rtrim COLLATE binary COLLATE rtrim COLLATE nocase |
| 319 | ); |
| 320 | INSERT INTO c5 VALUES(1, 'abc','abc','abc'); |
| 321 | INSERT INTO c5 VALUES(2, 'abc ','ABC','ABC'); |
| 322 | SELECT id FROM c5 WHERE a='abc' ORDER BY id; |
| 323 | } |
| 324 | } {1 2} |
| 325 | do_test collate1-5.2 { |
| 326 | execsql { |
| 327 | SELECT id FROM c5 WHERE b='abc' ORDER BY id; |
| 328 | } |
| 329 | } {1} |
| 330 | do_test collate1-5.3 { |
| 331 | execsql { |
| 332 | SELECT id FROM c5 WHERE c='abc' ORDER BY id; |
| 333 | } |
| 334 | } {1 2} |
| 335 | |
dan | 80103fc | 2015-03-20 08:43:59 +0000 | [diff] [blame] | 336 | |
| 337 | |
| 338 | #------------------------------------------------------------------------- |
| 339 | # Fix problems with handling collation sequences named '"""'. |
| 340 | # |
| 341 | do_execsql_test 6.1 { |
| 342 | SELECT """"""""; |
| 343 | } {\"\"\"} |
| 344 | |
| 345 | do_catchsql_test 6.2 { |
| 346 | CREATE TABLE x1(a); |
| 347 | SELECT a FROM x1 ORDER BY a COLLATE """"""""; |
| 348 | } {1 {no such collation sequence: """}} |
| 349 | |
| 350 | do_catchsql_test 6.3 { |
| 351 | SELECT a FROM x1 ORDER BY 1 COLLATE """"""""; |
| 352 | } {1 {no such collation sequence: """}} |
| 353 | |
| 354 | do_catchsql_test 6.4 { |
| 355 | SELECT 0 UNION SELECT 0 ORDER BY 1 COLLATE """"""""; |
| 356 | } {1 {no such collation sequence: """}} |
| 357 | |
| 358 | db collate {"""} [list string compare -nocase] |
| 359 | |
| 360 | do_execsql_test 6.5 { |
| 361 | PRAGMA foreign_keys = ON; |
| 362 | CREATE TABLE p1(a PRIMARY KEY COLLATE '"""'); |
| 363 | CREATE TABLE c1(x, y REFERENCES p1); |
| 364 | } {} |
| 365 | |
| 366 | do_execsql_test 6.6 { |
| 367 | INSERT INTO p1 VALUES('abc'); |
| 368 | INSERT INTO c1 VALUES(1, 'ABC'); |
| 369 | } |
| 370 | |
| 371 | ifcapable foreignkey { |
| 372 | do_catchsql_test 6.7 { |
| 373 | DELETE FROM p1 WHERE rowid = 1 |
| 374 | } {1 {FOREIGN KEY constraint failed}} |
| 375 | } |
| 376 | |
| 377 | do_execsql_test 6.8 { |
| 378 | INSERT INTO p1 VALUES('abb'); |
| 379 | INSERT INTO p1 VALUES('wxz'); |
| 380 | INSERT INTO p1 VALUES('wxy'); |
| 381 | |
| 382 | INSERT INTO c1 VALUES(2, 'abb'); |
| 383 | INSERT INTO c1 VALUES(3, 'wxz'); |
| 384 | INSERT INTO c1 VALUES(4, 'WXY'); |
| 385 | SELECT x, y FROM c1 ORDER BY y COLLATE """"""""; |
| 386 | } {2 abb 1 ABC 4 WXY 3 wxz} |
| 387 | |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 388 | finish_test |
dan | 80103fc | 2015-03-20 08:43:59 +0000 | [diff] [blame] | 389 | |
| 390 | |