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
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.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}
77do_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}
83do_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}
89do_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}
95do_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}
101do_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}
107do_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}
113do_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}
119do_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}
125do_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}
131do_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}
137do_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}
143do_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}
149do_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}
155do_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}
161do_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}
167do_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}
173do_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}
179do_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}
185do_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}
191do_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}
197do_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}
203do_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}
209do_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}
215do_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}
221do_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}
227do_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}
233do_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}
239do_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}
245do_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}
251do_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}
257do_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}
263do_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}
269do_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}
275do_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}
281do_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}
287do_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}
293do_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}
299do_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}
305do_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}
311do_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}
317do_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}
323do_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}
329do_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}
335do_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}
341do_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}
347do_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}
353do_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}
359do_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}
365do_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}
371do_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}
377do_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}
383do_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}
389do_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}
395do_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}
401do_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}
407do_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}
413do_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}
419do_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}
425do_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}
431do_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}
437do_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}
443do_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}
449do_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}
455do_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}
461do_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}
467do_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}
473do_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}
479do_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}
487do_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}
495do_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}
503do_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}
518do_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}
524do_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}
530do_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}
536do_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}
542do_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}
548do_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}
554do_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}
560do_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}
566do_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}
572do_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}
578do_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}
584do_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}
590do_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}
596do_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}
602do_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}
608do_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}
614do_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}
620do_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}
626do_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}
632do_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}
638do_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}
650do_test selectA-2.93 {
651  execsql {
652    SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
653  }
654} {A}
655do_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}
660do_test selectA-2.95 {
661  execsql {
662    SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
663  }
664} {{}}
665do_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
672do_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}
683do_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}
689do_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}
695do_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}
701do_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}
707do_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}
713do_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}
719do_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}
725do_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}
731do_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}
737do_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}
743do_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}
749do_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}
755do_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}
761do_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}
767do_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}
773do_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}
779do_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}
785do_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}
791do_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}
797do_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}
803do_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}
809do_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}
815do_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}
821do_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}
827do_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}
833do_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}
839do_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}
845do_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}
851do_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}
857do_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}
863do_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}
869do_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}
875do_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}
881do_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}
887do_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}
893do_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}
899do_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}
905do_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}
911do_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}
917do_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}
923do_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}
929do_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}
935do_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}
941do_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}
947do_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}
953do_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}
959do_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}
965do_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}
971do_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}
977do_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}
983do_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}
989do_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}
995do_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}
1001do_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}
1007do_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}
1013do_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}
1019do_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}
1025do_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}
1031do_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}
1037do_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}
1043do_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}
1049do_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}
1055do_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}
1061do_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}
1067do_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}
1073do_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}
1079do_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}
1085do_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}
1091do_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}
1099do_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}
1107do_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}
1115do_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}
1130do_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}
1136do_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}
1142do_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}
1148do_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}
1154do_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}
1160do_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}
1166do_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}
1172do_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}
1178do_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}
1184do_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}
1190do_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}
1196do_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}
1202do_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}
1208do_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}
1214do_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}
1220do_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}
1226do_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}
1232do_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}
1238do_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}
1244do_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}
1250do_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}
1262do_test selectA-3.93 {
1263  execsql {
1264    SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
1265  }
1266} {A}
1267do_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}
1272do_test selectA-3.95 {
1273  execsql {
1274    SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
1275  }
1276} {{}}
1277do_test selectA-3.96 {
1278  execsql {
1279    SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
1280  }
1281} {m}
1282do_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
1296finish_test
1297