-- @@@ 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 @@@
prepare checkPlan from
select operator, tname
from table(explain(NULL, 'XX'))
where operator like '%_SCAN%'
order by tname;

drop schema mvqr_sch008 cascade;
create schema mvqr_sch008;
set schema mvqr_sch008;
--drop table t1 cascade;
--drop table t2 cascade;
create table t1 (
  a int not null
, b int not null
, c int not null
, d int not null
, e int not null
, primary key(a)
)
;
create table t2 (
  a int not null
, b int
, c int
, d int
, e int
)
store by(a)
;

insert into t1 values(1,1,1,1,1),(2,1,15,2,2);
insert into t2 values(1,1,5,1,1),(1,1,5,1,1),(2,1,15,2,2),(2,1,15,2,2);

--drop mv mv1;
--drop mv mv2;

log LOG008 clear;
control query default MVQR_REWRITE_LEVEL '4';
control query default MVQR_REWRITE_ENABLED_OPTION 'ON';
control query default MVQR_PUBLISH_TO 'PRIVATE';
control query default REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT '1';
control query default MVQR_ALL_JBBS_IN_QD 'ON';
control query default multi_join_threshold '2';

create mv dmv1
REFRESH BY USER
INITIALIZE BY USER
ENABLE QUERY REWRITE
as
select t1.b b1
     , t2.b b2
     ,sum(distinct t1.c) s2
from t1, t2
where t1.a=t2.a
group by t1.b, t2.b;

-- initialize mv
insert into dmv1
select t1.b b1
           , t2.b b2
           ,sum(distinct t1.c) s2
      from t1, t2
      where t1.a=t2.a
      group by t1.b, t2.b
      ;


create mv mv1
REFRESH on request INITIALIZE on create ENABLE QUERY REWRITE as
--REFRESH BY USER INITIALIZE BY USER ENABLE QUERY REWRITE as
select t1.b b1, t2.b b2, t1.c c1, t2.c c2
from t1, t2
where t1.a=t2.a
;

create mv mv2
REFRESH on request INITIALIZE on create ENABLE QUERY REWRITE as
--REFRESH BY USER INITIALIZE BY USER ENABLE QUERY REWRITE as
select b1, b2, c1, c2
from mv1
where c1=1 and c2<20
;


-- verify fix for soln 10-100426-9686
prepare xx from 
select t1.b b1
           , t2.b b2
           ,sum(distinct t1.c) s2
      from t1, t2
      where t1.a=t2.a
      group by t1.b, t2.b
      ;
execute checkPlan;
execute xx;

cqd query_text_cache 'on';
cqd query_template_cache 'on';

cqd query_cache '0';
cqd query_cache reset;

cqd mvqr_rewrite_candidates 'CAT.MVQR_SCH008.MV2';

prepare xx from
select t1.b b1, t2.b b2, t1.c c1, t2.c c2
from t1, t2
where t1.a=t2.a and t1.c=1 and t2.c<20
;
execute checkPlan; --explain options 'f' xx;
-- should be an mvqr "w" plan

obey test008(show_cache_entries);
-- should have no cache entries

execute xx;

-- should not hit any cache entry
prepare xx from
select t1.b b1, t2.b b2, t1.c c1, t2.c c2
from t1, t2
where t1.a=t2.a and t1.c=1 and t2.c<20
;

execute checkPlan; --explain options 'f' xx;
-- should be an mvqr "w" plan

obey test008(show_cache_entries);
-- should have no cache entries

execute xx;

-- used to hit cache and RETURNS WRONG RESULT !!!!!!!
prepare xx from
select t1.b b1, t2.b b2, t1.c c1, t2.c c2
from t1, t2
where t1.a=t2.a and t1.c=15 and t2.c<20
;

execute checkPlan; --explain options 'f' xx;
-- should be an mvqr "w" plan

obey test008(show_cache_entries);
-- should have no cache entries

execute xx;
-- should return correct results

-- any mv plan should not be cacheable
prepare xx from
select t1.b b1, t2.b b2, t1.c c1, t2.c c2
from t1, t2
where t1.a=t2.a
order by 1,2,3,4
;

execute checkPlan; --explain options 'f' xx;
-- should be an mvqr plan
obey test008(show_cache_entries);
-- shoule be cached
execute xx;

-- should not be compiled as a cache hit
prepare xx from
select t1.b b1, t2.b b2, t1.c c1, t2.c c2
from t1, t2
where t1.a=t2.a
order by 1,2,3,4
;
obey test008(show_cache_entries);
-- shoule show a cache hit

log;

drop schema mvqr_sch008 cascade;

?section show_cache_entries
select num_hits hits, phase, cast(substring(text,1,70) as char(70)) query_text
from table(querycacheentries());
