core/sql/regress/core/TEST116

-- Test116 (CORE)
-- @@@ 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 @@@
--
-- Functionality:
--      Test transactions support for DDL operations.
--      This test requires the ENV variable TM_ENABLE_DDL_TRANS
--      is enabled in ms.env and environment restarted.
--      Also DDL_TRANSACTIONS CQD must be 'ON'.
-- Expected Files: EXPECTED116


?section main
obey TEST116(clean_up);

log LOG116 clear;
control query default DDL_TRANSACTIONS 'ON';

-- Simple create then rollback
------------------------------
begin work;
create table t116t1(a1 int, c1 int, b1 char(6));
create table t116t2(a2 int, c2 int, b2 char(6));
get user hbase objects, match 'TRAFODION.SCH.T116T1';
get user hbase objects, match 'TRAFODION.SCH.T116T2';

rollback work;
get user hbase objects, match 'TRAFODION.SCH.T116T1';
get user hbase objects, match 'TRAFODION.SCH.T116T2';

--repeat the same tables again
------------------------------
begin work;
create table t116t1(a1 int, c1 int, b1 char(6));
create table t116t2(a2 int, c2 int, b2 char(6));
get user hbase objects, match 'TRAFODION.SCH.T116T1';
get user hbase objects, match 'TRAFODION.SCH.T116T2';
rollback work;
get user hbase objects, match 'TRAFODION.SCH.T116T1';
get user hbase objects, match 'TRAFODION.SCH.T116T2';

-- unique constraint
---------------------
begin work;
create table t116t1(a1 int, c1 int, b1 char(6));
alter table t116t1 add constraint tc1 unique (c1);
get user hbase objects, match 'TRAFODION.SCH.T116T1';
get user hbase objects, match 'TRAFODION.SCH.TC1';
rollback work;
get user hbase objects, match 'TRAFODION.SCH.T116T1';
get user hbase objects, match 'TRAFODION.SCH.TC1';

--setup two tables
-------------------
begin work;
create table t116t1(a1 int, c1 int, b1 char(6));
insert into t116t1 values(1,1,'a');
create table t116t2(a1 int, c1 int, b1 char(6));
insert into t116t2 values(1,1,'a');
commit work;

--drop table simple case
--------------------------
get user hbase objects, match 'TRAFODION.SCH.T116T1';
begin work;
drop table t116t1;
commit work;
get user hbase objects, match 'TRAFODION.SCH.T116T1';

--drop table with rollback
----------------------------
--sh `echo "exists '$TEST_SCHEMA.T116T2'" | regrhbase.ksh >> LOG116`;
get user hbase objects, match 'TRAFODION.SCH.T116T2';
begin work;
insert into t116t2 values(3,3,'c');
select * from t116t2;
drop table t116t2;
insert into t116t2 values(1,1,'a');
select * from t116t2;
get user hbase objects, match 'TRAFODION.SCH.T116T2';
rollback work;
get user hbase objects, match 'TRAFODION.SCH.T116T2';
select * from t116t2;
insert into t116t2 values(1,1,'a');
select * from t116t2;

--create table1 with dml, drop table2
-------------------------------------
get user hbase objects, match 'TRAFODION.SCH.T116T2';
get user hbase objects, match 'TRAFODION.SCH.T116T3';
begin work;
create table t116t3(a1 int, c1 int, b1 char(6));
insert into t116t3 values(2,2,'b');
get user hbase objects, match 'TRAFODION.SCH.T116T2';
get user hbase objects, match 'TRAFODION.SCH.T116T3';
drop table t116t2;
commit work;
get user hbase objects, match 'TRAFODION.SCH.T116T2';
get user hbase objects, match 'TRAFODION.SCH.T116T3';

--create table1 with dml , drop table2 and rollback
---------------------------------------------------
get user hbase objects, match 'TRAFODION.SCH.T116T3';
get user hbase objects, match 'TRAFODION.SCH.T116T4';
begin work;
create table t116t4(a1 int, c1 int, b1 char(6));
insert into t116t4 values(2,2,'b');
get user hbase objects, match 'TRAFODION.SCH.T116T3';
get user hbase objects, match 'TRAFODION.SCH.T116T4';
select * from t116t3;
drop table t116t3;
insert into t116t3 values(2,2,'b');
get user hbase objects, match 'TRAFODION.SCH.T116T3';
rollback work;
get user hbase objects, match 'TRAFODION.SCH.T116T3';
get user hbase objects, match 'TRAFODION.SCH.T116T4';
select * from t116t3;
insert into t116t3 values(2,2,'b');

-- tests to validate ddl definitions are correctly loaded
drop table if exists t116t1;
create table t116t1(a int not null, b varchar(2), 
   c varchar(4) character set utf8 not null);
create view t116v1 as select * from t116t1;
invoke t116t1;
begin work;
invoke t116t1;
invoke t116v1;
alter table t116t1 alter column b varchar(3);
invoke t116t1;
invoke t116v1;
commit work;
invoke t116t1;
invoke t116v1;

begin work;
invoke t116t1;
invoke t116v1;
alter table t116t1 alter column b varchar(4);
invoke t116t1;
invoke t116v1;
rollback work;

invoke t116t1;
invoke t116v1;

-- concurrent ddl operations
drop table if exists t116t1 cascade;
begin work;
create table t116t1 (a int);

sh sqlci -iTEST116\(concurrent_ddl\) >> LOG116;

commit work;
invoke t116t1;
cleanup table t116t1;

-- test table/schema drops and get stmts with read uncommitted access
create schema if not exists t116sch1;
set schema t116sch1;
drop table if exists t116t1;
drop table if exists t116t2;

create table t116t1 (a int);
begin work;
get tables in schema t116sch1, match 'T116T1';
drop table t116t1;
get tables in schema t116sch1;
create table t116t2 (a int);
get tables in schema t116sch1;
drop schema t116sch1 cascade;
get tables in schema t116sch1;
select count(*) from "_MD_".objects where schema_name = 'T116SCH1' 
   for read uncommitted access;
rollback work;
get tables in schema t116sch1;

set schema sch;

-- ddl with autocommit off
set transaction autocommit off;
drop table if exists t116t2;
commit work;
invoke t116t2;
create table t116t2 (a int);
invoke t116t2;
rollback work;
invoke t116t2;
create table t116t2 (a int);
commit work;
invoke t116t2;
-- ddl not allowed with autocommit off
purgedata t116t2;
alter table t116t2 alter column a largeint;
cleanup table t116t2;
rollback work;
set transaction autocommit on;
drop table if exists t116t2;
create table t116t2 (a int) attribute hbase format;
set transaction autocommit off;
alter table t116t2 alter column a largeint;
rollback work;
set transaction autocommit on;

-- index creation checking
-- index creation is done with multiple transactions
-- to avoid populating the index within the transaction
create table t116t5 (c1 int not null primary key, c2 int, c3 int) ;
insert into t116t5 values (1,1,1), (2,2,2), (3,3,2);
-- create index - Second attempt should fail 
create index t116t5_i1 on t116t5(c2);
create index t116t5_i1 on t116t5(c2);
set parserflags 1;
select count(*) from table(index_table t116t5_i1);
-- create unique index - Both attempts should fail with the same error
create unique index t116t5_i2 on t116t5(c3);
create unique index t116t5_i2 on t116t5(c3);
-- create unique index with no populate
create unique index t116t5_i2 on t116t5(c3) no populate;
-- Both attempts should fail with the same error
populate index t116t5_i2 on t116t5;
populate index t116t5_i2 on t116t5;

-- region transactions support
create table t116t6 (a int not null primary key, b int);

-- scenario 1, concurrent hbase transaction with insert(checkAndPut)
begin work;
insert into t116t6 values (1,1);

-- insert a row using a concurrent hbase xn
sh sqlci -iTEST116\(concurrent_hbase_xn_ins\) >> LOG116;

-- should not show conflict
commit work;
select * from t116t6;

-- scenario 2, concurrent region transaction with insert(checkAndPut)
delete from t116t6;
begin work;
insert into t116t6 values (1,1);

-- insert a row using a concurrent region xn
sh sqlci -iTEST116\(concurrent_region_xn_ins\) >> LOG116;

-- should show conflict
commit work;
select * from t116t6;

-- scenario 3, concurrent hbase transaction with upsert(put)
delete from t116t6;
begin work;
upsert into t116t6 values (1,1);

-- upsert a row using a concurrent hbase xn
sh sqlci -iTEST116\(concurrent_hbase_xn_ups\) >> LOG116;

-- should not show conflict
commit work;
select * from t116t6;

-- scenario 4, concurrent region transaction with upsert(put)
delete from t116t6;
begin work;
upsert into t116t6 values (1,1);

-- insert a row using a concurrent region xn
sh sqlci -iTEST116\(concurrent_region_xn_ups\) >> LOG116;

-- should show conflict
commit work;
select * from t116t6;

-- scenario 5, concurrent hbase transaction with delete
delete from t116t6;
insert into t116t6 values (1,1);
begin work;
delete from t116t6 where a = 1 and b > 0;

-- delete a row using a concurrent hbase xn
sh sqlci -iTEST116\(concurrent_hbase_xn_del\) >> LOG116;

-- should not show conflict
commit work;
select * from t116t6;

-- scenario 6, concurrent region transaction with delete
delete from t116t6;
insert into t116t6 values (1,1);
begin work;
delete from t116t6 where a = 1 and b > 0;

-- delete a row using a concurrent region xn
sh sqlci -iTEST116\(concurrent_region_xn_del\) >> LOG116;

-- should show conflict
commit work;
select * from t116t6;

-- scenario 7, concurrent hbase transaction with checkanddelete
delete from t116t6;
insert into t116t6 values (1,1);
begin work;
delete from t116t6 where a = 1;

-- delete a row using a concurrent hbase xn
sh sqlci -iTEST116\(concurrent_hbase_xn_checkanddel\) >> LOG116;

-- should not show conflict
commit work;
select * from t116t6;

-- scenario 8, concurrent region transaction with checkanddelete
delete from t116t6;
insert into t116t6 values (1,1);
begin work;
delete from t116t6 where a = 1;

-- delete a row using a concurrent region xn
sh sqlci -iTEST116\(concurrent_region_xn_checkanddel\) >> LOG116;

-- should show conflict
commit work;
select * from t116t6;

-- scenario 9, concurrent hbase transaction with checkandupdate
delete from t116t6;
insert into t116t6 values (1,1);
begin work;
update t116t6 set b = 333 where a = 1;

-- update row using a concurrent hbase xn
sh sqlci -iTEST116\(concurrent_hbase_xn_checkandupd\) >> LOG116;

-- should not show conflict
commit work;
select * from t116t6;

-- scenario 10, concurrent region transaction with checkandupdate
delete from t116t6;
insert into t116t6 values (1,1);
begin work;
update t116t6 set b = 333 where a = 1;

-- update row using a concurrent region xn
sh sqlci -iTEST116\(concurrent_region_xn_checkandupd\) >> LOG116;

-- should show conflict
commit work;
select * from t116t6;


obey TEST116(clean_up);
log;
exit;

?section concurrent_ddl
cqd ddl_transactions 'ON';
begin work;

-- should fail
create table t116t1 (a int);
commit work;

?section concurrent_hbase_xn_ins
cqd traf_use_region_xn 'OFF';
prepare s from insert into trafodion.sch.t116t6 values (1,2);
explain options 'f' s;
execute s;

?section concurrent_hbase_xn_ups
cqd traf_use_region_xn 'OFF';
prepare s from upsert into trafodion.sch.t116t6 values (1,2);
explain options 'f' s;
execute s;

?section concurrent_region_xn_ins
cqd traf_use_region_xn 'ON';
prepare s from insert into trafodion.sch.t116t6 values (1,2);
explain options 'f' s;
execute s;

?section concurrent_region_xn_ups
cqd traf_use_region_xn 'ON';
prepare s from upsert into trafodion.sch.t116t6 values (1,2);
explain options 'f' s;
execute s;

?section concurrent_hbase_xn_del
cqd traf_use_region_xn 'OFF';
prepare s from delete from trafodion.sch.t116t6 where a = 1 and b > 0;
explain options 'f' s;
execute s;

?section concurrent_region_xn_del
cqd traf_use_region_xn 'ON';
prepare s from delete from trafodion.sch.t116t6 where a = 1 and b > 0;
explain options 'f' s;
execute s;

?section concurrent_hbase_xn_checkanddel
cqd traf_use_region_xn 'OFF';
prepare s from delete from trafodion.sch.t116t6 where a = 1;
explain options 'f' s;
execute s;

?section concurrent_region_xn_checkanddel
cqd traf_use_region_xn 'ON';
prepare s from delete from trafodion.sch.t116t6 where a = 1;
explain options 'f' s;
execute s;

?section concurrent_hbase_xn_checkandupd
cqd traf_use_region_xn 'OFF';
prepare s from update trafodion.sch.t116t6 set b = 111 where a = 1;
explain options 'f' s;
execute s;

?section concurrent_region_xn_checkandupd
cqd traf_use_region_xn 'ON';
prepare s from update trafodion.sch.t116t6 set b = 111 where a = 1;
explain options 'f' s;
execute s;

?section clean_up
drop table t116t1;
drop table t116t2;
drop table t116t3;
drop table t116t4;
drop table t116t5;
drop table t116t6;