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