-- @@@ 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 @@@
----------------------------------------------
-- TESTDML16 (Cardinality)
-- Functionality: Showstats for Query command.
-- Expected Files: ETESTDML09.
-- History: Created on 04/26/2011
----------------------------------------------

log ATESTDML16 clear;

set schema cat.hcube;

control query default query_cache '0';

-------------------------------------
-- Scan test cases
-------------------------------------

showstats for query 
select a from t1;

showstats for query 
select * from t1;

showstats for query 
select b from t1;

-------------------------------------
-- Join test cases
-------------------------------------

-- 2-way joins
showstats for query
select t1.a, t3.b from t1, t3
where t1.a = t3.b;

showstats for query
select t1.a, t3.b from t1, t3
where t1.b = t3.a;

showstats for query
select * from t1, t3
where t1.b = t3.a;

-- 3-way joins
showstats for query
select t1.a, t3.b, t5.c from t1, t3, t5
where t1.a = t3.b
and   t3.a = t5.c;

showstats for query
select t1.a, t3.b, t5.c from t1, t3, t5
where t1.b = t3.a
and   t3.c = t5.a;

showstats for query
select * from t1, t3, t5
where t1.a = t3.b
and   t3.a = t5.c;
 
-------------------------------------
-- Group By test cases
-------------------------------------

showstats for query 
select a from t1
group by a;

showstats for query 
select a, count(*) from t1
group by a;

showstats for query 
select a, b from t1
group by a, b;

showstats for query 
select count(a) from t1;

showstats for query 
select count(*) from t1;

showstats for query
select t1.a from t1, t3
where t1.b = t3.a
group by t1.a;

-------------------------------------
-- Sub-query test cases
-------------------------------------

showstats for query 
select * from t1 where a = (select min(a) from t2);

showstats for query 
select (select a from t1), (select b from t2) from t1;

showstats for query 
select (select count(*) from t1), (select count(*) from t2) from t1;

showstats for query
select t5.a
from t5
where t5.b >= (select max (t4.a)
               from t4
               where t5.c = t4.b);

showstats for query
select *
from t4
where t4.b in (select t5.a
               from t5
               where t4.c = t5.b);

showstats for query
select t4.a
from t4
where exists (select *
              from t7
              where t4.b = t7.b);

showstats for query
select count(t4.a)
from t4
where not exists (select *
                  from t7
                  where t4.b = t7.b);

showstats for query
select *
from t8
where exists (select *
              from t9 where t8.b = t9.b);

log;
