set hive.llap.io.enabled=false;
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10;

drop table if exists t0;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;

create table if not exists t0 (c00 int, c01 int, c03 TIMESTAMP) PARTITIONED BY (c02 string) stored as orc;
create table if not exists t1 (c10 int, c11 int, c12 int) PARTITIONED BY (c13 string) stored as orc;

create table if not exists t2 (c20 int) PARTITIONED BY (c21 string) stored as orc;
create table if not exists t3 (c30 TIMESTAMP) PARTITIONED BY (c31 string) stored as orc;


alter table t0 add partition(c02='test0');
alter table t1 add partition(c13='test1');
alter table t2 add partition(c21='test1');
alter table t3 add partition(c31='test2');


alter table t0 partition(c02='test0') update statistics set('numRows'='153373500','rawDataSize'='2053794707568');
alter table t1 partition(c13='test1') update statistics set('numRows'='1250000','rawDataSize'='2700000000');
alter table t2 partition(c21='test1') update statistics set('numRows'='475011','rawDataSize'='641987831');
alter table t3 partition(c31='test2') update statistics set('numRows'='136672296','rawDataSize'='141045810480');


set hive.explain.user=false;
set hive.cbo.enable=false;
set hive.join.inner.residual=false;

explain SELECT t0.c00 FROM t0
JOIN t1 ON (t0.c00 = t1.c10 AND t0.c01 BETWEEN t1.c11 AND t1.c12)
LEFT OUTER JOIN t2 ON ( t1.c13 = t2.c21)
LEFT OUTER JOIN
  (SELECT c30 FROM t3) s0 ON datediff (CURRENT_TIMESTAMP, t0.c03) = datediff (CURRENT_TIMESTAMP, s0.c30)
WHERE t1.c13 = 'test1';

SELECT t0.c00 FROM t0
JOIN t1 ON (t0.c00 = t1.c10 AND t0.c01 BETWEEN t1.c11 AND t1.c12)
LEFT OUTER JOIN t2 ON ( t1.c13 = t2.c21)
LEFT OUTER JOIN
  (SELECT c30 FROM t3) s0 ON datediff (CURRENT_TIMESTAMP, t0.c03) = datediff (CURRENT_TIMESTAMP, s0.c30)
WHERE t1.c13 = 'test1';