src/test/regress/expected/union.out - master - githubmirror/postgres

src/test/regress/expected/union.out
1 707 строк45 KB
Richard Guo
Add an enable_groupagg GUC parameter
09 июл 2026, 03:44
09 июл 2026, 03:44e01b23b
--
-- UNION (also INTERSECT, EXCEPT)
--
-- Simple UNION constructs
SELECT 1 AS two UNION SELECT 2 ORDER BY 1;
 two 
-----
   1
   2
(2 rows)

SELECT 1 AS one UNION SELECT 1 ORDER BY 1;
 one 
-----
   1
(1 row)

SELECT 1 AS two UNION ALL SELECT 2;
 two 
-----
   1
   2
(2 rows)

SELECT 1 AS two UNION ALL SELECT 1;
 two 
-----
   1
   1
(2 rows)

SELECT 1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
 three 
-------
     1
     2
     3
(3 rows)

SELECT 1 AS two UNION SELECT 2 UNION SELECT 2 ORDER BY 1;
 two 
-----
   1
   2
(2 rows)

SELECT 1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
 three 
-------
     1
     2
     2
(3 rows)

SELECT 1.1 AS two UNION SELECT 2.2 ORDER BY 1;
 two 
-----
 1.1
 2.2
(2 rows)

-- Mixed types
SELECT 1.1 AS two UNION SELECT 2 ORDER BY 1;
 two 
-----
 1.1
   2
(2 rows)

SELECT 1 AS two UNION SELECT 2.2 ORDER BY 1;
 two 
-----
   1
 2.2
(2 rows)

SELECT 1 AS one UNION SELECT 1.0::float8 ORDER BY 1;
 one 
-----
   1
(1 row)

SELECT 1.1 AS two UNION ALL SELECT 2 ORDER BY 1;
 two 
-----
 1.1
   2
(2 rows)

SELECT 1.0::float8 AS two UNION ALL SELECT 1 ORDER BY 1;
 two 
-----
   1
   1
(2 rows)

SELECT 1.1 AS three UNION SELECT 2 UNION SELECT 3 ORDER BY 1;
 three 
-------
   1.1
     2
     3
(3 rows)

SELECT 1.1::float8 AS two UNION SELECT 2 UNION SELECT 2.0::float8 ORDER BY 1;
 two 
-----
 1.1
   2
(2 rows)

SELECT 1.1 AS three UNION SELECT 2 UNION ALL SELECT 2 ORDER BY 1;
 three 
-------
   1.1
     2
     2
(3 rows)

SELECT 1.1 AS two UNION (SELECT 2 UNION ALL SELECT 2) ORDER BY 1;
 two 
-----
 1.1
   2
(2 rows)

--
-- Try testing from tables...
--
SELECT f1 AS five FROM FLOAT8_TBL
UNION
SELECT f1 FROM FLOAT8_TBL
ORDER BY 1;
         five          
-----------------------
 -1.2345678901234e+200
               -1004.3
                -34.84
 -1.2345678901234e-200
                     0
(5 rows)

SELECT f1 AS ten FROM FLOAT8_TBL
UNION ALL
SELECT f1 FROM FLOAT8_TBL;
          ten          
-----------------------
                     0
                -34.84
               -1004.3
 -1.2345678901234e+200
 -1.2345678901234e-200
                     0
                -34.84
               -1004.3
 -1.2345678901234e+200
 -1.2345678901234e-200
(10 rows)

SELECT f1 AS nine FROM FLOAT8_TBL
UNION
SELECT f1 FROM INT4_TBL
ORDER BY 1;
         nine          
-----------------------
 -1.2345678901234e+200
           -2147483647
               -123456
               -1004.3
                -34.84
 -1.2345678901234e-200
                     0
                123456
            2147483647
(9 rows)

SELECT f1 AS ten FROM FLOAT8_TBL
UNION ALL
SELECT f1 FROM INT4_TBL;
          ten          
-----------------------
                     0
                -34.84
               -1004.3
 -1.2345678901234e+200
 -1.2345678901234e-200
                     0
                123456
               -123456
            2147483647
           -2147483647
(10 rows)

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
ORDER BY 1;
         five          
-----------------------
               -1004.3
                -34.84
 -1.2345678901234e-200
                     0
                123456
(5 rows)

SELECT CAST(f1 AS char(4)) AS three FROM VARCHAR_TBL
UNION
SELECT f1 FROM CHAR_TBL
ORDER BY 1;
 three 
-------
 a   
 ab  
 abcd
(3 rows)

SELECT f1 AS three FROM VARCHAR_TBL
UNION
SELECT CAST(f1 AS varchar) FROM CHAR_TBL
ORDER BY 1;
 three 
-------
 a
 ab
 abcd
(3 rows)

SELECT f1 AS eight FROM VARCHAR_TBL
UNION ALL
SELECT f1 FROM CHAR_TBL;
 eight 
-------
 a
 ab
 abcd
 abcd
 a
 ab
 abcd
 abcd
(8 rows)

SELECT f1 AS five FROM TEXT_TBL
UNION
SELECT f1 FROM VARCHAR_TBL
UNION
SELECT TRIM(TRAILING FROM f1) FROM CHAR_TBL
ORDER BY 1;
       five        
-------------------
 a
 ab
 abcd
 doh!
 hi de ho neighbor
(5 rows)

--
-- INTERSECT and EXCEPT
--
SELECT q2 FROM int8_tbl INTERSECT SELECT q1 FROM int8_tbl ORDER BY 1;
        q2        
------------------
              123
 4567890123456789
(2 rows)

SELECT q2 FROM int8_tbl INTERSECT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
        q2        
------------------
              123
 4567890123456789
 4567890123456789
(3 rows)

SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
        q2         
-------------------
 -4567890123456789
               456
(2 rows)

SELECT q2 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl ORDER BY 1;
        q2         
-------------------
 -4567890123456789
               456
(2 rows)

SELECT q2 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q1 FROM int8_tbl ORDER BY 1;
        q2         
-------------------
 -4567890123456789
               456
  4567890123456789
(3 rows)

SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY 1;
 q1 
----
(0 rows)

SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q2 FROM int8_tbl ORDER BY 1;
        q1        
------------------
              123
 4567890123456789
(2 rows)

SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl ORDER BY 1;
        q1        
------------------
              123
 4567890123456789
 4567890123456789
(3 rows)

SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
ERROR:  FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
-- nested cases
(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
 ?column? | ?column? | ?column? 
----------+----------+----------
        4 |        5 |        6
(1 row)

(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
 ?column? | ?column? | ?column? 
----------+----------+----------
        4 |        5 |        6
(1 row)

(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
 ?column? | ?column? | ?column? 
----------+----------+----------
        1 |        2 |        3
(1 row)

(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
 ?column? | ?column? | ?column? 
----------+----------+----------
        1 |        2 |        3
(1 row)

-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
explain (costs off)
select count(*) from
  ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
                           QUERY PLAN                           
----------------------------------------------------------------
 Aggregate
   ->  HashAggregate
         Group Key: tenk1.unique1
         ->  Append
               ->  Index Only Scan using tenk1_unique1 on tenk1
               ->  Seq Scan on tenk1 tenk1_1
(6 rows)

select count(*) from
  ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
 count 
-------
 10000
(1 row)

explain (costs off)
select count(*) from
  ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
                            QUERY PLAN                            
------------------------------------------------------------------
 Aggregate
   ->  HashSetOp Intersect
         ->  Seq Scan on tenk1
         ->  Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
(4 rows)

select count(*) from
  ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
 count 
-------
  5000
(1 row)

-- this query will prefer a sorted setop unless we force it.
set enable_groupagg to off;
explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
                         QUERY PLAN                         
------------------------------------------------------------
 HashSetOp Except
   ->  Index Only Scan using tenk1_unique1 on tenk1
   ->  Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
         Filter: (unique2 <> 10)
(4 rows)

select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
 unique1 
---------
      10
(1 row)

reset enable_groupagg;
-- the hashed implementation is sensitive to child plans' tuple slot types
explain (costs off)
select * from int8_tbl intersect select q2, q1 from int8_tbl order by 1, 2;
                 QUERY PLAN                  
---------------------------------------------
 Sort
   Sort Key: int8_tbl.q1, int8_tbl.q2
   ->  HashSetOp Intersect
         ->  Seq Scan on int8_tbl
         ->  Seq Scan on int8_tbl int8_tbl_1
(5 rows)

select * from int8_tbl intersect select q2, q1 from int8_tbl order by 1, 2;
        q1        |        q2        
------------------+------------------
              123 | 4567890123456789
 4567890123456789 |              123
 4567890123456789 | 4567890123456789
(3 rows)

select q2, q1 from int8_tbl intersect select * from int8_tbl order by 1, 2;
        q2        |        q1        
------------------+------------------
              123 | 4567890123456789
 4567890123456789 |              123
 4567890123456789 | 4567890123456789
(3 rows)

set enable_hashagg to off;
explain (costs off)
select count(*) from
  ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
                           QUERY PLAN                           
----------------------------------------------------------------
 Aggregate
   ->  Unique
         ->  Merge Append
               Sort Key: tenk1.unique1
               ->  Index Only Scan using tenk1_unique1 on tenk1
               ->  Sort
                     Sort Key: tenk1_1.fivethous
                     ->  Seq Scan on tenk1 tenk1_1
(8 rows)

select count(*) from
  ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
 count 
-------
 10000
(1 row)

explain (costs off)
select count(*) from
  ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
                            QUERY PLAN                            
------------------------------------------------------------------
 Aggregate
   ->  SetOp Intersect
         ->  Sort
               Sort Key: tenk1.fivethous
               ->  Seq Scan on tenk1
         ->  Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
(6 rows)

select count(*) from
  ( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
 count 
-------
  5000
(1 row)

explain (costs off)
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
                         QUERY PLAN                         
------------------------------------------------------------
 SetOp Except
   ->  Index Only Scan using tenk1_unique1 on tenk1
   ->  Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
         Filter: (unique2 <> 10)
(4 rows)

select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
 unique1 
---------
      10
(1 row)

explain (costs off)
select f1 from int4_tbl union all
  (select unique1 from tenk1 union select unique2 from tenk1);
                               QUERY PLAN                               
------------------------------------------------------------------------
 Append
   ->  Seq Scan on int4_tbl
   ->  Unique
         ->  Merge Append
               Sort Key: tenk1.unique1
               ->  Index Only Scan using tenk1_unique1 on tenk1
               ->  Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
(7 rows)

reset enable_hashagg;
-- non-hashable type
set enable_hashagg to on;
explain (costs off)
select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit)) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

set enable_hashagg to off;
explain (costs off)
select x from (values ('11'::varbit), ('10'::varbit)) _(x) union select x from (values ('11'::varbit), ('10'::varbit)) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

reset enable_hashagg;
-- arrays
set enable_hashagg to on;
explain (costs off)
select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
               QUERY PLAN                
-----------------------------------------
 HashAggregate
   Group Key: "*VALUES*".column1
   ->  Append
         ->  Values Scan on "*VALUES*"
         ->  Values Scan on "*VALUES*_1"
(5 rows)

select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
   x   
-------
 {1,4}
 {1,2}
 {1,3}
(3 rows)

explain (costs off)
select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
            QUERY PLAN             
-----------------------------------
 HashSetOp Intersect
   ->  Values Scan on "*VALUES*"
   ->  Values Scan on "*VALUES*_1"
(3 rows)

select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
   x   
-------
 {1,2}
(1 row)

explain (costs off)
select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
            QUERY PLAN             
-----------------------------------
 HashSetOp Except
   ->  Values Scan on "*VALUES*"
   ->  Values Scan on "*VALUES*_1"
(3 rows)

select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
   x   
-------
 {1,3}
(1 row)

-- non-hashable type
explain (costs off)
select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values (array['10'::varbit]), (array['01'::varbit])) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

select x from (values (array['10'::varbit]), (array['11'::varbit])) _(x) union select x from (values (array['10'::varbit]), (array['01'::varbit])) _(x);
  x   
------
 {01}
 {10}
 {11}
(3 rows)

set enable_hashagg to off;
explain (costs off)
select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
   x   
-------
 {1,2}
 {1,3}
 {1,4}
(3 rows)

explain (costs off)
select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
               QUERY PLAN                
-----------------------------------------
 SetOp Intersect
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Sort
         Sort Key: "*VALUES*_1".column1
         ->  Values Scan on "*VALUES*_1"
(7 rows)

select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
   x   
-------
 {1,2}
(1 row)

explain (costs off)
select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
               QUERY PLAN                
-----------------------------------------
 SetOp Except
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Sort
         Sort Key: "*VALUES*_1".column1
         ->  Values Scan on "*VALUES*_1"
(7 rows)

select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
   x   
-------
 {1,3}
(1 row)

reset enable_hashagg;
-- records
set enable_hashagg to on;
explain (costs off)
select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
   x   
-------
 (1,2)
 (1,3)
 (1,4)
(3 rows)

explain (costs off)
select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
               QUERY PLAN                
-----------------------------------------
 SetOp Intersect
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Sort
         Sort Key: "*VALUES*_1".column1
         ->  Values Scan on "*VALUES*_1"
(7 rows)

select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
   x   
-------
 (1,2)
(1 row)

explain (costs off)
select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
               QUERY PLAN                
-----------------------------------------
 SetOp Except
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Sort
         Sort Key: "*VALUES*_1".column1
         ->  Values Scan on "*VALUES*_1"
(7 rows)

select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
   x   
-------
 (1,3)
(1 row)

-- non-hashable type
-- With an anonymous row type, the typcache does not report that the
-- type is hashable.  (Otherwise, this would fail at execution time.)
explain (costs off)
select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

select x from (values (row('10'::varbit)), (row('11'::varbit))) _(x) union select x from (values (row('10'::varbit)), (row('01'::varbit))) _(x);
  x   
------
 (01)
 (10)
 (11)
(3 rows)

-- With a defined row type, the typcache can inspect the type's fields
-- for hashability.
create type ct1 as (f1 varbit);
explain (costs off)
select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbit)::ct1)) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

select x from (values (row('10'::varbit)::ct1), (row('11'::varbit)::ct1)) _(x) union select x from (values (row('10'::varbit)::ct1), (row('01'::varbit)::ct1)) _(x);
  x   
------
 (01)
 (10)
 (11)
(3 rows)

drop type ct1;
set enable_hashagg to off;
explain (costs off)
select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Append
               ->  Values Scan on "*VALUES*"
               ->  Values Scan on "*VALUES*_1"
(6 rows)

select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
   x   
-------
 (1,2)
 (1,3)
 (1,4)
(3 rows)

explain (costs off)
select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
               QUERY PLAN                
-----------------------------------------
 SetOp Intersect
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Sort
         Sort Key: "*VALUES*_1".column1
         ->  Values Scan on "*VALUES*_1"
(7 rows)

select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
   x   
-------
 (1,2)
(1 row)

explain (costs off)
select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
               QUERY PLAN                
-----------------------------------------
 SetOp Except
   ->  Sort
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"
   ->  Sort
         Sort Key: "*VALUES*_1".column1
         ->  Values Scan on "*VALUES*_1"
(7 rows)

select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
   x   
-------
 (1,3)
(1 row)

-- non-sortable type
-- Ensure we get a HashAggregate plan.  Keep enable_hashagg=off to ensure
-- there's no chance of a sort.
explain (costs off) select '123'::xid union select '123'::xid;
        QUERY PLAN         
---------------------------
 HashAggregate
   Disabled: true
   Group Key: ('123'::xid)
   ->  Append
         ->  Result
         ->  Result
(6 rows)

reset enable_hashagg;
--
-- Mixed types
--
SELECT f1 FROM float8_tbl INTERSECT SELECT f1 FROM int4_tbl ORDER BY 1;
 f1 
----
  0
(1 row)

SELECT f1 FROM float8_tbl EXCEPT SELECT f1 FROM int4_tbl ORDER BY 1;
          f1           
-----------------------
 -1.2345678901234e+200
               -1004.3
                -34.84
 -1.2345678901234e-200
(4 rows)

--
-- Operator precedence and (((((extra))))) parentheses
--
SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl  ORDER BY 1;
        q1         
-------------------
 -4567890123456789
               123
               123
               456
  4567890123456789
  4567890123456789
  4567890123456789
(7 rows)

SELECT q1 FROM int8_tbl INTERSECT (((SELECT q2 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) ORDER BY 1;
        q1        
------------------
              123
 4567890123456789
(2 rows)

(((SELECT q1 FROM int8_tbl INTERSECT SELECT q2 FROM int8_tbl ORDER BY 1))) UNION ALL SELECT q2 FROM int8_tbl;
        q1         
-------------------
               123
  4567890123456789
               456
  4567890123456789
               123
  4567890123456789
 -4567890123456789
(7 rows)

SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
        q1         
-------------------
 -4567890123456789
               456
(2 rows)

SELECT q1 FROM int8_tbl UNION ALL (((SELECT q2 FROM int8_tbl EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1)));
        q1         
-------------------
               123
               123
  4567890123456789
  4567890123456789
  4567890123456789
 -4567890123456789
               456
(7 rows)

(((SELECT q1 FROM int8_tbl UNION ALL SELECT q2 FROM int8_tbl))) EXCEPT SELECT q1 FROM int8_tbl ORDER BY 1;
        q1         
-------------------
 -4567890123456789
               456
(2 rows)

--
-- 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;
        q1        |        q2         
------------------+-------------------
 4567890123456789 | -4567890123456789
              123 |               456
(2 rows)

-- 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;
ERROR:  column "q2" does not exist
LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
                                                             ^
DETAIL:  There is a column named "q2" in table "unnamed_subquery", but it cannot be referenced from this part of the query.
-- But this should work:
SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
        q1        
------------------
              123
 4567890123456789
(2 rows)

--
-- New syntaxes (7.1) permit new tests
--
(((((select * from int8_tbl)))));
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

--
-- Check behavior with empty select list (allowed since 9.4)
--
select union select;
--
(1 row)

select intersect select;
--
(1 row)

select except select;
--
(0 rows)

-- check hashed implementation
set enable_hashagg = true;
set enable_groupagg = false;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
                           QUERY PLAN                           
----------------------------------------------------------------
 HashAggregate
   ->  Append
         ->  Function Scan on generate_series
         ->  Function Scan on generate_series generate_series_1
(4 rows)

explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
                        QUERY PLAN                        
----------------------------------------------------------
 HashSetOp Intersect
   ->  Function Scan on generate_series
   ->  Function Scan on generate_series generate_series_1
(3 rows)

select from generate_series(1,5) union select from generate_series(1,3);
--
(1 row)

select from generate_series(1,5) union all select from generate_series(1,3);
--
(8 rows)

select from generate_series(1,5) intersect select from generate_series(1,3);
--
(1 row)

select from generate_series(1,5) intersect all select from generate_series(1,3);
--
(3 rows)

select from generate_series(1,5) except select from generate_series(1,3);
--
(0 rows)

select from generate_series(1,5) except all select from generate_series(1,3);
--
(2 rows)

-- check sorted implementation
set enable_hashagg = false;
set enable_groupagg = true;
explain (costs off)
select from generate_series(1,5) union select from generate_series(1,3);
                           QUERY PLAN                           
----------------------------------------------------------------
 Unique
   ->  Append
         ->  Function Scan on generate_series
         ->  Function Scan on generate_series generate_series_1
(4 rows)

explain (costs off)
select from generate_series(1,5) intersect select from generate_series(1,3);
                        QUERY PLAN                        
----------------------------------------------------------
 SetOp Intersect
   ->  Function Scan on generate_series
   ->  Function Scan on generate_series generate_series_1
(3 rows)

select from generate_series(1,5) union select from generate_series(1,3);
--
(1 row)

select from generate_series(1,5) union all select from generate_series(1,3);
--
(8 rows)

select from generate_series(1,5) intersect select from generate_series(1,3);
--
(1 row)

select from generate_series(1,5) intersect all select from generate_series(1,3);
--
(3 rows)

select from generate_series(1,5) except select from generate_series(1,3);
--
(0 rows)

select from generate_series(1,5) except all select from generate_series(1,3);
--
(2 rows)

-- Try a variation of the above but with a CTE which contains a column, again
-- with an empty final select list.
-- Ensure we get the expected 1 row with 0 columns
with cte as materialized (select s from generate_series(1,5) s)
select from cte union select from cte;
--
(1 row)

-- Ensure we get the same result as the above.
with cte as not materialized (select s from generate_series(1,5) s)
select from cte union select from cte;
--
(1 row)

reset enable_hashagg;
reset enable_groupagg;
--
-- Check handling of a case with unknown constants.  We don't guarantee
-- an undecorated constant will work in all cases, but historically this
-- usage has worked, so test we don't break it.
--
SELECT a.f1 FROM (SELECT 'test' AS f1 FROM varchar_tbl) a
UNION
SELECT b.f1 FROM (SELECT f1 FROM varchar_tbl) b
ORDER BY 1;
  f1  
------
 a
 ab
 abcd
 test
(4 rows)

-- This should fail, but it should produce an error cursor
SELECT '3.4'::numeric UNION SELECT 'foo';
ERROR:  invalid input syntax for type numeric: "foo"
LINE 1: SELECT '3.4'::numeric UNION SELECT 'foo';
                                           ^
--
-- Test that expression-index constraints can be pushed down through
-- UNION or UNION ALL
--
CREATE TEMP TABLE t1 (a text, b text);
CREATE INDEX t1_ab_idx on t1 ((a || b));
CREATE TEMP TABLE t2 (ab text primary key);
INSERT INTO t1 VALUES ('a', 'b'), ('x', 'y');
INSERT INTO t2 VALUES ('ab'), ('xy');
set enable_seqscan = off;
set enable_indexscan = on;
set enable_bitmapscan = off;
set enable_sort = off;
explain (costs off)
 SELECT * FROM
 (SELECT a || b AS ab FROM t1
  UNION ALL
  SELECT * FROM t2) t
 WHERE ab = 'ab';
                 QUERY PLAN                  
---------------------------------------------
 Append
   ->  Index Scan using t1_ab_idx on t1
         Index Cond: ((a || b) = 'ab'::text)
   ->  Index Only Scan using t2_pkey on t2
         Index Cond: (ab = 'ab'::text)
(5 rows)

explain (costs off)
 SELECT * FROM
 (SELECT a || b AS ab FROM t1
  UNION
  SELECT * FROM t2) t
 WHERE ab = 'ab';
                    QUERY PLAN                     
---------------------------------------------------
 HashAggregate
   Group Key: ((t1.a || t1.b))
   ->  Append
         ->  Index Scan using t1_ab_idx on t1
               Index Cond: ((a || b) = 'ab'::text)
         ->  Index Only Scan using t2_pkey on t2
               Index Cond: (ab = 'ab'::text)
(7 rows)

--
-- Test that ORDER BY for UNION ALL can be pushed down to inheritance
-- children.
--
CREATE TEMP TABLE t1c (b text, a text);
ALTER TABLE t1c INHERIT t1;
CREATE TEMP TABLE t2c (primary key (ab)) INHERITS (t2);
INSERT INTO t1c VALUES ('v', 'w'), ('c', 'd'), ('m', 'n'), ('e', 'f');
INSERT INTO t2c VALUES ('vw'), ('cd'), ('mn'), ('ef');
CREATE INDEX t1c_ab_idx on t1c ((a || b));
set enable_seqscan = on;
set enable_indexonlyscan = off;
explain (costs off)
  SELECT * FROM
  (SELECT a || b AS ab FROM t1
   UNION ALL
   SELECT ab FROM t2) t
  ORDER BY 1 LIMIT 8;
                     QUERY PLAN                      
-----------------------------------------------------
 Limit
   ->  Merge Append
         Sort Key: ((t1.a || t1.b))
         ->  Index Scan using t1_ab_idx on t1
         ->  Index Scan using t1c_ab_idx on t1c t1_1
         ->  Index Scan using t2_pkey on t2
         ->  Index Scan using t2c_pkey on t2c t2_1
(7 rows)

  SELECT * FROM
  (SELECT a || b AS ab FROM t1
   UNION ALL
   SELECT ab FROM t2) t
  ORDER BY 1 LIMIT 8;
 ab 
----
 ab
 ab
 cd
 dc
 ef
 fe
 mn
 nm
(8 rows)

reset enable_seqscan;
reset enable_indexscan;
reset enable_bitmapscan;
reset enable_sort;
-- This simpler variant of the above test has been observed to fail differently
create table events (event_id int primary key);
create table other_events (event_id int primary key);
create table events_child () inherits (events);
explain (costs off)
select event_id
 from (select event_id from events
       union all
       select event_id from other_events) ss
 order by event_id;
                        QUERY PLAN                        
----------------------------------------------------------
 Merge Append
   Sort Key: events.event_id
   ->  Index Scan using events_pkey on events
   ->  Sort
         Sort Key: events_1.event_id
         ->  Seq Scan on events_child events_1
   ->  Index Scan using other_events_pkey on other_events
(7 rows)

drop table events_child, events, other_events;
reset enable_indexonlyscan;
--
-- Test handling of UNION / EXCEPT / INTERSECT with provably empty inputs
--
-- Ensure the empty UNION input is pruned and de-duplication is done for the
-- remaining relation.
EXPLAIN (COSTS OFF, VERBOSE)
SELECT two FROM tenk1 WHERE 1=2
UNION
SELECT four FROM tenk1
ORDER BY 1;
              QUERY PLAN              
--------------------------------------
 Sort
   Output: tenk1.four
   Sort Key: tenk1.four
   ->  HashAggregate
         Output: tenk1.four
         Group Key: tenk1.four
         ->  Seq Scan on public.tenk1
               Output: tenk1.four
(8 rows)

-- Validate that the results of the above are correct
SELECT two FROM tenk1 WHERE 1=2
UNION
SELECT four FROM tenk1
ORDER BY 1;
 two 
-----
   0
   1
   2
   3
(4 rows)

-- All UNION inputs are proven empty.  Ensure the planner provides a
-- const-false Result node
EXPLAIN (COSTS OFF, VERBOSE)
SELECT two FROM tenk1 WHERE 1=2
UNION
SELECT four FROM tenk1 WHERE 1=2
UNION
SELECT ten FROM tenk1 WHERE 1=2
ORDER BY 1;
                                       QUERY PLAN                                        
-----------------------------------------------------------------------------------------
 Sort
   Output: unnamed_subquery.two
   Sort Key: unnamed_subquery.two
   ->  Result
         Output: unnamed_subquery.two
         Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1, unnamed_subquery_2
         One-Time Filter: false
(7 rows)

-- Ensure the planner provides a const-false Result node
EXPLAIN (COSTS OFF, VERBOSE)
SELECT two FROM tenk1 WHERE 1=2
INTERSECT
SELECT four FROM tenk1
ORDER BY 1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Sort
   Output: unnamed_subquery.two
   Sort Key: unnamed_subquery.two
   ->  Result
         Output: unnamed_subquery.two
         Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
         One-Time Filter: false
(7 rows)

-- As above, with the inputs swapped
EXPLAIN (COSTS OFF, VERBOSE)
SELECT four FROM tenk1
INTERSECT
SELECT two FROM tenk1 WHERE 1=2
ORDER BY 1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Sort
   Output: unnamed_subquery.four
   Sort Key: unnamed_subquery.four
   ->  Result
         Output: unnamed_subquery.four
         Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
         One-Time Filter: false
(7 rows)

-- Try with both inputs dummy
EXPLAIN (COSTS OFF, VERBOSE)
SELECT four FROM tenk1 WHERE 1=2
INTERSECT
SELECT two FROM tenk1 WHERE 1=2
ORDER BY 1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Sort
   Output: unnamed_subquery.four
   Sort Key: unnamed_subquery.four
   ->  Result
         Output: unnamed_subquery.four
         Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
         One-Time Filter: false
(7 rows)

-- Ensure the planner provides a const-false Result node when the left input
-- is empty
EXPLAIN (COSTS OFF, VERBOSE)
SELECT two FROM tenk1 WHERE 1=2
EXCEPT
SELECT four FROM tenk1
ORDER BY 1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Sort
   Output: unnamed_subquery.two
   Sort Key: unnamed_subquery.two
   ->  Result
         Output: unnamed_subquery.two
         Replaces: Aggregate on unnamed_subquery, unnamed_subquery_1
         One-Time Filter: false
(7 rows)

-- Ensure the planner only scans the left input when right input is empty
EXPLAIN (COSTS OFF, VERBOSE)
SELECT two FROM tenk1
EXCEPT ALL
SELECT four FROM tenk1 WHERE 1=2
ORDER BY 1;
           QUERY PLAN           
--------------------------------
 Sort
   Output: tenk1.two
   Sort Key: tenk1.two
   ->  Seq Scan on public.tenk1
         Output: tenk1.two
(5 rows)

-- Try a mixed setop case.  Ensure the right-hand UNION child gets removed.
EXPLAIN (COSTS OFF, VERBOSE)
SELECT two FROM tenk1 t1
EXCEPT
SELECT four FROM tenk1 t2
UNION
SELECT ten FROM tenk1 dummy WHERE 1=2;
                  QUERY PLAN                   
-----------------------------------------------
 Unique
   Output: t1.two
   ->  Sort
         Output: t1.two
         Sort Key: t1.two
         ->  HashSetOp Except
               Output: t1.two
               ->  Seq Scan on public.tenk1 t1
                     Output: t1.two
               ->  Seq Scan on public.tenk1 t2
                     Output: t2.four
(11 rows)

-- Test constraint exclusion of UNION ALL subqueries
explain (costs off)
 SELECT * FROM
  (SELECT 1 AS t, * FROM tenk1 a
   UNION ALL
   SELECT 2 AS t, * FROM tenk1 b) c
 WHERE t = 2;
     QUERY PLAN      
---------------------
 Seq Scan on tenk1 b
(1 row)

-- Test that we push quals into UNION sub-selects only when it's safe
explain (costs off)
SELECT * FROM
  (SELECT 1 AS t, 2 AS x
   UNION
   SELECT 2 AS t, 4 AS x) ss
WHERE x < 4
ORDER BY x;
                    QUERY PLAN                    
--------------------------------------------------
 Sort
   Sort Key: (2)
   ->  Unique
         ->  Sort
               Sort Key: (1), (2)
               ->  Append
                     ->  Result
                     ->  Result
                           One-Time Filter: false
(9 rows)

SELECT * FROM
  (SELECT 1 AS t, 2 AS x
   UNION
   SELECT 2 AS t, 4 AS x) ss
WHERE x < 4
ORDER BY x;
 t | x 
---+---
 1 | 2
(1 row)

explain (costs off)
SELECT * FROM
  (SELECT 1 AS t, generate_series(1,10) AS x
   UNION
   SELECT 2 AS t, 4 AS x) ss
WHERE x < 4
ORDER BY x;
                       QUERY PLAN                       
--------------------------------------------------------
 Sort
   Sort Key: ss.x
   ->  Subquery Scan on ss
         Filter: (ss.x < 4)
         ->  HashAggregate
               Group Key: (1), (generate_series(1, 10))
               ->  Append
                     ->  ProjectSet
                           ->  Result
                     ->  Result
(10 rows)

SELECT * FROM
  (SELECT 1 AS t, generate_series(1,10) AS x
   UNION
   SELECT 2 AS t, 4 AS x) ss
WHERE x < 4
ORDER BY x;
 t | x 
---+---
 1 | 1
 1 | 2
 1 | 3
(3 rows)

explain (costs off)
SELECT * FROM
  (SELECT 1 AS t, (random()*3)::int AS x
   UNION
   SELECT 2 AS t, 4 AS x) ss
WHERE x > 3
ORDER BY x;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Sort
   Sort Key: ss.x
   ->  Subquery Scan on ss
         Filter: (ss.x > 3)
         ->  Unique
               ->  Sort
                     Sort Key: (1), (((random() * '3'::double precision))::integer)
                     ->  Append
                           ->  Result
                           ->  Result
(10 rows)

SELECT * FROM
  (SELECT 1 AS t, (random()*3)::int AS x
   UNION
   SELECT 2 AS t, 4 AS x) ss
WHERE x > 3
ORDER BY x;
 t | x 
---+---
 2 | 4
(1 row)

-- Test cases where the native ordering of a sub-select has more pathkeys
-- than the outer query cares about
explain (costs off)
select distinct q1 from
  (select distinct * from int8_tbl i81
   union all
   select distinct * from int8_tbl i82) ss
where q2 = q2;
                        QUERY PLAN                        
----------------------------------------------------------
 Unique
   ->  Merge Append
         Sort Key: unnamed_subquery.q1
         ->  Subquery Scan on unnamed_subquery
               ->  Unique
                     ->  Sort
                           Sort Key: i81.q1, i81.q2
                           ->  Seq Scan on int8_tbl i81
                                 Filter: (q2 IS NOT NULL)
         ->  Subquery Scan on unnamed_subquery_1
               ->  Unique
                     ->  Sort
                           Sort Key: i82.q1, i82.q2
                           ->  Seq Scan on int8_tbl i82
                                 Filter: (q2 IS NOT NULL)
(15 rows)

select distinct q1 from
  (select distinct * from int8_tbl i81
   union all
   select distinct * from int8_tbl i82) ss
where q2 = q2;
        q1        
------------------
              123
 4567890123456789
(2 rows)

explain (costs off)
select distinct q1 from
  (select distinct * from int8_tbl i81
   union all
   select distinct * from int8_tbl i82) ss
where -q1 = q2;
                       QUERY PLAN                       
--------------------------------------------------------
 Unique
   ->  Merge Append
         Sort Key: unnamed_subquery.q1
         ->  Subquery Scan on unnamed_subquery
               ->  Unique
                     ->  Sort
                           Sort Key: i81.q1, i81.q2
                           ->  Seq Scan on int8_tbl i81
                                 Filter: ((- q1) = q2)
         ->  Subquery Scan on unnamed_subquery_1
               ->  Unique
                     ->  Sort
                           Sort Key: i82.q1, i82.q2
                           ->  Seq Scan on int8_tbl i82
                                 Filter: ((- q1) = q2)
(15 rows)

select distinct q1 from
  (select distinct * from int8_tbl i81
   union all
   select distinct * from int8_tbl i82) ss
where -q1 = q2;
        q1        
------------------
 4567890123456789
(1 row)

-- Test proper handling of parameterized appendrel paths when the
-- potential join qual is expensive
create function expensivefunc(int) returns int
language plpgsql immutable strict cost 10000
as $$begin return $1; end$$;
create temp table t3 as select generate_series(-1000,1000) as x;
create index t3i on t3 (expensivefunc(x));
analyze t3;
explain (costs off)
select * from
  (select * from t3 a union all select * from t3 b) ss
  join int4_tbl on f1 = expensivefunc(x);
                         QUERY PLAN                         
------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on int4_tbl
   ->  Append
         ->  Index Scan using t3i on t3 a
               Index Cond: (expensivefunc(x) = int4_tbl.f1)
         ->  Index Scan using t3i on t3 b
               Index Cond: (expensivefunc(x) = int4_tbl.f1)
(7 rows)

select * from
  (select * from t3 a union all select * from t3 b) ss
  join int4_tbl on f1 = expensivefunc(x);
 x | f1 
---+----
 0 |  0
 0 |  0
(2 rows)

drop table t3;
drop function expensivefunc(int);
-- Test handling of appendrel quals that const-simplify into an AND
explain (costs off)
select * from
  (select *, 0 as x from int8_tbl a
   union all
   select *, 1 as x from int8_tbl b) ss
where (x = 0) or (q1 >= q2 and q1 <= q2);
                 QUERY PLAN                  
---------------------------------------------
 Append
   ->  Seq Scan on int8_tbl a
   ->  Seq Scan on int8_tbl b
         Filter: ((q1 >= q2) AND (q1 <= q2))
(4 rows)

select * from
  (select *, 0 as x from int8_tbl a
   union all
   select *, 1 as x from int8_tbl b) ss
where (x = 0) or (q1 >= q2 and q1 <= q2);
        q1        |        q2         | x 
------------------+-------------------+---
              123 |               456 | 0
              123 |  4567890123456789 | 0
 4567890123456789 |               123 | 0
 4567890123456789 |  4567890123456789 | 0
 4567890123456789 | -4567890123456789 | 0
 4567890123456789 |  4567890123456789 | 1
(6 rows)

--
-- Test the planner's ability to produce cheap startup plans with Append nodes
--
-- Ensure we get a Nested Loop join between tenk1 and tenk2
explain (costs off)
select t1.unique1 from tenk1 t1
inner join tenk2 t2 on t1.tenthous = t2.tenthous and t2.thousand = 0
   union all
(values(1)) limit 1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Limit
   ->  Append
         ->  Nested Loop
               ->  Seq Scan on tenk2 t2
                     Filter: (thousand = 0)
               ->  Index Scan using tenk1_thous_tenthous on tenk1 t1
                     Index Cond: (tenthous = t2.tenthous)
         ->  Result
(8 rows)

-- Ensure there is no problem if cheapest_startup_path is NULL
explain (costs off)
select * from tenk1 t1
left join lateral
  (select t1.tenthous from tenk2 t2 union all (values(1)))
on true limit 1;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Limit
   ->  Nested Loop Left Join
         ->  Seq Scan on tenk1 t1
         ->  Append
               ->  Index Only Scan using tenk2_hundred on tenk2 t2
               ->  Result
(6 rows)

-- Test handling of Vars with varno 0 in estimate_array_length
explain (verbose, costs off)
select null::int[] union all select null::int[] union all select null::bigint[];
                 QUERY PLAN                  
---------------------------------------------
 Append
   ->  Result
         Output: (NULL::integer[])
         ->  Append
               ->  Result
                     Output: NULL::integer[]
               ->  Result
                     Output: NULL::integer[]
   ->  Result
         Output: NULL::bigint[]
(10 rows)

-- Estimate a UNION's output rows as the sum of its children's distinct-group
-- estimates, not the total input size.  Ensure we get an index nested loop
explain (costs off)
select * from tenk1 t
join (select ten from tenk1 union select ten from onek) s on s.ten = t.unique1;
                   QUERY PLAN                    
-------------------------------------------------
 Nested Loop
   ->  HashAggregate
         Group Key: tenk1.ten
         ->  Append
               ->  Seq Scan on tenk1
               ->  Seq Scan on onek
   ->  Index Scan using tenk1_unique1 on tenk1 t
         Index Cond: (unique1 = tenk1.ten)
(8 rows)