core/sql/regress/executor/TEST013

-- Test: TEST013 (Executor)
-- @@@ 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: Volatile Tables
-- Expected files: EXPECTED013
-- Table created:
-- Limitations:
-- To do:
-- Revision history:

#ifndef SEABASE_REGRESS
set schema cat.t013_sch;
#else
cqd hbase_async_drop_table 'OFF';
--cqd call_embedded_arkcmp 'OFF';
--cqd seabase_volatile_tables 'ON';
set schema trafodion.t013_sch;
#endif

drop table t013t1 cascade;
drop table t013t2 cascade;
drop table t013t3 cascade;

#ifndef SEABASE_REGRESS
drop catalog t013cat;
drop catalog t013_volatile_cat;
#endif
drop schema t013_sch cascade;

control query default POS 'OFF';
#ifNSK
create catalog t013_volatile_cat_$$SYSNAME$$;
#ifNSK
#ifNT
create catalog t013_volatile_cat_NSK;
#ifNT

cleanup obsolete volatile tables;

log LOG013 clear;

#ifndef SEABASE_REGRESS
control query default volatile_catalog 'T013_VOLATILE_CAT';
#endif

#ifndef SEABASE_REGRESS
create schema t013_sch;
set schema $$TEST_CATALOG$$.t013_sch;
#else
set schema trafodion.t013_sch;
create schema trafodion.t013_sch;
#endif

create table t013t1 (a int);
invoke t013t1;
insert into t013t1 values (1);
select * from t013t1;
create index t013t1i on t013t1(a);

create volatile table t013t1(a int, b int);
invoke t013t1;
insert into t013t1 values (1,2);	
select * from t013t1;

create volatile index t013t1i1 on t013t1 (a);
insert into t013t1 values (3,4);	
select * from t013t1;

create volatile index t013t1i2 on t013t1 (b);

update statistics for table t013t1 on every column;

select * from t013_sch.t013t1;

drop volatile index t013t1i1;
drop volatile table t013t1;
invoke t013t1;
select * from t013t1;

create volatile table t013t1(a int, b int, c int);
create volatile index t013t1i on t013t1(b);
invoke t013t1;
insert into t013t1 values (1,2,3);	
select * from t013t1;

set session default sql_session 'END';
#ifndef SEABASE_REGRESS
set session default sql_user 'SQL_USER1';
#endif
set session default sql_session 'BEGIN';

#ifndef SEABASE_REGRESS
grant create on schema t013_sch to sql_user1 ;
grant alter on schema t013_sch to sql_user1 ;
#endif

invoke t013t1;
select * from t013t1;

create volatile table t013t1(a int, b int, c int, d int);
invoke t013t1;
insert into t013t1 values (1,2,3,4);	
select * from t013t1;

#ifndef SEABASE_REGRESS
select * from sql_user1.t013t1;

-- should return error.
select * from $$TEST_CATALOG$$.sql_user1.t013t1;
select * from sql_user1_2.t013t1;
#endif

-- Negative tests

-- cannot use VOLATILE_SCHEMA_ prefix in sql queries
set schema volatile_schema_a;
set schema $$TEST_CATALOG$$.volatile_schema_a;
control query default schema 'volatile_schema_a';
#ifndef SEABASE_REGRESS
control query default schema 'cat.volatile_schema_a';
#else
control query default schema 'seabase.volatile_schema_a';
#endif

select * from volatile_schema_a.t;
select * from $$TEST_CATALOG$$.volatile_schema_a.t;

drop table volatile_schema_a.t;
drop table $$TEST_CATALOG$$.volatile_schema_a.t;

update statistics for table volatile_schema_a.t on every column;
update statistics for table $$TEST_CATALOG$$.volatile_schema_a.t on every column;

-- cannot create volatile index on regular tables and vica-versa
create volatile index tempi on $$TEST_CATALOG$$.t013_sch.t013t1(a);
create volatile index tempi on sch.t013t1(a);

-- cannot create views, alter, etc on volatile tables

-- this will create view on the permanent table t013t1
create view tempv as select * from t013t1;
showddl tempv;

create volatile table t013t2 (a int);
-- this will return an error, no permanent table exists
create view tempv2 as select * from t013t2;

-- will alter base table, t013t1
alter table t013t1 add column z int default 0 not null;

-- this will return an error, no permanent table exists
alter table t013t2 add column z int default 0 not null;

-- cannot use volatile syntax to drop regular tables
drop volatile table $$TEST_CATALOG$$.t013_sch.t013t1;

-- duplicate volatile table error
create volatile table t013t1 (a int);

-- duplicate volatile index error
create volatile index t013t1i1 on t013t1(a);
create volatile index t013t1i1 on t013t1(a);

-- remove volatile index
drop volatile index t013t1i1;

-- volatile index doesn't exist
drop volatile index t013t1i1;

-- remove the volatile table. 
drop volatile table t013t1;

-- volatile table doesn't exist
drop volatile table t013t1;

-- cannot use volatile syntax on regular tables
create volatile index tempi on sch.t013t1(a);
create volatile index tempi on t013t1(a);
drop volatile index t013t1i;
drop volatile table t01351;

-- should not show SESSION_ID or any internal CQDs
showcontrol defaults;

--drop table t013t1;

invoke t013t1;

set session default sql_session 'END';
set session default sql_session 'BEGIN';

invoke t013t1;
drop volatile table t013t1;
create volatile index tempi on t013t1(a);
drop volatile index tempi;

create schema if not exists trafodion.t013sch1;
set schema trafodion.t013sch1;

create volatile table t013t1 (a int);

select b from t013t1;
select t013t1.b from t013t1;
select b from t013_sch.t013t1;

create volatile index t013t1i on t013t1(a desc);

-- explain, showshape...
--explain select * from t013t1;
--explain options 'f' select * from t013t1;
--explain insert into t013t1 values (1);
--explain options 'f' insert into t013t1 values (1);
showshape select * from t013t1 order by a desc;
showshape insert into t013t1 values (1);

set session default sql_session 'END';

set session default sql_session 'BEGIN';

create table t013t3 (a int, b int);
create index t013t3i1 on t013t3(a);
invoke t013t3;

-- test use of regular stmts(drop table, drop index, create index)
-- on volatile tables
create local temporary table t013t3(a int);
invoke t013t3;

-- should drop volatile table
drop table t013t3;
invoke t013t3;

create volatile table t013t3(a int);
create index t013t3i1 on t013t3(a);
showddl t013t3;

-- should drop volatile index	
drop index t013t3i1;
showddl t013t3;

-- return error
drop table tempuser.t013t3;

-- should drop volatile table
#ifndef SEABASE_REGRESS
drop table sql_user1.t013t3;
#else
drop table t013t3;
#endif
invoke t013t3;

-- drop regular table
drop table t013sch1.t013t3;

-- these tests do not automatically make the first col to be NNND and
-- the pkey of the volatile table.
-- they also test for nullable primary keys for volatile tables.
control query default volatile_table_find_suitable_key 'ON';
control query default POS 'MULTI_NODE';

-- NULLABLE unique constraints (primary key, unique key constr)
drop volatile table t013t1;
create volatile table t013t1 (a int, primary key(a));
showddl t013t1, external;

drop volatile table t013t1;
create volatile table t013t1 (a int, primary key(a) droppable);
showddl t013t1, external;

drop volatile table t013t1;
create volatile table t013t1 (a int) store by (a);
showddl t013t1, external;

drop volatile table t013t1;
create volatile table t013t1 (a int, primary key(a))
#ifndef SEABASE_REGRESS
partition by (a)
#endif
;
showddl t013t1, external;

drop volatile table t013t1;
create volatile table t013t1 (a int) store by (a)
#ifndef SEABASE_REGRESS
partition by (a)
#endif
;
showddl t013t1, external;

drop volatile table t013t1;
create volatile table t013t1 (a int unique);
showddl t013t1, external;


-- store by a, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a int);
showddl t013t1, external;

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a char(10), b int);
showddl t013t1, external;

-- store by a, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a int not null);
showddl t013t1, external;

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a char(10) not null, b int not null);
showddl t013t1, external;

-- store by c, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a int, b char(10) not null, c int not null, d date, e int not null);
showddl t013t1, external;

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a date, b char(10), c date not null);
showddl t013t1, external;

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a date, b date not null);
showddl t013t1, external;

-- store by b, 4 partitions
drop volatile table t013t1;
create volatile table t013t1 (a real not null, b char(10));
showddl t013t1, external;

-- store by d, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a real not null, b varchar(10) not null, c char(10), d largeint);
showddl t013t1, external;

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a real not null, b interval second, c varchar(10));
showddl t013t1, external;

-- store by c, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a real not null, b interval second, c interval year, d varchar(10) not null);
showddl t013t1, external;

-- NO PARTITION, float keys not supported
drop volatile table t013t1;
create volatile table t013t1 (a real not null);
showddl t013t1, external;
drop volatile table t013t1;
create volatile table t013t1 (a real);
showddl t013t1, external;

-- store by a, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a varchar(10) not null);
showddl t013t1, external;
drop volatile table t013t1;
create volatile table t013t1 (a varchar(10));
showddl t013t1, external;

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a real not null, b varchar(10) not null);
showddl t013t1, external;

-- NON-NULLABLE columns to get preferenace over nullable columns

-- store by b, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a int, b int not null);
showddl t013t1, external;

-- store by a, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a int not null, b int);
showddl t013t1, external;

-- both nullable cols, choose the first one. store by a, 4 partns
drop volatile table t013t1;
create volatile table t013t1 (a int, b int);
showddl t013t1, external;

-- USER specified clauses will disable auto-selection of keys

-- store by b, 4 partitions.
drop volatile table t013t1;
create volatile table t013t1 (a int not null, b int, primary key(b));
showddl t013t1, external;

-- store by b, 4 partitions.
drop volatile table t013t1;
create volatile table t013t1 (a int not null, b int not null) store by (b);
showddl t013t1, external;

-- store by b, 4 partitions.
drop volatile table t013t1;
create volatile table t013t1 (a int not null, b int not null) 
#ifndef SEABASE_REGRESS
partition by (b)
#endif
;
showddl t013t1, external;


-- CTAS queries
create table t013t2 (a int not null, b int) no partition;
drop volatile table t013t1;
create volatile table t013t1 as select b from t013t2;
showddl t013t1, external;

drop table t013t1;
drop table t013t2;

drop schema t013sch1 cascade;

cleanup obsolete volatile tables;

#ifndef SEABASE_REGRESS
set catalog cat;
set schema $$TEST_CATALOG$$.t013_sch;
#else
set schema trafodion.t013_sch;
#endif

invoke t013t1;

select * from t013t1;
select * from $$TEST_CATALOG$$.t013_sch.t013t1;


log;
drop table t013t1;
drop table t013t2;
drop table t013t3;
drop table t013sch.t013t1 cascade;
drop table t013sch.t013t2 cascade;
drop table t013sch.t013t3 cascade;
get tables in schema t013sch;
drop schema t013sch cascade;
drop schema t013_sch cascade;