# test blocking operator for material
--disable_warnings
drop table if exists t1;
drop table if exists t2;
--enable_warnings
create table t1(c1 bigint primary key,c2 bigint) partition by hash (c1) partitions 10;
create table t2(c1 bigint primary key,c2 bigint) partition by hash (c1) partitions 6;
insert into t1 values(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(0,1),(7,8),(8,9),(9,10);
insert into t2 values(1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(0,1),(7,8),(8,9),(9,10);
--sleep 1
commit;

##need add materail for the left child of merge join
explain basic
select /*+ USE_PX parallel(3) */* from
(select c1,count(*) over(partition by c1) c2 from
 (select c1,count(c2) c2 from t1 group by c1)c) a join
(select c1,count(*) c2 from t2 group by c1)b on a.c1=b.c1 ;
--sorted_result
select /*+ USE_PX parallel(3) */* from
(select c1,count(*) over(partition by c1) c2 from
 (select c1,count(c2) c2 from t1 group by c1)c) a join
(select c1,count(*) c2 from t2 group by c1)b on a.c1=b.c1;

##no need material operator for merge sort receice with local order
explain basic
select a.c2,count(*) from (select /*+ USE_PX parallel(3) PQ_DISTRIBUTE(b HASH HASH) */a.c1,a.c2,b.c1 c3,b.c2 c4 from t1 a join t2 b on a.c1=b.c1)a group by a.c2;
--sorted_result
select a.c2,count(*) from (select /*+ USE_PX parallel(3) PQ_DISTRIBUTE(b HASH HASH) */a.c1,a.c2,b.c1 c3,b.c2 c4 from t1 a join t2 b on a.c1=b.c1)a group by a.c2;