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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
# name: test/sql/setops/test_union_by_name.test
# description: test union by name operations
# group: [setops]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE t1 (x INT, y INT);
statement ok
INSERT INTO t1 VALUES (3, 3), (3, 3), (1, 1);
statement ok
CREATE TABLE t2 (y INT, z INT);
statement ok
INSERT INTO t2 VALUES (2, 2), (4, 4);
########## test binding
query I
SELECT t1.x FROM t1 UNION BY NAME SELECT x FROM t1 ORDER BY t1.x;
----
1
3
query I
SELECT x FROM t1 UNION BY NAME SELECT x FROM t1 ORDER BY t1.x;
----
1
3
query II
(SELECT x FROM t1 UNION ALL SELECT x FROM t1) UNION BY NAME SELECT 5 ORDER BY t1.x;
----
NULL 5
1 NULL
3 NULL
query II
(SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION BY NAME SELECT 5 ORDER BY y;
----
NULL 5
1 NULL
3 NULL
# Ambiguous name
statement error
SELECT x AS a FROM t1 UNION BY NAME SELECT x AS b FROM t1 ORDER BY t1.x;
----
<REGEX>:Binder Error.*Ambiguous reference to column.*
query II
(SELECT x FROM t1 UNION ALL SELECT y FROM t1) UNION BY NAME (SELECT z FROM t2 UNION ALL SELECT y FROM t2) ORDER BY y, z;
----
NULL 2
NULL 4
1 NULL
3 NULL
########## test limit
query III
SELECT 1 UNION BY NAME SELECT * FROM range(2, 100) UNION BY NAME SELECT 999 ORDER BY #2, #1 LIMIT 5;
----
NULL NULL 999
1 NULL NULL
NULL 2 NULL
NULL 3 NULL
NULL 4 NULL
########## test order by
query III
SELECT x, y FROM t1 UNION BY NAME SELECT y, z FROM t2 ORDER BY y;
----
1 1 NULL
NULL 2 2
3 3 NULL
NULL 4 4
query III
SELECT x, y FROM t1 UNION BY NAME SELECT y, z FROM t2 ORDER BY 3, 1;
----
1 1 NULL
3 3 NULL
NULL 2 2
NULL 4 4
statement error
SELECT x, y FROM t1 UNION BY NAME SELECT y, z FROM t2 ORDER BY 4;
----
<REGEX>:Binder Error.*out of range.*
########## multi set operations
query IIII
(SELECT 1 UNION BY NAME SELECT x, y FROM t1) UNION BY NAME SELECT y, z FROM t2 ORDER BY ALL;
----
NULL NULL 2 2
NULL NULL 4 4
NULL 1 1 NULL
NULL 3 3 NULL
1 NULL NULL NULL
query III
SELECT x, y FROM t1 UNION BY NAME (SELECT y, z FROM t2 INTERSECT SELECT 2, 2 FROM t1 ORDER BY #1) ORDER BY #1;
----
NULL 2 2
1 1 NULL
3 3 NULL
query III
(SELECT x, y FROM t1 UNION BY NAME SELECT y, z FROM t2 ORDER BY #1) EXCEPT SELECT NULL, 2, 2 FROM t1 ORDER BY #1;
----
NULL 4 4
1 1 NULL
3 3 NULL
########## same name in select list
query III
SELECT x, x as a FROM t1 UNION BY NAME SELECT y FROM t2 ORDER BY #1, #3;
----
NULL NULL 2
NULL NULL 4
1 1 NULL
3 3 NULL
########## alias name in select list
query II
SELECT x as a FROM t1 UNION BY NAME SELECT x FROM t1 ORDER BY #1, #2;
----
NULL 1
NULL 3
1 NULL
3 NULL
# union by name with different types
query I
select '0' as c union all select 0 as c;
----
0
0
query I
select '0' as c union all by name select 0 as c;
----
0
0
query I
select {'a': '0'} as c union all by name select {'a': 0} as c
----
{'a': 0}
{'a': 0}
query I
SELECT {'a': 'hello'} AS c UNION ALL BY NAME SELECT {'b': 'hello'} AS c;
----
{'a': hello, 'b': NULL}
{'a': NULL, 'b': hello}
query I
SELECT {'a': 'hello'} AS c UNION ALL BY NAME SELECT {'a': 'hello', 'b': 'world'} AS c;
----
{'a': hello, 'b': NULL}
{'a': hello, 'b': world}
query I
SELECT [{'a': 42}, {'b': 84}];
----
[{'a': 42, 'b': NULL}, {'a': NULL, 'b': 84}]
|
