-- @@@ 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 @@@
----------------------------------------------------
-- TEST009
-- Indirect GroupBy verification tests
-- Author: Tom Reyes
-- Owner: MV Query Rewrite team
----------------------------------------------------
obey test009(clean_up);
log log009 clear;
obey test009(hcube_create);
obey test009(hcube_populate);
--obey test009(hcube_indexes);
obey test009(hcube_stats);
obey test009(mvqr_cqds);
obey test009(igb_setup_mv);
-- test with NO query caching
cqd query_cache '0';
showcontrol default query_cache, match full, no header;
obey test009(igb_test1);

-- test with query caching
cqd query_cache '16384';
showcontrol default query_cache, match full, no header;
obey test009(igb_test1);

-- test mvqr master cqd
obey test009(mvqr_master_cqd);

-- verify fix to soln 10-100430-9921
-- this used to cause mxcmp to saveabend from a GenAssert in GenPreCode.cpp 
-- ScalarVariance::preCodeGen which requires all 3 args to be double prec
cqd MVQR_REWRITE_CANDIDATES 'CAT.MYHCUBE.YMV1';
prepare xx from 
  select y1.b, stddev(y1.d) s1
  from  y1
  group by y1.b;
cqd MVQR_REWRITE_CANDIDATES reset;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
cqd MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
  select y1.b, stddev(y1.d) s1
  from  y1
  group by y1.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

cqd MVQR_REWRITE_LEVEL '4'; 

-- verify fix to soln 10-100429-9890
cqd MVQR_REWRITE_CANDIDATES 'CAT.MYHCUBE.YMV1';
prepare xx from 
  select y1.b, stddev(y1.d)/sum(y1.d)
  from  y1
  group by y1.b;
cqd MVQR_REWRITE_CANDIDATES reset;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
cqd MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
  select y1.b, stddev(y1.d)/sum(y1.d)
  from  y1
  group by y1.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

log;

obey test009(clean_up);

exit;

?section mvqr_master_cqd
control query default MVQR_REWRITE_LEVEL '0'; 
showcontrol default mvqr_rewrite_level, match full, no header;
showcontrol default multi_join_threshold, match full, no header;

control query default MVQR_REWRITE_LEVEL '4'; 
showcontrol default mvqr_rewrite_level, match full, no header;
showcontrol default multi_join_threshold, match full, no header;

--control query default MVQR_REWRITE_LEVEL reset; 
--showcontrol default mvqr_rewrite_level, match full, no header;
--showcontrol default multi_join_threshold, match full, no header;

?section mvqr_cqds
set schema cat.myhcube;
control query default MVQR_REWRITE_ENABLED_OPTION 'OFF';
control query default REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT '1';
control query default MVQR_ALL_JBBS_IN_QD 'ON';
control query default MVQR_USE_RI_FOR_EXTRA_HUB_TABLES 'ON';

control query default MVQR_LOG_QUERY_DESCRIPTORS 'LOG'; 
control query default MVQR_REWRITE_LEVEL '4'; 
control query default MVQR_PUBLISH_TO 'PRIVATE'; 

?section igb_setup_mv
-- This is used to make sure the MV is used in the plan.
-- explain options 'f' is nicer but unstable 
-- causing many false alarms from build to build.
prepare checkPlan from
select operator, tname
from table(explain(NULL, 'XX'))
where operator like '%_SCAN%'
order by tname;

drop mv mv1;
CREATE MV MV1
    REFRESH ON REQUEST
    INITIALIZED ON create
    ENABLE QUERY REWRITE
    AS
SELECT a, txt, 
  min(d) as minD, min(e) as minE, min(f) as minF,
  max(d) as maxD, max(e) as maxE, max(f) as maxF,
  sum(d) as sumD, sum(e) as sumE, sum(f) as sumF,
  stddev(d) as stddevD, stddev(e) as stddevE, stddev(f) as stddevF,
  variance(d) as varianceD, variance(e) as varianceE, variance(f) as varianceF
FROM cube1
GROUP BY a, txt;

update statistics for table mv1 on every column;

-- setup test for soln 10-100430-9921
create table y1 (
          a int not null
        , b int not null
        , c int not null
        , d int
        , e int not null
        , primary key(a)
        )
        ;
        
        insert into y1 values
         (1,1,1,1,1)
        ,(2,1,2,2,1)
        ,(3,1,3,3,1)
        ,(4,1,4,2,1)
        ,(5,1,5,1,1)
        ;

        drop mv ymv1;
        create mv ymv1 
        --REFRESH ON REQUEST
        --INITIALIZED ON create
        REFRESH BY USER 
        INITIALIZE BY USER
        ENABLE QUERY REWRITE
        as
        select y1.b, y1.c, sum(y1.d) s1, count(y1.d) c1, sum(y1.d*y1.d) s2
        from  y1
        group by y1.b, y1.c
        ;

insert into ymv1
        select y1.b, y1.c, sum(y1.d) s1, count(y1.d) c1, sum(y1.d*y1.d) s2
        from  y1
        group by y1.b, y1.c;

?section igb_test1
-- should get mvqr plan
prepare xx from 
SELECT d.b, MIN(f.e) as "minE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, MIN(f.e) as "minE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, MAX(f.e) as "maxE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, MAX(f.e) as "maxE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, COUNT(*) as "countStar"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, COUNT(*) as "countStar"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, SUM(f.e) as "sumE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, SUM(f.e) as "sumE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, COUNT(f.e) as "countE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, COUNT(f.e) as "countE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, SUM(f.e)/COUNT(f.e) as "avgE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, SUM(f.e)/COUNT(f.e) as "avgE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, AVG(f.e) as "avgE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, AVG(f.e) as "avgE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, STDDEV(f.e) as "stddevE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, STDDEV(f.e) as "stddevE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

-- should get mvqr plan
prepare xx from 
SELECT d.b, VARIANCE(f.e) as "varE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;

-- verify against non-mvqr plan
control query default MVQR_REWRITE_LEVEL '0'; 
prepare xx from 
SELECT d.b, VARIANCE(f.e) as "varE"
FROM cube1 f INNER JOIN t0 d ON (f.a = d.a) WHERE d.b < 3
GROUP BY d.b;
execute checkPlan; --explain options 'f' xx;
execute xx;
control query default MVQR_REWRITE_LEVEL '4'; 

?section hcube_create
create schema cat.myhcube;
set schema cat.myhcube;

cqd pos 'OFF';
create table t0 (a int not null not droppable, b int, c int, primary key (a));
create table t1 (a int not null not droppable, b int, c int, primary key (a));
create table t2 (a int not null not droppable, b int, c int, primary key (a));
create table t3 (a int not null not droppable, b int, c int, primary key (a));
create table t4 (a int not null not droppable, b int, c int, primary key (a));
create table t5 (a int not null not droppable, b int, c int, primary key (a));
create table t6 (a int not null not droppable, b int, c int, primary key (a));
create table t7 (a int not null not droppable, b int, c int, primary key (a));
create table t8 (a int not null not droppable, b int, c int, primary key (a));
create table t9 (a int not null not droppable, b int, c int, primary key (a));
create table t10 (a int not null not droppable, b int, c int, primary key (a));

cqd pos 'LOCAL_NODE';
create table cube1
(a int not null not droppable, 
b int not null not droppable,
c int not null not droppable,
d int, e int, f int, txt char(100),
primary key (a,b,c))
location $$partition$$
HASH2 PARTITION BY (a,b)
  (
    ADD LOCATION $$partition2$$  EXTENT (8192, 8192)
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition3$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192)
  );

create table cube2
(a int not null not droppable, 
b int not null not droppable,
c int not null not droppable,
d int, e int, f int, txt char(100),
primary key (a,b,c))
location $$partition$$
HASH2 PARTITION BY (a,b)
  (
    ADD LOCATION $$partition2$$  EXTENT (8192, 8192)
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition3$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192)
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192)
  );
  
create table cube3
(a int not null not droppable, 
b int not null not droppable,
c int not null not droppable,
d int not null not droppable,
e int, f int,
primary key (a,b,c,d))
location $$partition$$ ATTRIBUTES EXTENT (8192, 8192)
HASH2 PARTITION BY (a,b)
  (
    ADD LOCATION $$partition2$$  EXTENT (8192, 8192) 
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition3$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192) 
  );
  
create table cube4
(a int not null not droppable, 
b int not null not droppable,
c int not null not droppable,
d int not null not droppable,
e int, f int,
primary key (a,b,c,d))
location $$partition$$ ATTRIBUTES EXTENT (8192, 8192)
HASH2 PARTITION BY (a,b,c)
  (
    ADD LOCATION $$partition2$$  EXTENT (8192, 8192) 
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition3$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition2$$ EXTENT (8192, 8192) 
  , ADD LOCATION $$partition1$$ EXTENT (8192, 8192) 
  );

?section hcube_populate
-- insert 10 rows into t0
insert into t0 values (0,0,0);
insert into t0 values (1,1,1);
insert into t0 values (2,2,2);
insert into t0 values (3,3,3);
insert into t0 values (4,4,4);
insert into t0 values (5,5,5);
insert into t0 values (6,6,6);
insert into t0 values (7,7,7);
insert into t0 values (8,8,8);
insert into t0 values (9,9,9);

-- t1,t2,t3,t4,t5 are 10 rows similar to t0
insert into t1 select * from t0;
insert into t2 select * from t0;
insert into t3 select * from t0;
insert into t4 select * from t0;
insert into t5 select * from t0;

-- t6, t7 are 100 rows
insert into t6 select t1.a+10*t2.a,t1.a,t2.a from t1,t2;
insert into t7 select t1.a+10*t2.a,t1.a,t2.a from t1,t2;

-- t8 is 1000 rows
insert into t8 select t6.a+100*t1.a,t6.a,t1.a from t1,t6;

-- t9 is 10000 rows
insert into t9 select t8.a+1000*t1.a,t8.a,t1.a from t1,t8;

-- t10 is 100000 rows
insert into t10 select t8.a+1000*t6.a,t8.a,t6.a from t6,t8;

-- cube1 is 100000 row (change t8.a < 100 if you want more rows)
insert into cube1 select t1.a, t6.a, t8.a, t1.a, t6.a, t8.a, 'some text'
from t1, t6, t8 where t8.a < 100;

?section hcube_indexes
--create index ix6b on t6(b);
--create index ix6c on t6(c);
--create index ix7b on t7(b);
--create index ix7c on t7(c);
--create index ix8b on t8(b);
--create index ix8c on t8(c);
--create index ix9b on t9(b);
--create index ix9c on t9(c);
--create index ix10b on t10(b);
--create index ix10c on t10(c);
create index ixcube1d on cube1(d);
create index ixcube1e on cube1(e);
create index ixcube1f on cube1(f);

?section hcube_stats
-- CREATE stat
update statistics for table t0 on every column;
--update statistics for table t1 on every column;
--update statistics for table t2 on every column;
--update statistics for table t3 on every column;
--update statistics for table t4 on every column;
--update statistics for table t5 on every column;
--update statistics for table t6 on every column;
--update statistics for table t7 on every column;
--update statistics for table t8 on every column;
--update statistics for table t9 on every column;
--update statistics for table t10 on every column;
update statistics for table cube1 on every column;

?section clean_up
drop schema cat.myhcube cascade;
    
