http://git-wip-us.apache.org/repos/asf/hive/blob/cb534ab0/ql/src/test/queries/clientpositive/schema_evol_orc_acid_mapwork_table.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/schema_evol_orc_acid_mapwork_table.q b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_mapwork_table.q deleted file mode 100644 index cbaf30c..0000000 --- a/ql/src/test/queries/clientpositive/schema_evol_orc_acid_mapwork_table.q +++ /dev/null @@ -1,857 +0,0 @@ -set hive.cli.print.header=true; -set hive.support.concurrency=true; -set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; -set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; -SET hive.exec.schema.evolution=false; -SET hive.vectorized.use.vectorized.input.format=true; -SET hive.vectorized.use.vector.serde.deserialize=false; -SET hive.vectorized.use.row.serde.deserialize=false; -set hive.fetch.task.conversion=none; -SET hive.vectorized.execution.enabled=false; -set hive.exec.dynamic.partition.mode=nonstrict; -set hive.metastore.disallow.incompatible.col.type.changes=true; -set hive.default.fileformat=orc; -set hive.llap.io.enabled=false; - --- SORT_QUERY_RESULTS --- --- FILE VARIATION: ORC, ACID Non-Vectorized, MapWork, Table --- *IMPORTANT NOTE* We set hive.exec.schema.evolution=false above since schema evolution is always used for ACID. --- Also, we don't do EXPLAINs on ACID files because the transaction id causes Q file statistics differences... --- ------------------------------------------------------------------------------------------- --- SECTION: ALTER TABLE ADD COLUMNS --- --- --- SUBSECTION: ALTER TABLE ADD COLUMNS: INT PERMUTE SELECT --- --- -CREATE TABLE table_add_int_permute_select(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_add_int_permute_select - values (1, 1, 'original'), - (2, 2, 'original'), - (3, 3, 'original'), - (4, 4, 'original'); - --- Table-Non-Cascade ADD COLUMNS ... -alter table table_add_int_permute_select add columns(c int); - -insert into table table_add_int_permute_select - values (5, 1, 'new', 10), - (6, 2, 'new', 20), - (7, 3, 'new', 30), - (8, 4, 'new', 40); - -insert into table table_add_int_permute_select - values (9, 5, 'new', 100), - (10, 6, 'new', 200); - --- SELECT permutation columns to make sure NULL defaulting works right -select insert_num,a,b from table_add_int_permute_select; -select insert_num,a,b,c from table_add_int_permute_select; -select insert_num,c from table_add_int_permute_select; - -drop table table_add_int_permute_select; - - --- SUBSECTION: ALTER TABLE ADD COLUMNS: INT, STRING, PERMUTE SELECT --- --- -CREATE TABLE table_add_int_string_permute_select(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_add_int_string_permute_select - values (1, 1, 'original'), - (2, 2, 'original'), - (3, 3, 'original'), - (4, 4, 'original'); - --- Table-Non-Cascade ADD COLUMNS ... -alter table table_add_int_string_permute_select add columns(c int, d string); - -insert into table table_add_int_string_permute_select - values (5, 1, 'new', 10, 'ten'), - (6, 2, 'new', 20, 'twenty'), - (7, 3, 'new', 30, 'thirty'), - (8, 4, 'new', 40, 'forty'); - -insert into table table_add_int_string_permute_select - values (9, 5, 'new', 100, 'hundred'), - (10, 6, 'new', 200, 'two hundred'); - --- SELECT permutation columns to make sure NULL defaulting works right -select insert_num,a,b from table_add_int_string_permute_select; -select insert_num,a,b,c from table_add_int_string_permute_select; -select insert_num,a,b,c,d from table_add_int_string_permute_select; -select insert_num,a,c,d from table_add_int_string_permute_select; -select insert_num,a,d from table_add_int_string_permute_select; -select insert_num,c from table_add_int_string_permute_select; -select insert_num,d from table_add_int_string_permute_select; - -drop table table_add_int_string_permute_select; - - - ------------------------------------------------------------------------------------------- --- SECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> DOUBLE --- --- --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> DOUBLE: (STRING, CHAR, VARCHAR) --- -CREATE TABLE table_change_string_group_double(insert_num int, c1 STRING, c2 CHAR(50), c3 VARCHAR(50), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_string_group_double - values (1, '753.7028', '753.7028', '753.7028', 'original'), - (2, '-3651.672121', '-3651.672121', '-3651.672121', 'original'), - (3, '-29.0764', '-29.0764', '-29.0764', 'original'), - (4, '-10.3', '-10.3', '-10.3', 'original'); - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_string_group_double replace columns (insert_num int, c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, b STRING); - -insert into table table_change_string_group_double - values (5, 30.774, 30.774, 30.774, 'new'), - (6, 20.31, 20.31, 20.31, 'new'), - (7, 46114.284799488, 46114.284799488, 46114.284799488, 'new'), - (8, -66475.561431, -66475.561431, -66475.561431, 'new'); - -insert into table table_change_string_group_double - values (9, 17808.963785, 17808.963785, 17808.963785, 'new'), - (10, 9250340.75 , 9250340.75 , 9250340.75 , 'new'); - -select insert_num,c1,c2,c3,b from table_change_string_group_double; - -drop table table_change_string_group_double; - ------------------------------------------------------------------------------------------- --- SECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP --- --- --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP: TIMESTAMP, (STRING, CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) --- -CREATE TABLE table_change_date_group_string_group_timestamp(insert_num int, c1 TIMESTAMP, c2 TIMESTAMP, c3 TIMESTAMP, c4 TIMESTAMP, c5 TIMESTAMP, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_date_group_string_group_timestamp - values (1, '2000-12-18 08:42:30.000595596', '2000-12-18 08:42:30.000595596', '2000-12-18 08:42:30.000595596', '2000-12-18 08:42:30.000595596', '2000-12-18 08:42:30.000595596', 'original'), - (2, '2024-11-11 16:42:41.101', '2024-11-11 16:42:41.101', '2024-11-11 16:42:41.101', '2024-11-11 16:42:41.101', '2024-11-11 16:42:41.101', 'original'), - (3, '2021-09-24 03:18:32.413655165', '2021-09-24 03:18:32.413655165', '2021-09-24 03:18:32.413655165', '2021-09-24 03:18:32.413655165', '2021-09-24 03:18:32.413655165', 'original'); - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_date_group_string_group_timestamp replace columns(insert_num int, c1 STRING, c2 CHAR(50), c3 CHAR(15), c4 VARCHAR(50), c5 VARCHAR(15), b STRING); - -insert into table table_change_date_group_string_group_timestamp - values (4, '0004-09-22 18:26:29.519542222', '0004-09-22 18:26:29.519542222', '0004-09-22 18:26:29.519542222', '0004-09-22 18:26:29.519542222', '0004-09-22 18:26:29.519542222', 'new'), - (5, '1815-05-06 00:12:37.543584705', '1815-05-06 00:12:37.543584705', '1815-05-06 00:12:37.543584705', '1815-05-06 00:12:37.543584705', '1815-05-06 00:12:37.543584705', 'new'), - (6, '2007-02-09 05:17:29.368756876', '2007-02-09 05:17:29.368756876', '2007-02-09 05:17:29.368756876', '2007-02-09 05:17:29.368756876', '2007-02-09 05:17:29.368756876', 'new'), - (7, '2002-05-10 05:29:48.990818073', '2002-05-10 05:29:48.990818073', '2002-05-10 05:29:48.990818073', '2002-05-10 05:29:48.990818073', '2002-05-10 05:29:48.990818073', 'new'); -insert into table table_change_date_group_string_group_timestamp - values (8, '6229-06-28 02:54:28.970117179', '6229-06-28 02:54:28.970117179', '6229-06-28 02:54:28.970117179', '6229-06-28 02:54:28.970117179', '6229-06-28 02:54:28.970117179', 'new'), - (9, '5966-07-09 03:30:50.597', '5966-07-09 03:30:50.597', '5966-07-09 03:30:50.597', '5966-07-09 03:30:50.597', '5966-07-09 03:30:50.597', 'new'); - -select insert_num,c1,c2,c3,c4,c5,b from table_change_date_group_string_group_timestamp; - -drop table table_change_date_group_string_group_timestamp; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP: DATE, (STRING, CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) --- -CREATE TABLE table_change_date_group_string_group_date(insert_num int, c1 DATE, c2 DATE, c3 DATE, c4 DATE, c5 DATE, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_date_group_string_group_date - values (1, '2000-12-18', '2000-12-18', '2000-12-18', '2000-12-18', '2000-12-18', 'original'), - (2, '2024-11-11', '2024-11-11', '2024-11-11', '2024-11-11', '2024-11-11', 'original'), - (3, '2021-09-24', '2021-09-24', '2021-09-24', '2021-09-24', '2021-09-24', 'original'); - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_date_group_string_group_date replace columns(insert_num int, c1 STRING, c2 CHAR(50), c3 CHAR(15), c4 VARCHAR(50), c5 VARCHAR(15), b STRING); - -insert into table table_change_date_group_string_group_date - values (4, '0004-09-22', '0004-09-22', '0004-09-22', '0004-09-22', '0004-09-22', 'new'), - (5, '1815-05-06', '1815-05-06', '1815-05-06', '1815-05-06', '1815-05-06', 'new'), - (6, '2007-02-09', '2007-02-09', '2007-02-09', '2007-02-09', '2007-02-09', 'new'), - (7, '2002-05-10', '2002-05-10', '2002-05-10', '2002-05-10', '2002-05-10', 'new'), - (8, '6229-06-28', '6229-06-28', '6229-06-28', '6229-06-28', '6229-06-28', 'new'), - (9, '5966-07-09', '5966-07-09', '5966-07-09', '5966-07-09', '5966-07-09', 'new'); - -select insert_num,c1,c2,c3,c4,c5,b from table_change_date_group_string_group_date; - -drop table table_change_date_group_string_group_date; - - - ------------------------------------------------------------------------------------------- --- SECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP --- --- --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (TINYINT, SMALLINT, INT, BIGINT), STRING --- -CREATE TABLE table_change_numeric_group_string_group_multi_ints_string(insert_num int, c1 tinyint, c2 smallint, c3 int, c4 bigint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_multi_ints_string - values (1, 45, 1000, 483777, -23866739993, 'original'), - (2, -2, -6737, 56, 28899333, 'original'), - (3, -255, 4957, 832222222, 9000000000, 'original'), - (4, 0, 20435, 847492223, -999999999999, 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_string; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_multi_ints_string replace columns (insert_num int, c1 STRING, c2 STRING, c3 STRING, c4 STRING, b STRING) ; - -insert into table table_change_numeric_group_string_group_multi_ints_string - values (5, '2000', '72909', '3244222', '-93222', 'new'), - (6, '1', '200', '2323322', '5430907', 'new'), - (7, '256', '32768', '31889', '470614135', 'new'), - (8, '5555', '40000', '-719017797', '810662019', 'new'), - (9, '100', '5000', '5443', '0', 'new'), - (10, '17', '90000', '754072151', '3289094', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_string; - -drop table table_change_numeric_group_string_group_multi_ints_string; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (TINYINT, SMALLINT, INT, BIGINT), CHAR --- -CREATE TABLE table_change_numeric_group_string_group_multi_ints_char(insert_num int, c1 tinyint, c2 smallint, c3 int, c4 bigint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_multi_ints_char - values (1, 45, 1000, 483777, -23866739993, 'original'), - (2, -2, -6737, 56, 28899333, 'original'), - (3, -255, 4957, 832222222, 9000000000, 'original'), - (4, 0, 20435, 847492223, -999999999999, 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_char; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_multi_ints_char replace columns (insert_num int, c1 CHAR(50), c2 CHAR(50), c3 CHAR(50), c4 CHAR(50), b STRING) ; - -insert into table table_change_numeric_group_string_group_multi_ints_char - values (5, '2000', '72909', '3244222', '-93222', 'new'), - (6, '1', '200', '2323322', '5430907', 'new'), - (7, '256', '32768', '31889', '470614135', 'new'), - (8, '5555', '40000', '-719017797', '810662019', 'new'), - (9, '100', '5000', '5443', '0', 'new'), - (10, '17', '90000', '754072151', '3289094', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_char; - -drop table table_change_numeric_group_string_group_multi_ints_char; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (TINYINT, SMALLINT, INT, BIGINT), CHAR truncation --- -CREATE TABLE table_change_numeric_group_string_group_multi_ints_char_trunc(insert_num int, c1 tinyint, c2 smallint, c3 int, c4 bigint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_multi_ints_char_trunc - values (1, 45, 1000, 483777, -23866739993, 'original'), - (2, -2, -6737, 56, 28899333, 'original'), - (3, -255, 4957, 832222222, 9000000000, 'original'), - (4, 0, 20435, 847492223, -999999999999, 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_char_trunc; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_multi_ints_char_trunc replace columns (insert_num int, c1 CHAR(5), c2 CHAR(5), c3 CHAR(5), c4 CHAR(5), b STRING) ; - -insert into table table_change_numeric_group_string_group_multi_ints_char_trunc - values (5, '2000', '72909', '3244222', '-93222', 'new'), - (6, '1', '200', '2323322', '5430907', 'new'), - (7, '256', '32768', '31889', '470614135', 'new'), - (8, '5555', '40000', '-719017797', '810662019', 'new'), - (9, '100', '5000', '5443', '0', 'new'), - (10, '17', '90000', '754072151', '3289094', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_char_trunc; - -drop table table_change_numeric_group_string_group_multi_ints_char_trunc; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (TINYINT, SMALLINT, INT, BIGINT), VARCHAR --- -CREATE TABLE table_change_numeric_group_string_group_multi_ints_varchar(insert_num int, c1 tinyint, c2 smallint, c3 int, c4 bigint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_multi_ints_varchar - values (1, 45, 1000, 483777, -23866739993, 'original'), - (2, -2, -6737, 56, 28899333, 'original'), - (3, -255, 4957, 832222222, 9000000000, 'original'), - (4, 0, 20435, 847492223, -999999999999, 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_varchar; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_multi_ints_varchar replace columns (insert_num int, c1 VARCHAR(50), c2 VARCHAR(50), c3 VARCHAR(50), c4 VARCHAR(50), b STRING) ; - -insert into table table_change_numeric_group_string_group_multi_ints_varchar - values (5, '2000', '72909', '3244222', '-93222', 'new'), - (6, '1', '200', '2323322', '5430907', 'new'), - (7, '256', '32768', '31889', '470614135', 'new'), - (8, '5555', '40000', '-719017797', '810662019', 'new'), - (9, '100', '5000', '5443', '0', 'new'), - (10, '17', '90000', '754072151', '3289094', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_varchar; - -drop table table_change_numeric_group_string_group_multi_ints_varchar; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (TINYINT, SMALLINT, INT, BIGINT), VARCHAR truncation --- -CREATE TABLE table_change_numeric_group_string_group_multi_ints_varchar_trunc(insert_num int, c1 tinyint, c2 smallint, c3 int, c4 bigint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_multi_ints_varchar_trunc - values (1, 45, 1000, 483777, -23866739993, 'original'), - (2, -2, -6737, 56, 28899333, 'original'), - (3, -255, 4957, 832222222, 9000000000, 'original'), - (4, 0, 20435, 847492223, -999999999999, 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_varchar_trunc; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_multi_ints_varchar_trunc replace columns (insert_num int, c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5), b STRING) ; - -insert into table table_change_numeric_group_string_group_multi_ints_varchar_trunc - values (5, '2000', '72909', '3244222', '-93222', 'new'), - (6, '1', '200', '2323322', '5430907', 'new'), - (7, '256', '32768', '31889', '470614135', 'new'), - (8, '5555', '40000', '-719017797', '810662019', 'new'), - (9, '100', '5000', '5443', '0', 'new'), - (10, '17', '90000', '754072151', '3289094', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_numeric_group_string_group_multi_ints_varchar_trunc; - -drop table table_change_numeric_group_string_group_multi_ints_varchar_trunc; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (FLOAT, DOUBLE, DECIMAL), STRING --- -CREATE TABLE table_change_numeric_group_string_group_floating_string(insert_num int, c1 decimal(38,18), c2 float, c3 double, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_floating_string - values (1, -23866739993, 753.7028, -3651.672121, 'original'), - (2, -10.3, -2, -29.0764, 'original'), - (3, - 832222222, 255, 4957,'original'), - (4, 847492223, 0, 20435, 'original'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_string; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_floating_string replace columns (insert_num int, c1 STRING, c2 STRING, c3 STRING, b STRING) ; - -insert into table table_change_numeric_group_string_group_floating_string - values (5, '30.774', '20.31', '46114.284799488', 'new'), - (6, '-66475.561431', '52927714', '7203778961', 'new'), - (7, '256', '32768', '31889', 'new'), - (8, '5555', '40000', '-719017797', 'new'), - (9, '100', '5000', '5443', 'new'), - (10, '17', '90000', '754072151', 'new'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_string; - -drop table table_change_numeric_group_string_group_floating_string; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (DECIMAL, FLOAT, DOUBLE), CHAR --- -CREATE TABLE table_change_numeric_group_string_group_floating_char(insert_num int, c1 decimal(38,18), c2 float, c3 double, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_floating_char - values (1, -23866739993, 753.7028, -3651.672121, 'original'), - (2, -10.3, -2, -29.0764, 'original'), - (3, 9000000000, -255, 4957,'original'), - (4, -999999999999, 0, 20435, 'original'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_char; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_floating_char replace columns (insert_num int, c1 CHAR(50), c2 CHAR(50), c3 CHAR(50), b STRING) ; - -insert into table table_change_numeric_group_string_group_floating_char - values (5, '30.774', '20.31', '46114.284799488', 'new'), - (6, '-66475.561431', '52927714', '7203778961', 'new'), - (7, '256', '32768', '31889', 'new'), - (8, '5555', '40000', '-719017797', 'new'), - (9, '100', '5000', '5443', 'new'), - (10, '17', '90000', '754072151', 'new'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_char; - -drop table table_change_numeric_group_string_group_floating_char; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (DECIMAL, FLOAT, DOUBLE), CHAR truncation - -CREATE TABLE table_change_numeric_group_string_group_floating_char_trunc(insert_num int, c1 decimal(38,18), c2 float, c3 double, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_floating_char_trunc - values (1, -23866739993, 753.7028, -3651.672121, 'original'), - (2, -10.3, -2, -29.0764, 'original'), - (3, 832222222, -255, 4957, 'original'), - (4, 847492223, 0, 20435, 'original'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_char_trunc; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_floating_char_trunc replace columns (insert_num int, c1 CHAR(7), c2 CHAR(7), c3 CHAR(7), b STRING) ; - -insert into table table_change_numeric_group_string_group_floating_char_trunc - values (5, '30.774', '20.31', '46114.284799488', 'new'), - (6, '-66475.561431', '52927714', '7203778961', 'new'), - (7, '256', '32768', '31889', 'new'), - (8, '5555', '40000', '-719017797', 'new'), - (9, '100', '5000', '5443', 'new'), - (10, '17', '90000', '754072151', 'new'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_char_trunc; - -drop table table_change_numeric_group_string_group_floating_char_trunc; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (DECIMAL, FLOAT, DOUBLE), VARCHAR --- -CREATE TABLE table_change_numeric_group_string_group_floating_varchar(insert_num int, c1 float, c2 double, c3 decimal(38,18), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_floating_varchar - values (1, -23866739993, 753.7028, -3651.672121, 'original'), - (2, -10.3, -2, -29.0764, 'original'), - (3, 9000000000, -255, 4957, 'original'), - (4, -999999999999, 0, 20435, 'original'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_varchar; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_floating_varchar replace columns (insert_num int, c1 VARCHAR(50), c2 VARCHAR(50), c3 VARCHAR(50), b STRING) ; - -insert into table table_change_numeric_group_string_group_floating_varchar - values (5, '30.774', '20.31', '46114.284799488', 'new'), - (6, '-66475.561431', '52927714', '7203778961', 'new'), - (7, '256', '32768', '31889', 'new'), - (8, '5555', '40000', '-719017797', 'new'), - (9, '100', '5000', '5443', 'new'), - (10, '17', '90000', '754072151', 'new'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_varchar; - -drop table table_change_numeric_group_string_group_floating_varchar; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: (DECIMAL, FLOAT, DOUBLE), VARCHAR truncation --- -CREATE TABLE table_change_numeric_group_string_group_floating_varchar_trunc(insert_num int, c1 decimal(38,18), c2 float, c3 double, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_numeric_group_string_group_floating_varchar_trunc - values (1, -23866739993, 753.7028, -3651.672121, 'original'), - (2, -10.3, -2, -29.0764, 'original'), - (3, 9000000000, -255, 4957, 'original'), - (4, -999999999999, 0, 20435, 'original'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_varchar_trunc; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_numeric_group_string_group_floating_varchar_trunc replace columns (insert_num int, c1 VARCHAR(7), c2 VARCHAR(7), c3 VARCHAR(7), b STRING) ; - -insert into table table_change_numeric_group_string_group_floating_varchar_trunc - values (5, '30.774', '20.31', '46114.284799488', 'new'), - (6, '-66475.561431', '52927714', '7203778961', 'new'), - (7, '256', '32768', '31889', 'new'), - (8, '5555', '40000', '-719017797', 'new'), - (9, '100', '5000', '5443', 'new'), - (10, '17', '90000', '754072151', 'new'); - -select insert_num,c1,c2,c3,b from table_change_numeric_group_string_group_floating_varchar_trunc; - -drop table table_change_numeric_group_string_group_floating_varchar_trunc; - - ------------------------------------------------------------------------------------------- --- SECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP --- --- --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP: STRING, (CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) --- -CREATE TABLE table_change_string_group_string_group_string(insert_num int, c1 string, c2 string, c3 string, c4 string, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_string_group_string_group_string - values (1, 'escapist', 'escapist', 'escapist', 'escapist', 'original'), - (2, 'heartbeat', 'heartbeat', 'heartbeat', 'heartbeat', 'original'), - (3, 'dynamic reptile', 'dynamic reptile', 'dynamic reptile', 'dynamic reptile', 'original'), - (4, 'blank pads ', 'blank pads ', 'blank pads ', 'blank pads ', 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_string_group_string_group_string; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_string_group_string_group_string replace columns (insert_num int, c1 CHAR(50), c2 CHAR(9), c3 VARCHAR(50), c4 CHAR(9), b STRING) ; - -insert into table table_change_string_group_string_group_string - values (5, 'junkyard', 'junkyard', 'junkyard', 'junkyard', 'new'), - (6, ' baffling ', ' baffling ', ' baffling ', ' baffling ', 'new'), - (7, ' featherweight ', ' featherweight ',' featherweight ',' featherweight ', 'new'), - (8, ' against', ' against', ' against', ' against', 'new'), - (9, 'hangar paralysed companion ', 'hangar paralysed companion ', 'hangar paralysed companion ', 'hangar paralysed companion ', 'new'), - (10, 'bottom ', 'bottom ', 'bottom ', 'bottom ', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_string_group_string_group_string; - -drop table table_change_string_group_string_group_string; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP: CHAR, (VARCHAR, VARCHAR trunc, STRING) --- -CREATE TABLE table_change_string_group_string_group_char(insert_num int, c1 CHAR(50), c2 CHAR(50), c3 CHAR(50), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_string_group_string_group_char - values (1, 'escapist', 'escapist', 'escapist', 'original'), - (2, 'heartbeat', 'heartbeat', 'heartbeat', 'original'), - (3, 'dynamic reptile', 'dynamic reptile', 'dynamic reptile', 'original'), - (4, 'blank pads ', 'blank pads ', 'blank pads ', 'original'); - -select insert_num,c1,c2,c3,b from table_change_string_group_string_group_char; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_string_group_string_group_char replace columns (insert_num int, c1 VARCHAR(50), c2 VARCHAR(9), c3 STRING, b STRING) ; - -insert into table table_change_string_group_string_group_char - values (5, 'junkyard', 'junkyard', 'junkyard', 'new'), - (6, ' baffling ', ' baffling ', ' baffling ', 'new'), - (7, ' featherweight ', ' featherweight ',' featherweight ', 'new'), - (8, ' against', ' against', ' against', 'new'), - (9, 'hangar paralysed companion ', 'hangar paralysed companion ', 'hangar paralysed companion ', 'new'), - (10, 'bottom ', 'bottom ', 'bottom ', 'new'); - -select insert_num,c1,c2,c3,b from table_change_string_group_string_group_char; - -drop table table_change_string_group_string_group_char; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP: VARCHAR, (CHAR, CHAR trunc, STRING) --- -CREATE TABLE table_change_string_group_string_group_varchar(insert_num int, c1 VARCHAR(50), c2 VARCHAR(50), c3 VARCHAR(50), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_string_group_string_group_varchar - values (1, 'escapist', 'escapist', 'escapist', 'original'), - (2, 'heartbeat', 'heartbeat', 'heartbeat', 'original'), - (3, 'dynamic reptile', 'dynamic reptile', 'dynamic reptile', 'original'), - (4, 'blank pads ', 'blank pads ', 'blank pads ', 'original'); - -select insert_num,c1,c2,c3,b from table_change_string_group_string_group_varchar; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_string_group_string_group_varchar replace columns (insert_num int, c1 CHAR(50), c2 CHAR(9), c3 STRING, b STRING) ; - -insert into table table_change_string_group_string_group_varchar - values (5, 'junkyard', 'junkyard', 'junkyard', 'new'), - (6, ' baffling ', ' baffling ', ' baffling ', 'new'), - (7, ' featherweight ', ' featherweight ',' featherweight ', 'new'), - (8, ' against', ' against', ' against', 'new'), - (9, 'hangar paralysed companion ', 'hangar paralysed companion ', 'hangar paralysed companion ', 'new'), - (10, 'bottom ', 'bottom ', 'bottom ', 'new'); - -select insert_num,c1,c2,c3,b from table_change_string_group_string_group_varchar; - -drop table table_change_string_group_string_group_varchar; - - - ------------------------------------------------------------------------------------------- --- SECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP --- --- --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: TINYINT, (SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE) --- -CREATE TABLE table_change_lower_to_higher_numeric_group_tinyint(insert_num int, c1 tinyint, c2 tinyint, c3 tinyint, c4 tinyint, c5 tinyint, c6 tinyint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_lower_to_higher_numeric_group_tinyint - values (1, 45, 45, 45, 45, 45, 45, 'original'), - (2, -2, -2, -2, -2, -2, -2, 'original'), - (3, -255, -255, -255, -255, -255, -255, 'original'), - (4, 100, 100, 100, 100, 100, 100, 'original'); - -select insert_num,c1,c2,c3,c4,c5,c6,b from table_change_lower_to_higher_numeric_group_tinyint; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_lower_to_higher_numeric_group_tinyint replace columns (insert_num int, c1 SMALLINT, c2 INT, c3 BIGINT, c4 decimal(38,18), c5 FLOAT, c6 DOUBLE, b STRING); - -insert into table table_change_lower_to_higher_numeric_group_tinyint - values (5, '774', '2031', '200', '12', '99', '0', 'new'), - (6, '561431', '52927714', '7203778961', '8', '7', '6', 'new'), - (7, '256', '32768', '31889', '300', '444', '506', 'new'), - (8, '5555', '40000', '-719017797', '45', '55', '65', 'new'), - (9, '100', '5000', '5443', '22', '2', '-2', 'new'), - (10, '17', '90000', '754072151', '95', '20', '18', 'new'); - -select insert_num,c1,c2,c3,c4,c5,c6,b from table_change_lower_to_higher_numeric_group_tinyint; - -drop table table_change_lower_to_higher_numeric_group_tinyint; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: SMALLINT, (INT, BIGINT, DECIMAL, FLOAT, DOUBLE) --- -CREATE TABLE table_change_lower_to_higher_numeric_group_smallint(insert_num int, c1 smallint, c2 smallint, c3 smallint, c4 smallint, c5 smallint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_lower_to_higher_numeric_group_smallint - values (1, 2031, 2031, 2031, 2031, 2031, 'original'), - (2, -2, -2, -2, -2, -2, 'original'), - (3, -5000, -5000, -5000, -5000, -5000, 'original'), - (4, 100, 100, 100, 100, 100, 'original'); - -select insert_num,c1,c2,c3,c4,c5,b from table_change_lower_to_higher_numeric_group_smallint; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_lower_to_higher_numeric_group_smallint replace columns (insert_num int, c1 INT, c2 BIGINT, c3 decimal(38,18), c4 FLOAT, c5 DOUBLE, b STRING) ; - -insert into table table_change_lower_to_higher_numeric_group_smallint - values (5, '774', '2031', '200', '12', '99', 'new'), - (6, '561431', '52927714', '7203778961', '8', '7', 'new'), - (7, '256', '32768', '31889', '300', '444', 'new'), - (8, '5555', '40000', '-719017797', '45', '55', 'new'), - (9, '100', '5000', '5443', '22', '2', 'new'), - (10, '17', '90000', '754072151', '95', '20', 'new'); - -select insert_num,c1,c2,c3,c4,c5,b from table_change_lower_to_higher_numeric_group_smallint; - -drop table table_change_lower_to_higher_numeric_group_smallint; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: INT, (BIGINT, DECIMAL, FLOAT, DOUBLE) --- -CREATE TABLE table_change_lower_to_higher_numeric_group_int(insert_num int, c1 int, c2 int, c3 int, c4 int, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_lower_to_higher_numeric_group_int - values (1, 2031, 2031, 2031, 2031, 'original'), - (2, -2, -2, -2, -2, 'original'), - (3, -5000, -5000, -5000, -5000, 'original'), - (4, 52927714, 52927714, 52927714, 52927714, 'original'); - -select insert_num,c1,c2,c3,c4,b from table_change_lower_to_higher_numeric_group_int; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_lower_to_higher_numeric_group_int replace columns (insert_num int, c1 BIGINT, c2 decimal(38,18), c3 FLOAT, c4 DOUBLE, b STRING) ; - -insert into table table_change_lower_to_higher_numeric_group_int - values (5, '774', '2031', '200', '12', 'new'), - (6, '561431', '52927714', '7203778961', '8', 'new'), - (7, '256', '32768', '31889', '300', 'new'), - (8, '5555', '40000', '-719017797', '45', 'new'), - (9, '100', '5000', '5443', '22', 'new'), - (10, '17', '90000', '754072151', '95', 'new'); - -select insert_num,c1,c2,c3,c4,b from table_change_lower_to_higher_numeric_group_int; - -drop table table_change_lower_to_higher_numeric_group_int; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: BIGINT, (DECIMAL, FLOAT, DOUBLE) --- -CREATE TABLE table_change_lower_to_higher_numeric_group_bigint(insert_num int, c1 bigint, c2 bigint, c3 bigint, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_lower_to_higher_numeric_group_bigint - values (1, 7203778961, 7203778961, 7203778961, 'original'), - (2, -2, -2, -2, 'original'), - (3, -5000, -5000, -5000, 'original'), - (4, 52927714, 52927714, 52927714, 'original'); - -select insert_num,c1,c2,c3,b from table_change_lower_to_higher_numeric_group_bigint; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_lower_to_higher_numeric_group_bigint replace columns (insert_num int, c1 decimal(38,18), c2 FLOAT, c3 DOUBLE, b STRING) ; - -insert into table table_change_lower_to_higher_numeric_group_bigint - values (5, '774', '2031', '200', 'new'), - (6, '561431', '52927714', '7203778961', 'new'), - (7, '256', '32768', '31889', 'new'), - (8, '5555', '40000', '-719017797', 'new'), - (9, '100', '5000', '5443', 'new'), - (10, '17', '90000', '754072151', 'new'); - -select insert_num,c1,c2,c3,b from table_change_lower_to_higher_numeric_group_bigint; - -drop table table_change_lower_to_higher_numeric_group_bigint; - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: DECIMAL, (FLOAT, DOUBLE) --- -CREATE TABLE table_change_lower_to_higher_numeric_group_decimal(insert_num int, c1 decimal(38,18), c2 decimal(38,18), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_lower_to_higher_numeric_group_decimal - values (1, -29.0764, -29.0764, 'original'), - (2, 753.7028, 753.7028, 'original'), - (3, -5000, -5000, 'original'), - (4, 52927714, 52927714, 'original'); - -select insert_num,c1,c2,b from table_change_lower_to_higher_numeric_group_decimal; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_lower_to_higher_numeric_group_decimal replace columns (insert_num int, c1 float, c2 double, b STRING) ; - -insert into table table_change_lower_to_higher_numeric_group_decimal - values (5, '7.74', '22.3', 'new'), - (6, '56.1431', '90.9', 'new'), - (7, '2.56', '25.6', 'new'), - (8, '555.5', '55.55', 'new'), - (9, '10.0', '0.100', 'new'), - (10, '1.7', '17.8888', 'new'); - -select insert_num,c1,c2,b from table_change_lower_to_higher_numeric_group_decimal; - -drop table table_change_lower_to_higher_numeric_group_decimal; - - - --- --- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: FLOAT, (DOUBLE) --- -CREATE TABLE table_change_lower_to_higher_numeric_group_float(insert_num int, c1 float, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table_change_lower_to_higher_numeric_group_float - values (1, -29.0764, 'original'), - (2, 753.7028, 'original'), - (3, -5000, 'original'), - (4, 52927714, 'original'); - -select insert_num,c1,b from table_change_lower_to_higher_numeric_group_float; - --- Table-Non-Cascade CHANGE COLUMNS ... -alter table table_change_lower_to_higher_numeric_group_float replace columns (insert_num int, c1 DOUBLE, b STRING) ; - -insert into table table_change_lower_to_higher_numeric_group_float - values (5, '774', 'new'), - (6, '561431', 'new'), - (7, '256', 'new'), - (8, '5555', 'new'), - (9, '100', 'new'), - (10, '17', 'new'); - -select insert_num,c1,b from table_change_lower_to_higher_numeric_group_float; - -drop table table_change_lower_to_higher_numeric_group_float; - - - --- --- --- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... UPDATE New Columns ---- -CREATE TABLE table5(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table5 - values (1, 1, 'original'), - (2, 2, 'original'), - (3, 3, 'original'), - (4, 4, 'original'); - --- Table-Non-Cascade ADD COLUMNS ... -alter table table5 add columns(c int, d string); - -insert into table table5 - values (5, 1, 'new', 10, 'ten'), - (6, 2, 'new', 20, 'twenty'), - (7, 3, 'new', 30, 'thirty'), - (8, 4, 'new', 40, 'forty'); - -insert into table table5 - values (9, 5, 'new', 100, 'hundred'), - (10, 6, 'new', 200, 'two hundred'); - -select a,b,c,d from table5; - --- UPDATE New Columns -update table5 set c=99; - -select a,b,c,d from table5; - - --- --- --- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... DELETE where old column ---- -CREATE TABLE table6(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table6 - values (1, 1, 'original'), - (2, 2, 'original'), - (3, 3, 'original'), - (4, 4, 'original'); - --- Table-Non-Cascade ADD COLUMNS ... -alter table table6 add columns(c int, d string); - -insert into table table6 - values (5, 1, 'new', 10, 'ten'), - (6, 2, 'new', 20, 'twenty'), - (7, 3, 'new', 30, 'thirty'), - (8, 4, 'new', 40, 'forty'); - -insert into table table6 - values (9, 5, 'new', 100, 'hundred'), - (10, 6, 'new', 200, 'two hundred'); - -select a,b,c,d from table6; - --- DELETE where old column -delete from table6 where a = 2 or a = 4 or a = 6; - -select a,b,c,d from table6; - - --- --- --- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... DELETE where new column ---- -CREATE TABLE table7(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); - -insert into table table7 - values (1, 1, 'original'), - (2, 2, 'original'), - (3, 3, 'original'), - (4, 4, 'original'); - --- Table-Non-Cascade ADD COLUMNS ... -alter table table7 add columns(c int, d string); - -insert into table table7 - values (5, 1, 'new', 10, 'ten'), - (6, 2, 'new', 20, 'twenty'), - (7, 3, 'new', 30, 'thirty'), - (8, 4, 'new', 40, 'forty'); - -insert into table table7 - values (9, 5, 'new', 100, 'hundred'), - (10, 6, 'new', 200, 'two hundred'); - -select a,b,c,d from table7; - --- DELETE where new column -delete from table7 where a = 1 or c = 30 or c == 100; - -select a,b,c,d from table7; - -DROP TABLE table5; -DROP TABLE table6; -DROP TABLE table7;
http://git-wip-us.apache.org/repos/asf/hive/blob/cb534ab0/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part.q b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part.q new file mode 100644 index 0000000..25ec8bb --- /dev/null +++ b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part.q @@ -0,0 +1,320 @@ +set hive.mapred.mode=nonstrict; +set hive.cli.print.header=true; +set hive.support.concurrency=true; +set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; +set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; +SET hive.exec.schema.evolution=false; +SET hive.vectorized.use.vectorized.input.format=true; +SET hive.vectorized.use.vector.serde.deserialize=false; +SET hive.vectorized.use.row.serde.deserialize=false; +set hive.fetch.task.conversion=none; +SET hive.vectorized.execution.enabled=false; +set hive.exec.dynamic.partition.mode=nonstrict; +set hive.metastore.disallow.incompatible.col.type.changes=true; +set hive.default.fileformat=orc; +set hive.llap.io.enabled=false; + +-- SORT_QUERY_RESULTS +-- +-- FILE VARIATION: ORC, ACID Non-Vectorized, MapWork, Partitioned +-- *IMPORTANT NOTE* We set hive.exec.schema.evolution=false above since schema evolution is always used for ACID. +-- Also, we don't do EXPLAINs on ACID files because the transaction id causes Q file statistics differences... +-- + +CREATE TABLE schema_evolution_data(insert_num int, boolean1 boolean, tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, decimal1 decimal(38,18), float1 float, double1 double, string1 string, string2 string, date1 date, timestamp1 timestamp, boolean_str string, tinyint_str string, smallint_str string, int_str string, bigint_str string, decimal_str string, float_str string, double_str string, date_str string, timestamp_str string, filler string) +row format delimited fields terminated by '|' stored as textfile; +load data local inpath '../../data/files/schema_evolution/schema_evolution_data.txt' overwrite into table schema_evolution_data; + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE ADD COLUMNS +-- +-- +-- SUBSECTION: ALTER TABLE ADD COLUMNS: INT PERMUTE SELECT +-- +-- +CREATE TABLE part_add_int_permute_select(insert_num int, a INT, b STRING) PARTITIONED BY(part INT) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_add_int_permute_select partition(part=1) VALUES (1, 1111, 'new'); + +-- Table-Non-Cascade ADD COLUMNS ... +alter table part_add_int_permute_select add columns(c int); + +insert into table part_add_int_permute_select partition(part=1) VALUES (2, 2222, 'new', 3333); + +-- SELECT permutation columns to make sure NULL defaulting works right +select insert_num,part,a,b from part_add_int_permute_select; +select insert_num,part,a,b,c from part_add_int_permute_select; +select insert_num,part,c from part_add_int_permute_select; + +drop table part_add_int_permute_select; + + +-- SUBSECTION: ALTER TABLE ADD COLUMNS: INT, STRING, PERMUTE SELECT +-- +-- +CREATE TABLE part_add_int_string_permute_select(insert_num int, a INT, b STRING) PARTITIONED BY(part INT) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_add_int_string_permute_select partition(part=1) VALUES (1, 1111, 'new'); + +-- Table-Non-Cascade ADD COLUMNS ... +alter table part_add_int_string_permute_select add columns(c int, d string); + +insert into table part_add_int_string_permute_select partition(part=1) VALUES (2, 2222, 'new', 3333, '4444'); + +-- SELECT permutation columns to make sure NULL defaulting works right +select insert_num,part,a,b from part_add_int_string_permute_select; +select insert_num,part,a,b,c from part_add_int_string_permute_select; +select insert_num,part,a,b,c,d from part_add_int_string_permute_select; +select insert_num,part,a,c,d from part_add_int_string_permute_select; +select insert_num,part,a,d from part_add_int_string_permute_select; +select insert_num,part,c from part_add_int_string_permute_select; +select insert_num,part,d from part_add_int_string_permute_select; + +drop table part_add_int_string_permute_select; + + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> DOUBLE +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> DOUBLE: (STRING, CHAR, VARCHAR) +-- +CREATE TABLE part_change_string_group_double(insert_num int, c1 STRING, c2 CHAR(50), c3 VARCHAR(50), b STRING) PARTITIONED BY(part INT) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_change_string_group_double partition(part=1) SELECT insert_num, double_str, double_str, double_str, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_string_group_double replace columns (insert_num int, c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, b STRING); + +insert into table part_change_string_group_double partition(part=1) SELECT insert_num, double1, double1, double1, 'new' FROM schema_evolution_data WHERE insert_num = 111; + +select insert_num,part,c1,c2,c3,b from part_change_string_group_double; + +drop table part_change_string_group_double; + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP: DATE,TIMESTAMP, (STRING, CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) +-- +CREATE TABLE part_change_date_group_string_group_date_timestamp(insert_num int, c1 DATE, c2 DATE, c3 DATE, c4 DATE, c5 DATE, c6 TIMESTAMP, c7 TIMESTAMP, c8 TIMESTAMP, c9 TIMESTAMP, c10 TIMESTAMP, b STRING) PARTITIONED BY(part INT) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_change_date_group_string_group_date_timestamp partition(part=1) SELECT insert_num, date1, date1, date1, date1, date1, timestamp1, timestamp1, timestamp1, timestamp1, timestamp1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_date_group_string_group_date_timestamp replace columns(insert_num int, c1 STRING, c2 CHAR(50), c3 CHAR(15), c4 VARCHAR(50), c5 VARCHAR(15), c6 STRING, c7 CHAR(50), c8 CHAR(15), c9 VARCHAR(50), c10 VARCHAR(15), b STRING); + +insert into table part_change_date_group_string_group_date_timestamp partition(part=1) VALUES (111, 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'new'); + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,b from part_change_date_group_string_group_date_timestamp; + +drop table part_change_date_group_string_group_date_timestamp; + + + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: +-- (TINYINT, SMALLINT, INT, BIGINT), STRING and +-- (TINYINT, SMALLINT, INT, BIGINT), CHAR and CHAR trunc and +-- (TINYINT, SMALLINT, INT, BIGINT), VARCHAR and VARCHAR trunc +-- +-- +CREATE TABLE part_change_numeric_group_string_group_multi_ints_string_group(insert_num int, + c1 tinyint, c2 smallint, c3 int, c4 bigint, + c5 tinyint, c6 smallint, c7 int, c8 bigint, c9 tinyint, c10 smallint, c11 int, c12 bigint, + c13 tinyint, c14 smallint, c15 int, c16 bigint, c17 tinyint, c18 smallint, c19 int, c20 bigint, + b STRING) PARTITIONED BY(part INT); + +insert into table part_change_numeric_group_string_group_multi_ints_string_group partition(part=1) SELECT insert_num, + tinyint1, smallint1, int1, bigint1, + tinyint1, smallint1, int1, bigint1, tinyint1, smallint1, int1, bigint1, + tinyint1, smallint1, int1, bigint1, tinyint1, smallint1, int1, bigint1, + 'original' FROM schema_evolution_data; + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,b from part_change_numeric_group_string_group_multi_ints_string_group; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_numeric_group_string_group_multi_ints_string_group replace columns (insert_num int, + c1 STRING, c2 STRING, c3 STRING, c4 STRING, + c5 CHAR(50), c6 CHAR(50), c7 CHAR(50), c8 CHAR(50), c9 CHAR(5), c10 CHAR(5), c11 CHAR(5), c12 CHAR(5), + c13 VARCHAR(50), c14 VARCHAR(50), c15 VARCHAR(50), c16 VARCHAR(50), c17 VARCHAR(5), c18 VARCHAR(5), c19 VARCHAR(5), c20 VARCHAR(5), + b STRING) ; + +insert into table part_change_numeric_group_string_group_multi_ints_string_group partition(part=1) VALUES (111, + 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'new'); + +explain +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,b from part_change_numeric_group_string_group_multi_ints_string_group; + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,b from part_change_numeric_group_string_group_multi_ints_string_group; + +drop table part_change_numeric_group_string_group_multi_ints_string_group; + + + +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: +-- (DECIMAL, FLOAT, DOUBLE), STRING and +-- (DECIMAL, FLOAT, DOUBLE), CHAR and CHAR trunc and +-- (DECIMAL, FLOAT, DOUBLE), VARCHAR and VARCHAR trunc +-- +-- +CREATE TABLE part_change_numeric_group_string_group_floating_string_group(insert_num int, + c1 decimal(38,18), c2 float, c3 double, + c4 decimal(38,18), c5 float, c6 double, c7 decimal(38,18), c8 float, c9 double, + c10 decimal(38,18), c11 float, c12 double, c13 decimal(38,18), c14 float, c15 double, + b STRING) PARTITIONED BY(part INT); + +insert into table part_change_numeric_group_string_group_floating_string_group partition(part=1) SELECT insert_num, + decimal1, float1, double1, + decimal1, float1, double1, decimal1, float1, double1, + decimal1, float1, double1, decimal1, float1, double1, + 'original' FROM schema_evolution_data; + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,b from part_change_numeric_group_string_group_floating_string_group; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_numeric_group_string_group_floating_string_group replace columns (insert_num int, + c1 STRING, c2 STRING, c3 STRING, + c4 CHAR(50), c5 CHAR(50), c6 CHAR(50), c7 CHAR(7), c8 CHAR(7), c9 CHAR(7), + c10 VARCHAR(50), c11 VARCHAR(50), c12 VARCHAR(50), c13 VARCHAR(7), c14 VARCHAR(7), c15 VARCHAR(7), + b STRING); + +insert into table part_change_numeric_group_string_group_floating_string_group partition(part=1) VALUES (111, + 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'new'); + +explain +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,b from part_change_numeric_group_string_group_floating_string_group; + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,b from part_change_numeric_group_string_group_floating_string_group; + +drop table part_change_numeric_group_string_group_floating_string_group; + + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP: STRING, (CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) and +-- CHAR, (VARCHAR, VARCHAR trunc, STRING) and VARCHAR, (CHAR, CHAR trunc, STRING) +-- +CREATE TABLE part_change_string_group_string_group_string(insert_num int, + c1 string, c2 string, c3 string, c4 string, + c5 CHAR(50), c6 CHAR(50), c7 CHAR(50), + c8 VARCHAR(50), c9 VARCHAR(50), c10 VARCHAR(50), b STRING) PARTITIONED BY(part INT) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_change_string_group_string_group_string partition(part=1) SELECT insert_num, + string2, string2, string2, string2, + string2, string2, string2, + string2, string2, string2, + 'original' FROM schema_evolution_data; + +select insert_num,part,c1,c2,c3,c4,b from part_change_string_group_string_group_string; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_string_group_string_group_string replace columns (insert_num int, + c1 CHAR(50), c2 CHAR(9), c3 VARCHAR(50), c4 CHAR(9), + c5 VARCHAR(50), c6 VARCHAR(9), c7 STRING, + c8 CHAR(50), c9 CHAR(9), c10 STRING, b STRING) ; + +insert into table part_change_string_group_string_group_string partition(part=1) VALUES (111, + 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', + 'new'); + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,b from part_change_string_group_string_group_string; + +drop table part_change_string_group_string_group_string; + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: +-- TINYINT, (SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE) and +-- SMALLINT, (INT, BIGINT, DECIMAL, FLOAT, DOUBLE) and +-- INT, (BIGINT, DECIMAL, FLOAT, DOUBLE) and +-- BIGINT, (DECIMAL, FLOAT, DOUBLE) +-- +CREATE TABLE part_change_lower_to_higher_numeric_group_tinyint_to_bigint(insert_num int, + c1 tinyint, c2 tinyint, c3 tinyint, c4 tinyint, c5 tinyint, c6 tinyint, + c7 smallint, c8 smallint, c9 smallint, c10 smallint, c11 smallint, + c12 int, c13 int, c14 int, c15 int, + c16 bigint, c17 bigint, c18 bigint, + b STRING) PARTITIONED BY(part INT) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_change_lower_to_higher_numeric_group_tinyint_to_bigint partition(part=1) SELECT insert_num, + tinyint1, tinyint1, tinyint1, tinyint1, tinyint1, tinyint1, + smallint1, smallint1, smallint1, smallint1, smallint1, + int1, int1, int1, int1, + bigint1, bigint1, bigint1, + 'original' FROM schema_evolution_data; + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,b from part_change_lower_to_higher_numeric_group_tinyint_to_bigint; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_lower_to_higher_numeric_group_tinyint_to_bigint replace columns (insert_num int, + c1 SMALLINT, c2 INT, c3 BIGINT, c4 decimal(38,18), c5 FLOAT, c6 DOUBLE, + c7 INT, c8 BIGINT, c9 decimal(38,18), c10 FLOAT, c11 DOUBLE, + c12 BIGINT, c13 decimal(38,18), c14 FLOAT, c15 DOUBLE, + c16 decimal(38,18), c17 FLOAT, c18 DOUBLE, + b STRING) ; + +insert into table part_change_lower_to_higher_numeric_group_tinyint_to_bigint partition(part=1) VALUES (111, + 7000, 80000, 90000000, 1234.5678, 9876.543, 789.321, + 80000, 90000000, 1234.5678, 9876.543, 789.321, + 90000000, 1234.5678, 9876.543, 789.321, + 1234.5678, 9876.543, 789.321, + 'new'); + +select insert_num,part,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,b from part_change_lower_to_higher_numeric_group_tinyint_to_bigint; + +drop table part_change_lower_to_higher_numeric_group_tinyint_to_bigint; + + + +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: +-- DECIMAL, (FLOAT, DOUBLE) and +-- FLOAT, (DOUBLE) +-- +CREATE TABLE part_change_lower_to_higher_numeric_group_decimal_to_float(insert_num int, + c1 decimal(38,18), c2 decimal(38,18), + c3 float, + b STRING) PARTITIONED BY(part INT) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table part_change_lower_to_higher_numeric_group_decimal_to_float partition(part=1) SELECT insert_num, + decimal1, decimal1, + float1, + 'original' FROM schema_evolution_data; + +select insert_num,part,c1,c2,c3,b from part_change_lower_to_higher_numeric_group_decimal_to_float; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table part_change_lower_to_higher_numeric_group_decimal_to_float replace columns (insert_num int, c1 float, c2 double, c3 DOUBLE, b STRING) ; + +insert into table part_change_lower_to_higher_numeric_group_decimal_to_float partition(part=1) VALUES (111, 1234.5678, 9876.543, 1234.5678, 'new'); + +select insert_num,part,c1,c2,c3,b from part_change_lower_to_higher_numeric_group_decimal_to_float; + +drop table part_change_lower_to_higher_numeric_group_decimal_to_float; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/cb534ab0/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part_update.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part_update.q b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part_update.q new file mode 100644 index 0000000..d971649 --- /dev/null +++ b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_part_update.q @@ -0,0 +1,117 @@ +set hive.mapred.mode=nonstrict; +set hive.cli.print.header=true; +set hive.support.concurrency=true; +set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; +set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; +SET hive.exec.schema.evolution=false; +SET hive.vectorized.use.vectorized.input.format=true; +SET hive.vectorized.use.vector.serde.deserialize=false; +SET hive.vectorized.use.row.serde.deserialize=false; +set hive.fetch.task.conversion=none; +SET hive.vectorized.execution.enabled=false; +set hive.exec.dynamic.partition.mode=nonstrict; +set hive.metastore.disallow.incompatible.col.type.changes=true; +set hive.default.fileformat=orc; +set hive.llap.io.enabled=false; + +-- SORT_QUERY_RESULTS +-- +-- FILE VARIATION: ORC, ACID Non-Vectorized, MapWork, Partitioned +-- *IMPORTANT NOTE* We set hive.exec.schema.evolution=false above since schema evolution is always used for ACID. +-- Also, we don't do EXPLAINs on ACID files because the transaction id causes Q file statistics differences... +-- + +CREATE TABLE schema_evolution_data(insert_num int, boolean1 boolean, tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, decimal1 decimal(38,18), float1 float, double1 double, string1 string, string2 string, date1 date, timestamp1 timestamp, boolean_str string, tinyint_str string, smallint_str string, int_str string, bigint_str string, decimal_str string, float_str string, double_str string, date_str string, timestamp_str string, filler string) +row format delimited fields terminated by '|' stored as textfile; +load data local inpath '../../data/files/schema_evolution/schema_evolution_data.txt' overwrite into table schema_evolution_data; + +CREATE TABLE schema_evolution_data_2(insert_num int, boolean1 boolean, tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, decimal1 decimal(38,18), float1 float, double1 double, string1 string, string2 string, date1 date, timestamp1 timestamp, boolean_str string, tinyint_str string, smallint_str string, int_str string, bigint_str string, decimal_str string, float_str string, double_str string, date_str string, timestamp_str string, filler string) +row format delimited fields terminated by '|' stored as textfile; +load data local inpath '../../data/files/schema_evolution/schema_evolution_data_2.txt' overwrite into table schema_evolution_data_2; + +-- +-- +-- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... UPDATE New Columns +--- +CREATE TABLE partitioned_update_1(insert_num int, a INT, b STRING) PARTITIONED BY(part INT) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table partitioned_update_1 partition(part=1) SELECT insert_num, int1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade ADD COLUMNS ... +alter table partitioned_update_1 add columns(c int, d string); + +insert into table partitioned_update_1 partition(part=2) SELECT insert_num, int1, 'new', int1, string1 FROM schema_evolution_data_2 WHERE insert_num <=110; + +insert into table partitioned_update_1 partition(part=1) SELECT insert_num, int1, 'new', int1, string1 FROM schema_evolution_data_2 WHERE insert_num > 110; + +select insert_num,part,a,b,c,d from partitioned_update_1; + +-- UPDATE New Columns +update partitioned_update_1 set c=99; + +select insert_num,part,a,b,c,d from partitioned_update_1; + +alter table partitioned_update_1 partition(part=1) compact 'major'; +alter table partitioned_update_1 partition(part=2) compact 'major'; + +select insert_num,part,a,b,c,d from partitioned_update_1; + +DROP TABLE partitioned_update_1; + +-- +-- +-- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... DELETE where old column +--- +CREATE TABLE partitioned_delete_1(insert_num int, a INT, b STRING) PARTITIONED BY(part INT) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table partitioned_delete_1 partition(part=1) SELECT insert_num, int1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade ADD COLUMNS ... +alter table partitioned_delete_1 add columns(c int, d string); + +insert into table partitioned_delete_1 partition(part=2) SELECT insert_num, int1, 'new', int1, string1 FROM schema_evolution_data_2 WHERE insert_num <=110; + +insert into table partitioned_delete_1 partition(part=1) SELECT insert_num, int1, 'new', int1, string1 FROM schema_evolution_data_2 WHERE insert_num > 110; + +select part,a,b,c,d from partitioned_delete_1; + +-- DELETE where old column +delete from partitioned_delete_1 where insert_num = 102 or insert_num = 104 or insert_num = 106; + +select insert_num,part,a,b,c,d from partitioned_delete_1; + +alter table partitioned_delete_1 partition(part=1) compact 'major'; +alter table partitioned_delete_1 partition(part=2) compact 'major'; + +select insert_num,part,a,b,c,d from partitioned_delete_1; + +DROP TABLE partitioned_delete_1; + +-- +-- +-- SECTION VARIATION: ALTER TABLE ADD COLUMNS ... DELETE where new column +--- +CREATE TABLE partitioned_delete_2(insert_num int, a INT, b STRING) PARTITIONED BY(part INT) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table partitioned_delete_2 partition(part=1) SELECT insert_num, int1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade ADD COLUMNS ... +alter table partitioned_delete_2 add columns(c int, d string); + +insert into table partitioned_delete_2 partition(part=2) SELECT insert_num, int1, 'new', int1, string1 FROM schema_evolution_data_2 WHERE insert_num <=110; + +insert into table partitioned_delete_2 partition(part=1) SELECT insert_num, int1, 'new', int1, string1 FROM schema_evolution_data_2 WHERE insert_num > 110; + +select insert_num,part,a,b,c,d from partitioned_delete_2; + +-- DELETE where new column +delete from partitioned_delete_2 where insert_num = 108 or insert_num > 113; + +select insert_num,part,a,b,c,d from partitioned_delete_2; + +alter table partitioned_delete_2 partition(part=1) compact 'major'; +alter table partitioned_delete_2 partition(part=2) compact 'major'; + +select insert_num,part,a,b,c,d from partitioned_delete_2; + +DROP TABLE partitioned_delete_2; http://git-wip-us.apache.org/repos/asf/hive/blob/cb534ab0/ql/src/test/queries/clientpositive/schema_evol_orc_acid_table.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/schema_evol_orc_acid_table.q b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_table.q new file mode 100644 index 0000000..7767d80 --- /dev/null +++ b/ql/src/test/queries/clientpositive/schema_evol_orc_acid_table.q @@ -0,0 +1,312 @@ +set hive.cli.print.header=true; +set hive.support.concurrency=true; +set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; +set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat; +SET hive.exec.schema.evolution=false; +SET hive.vectorized.use.vectorized.input.format=true; +SET hive.vectorized.use.vector.serde.deserialize=false; +SET hive.vectorized.use.row.serde.deserialize=false; +set hive.fetch.task.conversion=none; +SET hive.vectorized.execution.enabled=false; +set hive.exec.dynamic.partition.mode=nonstrict; +set hive.metastore.disallow.incompatible.col.type.changes=true; +set hive.default.fileformat=orc; +set hive.llap.io.enabled=false; + +-- SORT_QUERY_RESULTS +-- +-- FILE VARIATION: ORC, ACID Non-Vectorized, MapWork, Table +-- *IMPORTANT NOTE* We set hive.exec.schema.evolution=false above since schema evolution is always used for ACID. +-- Also, we don't do EXPLAINs on ACID files because the transaction id causes Q file statistics differences... +-- + +CREATE TABLE schema_evolution_data(insert_num int, boolean1 boolean, tinyint1 tinyint, smallint1 smallint, int1 int, bigint1 bigint, decimal1 decimal(38,18), float1 float, double1 double, string1 string, string2 string, date1 date, timestamp1 timestamp, boolean_str string, tinyint_str string, smallint_str string, int_str string, bigint_str string, decimal_str string, float_str string, double_str string, date_str string, timestamp_str string, filler string) +row format delimited fields terminated by '|' stored as textfile; +load data local inpath '../../data/files/schema_evolution/schema_evolution_data.txt' overwrite into table schema_evolution_data; + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE ADD COLUMNS +-- +-- +-- SUBSECTION: ALTER TABLE ADD COLUMNS: INT PERMUTE SELECT +-- +-- +CREATE TABLE table_add_int_permute_select(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_add_int_permute_select SELECT insert_num, int1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade ADD COLUMNS ... +alter table table_add_int_permute_select add columns(c int); + +insert into table table_add_int_permute_select VALUES (111, 80000, 'new', 80000); + +-- SELECT permutation columns to make sure NULL defaulting works right +select insert_num,a,b from table_add_int_permute_select; +select insert_num,a,b,c from table_add_int_permute_select; +select insert_num,c from table_add_int_permute_select; + +drop table table_add_int_permute_select; + + +-- SUBSECTION: ALTER TABLE ADD COLUMNS: INT, STRING, PERMUTE SELECT +-- +-- +CREATE TABLE table_add_int_string_permute_select(insert_num int, a INT, b STRING) clustered by (a) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_add_int_string_permute_select SELECT insert_num, int1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade ADD COLUMNS ... +alter table table_add_int_string_permute_select add columns(c int, d string); + +insert into table table_add_int_string_permute_select VALUES (111, 80000, 'new', 80000, 'filler'); + +-- SELECT permutation columns to make sure NULL defaulting works right +select insert_num,a,b from table_add_int_string_permute_select; +select insert_num,a,b,c from table_add_int_string_permute_select; +select insert_num,a,b,c,d from table_add_int_string_permute_select; +select insert_num,a,c,d from table_add_int_string_permute_select; +select insert_num,a,d from table_add_int_string_permute_select; +select insert_num,c from table_add_int_string_permute_select; +select insert_num,d from table_add_int_string_permute_select; + +drop table table_add_int_string_permute_select; + + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> DOUBLE +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> DOUBLE: (STRING, CHAR, VARCHAR) +-- +CREATE TABLE table_change_string_group_double(insert_num int, c1 STRING, c2 CHAR(50), c3 VARCHAR(50), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_string_group_double SELECT insert_num, double_str, double_str, double_str, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_string_group_double replace columns (insert_num int, c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, b STRING); + +insert into table table_change_string_group_double VALUES (111, 789.321, 789.321, 789.321, 'new'); + +select insert_num,c1,c2,c3,b from table_change_string_group_double; + +drop table table_change_string_group_double; + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for DATE_GROUP -> STRING_GROUP: DATE,TIMESTAMP, (STRING, CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) +-- +CREATE TABLE table_change_date_group_string_group_date_group(insert_num int, c1 DATE, c2 DATE, c3 DATE, c4 DATE, c5 DATE, c6 TIMESTAMP, c7 TIMESTAMP, c8 TIMESTAMP, c9 TIMESTAMP, c10 TIMESTAMP, b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_date_group_string_group_date_group SELECT insert_num, date1, date1, date1, date1, date1, timestamp1, timestamp1, timestamp1, timestamp1, timestamp1, 'original' FROM schema_evolution_data; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_date_group_string_group_date_group replace columns(insert_num int, c1 STRING, c2 CHAR(50), c3 CHAR(15), c4 VARCHAR(50), c5 VARCHAR(15), c6 STRING, c7 CHAR(50), c8 CHAR(15), c9 VARCHAR(50), c10 VARCHAR(15), b STRING); + +insert into table table_change_date_group_string_group_date_group VALUES (111, 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'new'); + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,b from table_change_date_group_string_group_date_group; + +drop table table_change_date_group_string_group_date_group; + + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: +-- (TINYINT, SMALLINT, INT, BIGINT), STRING and +-- (TINYINT, SMALLINT, INT, BIGINT), CHAR and CHAR trunc and +-- (TINYINT, SMALLINT, INT, BIGINT), VARCHAR and VARCHAR trunc +-- +-- +CREATE TABLE table_change_numeric_group_string_group_multi_ints_string_group(insert_num int, + c1 tinyint, c2 smallint, c3 int, c4 bigint, + c5 tinyint, c6 smallint, c7 int, c8 bigint, c9 tinyint, c10 smallint, c11 int, c12 bigint, + c13 tinyint, c14 smallint, c15 int, c16 bigint, c17 tinyint, c18 smallint, c19 int, c20 bigint, + b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_numeric_group_string_group_multi_ints_string_group SELECT insert_num, + tinyint1, smallint1, int1, bigint1, + tinyint1, smallint1, int1, bigint1, tinyint1, smallint1, int1, bigint1, + tinyint1, smallint1, int1, bigint1, tinyint1, smallint1, int1, bigint1, + 'original' FROM schema_evolution_data; + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,b from table_change_numeric_group_string_group_multi_ints_string_group; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_numeric_group_string_group_multi_ints_string_group replace columns (insert_num int, + c1 STRING, c2 STRING, c3 STRING, c4 STRING, + c5 CHAR(50), c6 CHAR(50), c7 CHAR(50), c8 CHAR(50), c9 CHAR(5), c10 CHAR(5), c11 CHAR(5), c12 CHAR(5), + c13 VARCHAR(50), c14 VARCHAR(50), c15 VARCHAR(50), c16 VARCHAR(50), c17 VARCHAR(5), c18 VARCHAR(5), c19 VARCHAR(5), c20 VARCHAR(5), + b STRING) ; + +insert into table table_change_numeric_group_string_group_multi_ints_string_group VALUES (111, + 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'new'); + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,b from table_change_numeric_group_string_group_multi_ints_string_group; + +drop table table_change_numeric_group_string_group_multi_ints_string_group; + + + +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for NUMERIC_GROUP -> STRING_GROUP: +-- (DECIMAL, FLOAT, DOUBLE), STRING and +-- (DECIMAL, FLOAT, DOUBLE), CHAR and CHAR trunc and +-- (DECIMAL, FLOAT, DOUBLE), VARCHAR and VARCHAR trunc +-- +-- +CREATE TABLE table_change_numeric_group_string_group_floating_string_group(insert_num int, + c1 decimal(38,18), c2 float, c3 double, + c4 decimal(38,18), c5 float, c6 double, c7 decimal(38,18), c8 float, c9 double, + c10 decimal(38,18), c11 float, c12 double, c13 decimal(38,18), c14 float, c15 double, + b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_numeric_group_string_group_floating_string_group SELECT insert_num, + decimal1, float1, double1, + decimal1, float1, double1, decimal1, float1, double1, + decimal1, float1, double1, decimal1, float1, double1, + 'original' FROM schema_evolution_data; + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,b from table_change_numeric_group_string_group_floating_string_group; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_numeric_group_string_group_floating_string_group replace columns (insert_num int, + c1 STRING, c2 STRING, c3 STRING, + c4 CHAR(50), c5 CHAR(50), c6 CHAR(50), c7 CHAR(7), c8 CHAR(7), c9 CHAR(7), + c10 VARCHAR(50), c11 VARCHAR(50), c12 VARCHAR(50), c13 VARCHAR(7), c14 VARCHAR(7), c15 VARCHAR(7), + b STRING); + +insert into table table_change_numeric_group_string_group_floating_string_group VALUES (111, + 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', 'filler', 'filler', 'filler', + 'new'); + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,b from table_change_numeric_group_string_group_floating_string_group; + +drop table table_change_numeric_group_string_group_floating_string_group; + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for STRING_GROUP -> STRING_GROUP: STRING, (CHAR, CHAR trunc, VARCHAR, VARCHAR trunc) and +-- CHAR, (VARCHAR, VARCHAR trunc, STRING) and VARCHAR, (CHAR, CHAR trunc, STRING) +-- +CREATE TABLE table_change_string_group_string_group_string(insert_num int, + c1 string, c2 string, c3 string, c4 string, + c5 CHAR(50), c6 CHAR(50), c7 CHAR(50), + c8 VARCHAR(50), c9 VARCHAR(50), c10 VARCHAR(50), b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_string_group_string_group_string SELECT insert_num, + string2, string2, string2, string2, + string2, string2, string2, + string2, string2, string2, + 'original' FROM schema_evolution_data; + +select insert_num,c1,c2,c3,c4,b from table_change_string_group_string_group_string; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_string_group_string_group_string replace columns (insert_num int, + c1 CHAR(50), c2 CHAR(9), c3 VARCHAR(50), c4 CHAR(9), + c5 VARCHAR(50), c6 VARCHAR(9), c7 STRING, + c8 CHAR(50), c9 CHAR(9), c10 STRING, b STRING) ; + +insert into table table_change_string_group_string_group_string VALUES (111, + 'filler', 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', + 'filler', 'filler', 'filler', + 'new'); + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,b from table_change_string_group_string_group_string; + +drop table table_change_string_group_string_group_string; + + + +------------------------------------------------------------------------------------------ +-- SECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP +-- +-- +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: +-- TINYINT, (SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE) and +-- SMALLINT, (INT, BIGINT, DECIMAL, FLOAT, DOUBLE) and +-- INT, (BIGINT, DECIMAL, FLOAT, DOUBLE) and +-- BIGINT, (DECIMAL, FLOAT, DOUBLE) +-- +CREATE TABLE table_change_lower_to_higher_numeric_group_tinyint_to_bigint(insert_num int, + c1 tinyint, c2 tinyint, c3 tinyint, c4 tinyint, c5 tinyint, c6 tinyint, + c7 smallint, c8 smallint, c9 smallint, c10 smallint, c11 smallint, + c12 int, c13 int, c14 int, c15 int, + c16 bigint, c17 bigint, c18 bigint, + b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_lower_to_higher_numeric_group_tinyint_to_bigint SELECT insert_num, + tinyint1, tinyint1, tinyint1, tinyint1, tinyint1, tinyint1, + smallint1, smallint1, smallint1, smallint1, smallint1, + int1, int1, int1, int1, + bigint1, bigint1, bigint1, + 'original' FROM schema_evolution_data; + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,b from table_change_lower_to_higher_numeric_group_tinyint_to_bigint; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_lower_to_higher_numeric_group_tinyint_to_bigint replace columns (insert_num int, + c1 SMALLINT, c2 INT, c3 BIGINT, c4 decimal(38,18), c5 FLOAT, c6 DOUBLE, + c7 INT, c8 BIGINT, c9 decimal(38,18), c10 FLOAT, c11 DOUBLE, + c12 BIGINT, c13 decimal(38,18), c14 FLOAT, c15 DOUBLE, + c16 decimal(38,18), c17 FLOAT, c18 DOUBLE, + b STRING) ; + +insert into table table_change_lower_to_higher_numeric_group_tinyint_to_bigint VALUES (111, + 7000, 80000, 90000000, 1234.5678, 9876.543, 789.321, + 80000, 90000000, 1234.5678, 9876.543, 789.321, + 90000000, 1234.5678, 9876.543, 789.321, + 1234.5678, 9876.543, 789.321, + 'new'); + +select insert_num,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,b from table_change_lower_to_higher_numeric_group_tinyint_to_bigint; + +drop table table_change_lower_to_higher_numeric_group_tinyint_to_bigint; + + + +-- +-- SUBSECTION: ALTER TABLE CHANGE COLUMNS for "lower" type to "higher" NUMERIC_GROUP: +-- DECIMAL, (FLOAT, DOUBLE) and +-- FLOAT, (DOUBLE) +-- +CREATE TABLE table_change_lower_to_higher_numeric_group_decimal_to_float(insert_num int, + c1 decimal(38,18), c2 decimal(38,18), + c3 float, + b STRING) clustered by (c1) into 2 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true'); + +insert into table table_change_lower_to_higher_numeric_group_decimal_to_float SELECT insert_num, + decimal1, decimal1, + float1, + 'original' FROM schema_evolution_data; + +select insert_num,c1,c2,c3,b from table_change_lower_to_higher_numeric_group_decimal_to_float; + +-- Table-Non-Cascade CHANGE COLUMNS ... +alter table table_change_lower_to_higher_numeric_group_decimal_to_float replace columns (insert_num int, c1 float, c2 double, c3 DOUBLE, b STRING) ; + +insert into table table_change_lower_to_higher_numeric_group_decimal_to_float VALUES (111, 1234.5678, 9876.543, 1234.5678, 'new'); + +select insert_num,c1,c2,c3,b from table_change_lower_to_higher_numeric_group_decimal_to_float; + +drop table table_change_lower_to_higher_numeric_group_decimal_to_float;
