wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 | |||
22 | set testdir [file dirname $argv0] |
||
23 | source $testdir/tester.tcl |
||
24 | |||
25 | ifcapable !compound { |
||
26 | finish_test |
||
27 | return |
||
28 | } |
||
29 | |||
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} |
||
64 | |||
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} |
||
670 | |||
671 | |||
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} |
||
1295 | |||
1296 | finish_test |