set hive.mapred.mode=nonstrict;
-- set of tests HIVE-9481

drop database if exists x314 cascade;
create database x314;
use x314;
create table source_n0(s1 int, s2 int);
create table target1(x int, y int, z int);
create table target2(x int, y int, z int);
create table target3(x int, y int, z int);

insert into source_n0(s2,s1) values(2,1);
-- expect source_n0 to contain 1 row (1,2)
select * from source_n0;
insert into target1(z,x) select * from source_n0;
-- expect target1 to contain 1 row (2,NULL,1)
select * from target1;

-- note that schema spec for target1 and target2 are different
from source_n0 insert into target1(x,y) select * insert into target2(x,z) select s2,s1;
--expect target1 to have 2rows (2,NULL,1), (1,2,NULL)
select * from target1 order by x,y,z;
-- expect target2 to have 1 row: (2,NULL,1)
select * from target2;


from source_n0 insert into target1(x,y,z) select null as x, * insert into target2(x,y,z) select null as x, source_n0.*;
-- expect target1 to have 3 rows: (2,NULL,1), (1,2,NULL), (NULL, 1,2)
select * from target1 order by x,y,z;
-- expect target2 to have 2 rows: (2,NULL,1), (NULL, 1,2)
select * from target2 order by x,y,z;

create table source2(s1 int, s2 int);
insert into target3 (x,z) select source_n0.s1,source2.s2 from source_n0 left outer join source2 on source_n0.s1=source2.s2;
--expect target3 to have 1 row (1,NULL,NULL)
select * from target3;


-- partitioned tables
CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source_n0 STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 4 BUCKETS STORED AS ORC;
INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23', i = 1)(userid,link) VALUES ('jsmith', 'mail.com');
-- expect 1 row: ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1'
select * from pageviews;


-- dynamic partitioning 


INSERT INTO TABLE pageviews PARTITION (datestamp='2014-09-23',i)(userid,i,link) VALUES ('jsmith', 7, '7mail.com');


INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 17, '17mail.com', '2014-09-23');
INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 19, '19mail.com', '2014-09-24');
-- here the 'datestamp' partition column is not provided and will be NULL-filled
INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link) VALUES ('jsmith', 23, '23mail.com');
-- expect 5 rows:
-- expect ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1'
-- expect ('jsmith', '7mail.com', NULL) in partition '2014-09-23'/'7'
-- expect ('jsmith', '17mail.com', NULL) in partition '2014-09-23'/'17'
-- expect ('jsmith', '19mail.com', NULL) in partition '2014-09-24'/'19'
-- expect ('jsmith', '23mail.com', NULL) in partition '__HIVE_DEFAULT_PARTITION__'/'23'
select * from pageviews order by link;


drop database if exists x314 cascade;