core/sql/regress/seabase/TEST027

-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- tests for support of multiple column families
-- tests for alter table alter column
-- tests for reserved words usage in create/add/alter stmts

create schema trafodion.sch027;
set schema trafodion.sch027;

obey TEST027(clean_up);

log LOG027 clear;

cqd TRAF_MULTI_COL_FAM 'ON';
cqd traf_aligned_row_format 'OFF';

drop table if exists t027t01;
create table t027t01 (a int not null primary key);
invoke t027t01;
showddl t027t01;

drop table if exists t027t01;
create table t027t01 (a int not null primary key) attribute default column family 'cf';
invoke t027t01;
showddl t027t01;

drop table if exists t027t01;
create table t027t01 (a int not null primary key, "cf2".b int) attribute default column family 'cf';
invoke t027t01;
showddl t027t01;

drop table if exists t027t01;
create table t027t01 (a int not null primary key, "cf2".b int, cf3.c int) 
       attribute default column family 'cf';
invoke t027t01;
showddl t027t01;

create index t027t01i1 on t027t01(b);
invoke t027t01;
showddl t027t01;

insert into t027t01 values (1,2,3);
select * from t027t01;
update t027t01 set c = 33 where a = 1;
select * from t027t01;
delete from t027t01;
select * from t027t01;

alter table t027t01 add column "cf2".d int;
invoke t027t01;
showddl t027t01;

alter table t027t01 add column "cf4".e int;
invoke t027t01;
showddl t027t01;

alter table t027t01 drop column d;
invoke t027t01;
showddl t027t01;

alter table t027t01 add column "cf2".d int;
invoke t027t01;
showddl t027t01;

create table t027t011 like t027t01;
invoke t027t011;
showddl t027t011;

insert into t027t011 values (1, 2, 3, 4, 5);
select * from t027t011;
purgedata t027t011;
invoke t027t011;
showddl t027t011;
select * from t027t011;

drop table t027t011;
create table t027t011 as select * from t027t01;
invoke t027t011;
showddl t027t011;

drop table t027t011;
create table t027t011("cf".a, "cf2".b, cf3.c, "cf4".e, "cf2".d) as select * from t027t01;
invoke t027t011;
showddl t027t011;

create volatile table t027t03 ("cf1".a int, "cf2".b int, c int);
invoke t027t03;
showddl t027t03;

drop volatile table t027t03;
create volatile table t027t03 ("cf1".a int, "cf2".b int, c int) attribute default column family 'cf';
invoke t027t03;
showddl t027t03;

drop table if exists t027t02;
create table t027t02 (
     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
    a30.a30 int) attribute default column family 'cf';

drop table if exists t027t02;
create table t027t02 (
     "cf".a0a0 int, "cf".a1a1 int, "cf".a2a2 int, "cf".a3a3 int, "cf".a4a4 int, "cf".a5a5 int, "cf".a6a6 int, "cf".a7a7 int, "cf".a8a8 int, "cf".a9a9 int,
     "cf".a10a10 int, "cf".a11a11 int, "cf".a12a12 int, "cf".a13a13 int, "cf".a14a14 int, "cf".a15a15 int, "cf".a16a16 int, "cf".a17a17 int, "cf".a18a18 int, "cf".a19a19 int,
     "cf".a20a20 int, "cf".a21a21 int, "cf".a22a22 int, "cf".a23a23 int, "cf".a24a24 int, "cf".a25a25 int, "cf".a26a26 int, "cf".a27a27 int, "cf".a28a28 int, "cf".a29a29 int,
    "cf".a30a30 int, "cf".a31a31 int, "cf".a32a32 int, "cf".a33a33 int) 
attribute default column family 'cf';
invoke t027t02;

drop table if exists t027t03;
create table t027t03("cf1".a int not null, "cf2".b int not null, c int not null, d int not null, 
 primary key (a, b));
invoke t027t03;
insert into t027t03 values (1,1,1,1);
insert into t027t03 values (1,2,2,2);
select * from t027t03;

-- create table like metadata table
drop table if exists t027t02;
create table t027t02 like "_MD_".keys;
invoke t027t02;
select * from t027t02;

-- negative tests
drop table if exists t027t02;

-- cannot have 3 part col name
create table t027t02 (a.a.a  int);

-- cannot have col fam for aligned format tables
create table t027t02 ("cf".a int) attribute aligned format;

-- cannot specify col fam for dropped cols
alter table t027t01 drop column "cf2".d;

-- cannot create a different col fam for an index col
create index t027t01i2 on t027t01("cf2".b);

-- cannot use col fam in dml stmts 
select * from t027t01 where "cf".a = 1;

-- cannot have > 32 col families
create table t027t02 (
     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
    a30.a30 int, a31.a31 int, a32.a32 int not null primary key);

create table t027t02 (
     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
    a30.a30 int, a31.a31 int);

create table t027t02 (
     a0.a0 int, a1.a1 int, a2.a2 int, a3.a3 int, a4.a4 int, a5.a5 int, a6.a6 int, a7.a7 int, a8.a8 int, a9.a9 int,
     a10.a10 int, a11.a11 int, a12.a12 int, a13.a13 int, a14.a14 int, a15.a15 int, a16.a16 int, a17.a17 int, a18.a18 int, a19.a19 int,
     a20.a20 int, a21.a21 int, a22.a22 int, a23.a23 int, a24.a24 int, a25.a25 int, a26.a26 int, a27.a27 int, a28.a28 int, a29.a29 int,
    a30.a30 int, a31.a31 int) attribute default column family 'cf';

-- tests for alter varchar column 
drop table if exists t027t7;
create table t027t7(a int not null, b varchar(2), 
   c varchar(4) character set utf8 not null, z int not null primary key)
salt using 2 partitions;

invoke t027t7;
insert into t027t7 values (1, 'ab', 'cd', 10);
select * from t027t7;
alter table t027t7 alter column b varchar(3);
invoke t027t7;
alter table t027t7 alter column c varchar(5) character set utf8;
invoke t027t7;
alter table t027t7 alter column c varchar(4) character set utf8;
invoke t027t7;

alter table t027t7 alter column a largeint;
invoke t027t7;
alter table t027t7 alter column b varchar(4) character set utf8;
invoke t027t7;
alter table t027t7 alter column c varchar(6);
invoke t027t7;
alter table t027t7 alter column b varchar(2);
invoke t027t7;
alter table t027t7 alter column a int;
invoke t027t7;
alter table t027t7 alter column a smallint default 0 not null;
invoke t027t7;
select * from t027t7;

-- aligned format
drop table if exists t027t7 cascade;
create table t027t7(a int not null, b varchar(2), 
   c varchar(4) character set utf8 not null, z int not null primary key)
salt using 2 partitions attribute aligned format;
invoke t027t7;
insert into t027t7 values (1, 'ab', 'cd', 10);
select * from t027t7;
alter table t027t7 alter column a largeint;
invoke t027t7;
select * from t027t7;
alter table t027t7 drop column b;
invoke t027t7;
select * from t027t7;
alter table t027t7 add column b char(10) default 'abc' not null;
invoke t027t7;
select * from t027t7;
alter table t027t7 drop column b;
invoke t027t7;
select * from t027t7;
create view t027v1 as select * from t027t7;
get all views on table t027t7;
invoke t027v1;
alter table t027t7 alter column a smallint;
invoke t027v1;

-- negative tests for alter/drop column
alter table t027t7 alter column b int;
alter table t027t7 alter column a char(10);
alter table t027t7 alter column c char(1);
alter table t027t7 drop column e;
alter table t027t7 drop column a;
alter table t027v1 alter column a smallint;
get all views on table t027t7;

-- tests for alter column rename
drop table if exists t027t7 cascade;
create table t027t7(a int not null, b varchar(2), 
   c varchar(4) character set utf8 not null, z int not null primary key)
attribute aligned format
salt using 2 partitions;
create index t027t7i1 on t027t7(b);
insert into t027t7 values (1, 'ab', 'cd', 10);
invoke t027t7;
select * from t027t7;
alter table t027t7 alter column b rename to bb;
invoke t027t7;
select * from t027t7;
alter table t027t7 alter column z rename to zz;
invoke t027t7;
select * from t027t7;
create view t027v1(zz) as select zz from t027t7;
alter table t027t7 alter column c rename to cc;
create view t027v2(zz) as select zz from t027t7 where cc = 'a';
showddl t027v2;

-- negative tests for alter column rename
alter table t027t7 alter column zz rename to a;
alter table t027t7 alter column "_SALT_" rename to nosalt;
alter table t027t7 alter column cc rename to "_SALT_";
alter table t027t7 alter column zz rename to zzz;
alter table t027t7 alter column cc rename to ccc;

-- alter col with views
drop table if exists t027t7 cascade;
create table t027t7 (a varchar(10), b int)
attribute aligned format;
create view t027v1 as select * from t027t7;
create view t027v12 as select * from t027v1;
create view t027v11 as select * from t027t7;
create view t027v122 as select * from t027v12;
create view t027v113 as select * from t027v11;
get all views on table t027t7;
alter table t027t7 alter column a varchar(20);
get all views on table t027t7;

-- some alter operations cannot be performed within a user xn
cqd ddl_transactions 'ON';
begin work;
alter table t027t7 drop column b;
rollback work;
begin work;
alter table t027t7 alter column b largeint;
rollback work;

-- reserved words cannot be used as colnames in create/add/alter stmts
drop table if exists t027t7 cascade;
create table t027t7 (syskey int);
create table t027t7 ("_SALT_" int);
create table t027t7 ("_DIVISION_2_" int);
create table t027t7 (a int not null primary key);
alter table t027t7 add column "_SALT_" int;
alter table t027t7 alter column a rename to SYSKEY;

-- if cqd is specified, then reserved cols can be used. 
-- Use this cqd carefully.
cqd traf_allow_reserved_colnames 'ON';
drop table if exists t027t7 cascade;
create table t027t7 (syskey int not null primary key, b int);
alter table t027t7 add column "_DIVISION_1" int;
alter table t027t7 alter column b rename to "_SALT_";
invoke t027t7;

-- not null and default clause can appear in any order
drop table if exists t027t1 cascade;
create table t027t1 (a int, b int not null, c int default 10,
   d int default 10 not null, e int not null default 10, 
   f int not null default 10 check (f > 0),
   g int not null not droppable default 10);
invoke t027t1;
insert into t027t1 (b) values (10);
select * from t027t1;
alter table t027t1 add column h int not null default 10;
invoke t027t1;
select * from t027t1;

--should give error
create table t027t2 (a int default 10 not null default 20);

-- cleanup
?section clean_up
drop table if exists t027t7 cascade;
drop table if exists t027t01;
drop table if exists t027t02;
drop table if exists t027t011;
drop table if exists t027t03;

log;