-- @@@ 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 @@@
-- @@@ START COPYRIGHT @@@
--
------------------------------------------------------
--- TestMV215
--- 2.4.4.
--- PURPOSE: Internal Refresh tests with TPCD database         
------------------------------------------------------



obey TESTMV215(CLEAN_UP);

log LOGMV215 clear;

obey TESTMV215(SET_UP);

-- Run Tests
    set schema cat.tpcd;
    obey TESTMV215(TEST1);
    obey TESTMV215(TEST2);
    obey TESTMV215(TEST3); 
    obey TESTMV215(TEST4);
--- obey TESTMV215(TEST5); -- Bug 276
--- obey TESTMV215(TEST6); -- Bug 276
    obey TESTMV215(TEST7);
    obey TESTMV215(TEST8);
    obey TESTMV215(TEST9);
    obey TESTMV215(TEST10);
-- Clean and exit

obey TESTMV215(CLEAN_UP);

-- The objects that were left in the system:
obey ./TPCD/SHOW_OBJECTS_TPCD;
log;
exit;

-------------------------------------------------------
?section SET_UP
-------------------------------------------------------
control query default insert_vsbb 'USER';
set PARSERFLAGS 3;
set schema cat.tpcd;

obey ./TPCD/CREATEALL.OBEY(SET_UP);

-- insert only selected data

delete from lineitem;
delete from supplier;
delete from orders;
delete from part;
delete from partsupp;
delete from customer;
delete from nation;
delete from region;

ALTER TABLE orders Attributes automatic RANGELOG ;
ALTER TABLE lineitem Attributes automatic RANGELOG ;
ALTER TABLE supplier Attributes automatic RANGELOG ;
ALTER TABLE customer Attributes automatic RANGELOG ;
ALTER TABLE part	Attributes automatic RANGELOG ;
ALTER TABLE partsupp Attributes automatic RANGELOG ;
ALTER TABLE nation	Attributes automatic RANGELOG ;
ALTER TABLE region Attributes automatic RANGELOG ;

cd tpcd/tpcdtemp;
sh import cat.tpcd.nation -I nation.tbl -U nation.fmt;
sh import cat.tpcd.region -I region.tbl -U region.fmt;
sh import cat.tpcd.supplier -I supplier.tbl -U supplier.fmt -r -C 30;
sh import cat.tpcd.lineitem -I lineitem.tbl -U lineitem.fmt -r -C 17000;
sh import cat.tpcd.orders -I orders.tbl -U orders.fmt -r -C 4500;
sh import cat.tpcd.part -I part.tbl -U part.fmt -r -C 600;
sh import cat.tpcd.partsupp -I partsupp.tbl -U partsupp.fmt -r -C 2400;
sh import cat.tpcd.customer -I customer.tbl -U customer.fmt -r -C 450;
cd ../..;

obey PREPARE_DDL_SELECTS;

-----------------------------------------------------
?section CLEAN_UP
-----------------------------------------------------


set schema cat.tpcd;
obey ./TPCD/CREATEMVS(DROPALL);
drop table cat3.details.partsupp;
drop table cat3.details.supplier;
drop table cat3.geography.nation;
drop schema cat3.details;
drop schema cat3.geography;
drop schema cat3.testing;
drop catalog cat3;
obey ./TPCD/CREATEALL.OBEY(CLEAN_UP);

-- ---------------------------------------------------------------------------
?section TEST1
-----------------------------------------------------

obey ./TPCD/CREATEMVS(MV5);

-- Basic Q5
-- for compare 
PREPARE stat1 FROM 
	select n_name, sum(l_extendedprice*(1-l_discount)) as revenue,count(*) rows_in_group
	from customer,orders,lineitem,supplier,nation, region
	where
	    c_custkey = o_custkey
	    and o_orderkey = l_orderkey
	    and l_suppkey = s_suppkey
	    and c_nationkey= s_nationkey
	    and s_nationkey = n_nationkey
	    and n_regionkey = r_regionkey
	    and r_name in ('ASIA','AMERICA','EUROPE','MIDDLE EAST','AFRICA')
	    and o_orderdate >= date '1994-01-01'
	    and o_orderdate < date '1994-01-01' + interval '3' year
	group by n_name
	order by n_name,revenue desc ;

PREPARE stat2 FROM 
	select n_name,revenue,rows_in_group
	from mv4q5 
	order by n_name,revenue desc;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

SET PARAM ?table_name 'SUPPLIER';
execute select_mvs_table_info;
log;
sh import cat.tpcd.supplier -I ./tpcd/tpcdtemp/supplier.tbl -U ./tpcd/tpcdtemp/supplier.fmt -r -F 30 -C 5 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

-- should be equal to 5
select count(*) from table(iud_log_table cat.tpcd.supplier);


REFRESH CAT.TPCD.MV4Q5;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;


obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

log;
sh import cat.tpcd.customer -I ./tpcd/tpcdtemp/customer.tbl -U ./tpcd/tpcdtemp/customer.fmt -r -F 450 -C 75 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update customer
set c_nationkey = 12 -- 'JAPAN'
where c_nationkey = 10 and c_acctbal < 4000;

update customer
set c_nationkey = 10 -- 'IRAN'
where c_nationkey = 12 and c_acctbal > 3000;

update customer
set c_nationkey = 12 -- 'IRAN'
where c_nationkey = 12 and c_acctbal < 2500;

--select count(*),c_custkey 
--from table(iud_log_table customer) 
--group by c_custkey
--order by c_custkey;

REFRESH CAT.TPCD.MV4Q5;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
delete from table (iud_log_table cat.tpcd.customer);
log LOGMV215;

-------------------------------------------------------------
?section TEST2
-------------------------------------------------------------

obey ./TPCD/CREATEMVS(MV9);
-- Basic Q9
PREPARE stat1 FROM 
	select nation, yr, sum(amount) as sum_profit
	from (select
         	n_name as nation, 
         	extract(year from o_orderdate) as yr,
         	((l_extendedprice*(1-l_discount)) - (ps_supplycost*l_quantity)) as amount
     from part,supplier,lineitem,partsupp,orders, nation
     where 
         s_suppkey  = l_suppkey
         and ps_suppkey = l_suppkey
         and ps_partkey = l_partkey
         and p_partkey  = l_partkey
         and o_orderkey = l_orderkey
         and s_nationkey = n_nationkey
         and p_name like '%green%'
    ) as profit
	group by nation, yr
	order by nation, yr;

PREPARE stat2 FROM 
	select nation, yr,sum_profit
	from mv4q9 
	order by nation, yr;



log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

--parts

log;
sh import cat.tpcd.part -I ./tpcd/tpcdtemp/part.tbl -U ./tpcd/tpcdtemp/part.fmt -r -F 600 -C 100 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

refresh mv4q9 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;


log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- partsupp
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.partsupp@ @0@ @20@ ;
log;
sh import cat.tpcd.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 2400 -C 400 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

refresh mv4q9 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;


log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- lineitem
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 17000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

refresh mv4q9 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;


----------------------------------------------------------
?section TEST3
----------------------------------------------------------

obey ./TPCD/CREATEMVS(MV1);
-- Basic Q1
-- for compare
PREPARE stat1 FROM
    select
        l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum( l_extendedprice * (1-l_discount)) as sum_disc_price,
        sum(l_extendedprice * (1-l_discount) * (1 + l_tax) )as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price,
        avg(l_discount) as avg_disc,
        count(*) as count_order
    from lineitem
    where 
        l_shipdate <= date '1998-12-01' - interval '90' day
    group by l_returnflag, l_linestatus
    order by l_returnflag, l_linestatus;

PREPARE stat2 FROM
    select l_returnflag, l_linestatus, sum_qty, sum_base_price, sum_disc_price,
           sum_charge, avg_qty, avg_price, avg_disc, count_order
    from mv4q1
    order by l_returnflag, l_linestatus;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

-- insert delta
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 19500 -C 500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

refresh mv4q1 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

update lineitem
set l_quantity = 1000
where l_partkey between 30.00 and 39.00;

refresh mv4q1 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;


log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

--------------------------------------------------------------
?section TEST4
--------------------------------------------------------------
-- Irefresh from multidelta
obey ./TPCD/CREATEMVS(MV3);
-- Basic Q3
-- for compare
PREPARE stat1 FROM
    select
        l_orderkey, 
        cast(sum(l_extendedprice*(1-l_discount)) as numeric(18,2)) as revenue,
        o_orderdate, o_shippriority
    from customer,orders,lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate >  date '1995-03-15'
    group by l_orderkey, o_orderdate, o_shippriority
    order by revenue desc,o_orderdate;

PREPARE stat2 FROM
    select l_orderkey, revenue, o_orderdate, o_shippriority
    from mv4q3
    order by revenue desc, o_orderdate;        

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

-- iud deltas
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 20000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
sh import cat.tpcd.orders -I ./tpcd/tpcdtemp/orders.tbl -U ./tpcd/tpcdtemp/orders.fmt -r -F 4500 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
sh import cat.tpcd.customer -I ./tpcd/tpcdtemp/customer.tbl -U ./tpcd/tpcdtemp/customer.fmt -r -F 525 -C 75 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update lineitem
set l_comment = 'broken'
where l_suppkey > 30 and l_suppkey < 40;

update customer
set c_comment = 'good'
where c_mktsegment = 'AUTOMOBILE';


refresh mv4q3 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;


----------------------------------------------------
?section TEST5
----------------------------------------------------

obey ./TPCD/CREATEMVS(MV6);
-- Basic Q6
-- for compare
PREPARE stat1 FROM
    select
       l_shipdate,
       cast(sum(l_extendedprice*l_discount)as numeric(18,2)) as revenue
    from lineitem
    where 
        l_shipdate >= date '1994-01-01'
        and l_shipdate < date '1994-01-01' + interval '1' year
        and l_discount between .06 - 0.01 and .06 + 0.01
        and l_quantity < 24
    group by l_shipdate
    order by revenue;

PREPARE stat2 FROM
    select l_shipdate, revenue
    from mv4q6
    order by revenue;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 22500 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

delete from lineitem where l_partkey < 10;

refresh mv4q6 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

----------------------------------------------------
?section TEST6
----------------------------------------------------

obey ./TPCD/CREATEMVS(MV10);
-- Basic Q10
-- for compare
PREPARE stat1 FROM
    select 
        c_custkey, c_name,
        cast (sum(l_extendedprice*(1-l_discount)) 
        as numeric(18,2)) as revenue,
        c_acctbal,  n_name, c_address, c_phone, c_comment
    from customer,orders,lineitem, nation
    where 
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate >= date '1993-10-01'
        and o_orderdate < date '1993-10-01' + interval '3' month
        and l_returnflag = 'R'
        and c_nationkey = n_nationkey
    group by c_custkey, c_name, c_acctbal, c_phone, n_name,
        c_address, c_comment
    order by revenue desc;

PREPARE stat2 FROM
    select c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment
    from mv4q10
    order by revenue desc;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- customer
log;
sh import cat.tpcd.customer -I ./tpcd/tpcdtemp/customer.tbl -U ./tpcd/tpcdtemp/customer.fmt -r -F 600 -C 75 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update customer
set c_nationkey = 12 -- 'JAPAN'
where c_custkey in (
	select c_custkey
	from customer,nation
	where c_nationkey = n_nationkey 
	      and c_acctbal < 4000
	      and n_name = 'IRAN'
	);


refresh mv4q10 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- orders
log;
sh import cat.tpcd.orders -I ./tpcd/tpcdtemp/orders.tbl -U ./tpcd/tpcdtemp/orders.fmt -r -F 7000 -C 1000 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

delete from orders where (o_orderkey between 4 and 15) or (o_orderkey between 20 and 28);

refresh mv4q10 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- lineitem
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 25000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update lineitem
set l_quantity = 1050
where l_partkey = 30.00 or l_partkey = 35.00;

refresh mv4q10 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- nation
insert into nation values (40, 1, 'ISRAEL', ''), (41, 3, 'PERU', 'no');
delete from nation where n_name = 'ISRAEL';
insert into nation values (40, 1, 'ISRAEL', 'OK');

refresh mv4q10 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

----------------------------------------------------
?section TEST7
----------------------------------------------------
-- Irefresh from multidelta, one of them is self-cancelling
obey ./TPCD/CREATEMVS(MV11);
-- Basic Q11
-- for compare
PREPARE stat1 FROM
    select
        ps_partkey, sum(ps_supplycost*ps_availqty) as pvalue
    from partsupp,supplier,nation
    where ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
    group by ps_partkey
    order by pvalue desc;

PREPARE stat2 FROM
    select 
        ps_partkey, pvalue
    from mv4q11
    order by pvalue desc;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- iud delta
log;
sh import cat.tpcd.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 2800 -C 400 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
sh import cat.tpcd.supplier -I ./tpcd/tpcdtemp/supplier.tbl -U ./tpcd/tpcdtemp/supplier.fmt -r -F 35 -C 10 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update partsupp
set ps_comment = 'not available'
where ps_partkey between 52 and 57;

-- nation ----> self cancelling delta
insert into nation values (45, 2, 'BOLIVIA', 'yes'), (46, 3, 'ICELAND', '');
delete from nation where n_name = 'ICELAND';
insert into nation values (87, 3, 'HOLLAND','yes');
delete from nation where n_name = 'HOLLAND' or n_name = 'BOLIVIA';


refresh mv4q11 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;


-------------------------------------------------------
?section TEST8
-------------------------------------------------------
-- Irefresh from multidelta
obey ./TPCD/CREATEMVS(MV12);
-- Basic Q12
-- for compare
PREPARE stat1 FROM
    select
        l_shipmode, 
        sum (case when o_orderpriority ='1-URGENT'
        or o_orderpriority ='2-HIGH'
        then 1 else 0 end) as high_line_count,
        sum (case when o_orderpriority <> '1-URGENT'
        and o_orderpriority <> '2-HIGH'
        then 1 else 0 end) as low_line_count
    from orders,lineitem
    where 
        o_orderkey = l_orderkey
        and l_shipmode in ('MAIL','SHIP')
        and l_commitdate < l_receiptdate
        and l_shipdate < l_commitdate
        and l_receiptdate >= date '1994-01-01'
        and l_receiptdate < date '1994-01-01' + interval '1' year
    group by l_shipmode
    order by l_shipmode;

PREPARE stat2 FROM
    select
        l_shipmode, high_line_count, low_line_count
    from mv4q12
    order by l_shipmode;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- iud deltas
log;
sh import cat.tpcd.orders -I ./tpcd/tpcdtemp/orders.tbl -U ./tpcd/tpcdtemp/orders.fmt -r -F 8000 -C 1000 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 27500 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update orders
set o_totalprice = 1099.00 where o_custkey < 100;

refresh mv4q12 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;


-------------------------------------------------------
?section TEST9
-------------------------------------------------------

obey ./TPCD/CREATEMVS(MV15);
-- Basic Q15
-- for compare
PREPARE stat1 FROM
    select
        l_suppkey , sum(l_extendedprice * (1-l_discount)) as total
    from lineitem
    where
        l_shipdate >= date '1996-01-01'
        and l_shipdate < date '1996-01-01' + interval '3' month
    group by l_suppkey
    order by l_suppkey;

PREPARE stat2 FROM
    select l_suppkey, total
    from mv4q15
    order by l_suppkey;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- lineitem
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 30000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;
delete from lineitem where l_partkey < 100;


refresh mv4q15 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-------------------------------------------------------
?section TEST10
-------------------------------------------------------
-- This test checks MV that is based on tables from different schemas

-- Creating new schemas
create catalog cat3;
create schema cat3.details;
create schema cat3.geography;
create schema cat3.testing;
-- Creating new tables
set schema cat3.details;
Create table partsupp  (
   ps_partkey          int                not null not droppable, 
   ps_suppkey          int                not null not droppable, 
   ps_availqty         int                not null not droppable, 
   ps_supplycost       numeric(12,2)      not null not droppable, 
   ps_comment          varchar(199)       not null not droppable, 
primary key (ps_partkey,ps_suppkey) not droppable) 
store by primary key 
location $$partition1$$
partition ( 
   add first key (          1001) location $$partition2$$);

Create table supplier  (
   s_suppkey           int                not null not droppable, 
   s_nationkey         int                not null not droppable, 
   s_acctbal           numeric(12,2)      not null not droppable, 
   s_phone             char(15)           not null not droppable, 
   s_name              char(25)           not null not droppable, 
   s_address           varchar(40)        not null not droppable, 
   s_comment           varchar(101)       not null not droppable, 
primary key (s_suppkey) not droppable) 
store by primary key 
location $$partition1$$
partition ( 
   add first key (            501) location $$partition2$$);

set schema cat3.geography;

Create table nation  (
   n_nationkey         int                not null not droppable, 
   n_regionkey         int                not null not droppable, 
   n_name              char(25)           not null not droppable, 
   n_comment           varchar(152)       not null not droppable, 
primary key (n_nationkey) not droppable) 
store by primary key 
location $$partition1$$;

-- Inserting data
cd ./tpcd/tpcdtemp;
sh import cat3.details.supplier -I supplier.tbl -U supplier.fmt -r -C 60;
sh import cat3.details.partsupp -I partsupp.tbl -U partsupp.fmt -r -C 6000;
sh import cat3.geography.nation -I nation.tbl -U nation.fmt;
cd ../..;

-- Creating MV67
set schema cat3.testing;
obey ./TPCD/CREATEMVS(MV67);
-- Basic Q67
-- for compare
PREPARE stat1 FROM
    select
    ps_suppkey,
    sum(ps_supplycost*ps_availqty) as total
    from cat3.details.partsupp,
         cat3.details.supplier,
         cat3.geography.nation
    where 
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
    group by ps_suppkey
    order by total;

PREPARE stat2 FROM
    select ps_suppkey, total
    from cat3.testing.mv4q67
    order by total;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- partsupp
log;
sh import cat3.details.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 6000 -C 400 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

refresh mv4q67 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- supplier
log;
sh import cat3.details.supplier -I ./tpcd/tpcdtemp/supplier.tbl -U ./tpcd/tpcdtemp/supplier.fmt -r -F 60 -C 10 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215;
log LOGMV215;

update cat3.details.supplier
set s_comment = 'not trustable'
where s_suppkey < 15 and s_suppkey > 3;


refresh mv4q67 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- nation
insert into cat3.geography.nation values (50, 1, 'CANADA', 'yes'), (51, 2, 'MEXICO', 'yes');

refresh mv4q67 ;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

