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