granicus.if.org Git - postgresql/blob - src/test/regress/expected/union.out

   1 --
   2 -- UNION (also INTERSECT, EXCEPT)
   3 --
   4 -- Simple UNION constructs
   5 SELECT 1 AS two UNION SELECT 2;
   6  two
   7 -----
   8    1
   9    2
  10 (2 rows)
  11
  12 SELECT 1 AS one UNION SELECT 1;
  13  one
  14 -----
  15    1
  16 (1 row)
  17
  18 SELECT 1 AS two UNION ALL SELECT 2;
  19  two
  20 -----
  21    1
  22    2
  23 (2 rows)
  24
  25 SELECT 1 AS two UNION ALL SELECT 1;
  26  two
  27 -----
  28    1
  29    1
  30 (2 rows)
  31
  32 SELECT 1 AS three UNION SELECT 2 UNION SELECT 3;
  33  three
  34 -------
  35      1
  36      2
  37      3
  38 (3 rows)
  39
  40 SELECT 1 AS two UNION SELECT 2 UNION SELECT 2;
  41  two
  42 -----
  43    1
  44    2
  45 (2 rows)
  46
  47 SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2;
  48  three
  49 -------
  50      1
  51      2
  52      2
  53 (3 rows)
  54
  55 SELECT 1.1 AS two UNION SELECT 2.2;
  56  two
  57 -----
  58  1.1
  59  2.2
  60 (2 rows)
  61
  62 -- Mixed types
  63 SELECT 1.1 AS two UNION SELECT 2;
  64  two
  65 -----
  66  1.1
  67    2
  68 (2 rows)
  69
  70 SELECT 1 AS two UNION SELECT 2.2;
  71  two
  72 -----
  73    1
  74  2.2
  75 (2 rows)
  76
  77 SELECT 1 AS one UNION SELECT 1.0::float8;
  78  one
  79 -----
  80    1
  81 (1 row)
  82
  83 SELECT 1.1 AS two UNION ALL SELECT 2;
  84  two
  85 -----
  86  1.1
  87    2
  88 (2 rows)
  89
  90 SELECT 1.0::float8 AS two UNION ALL SELECT 1;
  91  two
  92 -----
  93    1
  94    1
  95 (2 rows)
  96
  97 SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3;
  98  three
  99 -------
 100    1.1
 101      2
 102      3
 103 (3 rows)
 104
 105 SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
 106  two
 107 -----
 108  1.1
 109    2
 110 (2 rows)
 111
 112 SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2;
 113  three
 114 -------
 115    1.1
 116      2
 117      2
 118 (3 rows)
 119
 120 SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2);
 121  two
 122 -----
 123  1.1
 124    2
 125 (2 rows)
 126
 127 --
 128 -- Try testing from tables...
 129 --
 130 SELECT f1 AS five FROM FLOAT8_TBL
 131 UNION
 132 SELECT f1 FROM FLOAT8_TBL
 133 ORDER BY 1;
 134          five
 135 -----------------------
 136  -1.2345678901234e+200
 137                -1004.3
 138                 -34.84
 139  -1.2345678901234e-200
 140                      0
 141 (5 rows)
 142
 143 SELECT f1 AS ten FROM FLOAT8_TBL
 144 UNION ALL
 145 SELECT f1 FROM FLOAT8_TBL;
 146           ten
 147 -----------------------
 148                      0
 149                 -34.84
 150                -1004.3
 151  -1.2345678901234e+200
 152  -1.2345678901234e-200
 153                      0
 154                 -34.84
 155                -1004.3
 156  -1.2345678901234e+200
 157  -1.2345678901234e-200
 158 (10 rows)
 159
 160 SELECT f1 AS nine FROM FLOAT8_TBL
 161 UNION
 162 SELECT f1 FROM INT4_TBL
 163 ORDER BY 1;
 164          nine
 165 -----------------------
 166  -1.2345678901234e+200
 167            -2147483647
 168                -123456
 169                -1004.3
 170                 -34.84
 171  -1.2345678901234e-200
 172                      0
 173                 123456
 174             2147483647
 175 (9 rows)
 176
 177 SELECT f1 AS ten FROM FLOAT8_TBL
 178 UNION ALL
 179 SELECT f1 FROM INT4_TBL;
 180           ten
 181 -----------------------
 182                      0
 183                 -34.84
 184                -1004.3
 185  -1.2345678901234e+200
 186  -1.2345678901234e-200
 187                      0
 188                 123456
 189                -123456
 190             2147483647
 191            -2147483647
 192 (10 rows)
 193
 194 SELECT f1 AS five FROM FLOAT8_TBL
 195   WHERE f1 BETWEEN -1e6 AND 1e6
 196 UNION
 197 SELECT f1 FROM INT4_TBL
 198   WHERE f1 BETWEEN 0 AND 1000000;
 199          five
 200 -----------------------
 201                -1004.3
 202                 -34.84
 203  -1.2345678901234e-200
 204                      0
 205                 123456
 206 (5 rows)
 207
 208 SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
 209 UNION
 210 SELECT f1 FROM CHAR_TBL
 211 ORDER BY 1;
 212  three
 213 -------
 214  a
 215  ab
 216  abcd
 217 (3 rows)
 218
 219 SELECT f1 AS three FROM VARCHAR_TBL
 220 UNION
 221 SELECT CAST(f1 AS varchar) FROM CHAR_TBL
 222 ORDER BY 1;
 223  three
 224 -------
 225  a
 226  ab
 227  abcd
 228 (3 rows)
 229
 230 SELECT f1 AS eight FROM VARCHAR_TBL
 231 UNION ALL
 232 SELECT f1 FROM CHAR_TBL;
 233  eight
 234 -------
 235  a
 236  ab
 237  abcd
 238  abcd
 239  a
 240  ab
 241  abcd
 242  abcd
 243 (8 rows)
 244
 245 SELECT f1 AS five FROM TEXT_TBL
 246 UNION
 247 SELECT f1 FROM VARCHAR_TBL
 248 UNION
 249 SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
 250 ORDER BY 1;
 251        five
 252 -------------------
 253  a
 254  ab
 255  abcd
 256  doh!
 257  hi de ho neighbor
 258 (5 rows)
 259
 260 --
 261 -- INTERSECT and EXCEPT
 262 --
 263 SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl;
 264         q2
 265 ------------------
 266  4567890123456789
 267               123
 268 (2 rows)
 269
 270 SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl;
 271         q2
 272 ------------------
 273  4567890123456789
 274  4567890123456789
 275               123
 276 (3 rows)
 277
 278 SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
 279         q2
 280 -------------------
 281  -4567890123456789
 282                456
 283 (2 rows)
 284
 285 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
 286         q2
 287 -------------------
 288  -4567890123456789
 289                456
 290 (2 rows)
 291
 292 SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
 293         q2
 294 -------------------
 295  -4567890123456789
 296                456
 297   4567890123456789
 298 (3 rows)
 299
 300 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl;
 301  q1
 302 ----
 303 (0 rows)
 304
 305 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl;
 306         q1
 307 ------------------
 308  4567890123456789
 309               123
 310 (2 rows)
 311
 312 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
 313         q1
 314 ------------------
 315  4567890123456789
 316  4567890123456789
 317               123
 318 (3 rows)
 319
 320 SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
 321 ERROR:  FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
 322 --
 323 -- Mixed types
 324 --
 325 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
 326  f1
 327 ----
 328   0
 329 (1 row)
 330
 331 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
 332           f1
 333 -----------------------
 334  -1.2345678901234e+200
 335                -1004.3
 336                 -34.84
 337  -1.2345678901234e-200
 338 (4 rows)
 339
 340 --
 341 -- Operator precedence and (((((extra))))) parentheses
 342 --
 343 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
 344         q1
 345 -------------------
 346   4567890123456789
 347                123
 348                456
 349   4567890123456789
 350                123
 351   4567890123456789
 352  -4567890123456789
 353 (7 rows)
 354
 355 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
 356         q1
 357 ------------------
 358  4567890123456789
 359               123
 360 (2 rows)
 361
 362 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
 363         q1
 364 -------------------
 365   4567890123456789
 366                123
 367                456
 368   4567890123456789
 369                123
 370   4567890123456789
 371  -4567890123456789
 372 (7 rows)
 373
 374 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
 375         q1
 376 -------------------
 377  -4567890123456789
 378                456
 379 (2 rows)
 380
 381 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
 382         q1
 383 -------------------
 384                123
 385                123
 386   4567890123456789
 387   4567890123456789
 388   4567890123456789
 389  -4567890123456789
 390                456
 391 (7 rows)
 392
 393 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
 394         q1
 395 -------------------
 396  -4567890123456789
 397                456
 398 (2 rows)
 399
 400 --
 401 -- Subqueries with ORDER BY & LIMIT clauses
 402 --
 403 -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
 404 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
 405 ORDER BY q2,q1;
 406         q1        |        q2
 407 ------------------+-------------------
 408  4567890123456789 | -4567890123456789
 409               123 |               456
 410 (2 rows)
 411
 412 -- This should fail, because q2 isn't a name of an EXCEPT output column
 413 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
 414 ERROR:  column "q2" does not exist
 415 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
 416                                                              ^
 417 HINT:  There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
 418 -- But this should work:
 419 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
 420         q1
 421 ------------------
 422  4567890123456789
 423               123
 424 (2 rows)
 425
 426 --
 427 -- New syntaxes (7.1) permit new tests
 428 --
 429 (((((select * from int8_tbl)))));
 430         q1        |        q2
 431 ------------------+-------------------
 432               123 |               456
 433               123 |  4567890123456789
 434  4567890123456789 |               123
 435  4567890123456789 |  4567890123456789
 436  4567890123456789 | -4567890123456789
 437 (5 rows)
 438
 439 --
 440 -- Check handling of a case with unknown constants.  We don't guarantee
 441 -- an undecorated constant will work in all cases, but historically this
 442 -- usage has worked, so test we don't break it.
 443 --
 444 SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
 445 UNION
 446 SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
 447 ORDER BY 1;
 448   f1
 449 ------
 450  a
 451  ab
 452  abcd
 453  test
 454 (4 rows)
 455
 456 -- This should fail, but it should produce an error cursor
 457 SELECT '3.4'::numeric UNION SELECT 'foo';
 458 ERROR:  invalid input syntax for type numeric: "foo"
 459 LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
 460                                            ^
 461 --
 462 -- Test that expression-index constraints can be pushed down through
 463 -- UNION or UNION ALL
 464 --
 465 CREATE TEMP TABLE t1 (a text, b text);
 466 CREATE INDEX t1_ab_idx on t1 ((a || b));
 467 CREATE TEMP TABLE t2 (ab text primary key);
 468 INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y');
 469 INSERT INTO t2 VALUES ('ab'), ('xy');
 470 set enable_seqscan = off;
 471 set enable_indexscan = on;
 472 set enable_bitmapscan = off;
 473 explain (costs off)
 474  SELECT * FROM
 475  (SELECT a || b AS ab FROM t1
 476   UNION ALL
 477   SELECT * FROM t2) t
 478  WHERE ab = 'ab';
 479                  QUERY PLAN
 480 ---------------------------------------------
 481  Append
 482    ->  Index Scan using t1_ab_idx on t1
 483          Index Cond: ((a || b) = 'ab'::text)
 484    ->  Index Only Scan using t2_pkey on t2
 485          Index Cond: (ab = 'ab'::text)
 486 (5 rows)
 487
 488 explain (costs off)
 489  SELECT * FROM
 490  (SELECT a || b AS ab FROM t1
 491   UNION
 492   SELECT * FROM t2) t
 493  WHERE ab = 'ab';
 494                     QUERY PLAN
 495 ---------------------------------------------------
 496  HashAggregate
 497    Group Key: ((t1.a || t1.b))
 498    ->  Append
 499          ->  Index Scan using t1_ab_idx on t1
 500                Index Cond: ((a || b) = 'ab'::text)
 501          ->  Index Only Scan using t2_pkey on t2
 502                Index Cond: (ab = 'ab'::text)
 503 (7 rows)
 504
 505 reset enable_seqscan;
 506 reset enable_indexscan;
 507 reset enable_bitmapscan;
 508 -- Test constraint exclusion of UNION ALL subqueries
 509 explain (costs off)
 510  SELECT * FROM
 511   (SELECT 1 AS t, * FROM tenk1 a
 512    UNION ALL
 513    SELECT 2 AS t, * FROM tenk1 b) c
 514  WHERE t = 2;
 515         QUERY PLAN
 516 ---------------------------
 517  Append
 518    ->  Seq Scan on tenk1 b
 519 (2 rows)
 520
 521 -- Test that we push quals into UNION sub-selects only when it's safe
 522 explain (costs off)
 523 SELECT * FROM
 524   (SELECT 1 AS t, 2 AS x
 525    UNION
 526    SELECT 2 AS t, 4 AS x) ss
 527 WHERE x < 4;
 528                  QUERY PLAN
 529 --------------------------------------------
 530  Unique
 531    ->  Sort
 532          Sort Key: (1), (2)
 533          ->  Append
 534                ->  Result
 535                ->  Result
 536                      One-Time Filter: false
 537 (7 rows)
 538
 539 SELECT * FROM
 540   (SELECT 1 AS t, 2 AS x
 541    UNION
 542    SELECT 2 AS t, 4 AS x) ss
 543 WHERE x < 4;
 544  t | x
 545 ---+---
 546  1 | 2
 547 (1 row)
 548
 549 explain (costs off)
 550 SELECT * FROM
 551   (SELECT 1 AS t, generate_series(1,10) AS x
 552    UNION
 553    SELECT 2 AS t, 4 AS x) ss
 554 WHERE x < 4
 555 ORDER BY x;
 556                        QUERY PLAN
 557 --------------------------------------------------------
 558  Sort
 559    Sort Key: ss.x
 560    ->  Subquery Scan on ss
 561          Filter: (ss.x < 4)
 562          ->  HashAggregate
 563                Group Key: (1), (generate_series(1, 10))
 564                ->  Append
 565                      ->  Result
 566                      ->  Result
 567 (9 rows)
 568
 569 SELECT * FROM
 570   (SELECT 1 AS t, generate_series(1,10) AS x
 571    UNION
 572    SELECT 2 AS t, 4 AS x) ss
 573 WHERE x < 4
 574 ORDER BY x;
 575  t | x
 576 ---+---
 577  1 | 1
 578  1 | 2
 579  1 | 3
 580 (3 rows)
 581
 582 explain (costs off)
 583 SELECT * FROM
 584   (SELECT 1 AS t, (random()*3)::int AS x
 585    UNION
 586    SELECT 2 AS t, 4 AS x) ss
 587 WHERE x > 3;
 588                                  QUERY PLAN
 589 ----------------------------------------------------------------------------
 590  Subquery Scan on ss
 591    Filter: (ss.x > 3)
 592    ->  Unique
 593          ->  Sort
 594                Sort Key: (1), (((random() * 3::double precision))::integer)
 595                ->  Append
 596                      ->  Result
 597                      ->  Result
 598 (8 rows)
 599
 600 SELECT * FROM
 601   (SELECT 1 AS t, (random()*3)::int AS x
 602    UNION
 603    SELECT 2 AS t, 4 AS x) ss
 604 WHERE x > 3;
 605  t | x
 606 ---+---
 607  2 | 4
 608 (1 row)
 609
 610 -- Test proper handling of parameterized appendrel paths when the
 611 -- potential join qual is expensive
 612 create function expensivefunc(int) returns int
 613 language plpgsql immutable strict cost 10000
 614 as $$begin return $1; end$$;
 615 create temp table t3 as select generate_series(-1000,1000) as x;
 616 create index t3i on t3 (expensivefunc(x));
 617 analyze t3;
 618 explain (costs off)
 619 select * from
 620   (select * from t3 a union all select * from t3 b) ss
 621   join int4_tbl on f1 = expensivefunc(x);
 622                          QUERY PLAN
 623 ------------------------------------------------------------
 624  Nested Loop
 625    ->  Seq Scan on int4_tbl
 626    ->  Append
 627          ->  Index Scan using t3i on t3 a
 628                Index Cond: (expensivefunc(x) = int4_tbl.f1)
 629          ->  Index Scan using t3i on t3 b
 630                Index Cond: (expensivefunc(x) = int4_tbl.f1)
 631 (7 rows)
 632
 633 select * from
 634   (select * from t3 a union all select * from t3 b) ss
 635   join int4_tbl on f1 = expensivefunc(x);
 636  x | f1
 637 ---+----
 638  0 |  0
 639  0 |  0
 640 (2 rows)
 641
 642 drop table t3;
 643 drop function expensivefunc(int);