src/test/regress/sql/eagerfree.sql

drop table if exists smallt;

create table smallt (i int, t text, d date) distributed by (i);
insert into smallt select i%10, 'text ' || (i%15), '2011-01-01'::date + ((i%20) || ' days')::interval
from generate_series(0, 99) i;

drop table if exists bigt;

create table bigt (i int, t text, d date) distributed by (i);
insert into bigt select i/10, 'text ' || (i/15), '2011-01-01'::date + ((i/20) || ' days')::interval
from generate_series(0, 999999) i;

drop table if exists smallt2;
create table smallt2 (i int, t text, d date) distributed by (i);
insert into smallt2 select i%5, 'text ' || (i%10), '2011-01-01'::date + ((i%15) || ' days')::interval
from generate_series(0, 49) i;

-- HashAgg, Agg
select d, count(*) from smallt group by d;
explain analyze select d, count(*) from smallt group by d;

set statement_mem=2560;
select count(*) from (select i, t, d, count(*) from bigt group by i, t, d) tmp;
explain analyze select count(*) from (select i, t, d, count(*) from bigt group by i, t, d) tmp;
set statement_mem=128000;

-- DQA
set gp_enable_agg_distinct=off;
set gp_eager_one_phase_agg=on;
select count(distinct d) from smallt;
explain analyze select count(distinct d) from smallt;

set statement_mem=2560;
select count(distinct d) from bigt;
explain analyze select count(distinct d) from bigt;
set statement_mem=128000;

set gp_enable_agg_distinct=on;
set gp_eager_one_phase_agg=off;

-- Rescan on Agg (with Material in the inner side of nestloop)
set enable_nestloop=on;
set enable_hashjoin=off;
select t1.*, t2.* from
(select d, count(*) from smallt group by d) as t1, (select d, sum(i) from smallt group by d) as t2
where t1.d = t2.d;
explain analyze select t1.*, t2.* from
(select d, count(*) from smallt group by d) as t1, (select d, sum(i) from smallt group by d) as t2
where t1.d = t2.d;
set enable_nestloop=off;
set enable_hashjoin=on;

-- Rescan on Agg (with Material in the inner side of nestloop)
set enable_nestloop=on;
set enable_hashjoin=off;
select t1.*, t2.* from
(select i, count(*) from smallt group by i) as t1, (select i, sum(i) from smallt group by i) as t2
where t1.i = t2.i;
explain analyze select t1.*, t2.* from
(select i, count(*) from smallt group by i) as t1, (select i, sum(i) from smallt group by i) as t2
where t1.i = t2.i;
set enable_nestloop=off;
set enable_hashjoin=on;

-- Limit on Agg
select d, count(*) from smallt group by d limit 5; --ignore
explain analyze select d, count(*) from smallt group by d limit 5;

-- HashJoin
select t1.* from smallt as t1, smallt as t2 where t1.i = t2.i;
explain analyze select t1.* from smallt as t1, smallt as t2 where t1.i = t2.i;

-- Rescan on HashJoin
--select t1.* from (select t11.* from smallt as t11, smallt as t22 where t11.i = t22.i and t11.i < 2) as t1,
--   (select t11.* from smallt as t11, smallt as t22 where t11.d = t22.d and t11.i < 5) as t2;

-- Material in SubPlan
select smallt2.* from smallt2
where i < (select count(*) from smallt where smallt.i = smallt2.i);
explain select smallt2.* from smallt2
where i < (select count(*) from smallt where smallt.i = smallt2.i);

-- Append + Material in SubPlan
drop table if exists smallt_part;
drop table if exists smallt2_part;

create table smallt_part (i int, t text, d date)
partition by range (d) (start ('2011-01-01'::date) end ('2011-01-21'::date) every ('5 days'::interval));

create table smallt2_part (i int, t text, d date)
partition by range (d) (start ('2011-01-01'::date) end ('2011-01-16'::date) every ('4 days'::interval));

insert into smallt_part select i%10, 'text ' || (i%15), '2011-01-01'::date + ((i%20) || ' days')::interval
from generate_series(0, 99) i;

insert into smallt2_part select i%5, 'text ' || (i%10), '2011-01-01'::date + ((i%15) || ' days')::interval
from generate_series(0, 49) i;

select * from smallt_part where i < any (select count(*) from smallt2_part where smallt_part.i = smallt2_part.i group by d);
explain analyze select * from smallt_part where i < any (select count(*) from smallt2_part where smallt_part.i = smallt2_part.i group by d);

-- Sort in MergeJoin
set enable_hashjoin=off;
set enable_mergejoin=on;
select t1.* from smallt as t1, smallt as t2 where t1.i = t2.i and t1.i < 2;
explain analyze select t1.* from smallt as t1, smallt as t2 where t1.i = t2.i and t1.i < 2;

select t1.* from smallt as t1, smallt as t2 where t1.d = t2.d and t1.i < 2;
--start_ignore
explain analyze select t1.* from smallt as t1, smallt as t2 where t1.d = t2.d and t1.i < 2;
--end_ignore
set enable_hashjoin=on;
set enable_mergejoin=off;

-- ShareInputScan
--with my_group_max(i, maximum) as (select i, max(d) from smallt group by i)
--select smallt2.* from my_group_max, smallt2 where my_group_max.i = smallt2.i
--and smallt2.i < any (select maximum from my_group_max);
--explain analyze with my_group_max(i, maximum) as (select i, max(d) from smallt group by i)
--select smallt2.* from my_group_max, smallt2 where my_group_max.i = smallt2.i
--and smallt2.i < any (select maximum from my_group_max);

-- IndexScan
create index smallt_d_idx on smallt (d);
create index smallt2_d_idx on smallt2 (d);

set enable_hashjoin=off;
set enable_nestloop=on;
set enable_seqscan=off;
set enable_bitmapscan=off;
select smallt.* from smallt, smallt2 where smallt.i = smallt2.i and smallt2.d = '2011-01-04'::date
and smallt.d = '2011-01-04'::date;
explain analyze select smallt.* from smallt, smallt2 where smallt.i = smallt2.i and smallt2.d = '2011-01-04'::date
and smallt.d = '2011-01-04'::date;

-- BitmapScan
set enable_indexscan=off;
set enable_bitmapscan=on;
select smallt.* from smallt, smallt2 where smallt.i = smallt2.i and smallt2.d = '2011-01-04'::date
and smallt.d = '2011-01-04'::date;
explain analyze select smallt.* from smallt, smallt2 where smallt.i = smallt2.i and smallt2.d = '2011-01-04'::date
and smallt.d = '2011-01-04'::date;
set enable_hashjoin=on;
set enable_nestloop=off;
set enable_seqscan=on;
set enable_indexscan=on;

-- SubPlan
with my_group_sum(d, total) as (select d, sum(i) from smallt group by d)
select smallt2.* from smallt2
where i < all (select total from my_group_sum, smallt, smallt2 as tmp where my_group_sum.d = smallt.d and smallt.d = tmp.d and my_group_sum.d = smallt2.d)
and i = 0 order by 1,2,3; --order 1,2,3

select smallt2.* from smallt2
where i < all (select total from (select d, sum(i) as total from smallt group by d) as my_group_sum, smallt, smallt2 as tmp
    where my_group_sum.d = smallt.d and smallt.d = tmp.d and my_group_sum.d = smallt2.d)
and i = 0 order by 1,2,3; --order 1,2,3

-- Nested Subplan
drop table if exists r;
drop table if exists s;
drop table if exists t;
create table r (r1 int, r2 int, r3 int);
create table s (s1 int, s2 int, s3 int);
create table t (t1 int, t2 int, t3 int);
insert into r select generate_series(1, 20), generate_series(1, 5), generate_series(1, 8);
insert into s select generate_series(1, 20), generate_series(6, 10), generate_series(1, 4);
insert into t select generate_series(1, 30), generate_series(1, 6), generate_series(1, 5);

select * from t where t1 > (select min(r1) from r where r2<t2 and r3 > (Select min(s3) from s where s1<r1));