-- @@@ 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 @@@
----------------------------------------------
-- TESTDML17 (Cardinality)
-- Functionality: Selectivity hints.
-- Expected Files: ETESTDML17.
-- History: Created on 12/12/12
----------------------------------------------

log ATESTDML17 clear;

set schema cat.hcube;

control query default query_cache '0';

----------------------------------------------------------------------------------
-- Single table using local predicates
----------------------------------------------------------------------------------

-- Comparison predicates
prepare xx from select * from t10 where b = 3 <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where (b = 3 and c = 2) <<+ selectivity 10e-2 >> ; 
explain options 'f' xx;
prepare xx from select * from t10 where b = c and c = 2 <<+ selectivity 10e-2 >> ;
explain options 'f' xx;
prepare xx from select * from t10 where b > 10 <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where b  <= 123 <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where b = (select b from t10) <<+ selectivity 10e-2 >>;
explain options 'f' xx;

-- Null predicates
prepare xx from select * from t10 where b is null <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where b is not null <<+ selectivity 10e-2 >>;
explain options 'f' xx;

-- Between predicates 
prepare xx from select * from t10 where c between 2 and 10 <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where A * c between 2 and 10 <<+ selectivity 10e-2 >>;
explain options 'f' xx;

-- In predicates
prepare xx from select * from t10 where b  in (1) <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where b  in (1,2,3) <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where b  in (select b from t10) <<+ selectivity 10e-2 >>;
explain options 'f' xx;

-- Quantified comparison predicates
prepare xx from select * from t10 where b = any(select b from t10) <<+ selectivity 10e-2 >>; 
explain options 'f' xx;
prepare xx from select * from t10 where (b  <> any(select b from t10)) <<+ selectivity 10e-2 >>; 
explain options 'f' xx;
prepare xx from select * from t10 where a > all(select b from t10) <<+ selectivity 10e-2 >>; 
explain options 'f' xx;
prepare xx from select * from t10 where (b  < all(select b from t10)) <<+ selectivity 10e-2 >>; 
explain options 'f' xx;
prepare xx from select * from t10 where b  <= some(select b from t10) <<+ selectivity 10e-2 >>; 
explain options 'f' xx;
prepare xx from select * from t10 where (a >= some(select b from t10)) <<+ selectivity 10e-2 >>; 
explain options 'f' xx;

-- BiLogic predicates
prepare xx from select * from t10 where a = 3 or b = 2 <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where (a = 3 or b = 2) <<+ selectivity 10e-2 >>;
explain options 'f' xx;

-- Like predicates
prepare xx from select * from cube2 where txt like 'some text' <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from cube2 where txt like 'blue%' <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from cube2 where txt not like 'some%text' <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from cube2 where txt like '%lue' <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from cube2 where txt not like 'blue sky%' <<+ selectivity 10e-2 >>;
explain options 'f' xx;


----------------------------------------------------------------------------------
-- Join predicate 
----------------------------------------------------------------------------------

prepare xx from select * from t10 r1, t10 r2 where r1.c = r2.b  <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 where b  in (select b from t10) <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2 where r1.c = r2.b  <<+ selectivity 10e-2 >> and r1.b = 3; 
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2 where r1.c = r2.a and r1.b = 3 <<+ selectivity 10e-2 >>;
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2 where r1.c = r2.b  <<+ selectivity 10e-2 >> and r1.b = 3 <<+ selectivity 10e-3 >>;
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2, t10 r3 where r1.c = r2.b  <<+ selectivity 10e-2 >> and r1.b = r3.a and r1.b = 3; 
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2, t10 r3 where r1.c = r2.b  <<+ selectivity 10e-2 >> and r1.b = r3.a;
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2, t10 r3 where r1.c = r2.a and r1.b = r3.a  <<+ selectivity 10e-3 >>;
explain options 'f' xx;
prepare xx from select * from t10 r1, t10 r2, t10 r3 where r1.c = r2.b  <<+ selectivity 10e-2 >> and r1.b = r3.a  <<+ selectivity 10e-3 >> ;
explain options 'f' xx;

log;

exit;


