-- @@@ 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 @@@
------------------------------------------------------------------------------
--- TestMV503 (known diff for NSK exists for bug 604 - delete when bug closed)
--	Add COLUMN
--- PURPOSE: 
------------------------------------------------------------------------------

obey TESTMV503(CLEAN_UP);
obey TESTMV503(SET_UP);
log LOGMV503 clear;


------------------------------------------------------------------------------
-- ADD COLUMN
-- Run Tests
	obey TESTMV503(ADD1);
	obey TESTMV503(ADD2); 
	obey TESTMV503(ADD3); 

-- Clean and exit
log;
obey TESTMV503(CLEAN_UP);
exit;

?section SET_UP
-- Create the base tables
create catalog c;
create schema c.s;
set schema c.s;
set PARSERFLAGS 1;
set envvar MV_UNSUPPORTED_FEATURE_DEBUG 1;
-- obey PREPARE_DDL_SELECTS;

?section ADD1

create table T_503_A ( a int );
ALTER TABLE T_503_A attribute all mvs allowed;
create mv MV_503_A refresh on request 
initialized on refresh
as
select * from T_503_A group by a;
ALTER MV MV_503_A attribute all mvs allowed;
 
showddl T_503_A;
showddl table (iud_log_table T_503_A);

ALTER TABLE T_503_A ADD COLUMN b int;

showddl T_503_A;
showddl table (iud_log_table T_503_A);

ALTER TABLE T_503_A ADD COLUMN c int default 3;

showddl T_503_A;
showddl table (iud_log_table T_503_A);

ALTER TABLE T_503_A ADD COLUMN d int default 5 NOT NULL NOT DROPPABLE;

showddl T_503_A;
showddl table (iud_log_table T_503_A);

ALTER TABLE T_503_A ADD COLUMN e int default 7 NOT NULL NOT DROPPABLE UNIQUE;

showddl T_503_A;
showddl table (iud_log_table T_503_A);


create table T_503_B ( a int not null not droppable unique);
ALTER TABLE T_503_B attribute all mvs allowed;
create table T_503_C ( a int );
ALTER TABLE T_503_C attribute all mvs allowed;

ALTER TABLE T_503_C ADD COLUMN b int REFERENCES T_503_B (a);

ALTER TABLE T_503_A ADD COLUMN f int REFERENCES T_503_B (a);

showddl T_503_A;
showddl table (iud_log_table T_503_A);


drop mv MV_503_A;
drop table T_503_A;
drop table T_503_C;
drop table T_503_B;

?section ADD2

------------------------------------------------------------------------------
-- add column exceeding bitmap when the log is empty
-- should drop the log and create a new one

set envvar DEBUG_LOG_TABLE;


create table T_503_A2 (	a1 int,  a2 int,  a3 int,  a4 int,  a5 int,  a6 int,  a7 int,  a8 int,  a9 int, a10 int,
						a11 int, a12 int, a13 int, a14 int, a15 int, a16 int, a17 int, a18 int, a19 int, a20 int,
						a21 int, a22 int, a23 int) location $$partition1$$; 
ALTER TABLE T_503_A2 attribute all mvs allowed;


create mv MV_503_A2 refresh on request 
initialized on refresh
as
select * from T_503_A2;
ALTER MV MV_503_A2 attribute all mvs allowed;
 
ALTER TABLE T_503_A2 ADD COLUMN a24 int;
ALTER TABLE T_503_A2 ADD COLUMN a25 int;
ALTER TABLE T_503_A2 ADD COLUMN a26 int;
ALTER TABLE T_503_A2 ADD COLUMN a27 int;
ALTER TABLE T_503_A2 ADD COLUMN a28 int;
ALTER TABLE T_503_A2 ADD COLUMN a29 int;
ALTER TABLE T_503_A2 ADD COLUMN a30 int;
ALTER TABLE T_503_A2 ADD COLUMN a31 int;
ALTER TABLE T_503_A2 ADD COLUMN a32 int;
ALTER TABLE T_503_A2 ADD COLUMN a33 int;
ALTER TABLE T_503_A2 ADD COLUMN a34 int;
ALTER TABLE T_503_A2 ADD COLUMN a35 int;
ALTER TABLE T_503_A2 ADD COLUMN a36 int;
ALTER TABLE T_503_A2 ADD COLUMN a37 int;
ALTER TABLE T_503_A2 ADD COLUMN a38 int;
ALTER TABLE T_503_A2 ADD COLUMN a39 int;
ALTER TABLE T_503_A2 ADD COLUMN a40 int;
ALTER TABLE T_503_A2 ADD COLUMN a41 int;
ALTER TABLE T_503_A2 ADD COLUMN a42 int;
ALTER TABLE T_503_A2 ADD COLUMN a43 int;
ALTER TABLE T_503_A2 ADD COLUMN a44 int;
ALTER TABLE T_503_A2 ADD COLUMN a45 int;
ALTER TABLE T_503_A2 ADD COLUMN a46 int;
ALTER TABLE T_503_A2 ADD COLUMN a47 int;
ALTER TABLE T_503_A2 ADD COLUMN a48 int;
ALTER TABLE T_503_A2 ADD COLUMN a49 int;
ALTER TABLE T_503_A2 ADD COLUMN a50 int;
ALTER TABLE T_503_A2 ADD COLUMN a51 int;
ALTER TABLE T_503_A2 ADD COLUMN a52 int;
ALTER TABLE T_503_A2 ADD COLUMN a53 int;
ALTER TABLE T_503_A2 ADD COLUMN a54 int;
ALTER TABLE T_503_A2 ADD COLUMN a55 int;
ALTER TABLE T_503_A2 ADD COLUMN a56 int;
ALTER TABLE T_503_A2 ADD COLUMN a57 int;
ALTER TABLE T_503_A2 ADD COLUMN a58 int;
ALTER TABLE T_503_A2 ADD COLUMN a59 int;
ALTER TABLE T_503_A2 ADD COLUMN a60 int;
ALTER TABLE T_503_A2 ADD COLUMN a61 int;
ALTER TABLE T_503_A2 ADD COLUMN a62 int;
ALTER TABLE T_503_A2 ADD COLUMN a63 int;

-- this should cause drop and recreate log

ALTER TABLE T_503_A2 ADD COLUMN a64 int;


ALTER TABLE T_503_A2 ADD COLUMN a65 int;
ALTER TABLE T_503_A2 ADD COLUMN a66 int;
ALTER TABLE T_503_A2 ADD COLUMN a67 int;
ALTER TABLE T_503_A2 ADD COLUMN a68 int;
ALTER TABLE T_503_A2 ADD COLUMN a69 int;
ALTER TABLE T_503_A2 ADD COLUMN a70 int;
ALTER TABLE T_503_A2 ADD COLUMN a71 int;



drop mv MV_503_A2;
drop table T_503_A2;






?section ADD3
------------------------------------------------------------------------------
-- add column exceeding bitmap when the log is not empty
-- should result in an error

set envvar DEBUG_LOG_TABLE;


create table T_503_A3 (	a1 int,  a2 int,  a3 int,  a4 int,  a5 int,  a6 int,  a7 int,  a8 int,  a9 int, a10 int,
						a11 int, a12 int, a13 int, a14 int, a15 int, a16 int, a17 int, a18 int, a19 int, a20 int,
						a21 int, a22 int, a23 int) location $$partition1$$; 
ALTER TABLE T_503_A3 attribute all mvs allowed;

create mv MV_503_A3 refresh on request 
initialized on create
as
select * from T_503_A3;
ALTER MV MV_503_A3 attribute all mvs allowed;
 
-- make the iud log not empty
insert into T_503_A3 values (1,2,3,4,5,6,7,8,9, 10,11,12,13,14,15,16,17,18,19,20,21,22,23);


ALTER TABLE T_503_A3 ADD COLUMN a24 int;
ALTER TABLE T_503_A3 ADD COLUMN a25 int;
ALTER TABLE T_503_A3 ADD COLUMN a26 int;
ALTER TABLE T_503_A3 ADD COLUMN a27 int;
ALTER TABLE T_503_A3 ADD COLUMN a28 int;
ALTER TABLE T_503_A3 ADD COLUMN a29 int;
ALTER TABLE T_503_A3 ADD COLUMN a30 int;
ALTER TABLE T_503_A3 ADD COLUMN a31 int;
ALTER TABLE T_503_A3 ADD COLUMN a32 int;
ALTER TABLE T_503_A3 ADD COLUMN a33 int;
ALTER TABLE T_503_A3 ADD COLUMN a34 int;
ALTER TABLE T_503_A3 ADD COLUMN a35 int;
ALTER TABLE T_503_A3 ADD COLUMN a36 int;
ALTER TABLE T_503_A3 ADD COLUMN a37 int;
ALTER TABLE T_503_A3 ADD COLUMN a38 int;
ALTER TABLE T_503_A3 ADD COLUMN a39 int;
ALTER TABLE T_503_A3 ADD COLUMN a40 int;
ALTER TABLE T_503_A3 ADD COLUMN a41 int;
ALTER TABLE T_503_A3 ADD COLUMN a42 int;
ALTER TABLE T_503_A3 ADD COLUMN a43 int;
ALTER TABLE T_503_A3 ADD COLUMN a44 int;
ALTER TABLE T_503_A3 ADD COLUMN a45 int;
ALTER TABLE T_503_A3 ADD COLUMN a46 int;
ALTER TABLE T_503_A3 ADD COLUMN a47 int;
ALTER TABLE T_503_A3 ADD COLUMN a48 int;
ALTER TABLE T_503_A3 ADD COLUMN a49 int;
ALTER TABLE T_503_A3 ADD COLUMN a50 int;
ALTER TABLE T_503_A3 ADD COLUMN a51 int;
ALTER TABLE T_503_A3 ADD COLUMN a52 int;
ALTER TABLE T_503_A3 ADD COLUMN a53 int;
ALTER TABLE T_503_A3 ADD COLUMN a54 int;
ALTER TABLE T_503_A3 ADD COLUMN a55 int;
ALTER TABLE T_503_A3 ADD COLUMN a56 int;
ALTER TABLE T_503_A3 ADD COLUMN a57 int;
ALTER TABLE T_503_A3 ADD COLUMN a58 int;
ALTER TABLE T_503_A3 ADD COLUMN a59 int;
ALTER TABLE T_503_A3 ADD COLUMN a60 int;
ALTER TABLE T_503_A3 ADD COLUMN a61 int;
ALTER TABLE T_503_A3 ADD COLUMN a62 int;
ALTER TABLE T_503_A3 ADD COLUMN a63 int;



-- FAIL
ALTER TABLE T_503_A3 ADD COLUMN a64 int;
-- FAIL
ALTER TABLE T_503_A3 ADD COLUMN a65 int;
-- FAIL
ALTER TABLE T_503_A3 ADD COLUMN a66 int;
-- FAIL
ALTER TABLE T_503_A3 ADD COLUMN a67 int;
-- FAIL
ALTER TABLE T_503_A3 ADD COLUMN a68 int;


drop mv MV_503_A3;
drop table T_503_A3;



?section CLEAN_UP
set schema c.s;
drop schema c.s;
drop catalog c;
reset envvar MV_UNSUPPORTED_FEATURE_DEBUG;


