wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 script is page cache subsystem.
14 #
15 # $Id: collate2.test,v 1.6 2008/08/20 16:35:10 drh Exp $
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 #
21 # Tests are organised as follows:
22 #
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.
28 #
29  
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} {
35 set ra {};
36 set rb {}
37 foreach c [split $a {}] { set ra $c$ra }
38 foreach c [split $b {}] { set rb $c$rb }
39 return [string compare $ra $rb]
40 }
41  
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
44 #
45 # The collation orders for each of the tested collation types are:
46 #
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
50 #
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.
54 #
55 # The collation sequences BINARY and NOCASE are built-in, the BACKWARDS
56 # collation sequence is implemented by the TCL proc backwards_collate
57 # above.
58 #
59 do_test collate2-1.0 {
60 execsql {
61 CREATE TABLE collate2t1(
62 a COLLATE BINARY,
63 b COLLATE NOCASE,
64 c COLLATE BACKWARDS
65 );
66 INSERT INTO collate2t1 VALUES( NULL, NULL, NULL );
67  
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' );
72  
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' );
77  
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' );
82  
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' );
87 }
88 if {[info exists collate_test_use_index]} {
89 execsql {
90 CREATE INDEX collate2t1_i1 ON collate2t1(a);
91 CREATE INDEX collate2t1_i2 ON collate2t1(b);
92 CREATE INDEX collate2t1_i3 ON collate2t1(c);
93 }
94 }
95 } {}
96 do_test collate2-1.1 {
97 execsql {
98 SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;
99 }
100 } {ab bA bB ba bb}
101 do_test collate2-1.1.1 {
102 execsql {
103 SELECT a FROM collate2t1 WHERE a COLLATE binary > 'aa' ORDER BY 1;
104 }
105 } {ab bA bB ba bb}
106 do_test collate2-1.1.2 {
107 execsql {
108 SELECT a FROM collate2t1 WHERE b COLLATE binary > 'aa' ORDER BY 1;
109 }
110 } {ab bA bB ba bb}
111 do_test collate2-1.1.3 {
112 execsql {
113 SELECT a FROM collate2t1 WHERE c COLLATE binary > 'aa' ORDER BY 1;
114 }
115 } {ab bA bB ba bb}
116 do_test collate2-1.2 {
117 execsql {
118 SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid;
119 }
120 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
121 do_test collate2-1.2.1 {
122 execsql {
123 SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa'
124 ORDER BY 1, oid;
125 }
126 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
127 do_test collate2-1.2.2 {
128 execsql {
129 SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa'
130 ORDER BY 1, oid;
131 }
132 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
133 do_test collate2-1.2.3 {
134 execsql {
135 SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa'
136 ORDER BY 1, oid;
137 }
138 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
139 do_test collate2-1.2.4 {
140 execsql {
141 SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY +b;
142 }
143 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
144 do_test collate2-1.2.5 {
145 execsql {
146 SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa' ORDER BY +b;
147 }
148 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
149 do_test collate2-1.2.6 {
150 execsql {
151 SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa' ORDER BY +b;
152 }
153 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
154 do_test collate2-1.2.7 {
155 execsql {
156 SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa' ORDER BY +b;
157 }
158 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
159 do_test collate2-1.3 {
160 execsql {
161 SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;
162 }
163 } {ba Ab Bb ab bb}
164 do_test collate2-1.3.1 {
165 execsql {
166 SELECT c FROM collate2t1 WHERE a COLLATE backwards > 'aa'
167 ORDER BY 1;
168 }
169 } {ba Ab Bb ab bb}
170 do_test collate2-1.3.2 {
171 execsql {
172 SELECT c FROM collate2t1 WHERE b COLLATE backwards > 'aa'
173 ORDER BY 1;
174 }
175 } {ba Ab Bb ab bb}
176 do_test collate2-1.3.3 {
177 execsql {
178 SELECT c FROM collate2t1 WHERE c COLLATE backwards > 'aa'
179 ORDER BY 1;
180 }
181 } {ba Ab Bb ab bb}
182 do_test collate2-1.4 {
183 execsql {
184 SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1;
185 }
186 } {AA AB Aa Ab BA BB Ba Bb aA aB}
187 do_test collate2-1.5 {
188 execsql {
189 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid;
190 }
191 } {}
192 do_test collate2-1.5.1 {
193 execsql {
194 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY +b;
195 }
196 } {}
197 do_test collate2-1.6 {
198 execsql {
199 SELECT c FROM collate2t1 WHERE c < 'aa' ORDER BY 1;
200 }
201 } {AA BA aA bA AB BB aB bB Aa Ba}
202 do_test collate2-1.7 {
203 execsql {
204 SELECT a FROM collate2t1 WHERE a = 'aa';
205 }
206 } {aa}
207 do_test collate2-1.8 {
208 execsql {
209 SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid;
210 }
211 } {aa aA Aa AA}
212 do_test collate2-1.9 {
213 execsql {
214 SELECT c FROM collate2t1 WHERE c = 'aa';
215 }
216 } {aa}
217 do_test collate2-1.10 {
218 execsql {
219 SELECT a FROM collate2t1 WHERE a >= 'aa' ORDER BY 1;
220 }
221 } {aa ab bA bB ba bb}
222 do_test collate2-1.11 {
223 execsql {
224 SELECT b FROM collate2t1 WHERE b >= 'aa' ORDER BY 1, oid;
225 }
226 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
227 do_test collate2-1.12 {
228 execsql {
229 SELECT c FROM collate2t1 WHERE c >= 'aa' ORDER BY 1;
230 }
231 } {aa ba Ab Bb ab bb}
232 do_test collate2-1.13 {
233 execsql {
234 SELECT a FROM collate2t1 WHERE a <= 'aa' ORDER BY 1;
235 }
236 } {AA AB Aa Ab BA BB Ba Bb aA aB aa}
237 do_test collate2-1.14 {
238 execsql {
239 SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid;
240 }
241 } {aa aA Aa AA}
242 do_test collate2-1.15 {
243 execsql {
244 SELECT c FROM collate2t1 WHERE c <= 'aa' ORDER BY 1;
245 }
246 } {AA BA aA bA AB BB aB bB Aa Ba aa}
247 do_test collate2-1.16 {
248 execsql {
249 SELECT a FROM collate2t1 WHERE a BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
250 }
251 } {Aa Ab BA BB Ba Bb}
252 do_test collate2-1.17 {
253 execsql {
254 SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
255 }
256 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
257 do_test collate2-1.17.1 {
258 execsql {
259 SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY +b;
260 }
261 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
262 do_test collate2-1.18 {
263 execsql {
264 SELECT c FROM collate2t1 WHERE c BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
265 }
266 } {Aa Ba aa ba Ab Bb}
267 do_test collate2-1.19 {
268 execsql {
269 SELECT a FROM collate2t1 WHERE
270 CASE a WHEN 'aa' THEN 1 ELSE 0 END
271 ORDER BY 1, oid;
272 }
273 } {aa}
274 do_test collate2-1.20 {
275 execsql {
276 SELECT b FROM collate2t1 WHERE
277 CASE b WHEN 'aa' THEN 1 ELSE 0 END
278 ORDER BY 1, oid;
279 }
280 } {aa aA Aa AA}
281 do_test collate2-1.21 {
282 execsql {
283 SELECT c FROM collate2t1 WHERE
284 CASE c WHEN 'aa' THEN 1 ELSE 0 END
285 ORDER BY 1, oid;
286 }
287 } {aa}
288  
289 ifcapable subquery {
290 do_test collate2-1.22 {
291 execsql {
292 SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;
293 }
294 } {aa bb}
295 do_test collate2-1.23 {
296 execsql {
297 SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;
298 }
299 } {aa aA Aa AA bb bB Bb BB}
300 do_test collate2-1.24 {
301 execsql {
302 SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;
303 }
304 } {aa bb}
305 do_test collate2-1.25 {
306 execsql {
307 SELECT a FROM collate2t1
308 WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
309 }
310 } {aa bb}
311 do_test collate2-1.26 {
312 execsql {
313 SELECT b FROM collate2t1
314 WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
315 }
316 } {aa bb aA bB Aa Bb AA BB}
317 do_test collate2-1.27 {
318 execsql {
319 SELECT c FROM collate2t1
320 WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
321 }
322 } {aa bb}
323 } ;# ifcapable subquery
324  
325 do_test collate2-2.1 {
326 execsql {
327 SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;
328 }
329 } {AA AB Aa Ab BA BB Ba Bb aA aB aa}
330 do_test collate2-2.2 {
331 execsql {
332 SELECT b FROM collate2t1 WHERE NOT b > 'aa' ORDER BY 1, oid;
333 }
334 } {aa aA Aa AA}
335 do_test collate2-2.3 {
336 execsql {
337 SELECT c FROM collate2t1 WHERE NOT c > 'aa' ORDER BY 1;
338 }
339 } {AA BA aA bA AB BB aB bB Aa Ba aa}
340 do_test collate2-2.4 {
341 execsql {
342 SELECT a FROM collate2t1 WHERE NOT a < 'aa' ORDER BY 1;
343 }
344 } {aa ab bA bB ba bb}
345 do_test collate2-2.5 {
346 execsql {
347 SELECT b FROM collate2t1 WHERE NOT b < 'aa' ORDER BY 1, oid;
348 }
349 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
350 do_test collate2-2.6 {
351 execsql {
352 SELECT c FROM collate2t1 WHERE NOT c < 'aa' ORDER BY 1;
353 }
354 } {aa ba Ab Bb ab bb}
355 do_test collate2-2.7 {
356 execsql {
357 SELECT a FROM collate2t1 WHERE NOT a = 'aa';
358 }
359 } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
360 do_test collate2-2.8 {
361 execsql {
362 SELECT b FROM collate2t1 WHERE NOT b = 'aa';
363 }
364 } {ab ba bb aB bA bB Ab Ba Bb AB BA BB}
365 do_test collate2-2.9 {
366 execsql {
367 SELECT c FROM collate2t1 WHERE NOT c = 'aa';
368 }
369 } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
370 do_test collate2-2.10 {
371 execsql {
372 SELECT a FROM collate2t1 WHERE NOT a >= 'aa' ORDER BY 1;
373 }
374 } {AA AB Aa Ab BA BB Ba Bb aA aB}
375 do_test collate2-2.11 {
376 execsql {
377 SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid;
378 }
379 } {}
380 do_test collate2-2.12 {
381 execsql {
382 SELECT c FROM collate2t1 WHERE NOT c >= 'aa' ORDER BY 1;
383 }
384 } {AA BA aA bA AB BB aB bB Aa Ba}
385 do_test collate2-2.13 {
386 execsql {
387 SELECT a FROM collate2t1 WHERE NOT a <= 'aa' ORDER BY 1;
388 }
389 } {ab bA bB ba bb}
390 do_test collate2-2.14 {
391 execsql {
392 SELECT b FROM collate2t1 WHERE NOT b <= 'aa' ORDER BY 1, oid;
393 }
394 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
395 do_test collate2-2.15 {
396 execsql {
397 SELECT c FROM collate2t1 WHERE NOT c <= 'aa' ORDER BY 1;
398 }
399 } {ba Ab Bb ab bb}
400 do_test collate2-2.16 {
401 execsql {
402 SELECT a FROM collate2t1 WHERE a NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
403 }
404 } {AA AB aA aB aa ab bA bB ba bb}
405 do_test collate2-2.17 {
406 execsql {
407 SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
408 }
409 } {}
410 do_test collate2-2.18 {
411 execsql {
412 SELECT c FROM collate2t1 WHERE c NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
413 }
414 } {AA BA aA bA AB BB aB bB ab bb}
415 do_test collate2-2.19 {
416 execsql {
417 SELECT a FROM collate2t1 WHERE NOT CASE a WHEN 'aa' THEN 1 ELSE 0 END;
418 }
419 } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
420 do_test collate2-2.20 {
421 execsql {
422 SELECT b FROM collate2t1 WHERE NOT CASE b WHEN 'aa' THEN 1 ELSE 0 END;
423 }
424 } {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}
425 do_test collate2-2.21 {
426 execsql {
427 SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;
428 }
429 } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
430  
431 ifcapable subquery {
432 do_test collate2-2.22 {
433 execsql {
434 SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');
435 }
436 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
437 do_test collate2-2.23 {
438 execsql {
439 SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');
440 }
441 } {ab ba aB bA Ab Ba AB BA}
442 do_test collate2-2.24 {
443 execsql {
444 SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');
445 }
446 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
447 do_test collate2-2.25 {
448 execsql {
449 SELECT a FROM collate2t1
450 WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
451 }
452 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
453 do_test collate2-2.26 {
454 execsql {
455 SELECT b FROM collate2t1
456 WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
457 }
458 } {ab ba aB bA Ab Ba AB BA}
459 do_test collate2-2.27 {
460 execsql {
461 SELECT c FROM collate2t1
462 WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
463 }
464 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
465 }
466  
467 do_test collate2-3.1 {
468 execsql {
469 SELECT a > 'aa' FROM collate2t1;
470 }
471 } {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
472 do_test collate2-3.2 {
473 execsql {
474 SELECT b > 'aa' FROM collate2t1;
475 }
476 } {{} 0 1 1 1 0 1 1 1 0 1 1 1 0 1 1 1}
477 do_test collate2-3.3 {
478 execsql {
479 SELECT c > 'aa' FROM collate2t1;
480 }
481 } {{} 0 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
482 do_test collate2-3.4 {
483 execsql {
484 SELECT a < 'aa' FROM collate2t1;
485 }
486 } {{} 0 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
487 do_test collate2-3.5 {
488 execsql {
489 SELECT b < 'aa' FROM collate2t1;
490 }
491 } {{} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
492 do_test collate2-3.6 {
493 execsql {
494 SELECT c < 'aa' FROM collate2t1;
495 }
496 } {{} 0 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
497 do_test collate2-3.7 {
498 execsql {
499 SELECT a = 'aa' FROM collate2t1;
500 }
501 } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
502 do_test collate2-3.8 {
503 execsql {
504 SELECT b = 'aa' FROM collate2t1;
505 }
506 } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
507 do_test collate2-3.9 {
508 execsql {
509 SELECT c = 'aa' FROM collate2t1;
510 }
511 } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
512 do_test collate2-3.10 {
513 execsql {
514 SELECT a <= 'aa' FROM collate2t1;
515 }
516 } {{} 1 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
517 do_test collate2-3.11 {
518 execsql {
519 SELECT b <= 'aa' FROM collate2t1;
520 }
521 } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
522 do_test collate2-3.12 {
523 execsql {
524 SELECT c <= 'aa' FROM collate2t1;
525 }
526 } {{} 1 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
527 do_test collate2-3.13 {
528 execsql {
529 SELECT a >= 'aa' FROM collate2t1;
530 }
531 } {{} 1 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
532 do_test collate2-3.14 {
533 execsql {
534 SELECT b >= 'aa' FROM collate2t1;
535 }
536 } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
537 do_test collate2-3.15 {
538 execsql {
539 SELECT c >= 'aa' FROM collate2t1;
540 }
541 } {{} 1 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
542 do_test collate2-3.16 {
543 execsql {
544 SELECT a BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
545 }
546 } {{} 0 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1}
547 do_test collate2-3.17 {
548 execsql {
549 SELECT b BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
550 }
551 } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
552 do_test collate2-3.18 {
553 execsql {
554 SELECT c BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
555 }
556 } {{} 1 0 1 0 0 0 0 0 1 1 1 1 0 0 0 0}
557 do_test collate2-3.19 {
558 execsql {
559 SELECT CASE a WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
560 }
561 } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
562 do_test collate2-3.20 {
563 execsql {
564 SELECT CASE b WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
565 }
566 } {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
567 do_test collate2-3.21 {
568 execsql {
569 SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
570 }
571 } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
572  
573 ifcapable subquery {
574 do_test collate2-3.22 {
575 execsql {
576 SELECT a IN ('aa', 'bb') FROM collate2t1;
577 }
578 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
579 do_test collate2-3.23 {
580 execsql {
581 SELECT b IN ('aa', 'bb') FROM collate2t1;
582 }
583 } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
584 do_test collate2-3.24 {
585 execsql {
586 SELECT c IN ('aa', 'bb') FROM collate2t1;
587 }
588 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
589 do_test collate2-3.25 {
590 execsql {
591 SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
592 FROM collate2t1;
593 }
594 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
595 do_test collate2-3.26 {
596 execsql {
597 SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
598 FROM collate2t1;
599 }
600 } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
601 do_test collate2-3.27 {
602 execsql {
603 SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
604 FROM collate2t1;
605 }
606 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
607 }
608  
609 do_test collate2-4.0 {
610 execsql {
611 CREATE TABLE collate2t2(b COLLATE binary);
612 CREATE TABLE collate2t3(b text);
613 INSERT INTO collate2t2 VALUES('aa');
614 INSERT INTO collate2t3 VALUES('aa');
615 }
616 } {}
617  
618 # Test that when both sides of a binary comparison operator have
619 # default collation types, the collate type for the leftmost term
620 # is used.
621 do_test collate2-4.1 {
622 execsql {
623 SELECT collate2t1.a FROM collate2t1, collate2t2
624 WHERE collate2t1.b = collate2t2.b;
625 }
626 } {aa aA Aa AA}
627 do_test collate2-4.2 {
628 execsql {
629 SELECT collate2t1.a FROM collate2t1, collate2t2
630 WHERE collate2t2.b = collate2t1.b;
631 }
632 } {aa}
633  
634 # Test that when one side has a default collation type and the other
635 # does not, the collation type is used.
636 do_test collate2-4.3 {
637 execsql {
638 SELECT collate2t1.a FROM collate2t1, collate2t3
639 WHERE collate2t1.b = collate2t3.b||'';
640 }
641 } {aa aA Aa AA}
642 do_test collate2-4.4 {
643 execsql {
644 SELECT collate2t1.a FROM collate2t1, collate2t3
645 WHERE collate2t3.b||'' = collate2t1.b;
646 }
647 } {aa aA Aa AA}
648  
649 do_test collate2-4.5 {
650 execsql {
651 DROP TABLE collate2t3;
652 }
653 } {}
654  
655 #
656 # Test that the default collation types are used when the JOIN syntax
657 # is used in place of a WHERE clause.
658 #
659 # SQLite transforms the JOIN syntax into a WHERE clause internally, so
660 # the focus of these tests is to ensure that the table on the left-hand-side
661 # of the join determines the collation type used.
662 #
663 do_test collate2-5.0 {
664 execsql {
665 SELECT collate2t1.b FROM collate2t1 JOIN collate2t2 USING (b);
666 }
667 } {aa aA Aa AA}
668 do_test collate2-5.1 {
669 execsql {
670 SELECT collate2t1.b FROM collate2t2 JOIN collate2t1 USING (b);
671 }
672 } {aa}
673 do_test collate2-5.2 {
674 execsql {
675 SELECT collate2t1.b FROM collate2t1 NATURAL JOIN collate2t2;
676 }
677 } {aa aA Aa AA}
678 do_test collate2-5.3 {
679 execsql {
680 SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
681 }
682 } {aa}
683 do_test collate2-5.4 {
684 execsql {
685 SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
686 }
687 } {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
688 do_test collate2-5.5 {
689 execsql {
690 SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);
691 }
692 } {aa aa}
693  
694 finish_test