testdata/workloads/functional-query/queries/QueryTest/alter-table-zorder.test

====
---- QUERY
create table insert_data_z (i int, d int, f int, b boolean);
insert into insert_data_z values (1, 2, 3, false), (4, 5, 6, true);
====
---- QUERY
create table insert_zsorted (i int, d int, f int, b boolean);
====
---- QUERY
# Test setting the sort.columns and sort.zorder property
alter table insert_zsorted sort by zorder(i, d);
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','i,d                 '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test inserting after alter table
insert into table insert_zsorted select i, d, f, b from insert_data_z;
---- RUNTIME_PROFILE
row_regex: .*order by: ZORDER: i, d
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted;
---- RESULTS
2
====
---- QUERY
# Test altering the sort.columns andd sort.order property
alter table insert_zsorted sort by zorder(b, d, f);
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','b,d,f               '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test inserting after alter table
insert into table insert_zsorted select i, d, f, b from insert_data_z;
---- RUNTIME_PROFILE
row_regex: .*order by: ZORDER: b, d, f
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted;
---- RESULTS
4
====
---- QUERY
# Test renaming a column in the sort by zorder list.
alter table insert_zsorted change d e int;
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','b,e,f               '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test inserting after alter table
insert into table insert_zsorted select i, d, f, b from insert_data_z;
---- RUNTIME_PROFILE
row_regex: .*order by: ZORDER: b, d, f
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted;
---- RESULTS
6
====
---- QUERY
# Test replacing the column list, including a column in the sort by zorder list.
alter table insert_zsorted replace columns (i bigint, e decimal(12,2), f boolean);
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','e,f                 '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test inserting after alter table
insert into table insert_zsorted select i, cast(d as decimal(12,2)), b from insert_data_z;
---- RUNTIME_PROFILE
row_regex: .*order by: ZORDER: CAST\(d AS DECIMAL\(12,2\)\), b
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted;
---- RESULTS
8
====
---- QUERY
# Test dropping a column in the sort by list
alter table insert_zsorted drop column f;
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','e                   '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test that a dropped column cannot be added as a sort column
alter table insert_zsorted sort by zorder(f);
---- CATCH
AnalysisException: Could not find SORT BY column 'f' in table.
====
---- QUERY
# Test that erroneous query didn't change sort columns
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','e                   '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test removing the sort.columns property
alter table insert_zsorted sort by zorder ();
describe formatted insert_zsorted;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','                    '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test inserting after alter table
insert into table insert_zsorted select i, cast(d as decimal(12,2)) from insert_data_z;
---- RUNTIME_PROFILE
aggregation(SUM, InitialRunsCreated): 0
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted;
---- RESULTS
10
====
---- QUERY
create table insert_zsorted_partitioned (i int, d int, f int, b boolean) partitioned by (p int) sort by zorder (i, d);
====
---- QUERY
# Test removing all sort columns.
alter table insert_zsorted_partitioned sort by zorder();
describe formatted insert_zsorted_partitioned;
---- RESULTS: VERIFY_IS_NOT_IN
'','sort.columns        ','i,d                 '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test inserting after alter table
insert into table insert_zsorted_partitioned partition (p=1) select i, d, f, b from insert_data_z;
---- RUNTIME_PROFILE
aggregation(SUM, InitialRunsCreated): 0
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted_partitioned;
---- RESULTS
2
====
---- QUERY
# Re-add a sort column.
alter table insert_zsorted_partitioned sort by zorder(d, i);
describe formatted insert_zsorted_partitioned;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','d,i                 '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
insert into table insert_zsorted_partitioned partition (p=1) select i, d, f, b from insert_data_z;
---- RUNTIME_PROFILE
row_regex: .*order by: ZORDER: d, i
====
---- QUERY
# Test selection after alter table
select count(*) from insert_zsorted_partitioned;
---- RESULTS
4
====
---- QUERY
describe formatted insert_zsorted_partitioned;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','d,i                 '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Test dropping one sort column, so that one remains.
alter table insert_zsorted_partitioned drop column i;
describe formatted insert_zsorted_partitioned;
---- RESULTS: VERIFY_IS_NOT_IN
'','sort.columns        ','d,i                 '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Creating table and swapping sorting orders
create table swapping_table (i int, j int, k int) sort by zorder (i, j);
describe formatted swapping_table;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','i,j                 '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Creating table and swapping sorting orders
alter table swapping_table sort by lexical (i);
describe formatted swapping_table;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','i                   '
'','sort.order          ','LEXICAL             '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Creating table and swapping sorting orders
alter table swapping_table sort by zorder (i, j, k);
describe formatted swapping_table;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','i,j,k               '
'','sort.order          ','ZORDER              '
---- TYPES
STRING,STRING,STRING
====
---- QUERY
# Creating table and swapping sorting orders
alter table swapping_table sort by ();
describe formatted swapping_table;
---- RESULTS: VERIFY_IS_SUBSET
'','sort.columns        ','                    '
'','sort.order          ','LEXICAL             '
---- TYPES
STRING,STRING,STRING
====