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 --
 321 -- Mixed types
 322 --
 323 SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl;
 324  f1
 325 ----
 326   0
 327 (1 row)
 328
 329 SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
 330           f1
 331 -----------------------
 332  -1.2345678901234e+200
 333                -1004.3
 334                 -34.84
 335  -1.2345678901234e-200
 336 (4 rows)
 337
 338 --
 339 -- Operator precedence and (((((extra))))) parentheses
 340 --
 341 SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl;
 342         q1
 343 -------------------
 344   4567890123456789
 345                123
 346                456
 347   4567890123456789
 348                123
 349   4567890123456789
 350  -4567890123456789
 351 (7 rows)
 352
 353 SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl)));
 354         q1
 355 ------------------
 356  4567890123456789
 357               123
 358 (2 rows)
 359
 360 (((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl))) UNION ALL SELECT q2 FROM int8_tbl;
 361         q1
 362 -------------------
 363   4567890123456789
 364                123
 365                456
 366   4567890123456789
 367                123
 368   4567890123456789
 369  -4567890123456789
 370 (7 rows)
 371
 372 SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
 373         q1
 374 -------------------
 375  -4567890123456789
 376                456
 377 (2 rows)
 378
 379 SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
 380         q1
 381 -------------------
 382                123
 383                123
 384   4567890123456789
 385   4567890123456789
 386   4567890123456789
 387  -4567890123456789
 388                456
 389 (7 rows)
 390
 391 (((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
 392         q1
 393 -------------------
 394  -4567890123456789
 395                456
 396 (2 rows)
 397
 398 --
 399 -- Subqueries with ORDER BY & LIMIT clauses
 400 --
 401 -- In this syntax, ORDER BY/LIMIT apply to the result of the EXCEPT
 402 SELECT q1,q2 FROM int8_tbl EXCEPT SELECT q2,q1 FROM int8_tbl
 403 ORDER BY q2,q1;
 404         q1        |        q2
 405 ------------------+-------------------
 406  4567890123456789 | -4567890123456789
 407               123 |               456
 408 (2 rows)
 409
 410 -- This should fail, because q2 isn't a name of an EXCEPT output column
 411 SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
 412 ERROR:  column "q2" does not exist
 413 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
 414                                                              ^
 415 -- But this should work:
 416 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)));
 417         q1
 418 ------------------
 419  4567890123456789
 420               123
 421 (2 rows)
 422
 423 --
 424 -- New syntaxes (7.1) permit new tests
 425 --
 426 (((((select * from int8_tbl)))));
 427         q1        |        q2
 428 ------------------+-------------------
 429               123 |               456
 430               123 |  4567890123456789
 431  4567890123456789 |               123
 432  4567890123456789 |  4567890123456789
 433  4567890123456789 | -4567890123456789
 434 (5 rows)
 435
 436 --
 437 -- Check handling of a case with unknown constants.  We don't guarantee
 438 -- an undecorated constant will work in all cases, but historically this
 439 -- usage has worked, so test we don't break it.
 440 --
 441 SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
 442 UNION
 443 SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
 444 ORDER BY 1;
 445   f1
 446 ------
 447  a
 448  ab
 449  abcd
 450  test
 451 (4 rows)
 452
 453 -- This should fail, but it should produce an error cursor
 454 SELECT '3.4'::numeric UNION SELECT 'foo';
 455 ERROR:  invalid input syntax for type numeric: "foo"
 456 LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
 457                                            ^