--! qt:dataset:part
set hive.explain.user=false;
set hive.fetch.task.conversion=none;
set hive.mapred.mode=nonstrict;
set hive.cli.print.header=true;
SET hive.exec.schema.evolution=true;
SET hive.vectorized.use.vectorized.input.format=false;
SET hive.vectorized.use.vector.serde.deserialize=false;
SET hive.vectorized.use.row.serde.deserialize=true;
SET hive.vectorized.execution.enabled=true;
set hive.metastore.disallow.incompatible.col.type.changes=false;
set hive.default.fileformat=textfile;
set hive.llap.io.enabled=false;

-- SORT_QUERY_RESULTS
--
-- FILE VARIATION: TEXTFILE, Non-Vectorized, MapWork, Partitioned --> all complex conversions
-- NOTE: the use of hive.vectorized.use.row.serde.deserialize above which enables doing
--  vectorized reading of TEXTFILE format files using the row SERDE methods.

--
------------------------------------------------------------------------------------------
--
-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for Various --> Various: STRUCT<BOOLEAN, TINYINT, SMALLINT, INT, LONG, FLOAT, DOUBLE, DECIMAL(38,18), CHAR, VARCHAR, TIMESTAMP, DATE, BINARY> --> STRUCT<STRING...
--
CREATE TABLE part_change_various_various_struct1_n4(insert_num int, s1 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>, b STRING) PARTITIONED BY(part INT);

CREATE TABLE complex_struct1_a_txt_n4(insert_num int, s1 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>, b STRING)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct1_a.txt' overwrite into table complex_struct1_a_txt_n4;

insert into table part_change_various_various_struct1_n4 partition(part=1) select * from complex_struct1_a_txt_n4;

select insert_num,part,s1,b from part_change_various_various_struct1_n4;

-- Table-Non-Cascade CHANGE COLUMNS ...
alter table part_change_various_various_struct1_n4 replace columns (insert_num int, s1 STRUCT<c1:STRING, c2:STRING, c3:STRING, c4:STRING, c5:STRING, c6:STRING, c7:STRING, c8:STRING, c9:STRING, c10:STRING, c11:STRING, c12:STRING, c13:STRING>, b STRING);

CREATE TABLE complex_struct1_b_txt_n4(insert_num int, s1 STRUCT<c1:STRING, c2:STRING, c3:STRING, c4:STRING, c5:STRING, c6:STRING, c7:STRING, c8:STRING, c9:STRING, c10:STRING, c11:STRING, c12:STRING, c13:STRING>, b STRING)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct1_b.txt' overwrite into table complex_struct1_b_txt_n4;

insert into table part_change_various_various_struct1_n4 partition(part=2) select * from complex_struct1_b_txt_n4;

CREATE TABLE complex_struct1_c_txt_n4(insert_num int, s1 STRUCT<c1:STRING, c2:STRING, c3:STRING, c4:STRING, c5:STRING, c6:STRING, c7:STRING, c8:STRING, c9:STRING, c10:STRING, c11:STRING, c12:STRING, c13:STRING>, b STRING)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct1_c.txt' overwrite into table complex_struct1_c_txt_n4;

insert into table part_change_various_various_struct1_n4 partition(part=1) select * from complex_struct1_c_txt_n4;

explain vectorization detail
select insert_num,part,s1,b from part_change_various_various_struct1_n4;

select insert_num,part,s1,b from part_change_various_various_struct1_n4;

drop table part_change_various_various_struct1_n4;


--
-- SUBSECTION: ALTER TABLE ADD COLUMNS for Various --> Various: STRUCT
--
CREATE TABLE part_add_various_various_struct2_n4(insert_num int, b STRING) PARTITIONED BY(part INT);

insert into table part_add_various_various_struct2_n4 partition(part=1)
    values(1, 'original'),
          (2, 'original');

select insert_num,part,b from part_add_various_various_struct2_n4;

-- Table-Non-Cascade ADD COLUMN ...
alter table part_add_various_various_struct2_n4 ADD columns (s2 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>);

CREATE TABLE complex_struct2_a_txt_n4(insert_num int, b STRING, s2 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct2_a.txt' overwrite into table complex_struct2_a_txt_n4;

insert into table part_add_various_various_struct2_n4 partition(part=1) select * from complex_struct2_a_txt_n4;

CREATE TABLE complex_struct2_b_txt_n4(insert_num int, b STRING, s2 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct2_b.txt' overwrite into table complex_struct2_b_txt_n4;

insert into table part_add_various_various_struct2_n4 partition(part=2) select * from complex_struct2_b_txt_n4;

select insert_num,part,b,s2 from part_add_various_various_struct2_n4;

-- Table-Non-Cascade CHANGE COLUMNS ...
alter table part_add_various_various_struct2_n4 REPLACE columns (insert_num int, b STRING, s2 STRUCT<c1:STRING, c2:STRING, c3:STRING, c4:STRING, c5:STRING, c6:STRING, c7:STRING, c8:STRING, c9:STRING, c10:STRING, c11:STRING, c12:STRING, c13:STRING>);

CREATE TABLE complex_struct2_c_txt_n4(insert_num int, b STRING, s2 STRUCT<c1:STRING, c2:STRING, c3:STRING, c4:STRING, c5:STRING, c6:STRING, c7:STRING, c8:STRING, c9:STRING, c10:STRING, c11:STRING, c12:STRING, c13:STRING>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct2_c.txt' overwrite into table complex_struct2_c_txt_n4;

insert into table part_add_various_various_struct2_n4 partition(part=2) select * from complex_struct2_c_txt_n4;

CREATE TABLE complex_struct2_d_txt_n4(insert_num int, b STRING, s2 STRUCT<c1:STRING, c2:STRING, c3:STRING, c4:STRING, c5:STRING, c6:STRING, c7:STRING, c8:STRING, c9:STRING, c10:STRING, c11:STRING, c12:STRING, c13:STRING>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct2_d.txt' overwrite into table complex_struct2_d_txt_n4;

insert into table part_add_various_various_struct2_n4 partition(part=1) select * from complex_struct2_d_txt_n4;

explain vectorization detail
select insert_num,part,b,s2 from part_add_various_various_struct2_n4;

select insert_num,part,b,s2 from part_add_various_various_struct2_n4;

drop table part_add_various_various_struct2_n4;


--
-- SUBSECTION: ALTER TABLE ADD COLUMNS for Various --> Various: ADD COLUMNS to STRUCT type as LAST column of 3 columns
--
CREATE TABLE part_add_to_various_various_struct4_n4(insert_num int, b STRING, s3 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT>) PARTITIONED BY(part INT);

CREATE TABLE complex_struct4_a_txt_n4(insert_num int, b STRING, s3 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct4_a.txt' overwrite into table complex_struct4_a_txt_n4;

insert into table part_add_to_various_various_struct4_n4 partition(part=1) select * from complex_struct4_a_txt_n4;

select insert_num,part,b,s3 from part_add_to_various_various_struct4_n4;

-- Table-Non-Cascade CHANGE COLUMNS ...
alter table part_add_to_various_various_struct4_n4 replace columns (insert_num int, b STRING, s3 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>);

CREATE TABLE complex_struct4_b_txt_n4(insert_num int, b STRING, s3 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct4_b.txt' overwrite into table complex_struct4_b_txt_n4;

insert into table part_add_to_various_various_struct4_n4 partition(part=2) select * from complex_struct4_b_txt_n4;

CREATE TABLE complex_struct4_c_txt_n4(insert_num int, b STRING, s3 STRUCT<c1:BOOLEAN, c2:TINYINT, c3:SMALLINT, c4:INT, c5:BIGINT, c6:FLOAT, c7:DOUBLE, c8:DECIMAL(38,18), c9:CHAR(25), c10:VARCHAR(25), c11:TIMESTAMP, c12:DATE, c13:BINARY>)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':' stored as textfile;
load data local inpath '../../data/files/schema_evolution/complex_struct4_c.txt' overwrite into table complex_struct4_c_txt_n4;

insert into table part_add_to_various_various_struct4_n4 partition(part=1) select * from complex_struct4_c_txt_n4;

explain vectorization detail
select insert_num,part,b,s3 from part_add_to_various_various_struct4_n4;

select insert_num,part,b,s3 from part_add_to_various_various_struct4_n4;

drop table part_add_to_various_various_struct4_n4;