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
|
# name: test/sql/setops/union_all_projection_pushdown.test
# description: Test projection pushdown in UNION ALL
# group: [setops]
statement ok
CREATE TABLE a AS SELECT i, i+1 AS j, i+2 AS k, i+3 AS l FROM range(42,43) t(i)
statement ok
CREATE TABLE b AS SELECT i, i+1 AS j, i+2 AS k, i+3 AS l FROM range(40,41) t(i)
query I
select COUNT(*) from (SELECT * FROM a UNION ALL SELECT * FROM b) t1 join (select 43) t2(l) on t1.l=t2.l
----
1
query I
select COUNT(*) from (SELECT * FROM a UNION ALL SELECT * FROM b) t1
----
2
query I
select COUNT(*) from (SELECT i, j, MIN(k), MIN(l) FROM a GROUP BY i, j UNION ALL SELECT * FROM b) t1
----
2
query I
select COUNT(*) from (SELECT DISTINCT i, j, k, l FROM a UNION ALL SELECT * FROM b) t1
----
2
query I
select COUNT(*) from (SELECT DISTINCT * FROM (SELECT DISTINCT i, j, k, l FROM a UNION ALL SELECT * FROM b) t1) t2
----
2
# multiple unions
query I
select COUNT(*) from (SELECT * FROM a UNION ALL SELECT * FROM b UNION ALL SELECT a.i,a.j,a.k,a.l FROM a,b) t1
----
3
# union
query I
select COUNT(*) from (SELECT * FROM a UNION SELECT * FROM b UNION SELECT a.i,a.j,a.k,a.l FROM a,b) t1
----
2
# empty result
query I
select COUNT(*) from (SELECT * FROM a WHERE 1=0 UNION ALL SELECT * FROM b) t1
----
1
query I
select j from (SELECT * FROM a WHERE i=42 UNION ALL SELECT * FROM b) t1 WHERE i=40
----
41
query I
select j from (SELECT i, COUNT(*) j, COUNT(*) k, COUNT(*) l FROM a WHERE i=42 GROUP BY i UNION ALL SELECT * FROM b) t1 WHERE i=40
----
41
query I
select j from (SELECT i i, i j, k, l FROM a UNION ALL SELECT * FROM b) t1 WHERE i=40 AND j=41
----
41
query I
select * from (select 'hello' i union all select 'h' i) t1 where i='h'
----
h
query I
select COUNT(*) from (SELECT * FROM a UNION ALL SELECT * FROM b) t1 join (select 43) t2(l) on t1.l=t2.l
----
1
query I
select MIN(t2.b) from (SELECT * FROM a UNION ALL SELECT * FROM b) t1 join (select 43, 'hello', 44, 'world') t2(l, a, b, c) on t1.l=t2.l
----
44
|
