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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select4.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test