blob: 0f846091cd10c298c41e87b5326e397e6866a3dc (
plain)
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
|
--
-- UNION (also INTERSECT, EXCEPT)
--
-- Simple UNION constructs
SELECT 1 AS two UNION SELECT 2;
SELECT 1 AS one UNION SELECT 1;
SELECT 1 AS two UNION ALL SELECT 2;
SELECT 1 AS two UNION ALL SELECT 1;
SELECT 1 AS three UNION SELECT 2 UNION SELECT 3;
SELECT 1 AS two UNION SELECT 2 UNION SELECT 2;
SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2;
SELECT 1.1 AS two UNION SELECT 2.2;
-- Mixed types
SELECT 1.1 AS two UNION SELECT 2;
SELECT 1 AS two UNION SELECT 2.2;
SELECT 1 AS one UNION SELECT 1.0::float8;
SELECT 1.1 AS two UNION ALL SELECT 2;
SELECT 1.0::float8 AS two UNION ALL SELECT 1;
SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3;
SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8;
SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2;
SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2);
--
-- Try testing from tables...
--
SELECT f1 AS five FROM FLOAT8_TBL
UNION
SELECT f1 FROM FLOAT8_TBL;
SELECT f1 AS ten FROM FLOAT8_TBL
UNION ALL
SELECT f1 FROM FLOAT8_TBL;
SELECT f1 AS nine FROM FLOAT8_TBL
UNION
SELECT f1 FROM INT4_TBL;
SELECT f1 AS ten FROM FLOAT8_TBL
UNION ALL
SELECT f1 FROM INT4_TBL;
SELECT f1 AS five FROM FLOAT8_TBL
WHERE f1 BETWEEN -1e6 AND 1e6
UNION
SELECT f1 FROM INT4_TBL
WHERE f1 BETWEEN 0 AND 1000000;
SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
UNION
SELECT f1 FROM CHAR_TBL;
SELECT f1 AS three FROM VARCHAR_TBL
UNION
SELECT CAST(f1 AS varchar) FROM CHAR_TBL;
SELECT f1 AS eight FROM VARCHAR_TBL
UNION ALL
SELECT f1 FROM CHAR_TBL;
SELECT f1 AS five FROM TEXT_TBL
UNION
SELECT f1 FROM VARCHAR_TBL
UNION
SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL;
--
-- INTERSECT and EXCEPT
--
SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl;
SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl;
SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl;
SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl;
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
--
-- Mixed types
--
SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl;
--
-- Operator precedence and (((((extra))))) parentheses
--
SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl;
SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl)));
(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl;
--
-- Subqueries with ORDER BY & LIMIT clauses
--
-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
ORDER BY q2,q1;
-- This should fail, because q2 isn't a name of an EXCEPT output column
SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
-- But this should work:
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
--
-- New syntaxes (7.1) permit new tests
--
(((((select * from int8_tbl)))));
|
