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