1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
# name: test/sql/setops/test_setops.test
# description: Test UNION/EXCEPT/INTERSECT
# group: [setops]
statement ok
PRAGMA enable_verification
query I
SELECT 1 UNION ALL SELECT 2
----
1
2
query IT
SELECT 1, 'a' UNION ALL SELECT 2, 'b'
----
1 a
2 b
query IT
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c' order by 1
----
1 a
2 b
3 c
query IT
SELECT 1, 'a' UNION ALL SELECT 2, 'b' UNION ALL SELECT 3, 'c' UNION ALL SELECT 4, 'd' order by 1
----
1 a
2 b
3 c
4 d
# UNION/EXCEPT/INTERSECT with NULL values
query I
SELECT NULL UNION SELECT NULL
----
NULL
query I
SELECT NULL EXCEPT SELECT NULL
----
query I
SELECT NULL INTERSECT SELECT NULL
----
NULL
# create tables
statement ok
CREATE TABLE test (a INTEGER, b INTEGER);
statement ok
INSERT INTO test VALUES (11, 1), (12, 1), (13, 2)
# UNION ALL, no unique results
query I
SELECT a FROM test WHERE a < 13 UNION ALL SELECT a FROM test WHERE a = 13
----
11
12
13
query I
SELECT b FROM test WHERE a < 13 UNION ALL SELECT b FROM test WHERE a > 11
----
1
1
1
2
# mixing types, should error
query I
SELECT 1 UNION ALL SELECT 'asdf'
----
1
asdf
query T
SELECT NULL UNION ALL SELECT 'asdf'
----
NULL
asdf
# only UNION, distinct results
query I
SELECT 1 UNION SELECT 1
----
1
query IT
SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 3, 'c' UNION SELECT 1, 'a' order by 1
----
1 a
2 b
3 c
query I
SELECT b FROM test WHERE a < 13 UNION SELECT b FROM test WHERE a > 11 ORDER BY 1
----
1
2
# mixed fun
query IT
SELECT 1, 'a' UNION ALL SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 1, 'a' ORDER BY 1
----
1 a
2 b
query IT
SELECT 1, 'a' UNION ALL SELECT 1, 'a' UNION SELECT 2, 'b' UNION SELECT 1, 'a' ORDER BY 1 DESC
----
2 b
1 a
# EXCEPT ALL / INTERSECT ALL
query II
select x, count(*) as c
from ((select * from (values(1),(2),(2),(3),(3),(3),(4),(4),(4),(4)) s(x) except all select * from (values(1),(3),(3)) t(x)) intersect all select * from (values(2),(2),(2),(4),(3),(3)) u(x)) s
group by x order by x
----
2 2
3 1
4 1
|
