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
|
# name: test/sql/setops/value_union.test
# description: Test union of VALUES
# group: [setops]
statement ok
PRAGMA enable_verification
statement ok
CREATE VIEW vals AS SELECT * FROM (VALUES (1, 10), (2, 5), (3, 4)) tbl(i, j);
query II
SELECT * FROM vals
----
1 10
2 5
3 4
statement ok
CREATE VIEW vunion AS
SELECT * FROM vals
UNION ALL
SELECT * FROM vals;
query II
SELECT * FROM vunion ORDER BY i
----
1 10
1 10
2 5
2 5
3 4
3 4
query II
SELECT * FROM vunion ORDER BY i LIMIT 1
----
1 10
query II
SELECT * FROM (SELECT * FROM vunion ORDER BY i LIMIT 4) tbl ORDER BY j LIMIT 2
----
2 5
2 5
query II
SELECT * FROM vunion WHERE i=1
----
1 10
1 10
query II
SELECT DISTINCT * FROM (SELECT * FROM vunion UNION ALL SELECT * FROM vunion) tbl ORDER BY 1
----
1 10
2 5
3 4
query II
SELECT * FROM (SELECT * FROM vunion INTERSECT SELECT * FROM vunion) tbl ORDER BY 1;
----
1 10
2 5
3 4
query II
SELECT * FROM (SELECT * FROM vunion EXCEPT SELECT * FROM vunion) tbl
----
|
