-- @@@ 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 @@@
-- run the individual queries first
obey TEST001(setcqds);
obey TEST001(updatestats);
obey TEST001(query1);
obey TEST001(query2);
obey TEST001(query3);
obey TEST001(query4);
obey TEST001(query5);
obey TEST001(query6);
obey TEST001(query7);
obey TEST001(query8);
--obey TEST001(query9);
obey TEST001(restores);

-- run results are in query?.log files

exit;

-- obey cidefs;

------------------------------------------
-- controls
------------------------------------------
-- control query default PARALLEL_EXECUTION 'OFF';
-- control query default DEF_NUM_LOCAL_SMP_CPUS '1';

-- control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER 
-- ?ifMX
-- '1'
-- ?ifMX
-- ?ifNSKRel1
-- '4'
-- ?ifNSKRel1
-- ;

?section setcqds

control query default QUERY_CACHE '0';
control query default DEF_PHYSICAL_MEMORY_AVAILABLE '128000000';
control query default DEF_VIRTUAL_MEMORY_AVAILABLE '1000000000';

?section updatestats
update statistics for table lineitem clear;
update statistics for table lineitem on l_shipdate, l_linestatus,
l_orderkey, l_returnflag sample;
update statistics for table orders clear;
update statistics for table orders on o_orderkey, o_orderdate, o_shippriority
sample;

----------------------------------------------------------------
--------------                                     -------------
------                 START TESTS                          ----
-----------                                      ---------------
----------------------------------------------------------------


?section query1
----------------------------------------------------------------
-- QUERY 1 - scan
--
-- produces 0 rows
-- WHERE clause applied on non-key column with ~50% selectivity
----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log scan_plan.log clear;
prepare q1 from
select [last 0] * from lineitem where l_shipdate <= date '1995-07-01';

display_explain options 'f' q1;
log;

log scan.qlog clear;
execute q1;
display statistics;
log;


?section query2
----------------------------------------------------------------
-- QUERY 2 - scan + aggr + hash_groupby (modified tpcd query 1)
--
-- produces 0 rows
----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;
-- control query shape hash_groupby(partition_access(scan('T1')));

control query default detailed_statistics 'ALL';
log hash_groupby_plan.log clear;
PREPARE q2 FROM
SELECT [last 0] l_returnflag, l_linestatus, SUM(l_quantity),
       CAST(SUM(l_extendedprice) AS NUMERIC(18,2)),
       CAST(SUM(l_extendedprice * (1-l_discount)) AS NUMERIC(18,2)),
       CAST(SUM(l_extendedprice * (1-l_discount) * (1 + l_tax))
            AS NUMERIC(18,2)),
       AVG(l_quantity),
       AVG(l_extendedprice),
       AVG(CAST(l_discount AS NUMERIC (10,3))),
       AVG(l_discount),
       COUNT(*)
FROM lineitem
GROUP BY l_returnflag, l_linestatus;

display_explain options 'f' q2;
log;

log hash_groupby.qlog clear;
execute q2;
display statistics;
log;


?section query3
-----------------------------------------------------------------
-- QUERY 3 - scan + aggr + sort_groupby + hash_groupby + sort
--
-- produces 0 rows
-----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log sort_groupby_plan.log clear;
-- control query shape sort_groupby(sort(partition_access(scan('T1'))));

prepare q3 from
select [last 0] l_linestatus, count(l_returnflag)
from lineitem
group by l_linestatus order by l_linestatus;

display_explain options 'f' q3;
log;

log sort_groupby.qlog clear;
execute q3;
display statistics;
log;


?section query4
----------------------------------------------------------------
-- QUERY 4 - hash join
--
-- produces 0 rows
-- joining on primary key columns
----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log hash_join_plan.log clear;
-- control query shape hybrid_hash_join(partition_access(
-- scan('T1')),partition_access(scan('T2')));

prepare q4 from
select [last 0] l_orderkey, o_orderdate
from orders, lineitem
where l_quantity = o_orderkey;

display_explain options 'f' q4;
log;

log hash_join.qlog clear;
execute q4;
display statistics;
log;


?section query5
-----------------------------------------------------------------
-- QUERY 5 - nested join & materialize
--
-- produces 0 rows
-- joining on non-key columns
-----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log nested_join_plan.log clear;
-- control query shape nested_join(partition_access(
-- scan('T2')), materialize(partition_access(scan('T1' ))));

prepare q5 from
select [last 0] l_orderkey, o_orderdate
from orders, lineitem
where l_orderkey = o_orderkey;

display_explain options 'f' q5;
log;

log nested_join.qlog clear;
execute q5;
display statistics;
log;


?section query6
-----------------------------------------------------------------
-- QUERY 6 - merge join
--
-- produces 0 rows 
-- joining on primary key columns
-----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log merge_join_plan.log clear;
-- control query shape merge_join(partition_access(
-- scan('T1')),partition_access(scan('T2')));

control query shape merge_join(cut, cut);

prepare q6 from
select [last 0] l_orderkey, o_orderdate
from orders, lineitem
where l_orderkey = o_orderkey ;

display_explain options 'f' q6;
log;

control query shape anything;

log merge_join.qlog clear;
execute q6;
display statistics;
log;


?section query7
-----------------------------------------------------------------
-- QUERY 7 - union all
-- 
-- produces 0 rows
-----------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log union_plan.log clear;
control query shape union(cut,cut);

prepare q7 from
select [last 0] * from (
  select l_orderkey from lineitem union all 
  select o_orderkey from orders
) x(a);

display_explain options 'f' q7;
log;

control query shape anything;

log union.qlog clear;
execute q7;
display statistics;
log;


?section query8
------------------------------------------------------------------
-- QUERY 8 - sort
--
------------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log sort_plan.log clear;
-- control query shape sort(nested_join(partition_access(
-- scan('T2')), materialize(partition_access(scan('T1')))));

prepare q8 from 
select [last 0] l_returnflag, l_linestatus, o_shippriority
from lineitem, orders
where l_orderkey = o_orderkey
order by l_returnflag, l_linestatus, o_shippriority;

display_explain options 'f' q8;
log;

log sort.qlog clear;
execute q8;
display statistics;
log;


?section query9
------------------------------------------------------------------
-- QUERY 9 - granddaddy query
--
------------------------------------------------------------------

-- clear disk cache
sh clear_cache.ksh;

control query default detailed_statistics 'ALL';
log query9_plan.log clear;
-- 
--control query shape hash_groupby(merge_join(sort(hybrid_hash_join(
--partition_access(scan('T1')),nested_join(partition_access(
--scan('T2')), materialize(partition_access(scan('T1')))))),partition_access(
--scan('T2'))));
--
prepare q9 from 
select [last 0] l_returnflag, l_linestatus, 
       sum(l_quantity), cast(sum(l_extendedprice) as numeric(18,2)), 
       avg(l_discount), count(*) 
from lineitem, orders
where o_orderkey = l_orderkey and 
      l_comment in (select l_comment from lineitem, orders where
                   l_orderkey = o_orderkey)
group by l_returnflag, l_linestatus for serializable access;

display_explain options 'f' q9;
log;

log query9.log clear;
execute q9;
display statistics;
log;


?section restores
------------------------------------------------------------------
-- Restore to original settings
--
------------------------------------------------------------------
sh clear_cache.ksh;
