-- @@@ 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 @@@
------------------------------------------------------
-- MVGROUPS

create table t1 ( a int not null not droppable , primary key (a)) LOCATION $$partition1$$;
ALTER TABLE t1 attribute all mvs allowed;
create table t2 ( b int not null not droppable , primary key (b)) LOCATION $$partition1$$;
ALTER TABLE t2 attribute all mvs allowed;


create mv mv1 refresh on request initialize on refresh as select * from t1 group by a;
ALTER MV mv1 attribute all mvs allowed;

create mv mv2 refresh on request initialize on refresh as select a from mv1 group by a;
ALTER MV mv2 attribute all mvs allowed;

create mv mv3 refresh on request initialize on refresh as select a from t2, mv2 where a=b group by a;
ALTER MV mv3 attribute all mvs allowed;


create mvgroup g1;

-- FAIL
alter mvgroup g1 add mv3;

-- PASS
alter mvgroup g1 add mv2;

-- FAIL
alter mvgroup g1 add mv3;


-- PASS
alter mvgroup g1 add mv1;

 
-- PASS
alter mvgroup g1 add mv3;

-- FAIL
alter mvgroup g1 remove mv1;

-- FAIL
alter mvgroup g1 remove mv2;


-- PASS
alter mvgroup g1 remove mv3;

-- PASS
alter mvgroup g1 remove mv1;

-- PASS
alter mvgroup g1 remove mv2;


-- PASS
drop mvgroup g1;
drop mv mv3;
drop mv mv2;
drop mv mv1;
drop table t1;
drop table t2;




create table t1 ( a int not null not droppable , primary key (a)) LOCATION $$partition1$$;
ALTER TABLE t1 attribute all mvs allowed;
create table t2 ( b int not null not droppable , primary key (b)) LOCATION $$partition1$$;
ALTER TABLE t2 attribute all mvs allowed;


create mv mv1 refresh on request initialize on refresh as select * from t1 group by a;
ALTER MV mv1 attribute all mvs allowed;

create mv mv2 refresh on request initialize on refresh as select a from mv1 group by a;
ALTER MV mv2 attribute all mvs allowed;

create mv mv3 refresh on request initialize on refresh as select a from t2, mv2 where a=b group by a;
ALTER MV mv3 attribute all mvs allowed;


create mvgroup g1;

-- FAIL
alter mvgroup g1 add mv3;

-- PASS
alter mvgroup g1 add mv2;

-- FAIL
alter mvgroup g1 add mv3;


-- PASS
alter mvgroup g1 add mv1;

 
-- PASS
alter mvgroup g1 add mv3;

-- FAIL
-- like alter mvgroup g1 remove mv1;
drop mv mv1; 

-- FAIL
-- like alter mvgroup g1 remove mv2;
drop mv mv2;

-- PASS
-- like alter mvgroup g1 remove mv3;
drop mv mv3;

-- FAIL
-- like alter mvgroup g1 remove mv1 but is used by mv2;
drop mv mv1;

-- PASS
--like alter mvgroup g1 remove mv2;
drop mv mv2;

drop mv mv3;
drop mv mv2;
drop mv mv1;
drop table t1;
drop table t2;
drop mvgroup g1;












