testdata/workloads/functional-query/queries/QueryTest/date-partitioning.test

====
---- QUERY
# Create a table partitioned by DATE.
create table $DATABASE.dtbl (c date) partitioned by (p date);
---- RESULTS
'Table has been created.'
====
---- QUERY
# Partition value is an invalid DATE literal
alter table $DATABASE.dtbl add partition (p='1300-1-');
---- CATCH
AnalysisException: Invalid date literal: '1300-1-'
====
---- QUERY
alter table $DATABASE.dtbl add partition (p='1300-1-1');
---- RESULTS
'New partition has been added to the table.'
====
---- QUERY
# Date formatted differently referring to the same partition.
alter table $DATABASE.dtbl add partition (p='1300-01-1');
---- CATCH
AnalysisException: Partition spec already exists: (p=DATE '1300-01-01').
====
---- QUERY
# Date partition formatted differently in insert
insert into $DATABASE.dtbl partition (p='1300-1-01') values ('1300-1-1');
---- RESULTS
p=1300-01-01/: 1
====
---- QUERY
insert into $DATABASE.dtbl partition (p='1300-01-1') values ('1300-1-02');
---- RESULTS
p=1300-01-01/: 1
====
---- QUERY
insert into $DATABASE.dtbl partition (p=DATE '1300-1-1') values ('1300-1-03');
---- RESULTS
p=1300-01-01/: 1
====
---- QUERY
# Insert into a new partition
insert into $DATABASE.dtbl partition (p=DATE '1400-01-1') values ('1400-1-1');
---- RESULTS
p=1400-01-01/: 1
====
---- QUERY
insert into $DATABASE.dtbl partition (p='1400-1-01') values ('1400-1-2');
---- RESULTS
p=1400-01-01/: 1
====
---- QUERY
select p, c from $DATABASE.dtbl;
---- RESULTS
1300-01-01,1300-01-01
1300-01-01,1300-01-02
1300-01-01,1300-01-03
1400-01-01,1400-01-01
1400-01-01,1400-01-02
---- TYPES
DATE,DATE
====
---- QUERY
# Create a table partitioned by STRING and fill up with date-like values.
create table $DATABASE.stbl (c string) partitioned by (p string);
insert into $DATABASE.stbl partition (p='1400-1-1') values ('1400-1-3');
insert into $DATABASE.stbl partition (p='1400-1-01') values ('1400-01-4');
insert into $DATABASE.stbl partition (p='1400-01-01') values ('400-01-4');
insert into $DATABASE.stbl partition (p='1400-01-') values ('1400-01-5');
insert into $DATABASE.stbl partition (p='1500-01-1') values ('1500-01-1');
====
---- QUERY
# Use dynamic partition specification in insert.
# When the implicit cast fails, insert fails with error
insert into $DATABASE.dtbl partition(p) select * from $DATABASE.stbl where p='1400-01-01';
---- CATCH
UDF ERROR: String to Date parse failed. Invalid string val: '400-01-4'
====
---- QUERY
insert into $DATABASE.dtbl partition(p) select * from $DATABASE.stbl where p='1400-01-';
---- CATCH
UDF ERROR: String to Date parse failed. Invalid string val: '1400-01-'
====
---- QUERY
# Use dynamic partition specification in insert.
# Test that STRING is implicitly cast to DATE.
insert into $DATABASE.dtbl partition(p) select * from $DATABASE.stbl
where p in ('1400-1-1', '1400-1-01', '1500-01-1');
---- RESULTS
p=1400-01-01/: 2
p=1500-01-01/: 1
====
---- QUERY
select p, c from $DATABASE.dtbl;
---- RESULTS
1300-01-01,1300-01-01
1300-01-01,1300-01-02
1300-01-01,1300-01-03
1400-01-01,1400-01-01
1400-01-01,1400-01-02
1400-01-01,1400-01-03
1400-01-01,1400-01-04
1500-01-01,1500-01-01
---- TYPES
DATE,DATE
====
---- QUERY
# Test dropping partitions
alter table $DATABASE.dtbl drop partition(p < '1400-1-1');
---- RESULTS
'Dropped 1 partition(s).'
====
---- QUERY
select p, c from $DATABASE.dtbl;
---- RESULTS
1400-01-01,1400-01-01
1400-01-01,1400-01-02
1400-01-01,1400-01-03
1400-01-01,1400-01-04
1500-01-01,1500-01-01
---- TYPES
DATE,DATE
====