wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2001 September 15 |
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. The |
||
12 | # focus of this file is testing UNION, INTERSECT and EXCEPT operators |
||
13 | # in SELECT statements. |
||
14 | # |
||
15 | # $Id: select4.test,v 1.30 2009/04/16 00:24:24 drh Exp $ |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | # Most tests in this file depend on compound-select. But there are a couple |
||
21 | # right at the end that test DISTINCT, so we cannot omit the entire file. |
||
22 | # |
||
23 | ifcapable compound { |
||
24 | |||
25 | # Build some test data |
||
26 | # |
||
27 | execsql { |
||
28 | CREATE TABLE t1(n int, log int); |
||
29 | BEGIN; |
||
30 | } |
||
31 | for {set i 1} {$i<32} {incr i} { |
||
32 | for {set j 0} {(1<<$j)<$i} {incr j} {} |
||
33 | execsql "INSERT INTO t1 VALUES($i,$j)" |
||
34 | } |
||
35 | execsql { |
||
36 | COMMIT; |
||
37 | } |
||
38 | |||
39 | do_test select4-1.0 { |
||
40 | execsql {SELECT DISTINCT log FROM t1 ORDER BY log} |
||
41 | } {0 1 2 3 4 5} |
||
42 | |||
43 | # Union All operator |
||
44 | # |
||
45 | do_test select4-1.1a { |
||
46 | lsort [execsql {SELECT DISTINCT log FROM t1}] |
||
47 | } {0 1 2 3 4 5} |
||
48 | do_test select4-1.1b { |
||
49 | lsort [execsql {SELECT n FROM t1 WHERE log=3}] |
||
50 | } {5 6 7 8} |
||
51 | do_test select4-1.1c { |
||
52 | execsql { |
||
53 | SELECT DISTINCT log FROM t1 |
||
54 | UNION ALL |
||
55 | SELECT n FROM t1 WHERE log=3 |
||
56 | ORDER BY log; |
||
57 | } |
||
58 | } {0 1 2 3 4 5 5 6 7 8} |
||
59 | do_test select4-1.1d { |
||
60 | execsql { |
||
61 | CREATE TABLE t2 AS |
||
62 | SELECT DISTINCT log FROM t1 |
||
63 | UNION ALL |
||
64 | SELECT n FROM t1 WHERE log=3 |
||
65 | ORDER BY log; |
||
66 | SELECT * FROM t2; |
||
67 | } |
||
68 | } {0 1 2 3 4 5 5 6 7 8} |
||
69 | execsql {DROP TABLE t2} |
||
70 | do_test select4-1.1e { |
||
71 | execsql { |
||
72 | CREATE TABLE t2 AS |
||
73 | SELECT DISTINCT log FROM t1 |
||
74 | UNION ALL |
||
75 | SELECT n FROM t1 WHERE log=3 |
||
76 | ORDER BY log DESC; |
||
77 | SELECT * FROM t2; |
||
78 | } |
||
79 | } {8 7 6 5 5 4 3 2 1 0} |
||
80 | execsql {DROP TABLE t2} |
||
81 | do_test select4-1.1f { |
||
82 | execsql { |
||
83 | SELECT DISTINCT log FROM t1 |
||
84 | UNION ALL |
||
85 | SELECT n FROM t1 WHERE log=2 |
||
86 | } |
||
87 | } {0 1 2 3 4 5 3 4} |
||
88 | do_test select4-1.1g { |
||
89 | execsql { |
||
90 | CREATE TABLE t2 AS |
||
91 | SELECT DISTINCT log FROM t1 |
||
92 | UNION ALL |
||
93 | SELECT n FROM t1 WHERE log=2; |
||
94 | SELECT * FROM t2; |
||
95 | } |
||
96 | } {0 1 2 3 4 5 3 4} |
||
97 | execsql {DROP TABLE t2} |
||
98 | ifcapable subquery { |
||
99 | do_test select4-1.2 { |
||
100 | execsql { |
||
101 | SELECT log FROM t1 WHERE n IN |
||
102 | (SELECT DISTINCT log FROM t1 UNION ALL |
||
103 | SELECT n FROM t1 WHERE log=3) |
||
104 | ORDER BY log; |
||
105 | } |
||
106 | } {0 1 2 2 3 3 3 3} |
||
107 | } |
||
108 | do_test select4-1.3 { |
||
109 | set v [catch {execsql { |
||
110 | SELECT DISTINCT log FROM t1 ORDER BY log |
||
111 | UNION ALL |
||
112 | SELECT n FROM t1 WHERE log=3 |
||
113 | ORDER BY log; |
||
114 | }} msg] |
||
115 | lappend v $msg |
||
116 | } {1 {ORDER BY clause should come after UNION ALL not before}} |
||
117 | |||
118 | # Union operator |
||
119 | # |
||
120 | do_test select4-2.1 { |
||
121 | execsql { |
||
122 | SELECT DISTINCT log FROM t1 |
||
123 | UNION |
||
124 | SELECT n FROM t1 WHERE log=3 |
||
125 | ORDER BY log; |
||
126 | } |
||
127 | } {0 1 2 3 4 5 6 7 8} |
||
128 | ifcapable subquery { |
||
129 | do_test select4-2.2 { |
||
130 | execsql { |
||
131 | SELECT log FROM t1 WHERE n IN |
||
132 | (SELECT DISTINCT log FROM t1 UNION |
||
133 | SELECT n FROM t1 WHERE log=3) |
||
134 | ORDER BY log; |
||
135 | } |
||
136 | } {0 1 2 2 3 3 3 3} |
||
137 | } |
||
138 | do_test select4-2.3 { |
||
139 | set v [catch {execsql { |
||
140 | SELECT DISTINCT log FROM t1 ORDER BY log |
||
141 | UNION |
||
142 | SELECT n FROM t1 WHERE log=3 |
||
143 | ORDER BY log; |
||
144 | }} msg] |
||
145 | lappend v $msg |
||
146 | } {1 {ORDER BY clause should come after UNION not before}} |
||
147 | |||
148 | # Except operator |
||
149 | # |
||
150 | do_test select4-3.1.1 { |
||
151 | execsql { |
||
152 | SELECT DISTINCT log FROM t1 |
||
153 | EXCEPT |
||
154 | SELECT n FROM t1 WHERE log=3 |
||
155 | ORDER BY log; |
||
156 | } |
||
157 | } {0 1 2 3 4} |
||
158 | do_test select4-3.1.2 { |
||
159 | execsql { |
||
160 | CREATE TABLE t2 AS |
||
161 | SELECT DISTINCT log FROM t1 |
||
162 | EXCEPT |
||
163 | SELECT n FROM t1 WHERE log=3 |
||
164 | ORDER BY log; |
||
165 | SELECT * FROM t2; |
||
166 | } |
||
167 | } {0 1 2 3 4} |
||
168 | execsql {DROP TABLE t2} |
||
169 | do_test select4-3.1.3 { |
||
170 | execsql { |
||
171 | CREATE TABLE t2 AS |
||
172 | SELECT DISTINCT log FROM t1 |
||
173 | EXCEPT |
||
174 | SELECT n FROM t1 WHERE log=3 |
||
175 | ORDER BY log DESC; |
||
176 | SELECT * FROM t2; |
||
177 | } |
||
178 | } {4 3 2 1 0} |
||
179 | execsql {DROP TABLE t2} |
||
180 | ifcapable subquery { |
||
181 | do_test select4-3.2 { |
||
182 | execsql { |
||
183 | SELECT log FROM t1 WHERE n IN |
||
184 | (SELECT DISTINCT log FROM t1 EXCEPT |
||
185 | SELECT n FROM t1 WHERE log=3) |
||
186 | ORDER BY log; |
||
187 | } |
||
188 | } {0 1 2 2} |
||
189 | } |
||
190 | do_test select4-3.3 { |
||
191 | set v [catch {execsql { |
||
192 | SELECT DISTINCT log FROM t1 ORDER BY log |
||
193 | EXCEPT |
||
194 | SELECT n FROM t1 WHERE log=3 |
||
195 | ORDER BY log; |
||
196 | }} msg] |
||
197 | lappend v $msg |
||
198 | } {1 {ORDER BY clause should come after EXCEPT not before}} |
||
199 | |||
200 | # Intersect operator |
||
201 | # |
||
202 | do_test select4-4.1.1 { |
||
203 | execsql { |
||
204 | SELECT DISTINCT log FROM t1 |
||
205 | INTERSECT |
||
206 | SELECT n FROM t1 WHERE log=3 |
||
207 | ORDER BY log; |
||
208 | } |
||
209 | } {5} |
||
210 | |||
211 | do_test select4-4.1.2 { |
||
212 | execsql { |
||
213 | SELECT DISTINCT log FROM t1 |
||
214 | UNION ALL |
||
215 | SELECT 6 |
||
216 | INTERSECT |
||
217 | SELECT n FROM t1 WHERE log=3 |
||
218 | ORDER BY t1.log; |
||
219 | } |
||
220 | } {5 6} |
||
221 | |||
222 | do_test select4-4.1.3 { |
||
223 | execsql { |
||
224 | CREATE TABLE t2 AS |
||
225 | SELECT DISTINCT log FROM t1 UNION ALL SELECT 6 |
||
226 | INTERSECT |
||
227 | SELECT n FROM t1 WHERE log=3 |
||
228 | ORDER BY log; |
||
229 | SELECT * FROM t2; |
||
230 | } |
||
231 | } {5 6} |
||
232 | execsql {DROP TABLE t2} |
||
233 | do_test select4-4.1.4 { |
||
234 | execsql { |
||
235 | CREATE TABLE t2 AS |
||
236 | SELECT DISTINCT log FROM t1 UNION ALL SELECT 6 |
||
237 | INTERSECT |
||
238 | SELECT n FROM t1 WHERE log=3 |
||
239 | ORDER BY log DESC; |
||
240 | SELECT * FROM t2; |
||
241 | } |
||
242 | } {6 5} |
||
243 | execsql {DROP TABLE t2} |
||
244 | ifcapable subquery { |
||
245 | do_test select4-4.2 { |
||
246 | execsql { |
||
247 | SELECT log FROM t1 WHERE n IN |
||
248 | (SELECT DISTINCT log FROM t1 INTERSECT |
||
249 | SELECT n FROM t1 WHERE log=3) |
||
250 | ORDER BY log; |
||
251 | } |
||
252 | } {3} |
||
253 | } |
||
254 | do_test select4-4.3 { |
||
255 | set v [catch {execsql { |
||
256 | SELECT DISTINCT log FROM t1 ORDER BY log |
||
257 | INTERSECT |
||
258 | SELECT n FROM t1 WHERE log=3 |
||
259 | ORDER BY log; |
||
260 | }} msg] |
||
261 | lappend v $msg |
||
262 | } {1 {ORDER BY clause should come after INTERSECT not before}} |
||
263 | |||
264 | # Various error messages while processing UNION or INTERSECT |
||
265 | # |
||
266 | do_test select4-5.1 { |
||
267 | set v [catch {execsql { |
||
268 | SELECT DISTINCT log FROM t2 |
||
269 | UNION ALL |
||
270 | SELECT n FROM t1 WHERE log=3 |
||
271 | ORDER BY log; |
||
272 | }} msg] |
||
273 | lappend v $msg |
||
274 | } {1 {no such table: t2}} |
||
275 | do_test select4-5.2 { |
||
276 | set v [catch {execsql { |
||
277 | SELECT DISTINCT log AS "xyzzy" FROM t1 |
||
278 | UNION ALL |
||
279 | SELECT n FROM t1 WHERE log=3 |
||
280 | ORDER BY xyzzy; |
||
281 | }} msg] |
||
282 | lappend v $msg |
||
283 | } {0 {0 1 2 3 4 5 5 6 7 8}} |
||
284 | do_test select4-5.2b { |
||
285 | set v [catch {execsql { |
||
286 | SELECT DISTINCT log AS xyzzy FROM t1 |
||
287 | UNION ALL |
||
288 | SELECT n FROM t1 WHERE log=3 |
||
289 | ORDER BY "xyzzy"; |
||
290 | }} msg] |
||
291 | lappend v $msg |
||
292 | } {0 {0 1 2 3 4 5 5 6 7 8}} |
||
293 | do_test select4-5.2c { |
||
294 | set v [catch {execsql { |
||
295 | SELECT DISTINCT log FROM t1 |
||
296 | UNION ALL |
||
297 | SELECT n FROM t1 WHERE log=3 |
||
298 | ORDER BY "xyzzy"; |
||
299 | }} msg] |
||
300 | lappend v $msg |
||
301 | } {1 {1st ORDER BY term does not match any column in the result set}} |
||
302 | do_test select4-5.2d { |
||
303 | set v [catch {execsql { |
||
304 | SELECT DISTINCT log FROM t1 |
||
305 | INTERSECT |
||
306 | SELECT n FROM t1 WHERE log=3 |
||
307 | ORDER BY "xyzzy"; |
||
308 | }} msg] |
||
309 | lappend v $msg |
||
310 | } {1 {1st ORDER BY term does not match any column in the result set}} |
||
311 | do_test select4-5.2e { |
||
312 | set v [catch {execsql { |
||
313 | SELECT DISTINCT log FROM t1 |
||
314 | UNION ALL |
||
315 | SELECT n FROM t1 WHERE log=3 |
||
316 | ORDER BY n; |
||
317 | }} msg] |
||
318 | lappend v $msg |
||
319 | } {0 {0 1 2 3 4 5 5 6 7 8}} |
||
320 | do_test select4-5.2f { |
||
321 | catchsql { |
||
322 | SELECT DISTINCT log FROM t1 |
||
323 | UNION ALL |
||
324 | SELECT n FROM t1 WHERE log=3 |
||
325 | ORDER BY log; |
||
326 | } |
||
327 | } {0 {0 1 2 3 4 5 5 6 7 8}} |
||
328 | do_test select4-5.2g { |
||
329 | catchsql { |
||
330 | SELECT DISTINCT log FROM t1 |
||
331 | UNION ALL |
||
332 | SELECT n FROM t1 WHERE log=3 |
||
333 | ORDER BY 1; |
||
334 | } |
||
335 | } {0 {0 1 2 3 4 5 5 6 7 8}} |
||
336 | do_test select4-5.2h { |
||
337 | catchsql { |
||
338 | SELECT DISTINCT log FROM t1 |
||
339 | UNION ALL |
||
340 | SELECT n FROM t1 WHERE log=3 |
||
341 | ORDER BY 2; |
||
342 | } |
||
343 | } {1 {1st ORDER BY term out of range - should be between 1 and 1}} |
||
344 | do_test select4-5.2i { |
||
345 | catchsql { |
||
346 | SELECT DISTINCT 1, log FROM t1 |
||
347 | UNION ALL |
||
348 | SELECT 2, n FROM t1 WHERE log=3 |
||
349 | ORDER BY 2, 1; |
||
350 | } |
||
351 | } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}} |
||
352 | do_test select4-5.2j { |
||
353 | catchsql { |
||
354 | SELECT DISTINCT 1, log FROM t1 |
||
355 | UNION ALL |
||
356 | SELECT 2, n FROM t1 WHERE log=3 |
||
357 | ORDER BY 1, 2 DESC; |
||
358 | } |
||
359 | } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}} |
||
360 | do_test select4-5.2k { |
||
361 | catchsql { |
||
362 | SELECT DISTINCT 1, log FROM t1 |
||
363 | UNION ALL |
||
364 | SELECT 2, n FROM t1 WHERE log=3 |
||
365 | ORDER BY n, 1; |
||
366 | } |
||
367 | } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}} |
||
368 | do_test select4-5.3 { |
||
369 | set v [catch {execsql { |
||
370 | SELECT DISTINCT log, n FROM t1 |
||
371 | UNION ALL |
||
372 | SELECT n FROM t1 WHERE log=3 |
||
373 | ORDER BY log; |
||
374 | }} msg] |
||
375 | lappend v $msg |
||
376 | } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} |
||
377 | do_test select4-5.3-3807-1 { |
||
378 | catchsql { |
||
379 | SELECT 1 UNION SELECT 2, 3 UNION SELECT 4, 5 ORDER BY 1; |
||
380 | } |
||
381 | } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
||
382 | do_test select4-5.4 { |
||
383 | set v [catch {execsql { |
||
384 | SELECT log FROM t1 WHERE n=2 |
||
385 | UNION ALL |
||
386 | SELECT log FROM t1 WHERE n=3 |
||
387 | UNION ALL |
||
388 | SELECT log FROM t1 WHERE n=4 |
||
389 | UNION ALL |
||
390 | SELECT log FROM t1 WHERE n=5 |
||
391 | ORDER BY log; |
||
392 | }} msg] |
||
393 | lappend v $msg |
||
394 | } {0 {1 2 2 3}} |
||
395 | |||
396 | do_test select4-6.1 { |
||
397 | execsql { |
||
398 | SELECT log, count(*) as cnt FROM t1 GROUP BY log |
||
399 | UNION |
||
400 | SELECT log, n FROM t1 WHERE n=7 |
||
401 | ORDER BY cnt, log; |
||
402 | } |
||
403 | } {0 1 1 1 2 2 3 4 3 7 4 8 5 15} |
||
404 | do_test select4-6.2 { |
||
405 | execsql { |
||
406 | SELECT log, count(*) FROM t1 GROUP BY log |
||
407 | UNION |
||
408 | SELECT log, n FROM t1 WHERE n=7 |
||
409 | ORDER BY count(*), log; |
||
410 | } |
||
411 | } {0 1 1 1 2 2 3 4 3 7 4 8 5 15} |
||
412 | |||
413 | # NULLs are indistinct for the UNION operator. |
||
414 | # Make sure the UNION operator recognizes this |
||
415 | # |
||
416 | do_test select4-6.3 { |
||
417 | execsql { |
||
418 | SELECT NULL UNION SELECT NULL UNION |
||
419 | SELECT 1 UNION SELECT 2 AS 'x' |
||
420 | ORDER BY x; |
||
421 | } |
||
422 | } {{} 1 2} |
||
423 | do_test select4-6.3.1 { |
||
424 | execsql { |
||
425 | SELECT NULL UNION ALL SELECT NULL UNION ALL |
||
426 | SELECT 1 UNION ALL SELECT 2 AS 'x' |
||
427 | ORDER BY x; |
||
428 | } |
||
429 | } {{} {} 1 2} |
||
430 | |||
431 | # Make sure the DISTINCT keyword treats NULLs as indistinct. |
||
432 | # |
||
433 | ifcapable subquery { |
||
434 | do_test select4-6.4 { |
||
435 | execsql { |
||
436 | SELECT * FROM ( |
||
437 | SELECT NULL, 1 UNION ALL SELECT NULL, 1 |
||
438 | ); |
||
439 | } |
||
440 | } {{} 1 {} 1} |
||
441 | do_test select4-6.5 { |
||
442 | execsql { |
||
443 | SELECT DISTINCT * FROM ( |
||
444 | SELECT NULL, 1 UNION ALL SELECT NULL, 1 |
||
445 | ); |
||
446 | } |
||
447 | } {{} 1} |
||
448 | do_test select4-6.6 { |
||
449 | execsql { |
||
450 | SELECT DISTINCT * FROM ( |
||
451 | SELECT 1,2 UNION ALL SELECT 1,2 |
||
452 | ); |
||
453 | } |
||
454 | } {1 2} |
||
455 | } |
||
456 | |||
457 | # Test distinctness of NULL in other ways. |
||
458 | # |
||
459 | do_test select4-6.7 { |
||
460 | execsql { |
||
461 | SELECT NULL EXCEPT SELECT NULL |
||
462 | } |
||
463 | } {} |
||
464 | |||
465 | |||
466 | # Make sure column names are correct when a compound select appears as |
||
467 | # an expression in the WHERE clause. |
||
468 | # |
||
469 | do_test select4-7.1 { |
||
470 | execsql { |
||
471 | CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log; |
||
472 | SELECT * FROM t2 ORDER BY x; |
||
473 | } |
||
474 | } {0 1 1 1 2 2 3 4 4 8 5 15} |
||
475 | ifcapable subquery { |
||
476 | do_test select4-7.2 { |
||
477 | execsql2 { |
||
478 | SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2) |
||
479 | ORDER BY n |
||
480 | } |
||
481 | } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3} |
||
482 | do_test select4-7.3 { |
||
483 | execsql2 { |
||
484 | SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2) |
||
485 | ORDER BY n LIMIT 2 |
||
486 | } |
||
487 | } {n 6 log 3 n 7 log 3} |
||
488 | do_test select4-7.4 { |
||
489 | execsql2 { |
||
490 | SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2) |
||
491 | ORDER BY n LIMIT 2 |
||
492 | } |
||
493 | } {n 1 log 0 n 2 log 1} |
||
494 | } ;# ifcapable subquery |
||
495 | |||
496 | } ;# ifcapable compound |
||
497 | |||
498 | # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns. |
||
499 | do_test select4-8.1 { |
||
500 | execsql { |
||
501 | BEGIN; |
||
502 | CREATE TABLE t3(a text, b float, c text); |
||
503 | INSERT INTO t3 VALUES(1, 1.1, '1.1'); |
||
504 | INSERT INTO t3 VALUES(2, 1.10, '1.10'); |
||
505 | INSERT INTO t3 VALUES(3, 1.10, '1.1'); |
||
506 | INSERT INTO t3 VALUES(4, 1.1, '1.10'); |
||
507 | INSERT INTO t3 VALUES(5, 1.2, '1.2'); |
||
508 | INSERT INTO t3 VALUES(6, 1.3, '1.3'); |
||
509 | COMMIT; |
||
510 | } |
||
511 | execsql { |
||
512 | SELECT DISTINCT b FROM t3 ORDER BY c; |
||
513 | } |
||
514 | } {1.1 1.2 1.3} |
||
515 | do_test select4-8.2 { |
||
516 | execsql { |
||
517 | SELECT DISTINCT c FROM t3 ORDER BY c; |
||
518 | } |
||
519 | } {1.1 1.10 1.2 1.3} |
||
520 | |||
521 | # Make sure the names of columns are taken from the right-most subquery |
||
522 | # right in a compound query. Ticket #1721 |
||
523 | # |
||
524 | ifcapable compound { |
||
525 | |||
526 | do_test select4-9.1 { |
||
527 | execsql2 { |
||
528 | SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1 |
||
529 | } |
||
530 | } {x 0 y 1} |
||
531 | do_test select4-9.2 { |
||
532 | execsql2 { |
||
533 | SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1 |
||
534 | } |
||
535 | } {x 0 y 1} |
||
536 | do_test select4-9.3 { |
||
537 | execsql2 { |
||
538 | SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1 |
||
539 | } |
||
540 | } {x 0 y 1} |
||
541 | do_test select4-9.4 { |
||
542 | execsql2 { |
||
543 | SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b; |
||
544 | } |
||
545 | } {x 0 y 1} |
||
546 | do_test select4-9.5 { |
||
547 | execsql2 { |
||
548 | SELECT 0 AS x, 1 AS y |
||
549 | UNION |
||
550 | SELECT 2 AS p, 3 AS q |
||
551 | UNION |
||
552 | SELECT 4 AS a, 5 AS b |
||
553 | ORDER BY x LIMIT 1 |
||
554 | } |
||
555 | } {x 0 y 1} |
||
556 | |||
557 | ifcapable subquery { |
||
558 | do_test select4-9.6 { |
||
559 | execsql2 { |
||
560 | SELECT * FROM ( |
||
561 | SELECT 0 AS x, 1 AS y |
||
562 | UNION |
||
563 | SELECT 2 AS p, 3 AS q |
||
564 | UNION |
||
565 | SELECT 4 AS a, 5 AS b |
||
566 | ) ORDER BY 1 LIMIT 1; |
||
567 | } |
||
568 | } {x 0 y 1} |
||
569 | do_test select4-9.7 { |
||
570 | execsql2 { |
||
571 | SELECT * FROM ( |
||
572 | SELECT 0 AS x, 1 AS y |
||
573 | UNION |
||
574 | SELECT 2 AS p, 3 AS q |
||
575 | UNION |
||
576 | SELECT 4 AS a, 5 AS b |
||
577 | ) ORDER BY x LIMIT 1; |
||
578 | } |
||
579 | } {x 0 y 1} |
||
580 | } ;# ifcapable subquery |
||
581 | |||
582 | do_test select4-9.8 { |
||
583 | execsql { |
||
584 | SELECT 0 AS x, 1 AS y |
||
585 | UNION |
||
586 | SELECT 2 AS y, -3 AS x |
||
587 | ORDER BY x LIMIT 1; |
||
588 | } |
||
589 | } {0 1} |
||
590 | |||
591 | do_test select4-9.9.1 { |
||
592 | execsql2 { |
||
593 | SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a |
||
594 | } |
||
595 | } {a 1 b 2 a 3 b 4} |
||
596 | |||
597 | ifcapable subquery { |
||
598 | do_test select4-9.9.2 { |
||
599 | execsql2 { |
||
600 | SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a) |
||
601 | WHERE b=3 |
||
602 | } |
||
603 | } {} |
||
604 | do_test select4-9.10 { |
||
605 | execsql2 { |
||
606 | SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a) |
||
607 | WHERE b=2 |
||
608 | } |
||
609 | } {a 1 b 2} |
||
610 | do_test select4-9.11 { |
||
611 | execsql2 { |
||
612 | SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b) |
||
613 | WHERE b=2 |
||
614 | } |
||
615 | } {a 1 b 2} |
||
616 | do_test select4-9.12 { |
||
617 | execsql2 { |
||
618 | SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b) |
||
619 | WHERE b>0 |
||
620 | } |
||
621 | } {a 1 b 2 a 3 b 4} |
||
622 | } ;# ifcapable subquery |
||
623 | |||
624 | # Try combining DISTINCT, LIMIT, and OFFSET. Make sure they all work |
||
625 | # together. |
||
626 | # |
||
627 | do_test select4-10.1 { |
||
628 | execsql { |
||
629 | SELECT DISTINCT log FROM t1 ORDER BY log |
||
630 | } |
||
631 | } {0 1 2 3 4 5} |
||
632 | do_test select4-10.2 { |
||
633 | execsql { |
||
634 | SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4 |
||
635 | } |
||
636 | } {0 1 2 3} |
||
637 | do_test select4-10.3 { |
||
638 | execsql { |
||
639 | SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 |
||
640 | } |
||
641 | } {} |
||
642 | do_test select4-10.4 { |
||
643 | execsql { |
||
644 | SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 |
||
645 | } |
||
646 | } {0 1 2 3 4 5} |
||
647 | do_test select4-10.5 { |
||
648 | execsql { |
||
649 | SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2 |
||
650 | } |
||
651 | } {2 3 4 5} |
||
652 | do_test select4-10.6 { |
||
653 | execsql { |
||
654 | SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2 |
||
655 | } |
||
656 | } {2 3 4} |
||
657 | do_test select4-10.7 { |
||
658 | execsql { |
||
659 | SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20 |
||
660 | } |
||
661 | } {} |
||
662 | do_test select4-10.8 { |
||
663 | execsql { |
||
664 | SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3 |
||
665 | } |
||
666 | } {} |
||
667 | do_test select4-10.9 { |
||
668 | execsql { |
||
669 | SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1 |
||
670 | } |
||
671 | } {31 5} |
||
672 | |||
673 | # Make sure compound SELECTs with wildly different numbers of columns |
||
674 | # do not cause assertion faults due to register allocation issues. |
||
675 | # |
||
676 | do_test select4-11.1 { |
||
677 | catchsql { |
||
678 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
679 | UNION |
||
680 | SELECT x FROM t2 |
||
681 | } |
||
682 | } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
||
683 | do_test select4-11.2 { |
||
684 | catchsql { |
||
685 | SELECT x FROM t2 |
||
686 | UNION |
||
687 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
688 | } |
||
689 | } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
||
690 | do_test select4-11.3 { |
||
691 | catchsql { |
||
692 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
693 | UNION ALL |
||
694 | SELECT x FROM t2 |
||
695 | } |
||
696 | } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} |
||
697 | do_test select4-11.4 { |
||
698 | catchsql { |
||
699 | SELECT x FROM t2 |
||
700 | UNION ALL |
||
701 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
702 | } |
||
703 | } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} |
||
704 | do_test select4-11.5 { |
||
705 | catchsql { |
||
706 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
707 | EXCEPT |
||
708 | SELECT x FROM t2 |
||
709 | } |
||
710 | } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} |
||
711 | do_test select4-11.6 { |
||
712 | catchsql { |
||
713 | SELECT x FROM t2 |
||
714 | EXCEPT |
||
715 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
716 | } |
||
717 | } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} |
||
718 | do_test select4-11.7 { |
||
719 | catchsql { |
||
720 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
721 | INTERSECT |
||
722 | SELECT x FROM t2 |
||
723 | } |
||
724 | } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} |
||
725 | do_test select4-11.8 { |
||
726 | catchsql { |
||
727 | SELECT x FROM t2 |
||
728 | INTERSECT |
||
729 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
730 | } |
||
731 | } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} |
||
732 | |||
733 | do_test select4-11.11 { |
||
734 | catchsql { |
||
735 | SELECT x FROM t2 |
||
736 | UNION |
||
737 | SELECT x FROM t2 |
||
738 | UNION ALL |
||
739 | SELECT x FROM t2 |
||
740 | EXCEPT |
||
741 | SELECT x FROM t2 |
||
742 | INTERSECT |
||
743 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
744 | } |
||
745 | } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}} |
||
746 | do_test select4-11.12 { |
||
747 | catchsql { |
||
748 | SELECT x FROM t2 |
||
749 | UNION |
||
750 | SELECT x FROM t2 |
||
751 | UNION ALL |
||
752 | SELECT x FROM t2 |
||
753 | EXCEPT |
||
754 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
755 | EXCEPT |
||
756 | SELECT x FROM t2 |
||
757 | } |
||
758 | } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}} |
||
759 | do_test select4-11.13 { |
||
760 | catchsql { |
||
761 | SELECT x FROM t2 |
||
762 | UNION |
||
763 | SELECT x FROM t2 |
||
764 | UNION ALL |
||
765 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
766 | UNION ALL |
||
767 | SELECT x FROM t2 |
||
768 | EXCEPT |
||
769 | SELECT x FROM t2 |
||
770 | } |
||
771 | } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}} |
||
772 | do_test select4-11.14 { |
||
773 | catchsql { |
||
774 | SELECT x FROM t2 |
||
775 | UNION |
||
776 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
777 | UNION |
||
778 | SELECT x FROM t2 |
||
779 | UNION ALL |
||
780 | SELECT x FROM t2 |
||
781 | EXCEPT |
||
782 | SELECT x FROM t2 |
||
783 | } |
||
784 | } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
||
785 | do_test select4-11.15 { |
||
786 | catchsql { |
||
787 | SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2 |
||
788 | UNION |
||
789 | SELECT x FROM t2 |
||
790 | INTERSECT |
||
791 | SELECT x FROM t2 |
||
792 | UNION ALL |
||
793 | SELECT x FROM t2 |
||
794 | EXCEPT |
||
795 | SELECT x FROM t2 |
||
796 | } |
||
797 | } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
||
798 | |||
799 | do_test select4-12.1 { |
||
800 | sqlite3 db2 :memory: |
||
801 | catchsql { |
||
802 | SELECT 1 UNION SELECT 2,3 UNION SELECT 4,5 ORDER BY 1; |
||
803 | } db2 |
||
804 | } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}} |
||
805 | |||
806 | } ;# ifcapable compound |
||
807 | |||
808 | finish_test |