src/test/regress/expected/external_table_union_all.source

SET optimizer_trace_fallback=on;
-- Test external table as left child of union all with replicated table
CREATE EXTERNAL TABLE multilocation_external_table(a INTEGER)
location ('file://@hostname@@abs_srcdir@/data/location1.csv', 'file://@hostname@@abs_srcdir@/data/location2.csv', 'file://@hostname@@abs_srcdir@/data/location3.csv')
ON ALL FORMAT 'text';
CREATE EXTERNAL TABLE one_external_table(a INTEGER)
location ('file://@hostname@@abs_srcdir@/data/location2.csv')
ON SEGMENT 2 FORMAT 'text';
CREATE TABLE simple_replicated_table(a integer) DISTRIBUTED REPLICATED;
INSERT INTO simple_replicated_table VALUES (1);
CREATE TABLE simple_distributed_table(a integer) DISTRIBUTED BY (a);
INSERT INTO simple_distributed_table VALUES (2);
EXPLAIN SELECT A FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..62359.50 rows=3288900 width=4)
   ->  Append  (cost=0.00..18507.50 rows=1096300 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..11000.00 rows=1000000 width=4)
         ->  Result  (cost=0.00..2026.00 rows=96300 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..1063.00 rows=96300 width=4)
 Optimizer: Postgres query optimizer
(7 rows)

SELECT A FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
 a 
---
 2
 1
 3
 1
(4 rows)

EXPLAIN SELECT A FROM simple_replicated_table UNION ALL SELECT A FROM multilocation_external_table;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..62359.50 rows=3288900 width=4)
   ->  Append  (cost=0.00..18507.50 rows=1096300 width=4)
         ->  Result  (cost=0.00..2026.00 rows=96300 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..1063.00 rows=96300 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..11000.00 rows=1000000 width=4)
 Optimizer: Postgres query optimizer
(7 rows)

SELECT A FROM simple_replicated_table UNION ALL SELECT A FROM multilocation_external_table;
 a 
---
 2
 1
 3
 1
(4 rows)

EXPLAIN SELECT A FROM simple_replicated_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_distributed_table;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..64159.00 rows=3385200 width=4)
   ->  Append  (cost=0.00..19023.00 rows=1128400 width=4)
         ->  Result  (cost=0.00..2026.00 rows=96300 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..1063.00 rows=96300 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..11000.00 rows=1000000 width=4)
         ->  Seq Scan on simple_distributed_table  (cost=0.00..355.00 rows=32100 width=4)
 Optimizer: Postgres query optimizer
(8 rows)

SELECT A FROM simple_replicated_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_distributed_table;
 a 
---
 1
 2
 2
 1
 3
(5 rows)

EXPLAIN SELECT A FROM simple_distributed_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..64159.00 rows=3385200 width=4)
   ->  Append  (cost=0.00..19023.00 rows=1128400 width=4)
         ->  Seq Scan on simple_distributed_table  (cost=0.00..355.00 rows=32100 width=4)
         ->  Foreign Scan on multilocation_external_table  (cost=0.00..11000.00 rows=1000000 width=4)
         ->  Result  (cost=0.00..2026.00 rows=96300 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..1063.00 rows=96300 width=4)
 Optimizer: Postgres query optimizer
(8 rows)

SELECT A FROM simple_distributed_table UNION ALL SELECT a FROM multilocation_external_table UNION ALL SELECT A FROM simple_replicated_table;
 a 
---
 2
 3
 1
 2
 1
(5 rows)

EXPLAIN SELECT * FROM one_external_table UNION ALL SELECT a FROM simple_replicated_table;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..62359.50 rows=3288900 width=4)
   ->  Append  (cost=0.00..18507.50 rows=1096300 width=4)
         ->  Foreign Scan on one_external_table  (cost=0.00..11000.00 rows=1000000 width=4)
         ->  Result  (cost=0.00..2026.00 rows=96300 width=4)
               One-Time Filter: (gp_execution_segment() = 2)
               ->  Seq Scan on simple_replicated_table  (cost=0.00..1063.00 rows=96300 width=4)
 Optimizer: Postgres query optimizer
(7 rows)

SELECT * FROM one_external_table UNION ALL SELECT a FROM simple_replicated_table;
 a 
---
 1
 2
(2 rows)