wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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