-- @@@ 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 @@@
-- TESTDML15 
-- Functionality: Tests Missing single column and multi-column stats warnings
-- Expected File: ETESTDML15
-- History: Created 12/10/2010
-- Owner: Renu Varshneya
-------------------------------------------------------------------

drop schema cat.missingwarn cascade;

log ATESTDML15 clear;

cqd HIST_MISSING_STATS_WARNING_LEVEL '4';

cqd HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION 'off';

cqd HIST_ROWCOUNT_REQUIRING_STATS '1';

CQD POS 'OFF';

create schema cat.missingwarn;

set schema cat.missingwarn;

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));

create table cube2 like cube1;

create table cube3 like cube1;

create table cube4 like cube1;

insert into cube1 (select * from cat.hcube.cube1);

insert into cube2 (select * from cube1 where a > 10 or b < 5);

insert into cube3 (select * from cube2 where a = 5 or b = 3 or c = 2);

insert into cube4 (select * from cube1 where d = 5 and e > 6);

update statistics for table cube1 on a,b,c,d,e,f,txt;

update statistics for table cube2 on a,b,c,d,e,f,txt;

update statistics for table cube3 on a,b,c,d,e,f,txt;

update statistics for table cube4 on a,b,c,d,e,f,txt;

-- Q1
prepare xx from select * from cube1 where e = 5 and f = 3;

-- Q2
prepare xx from select * from cube1 where e = 5 and f = 3 and b = 4;

-- Q3
prepare xx from select * from cube1 where e = 5 and f = 3 and b > 4 and a < 3;

-- Q4
prepare xx from select * from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.d;

-- Q5
prepare xx from select * from cube1, cube2, cube3, cube4 where 
cube1.a = cube2.a and
cube1.b = cube3.b and
cube1.d = cube4.d;

-- Q6
prepare xx from select * from cube1, cube2, cube3, cube4 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.c = cube3.c and
cube1.d = cube4.d;

-- Q7
prepare xx from select count(*) from cube1 where e = 5 and f = 3
group by a,b;

-- Q8
prepare xx from select count(*) from cube1 where e = 5 and f = 3 and b = 4
group by e,f,b,a;

-- Q9
prepare xx from select count(*) from cube1 where e = 5 and f = 3 and b > 4 and a < 3
group by a,b,e,f;

-- Q10
prepare xx from select cube1.a, cube1.b, cube1.d from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.d
group by cube1.a, cube1.b, cube1.d;

-- Q11
prepare xx from select cube1.a, cube2.a, cube1.b, cube2.b from cube1, cube2
group by cube1.a, cube2.a, cube1.b, cube2.b;

-- Q12
prepare xx from select cube1.a, cube1.b, cube1.d from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.d
group by cube1.a, cube1.b, cube1.c, cube1.d having
cube1.a =5 and cube1.c = 4;

-- Q13
prepare xx from select cube1.a, cube2.a, cube1.b, cube2.b from cube1, cube2
group by cube1.a, cube2.a, cube1.b, cube2.b having cube1.a = 5 and cube1.b = 4;

-- Q14
prepare xx from select cube1.a, cube2.a, cube1.b, cube2.b from cube1, cube2
group by cube1.a, cube2.a, cube1.b, cube2.b 
having cube1.a = 5 and cube1.b = 4 or
cube2.a = 4 and cube2.b = 5;

-- Q15
prepare xx from select cube1.a, cube1.b, cube1.c from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.c and
cube1.f = cube2.f
group by cube1.a, cube1.b, cube1.c;

-- Q16
update statistics for table cube2 on (a,b,c);
-- cube2 (a,b,c) is unique hence should not give warning for cube2(a,b,c,f)
prepare xx from select cube1.a, cube1.b, cube1.c from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.c and
cube1.f = cube2.f
group by cube1.a, cube1.b, cube1.c;

-- Q17
update statistics for table cube1 on (a,b,d);
-- cube1 (a,b,d) is not almost unique so there should be no change in the warnings
-- from previous query
prepare xx from select cube1.a, cube1.b, cube1.c from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.c and
cube1.f = cube2.f
group by cube1.a, cube1.b, cube1.c;

-- Q18
-- clear cube1 (a,b,d) and generate statistics for cube1 (a,b,f)
-- since cube1 (a,b,f) are almost unique hence should not get a warning for 
-- cube1 (a,b,d,f)
update statistics for table cube1 on (a,b,d) clear;
update statistics for table cube1 on (a,b,f);
prepare xx from select cube1.a, cube1.b, cube1.c from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.c and
cube1.f = cube2.f
group by cube1.a, cube1.b, cube1.c;

-- Q19
prepare xx from select cube1.a, cube1.b, cube1.c from cube1, cube2 where 
cube1.a = cube2.a and
cube1.b = cube2.b and
cube1.d = cube2.c and
cube1.e = cube2.e and
cube1.f = cube2.f 
group by cube1.a, cube1.b, cube1.c;

-- Q20
prepare xx from
select * from cube1, cube2, cube3, cube4 where
cube1.a = cube2.a and
cube1.a = cube3.a and
cube1.b = cube2.b and
cube1.b = cube3.b and
cube1.c = cube2.c and
cube1.c = cube3.c and
cube1.c = cube4.c;

-- Q21
-- Should get warning only for group by
prepare xx from
select cube1.a, cube1.b, cube1.c, cube2.a, cube3.b, cube4.c 
from cube1, cube2, cube3, cube4 where
cube1.a = cube2.a and
cube1.b = cube3.b and
cube1.c = cube4.c group by
cube1.a, cube1.b, cube1.c, cube2.a, cube3.b, cube4.c;

-- Q22
-- update statistics for subset of columns and retry.
-- If the subsets do not form a unique index, the warning 
-- should not go away
update statistics for table cube1 on (a,b), (b,c);
prepare xx from
select cube1.a, cube1.b, cube1.c, cube2.a, cube3.b, cube4.c 
from cube1, cube2, cube3, cube4 where
cube1.a = cube2.a and
cube1.b = cube3.b and
cube1.c = cube4.c group by
cube1.a, cube1.b, cube1.c, cube2.a, cube3.b, cube4.c;

-- Q23
-- add statistics for table cube1 (a,c). No impact on warnings
update statistics for table cube1 on (a,c);
prepare xx from
select cube1.a, cube1.b, cube1.c, cube2.a, cube3.b, cube4.c 
from cube1, cube2, cube3, cube4 where
cube1.a = cube2.a and
cube1.b = cube3.b and
cube1.c = cube4.c group by
cube1.a, cube1.b, cube1.c, cube2.a, cube3.b, cube4.c;

-- use another table and try to incrementally create statistics to see the
-- impact on warnings
-- Q24
prepare xx from select * from cube4
where e =3 and f = 4 and c = 5 and b = 5;

-- Q25
-- update statistics for (b,c) - should have no effect on warnings
update statistics for table cube4 on (b,c);
prepare xx from select * from cube4
where e =3 and f = 4 and c = 5 and b = 5;
update statistics for table cube4 on (b,c) clear;

-- Q26
-- update statistics for table (c,f). Since stats for (b,e) and (c,f)
-- exist and their product is equal to row count, do not issue a warning
-- for (a,b,c,f)
update statistics for table cube4 on (b,e);
update statistics for table cube4 on (c,f);
prepare xx from select * from cube4
where e =3 and f = 4 and c = 5 and b = 5;

drop schema cat.missingwarn cascade;

log;

exit;

