Annotation of embedaddon/sqlite3/test/selectA.test, revision 1.1.1.1
1.1 misho 1: # 2008 June 24
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library.
12: #
13: # The focus of this file is testing the compound-SELECT merge
14: # optimization. Or, in other words, making sure that all
15: # possible combinations of UNION, UNION ALL, EXCEPT, and
16: # INTERSECT work together with an ORDER BY clause (with or w/o
17: # explicit sort order and explicit collating secquites) and
18: # with and without optional LIMIT and OFFSET clauses.
19: #
20: # $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $
21:
22: set testdir [file dirname $argv0]
23: source $testdir/tester.tcl
24:
25: ifcapable !compound {
26: finish_test
27: return
28: }
29:
30: do_test selectA-1.0 {
31: execsql {
32: CREATE TABLE t1(a,b,c COLLATE NOCASE);
33: INSERT INTO t1 VALUES(1,'a','a');
34: INSERT INTO t1 VALUES(9.9, 'b', 'B');
35: INSERT INTO t1 VALUES(NULL, 'C', 'c');
36: INSERT INTO t1 VALUES('hello', 'd', 'D');
37: INSERT INTO t1 VALUES(x'616263', 'e', 'e');
38: SELECT * FROM t1;
39: }
40: } {1 a a 9.9 b B {} C c hello d D abc e e}
41: do_test selectA-1.1 {
42: execsql {
43: CREATE TABLE t2(x,y,z COLLATE NOCASE);
44: INSERT INTO t2 VALUES(NULL,'U','u');
45: INSERT INTO t2 VALUES('mad', 'Z', 'z');
46: INSERT INTO t2 VALUES(x'68617265', 'm', 'M');
47: INSERT INTO t2 VALUES(5.2e6, 'X', 'x');
48: INSERT INTO t2 VALUES(-23, 'Y', 'y');
49: SELECT * FROM t2;
50: }
51: } {{} U u mad Z z hare m M 5200000.0 X x -23 Y y}
52: do_test selectA-1.2 {
53: execsql {
54: CREATE TABLE t3(a,b,c COLLATE NOCASE);
55: INSERT INTO t3 SELECT * FROM t1;
56: INSERT INTO t3 SELECT * FROM t2;
57: INSERT INTO t3 SELECT * FROM t1;
58: INSERT INTO t3 SELECT * FROM t2;
59: INSERT INTO t3 SELECT * FROM t1;
60: INSERT INTO t3 SELECT * FROM t2;
61: SELECT count(*) FROM t3;
62: }
63: } {30}
64:
65: do_test selectA-2.1 {
66: execsql {
67: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
68: ORDER BY a,b,c
69: }
70: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
71: do_test selectA-2.1.1 { # Ticket #3314
72: execsql {
73: SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2
74: ORDER BY a,b,c
75: }
76: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
77: do_test selectA-2.1.2 { # Ticket #3314
78: execsql {
79: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
80: ORDER BY t1.a, t1.b, t1.c
81: }
82: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
83: do_test selectA-2.2 {
84: execsql {
85: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
86: ORDER BY a DESC,b,c
87: }
88: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
89: do_test selectA-2.3 {
90: execsql {
91: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
92: ORDER BY a,c,b
93: }
94: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
95: do_test selectA-2.4 {
96: execsql {
97: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
98: ORDER BY b,a,c
99: }
100: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
101: do_test selectA-2.5 {
102: execsql {
103: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
104: ORDER BY b COLLATE NOCASE,a,c
105: }
106: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
107: do_test selectA-2.6 {
108: execsql {
109: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
110: ORDER BY b COLLATE NOCASE DESC,a,c
111: }
112: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
113: do_test selectA-2.7 {
114: execsql {
115: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
116: ORDER BY c,b,a
117: }
118: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
119: do_test selectA-2.8 {
120: execsql {
121: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
122: ORDER BY c,a,b
123: }
124: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
125: do_test selectA-2.9 {
126: execsql {
127: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
128: ORDER BY c DESC,a,b
129: }
130: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
131: do_test selectA-2.10 {
132: execsql {
133: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
134: ORDER BY c COLLATE BINARY DESC,a,b
135: }
136: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
137: do_test selectA-2.11 {
138: execsql {
139: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
140: ORDER BY a,b,c
141: }
142: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
143: do_test selectA-2.12 {
144: execsql {
145: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
146: ORDER BY a DESC,b,c
147: }
148: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
149: do_test selectA-2.13 {
150: execsql {
151: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
152: ORDER BY a,c,b
153: }
154: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
155: do_test selectA-2.14 {
156: execsql {
157: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
158: ORDER BY b,a,c
159: }
160: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
161: do_test selectA-2.15 {
162: execsql {
163: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
164: ORDER BY b COLLATE NOCASE,a,c
165: }
166: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
167: do_test selectA-2.16 {
168: execsql {
169: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
170: ORDER BY b COLLATE NOCASE DESC,a,c
171: }
172: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
173: do_test selectA-2.17 {
174: execsql {
175: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
176: ORDER BY c,b,a
177: }
178: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
179: do_test selectA-2.18 {
180: execsql {
181: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
182: ORDER BY c,a,b
183: }
184: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
185: do_test selectA-2.19 {
186: execsql {
187: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
188: ORDER BY c DESC,a,b
189: }
190: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
191: do_test selectA-2.20 {
192: execsql {
193: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
194: ORDER BY c COLLATE BINARY DESC,a,b
195: }
196: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
197: do_test selectA-2.21 {
198: execsql {
199: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
200: ORDER BY a,b,c
201: }
202: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
203: do_test selectA-2.22 {
204: execsql {
205: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
206: ORDER BY a DESC,b,c
207: }
208: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
209: do_test selectA-2.23 {
210: execsql {
211: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
212: ORDER BY a,c,b
213: }
214: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
215: do_test selectA-2.24 {
216: execsql {
217: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
218: ORDER BY b,a,c
219: }
220: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
221: do_test selectA-2.25 {
222: execsql {
223: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
224: ORDER BY b COLLATE NOCASE,a,c
225: }
226: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
227: do_test selectA-2.26 {
228: execsql {
229: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
230: ORDER BY b COLLATE NOCASE DESC,a,c
231: }
232: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
233: do_test selectA-2.27 {
234: execsql {
235: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
236: ORDER BY c,b,a
237: }
238: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
239: do_test selectA-2.28 {
240: execsql {
241: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
242: ORDER BY c,a,b
243: }
244: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
245: do_test selectA-2.29 {
246: execsql {
247: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
248: ORDER BY c DESC,a,b
249: }
250: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
251: do_test selectA-2.30 {
252: execsql {
253: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
254: ORDER BY c COLLATE BINARY DESC,a,b
255: }
256: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
257: do_test selectA-2.31 {
258: execsql {
259: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
260: ORDER BY a,b,c
261: }
262: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
263: do_test selectA-2.32 {
264: execsql {
265: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
266: ORDER BY a DESC,b,c
267: }
268: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
269: do_test selectA-2.33 {
270: execsql {
271: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
272: ORDER BY a,c,b
273: }
274: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
275: do_test selectA-2.34 {
276: execsql {
277: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
278: ORDER BY b,a,c
279: }
280: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
281: do_test selectA-2.35 {
282: execsql {
283: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
284: ORDER BY b COLLATE NOCASE,a,c
285: }
286: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
287: do_test selectA-2.36 {
288: execsql {
289: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
290: ORDER BY b COLLATE NOCASE DESC,a,c
291: }
292: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
293: do_test selectA-2.37 {
294: execsql {
295: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
296: ORDER BY c,b,a
297: }
298: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
299: do_test selectA-2.38 {
300: execsql {
301: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
302: ORDER BY c,a,b
303: }
304: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
305: do_test selectA-2.39 {
306: execsql {
307: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
308: ORDER BY c DESC,a,b
309: }
310: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
311: do_test selectA-2.40 {
312: execsql {
313: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
314: ORDER BY c COLLATE BINARY DESC,a,b
315: }
316: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
317: do_test selectA-2.41 {
318: execsql {
319: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
320: ORDER BY a,b,c
321: }
322: } {{} C c 1 a a 9.9 b B}
323: do_test selectA-2.42 {
324: execsql {
325: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
326: ORDER BY a,b,c
327: }
328: } {hello d D abc e e}
329: do_test selectA-2.43 {
330: execsql {
331: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
332: ORDER BY a,b,c
333: }
334: } {hello d D abc e e}
335: do_test selectA-2.44 {
336: execsql {
337: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
338: ORDER BY a,b,c
339: }
340: } {hello d D abc e e}
341: do_test selectA-2.45 {
342: execsql {
343: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
344: ORDER BY a,b,c
345: }
346: } {{} C c 1 a a 9.9 b B}
347: do_test selectA-2.46 {
348: execsql {
349: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
350: ORDER BY a,b,c
351: }
352: } {{} C c 1 a a 9.9 b B}
353: do_test selectA-2.47 {
354: execsql {
355: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
356: ORDER BY a DESC
357: }
358: } {9.9 b B 1 a a {} C c}
359: do_test selectA-2.48 {
360: execsql {
361: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
362: ORDER BY a DESC
363: }
364: } {abc e e hello d D}
365: do_test selectA-2.49 {
366: execsql {
367: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
368: ORDER BY a DESC
369: }
370: } {abc e e hello d D}
371: do_test selectA-2.50 {
372: execsql {
373: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
374: ORDER BY a DESC
375: }
376: } {abc e e hello d D}
377: do_test selectA-2.51 {
378: execsql {
379: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
380: ORDER BY a DESC
381: }
382: } {9.9 b B 1 a a {} C c}
383: do_test selectA-2.52 {
384: execsql {
385: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
386: ORDER BY a DESC
387: }
388: } {9.9 b B 1 a a {} C c}
389: do_test selectA-2.53 {
390: execsql {
391: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
392: ORDER BY b, a DESC
393: }
394: } {{} C c 1 a a 9.9 b B}
395: do_test selectA-2.54 {
396: execsql {
397: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
398: ORDER BY b
399: }
400: } {hello d D abc e e}
401: do_test selectA-2.55 {
402: execsql {
403: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
404: ORDER BY b DESC, c
405: }
406: } {abc e e hello d D}
407: do_test selectA-2.56 {
408: execsql {
409: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
410: ORDER BY b, c DESC, a
411: }
412: } {hello d D abc e e}
413: do_test selectA-2.57 {
414: execsql {
415: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
416: ORDER BY b COLLATE NOCASE
417: }
418: } {1 a a 9.9 b B {} C c}
419: do_test selectA-2.58 {
420: execsql {
421: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
422: ORDER BY b
423: }
424: } {{} C c 1 a a 9.9 b B}
425: do_test selectA-2.59 {
426: execsql {
427: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
428: ORDER BY c, a DESC
429: }
430: } {1 a a 9.9 b B {} C c}
431: do_test selectA-2.60 {
432: execsql {
433: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
434: ORDER BY c
435: }
436: } {hello d D abc e e}
437: do_test selectA-2.61 {
438: execsql {
439: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
440: ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c
441: }
442: } {hello d D abc e e}
443: do_test selectA-2.62 {
444: execsql {
445: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
446: ORDER BY c DESC, a
447: }
448: } {abc e e hello d D}
449: do_test selectA-2.63 {
450: execsql {
451: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
452: ORDER BY c COLLATE NOCASE
453: }
454: } {1 a a 9.9 b B {} C c}
455: do_test selectA-2.64 {
456: execsql {
457: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
458: ORDER BY c
459: }
460: } {1 a a 9.9 b B {} C c}
461: do_test selectA-2.65 {
462: execsql {
463: SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
464: ORDER BY c COLLATE NOCASE
465: }
466: } {1 a a 9.9 b B {} C c}
467: do_test selectA-2.66 {
468: execsql {
469: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3
470: ORDER BY c
471: }
472: } {1 a a 9.9 b B {} C c}
473: do_test selectA-2.67 {
474: execsql {
475: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'
476: ORDER BY c DESC, a
477: }
478: } {abc e e hello d D}
479: do_test selectA-2.68 {
480: execsql {
481: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
482: INTERSECT SELECT a,b,c FROM t3
483: EXCEPT SELECT b,c,a FROM t3
484: ORDER BY c DESC, a
485: }
486: } {abc e e hello d D}
487: do_test selectA-2.69 {
488: execsql {
489: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
490: INTERSECT SELECT a,b,c FROM t3
491: EXCEPT SELECT b,c,a FROM t3
492: ORDER BY c COLLATE NOCASE
493: }
494: } {1 a a 9.9 b B {} C c}
495: do_test selectA-2.70 {
496: execsql {
497: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
498: INTERSECT SELECT a,b,c FROM t3
499: EXCEPT SELECT b,c,a FROM t3
500: ORDER BY c
501: }
502: } {1 a a 9.9 b B {} C c}
503: do_test selectA-2.71 {
504: execsql {
505: SELECT a,b,c FROM t1 WHERE b<'d'
506: INTERSECT SELECT a,b,c FROM t1
507: INTERSECT SELECT a,b,c FROM t3
508: EXCEPT SELECT b,c,a FROM t3
509: INTERSECT SELECT a,b,c FROM t1
510: EXCEPT SELECT x,y,z FROM t2
511: INTERSECT SELECT a,b,c FROM t3
512: EXCEPT SELECT y,x,z FROM t2
513: INTERSECT SELECT a,b,c FROM t1
514: EXCEPT SELECT c,b,a FROM t3
515: ORDER BY c
516: }
517: } {1 a a 9.9 b B {} C c}
518: do_test selectA-2.72 {
519: execsql {
520: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
521: ORDER BY a,b,c
522: }
523: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
524: do_test selectA-2.73 {
525: execsql {
526: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
527: ORDER BY a DESC,b,c
528: }
529: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
530: do_test selectA-2.74 {
531: execsql {
532: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
533: ORDER BY a,c,b
534: }
535: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
536: do_test selectA-2.75 {
537: execsql {
538: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
539: ORDER BY b,a,c
540: }
541: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
542: do_test selectA-2.76 {
543: execsql {
544: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
545: ORDER BY b COLLATE NOCASE,a,c
546: }
547: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
548: do_test selectA-2.77 {
549: execsql {
550: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
551: ORDER BY b COLLATE NOCASE DESC,a,c
552: }
553: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
554: do_test selectA-2.78 {
555: execsql {
556: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
557: ORDER BY c,b,a
558: }
559: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
560: do_test selectA-2.79 {
561: execsql {
562: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
563: ORDER BY c,a,b
564: }
565: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
566: do_test selectA-2.80 {
567: execsql {
568: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
569: ORDER BY c DESC,a,b
570: }
571: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
572: do_test selectA-2.81 {
573: execsql {
574: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
575: ORDER BY c COLLATE BINARY DESC,a,b
576: }
577: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
578: do_test selectA-2.82 {
579: execsql {
580: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
581: ORDER BY a,b,c
582: }
583: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
584: do_test selectA-2.83 {
585: execsql {
586: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
587: ORDER BY a DESC,b,c
588: }
589: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
590: do_test selectA-2.84 {
591: execsql {
592: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
593: ORDER BY a,c,b
594: }
595: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
596: do_test selectA-2.85 {
597: execsql {
598: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
599: ORDER BY b,a,c
600: }
601: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
602: do_test selectA-2.86 {
603: execsql {
604: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
605: ORDER BY b COLLATE NOCASE,a,c
606: }
607: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
608: do_test selectA-2.87 {
609: execsql {
610: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
611: ORDER BY y COLLATE NOCASE DESC,x,z
612: }
613: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
614: do_test selectA-2.88 {
615: execsql {
616: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
617: ORDER BY c,b,a
618: }
619: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
620: do_test selectA-2.89 {
621: execsql {
622: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
623: ORDER BY c,a,b
624: }
625: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
626: do_test selectA-2.90 {
627: execsql {
628: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
629: ORDER BY c DESC,a,b
630: }
631: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
632: do_test selectA-2.91 {
633: execsql {
634: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
635: ORDER BY c COLLATE BINARY DESC,a,b
636: }
637: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
638: do_test selectA-2.92 {
639: execsql {
640: SELECT x,y,z FROM t2
641: INTERSECT SELECT a,b,c FROM t3
642: EXCEPT SELECT c,b,a FROM t1
643: UNION SELECT a,b,c FROM t3
644: INTERSECT SELECT a,b,c FROM t3
645: EXCEPT SELECT c,b,a FROM t1
646: UNION SELECT a,b,c FROM t3
647: ORDER BY y COLLATE NOCASE DESC,x,z
648: }
649: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
650: do_test selectA-2.93 {
651: execsql {
652: SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
653: }
654: } {A}
655: do_test selectA-2.94 {
656: execsql {
657: SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
658: }
659: } {a}
660: do_test selectA-2.95 {
661: execsql {
662: SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
663: }
664: } {{}}
665: do_test selectA-2.96 {
666: execsql {
667: SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
668: }
669: } {m}
670:
671:
672: do_test selectA-3.0 {
673: execsql {
674: CREATE UNIQUE INDEX t1a ON t1(a);
675: CREATE UNIQUE INDEX t1b ON t1(b);
676: CREATE UNIQUE INDEX t1c ON t1(c);
677: CREATE UNIQUE INDEX t2x ON t2(x);
678: CREATE UNIQUE INDEX t2y ON t2(y);
679: CREATE UNIQUE INDEX t2z ON t2(z);
680: SELECT name FROM sqlite_master WHERE type='index'
681: }
682: } {t1a t1b t1c t2x t2y t2z}
683: do_test selectA-3.1 {
684: execsql {
685: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
686: ORDER BY a,b,c
687: }
688: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
689: do_test selectA-3.1.1 { # Ticket #3314
690: execsql {
691: SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2
692: ORDER BY a,t1.b,t1.c
693: }
694: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
695: do_test selectA-3.2 {
696: execsql {
697: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
698: ORDER BY a DESC,b,c
699: }
700: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
701: do_test selectA-3.3 {
702: execsql {
703: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
704: ORDER BY a,c,b
705: }
706: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
707: do_test selectA-3.4 {
708: execsql {
709: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
710: ORDER BY b,a,c
711: }
712: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
713: do_test selectA-3.5 {
714: execsql {
715: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
716: ORDER BY b COLLATE NOCASE,a,c
717: }
718: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
719: do_test selectA-3.6 {
720: execsql {
721: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
722: ORDER BY b COLLATE NOCASE DESC,a,c
723: }
724: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
725: do_test selectA-3.7 {
726: execsql {
727: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
728: ORDER BY c,b,a
729: }
730: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
731: do_test selectA-3.8 {
732: execsql {
733: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
734: ORDER BY c,a,b
735: }
736: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
737: do_test selectA-3.9 {
738: execsql {
739: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
740: ORDER BY c DESC,a,b
741: }
742: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
743: do_test selectA-3.10 {
744: execsql {
745: SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
746: ORDER BY c COLLATE BINARY DESC,a,b
747: }
748: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
749: do_test selectA-3.11 {
750: execsql {
751: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
752: ORDER BY a,b,c
753: }
754: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
755: do_test selectA-3.12 {
756: execsql {
757: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
758: ORDER BY a DESC,b,c
759: }
760: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
761: do_test selectA-3.13 {
762: execsql {
763: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
764: ORDER BY a,c,b
765: }
766: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
767: do_test selectA-3.14 {
768: execsql {
769: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
770: ORDER BY b,a,c
771: }
772: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
773: do_test selectA-3.15 {
774: execsql {
775: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
776: ORDER BY b COLLATE NOCASE,a,c
777: }
778: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
779: do_test selectA-3.16 {
780: execsql {
781: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
782: ORDER BY b COLLATE NOCASE DESC,a,c
783: }
784: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
785: do_test selectA-3.17 {
786: execsql {
787: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
788: ORDER BY c,b,a
789: }
790: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
791: do_test selectA-3.18 {
792: execsql {
793: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
794: ORDER BY c,a,b
795: }
796: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
797: do_test selectA-3.19 {
798: execsql {
799: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
800: ORDER BY c DESC,a,b
801: }
802: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
803: do_test selectA-3.20 {
804: execsql {
805: SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
806: ORDER BY c COLLATE BINARY DESC,a,b
807: }
808: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
809: do_test selectA-3.21 {
810: execsql {
811: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
812: ORDER BY a,b,c
813: }
814: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
815: do_test selectA-3.22 {
816: execsql {
817: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
818: ORDER BY a DESC,b,c
819: }
820: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
821: do_test selectA-3.23 {
822: execsql {
823: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
824: ORDER BY a,c,b
825: }
826: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
827: do_test selectA-3.24 {
828: execsql {
829: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
830: ORDER BY b,a,c
831: }
832: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
833: do_test selectA-3.25 {
834: execsql {
835: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
836: ORDER BY b COLLATE NOCASE,a,c
837: }
838: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
839: do_test selectA-3.26 {
840: execsql {
841: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
842: ORDER BY b COLLATE NOCASE DESC,a,c
843: }
844: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
845: do_test selectA-3.27 {
846: execsql {
847: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
848: ORDER BY c,b,a
849: }
850: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
851: do_test selectA-3.28 {
852: execsql {
853: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
854: ORDER BY c,a,b
855: }
856: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
857: do_test selectA-3.29 {
858: execsql {
859: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
860: ORDER BY c DESC,a,b
861: }
862: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
863: do_test selectA-3.30 {
864: execsql {
865: SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
866: ORDER BY c COLLATE BINARY DESC,a,b
867: }
868: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
869: do_test selectA-3.31 {
870: execsql {
871: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
872: ORDER BY a,b,c
873: }
874: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
875: do_test selectA-3.32 {
876: execsql {
877: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
878: ORDER BY a DESC,b,c
879: }
880: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
881: do_test selectA-3.33 {
882: execsql {
883: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
884: ORDER BY a,c,b
885: }
886: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
887: do_test selectA-3.34 {
888: execsql {
889: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
890: ORDER BY b,a,c
891: }
892: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
893: do_test selectA-3.35 {
894: execsql {
895: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
896: ORDER BY b COLLATE NOCASE,a,c
897: }
898: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
899: do_test selectA-3.36 {
900: execsql {
901: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
902: ORDER BY b COLLATE NOCASE DESC,a,c
903: }
904: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
905: do_test selectA-3.37 {
906: execsql {
907: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
908: ORDER BY c,b,a
909: }
910: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
911: do_test selectA-3.38 {
912: execsql {
913: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
914: ORDER BY c,a,b
915: }
916: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
917: do_test selectA-3.39 {
918: execsql {
919: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
920: ORDER BY c DESC,a,b
921: }
922: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
923: do_test selectA-3.40 {
924: execsql {
925: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1
926: ORDER BY c COLLATE BINARY DESC,a,b
927: }
928: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
929: do_test selectA-3.41 {
930: execsql {
931: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
932: ORDER BY a,b,c
933: }
934: } {{} C c 1 a a 9.9 b B}
935: do_test selectA-3.42 {
936: execsql {
937: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
938: ORDER BY a,b,c
939: }
940: } {hello d D abc e e}
941: do_test selectA-3.43 {
942: execsql {
943: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
944: ORDER BY a,b,c
945: }
946: } {hello d D abc e e}
947: do_test selectA-3.44 {
948: execsql {
949: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
950: ORDER BY a,b,c
951: }
952: } {hello d D abc e e}
953: do_test selectA-3.45 {
954: execsql {
955: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
956: ORDER BY a,b,c
957: }
958: } {{} C c 1 a a 9.9 b B}
959: do_test selectA-3.46 {
960: execsql {
961: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
962: ORDER BY a,b,c
963: }
964: } {{} C c 1 a a 9.9 b B}
965: do_test selectA-3.47 {
966: execsql {
967: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
968: ORDER BY a DESC
969: }
970: } {9.9 b B 1 a a {} C c}
971: do_test selectA-3.48 {
972: execsql {
973: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
974: ORDER BY a DESC
975: }
976: } {abc e e hello d D}
977: do_test selectA-3.49 {
978: execsql {
979: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
980: ORDER BY a DESC
981: }
982: } {abc e e hello d D}
983: do_test selectA-3.50 {
984: execsql {
985: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
986: ORDER BY a DESC
987: }
988: } {abc e e hello d D}
989: do_test selectA-3.51 {
990: execsql {
991: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
992: ORDER BY a DESC
993: }
994: } {9.9 b B 1 a a {} C c}
995: do_test selectA-3.52 {
996: execsql {
997: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
998: ORDER BY a DESC
999: }
1000: } {9.9 b B 1 a a {} C c}
1001: do_test selectA-3.53 {
1002: execsql {
1003: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1004: ORDER BY b, a DESC
1005: }
1006: } {{} C c 1 a a 9.9 b B}
1007: do_test selectA-3.54 {
1008: execsql {
1009: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1010: ORDER BY b
1011: }
1012: } {hello d D abc e e}
1013: do_test selectA-3.55 {
1014: execsql {
1015: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1016: ORDER BY b DESC, c
1017: }
1018: } {abc e e hello d D}
1019: do_test selectA-3.56 {
1020: execsql {
1021: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1022: ORDER BY b, c DESC, a
1023: }
1024: } {hello d D abc e e}
1025: do_test selectA-3.57 {
1026: execsql {
1027: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1028: ORDER BY b COLLATE NOCASE
1029: }
1030: } {1 a a 9.9 b B {} C c}
1031: do_test selectA-3.58 {
1032: execsql {
1033: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1034: ORDER BY b
1035: }
1036: } {{} C c 1 a a 9.9 b B}
1037: do_test selectA-3.59 {
1038: execsql {
1039: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
1040: ORDER BY c, a DESC
1041: }
1042: } {1 a a 9.9 b B {} C c}
1043: do_test selectA-3.60 {
1044: execsql {
1045: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
1046: ORDER BY c
1047: }
1048: } {hello d D abc e e}
1049: do_test selectA-3.61 {
1050: execsql {
1051: SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
1052: ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c
1053: }
1054: } {hello d D abc e e}
1055: do_test selectA-3.62 {
1056: execsql {
1057: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1058: ORDER BY c DESC, a
1059: }
1060: } {abc e e hello d D}
1061: do_test selectA-3.63 {
1062: execsql {
1063: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1064: ORDER BY c COLLATE NOCASE
1065: }
1066: } {1 a a 9.9 b B {} C c}
1067: do_test selectA-3.64 {
1068: execsql {
1069: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1070: ORDER BY c
1071: }
1072: } {1 a a 9.9 b B {} C c}
1073: do_test selectA-3.65 {
1074: execsql {
1075: SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1076: ORDER BY c COLLATE NOCASE
1077: }
1078: } {1 a a 9.9 b B {} C c}
1079: do_test selectA-3.66 {
1080: execsql {
1081: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3
1082: ORDER BY c
1083: }
1084: } {1 a a 9.9 b B {} C c}
1085: do_test selectA-3.67 {
1086: execsql {
1087: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'
1088: ORDER BY c DESC, a
1089: }
1090: } {abc e e hello d D}
1091: do_test selectA-3.68 {
1092: execsql {
1093: SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
1094: INTERSECT SELECT a,b,c FROM t3
1095: EXCEPT SELECT b,c,a FROM t3
1096: ORDER BY c DESC, a
1097: }
1098: } {abc e e hello d D}
1099: do_test selectA-3.69 {
1100: execsql {
1101: SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
1102: INTERSECT SELECT a,b,c FROM t3
1103: EXCEPT SELECT b,c,a FROM t3
1104: ORDER BY c COLLATE NOCASE
1105: }
1106: } {1 a a 9.9 b B {} C c}
1107: do_test selectA-3.70 {
1108: execsql {
1109: SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
1110: INTERSECT SELECT a,b,c FROM t3
1111: EXCEPT SELECT b,c,a FROM t3
1112: ORDER BY c
1113: }
1114: } {1 a a 9.9 b B {} C c}
1115: do_test selectA-3.71 {
1116: execsql {
1117: SELECT a,b,c FROM t1 WHERE b<'d'
1118: INTERSECT SELECT a,b,c FROM t1
1119: INTERSECT SELECT a,b,c FROM t3
1120: EXCEPT SELECT b,c,a FROM t3
1121: INTERSECT SELECT a,b,c FROM t1
1122: EXCEPT SELECT x,y,z FROM t2
1123: INTERSECT SELECT a,b,c FROM t3
1124: EXCEPT SELECT y,x,z FROM t2
1125: INTERSECT SELECT a,b,c FROM t1
1126: EXCEPT SELECT c,b,a FROM t3
1127: ORDER BY c
1128: }
1129: } {1 a a 9.9 b B {} C c}
1130: do_test selectA-3.72 {
1131: execsql {
1132: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1133: ORDER BY a,b,c
1134: }
1135: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
1136: do_test selectA-3.73 {
1137: execsql {
1138: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1139: ORDER BY a DESC,b,c
1140: }
1141: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
1142: do_test selectA-3.74 {
1143: execsql {
1144: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1145: ORDER BY a,c,b
1146: }
1147: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
1148: do_test selectA-3.75 {
1149: execsql {
1150: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1151: ORDER BY b,a,c
1152: }
1153: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
1154: do_test selectA-3.76 {
1155: execsql {
1156: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1157: ORDER BY b COLLATE NOCASE,a,c
1158: }
1159: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
1160: do_test selectA-3.77 {
1161: execsql {
1162: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1163: ORDER BY b COLLATE NOCASE DESC,a,c
1164: }
1165: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
1166: do_test selectA-3.78 {
1167: execsql {
1168: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1169: ORDER BY c,b,a
1170: }
1171: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
1172: do_test selectA-3.79 {
1173: execsql {
1174: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1175: ORDER BY c,a,b
1176: }
1177: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
1178: do_test selectA-3.80 {
1179: execsql {
1180: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1181: ORDER BY c DESC,a,b
1182: }
1183: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
1184: do_test selectA-3.81 {
1185: execsql {
1186: SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
1187: ORDER BY c COLLATE BINARY DESC,a,b
1188: }
1189: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
1190: do_test selectA-3.82 {
1191: execsql {
1192: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1193: ORDER BY a,b,c
1194: }
1195: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
1196: do_test selectA-3.83 {
1197: execsql {
1198: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1199: ORDER BY a DESC,b,c
1200: }
1201: } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
1202: do_test selectA-3.84 {
1203: execsql {
1204: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1205: ORDER BY a,c,b
1206: }
1207: } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
1208: do_test selectA-3.85 {
1209: execsql {
1210: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1211: ORDER BY b,a,c
1212: }
1213: } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
1214: do_test selectA-3.86 {
1215: execsql {
1216: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1217: ORDER BY b COLLATE NOCASE,a,c
1218: }
1219: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
1220: do_test selectA-3.87 {
1221: execsql {
1222: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1223: ORDER BY y COLLATE NOCASE DESC,x,z
1224: }
1225: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
1226: do_test selectA-3.88 {
1227: execsql {
1228: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1229: ORDER BY c,b,a
1230: }
1231: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
1232: do_test selectA-3.89 {
1233: execsql {
1234: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1235: ORDER BY c,a,b
1236: }
1237: } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
1238: do_test selectA-3.90 {
1239: execsql {
1240: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1241: ORDER BY c DESC,a,b
1242: }
1243: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
1244: do_test selectA-3.91 {
1245: execsql {
1246: SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
1247: ORDER BY c COLLATE BINARY DESC,a,b
1248: }
1249: } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
1250: do_test selectA-3.92 {
1251: execsql {
1252: SELECT x,y,z FROM t2
1253: INTERSECT SELECT a,b,c FROM t3
1254: EXCEPT SELECT c,b,a FROM t1
1255: UNION SELECT a,b,c FROM t3
1256: INTERSECT SELECT a,b,c FROM t3
1257: EXCEPT SELECT c,b,a FROM t1
1258: UNION SELECT a,b,c FROM t3
1259: ORDER BY y COLLATE NOCASE DESC,x,z
1260: }
1261: } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
1262: do_test selectA-3.93 {
1263: execsql {
1264: SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
1265: }
1266: } {A}
1267: do_test selectA-3.94 {
1268: execsql {
1269: SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
1270: }
1271: } {a}
1272: do_test selectA-3.95 {
1273: execsql {
1274: SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
1275: }
1276: } {{}}
1277: do_test selectA-3.96 {
1278: execsql {
1279: SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
1280: }
1281: } {m}
1282: do_test selectA-3.97 {
1283: execsql {
1284: SELECT upper((SELECT x FROM (
1285: SELECT x,y,z FROM t2
1286: INTERSECT SELECT a,b,c FROM t3
1287: EXCEPT SELECT c,b,a FROM t1
1288: UNION SELECT a,b,c FROM t3
1289: INTERSECT SELECT a,b,c FROM t3
1290: EXCEPT SELECT c,b,a FROM t1
1291: UNION SELECT a,b,c FROM t3
1292: ORDER BY y COLLATE NOCASE DESC,x,z)))
1293: }
1294: } {MAD}
1295:
1296: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to selectA.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