-- @@@ 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 @@@
---u------------------------------------------------
-- TEST006
-- Unit Test to test MVQR optimizer rules.
-- Author: Taoufik Ben Abdellatif
-- Owner: MV Query Rewrite team
----------------------------------------------------

obey TEST006(clean_up);
obey TEST006(clean_up_files);
log LOG006 clear;
#ifLINUX
--obey TEST006(check_install_script);
#ifLINUX
obey TEST006(set_up);
obey TEST006(create_tables);
obey TEST006(create_mvs);
obey TEST006(rewrite_queries);
obey TEST006(clean_up);
exit;

?section clean_up
----------------------------------------------------
------------------ clean up section ----------------
----------------------------------------------------
drop schema optimizer_rules cascade;

?section clean_up_files
----------------------------------------------------
------------------ clean up files section ----------
----------------------------------------------------
sh rm residual*;

?section set_up
----------------------------------------------------
-------------------- set up section ----------------
----------------------------------------------------

--control query default query_cache '0';
control query default MVQR_LOG_QUERY_DESCRIPTORS 'LOG';
control query default MVQR_REWRITE_LEVEL '3';
control query default MVQR_REWRITE_ENABLED_OPTION 'ON';
control query default MVQR_PUBLISH_TO 'PRIVATE';
control query default MULTI_JOIN_THRESHOLD '2';

obey GetMvDesc;

create schema optimizer_rules;
set schema optimizer_rules;
set pattern $$MVSchemaName$$ OPTIMIZER_RULES;

prepare checkPlan from
select operator, tname
from table(explain(NULL, 'QUERYSTMT'))
where operator like '%_SCAN%'
order by tname;

?section create_tables
--===========================================
---------- create tables section ------------
--===========================================

create table FACT (
        fday   int,
        fmonth int,
        fyear  int,
        fitem  int,
        fdep   int,
        fstore int,
        fstate int,
        fprice numeric (8,2),
        dimkey int) no partition;

create table fact2 (
        qitem  int,
        qquant int,
        qprice numeric (8,2),
        qdimkey int) no partition;

create table fact3 (
	fday   int,
	fmonth int,
	fyear  int,
	fitem  int,
	fquant int,
	fprice numeric (8,2),
	dimkey int) no partition;

create table dim1 (dkey int not null not droppable primary key, dimdata int, fk1 int) no partition;
create table dim2 (dkey int not null not droppable primary key, dimdata int, fk1 int) no partition;
create table dim3 (dkey int not null not droppable primary key, dimdata int, fk1 int) no partition;
create table dim4 (dkey int not null not droppable primary key, dimdata int, fk1 int) no partition;

insert into fact values
  (25,  3, 2012, 3, 1, 1, 7, 81, 14),
  (14,  1, 2011, 1, 2, 8, 8, 57, 14),
  ( 6,  2, 2012, 9, 3, 3, 6, 86, 18),
  (30,  1, 2012, 9, 7, 5, 4, 36,  7),
  (12, 12, 2011, 6, 1, 6, 2, 76,  4),
  ( 3,  1, 2010, 9, 4, 3, 4, 42,  3),
  (19, 11, 2010, 3, 3, 1, 7, 46,  7),
  (30,  5, 2010, 1, 7, 1, 7, 83, 10),
  (14, 12, 2010, 2, 8, 6, 9, 22,  9),
  (14,  7, 2012, 4, 4, 4, 6, 96, 18),
  (25,  2, 2010, 3, 3, 9, 6, 48,  7),
  (19,  3, 2012, 8, 5, 1, 7, 58, 10),
  (22,  3, 2011, 9, 7, 1, 2, 30, 18),
  (10, 12, 2012, 9, 6, 1, 9, 78, 16),
  (20, 12, 2010, 3, 7, 1, 6, 82,  2),
  (12,  9, 2010, 5, 3, 7, 3, 66,  1),
  (26,  6, 2012, 9, 9, 1, 6, 75, 19),
  (13,  5, 2012, 4, 5, 6, 6, 50,  8),
  (28,  3, 2012, 2, 4, 9, 5, 94, 19),
  ( 9,  7, 2012, 4, 7, 5, 6, 33,  5),
  ( 3,  4, 2011, 2, 9, 3, 1, 50, 19),
  (10, 10, 2010, 6, 5, 7, 9, 21, 18),
  (30,  4, 2012, 2, 7, 5, 9, 59,  5),
  (19,  6, 2011, 1, 5, 1, 8, 28,  2),
  (13,  3, 2010, 9, 5, 3, 9, 36, 17),
  (20,  6, 2011, 3, 2, 2, 8, 56,  3),
  (10,  7, 2010, 1, 3, 4, 1, 73, 11),
  (12,  9, 2011, 5, 2, 9, 2, 54,  8),
  (29,  9, 2012, 3, 6, 2, 5, 79, 11),
  (27, 12, 2010, 4, 2, 2, 9, 70,  1),
  (16,  9, 2010, 9, 3, 6, 9, 90,  3),
  ( 4,  4, 2012, 3, 6, 6, 3, 50,  4),
  (19,  2, 2012, 3, 3, 9, 8, 34,  1),
  ( 8,  6, 2011, 7, 3, 5, 1, 71, 10),
  ( 9,  6, 2011, 2, 7, 6, 7, 40, 15),
  (31, 11, 2011, 2, 6, 1, 9, 78, 10),
  (28,  1, 2012, 3, 1, 5, 8, 39,  6),
  (10,  6, 2011, 6, 4, 2, 5, 77,  2),
  ( 1,  3, 2011, 9, 3, 4, 7, 92,  8),
  ( 6,  9, 2011, 2, 5, 8, 2, 30, 16),
  (23,  5, 2011, 3, 5, 7, 7, 15, 11),
  (28,  7, 2010, 6, 8, 8, 4, 44, 16),
  ( 4, 12, 2010, 3, 2, 7, 6, 84, 19),
  (11,  2, 2011, 1, 2, 1, 6, 69,  8),
  (13, 12, 2011, 7, 7, 6, 2, 35, 19),
  (14, 11, 2011, 6, 5, 5, 3, 69,  3),
  (25, 11, 2011, 2, 1, 4, 2, 31, 11),
  (18,  8, 2010, 2, 4, 1, 8, 23, 16),
  (23,  9, 2010, 4, 4, 8, 9, 88,  8),
  ( 9,  9, 2011, 5, 3, 3, 7, 17, 11);

insert into fact3 values
  (31,  8, 2012, 6, 8, 55, 10),
  (15,  5, 2011, 4, 6, 64,  6),
  (17,  6, 2012, 9, 9, 76, 11),
  (12,  6, 2012, 7, 7, 14,  1),
  ( 1,  4, 2012, 2, 1, 85, 19),
  ( 7,  7, 2010, 8, 9, 21, 17),
  (27,  3, 2011, 4, 3, 94,  5),
  (18,  2, 2010, 6, 7, 47, 13),
  ( 8,  6, 2011, 9, 6, 93, 13),
  (16,  4, 2011, 4, 5, 33, 11),
  ( 5,  8, 2012, 1, 6, 20, 15),
  (13, 12, 2012, 3, 1, 29,  5),
  (30,  9, 2012, 8, 2, 78, 15),
  ( 3,  1, 2012, 1, 2, 96,  9),
  (14,  7, 2012, 8, 7, 57, 17),
  ( 7, 12, 2010, 8, 2, 33,  3),
  ( 1,  2, 2012, 5, 5, 43, 11),
  (29,  6, 2011, 6, 7, 96,  1),
  (30,  4, 2010, 4, 2, 32,  2),
  (16, 10, 2010, 1, 6, 21, 17),
  ( 7,  5, 2011, 7, 8, 15,  6),
  (11,  2, 2011, 9, 4, 27,  6),
  (22, 11, 2010, 5, 9, 61, 17),
  ( 2,  5, 2012, 5, 9, 64,  8),
  (13,  9, 2012, 6, 9, 86, 16),
  ( 7, 10, 2010, 1, 9, 63, 13),
  (13,  6, 2011, 8, 7, 66, 10),
  (24, 12, 2010, 2, 6, 22, 15),
  (20,  5, 2012, 4, 9, 87,  3),
  ( 2,  8, 2010, 1, 7, 91,  6),
  (29, 8 , 2010, 3, 9, 89, 13),
  (30,  6, 2012, 1, 9, 53,  3),
  (29,  8, 2012, 5, 1, 27, 16),
  (12,  3, 2010, 5, 7, 21, 13),
  (19,  6, 2012, 1, 8, 12, 15),
  (21, 11, 2011, 8, 7, 20, 18),
  (30,  1, 2010, 8, 5, 83,  2),
  ( 2, 12, 2010, 9, 7, 39, 18),
  ( 4, 11, 2012, 5, 1, 74,  9),
  (31,  8, 2011, 9, 2, 61,  3),
  (20, 10, 2012, 8, 2, 20, 15),
  (12,  3, 2011, 6, 5, 92,  6),
  (20, 12, 2010, 1, 3, 86,  9),
  ( 5, 11, 2012, 8, 1, 20, 10),
  (14,  5, 2011, 5, 8, 41,  7),
  ( 8,  7, 2012, 2, 7, 12,  2),
  (27,  6, 2012, 9, 5, 65,  7),
  (12,  2, 2011, 9, 2, 35, 18),
  (13,  2, 2011, 1, 5, 40, 16),
  ( 9,  8, 2011, 3, 5, 97, 13);

insert into dim1(dkey, dimdata, fk1) values
  (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9), (10, 10, 10),
  (11, 11, 11), (12, 12, 12), (13, 13, 13), (14, 14, 14), (15, 15, 15), (16, 16, 16), (17, 17, 17), (18, 18, 18), (19, 19, 19);
insert into dim1(dkey, fk1) values (20, 20);

insert into dim2(dkey, dimdata, fk1) 
  select dkey, dimdata, fk1 from dim1;

insert into dim3(dkey, dimdata, fk1)
  select dkey, dimdata, fk1 from dim1;

insert into dim4(dkey, dimdata, fk1)
  select dkey, dimdata, fk1 from dim1;

alter table fact  add constraint fact1_fk foreign key (dimkey) references dim1(dkey);
alter table fact2 add constraint fact2_fk foreign key (qdimkey) references dim1(dkey);
alter table dim1  add constraint dim1_fk foreign key (fk1) references dim2(dkey);
alter table dim2  add constraint dim2_fk foreign key (fk1) references dim3(dkey);

?section create_mvs
--===========================================
------------- create mvs section ------------
--===========================================

create mv sumbyItem
        refresh on request
        initialized on create
        as  select sum(fprice) total_price,
                   sum(dimdata) sumdata,
                   fitem oitem, fdep odep
            from  FACT, DIM1
            where dimkey=dkey
            group by fitem, fdep;

create mv sumbyMonthDepState
        refresh on request
        initialized on create
        as  select sum(fprice) total_price,
                   sum(dimdata) sumdata,
                   fmonth omonth, fyear oyear,
                   fdep odep,
                   fstate ostate
            from  FACT, DIM1
            where dimkey=dkey
            group by fmonth, fyear,
                     fdep,
                     fstate;

-- No range preds.
create mv rangeMjv1
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact3, dim4
	    where dimkey=dkey;

-- single value range pred
create mv rangeMjv2
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata = 10;

-- Between range pred
create mv rangeMjv3
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata between 5 and 15;

-- Between range pred
create mv rangeMjv4
	refresh on request
	initialized on create
	as  select fprice, fquant, 
		         dimkey, dimdata
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata*2 between 10 and 30;

?section ignore

--===========================================
--------------- query section ---------------
--===========================================

?section query1
prepare QueryStmt from
        select sum(fprice) total_price,
               sum(dimdata) sumdata,
               fitem oitem, 
               fdep odep
        from  FACT, DIM1
        where dimkey=dkey
        group by fitem, 
                 fdep
        order by fitem;

?section query2
prepare QueryStmt from
        select sum(fprice) total_price,
               sum(dimdata) sumdata,
               fmonth omonth, 
               fyear oyear,
               fdep odep,
               fstate ostate
          from  FACT, DIM1
                where dimkey=dkey
                group by fmonth, 
                         fyear,
                         fdep,
                         fstate
                order by fmonth;

?section query3
prepare QueryStmt from
      select fprice, fquant, dimkey, dimdata
	    from fact3, dim4
	    where dimkey=dkey
	      and fprice between 1 and 60
	    order by dimkey;

?section query4
prepare QueryStmt from
      select fprice, fquant, dimkey
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata = 10
	    order by dimkey;

?section query5
prepare QueryStmt from
      select fprice, fquant, dimkey
            from fact3, dim4
            where dimkey=dkey
              and dimdata = 10
            order by dimkey;

?section query6
prepare QueryStmt from
      select fprice, fquant, dimkey
            from fact3, dim4
            where dimkey=dkey
              and dimdata = 10
            order by dimkey;

?section query7
prepare QueryStmt from
      select fprice, fquant, dimkey
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata between 5 and 15
	    order by dimkey;

?section query8
prepare QueryStmt from
      select fprice, fquant, dimkey
            from fact3, dim4
            where dimkey=dkey
              and dimdata between 5 and 15
            order by dimkey;

?section query9
prepare QueryStmt from
      select fprice, fquant, dimkey
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata between 5 and 16
	    order by dimkey;

?section query10
prepare QueryStmt from
      select fprice, fquant, dimkey
	    from fact3, dim4
	    where dimkey=dkey
	      and (dimdata between 5 and 15 OR dimdata IS NULL)
	    order by dimkey;

?section query11
prepare QueryStmt from
      select fprice, fquant, dimkey
	    from fact3, dim4
	    where dimkey=dkey
	      and dimdata*2 between 10 and 30
	    order by dimkey;

?section query12
prepare QueryStmt from
      select fprice, fquant, dimkey
            from fact3, dim4
            where dimkey=dkey
              and dimdata*2 between 10 and 30
            order by dimkey;

?section rewrite_queries
--===========================================
--------------- query section ---------------
--===========================================

set pattern $$QueryName$$ Query1;
obey TEST006(compare);

--===========================================
set pattern $$QueryName$$ Query2;
obey TEST006(compare);

--===========================================
-- back join
-- mv matching a subset of the tables
-- range pred on different column - only MJV1 (NotProvided) should match
set pattern $$QueryName$$ Query3;
obey TEST006(compare);

--===========================================
-- single value on dimdata - should match MJV1 (NotProvided), MJV2 (Provided) and MJV3 (NotProvided).
--
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV1';
set pattern $$QueryName$$ Query4;
obey TEST006(compare);

--===========================================
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV2';
set pattern $$QueryName$$ Query5;
obey TEST006(compare);

--===========================================
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV3';
set pattern $$QueryName$$ Query6;
obey TEST006(compare);

--===========================================
-- identical range on dimdata - should match MJV1 (NotProvided) and MJV3 (Provided).
--
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV1';
set pattern $$QueryName$$ Query7;
obey TEST006(compare);

--===========================================
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV3';
set pattern $$QueryName$$ Query8;
obey TEST006(compare);

--===========================================
-- bigger range - should match only MJV1 (NotProvided).
--
set pattern $$QueryName$$ Query9;
obey TEST006(compare);

--===========================================
-- Same range but also IS NULL - should match only MJV1 (NotProvided).
--
set pattern $$QueryName$$ Query10;
obey TEST006(compare);

--===========================================
-- identical range on expression - should match MJV1 (NotProvided), MJV4 (Provided) 
-- and maybe one day also MJV3 (Provided).
--
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV1';
set pattern $$QueryName$$ Query11;
obey TEST006(compare);

--===========================================
control query default MVQR_REWRITE_CANDIDATES 'CAT.OPTIMIZER_RULES.RANGEMJV4';
set pattern $$QueryName$$ Query12;
obey TEST006(compare);

?section check_install_script
--===========================================
------ test the mvqr install scripts --------
--===========================================

log;
sh sh $scriptsdir/mvqr/uninstallMvqrSQ 2>&1;
log LOG006;
sh ps -u `whoami` | grep tdm_arkqm | wc -l | tee -a LOG006;
log;
sh sh $scriptsdir/mvqr/installMvqrSQ 2>&1;
log LOG006;
sh ps -u `whoami` | grep tdm_arkqmm | wc -l | tee -a LOG006;
sh ps -u `whoami` | grep tdm_arkqmp | wc -l | tee -a LOG006;
sh ps -u `whoami` | grep -l tdm_arkqms | wc -l | tee -a LOG006;
log;
sh sh $scriptsdir/mvqr/uninstallMvqrSQ;
sh rm uninstallMvqrSQt.ksh installMvqrSQt.ksh;
log LOG006;
sh ps -u `whoami` | grep tdm_arkqm | wc -l | tee -a LOG006;

--===========================================
--===========================================
--===========================================
?section compare

obey TEST006($$QueryName$$);

execute checkPlan;

execute QueryStmt;

log T006_C1.txt clear;
execute QueryStmt;
log;
log LOG006;

-- Verify the data is correct
control query default MVQR_REWRITE_LEVEL '0';

obey TEST006($$QueryName$$);

execute checkPlan;

execute QueryStmt;

control query default MVQR_REWRITE_LEVEL '3';

log T006_C2.txt clear;
execute QueryStmt;
log;
log LOG006;

-- Diff query results
log;
sh diff T006_C1.txt T006_C2.txt >> LOG006;
log LOG006;
-- Diff is done.

