ql/src/test/queries/clientpositive/orc_ppd_date.q

--! qt:dataset:src1
--! qt:dataset:src

set hive.vectorized.execution.enabled=false;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET mapred.min.split.size=1000;
SET mapred.max.split.size=5000;

create table newtypesorc_n3(c char(10), v varchar(10), d decimal(5,3), da date) stored as orc tblproperties("orc.stripe.size"="16777216"); 

insert overwrite table newtypesorc_n3 select * from (select cast("apple" as char(10)), cast("bee" as varchar(10)), 0.22, cast("1970-02-20" as date) from src src1 union all select cast("hello" as char(10)), cast("world" as varchar(10)), 11.22, cast("1970-02-27" as date) from src src2) uniontbl;

-- date data types (EQUAL, NOT_EQUAL, LESS_THAN, LESS_THAN_EQUALS, IN, BETWEEN tests)
select sum(hash(*)) from newtypesorc_n3 where da='1970-02-20';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da='1970-02-20';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da= date '1970-02-20';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da=cast('1970-02-20' as date);

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da=cast('1970-02-20' as date);

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da=cast('1970-02-20' as varchar(20));

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da=cast('1970-02-20' as varchar(20));

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da!='1970-02-20';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da!='1970-02-20';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da<'1970-02-27';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da<'1970-02-27';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da<'1970-02-29';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da<'1970-02-29';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da<'1970-02-15';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da<'1970-02-15';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da<='1970-02-20';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da<='1970-02-20';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da<='1970-02-27';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da<='1970-02-27';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da in (cast('1970-02-21' as date), cast('1970-02-27' as date));

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da in (cast('1970-02-21' as date), cast('1970-02-27' as date));

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da in (cast('1970-02-20' as date), cast('1970-02-27' as date));

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da in (cast('1970-02-20' as date), cast('1970-02-27' as date));

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da in (cast('1970-02-21' as date), cast('1970-02-22' as date));

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da in (cast('1970-02-21' as date), cast('1970-02-22' as date));

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da between '1970-02-19' and '1970-02-22';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da between '1970-02-19' and '1970-02-22';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da between '1970-02-19' and '1970-02-28';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da between '1970-02-19' and '1970-02-28';

set hive.optimize.index.filter=false;
select sum(hash(*)) from newtypesorc_n3 where da between '1970-02-18' and '1970-02-19';

set hive.optimize.index.filter=true;
select sum(hash(*)) from newtypesorc_n3 where da between '1970-02-18' and '1970-02-19';

create table test_lrl(c date) stored as orc;
insert into test_lrl values ('1900-01-01');

explain select count(*) from test_lrl where c='1900-01-01';
set hive.optimize.index.filter=true;
select count(*) from test_lrl where c='1900-01-01';
set hive.optimize.index.filter=false;
select count(*) from test_lrl where c='1900-01-01';

explain select * from test_lrl where c=cast('1900-01-01' as date);
set hive.optimize.index.filter=true;
select * from test_lrl where c=cast('1900-01-01' as date);
set hive.optimize.index.filter=false;
select * from test_lrl where c=cast('1900-01-01' as date);

explain select count(*) from test_lrl where c LIKE '1900-01-01%';
set hive.optimize.index.filter=true;
select count(*) from test_lrl where c LIKE '1900-01-01%';
set hive.optimize.index.filter=false;
select count(*) from test_lrl where c LIKE '1900-01-01%';