subselect_gp.out

set optimizer_enable_master_only_queries = on;
set optimizer_segments = 3;
set optimizer_nestloop_factor = 1.0;
--
-- Base tables for CSQ tests
--
drop table if exists csq_t1_base;
NOTICE:  table "csq_t1_base" does not exist, skipping
create table csq_t1_base(x int, y int) distributed by (x);
insert into csq_t1_base values(1,2);
insert into csq_t1_base values(2,1);
insert into csq_t1_base values(4,2);
drop table if exists csq_t2_base;
NOTICE:  table "csq_t2_base" does not exist, skipping
create table csq_t2_base(x int, y int) distributed by (x);
insert into csq_t2_base values(3,2);
insert into csq_t2_base values(3,2);
insert into csq_t2_base values(3,2);
insert into csq_t2_base values(3,2);
insert into csq_t2_base values(3,1);
--
-- Correlated subqueries
--
drop table if exists csq_t1;
NOTICE:  table "csq_t1" does not exist, skipping
drop table if exists csq_t2;
NOTICE:  table "csq_t2" does not exist, skipping
create table csq_t1(x int, y int) distributed by (x);
create table csq_t2(x int, y int) distributed by (x);
insert into csq_t1 select * from csq_t1_base;
insert into csq_t2 select * from csq_t2_base;
select * from csq_t1 where csq_t1.x >ALL (select csq_t2.x from csq_t2 where csq_t2.y=csq_t1.y) order by 1; -- expected (4,2)
 x | y 
---+---
 4 | 2
(1 row)

--
-- correlations in the targetlist
--
select csq_t1.x, (select sum(bar.x) from csq_t1 bar where bar.x >= csq_t1.x) as sum from csq_t1 order by csq_t1.x;
 x | sum 
---+-----
 1 |   7
 2 |   6
 4 |   4
(3 rows)

select csq_t1.x, (select sum(bar.x) from csq_t1 bar where bar.x = csq_t1.x) as sum from csq_t1 order by csq_t1.x;
 x | sum 
---+-----
 1 |   1
 2 |   2
 4 |   4
(3 rows)

select csq_t1.x, (select bar.x from csq_t1 bar where bar.x = csq_t1.x) as sum from csq_t1 order by csq_t1.x;
 x | sum 
---+-----
 1 |   1
 2 |   2
 4 |   4
(3 rows)

--
-- CSQs with partitioned tables
--
drop table if exists csq_t1;
drop table if exists csq_t2;
create table csq_t1(x int, y int) 
distributed by (x)
partition by range (y) ( start (0) end (4) every (1))
;
NOTICE:  CREATE TABLE will create partition "csq_t1_1_prt_1" for table "csq_t1"
NOTICE:  CREATE TABLE will create partition "csq_t1_1_prt_2" for table "csq_t1"
NOTICE:  CREATE TABLE will create partition "csq_t1_1_prt_3" for table "csq_t1"
NOTICE:  CREATE TABLE will create partition "csq_t1_1_prt_4" for table "csq_t1"
create table csq_t2(x int, y int) 
distributed by (x)
partition by range (y) ( start (0) end (4) every (1))
;
NOTICE:  CREATE TABLE will create partition "csq_t2_1_prt_1" for table "csq_t2"
NOTICE:  CREATE TABLE will create partition "csq_t2_1_prt_2" for table "csq_t2"
NOTICE:  CREATE TABLE will create partition "csq_t2_1_prt_3" for table "csq_t2"
NOTICE:  CREATE TABLE will create partition "csq_t2_1_prt_4" for table "csq_t2"
insert into csq_t1 select * from csq_t1_base;
insert into csq_t2 select * from csq_t2_base;
explain select * from csq_t1 where csq_t1.x >ALL (select csq_t2.x from csq_t2 where csq_t2.y=csq_t1.y) order by 1;
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice17; segments: 2)  (cost=20329.25..20331.40 rows=863 width=8)
   Merge Key: x
   ->  Sort  (cost=20329.25..20331.40 rows=432 width=8)
         Sort Key: public.csq_t1.x
         ->  Append  (cost=0.00..20287.16 rows=432 width=8)
               ->  Seq Scan on csq_t1_1_prt_1 csq_t1  (cost=0.00..0.00 rows=1 width=8)
                     Filter: (subplan)
                     SubPlan 1
                       ->  Result  (cost=0.00..23.53 rows=2 width=4)
                             ->  Append  (cost=0.00..23.53 rows=2 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice1; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_1 csq_t2  (cost=0.00..0.00 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice2; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_2 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice3; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_3 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice4; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_4 csq_t2  (cost=0.00..0.00 rows=1 width=4)
               ->  Seq Scan on csq_t1_1_prt_2 csq_t1  (cost=0.00..10143.58 rows=216 width=8)
                     Filter: (subplan)
                     SubPlan 1
                       ->  Result  (cost=0.00..23.53 rows=2 width=4)
                             ->  Append  (cost=0.00..23.53 rows=2 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice5; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_1 csq_t2  (cost=0.00..0.00 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice6; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_2 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice7; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_3 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice8; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_4 csq_t2  (cost=0.00..0.00 rows=1 width=4)
               ->  Seq Scan on csq_t1_1_prt_3 csq_t1  (cost=0.00..10143.58 rows=216 width=8)
                     Filter: (subplan)
                     SubPlan 1
                       ->  Result  (cost=0.00..23.53 rows=2 width=4)
                             ->  Append  (cost=0.00..23.53 rows=2 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice9; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_1 csq_t2  (cost=0.00..0.00 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice10; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_2 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice11; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_3 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice12; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_4 csq_t2  (cost=0.00..0.00 rows=1 width=4)
               ->  Seq Scan on csq_t1_1_prt_4 csq_t1  (cost=0.00..0.00 rows=1 width=8)
                     Filter: (subplan)
                     SubPlan 1
                       ->  Result  (cost=0.00..23.53 rows=2 width=4)
                             ->  Append  (cost=0.00..23.53 rows=2 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice13; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_1 csq_t2  (cost=0.00..0.00 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice14; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_2 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=11.76..11.77 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=11.76..11.77 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice15; segments: 2)  (cost=0.00..11.76 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_3 csq_t2  (cost=0.00..11.76 rows=1 width=4)
                                   ->  Result  (cost=0.00..0.01 rows=1 width=4)
                                         Filter: public.csq_t2.y = $0
                                         ->  Materialize  (cost=0.00..0.01 rows=1 width=4)
                                               ->  Broadcast Motion 2:2  (slice16; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                                                     ->  Seq Scan on csq_t2_1_prt_4 csq_t2  (cost=0.00..0.00 rows=1 width=4)
 Settings:  optimizer_segments=3
(106 rows)

select * from csq_t1 where csq_t1.x >ALL (select csq_t2.x from csq_t2 where csq_t2.y=csq_t1.y) order by 1; -- expected (4,2)
 x | y 
---+---
 4 | 2
(1 row)

drop table if exists csq_t1;
drop table if exists csq_t2;
drop table if exists csq_t1_base;
drop table if exists csq_t2_base;
--
-- Multi-row subqueries
--
drop table if exists mrs_t1;
NOTICE:  table "mrs_t1" does not exist, skipping
create table mrs_t1(x int) distributed by (x);
insert into mrs_t1 select generate_series(1,20);
explain select * from mrs_t1 where exists (select x from mrs_t1 where x < -1);
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=2.27..4.47 rows=20 width=4)
   ->  Result  (cost=2.27..4.47 rows=10 width=4)
         One-Time Filter: $0
         InitPlan  (slice3)
           ->  Limit  (cost=0.00..2.27 rows=1 width=0)
                 ->  Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..2.27 rows=1 width=0)
                       ->  Limit  (cost=0.00..2.25 rows=1 width=0)
                             ->  Seq Scan on mrs_t1  (cost=0.00..2.25 rows=1 width=0)
                                   Filter: x < (-1)
         ->  Seq Scan on mrs_t1  (cost=0.00..2.20 rows=10 width=4)
 Settings:  optimizer_segments=3
(11 rows)

select * from mrs_t1 where exists (select x from mrs_t1 where x < -1) order by 1;
 x 
---
(0 rows)

explain select * from mrs_t1 where exists (select x from mrs_t1 where x = 1);
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=2.27..4.47 rows=20 width=4)
   ->  Result  (cost=2.27..4.47 rows=10 width=4)
         One-Time Filter: $0
         InitPlan  (slice3)
           ->  Limit  (cost=0.00..2.27 rows=1 width=0)
                 ->  Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..2.27 rows=1 width=0)
                       ->  Limit  (cost=0.00..2.25 rows=1 width=0)
                             ->  Seq Scan on mrs_t1  (cost=0.00..2.25 rows=1 width=0)
                                   Filter: x = 1
         ->  Seq Scan on mrs_t1  (cost=0.00..2.20 rows=10 width=4)
 Settings:  optimizer_segments=3
(11 rows)

select * from mrs_t1 where exists (select x from mrs_t1 where x = 1) order by 1;
 x  
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
(20 rows)

explain select * from mrs_t1 where x in (select x-95 from mrs_t1) or x < 5;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=0.00..25.30 rows=12 width=4)
   ->  Seq Scan on mrs_t1  (cost=0.00..25.30 rows=6 width=4)
         Filter: ((subplan)) OR x < 5
         SubPlan 1
           ->  Materialize  (cost=2.27..2.47 rows=10 width=4)
                 ->  Broadcast Motion 2:2  (slice1; segments: 2)  (cost=0.00..2.25 rows=10 width=4)
                       ->  Seq Scan on mrs_t1  (cost=0.00..2.25 rows=10 width=4)
 Settings:  optimizer_segments=3
(8 rows)

select * from mrs_t1 where x in (select x-95 from mrs_t1) or x < 5 order by 1;
 x 
---
 1
 2
 3
 4
(4 rows)

drop table if exists mrs_t1;
--
-- Multi-row subquery from MSTR
--
drop table if exists mrs_u1;
NOTICE:  table "mrs_u1" does not exist, skipping
drop table if exists mrs_u2;
NOTICE:  table "mrs_u2" does not exist, skipping
create TABLE mrs_u1 (a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create TABLE mrs_u2 (a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into mrs_u1 values (1,2),(11,22);
insert into mrs_u2 values (1,2),(11,22),(33,44);
select * from mrs_u1 join mrs_u2 on mrs_u1.a=mrs_u2.a where mrs_u1.a in (1,11) or mrs_u2.a in (select a from mrs_u1 where a=1) order by 1;
 a  | b  | a  | b  
----+----+----+----
  1 |  2 |  1 |  2
 11 | 22 | 11 | 22
(2 rows)

drop table if exists mrs_u1;
drop table if exists mrs_u2;
--
-- MPP-13758
--
drop table if exists csq_m1;
NOTICE:  table "csq_m1" does not exist, skipping
create table csq_m1();
set allow_system_table_mods='DML';
delete from gp_distribution_policy where localoid='csq_m1'::regclass;
reset allow_system_table_mods;
alter table csq_m1 add column x int;
insert into csq_m1 values(1);
drop table if exists csq_d1;
NOTICE:  table "csq_d1" does not exist, skipping
create table csq_d1(x int) distributed by (x);
insert into csq_d1 select * from csq_m1;
explain select array(select x from csq_m1); -- no initplan
                          QUERY PLAN                          
--------------------------------------------------------------
 Result  (cost=1.01..1.02 rows=1 width=0)
   InitPlan
     ->  Seq Scan on csq_m1  (cost=0.00..1.01 rows=2 width=4)
 Settings:  optimizer_segments=3
(4 rows)

select array(select x from csq_m1); -- {1}
 ?column? 
----------
 {1}
(1 row)

explain select array(select x from csq_d1); -- initplan
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Result  (cost=1.01..1.02 rows=1 width=0)
   InitPlan  (slice2)
     ->  Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.01 rows=1 width=4)
           ->  Seq Scan on csq_d1  (cost=0.00..1.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(5 rows)

select array(select x from csq_d1); -- {1}
 ?column? 
----------
 {1}
(1 row)

--
-- CSQs involving master-only and distributed tables
--
drop table if exists t3coquicklz;
NOTICE:  table "t3coquicklz" does not exist, skipping
create table t3coquicklz (c1 int , c2 varchar) with (appendonly=true, compresstype=quicklz, orientation=column) distributed by (c1);
drop table if exists pg_attribute_storage;
NOTICE:  table "pg_attribute_storage" does not exist, skipping
create table pg_attribute_storage (attrelid int, attnum int, attoptions text[]) distributed by (attrelid);
insert into pg_attribute_storage values ('t3coquicklz'::regclass, 1, E'{\'something\'}');
insert into pg_attribute_storage values ('t3coquicklz'::regclass, 2, E'{\'something2\'}');
SELECT a.attname
, pg_catalog.format_type(a.atttypid, a.atttypmod)
, ( SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
 FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
)
, a.attnotnull
, a.attnum
, a.attstorage
, pg_catalog.col_description(a.attrelid, a.attnum)
, ( SELECT s.attoptions
FROM pg_attribute_storage s
WHERE s.attrelid = a.attrelid AND s.attnum = a.attnum
) newcolumn
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 't3coquicklz'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
; -- expect to see 2 rows
 attname |    format_type    | ?column? | attnotnull | attnum | attstorage | col_description |   newcolumn    
---------+-------------------+----------+------------+--------+------------+-----------------+----------------
 c1      | integer           |          | f          |      1 | p          |                 | {'something'}
 c2      | character varying |          | f          |      2 | x          |                 | {'something2'}
(2 rows)

--
-- More CSQs involving master-only and distributed relations
--
drop table if exists csq_m1;
create table csq_m1();
set allow_system_table_mods='DML';
delete from gp_distribution_policy where localoid='csq_m1'::regclass;
reset allow_system_table_mods;
alter table csq_m1 add column x int;
insert into csq_m1 values(1),(2),(3);
drop table if exists csq_d1;
create table csq_d1(x int) distributed by (x);
insert into csq_d1 select * from csq_m1 where x < 3;
insert into csq_d1 values(4);
select * from csq_m1;
 x 
---
 1
 2
 3
(3 rows)

select * from csq_d1;
 x 
---
 1
 2
 4
(3 rows)

--
-- outer plan node is master-only and CSQ has distributed relation
--
explain select * from csq_m1 where x not in (select x from csq_d1) or x < -100; -- gather motion
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Seq Scan on csq_m1  (cost=0.00..4.08 rows=3 width=4)
   Filter: (NOT ((subplan))) OR x < (-100)
   SubPlan 1
     ->  Materialize  (cost=2.02..2.04 rows=1 width=4)
           ->  Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..2.02 rows=2 width=4)
                 ->  Seq Scan on csq_d1  (cost=0.00..2.02 rows=1 width=4)
 Settings:  optimizer_segments=3
(7 rows)

select * from csq_m1 where x not in (select x from csq_d1) or x < -100; -- (3)
 x 
---
 3
(1 row)

--
-- outer plan node is master-only and CSQ has distributed relation
--
explain select * from csq_d1 where x not in (select x from csq_m1) or x < -100; -- broadcast motion
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=0.00..3.07 rows=1 width=4)
   ->  Seq Scan on csq_d1  (cost=0.00..3.07 rows=1 width=4)
         Filter: (NOT ((subplan))) OR x < (-100)
         SubPlan 1
           ->  Materialize  (cost=1.04..1.10 rows=6 width=4)
                 ->  Broadcast Motion 1:2  (slice1)  (cost=0.00..1.03 rows=6 width=4)
                       ->  Seq Scan on csq_m1  (cost=0.00..1.03 rows=6 width=4)
 Settings:  optimizer_segments=3
(8 rows)

select * from csq_d1 where x not in (select x from csq_m1) or x < -100; -- (4)
 x 
---
 4
(1 row)

--
-- MPP-14441 Don't lose track of initplans
--
drop table if exists csq_t1;
NOTICE:  table "csq_t1" does not exist, skipping
CREATE TABLE csq_t1 (a int, b int, c int, d int, e text) DISTRIBUTED BY (a);
INSERT INTO csq_t1 SELECT i, i/3, i%2, 100-i, 'text'||i  FROM generate_series(1,100) i;
select count(*) from csq_t1 t1 where a > (SELECT x.b FROM ( select avg(a)::int as b,'haha'::text from csq_t1 t2 where t2.a=t1.d) x ) ;
 count 
-------
    49
(1 row)

select count(*) from csq_t1 t1 where a > ( select avg(a)::int from csq_t1 t2 where t2.a=t1.d) ;
 count 
-------
    49
(1 row)

--
-- correlation in a func expr
--
CREATE FUNCTION csq_f(a int) RETURNS int AS $$ select $1 $$ LANGUAGE SQL CONTAINS SQL;
CREATE TABLE csq_r(a int) distributed by (a);
INSERT INTO csq_r VALUES (1);
-- subqueries shouldn't be pulled into a join if the from clause has a function call
-- with a correlated argument
-- force_explain
explain SELECT * FROM csq_r WHERE a IN (SELECT * FROM csq_f(csq_r.a));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: (subplan)
         SubPlan 1
           ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(6 rows)

SELECT * FROM csq_r WHERE a IN (SELECT * FROM csq_f(csq_r.a));
 a 
---
 1
(1 row)

-- force_explain
explain SELECT * FROM csq_r WHERE a not IN (SELECT * FROM csq_f(csq_r.a));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: (subplan)
         SubPlan 1
           ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(6 rows)

SELECT * FROM csq_r WHERE a not IN (SELECT * FROM csq_f(csq_r.a));
 a 
---
(0 rows)

-- force_explain
explain SELECT * FROM csq_r WHERE exists (SELECT * FROM csq_f(csq_r.a));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: (subplan)
         SubPlan 1
           ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(6 rows)

SELECT * FROM csq_r WHERE exists (SELECT * FROM csq_f(csq_r.a));
 a 
---
 1
(1 row)

-- force_explain
explain SELECT * FROM csq_r WHERE not exists (SELECT * FROM csq_f(csq_r.a));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: NOT ((subplan))
         SubPlan 1
           ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(6 rows)

SELECT * FROM csq_r WHERE not exists (SELECT * FROM csq_f(csq_r.a));
 a 
---
(0 rows)

-- force_explain
explain SELECT * FROM csq_r WHERE a > (SELECT csq_f FROM csq_f(csq_r.a) limit 1);
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: a > ((subplan))
         SubPlan 1
           ->  Limit  (cost=0.00..0.01 rows=1 width=4)
                 ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(7 rows)

SELECT * FROM csq_r WHERE a > (SELECT csq_f FROM csq_f(csq_r.a) limit 1);
 a 
---
(0 rows)

-- force_explain
explain SELECT * FROM csq_r WHERE a < ANY (SELECT csq_f FROM csq_f(csq_r.a));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: (subplan)
         SubPlan 1
           ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(6 rows)

SELECT * FROM csq_r WHERE a < ANY (SELECT csq_f FROM csq_f(csq_r.a));
 a 
---
(0 rows)

-- force_explain
explain SELECT * FROM csq_r WHERE a <= ALL (SELECT csq_f FROM csq_f(csq_r.a));
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1.02 rows=1 width=4)
   ->  Seq Scan on csq_r  (cost=0.00..1.02 rows=1 width=4)
         Filter: (subplan)
         SubPlan 1
           ->  Function Scan on csq_f  (cost=0.00..0.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(6 rows)

SELECT * FROM csq_r WHERE a <= ALL (SELECT csq_f FROM csq_f(csq_r.a));
 a 
---
 1
(1 row)

-- fails: correlation in distributed subplan
-- force_explain
explain SELECT * FROM csq_r WHERE a IN (SELECT csq_f FROM csq_f(csq_r.a),csq_r);
ERROR:  Cannot parallelize that query yet.
DETAIL:  In a subquery FROM clause, a function invocation cannot contain a correlated reference.
--
-- Test pullup of expr CSQs to joins
--
--
-- Test data
--
drop table if exists csq_pullup;
NOTICE:  table "csq_pullup" does not exist, skipping
create table csq_pullup(t text, n numeric, i int, v varchar(10)) distributed by (t);
insert into csq_pullup values ('abc',1, 2, 'xyz');
insert into csq_pullup values ('xyz',2, 3, 'def');  
insert into csq_pullup values ('def',3, 1, 'abc'); 
--
-- Expr CSQs to joins
--
--
-- text, text
--
explain select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.t=t1.t);
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=1.06..2.11 rows=4 width=19)
   ->  Hash Join  (cost=1.06..2.11 rows=2 width=19)
         Hash Cond: t0.t = "Expr_SUBQUERY".csq_c0
         ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.04..1.04 rows=1 width=32)
               ->  Subquery Scan "Expr_SUBQUERY"  (cost=1.02..1.04 rows=1 width=32)
                     ->  HashAggregate  (cost=1.02..1.03 rows=1 width=40)
                           Filter: 1 = count(*)
                           Group By: t1.t
                           ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(11 rows)

select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.t=t1.t);
  t  | n | i |  v  
-----+---+---+-----
 xyz | 2 | 3 | def
 def | 3 | 1 | abc
 abc | 1 | 2 | xyz
(3 rows)

--
-- text, varchar
--
explain select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.t=t1.v);
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=1.09..2.14 rows=4 width=19)
   ->  Hash Join  (cost=1.09..2.14 rows=2 width=19)
         Hash Cond: t0.t = "Expr_SUBQUERY".csq_c0
         ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.08..1.08 rows=1 width=32)
               ->  Subquery Scan "Expr_SUBQUERY"  (cost=1.05..1.08 rows=1 width=32)
                     ->  HashAggregate  (cost=1.05..1.07 rows=1 width=40)
                           Filter: 1 = count(partial_aggregation.unnamed_attr_2)
                           Group By: "?column1?"
                           ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=1.02..1.04 rows=1 width=40)
                                 Hash Key: unnamed_attr_1
                                 ->  HashAggregate  (cost=1.02..1.02 rows=1 width=40)
                                       Group By: t1.v::text
                                       ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(15 rows)

select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.t=t1.v);
  t  | n | i |  v  
-----+---+---+-----
 abc | 1 | 2 | xyz
 xyz | 2 | 3 | def
 def | 3 | 1 | abc
(3 rows)

--
-- numeric, numeric
--
explain select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.n=t1.n);
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice3; segments: 2)  (cost=1.09..2.16 rows=4 width=19)
   ->  Hash Join  (cost=1.09..2.16 rows=2 width=19)
         Hash Cond: t0.n = "Expr_SUBQUERY".csq_c0
         ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..1.03 rows=1 width=19)
               Hash Key: t0.n
               ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.08..1.08 rows=1 width=32)
               ->  Subquery Scan "Expr_SUBQUERY"  (cost=1.05..1.08 rows=1 width=32)
                     ->  HashAggregate  (cost=1.05..1.07 rows=1 width=40)
                           Filter: 1 = count(partial_aggregation.unnamed_attr_2)
                           Group By: t1.n
                           ->  Redistribute Motion 2:2  (slice2; segments: 2)  (cost=1.02..1.04 rows=1 width=40)
                                 Hash Key: t1.n
                                 ->  HashAggregate  (cost=1.02..1.02 rows=1 width=40)
                                       Group By: t1.n
                                       ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=7)
 Settings:  optimizer_segments=3
(17 rows)

select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.n=t1.n);
  t  | n | i |  v  
-----+---+---+-----
 def | 3 | 1 | abc
 abc | 1 | 2 | xyz
 xyz | 2 | 3 | def
(3 rows)

--
-- function(numeric), function(numeric)
--
explain select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.n + 1=t1.n + 1);
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice3; segments: 2)  (cost=1.10..2.17 rows=4 width=19)
   ->  Hash Join  (cost=1.10..2.17 rows=2 width=19)
         Hash Cond: (t0.n + 1::numeric) = "Expr_SUBQUERY".csq_c0
         ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..1.03 rows=1 width=19)
               Hash Key: t0.n + 1::numeric
               ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.09..1.09 rows=1 width=32)
               ->  Subquery Scan "Expr_SUBQUERY"  (cost=1.06..1.09 rows=1 width=32)
                     ->  HashAggregate  (cost=1.06..1.08 rows=1 width=40)
                           Filter: 1 = count(partial_aggregation.unnamed_attr_2)
                           Group By: "?column1?"
                           ->  Redistribute Motion 2:2  (slice2; segments: 2)  (cost=1.02..1.04 rows=1 width=40)
                                 Hash Key: unnamed_attr_1
                                 ->  HashAggregate  (cost=1.02..1.02 rows=1 width=40)
                                       Group By: t1.n + 1::numeric
                                       ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=7)
 Settings:  optimizer_segments=3
(17 rows)

select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.n + 1=t1.n + 1);
  t  | n | i |  v  
-----+---+---+-----
 xyz | 2 | 3 | def
 def | 3 | 1 | abc
 abc | 1 | 2 | xyz
(3 rows)

--
-- function(numeric), function(int)
--
explain select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.n + 1=t1.i + 1);
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice3; segments: 2)  (cost=1.10..2.18 rows=4 width=19)
   ->  Hash Join  (cost=1.10..2.18 rows=2 width=19)
         Hash Cond: (t0.n + 1::numeric) = "Expr_SUBQUERY".csq_c0
         ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..1.03 rows=1 width=19)
               Hash Key: t0.n + 1::numeric
               ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.09..1.09 rows=1 width=32)
               ->  Subquery Scan "Expr_SUBQUERY"  (cost=1.06..1.09 rows=1 width=32)
                     ->  HashAggregate  (cost=1.06..1.08 rows=1 width=40)
                           Filter: 1 = count(partial_aggregation.unnamed_attr_2)
                           Group By: "?column1?"
                           ->  Redistribute Motion 2:2  (slice2; segments: 2)  (cost=1.02..1.05 rows=1 width=40)
                                 Hash Key: unnamed_attr_1
                                 ->  HashAggregate  (cost=1.02..1.03 rows=1 width=40)
                                       Group By: (t1.i + 1)::numeric
                                       ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=4)
 Settings:  optimizer_segments=3
(17 rows)

select * from csq_pullup t0 where 1= (select count(*) from csq_pullup t1 where t0.n + 1=t1.i + 1);
  t  | n | i |  v  
-----+---+---+-----
 def | 3 | 1 | abc
 abc | 1 | 2 | xyz
 xyz | 2 | 3 | def
(3 rows)

--
-- NOT EXISTS CSQs to joins
--
--
-- text, text
--
explain select * from csq_pullup t0 where not exists (select 1 from csq_pullup t1 where t0.t=t1.t and t1.i = 1);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=1.02..2.07 rows=4 width=19)
   ->  Hash Left Anti Semi Join  (cost=1.02..2.07 rows=2 width=19)
         Hash Cond: t0.t = t1.t
         ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.01..1.01 rows=1 width=4)
               ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=4)
                     Filter: i = 1 AND t IS NOT NULL
 Settings:  optimizer_segments=3
(8 rows)

select * from csq_pullup t0 where not exists (select 1 from csq_pullup t1 where t0.t=t1.t and t1.i = 1);
  t  | n | i |  v  
-----+---+---+-----
 abc | 1 | 2 | xyz
 xyz | 2 | 3 | def
(2 rows)

--
-- int, function(int)
--
explain select * from csq_pullup t0 where not exists (select 1 from csq_pullup t1 where t0.i=t1.i + 1);
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=1.07..2.12 rows=4 width=19)
   ->  Hash Left Anti Semi Join  (cost=1.07..2.12 rows=2 width=19)
         Hash Cond: t0.i = (t1.i + 1)
         ->  Seq Scan on csq_pullup t0  (cost=0.00..1.01 rows=1 width=19)
         ->  Hash  (cost=1.04..1.04 rows=1 width=4)
               ->  Broadcast Motion 2:2  (slice1; segments: 2)  (cost=0.00..1.04 rows=1 width=4)
                     ->  Seq Scan on csq_pullup t1  (cost=0.00..1.01 rows=1 width=4)
                           Filter: (i + 1) IS NOT NULL
 Settings:  optimizer_segments=3
(9 rows)

select * from csq_pullup t0 where not exists (select 1 from csq_pullup t1 where t0.i=t1.i + 1);
  t  | n | i |  v  
-----+---+---+-----
 def | 3 | 1 | abc
(1 row)

--
-- wrong results bug MPP-16477
--
drop table if exists subselect_t1;
NOTICE:  table "subselect_t1" does not exist, skipping
drop table if exists subselect_t2;
NOTICE:  table "subselect_t2" does not exist, skipping
create table subselect_t1(x int) distributed by (x);
insert into subselect_t1 values(1),(2);
create table subselect_t2(y int) distributed by (y);
insert into subselect_t2 values(1),(2),(2);
analyze subselect_t1;
analyze subselect_t2;
explain select * from subselect_t1 where x in (select y from subselect_t2);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=2.07..4.13 rows=4 width=4)
   ->  Hash EXISTS Join  (cost=2.07..4.13 rows=2 width=4)
         Hash Cond: subselect_t1.x = subselect_t2.y
         ->  Seq Scan on subselect_t1  (cost=0.00..2.02 rows=1 width=4)
         ->  Hash  (cost=2.03..2.03 rows=2 width=4)
               ->  Seq Scan on subselect_t2  (cost=0.00..2.03 rows=2 width=4)
 Settings:  optimizer_segments=3
(7 rows)

select * from subselect_t1 where x in (select y from subselect_t2);
 x 
---
 1
 2
(2 rows)

-- start_ignore
-- Known_opt_diff: MPP-21351
-- end_ignore
explain select * from subselect_t1 where x in (select y from subselect_t2 union all select y from subselect_t2);
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice2; segments: 2)  (cost=4.37..6.44 rows=4 width=4)
   ->  Hash EXISTS Join  (cost=4.37..6.44 rows=2 width=4)
         Hash Cond: subselect_t1.x = "IN_subquery".y
         ->  Seq Scan on subselect_t1  (cost=0.00..2.02 rows=1 width=4)
         ->  Hash  (cost=4.30..4.30 rows=3 width=4)
               ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..4.30 rows=3 width=4)
                     Hash Key: "IN_subquery".y
                     ->  Append  (cost=0.00..4.12 rows=3 width=4)
                           ->  Seq Scan on subselect_t2  (cost=0.00..2.03 rows=2 width=4)
                           ->  Seq Scan on subselect_t2  (cost=0.00..2.03 rows=2 width=4)
 Settings:  optimizer_segments=3
(11 rows)

select * from subselect_t1 where x in (select y from subselect_t2 union all select y from subselect_t2);
 x 
---
 2
 1
(2 rows)

explain select count(*) from subselect_t1 where x in (select y from subselect_t2);
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Aggregate  (cost=4.20..4.21 rows=1 width=8)
   ->  Gather Motion 2:1  (slice1; segments: 2)  (cost=4.14..4.18 rows=1 width=8)
         ->  Aggregate  (cost=4.14..4.15 rows=1 width=8)
               ->  Hash EXISTS Join  (cost=2.07..4.13 rows=2 width=0)
                     Hash Cond: subselect_t1.x = subselect_t2.y
                     ->  Seq Scan on subselect_t1  (cost=0.00..2.02 rows=1 width=4)
                     ->  Hash  (cost=2.03..2.03 rows=2 width=4)
                           ->  Seq Scan on subselect_t2  (cost=0.00..2.03 rows=2 width=4)
 Settings:  optimizer_segments=3
(9 rows)

select count(*) from subselect_t1 where x in (select y from subselect_t2);
 count 
-------
     2
(1 row)

-- start_ignore
-- Known_opt_diff: MPP-21351
-- end_ignore
explain select count(*) from subselect_t1 where x in (select y from subselect_t2 union all select y from subselect_t2);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=6.50..6.51 rows=1 width=8)
   ->  Gather Motion 2:1  (slice2; segments: 2)  (cost=6.45..6.49 rows=1 width=8)
         ->  Aggregate  (cost=6.45..6.46 rows=1 width=8)
               ->  Hash EXISTS Join  (cost=4.37..6.44 rows=2 width=0)
                     Hash Cond: subselect_t1.x = "IN_subquery".y
                     ->  Seq Scan on subselect_t1  (cost=0.00..2.02 rows=1 width=4)
                     ->  Hash  (cost=4.30..4.30 rows=3 width=4)
                           ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..4.30 rows=3 width=4)
                                 Hash Key: "IN_subquery".y
                                 ->  Append  (cost=0.00..4.12 rows=3 width=4)
                                       ->  Seq Scan on subselect_t2  (cost=0.00..2.03 rows=2 width=4)
                                       ->  Seq Scan on subselect_t2  (cost=0.00..2.03 rows=2 width=4)
 Settings:  optimizer_segments=3
(13 rows)

select count(*) from subselect_t1 where x in (select y from subselect_t2 union all select y from subselect_t2);
 count 
-------
     2
(1 row)

select count(*) from 
       ( select 1 as FIELD_1 union all select 2 as FIELD_1 ) TABLE_1 
       where FIELD_1 in ( select 1 as FIELD_1 union all select 1 as FIELD_1 union all select 1 as FIELD_1 );
 count 
-------
     1
(1 row)

       
---
--- Query was deadlocking because of not squelching subplans (MPP-18936)
---
drop table if exists t1; 
NOTICE:  table "t1" does not exist, skipping
drop table if exists t2; 
NOTICE:  table "t2" does not exist, skipping
drop table if exists t3; 
NOTICE:  table "t3" does not exist, skipping
drop table if exists t4; 
NOTICE:  table "t4" does not exist, skipping
CREATE TABLE t1 AS (SELECT generate_series(1, 5000) AS i, generate_series(5001, 10000) AS j);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE t2 AS (SELECT * FROM t1 WHERE gp_segment_id = 0);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE t3 AS (SELECT * FROM t1 WHERE gp_segment_id = 1);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE t4 (i1 int, i2 int); 
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
set gp_interconnect_queue_depth=1;
-- This query was deadlocking on a 2P system
INSERT INTO t4 
(
SELECT t1.i, (SELECT t3.i FROM t3 WHERE t3.i + 1 = t1.i + 1)
FROM t1, t3
WHERE t1.i = t3.i
)
UNION
(
SELECT t1.i, (SELECT t2.i FROM t2 WHERE t2.i + 1 = t1.i + 1)
FROM t1, t2
WHERE t1.i = t2.i
);
drop table if exists t1; 
drop table if exists t2; 
drop table if exists t3; 
drop table if exists t4; 
--
-- Initplans with no corresponding params should be removed MPP-20600
--
drop table if exists t1;
NOTICE:  table "t1" does not exist, skipping
drop table if exists t2;
NOTICE:  table "t2" does not exist, skipping
create table t1(a int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table t2(b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
select * from t1 where a=1 and a=2 and a > (select t2.b from t2);
 a 
---
(0 rows)

explain select * from t1 where a=1 and a=2 and a > (select t2.b from t2);
                QUERY PLAN                
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
   One-Time Filter: false
 Settings:  optimizer_segments=3
(3 rows)

explain select * from t1 where a=1 and a=2 and a > (select t2.b from t2)
union all
select * from t1 where a=1 and a=2 and a > (select t2.b from t2);
                   QUERY PLAN                   
------------------------------------------------
 Append  (cost=0.00..0.04 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
         One-Time Filter: false
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
         One-Time Filter: false
 Settings:  optimizer_segments=3
(6 rows)

select * from t1 where a=1 and a=2 and a > (select t2.b from t2)
union all
select * from t1 where a=1 and a=2 and a > (select t2.b from t2);
 a 
---
(0 rows)

explain select * from t1,
(select * from t1 where a=1 and a=2 and a > (select t2.b from t2)) foo
where t1.a = foo.a;
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice2; segments: 1)  (cost=0.00..0.01 rows=4 width=8)
   ->  Result  (cost=0.00..0.01 rows=2 width=8)
         One-Time Filter: false
         InitPlan  (slice3)
           ->  Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..0.00 rows=1 width=4)
                 ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=4)
         ->  Nested Loop  (cost=0.00..0.01 rows=2 width=8)
               ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=4)
                     Filter: a > $0 AND a = 1
               ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=4)
                     Filter: a > $0 AND a = 1
 Settings:  optimizer_segments=3
(12 rows)

select * from t1,
(select * from t1 where a=1 and a=2 and a > (select t2.b from t2)) foo
where t1.a = foo.a;
 a | a 
---+---
(0 rows)

drop table if exists t1;
drop table if exists t2;
--
-- Test for a bug we used to have with eliminating InitPlans. The subplan,
-- (select max(content) from y), was eliminated when it shouldn't have been.
-- The query is supposed to return 0 rows, but returned > 0 when the bug was
-- present.
--
CREATE TABLE initplan_x (i int4, t text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into initplan_x values
 (1, 'foobar1'),
 (2, 'foobar2'),
 (3, 'foobar3'),
 (4, 'foobar4'),
 (5, 'foobar5');
CREATE TABLE initplan_y (content int4);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'content' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into initplan_y values (5);
select i, t from initplan_x
except
select g, t from initplan_x,
                 generate_series(0, (select max(content) from initplan_y)) g
order by 1;
 i | t 
---+---
(0 rows)

drop table if exists initplan_x;
drop table if exists initplan_y;
--
-- apply parallelization for subplan MPP-24563
--
create table t1_mpp_24563 (id int, value int) distributed by (id);
insert into t1_mpp_24563 values (1, 3);
create table t2_mpp_24563 (id int, value int, seq int) distributed by (id);
insert into t2_mpp_24563 values (1, 7, 5);
explain select row_number() over (order by seq asc) as id, foo.cnt
from
(select seq, (select count(*) from t1_mpp_24563 t1 where t1.id = t2.id) cnt from
	t2_mpp_24563 t2 where value = 7) foo;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Window  (cost=1.02..2.10 rows=1 width=8)
   Order By: t2.seq
   ->  Gather Motion 1:1  (slice2; segments: 1)  (cost=1.02..1.05 rows=1 width=8)
         Merge Key: t2.seq
         ->  Sort  (cost=1.02..1.03 rows=1 width=8)
               Sort Key: t2.seq
               ->  Seq Scan on t2_mpp_24563 t2  (cost=0.00..1.01 rows=1 width=8)
                     Filter: value = 7
   SubPlan 1
     ->  Aggregate  (cost=1.04..1.05 rows=1 width=8)
           ->  Result  (cost=1.01..1.02 rows=1 width=0)
                 Filter: t1.id = $0
                 ->  Materialize  (cost=1.01..1.02 rows=1 width=0)
                       ->  Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..1.01 rows=1 width=0)
                             ->  Seq Scan on t1_mpp_24563 t1  (cost=0.00..1.01 rows=1 width=0)
 Settings:  optimizer_segments=3
(16 rows)

drop table t1_mpp_24563;
drop table t2_mpp_24563;
--
-- MPP-20470 update the flow of node after parallelizing subplan.
--
CREATE TABLE t_mpp_20470 (
    col_date timestamp without time zone,
    col_name character varying(6),
    col_expiry date
) DISTRIBUTED BY (col_date) PARTITION BY RANGE(col_date)
(
START ('2013-05-10 00:00:00'::timestamp without time zone) END ('2013-05-11
	00:00:00'::timestamp without time zone) WITH (tablename='t_mpp_20470_ptr1', appendonly=false ),
START ('2013-05-24 00:00:00'::timestamp without time zone) END ('2013-05-25
	00:00:00'::timestamp without time zone) WITH (tablename='t_mpp_20470_ptr2', appendonly=false )
);
NOTICE:  CREATE TABLE will create partition "t_mpp_20470_ptr1" for table "t_mpp_20470"
NOTICE:  CREATE TABLE will create partition "t_mpp_20470_ptr2" for table "t_mpp_20470"
COPY t_mpp_20470 from STDIN delimiter '|' null '';
create view v1_mpp_20470 as
SELECT
CASE
	WHEN  b.col_name::text = 'FUTCUR'::text
	THEN  ( SELECT count(a.col_expiry) AS count FROM t_mpp_20470 a WHERE
		a.col_name::text = b.col_name::text)::text
	ELSE 'Q2'::text END  AS  cc,  1 AS nn
FROM t_mpp_20470 b;
explain SELECT  cc, sum(nn) over() FROM v1_mpp_20470;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=81.92..12932.78 rows=934 width=28)
   ->  Aggregate  (cost=41.63..41.64 rows=1 width=0)
         ->  Shared Scan (share slice:id 0:0)  (cost=40.29..41.63 rows=934 width=28)
               ->  Materialize  (cost=30.95..40.29 rows=934 width=28)
                     ->  Gather Motion 1:1  (slice3; segments: 1)  (cost=0.00..30.02 rows=934 width=28)
                           ->  Append  (cost=0.00..11.34 rows=934 width=28)
                                 ->  Seq Scan on t_mpp_20470_ptr1 b  (cost=0.00..5.67 rows=467 width=28)
                                 ->  Seq Scan on t_mpp_20470_ptr2 b  (cost=0.00..5.67 rows=467 width=28)
   ->  Subquery Scan coplan  (cost=40.29..50.97 rows=934 width=28)
         ->  Window  (cost=40.29..41.63 rows=934 width=28)
               ->  Shared Scan (share slice:id 0:0)  (cost=40.29..41.63 rows=934 width=28)
   SubPlan 1
     ->  Aggregate  (cost=13.72..13.73 rows=1 width=8)
           ->  Result  (cost=0.00..13.68 rows=2 width=4)
                 ->  Append  (cost=0.00..13.68 rows=2 width=4)
                       ->  Result  (cost=6.84..6.85 rows=1 width=4)
                             Filter: a.col_name::text = $0::text
                             ->  Materialize  (cost=6.84..6.85 rows=1 width=4)
                                   ->  Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..6.84 rows=1 width=4)
                                         ->  Seq Scan on t_mpp_20470_ptr1 a  (cost=0.00..6.84 rows=1 width=4)
                       ->  Result  (cost=6.84..6.85 rows=1 width=4)
                             Filter: a.col_name::text = $0::text
                             ->  Materialize  (cost=6.84..6.85 rows=1 width=4)
                                   ->  Gather Motion 1:1  (slice2; segments: 1)  (cost=0.00..6.84 rows=1 width=4)
                                         ->  Seq Scan on t_mpp_20470_ptr2 a  (cost=0.00..6.84 rows=1 width=4)
 Settings:  optimizer_segments=3
(26 rows)

drop view v1_mpp_20470;
drop table t_mpp_20470;
create table tbl_25484(id int, num int) distributed by (id);
insert into tbl_25484 values(1, 1), (2, 2), (3, 3);
select id from tbl_25484 where 3 = (select 3 where 3 = (select num));
 id
----
  3
(1 row)

drop table tbl_25484;
reset optimizer_segments;
reset optimizer_nestloop_factor;
--
-- Test case that once triggered a bug in the IN-clause pull-up code.
--
SELECT p.id
    FROM (SELECT * FROM generate_series(1,10) id
          WHERE id IN (
              SELECT 1
              UNION ALL
              SELECT 0)) p;
 id 
----
  1
(1 row)

--
-- Verify another bug in the IN-clause pull-up code. This returned some
-- rows from xsupplier twice, because of a bug in detecting whether a
-- Redistribute node was needed.
--
CREATE TABLE xlineitem (l_orderkey int4, l_suppkey int4) distributed by (l_orderkey);
insert into xlineitem select g+3, g from generate_series(10,100) g;
insert into xlineitem select g+1, g from generate_series(10,100) g;
insert into xlineitem select g, g from generate_series(10,100) g;
CREATE TABLE xsupplier (s_suppkey int4, s_name text) distributed by (s_suppkey);
insert into xsupplier select g, 'foo' || g from generate_series(1,10) g;
select s_name from xsupplier
where s_suppkey in (
  select g.l_suppkey from xlineitem g
) ;
 s_name 
--------
 foo10
(1 row)

--
-- Another case that failed at one point. (A planner bug in pulling up a
-- subquery with constant distribution key, 1, in the outer queries.)
--
create table nested_in_tbl(tc1 int, tc2 int) distributed by (tc1);
select * from nested_in_tbl t1  where tc1 in
  (select 1 from nested_in_tbl t2 where tc1 in
    (select 1 from nested_in_tbl t3 where t3.tc2 = t2.tc2));
 tc1 | tc2 
-----+-----
(0 rows)

drop table nested_in_tbl;