1-- 2-- UNION (also INTERSECT, EXCEPT) 3-- 4 5-- Simple UNION constructs 6 7SELECT 1 AS two UNION SELECT 2; 8 9SELECT 1 AS one UNION SELECT 1; 10 11SELECT 1 AS two UNION ALL SELECT 2; 12 13SELECT 1 AS two UNION ALL SELECT 1; 14 15SELECT 1 AS three UNION SELECT 2 UNION SELECT 3; 16 17SELECT 1 AS two UNION SELECT 2 UNION SELECT 2; 18 19SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2; 20 21SELECT 1.1 AS two UNION SELECT 2.2; 22 23-- Mixed types 24 25SELECT 1.1 AS two UNION SELECT 2; 26 27SELECT 1 AS two UNION SELECT 2.2; 28 29SELECT 1 AS one UNION SELECT 1.0::float8; 30 31SELECT 1.1 AS two UNION ALL SELECT 2; 32 33SELECT 1.0::float8 AS two UNION ALL SELECT 1; 34 35SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3; 36 37SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1; 38 39SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2; 40 41SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2); 42 43-- 44-- Try testing from tables... 45-- 46 47SELECT f1 AS five FROM FLOAT8_TBL 48UNION 49SELECT f1 FROM FLOAT8_TBL 50ORDER BY 1; 51 52SELECT f1 AS ten FROM FLOAT8_TBL 53UNION ALL 54SELECT f1 FROM FLOAT8_TBL; 55 56SELECT f1 AS nine FROM FLOAT8_TBL 57UNION 58SELECT f1 FROM INT4_TBL 59ORDER BY 1; 60 61SELECT f1 AS ten FROM FLOAT8_TBL 62UNION ALL 63SELECT f1 FROM INT4_TBL; 64 65SELECT f1 AS five FROM FLOAT8_TBL 66 WHERE f1 BETWEEN -1e6 AND 1e6 67UNION 68SELECT f1 FROM INT4_TBL 69 WHERE f1 BETWEEN 0 AND 1000000; 70 71SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL 72UNION 73SELECT f1 FROM CHAR_TBL 74ORDER BY 1; 75 76SELECT f1 AS three FROM VARCHAR_TBL 77UNION 78SELECT CAST(f1 AS varchar) FROM CHAR_TBL 79ORDER BY 1; 80 81SELECT f1 AS eight FROM VARCHAR_TBL 82UNION ALL 83SELECT f1 FROM CHAR_TBL; 84 85SELECT f1 AS five FROM TEXT_TBL 86UNION 87SELECT f1 FROM VARCHAR_TBL 88UNION 89SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL 90ORDER BY 1; 91 92-- 93-- INTERSECT and EXCEPT 94-- 95 96SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl; 97 98SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl; 99 100SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; 101 102SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1; 103 104SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1; 105 106SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl; 107 108SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl; 109 110SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl; 111 112SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE; 113 114-- nested cases 115(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6; 116(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6; 117(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6; 118(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6; 119 120-- 121-- Mixed types 122-- 123 124SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl; 125 126SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1; 127 128-- 129-- Operator precedence and (((((extra))))) parentheses 130-- 131 132SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl; 133 134SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))); 135 136(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl; 137 138SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; 139 140SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1))); 141 142(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1; 143 144-- 145-- Subqueries with ORDER BY & LIMIT clauses 146-- 147 148-- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT 149SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl 150ORDER BY q2,q1; 151 152-- This should fail, because q2 isn't a name of an EXCEPT output column 153SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1; 154 155-- But this should work: 156SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))); 157 158-- 159-- New syntaxes (7.1) permit new tests 160-- 161 162(((((select * from int8_tbl))))); 163 164-- 165-- Check handling of a case with unknown constants. We don't guarantee 166-- an undecorated constant will work in all cases, but historically this 167-- usage has worked, so test we don't break it. 168-- 169 170SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a 171UNION 172SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b 173ORDER BY 1; 174 175-- This should fail, but it should produce an error cursor 176SELECT '3.4'::numeric UNION SELECT 'foo'; 177 178-- 179-- Test that expression-index constraints can be pushed down through 180-- UNION or UNION ALL 181-- 182 183CREATE TEMP TABLE t1 (a text, b text); 184CREATE INDEX t1_ab_idx on t1 ((a || b)); 185CREATE TEMP TABLE t2 (ab text primary key); 186INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y'); 187INSERT INTO t2 VALUES ('ab'), ('xy'); 188 189set enable_seqscan = off; 190set enable_indexscan = on; 191set enable_bitmapscan = off; 192 193explain (costs off) 194 SELECT * FROM 195 (SELECT a || b AS ab FROM t1 196 UNION ALL 197 SELECT * FROM t2) t 198 WHERE ab = 'ab'; 199 200explain (costs off) 201 SELECT * FROM 202 (SELECT a || b AS ab FROM t1 203 UNION 204 SELECT * FROM t2) t 205 WHERE ab = 'ab'; 206 207-- 208-- Test that ORDER BY for UNION ALL can be pushed down to inheritance 209-- children. 210-- 211 212CREATE TEMP TABLE t1c (b text, a text); 213ALTER TABLE t1c INHERIT t1; 214CREATE TEMP TABLE t2c (primary key (ab)) INHERITS (t2); 215INSERT INTO t1c VALUES ('v', 'w'), ('c', 'd'), ('m', 'n'), ('e', 'f'); 216INSERT INTO t2c VALUES ('vw'), ('cd'), ('mn'), ('ef'); 217CREATE INDEX t1c_ab_idx on t1c ((a || b)); 218 219set enable_seqscan = on; 220set enable_indexonlyscan = off; 221 222explain (costs off) 223 SELECT * FROM 224 (SELECT a || b AS ab FROM t1 225 UNION ALL 226 SELECT ab FROM t2) t 227 ORDER BY 1 LIMIT 8; 228 229 SELECT * FROM 230 (SELECT a || b AS ab FROM t1 231 UNION ALL 232 SELECT ab FROM t2) t 233 ORDER BY 1 LIMIT 8; 234 235reset enable_seqscan; 236reset enable_indexscan; 237reset enable_bitmapscan; 238 239-- This simpler variant of the above test has been observed to fail differently 240 241create table events (event_id int primary key); 242create table other_events (event_id int primary key); 243create table events_child () inherits (events); 244 245explain (costs off) 246select event_id 247 from (select event_id from events 248 union all 249 select event_id from other_events) ss 250 order by event_id; 251 252drop table events_child, events, other_events; 253 254reset enable_indexonlyscan; 255 256-- Test constraint exclusion of UNION ALL subqueries 257explain (costs off) 258 SELECT * FROM 259 (SELECT 1 AS t, * FROM tenk1 a 260 UNION ALL 261 SELECT 2 AS t, * FROM tenk1 b) c 262 WHERE t = 2; 263 264-- Test that we push quals into UNION sub-selects only when it's safe 265explain (costs off) 266SELECT * FROM 267 (SELECT 1 AS t, 2 AS x 268 UNION 269 SELECT 2 AS t, 4 AS x) ss 270WHERE x < 4; 271 272SELECT * FROM 273 (SELECT 1 AS t, 2 AS x 274 UNION 275 SELECT 2 AS t, 4 AS x) ss 276WHERE x < 4; 277 278explain (costs off) 279SELECT * FROM 280 (SELECT 1 AS t, generate_series(1,10) AS x 281 UNION 282 SELECT 2 AS t, 4 AS x) ss 283WHERE x < 4 284ORDER BY x; 285 286SELECT * FROM 287 (SELECT 1 AS t, generate_series(1,10) AS x 288 UNION 289 SELECT 2 AS t, 4 AS x) ss 290WHERE x < 4 291ORDER BY x; 292 293explain (costs off) 294SELECT * FROM 295 (SELECT 1 AS t, (random()*3)::int AS x 296 UNION 297 SELECT 2 AS t, 4 AS x) ss 298WHERE x > 3; 299 300SELECT * FROM 301 (SELECT 1 AS t, (random()*3)::int AS x 302 UNION 303 SELECT 2 AS t, 4 AS x) ss 304WHERE x > 3; 305 306-- Test cases where the native ordering of a sub-select has more pathkeys 307-- than the outer query cares about 308explain (costs off) 309select distinct q1 from 310 (select distinct * from int8_tbl i81 311 union all 312 select distinct * from int8_tbl i82) ss 313where q2 = q2; 314 315select distinct q1 from 316 (select distinct * from int8_tbl i81 317 union all 318 select distinct * from int8_tbl i82) ss 319where q2 = q2; 320 321explain (costs off) 322select distinct q1 from 323 (select distinct * from int8_tbl i81 324 union all 325 select distinct * from int8_tbl i82) ss 326where -q1 = q2; 327 328select distinct q1 from 329 (select distinct * from int8_tbl i81 330 union all 331 select distinct * from int8_tbl i82) ss 332where -q1 = q2; 333 334-- Test proper handling of parameterized appendrel paths when the 335-- potential join qual is expensive 336create function expensivefunc(int) returns int 337language plpgsql immutable strict cost 10000 338as $$begin return $1; end$$; 339 340create temp table t3 as select generate_series(-1000,1000) as x; 341create index t3i on t3 (expensivefunc(x)); 342analyze t3; 343 344explain (costs off) 345select * from 346 (select * from t3 a union all select * from t3 b) ss 347 join int4_tbl on f1 = expensivefunc(x); 348select * from 349 (select * from t3 a union all select * from t3 b) ss 350 join int4_tbl on f1 = expensivefunc(x); 351 352drop table t3; 353drop function expensivefunc(int); 354