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 |
danielk1977 | dc1bdc4 | 2004-06-11 10:51:27 +0000 | [diff] [blame] | 13 | # focus of this script is page cache subsystem. |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 14 | # |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 15 | # $Id: collate1.test,v 1.5 2007/02/01 23:02:46 drh Exp $ |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 16 | |
| 17 | set testdir [file dirname $argv0] |
| 18 | source $testdir/tester.tcl |
| 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 { |
drh | 0a8a406 | 2012-12-07 18:38:16 +0000 | [diff] [blame] | 78 | breakpoint |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 79 | execsql { |
| 80 | SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex; |
| 81 | } |
| 82 | } {{} 0x2D 0x119} |
| 83 | do_test collate1-1.3 { |
| 84 | execsql { |
| 85 | SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC; |
| 86 | } |
| 87 | } {0x119 0x2D {}} |
| 88 | do_test collate1-1.4 { |
| 89 | execsql { |
| 90 | SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC; |
| 91 | } |
| 92 | } {{} 0x2D 0x119} |
| 93 | do_test collate1-1.5 { |
| 94 | execsql { |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 95 | SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 |
| 96 | } |
| 97 | } {{} 0x2D 0x119} |
| 98 | do_test collate1-1.6 { |
| 99 | execsql { |
| 100 | SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC |
| 101 | } |
| 102 | } {{} 0x2D 0x119} |
| 103 | do_test collate1-1.7 { |
| 104 | execsql { |
| 105 | SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC |
| 106 | } |
| 107 | } {0x119 0x2D {}} |
| 108 | do_test collate1-1.99 { |
| 109 | execsql { |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 110 | DROP TABLE collate1t1; |
| 111 | } |
| 112 | } {} |
| 113 | |
| 114 | do_test collate1-2.0 { |
| 115 | execsql { |
| 116 | CREATE TABLE collate1t1(c1, c2); |
| 117 | INSERT INTO collate1t1 VALUES('5', '0x11'); |
| 118 | INSERT INTO collate1t1 VALUES('5', '0xA'); |
| 119 | INSERT INTO collate1t1 VALUES(NULL, NULL); |
| 120 | INSERT INTO collate1t1 VALUES('7', '0xA'); |
| 121 | INSERT INTO collate1t1 VALUES('11', '0x11'); |
| 122 | INSERT INTO collate1t1 VALUES('11', '0x101'); |
| 123 | } |
| 124 | } {} |
| 125 | do_test collate1-2.2 { |
| 126 | execsql { |
| 127 | SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex; |
| 128 | } |
| 129 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 130 | do_test collate1-2.3 { |
| 131 | execsql { |
| 132 | SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex; |
| 133 | } |
| 134 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
| 135 | do_test collate1-2.4 { |
| 136 | execsql { |
| 137 | SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex; |
| 138 | } |
| 139 | } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}} |
| 140 | do_test collate1-2.5 { |
| 141 | execsql { |
| 142 | SELECT c1, c2 FROM collate1t1 |
| 143 | ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC; |
| 144 | } |
| 145 | } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}} |
| 146 | do_test collate1-2.6 { |
| 147 | execsql { |
| 148 | SELECT c1, c2 FROM collate1t1 |
| 149 | ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC; |
| 150 | } |
| 151 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 152 | do_test collate1-2.12.1 { |
| 153 | execsql { |
| 154 | SELECT c1 COLLATE numeric, c2 FROM collate1t1 |
| 155 | ORDER BY 1, 2 COLLATE hex; |
| 156 | } |
| 157 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 158 | do_test collate1-2.12.2 { |
| 159 | execsql { |
| 160 | SELECT c1 COLLATE hex, c2 FROM collate1t1 |
| 161 | ORDER BY 1 COLLATE numeric, 2 COLLATE hex; |
| 162 | } |
| 163 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 164 | do_test collate1-2.12.3 { |
| 165 | execsql { |
| 166 | SELECT c1, c2 COLLATE hex FROM collate1t1 |
| 167 | ORDER BY 1 COLLATE numeric, 2; |
| 168 | } |
| 169 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 170 | do_test collate1-2.12.4 { |
| 171 | execsql { |
| 172 | SELECT c1 COLLATE numeric, c2 COLLATE hex |
| 173 | FROM collate1t1 |
| 174 | ORDER BY 1, 2; |
| 175 | } |
| 176 | } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101} |
| 177 | do_test collate1-2.13 { |
| 178 | execsql { |
| 179 | SELECT c1 COLLATE binary, c2 COLLATE hex |
| 180 | FROM collate1t1 |
| 181 | ORDER BY 1, 2; |
| 182 | } |
| 183 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
| 184 | do_test collate1-2.14 { |
| 185 | execsql { |
| 186 | SELECT c1, c2 |
| 187 | FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex; |
| 188 | } |
| 189 | } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}} |
| 190 | do_test collate1-2.15 { |
| 191 | execsql { |
| 192 | SELECT c1 COLLATE binary, c2 COLLATE hex |
| 193 | FROM collate1t1 |
| 194 | ORDER BY 1 DESC, 2 DESC; |
| 195 | } |
| 196 | } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}} |
| 197 | do_test collate1-2.16 { |
| 198 | execsql { |
| 199 | SELECT c1 COLLATE hex, c2 COLLATE binary |
| 200 | FROM collate1t1 |
| 201 | ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC; |
| 202 | } |
| 203 | } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA} |
| 204 | do_test collate1-2.99 { |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 205 | execsql { |
| 206 | DROP TABLE collate1t1; |
| 207 | } |
| 208 | } {} |
| 209 | |
| 210 | # |
| 211 | # These tests ensure that the default collation type for a column is used |
| 212 | # by an ORDER BY clause correctly. The focus is all the different ways |
| 213 | # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc. |
| 214 | # |
| 215 | do_test collate1-3.0 { |
| 216 | execsql { |
| 217 | CREATE TABLE collate1t1(a COLLATE hex, b); |
| 218 | INSERT INTO collate1t1 VALUES( '0x5', 5 ); |
| 219 | INSERT INTO collate1t1 VALUES( '1', 1 ); |
| 220 | INSERT INTO collate1t1 VALUES( '0x45', 69 ); |
| 221 | INSERT INTO collate1t1 VALUES( NULL, NULL ); |
| 222 | SELECT * FROM collate1t1 ORDER BY a; |
| 223 | } |
| 224 | } {{} {} 1 1 0x5 5 0x45 69} |
| 225 | |
| 226 | do_test collate1-3.1 { |
| 227 | execsql { |
| 228 | SELECT * FROM collate1t1 ORDER BY 1; |
| 229 | } |
| 230 | } {{} {} 1 1 0x5 5 0x45 69} |
| 231 | do_test collate1-3.2 { |
| 232 | execsql { |
| 233 | SELECT * FROM collate1t1 ORDER BY collate1t1.a; |
| 234 | } |
| 235 | } {{} {} 1 1 0x5 5 0x45 69} |
| 236 | do_test collate1-3.3 { |
| 237 | execsql { |
| 238 | SELECT * FROM collate1t1 ORDER BY main.collate1t1.a; |
| 239 | } |
| 240 | } {{} {} 1 1 0x5 5 0x45 69} |
| 241 | do_test collate1-3.4 { |
| 242 | execsql { |
| 243 | SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1; |
| 244 | } |
| 245 | } {{} {} 1 1 0x5 5 0x45 69} |
| 246 | do_test collate1-3.5 { |
| 247 | execsql { |
| 248 | SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary; |
| 249 | } |
| 250 | } {{} {} 0x45 69 0x5 5 1 1} |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 251 | do_test collate1-3.5.1 { |
| 252 | execsql { |
| 253 | SELECT a COLLATE binary as c1, b as c2 |
| 254 | FROM collate1t1 ORDER BY c1; |
| 255 | } |
| 256 | } {{} {} 0x45 69 0x5 5 1 1} |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 257 | do_test collate1-3.6 { |
| 258 | execsql { |
| 259 | DROP TABLE collate1t1; |
| 260 | } |
| 261 | } {} |
| 262 | |
| 263 | # Update for SQLite version 3. The collate1-4.* test cases were written |
| 264 | # before manifest types were introduced. The following test cases still |
| 265 | # work, due to the 'affinity' mechanism, but they don't prove anything |
| 266 | # about collation sequences. |
| 267 | # |
| 268 | do_test collate1-4.0 { |
| 269 | execsql { |
| 270 | CREATE TABLE collate1t1(c1 numeric, c2 text); |
| 271 | INSERT INTO collate1t1 VALUES(1, 1); |
| 272 | INSERT INTO collate1t1 VALUES(12, 12); |
| 273 | INSERT INTO collate1t1 VALUES(NULL, NULL); |
| 274 | INSERT INTO collate1t1 VALUES(101, 101); |
| 275 | } |
| 276 | } {} |
| 277 | do_test collate1-4.1 { |
| 278 | execsql { |
| 279 | SELECT c1 FROM collate1t1 ORDER BY 1; |
| 280 | } |
| 281 | } {{} 1 12 101} |
| 282 | do_test collate1-4.2 { |
| 283 | execsql { |
| 284 | SELECT c2 FROM collate1t1 ORDER BY 1; |
| 285 | } |
| 286 | } {{} 1 101 12} |
| 287 | do_test collate1-4.3 { |
| 288 | execsql { |
| 289 | SELECT c2+0 FROM collate1t1 ORDER BY 1; |
| 290 | } |
drh | 8df447f | 2005-11-01 15:48:24 +0000 | [diff] [blame] | 291 | } {{} 1 12 101} |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 292 | do_test collate1-4.4 { |
| 293 | execsql { |
| 294 | SELECT c1||'' FROM collate1t1 ORDER BY 1; |
| 295 | } |
| 296 | } {{} 1 101 12} |
drh | 8b4c40d | 2007-02-01 23:02:45 +0000 | [diff] [blame] | 297 | do_test collate1-4.4.1 { |
| 298 | execsql { |
| 299 | SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1; |
| 300 | } |
| 301 | } {{} 1 12 101} |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 302 | do_test collate1-4.5 { |
| 303 | execsql { |
| 304 | DROP TABLE collate1t1; |
| 305 | } |
| 306 | } {} |
| 307 | |
drh | 0908186 | 2013-06-09 20:16:26 +0000 | [diff] [blame] | 308 | # A problem reported on the mailing list: A CREATE TABLE statement |
| 309 | # is allowed to have two or more COLLATE clauses on the same column. |
| 310 | # That probably ought to be an error, but we allow it for backwards |
| 311 | # compatibility. Just make sure it works and doesn't leak memory. |
| 312 | # |
| 313 | do_test collate1-5.1 { |
| 314 | execsql { |
| 315 | CREATE TABLE c5( |
| 316 | id INTEGER PRIMARY KEY, |
| 317 | a TEXT COLLATE binary COLLATE nocase COLLATE rtrim, |
| 318 | b TEXT COLLATE nocase COLLATE binary, |
| 319 | c TEXT COLLATE rtrim COLLATE binary COLLATE rtrim COLLATE nocase |
| 320 | ); |
| 321 | INSERT INTO c5 VALUES(1, 'abc','abc','abc'); |
| 322 | INSERT INTO c5 VALUES(2, 'abc ','ABC','ABC'); |
| 323 | SELECT id FROM c5 WHERE a='abc' ORDER BY id; |
| 324 | } |
| 325 | } {1 2} |
| 326 | do_test collate1-5.2 { |
| 327 | execsql { |
| 328 | SELECT id FROM c5 WHERE b='abc' ORDER BY id; |
| 329 | } |
| 330 | } {1} |
| 331 | do_test collate1-5.3 { |
| 332 | execsql { |
| 333 | SELECT id FROM c5 WHERE c='abc' ORDER BY id; |
| 334 | } |
| 335 | } {1 2} |
| 336 | |
danielk1977 | 0202b29 | 2004-06-09 09:55:16 +0000 | [diff] [blame] | 337 | finish_test |