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