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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test