4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
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.
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this script is page cache subsystem.
15 # $Id: collate2.test,v 1.4 2005/01/21 03:12:16 danielk1977 Exp $
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
21 # Tests are organised as follows:
23 # collate2-1.* WHERE <expr> expressions (sqliteExprIfTrue).
24 # collate2-2.* WHERE NOT <expr> expressions (sqliteExprIfFalse).
25 # collate2-3.* SELECT <expr> expressions (sqliteExprCode).
26 # collate2-4.* Precedence of collation/data types in binary comparisons
27 # collate2-5.* JOIN syntax.
30 # Create a collation type BACKWARDS for use in testing. This collation type
31 # is similar to the built-in TEXT collation type except the order of
32 # characters in each string is reversed before the comparison is performed.
33 db collate BACKWARDS backwards_collate
34 proc backwards_collate {a b} {
37 foreach c [split $a {}] { set ra $c$ra }
38 foreach c [split $b {}] { set rb $c$rb }
39 return [string compare $ra $rb]
42 # The following values are used in these tests:
43 # NULL aa ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB
45 # The collation orders for each of the tested collation types are:
47 # BINARY: NULL AA AB Aa Ab BA BB Ba Bb aA aB aa ab bA bB ba bb
48 # NOCASE: NULL aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB
49 # BACKWARDS: NULL AA BA aA bA AB BB aB bB Aa Ba aa ba Ab Bb ab bb
51 # These tests verify that the default collation type for a column is used
52 # for comparison operators (<, >, <=, >=, =) involving that column and
53 # an expression that is not a column with a default collation type.
55 # The collation sequences BINARY and NOCASE are built-in, the BACKWARDS
56 # collation sequence is implemented by the TCL proc backwards_collate
59 do_test collate2-1.0 {
61 CREATE TABLE collate2t1(
66 INSERT INTO collate2t1 VALUES( NULL, NULL, NULL );
68 INSERT INTO collate2t1 VALUES( 'aa', 'aa', 'aa' );
69 INSERT INTO collate2t1 VALUES( 'ab', 'ab', 'ab' );
70 INSERT INTO collate2t1 VALUES( 'ba', 'ba', 'ba' );
71 INSERT INTO collate2t1 VALUES( 'bb', 'bb', 'bb' );
73 INSERT INTO collate2t1 VALUES( 'aA', 'aA', 'aA' );
74 INSERT INTO collate2t1 VALUES( 'aB', 'aB', 'aB' );
75 INSERT INTO collate2t1 VALUES( 'bA', 'bA', 'bA' );
76 INSERT INTO collate2t1 VALUES( 'bB', 'bB', 'bB' );
78 INSERT INTO collate2t1 VALUES( 'Aa', 'Aa', 'Aa' );
79 INSERT INTO collate2t1 VALUES( 'Ab', 'Ab', 'Ab' );
80 INSERT INTO collate2t1 VALUES( 'Ba', 'Ba', 'Ba' );
81 INSERT INTO collate2t1 VALUES( 'Bb', 'Bb', 'Bb' );
83 INSERT INTO collate2t1 VALUES( 'AA', 'AA', 'AA' );
84 INSERT INTO collate2t1 VALUES( 'AB', 'AB', 'AB' );
85 INSERT INTO collate2t1 VALUES( 'BA', 'BA', 'BA' );
86 INSERT INTO collate2t1 VALUES( 'BB', 'BB', 'BB' );
88 if {[info exists collate_test_use_index]} {
90 CREATE INDEX collate2t1_i1 ON collate2t1(a);
91 CREATE INDEX collate2t1_i2 ON collate2t1(b);
92 CREATE INDEX collate2t1_i3 ON collate2t1(c);
96 do_test collate2-1.1 {
98 SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;
101 do_test collate2-1.2 {
103 SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid;
105 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
106 do_test collate2-1.3 {
108 SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;
111 do_test collate2-1.4 {
113 SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1;
115 } {AA AB Aa Ab BA BB Ba Bb aA aB}
116 do_test collate2-1.5 {
118 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid;
121 do_test collate2-1.6 {
123 SELECT c FROM collate2t1 WHERE c < 'aa' ORDER BY 1;
125 } {AA BA aA bA AB BB aB bB Aa Ba}
126 do_test collate2-1.7 {
128 SELECT a FROM collate2t1 WHERE a = 'aa';
131 do_test collate2-1.8 {
133 SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid;
136 do_test collate2-1.9 {
138 SELECT c FROM collate2t1 WHERE c = 'aa';
141 do_test collate2-1.10 {
143 SELECT a FROM collate2t1 WHERE a >= 'aa' ORDER BY 1;
145 } {aa ab bA bB ba bb}
146 do_test collate2-1.11 {
148 SELECT b FROM collate2t1 WHERE b >= 'aa' ORDER BY 1, oid;
150 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
151 do_test collate2-1.12 {
153 SELECT c FROM collate2t1 WHERE c >= 'aa' ORDER BY 1;
155 } {aa ba Ab Bb ab bb}
156 do_test collate2-1.13 {
158 SELECT a FROM collate2t1 WHERE a <= 'aa' ORDER BY 1;
160 } {AA AB Aa Ab BA BB Ba Bb aA aB aa}
161 do_test collate2-1.14 {
163 SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid;
166 do_test collate2-1.15 {
168 SELECT c FROM collate2t1 WHERE c <= 'aa' ORDER BY 1;
170 } {AA BA aA bA AB BB aB bB Aa Ba aa}
171 do_test collate2-1.16 {
173 SELECT a FROM collate2t1 WHERE a BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
175 } {Aa Ab BA BB Ba Bb}
176 do_test collate2-1.17 {
178 SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
180 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
181 do_test collate2-1.18 {
183 SELECT c FROM collate2t1 WHERE c BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
185 } {Aa Ba aa ba Ab Bb}
186 do_test collate2-1.19 {
188 SELECT a FROM collate2t1 WHERE
189 CASE a WHEN 'aa' THEN 1 ELSE 0 END
193 do_test collate2-1.20 {
195 SELECT b FROM collate2t1 WHERE
196 CASE b WHEN 'aa' THEN 1 ELSE 0 END
200 do_test collate2-1.21 {
202 SELECT c FROM collate2t1 WHERE
203 CASE c WHEN 'aa' THEN 1 ELSE 0 END
209 do_test collate2-1.22 {
211 SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;
214 do_test collate2-1.23 {
216 SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;
218 } {aa aA Aa AA bb bB Bb BB}
219 do_test collate2-1.24 {
221 SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;
224 do_test collate2-1.25 {
226 SELECT a FROM collate2t1
227 WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
230 do_test collate2-1.26 {
232 SELECT b FROM collate2t1
233 WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
235 } {aa bb aA bB Aa Bb AA BB}
236 do_test collate2-1.27 {
238 SELECT c FROM collate2t1
239 WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
242 } ;# ifcapable subquery
244 do_test collate2-2.1 {
246 SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;
248 } {AA AB Aa Ab BA BB Ba Bb aA aB aa}
249 do_test collate2-2.2 {
251 SELECT b FROM collate2t1 WHERE NOT b > 'aa' ORDER BY 1, oid;
254 do_test collate2-2.3 {
256 SELECT c FROM collate2t1 WHERE NOT c > 'aa' ORDER BY 1;
258 } {AA BA aA bA AB BB aB bB Aa Ba aa}
259 do_test collate2-2.4 {
261 SELECT a FROM collate2t1 WHERE NOT a < 'aa' ORDER BY 1;
263 } {aa ab bA bB ba bb}
264 do_test collate2-2.5 {
266 SELECT b FROM collate2t1 WHERE NOT b < 'aa' ORDER BY 1, oid;
268 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
269 do_test collate2-2.6 {
271 SELECT c FROM collate2t1 WHERE NOT c < 'aa' ORDER BY 1;
273 } {aa ba Ab Bb ab bb}
274 do_test collate2-2.7 {
276 SELECT a FROM collate2t1 WHERE NOT a = 'aa';
278 } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
279 do_test collate2-2.8 {
281 SELECT b FROM collate2t1 WHERE NOT b = 'aa';
283 } {ab ba bb aB bA bB Ab Ba Bb AB BA BB}
284 do_test collate2-2.9 {
286 SELECT c FROM collate2t1 WHERE NOT c = 'aa';
288 } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
289 do_test collate2-2.10 {
291 SELECT a FROM collate2t1 WHERE NOT a >= 'aa' ORDER BY 1;
293 } {AA AB Aa Ab BA BB Ba Bb aA aB}
294 do_test collate2-2.11 {
296 SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid;
299 do_test collate2-2.12 {
301 SELECT c FROM collate2t1 WHERE NOT c >= 'aa' ORDER BY 1;
303 } {AA BA aA bA AB BB aB bB Aa Ba}
304 do_test collate2-2.13 {
306 SELECT a FROM collate2t1 WHERE NOT a <= 'aa' ORDER BY 1;
309 do_test collate2-2.14 {
311 SELECT b FROM collate2t1 WHERE NOT b <= 'aa' ORDER BY 1, oid;
313 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
314 do_test collate2-2.15 {
316 SELECT c FROM collate2t1 WHERE NOT c <= 'aa' ORDER BY 1;
319 do_test collate2-2.16 {
321 SELECT a FROM collate2t1 WHERE a NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
323 } {AA AB aA aB aa ab bA bB ba bb}
324 do_test collate2-2.17 {
326 SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
329 do_test collate2-2.18 {
331 SELECT c FROM collate2t1 WHERE c NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
333 } {AA BA aA bA AB BB aB bB ab bb}
334 do_test collate2-2.19 {
336 SELECT a FROM collate2t1 WHERE NOT CASE a WHEN 'aa' THEN 1 ELSE 0 END;
338 } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
339 do_test collate2-2.20 {
341 SELECT b FROM collate2t1 WHERE NOT CASE b WHEN 'aa' THEN 1 ELSE 0 END;
343 } {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}
344 do_test collate2-2.21 {
346 SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;
348 } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
351 do_test collate2-2.22 {
353 SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');
355 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
356 do_test collate2-2.23 {
358 SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');
360 } {ab ba aB bA Ab Ba AB BA}
361 do_test collate2-2.24 {
363 SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');
365 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
366 do_test collate2-2.25 {
368 SELECT a FROM collate2t1
369 WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
371 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
372 do_test collate2-2.26 {
374 SELECT b FROM collate2t1
375 WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
377 } {ab ba aB bA Ab Ba AB BA}
378 do_test collate2-2.27 {
380 SELECT c FROM collate2t1
381 WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
383 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
386 do_test collate2-3.1 {
388 SELECT a > 'aa' FROM collate2t1;
390 } {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
391 do_test collate2-3.2 {
393 SELECT b > 'aa' FROM collate2t1;
395 } {{} 0 1 1 1 0 1 1 1 0 1 1 1 0 1 1 1}
396 do_test collate2-3.3 {
398 SELECT c > 'aa' FROM collate2t1;
400 } {{} 0 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
401 do_test collate2-3.4 {
403 SELECT a < 'aa' FROM collate2t1;
405 } {{} 0 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
406 do_test collate2-3.5 {
408 SELECT b < 'aa' FROM collate2t1;
410 } {{} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
411 do_test collate2-3.6 {
413 SELECT c < 'aa' FROM collate2t1;
415 } {{} 0 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
416 do_test collate2-3.7 {
418 SELECT a = 'aa' FROM collate2t1;
420 } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
421 do_test collate2-3.8 {
423 SELECT b = 'aa' FROM collate2t1;
425 } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
426 do_test collate2-3.9 {
428 SELECT c = 'aa' FROM collate2t1;
430 } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
431 do_test collate2-3.10 {
433 SELECT a <= 'aa' FROM collate2t1;
435 } {{} 1 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
436 do_test collate2-3.11 {
438 SELECT b <= 'aa' FROM collate2t1;
440 } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
441 do_test collate2-3.12 {
443 SELECT c <= 'aa' FROM collate2t1;
445 } {{} 1 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
446 do_test collate2-3.13 {
448 SELECT a >= 'aa' FROM collate2t1;
450 } {{} 1 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
451 do_test collate2-3.14 {
453 SELECT b >= 'aa' FROM collate2t1;
455 } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
456 do_test collate2-3.15 {
458 SELECT c >= 'aa' FROM collate2t1;
460 } {{} 1 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
461 do_test collate2-3.16 {
463 SELECT a BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
465 } {{} 0 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1}
466 do_test collate2-3.17 {
468 SELECT b BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
470 } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
471 do_test collate2-3.18 {
473 SELECT c BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
475 } {{} 1 0 1 0 0 0 0 0 1 1 1 1 0 0 0 0}
476 do_test collate2-3.19 {
478 SELECT CASE a WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
480 } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
481 do_test collate2-3.20 {
483 SELECT CASE b WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
485 } {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
486 do_test collate2-3.21 {
488 SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
490 } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
493 do_test collate2-3.22 {
495 SELECT a IN ('aa', 'bb') FROM collate2t1;
497 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
498 do_test collate2-3.23 {
500 SELECT b IN ('aa', 'bb') FROM collate2t1;
502 } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
503 do_test collate2-3.24 {
505 SELECT c IN ('aa', 'bb') FROM collate2t1;
507 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
508 do_test collate2-3.25 {
510 SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
513 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
514 do_test collate2-3.26 {
516 SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
519 } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
520 do_test collate2-3.27 {
522 SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
525 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
528 do_test collate2-4.0 {
530 CREATE TABLE collate2t2(b COLLATE binary);
531 CREATE TABLE collate2t3(b text);
532 INSERT INTO collate2t2 VALUES('aa');
533 INSERT INTO collate2t3 VALUES('aa');
537 # Test that when both sides of a binary comparison operator have
538 # default collation types, the collate type for the leftmost term
540 do_test collate2-4.1 {
542 SELECT collate2t1.a FROM collate2t1, collate2t2
543 WHERE collate2t1.b = collate2t2.b;
546 do_test collate2-4.2 {
548 SELECT collate2t1.a FROM collate2t1, collate2t2
549 WHERE collate2t2.b = collate2t1.b;
553 # Test that when one side has a default collation type and the other
554 # does not, the collation type is used.
555 do_test collate2-4.3 {
557 SELECT collate2t1.a FROM collate2t1, collate2t3
558 WHERE collate2t1.b = collate2t3.b||'';
561 do_test collate2-4.4 {
563 SELECT collate2t1.a FROM collate2t1, collate2t3
564 WHERE collate2t3.b||'' = collate2t1.b;
568 do_test collate2-4.5 {
570 DROP TABLE collate2t3;
575 # Test that the default collation types are used when the JOIN syntax
576 # is used in place of a WHERE clause.
578 # SQLite transforms the JOIN syntax into a WHERE clause internally, so
579 # the focus of these tests is to ensure that the table on the left-hand-side
580 # of the join determines the collation type used.
582 do_test collate2-5.0 {
584 SELECT collate2t1.b FROM collate2t1 JOIN collate2t2 USING (b);
587 do_test collate2-5.1 {
589 SELECT collate2t1.b FROM collate2t2 JOIN collate2t1 USING (b);
592 do_test collate2-5.2 {
594 SELECT collate2t1.b FROM collate2t1 NATURAL JOIN collate2t2;
597 do_test collate2-5.3 {
599 SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
602 do_test collate2-5.4 {
604 SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
606 } {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
607 do_test collate2-5.5 {
609 SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);