-- Test: TEST020 (Core)
-- @@@ 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 @@@
--
-- Functionality: Test the Explain Function
-- Expected files: EXPECTED020, EXPECTED020.MP
-- Tables created: TAB1, TAB2, TAB3
-- Limitations:
-- To do: - Test description field of EXPLAIN
-- Revision history:
-- (06/08/06) Added a few additional tests for problems found with
--            new explain syntax.
-- (10/05/02) Move test to core from fullstack2, without any change.    
-- (02/22/99) Added CQS's and order by's to make the test insensitive
--            to plan changes
-- (05/28/97) Clean up.

?section ddb
drop table TAB1;
drop table TAB2;
drop table TAB3;

?section cdb
log LOG020 clear;

create table TAB1 (col1 int, col2 int, col3 int);
create table TAB2 (col1 int, col2 int, col3 int);
create table TAB3 (col1 int, tname char(60), sname char(60));

insert into TAB1 values (1, 2, 3),
                        (2, 3, 4),
                        (3, 4, 5),
                        (4, 5, 6),
                        (5, 6, 7),
                        (6, 7, 8),
                        (7, 8, 9),
                        (8, 9, 0),
                        (9, 0, 1),
                        (0, 1, 2);

insert into TAB2 values (1, 2, 3),
                        (4, 5, 6),
                        (7, 8, 9),
                        (0, 1, 2),
                        (3, 4, 5),
                        (6, 7, 8),
                        (9, 0, 1),
                        (2, 3, 4),
                        (5, 6, 7);

insert into TAB3 values (1, 'TAB1', 'S1'),
                        (2, 'TAB2', 'S2'),
                        (3, 'TAB3', 'S3');


?section prepare_queries

-- QUERYS to be explained
-- Every query to be explained has a CQS in order to make the
-- test insensitive to plan changes.

#ifndef SEABASE_REGRESS
control query shape hybrid_hash_join(partition_access(
scan(path 'TAB1', forward, mdam off)),partition_access(
scan(path 'TAB2', forward, mdam off)));
#else
control query shape hybrid_hash_join(
scan(path 'TAB1', forward, mdam off),
scan(path 'TAB2', forward, mdam off));
#endif

prepare S1 from 
select * 
from TAB1 , TAB2
where TAB1.col1 = TAB2.col1
  and TAB1.col1 < 15
  and TAB2.col2 < 20
;

#ifndef SEABASE_REGRESS
control query shape hybrid_hash_join(partition_access(
scan(path 'TAB1', forward, mdam off)),partition_access(
scan(path 'TAB3', forward, mdam off)));
#else
control query shape hybrid_hash_join(
scan(path 'TAB1', forward, mdam off),
scan(path 'TAB3', forward, mdam off));
#endif

prepare S2 from 
select * 
from TAB1 , TAB3
where TAB1.col1 = TAB3.col1
  and TAB1.col2 < 10
  and TAB3.col1 < 15
  and TAB3.tname is not null
  and TAB3.sname is null
  and (TAB1.col2 + TAB1.col3 < TAB3.col1 or char_length(TAB3.tname) > 10)
  and substring(TAB3.tname from 1 for 3) <> 'ABC'
;

#ifndef SEABASE_REGRESS
control query shape hybrid_hash_join(partition_access(
scan(path 'TAB3', forward, mdam off)),partition_access(
scan(path 'TAB2', forward, mdam off)));
#else
control query shape hybrid_hash_join(
scan(path 'TAB3', forward, mdam off),
scan(path 'TAB2', forward, mdam off));
#endif

prepare S3 from 
select * 
from TAB2 , TAB3
where TAB2.col1 = TAB3.col1
  and TAB2.col2 < 10
  and TAB3.col1 < 10
;

#ifndef SEABASE_REGRESS
control query shape sort_groupby(nested_join(sort(partition_access(
scan(path 'TAB1', forward, mdam off))),
partition_access(scan(path 'TAB2', forward, mdam off))));
#else
control query shape sort_groupby(nested_join(sort(
scan(path 'TAB1', forward, mdam off)),
scan(path 'TAB2', forward, mdam off)));
#endif

prepare S4 from 
select TAB1.col1, TAB1.col2, sum(TAB2.col2), count(*)
from TAB1 , TAB2
where TAB1.col1 = TAB2.col1
  and TAB2.col2 < 30
group by TAB1.col1, TAB1.col2
order by TAB1.col2
;

#ifndef SEABASE_REGRESS
control query shape partition_access(insert);
#else
control query shape insert;
#endif

prepare S5 from
insert into TAB1
 values (42,42,42)
;

#ifndef SEABASE_REGRESS
control query shape nested_join(anything,partition_access(insert));
#else
control query shape nested_join(anything,insert);
#endif

prepare S6 from
insert into TAB1
 values (42,42,42),
	(52,52,52),
	(1,2,3)
;

#ifndef SEABASE_REGRESS
control query shape partition_access(update);
#else
control query shape update;
#endif

prepare S7 from
update TAB1
 set col1 = 12,
     col2 = col1 + col2,
     col3 = 12 * 4
where col1 < 10
;

#ifndef SEABASE_REGRESS
control query shape hybrid_hash_join(partition_access(
scan(path 'TAB1', forward, mdam off)),partition_access(
scan(path 'TAB2', forward, mdam off)));
#else
control query shape hybrid_hash_join(
scan(path 'TAB1', forward, mdam off),
scan(path 'TAB2', forward, mdam off));
#endif

prepare S8 from
select * from TAB1
where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < 10)
;

#ifndef SEABASE_REGRESS
control query shape hybrid_hash_join(partition_access(
scan(path 'TAB1', forward, mdam off)),partition_access(
scan(path 'TAB2', forward, mdam off)));
#else
control query shape hybrid_hash_join(
scan(path 'TAB1', forward, mdam off),
scan(path 'TAB2', forward, mdam off));
#endif

prepare S9 from
select * from TAB1
where TAB1.col1 in (select col2 from TAB2 where TAB2.col1 < TAB1.col2)
;

-- remove shape:
control query shape cut;

?section explain_queries

-- QUERY 1 - A simple Explain query with a predicate
select 
#ifMX
       tname,
#ifMX
#ifMP
       substring(tname, 1, 4),
#ifMP
       operator
from table (explain(NULL,'S1'))
where tname LIKE '%TAB1%'
order by
operator
;

?ignore
-- this query returns different values for cost on different
-- platforms with different releases.
-- It is not a good query for regressions.
-- QUERY 2 - The cost fields of the explain table.
select
       operator,
       operator_cost,
       total_cost,
       detail_cost
from table (explain(NULL,'S1'))
order by
operator
;
?ignore

-- QUERY 3 - The explain table is used in a JOIN.
select 
      operator,
      expp.tname
from table (explain (NULL, 'S1')) as expp, TAB3
where substring(expp.tname from position('TAB' in expp.tname) for 4) = TAB3.tname
order by
1,2--operator
;

-- QUERY 4 - The explain table is used in a JOIN.
select 
       operator,
       seq_num
from table (explain (NULL, 'S1')) as expp, TAB1
where expp.seq_num = TAB1.col1
order by
operator,seq_num
;

-- QUERY 5 - The explain function is used within a correlated
--           subquery.
select 
       *
from TAB3
where TAB3.tname in
   (select substring(tname from position('TAB' in tname) for 4)
    from table (explain (NULL,TAB3.sname)))
order by tname
;

-- QUERY 6 - Wildcarding is used in the explain parameters.
select 
       module_name,
       statement_name
from table (explain (NULL, 'S%'))
order by
module_name
,statement_name
;

--QUERY 7 - Wildcarding is used in the explain parameters.
select 
       operator
from table (explain (NULL, 'S%')) as expp
where expp.left_child_seq_num is not null 
and   expp.right_child_seq_num is not null
order by operator
;

-- QUERY 8

select seq_num,
	left_child_seq_num,
	operator,
	right_child_seq_num
from table (explain(NULL, 'S%'))
order by
seq_num
,left_child_seq_num
,operator
,right_child_seq_num
;

--QUERY 9
select 
       operator
from table (explain(NULL,'S1'))
order by
operator
;

--QUERY 10
select 
       operator
from table (explain(NULL,'S2'))
order by
operator
;

--QUERY 11
select
       operator
from table (explain(NULL,'S3'))
order by
operator
;

--QUERY 12
select
       operator
from table (explain(NULL,'S4'))
order by
operator
;

--QUERY 13
select
       operator
from table (explain(NULL,'S5'))
order by
operator
;

--QUERY 14
select
       operator
from table (explain(NULL,'S6'))
order by
operator
;

--QUERY 15
select
       operator
from table (explain(NULL,'S7'))
order by
operator
;

--QUERY 16
select
       operator
from table (explain(NULL,'S8'))
order by
operator
;

--QUERY 17
select
       operator
from table (explain(NULL,'S9'))
order by
operator
;

--QUERY 18
-- Tests description field
-- Currently commented out because query gives results that are not repeatable
-- (predicates printed contain "funny names" that change from run to run 
--  in their columns, probably the way to fix it is to change the "getText()"
--  method of the column item expr. 05/97)
-- select 	seq_num,
--	operator,
--	description
-- from table (explain(NULL, 'S%'))
-- ;

--QUERY 19
-- Solution 10-060524-6738. Make sure "explain" with access options
-- work.  This also turns off the fast_dp2_subset_opt option so the
-- plans are identical under Windows and NSK.  This is only tested
-- under MX because the output is easier to deal with, and this test
-- is mainly to ensure that the parser deals with the syntax correctly.
#ifMX
control query default fast_dp2_subset_opt 'off';
explain options 'f' select * from TAB1 where col1 < 15
 for serializable access;
#ifMX

--QUERY 20
-- Solution 10-060601-6896. This should cause a syntax error.
explain explain select * 
  from TAB1 , TAB2
  where TAB1.col1 = TAB2.col1
    and TAB1.col1 < 15
    and TAB2.col2 < 20
;

--QUERY 21
-- Solution 10-060606-7000. This should cause a syntax error.
explain;


obey TEST020(ddb);

log;
