-- Test: TEST001 (CompGeneral)
-- Functionality: It tests the push group by below join feature. 
-- Table created: t01emp, t01dept
-- Expected files: EXPECTED001 
-- Limitations:
-- Revision history:
--     (10/30/02) - Created  from TEST013 of fullstack.
--                  added showshape for some queries to ensure that 
--                  the group by is being pushed down
--     (05/13/06) - Changed call to display_explain to just
--                  explain and removed the '(s)' from the output which
--                  has been supressed.
--
-- @@@ 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 @@@

?section ddl
drop table t01emp;
drop table t01dept;
drop table t021;
drop table t022;
#ifMX
drop table t01sample;
drop table t02sample;
#ifMX

?section crdb
log LOG001 clear;

-- test aggregate-join transformations
create table t01emp(eno int not null ,
                 ename char(20),
                 dno int,
                 salary numeric(8,2),
                 primary key(eno) );
create table t01dept(dno int not null ,
                  dname char(20) not null,
                  dregion int not null,
                  primary key (dno) );
create table t021(eno int not null ,
                 ename char(20),
                 dno int,
                 salary numeric(8,2),
                 primary key(eno) );
create table t022(eno int ,
                  ename char(20) ,
                  dno int
                  );
#ifMX
create table t01sample (l_partkey int not null not droppable,
l_suppkey int not null not droppable,
l_orderkey int not null not droppable
 )
store by  (l_partkey, l_suppkey)
#ifndef SEABASE_REGRESS
location $$partition$$
range partition by (l_partkey,l_suppkey) 
(
add first key ( 200) location $$partition1$$,
add first key (400) location $$partition2$$)
#endif
;

create table t02sample (l_partkey int not null not droppable,
l_suppkey int not null not droppable,
l_orderkey int not null not droppable
 )
store by  (l_partkey desc, l_suppkey)
#ifndef SEABASE_REGRESS
location $$partition$$
range partition by (l_partkey,l_suppkey)
(
add first key ( 1200) location $$partition1$$,
add first key (400) location $$partition2$$)
#endif
;
#ifMX

--create unique index deptux on dept(dregion,dname);

?section dml
insert into t01emp values (1, 'Smith',  33, 2000);
insert into t01emp values (2, 'Jones',  33, 3000);
insert into t01emp values (3, 'Gray',   44, 4000);
insert into t01emp values (4, 'Miller', 55, 1000);

insert into t01dept values (33, 'Sales',      1),
                        (44, 'Marketing',  1),
                        (55, 'Production', 2);
insert into t021 values (1, 'Smith',  33, 2000);
insert into t021 values (2, 'Jones',  33, 3000);
insert into t021 values (3, 'Gray',   44, 4000);
insert into t021 values (4, 'Miller', 55, 1000);
insert into t021 values (5, 'Miller', null, null);
insert into t021 values (6, 'Miller', null, null);

insert into t022 values (1, 'Simith',   33),
                        (2, 'Jones',  33),
                        (3, 'Gray', 44),
                        (3, 'Gray', 44),
                        (4, 'Miller', null),
                        (6, 'Miller', null);
-- simulate a full outer join
select * from t01emp natural left  join t01dept
 union
select * from t01emp natural right join t01dept;

-- do a intersect
cqd mode_special_4 'on';
select eno, ename, dno from t021
 intersect
select eno, ename, dno from t022;

select eno, ename, dno from t021
 intersect  all
select eno, ename, dno from t022;

select eno, ename, dno from t021 where dno >10
 intersect
select eno, ename, dno from t022 where dno <50;

select * from  t021 
 intersect
select * from  t022;

select * from 
(
select eno, ename, dno from t021
 intersect
select eno, ename, dno from t022
) ;
select eno, ename, dno from t021 except select * from t022;
cqd mode_special_4 reset;
-- groupby can be pushed down
-- add showshape statement to ensure that the group by is
-- actually pushed down

-- set showshape on;

select dno,dname,sum(salary)
from t01emp natural join t01dept
group by dno,dname;

-- modify the statistics such that the pushed down groupby wins
select dno,dname,sum(salary)
from t01emp rows10000 natural join t01dept rows1000
group by dno,dname for browse access;

-- set showshape off;

-- exchange join and groupby and add count(*) > 0 clause to the groupby
select distinct t01dept.* from t01emp,t01dept for browse access;

-- won't push down, aggregate function references both tables
-- (later, add a rewrite rule for groupby that eliminates max(region))

-- set showshape on;

select dno,dname,sum(salary), max(dregion)
from t01emp rows10000 natural join t01dept rows1000
group by dno,dname for browse access;


-- can't push down, groupby column "eno + dregion" references both tables
-- add showshape to ensure that the group by is not pushed down

select "eno + dregion",dno,dname,sum(salary) as sal
from (select eno + dregion, dno, dname, salary
      from t01emp rows10000 natural join t01dept rows1000)
			 as t("eno + dregion",dno,dname,salary)
group by "eno + dregion",dno,dname for browse access;

-- can't push down, groupby columns don't cover t01dept.key

select dname,sum(salary), max(dregion)
from t01emp rows10000 natural join t01dept rows1000
group by dname for browse access;

-- set showshape off;

-- can't push down, but test a query with a high number of internally
-- produced rows and test a 10-way cross product in the optimizer
-- TBF: ex_sort_grby_tcb::work() Invalid state returned by child
?ignore
select count(*) from t01emp t1, t01emp t2, t01emp t3, t01emp t4, t01emp t5, t01emp t6,
                      t01emp t7, t01emp t8, t01emp t9, t01emp t10;
?ignore
-- 4**10 = 1048576

-- push grby down 5 times
-- Use force statement to ensure that the optimizer chooses to push
-- the group down as far as it can go
control query shape hj(hj(hj(hj(hj(hash_groupby(cut),cut),cut),cut),cut),cut);
select t1.ename,avg(t1.salary)
from t01emp t1, t01emp t2, t01emp t3, t01emp t4, t01emp t5, t01emp t6
group by t1.ename,t2.eno,t3.eno,t4.eno,t5.eno,t6.eno;
control query shape cut;

-- push groupby down multiple times
-- set showshape on;

select max(t1.ename), rows12e4.eno, rows13e4.eno,
       rows14e4.eno, rows15e4.eno, rows16e4.eno
from t01emp t1 join t01emp rows12e4 on t1.eno <> rows12e4.eno
            join t01emp rows13e4 on rows12e4.eno = rows13e4.eno
            join t01emp rows14e4 on rows13e4.eno = rows14e4.eno
            join t01emp rows15e4 on rows14e4.eno = rows15e4.eno
            join t01emp rows16e4 on rows15e4.eno = rows16e4.eno
group by rows12e4.eno,rows13e4.eno,rows14e4.eno,rows15e4.eno,rows16e4.eno
for browse access;

-- set showshape off;

-- can push down, join col "dummy" is covered by the grouping columns
-- TBF: Normalizer: VEGPredicate is generated on T01EMP scan which is
-- not covered by the scan and therefore fails in file scan impl. rule.
control query shape join(hash_groupby(cut),cut);
select dno,dname,sum(salary)
from (select 1,salary from t01emp rows10000) as e(dummy,salary),t01dept rows1000
where e.dummy = rows1000.dno
group by dno,dname,dummy;
control query shape cut;

#ifMX
control query default ATTEMPT_ASYNCHRONOUS_ACCESS 'OFF';
control query default ATTEMPT_REVERSE_SYNCHRONOUS_ORDER 'ON'; 
--control query default detailed_statistics 'all';
control query shape without enforcers shortcut_groupby(scan);

#ifdef SEABASE_REGRESS
prepare xxy from select min(l_partkey) from t01sample;
#else
prepare xxy from select max(l_partkey) from t01sample;
#endif


explain options 'f' xxy;

--- should fail
prepare xxz from
select max(l_suppkey) from t01sample;

control query shape cut;
insert into t01sample values (100,5,230), (200,5, 24), (400, 10, 45);

-- should return 400
execute xxy;

---select VAL1_TXT, VAL1, VAL2_TXT, VAL2,VAL3_TXT, VAL3 from
---table(statistics(NULL,'XXY')) where TDB_NAME = 'EX_DP2_SUBS_OPER';

delete from t01sample where l_suppkey=10;

-- should return 200
execute xxy;

insert into t02sample values (100,5,230), (200,5, 24), (400, 10, 45), (1300,23, 67),(999, 34, 12);

#ifdef SEABASE_REGRESS
control query shape shortcut_groupby(scan);
#else
control query shape shortcut_groupby(partition_access(scan));
#endif

set param ?a 5;

select min(l_partkey) from t02sample where l_suppkey=?a or l_suppkey > 12;
select min(l_partkey) from t02sample where l_orderkey < 100;

-- verify fix to genesis case 10-090520-0144 soln 10-090520-1731
control query shape cut;
-- min(x) or max(x) where x is 1st cluster key should get
-- shortcut groupby plan out-of-the-box
explain options 'f' select max(l_partkey) from t01sample;
explain options 'f' select min(l_partkey) from t01sample;
explain options 'f' select * from t02sample where l_partkey >=
  (select max(l_partkey) from t01sample);
explain options 'f' select * from t02sample where l_partkey <=
  (select min(l_partkey) from t01sample);

-- The wave fix should continue to hold
explain options 'f' select count(*) from t01sample;

#ifndef SEABASE_REGRESS
-- let's force a nested join and verify that a
-- nested_join(cut, shortcutgroupby) plan is very expensive relative to
-- the out-of-the-box plan
control query shape nested_join(cut,shortcut_groupby(cut));
prepare x1 from select * from t02sample where l_partkey >=
  (select max(l_partkey) from t01sample);

control query shape cut;
prepare xoob from select * from t02sample where l_partkey >=
  (select max(l_partkey) from t01sample);

select 
--s.total_cost nj_scgb_cost, o.total_cost oob_cost, s.total_cost/o.total_cost,
case when s.total_cost/o.total_cost > 1 then 'PASS' else 'FAIL' end
from table(explain(null, 'X1')) s, 
     table(explain(null, 'XOOB')) o
where s.operator = 'ROOT' 
  and o.operator = 'ROOT' ;
#endif

-- do it again for min
control query shape nested_join(cut,shortcut_groupby(cut));
prepare n1 from select * from t02sample where l_partkey <=
  (select min(l_partkey) from t01sample);

control query shape cut;
prepare noob from select * from t02sample where l_partkey <=
  (select min(l_partkey) from t01sample);

select 
--s.total_cost nj_scgb_cost, o.total_cost oob_cost, s.total_cost/o.total_cost,
case when s.total_cost/o.total_cost > 1 then 'PASS' else 'FAIL' end
from table(explain(null, 'N1')) s, 
     table(explain(null, 'NOOB')) o
where s.operator = 'ROOT' 
  and o.operator = 'ROOT' ;

-- add explain plans to make nested_join(c,scgb) tests easier to understand
-- but if these plans start causing false negatives, nuke them from test001
#ifndef SEABASE_REGRESS
explain options 'f' x1;
explain options 'f' xoob;
#endif
explain options 'f' n1;
explain options 'f' noob;

-- forcing shortcut groupby plan that can produce wrong result should fail
control query shape shortcut_groupby(cut);
explain options 'f' select max(l_suppkey) from t01sample;
explain options 'f' select min(l_suppkey) from t01sample;
control query shape cut;


-- 
-- Code Coverage additional tests
-- 
-- test comp_int_74 used in TSJFlowRule::topMatch() in TransRule.cpp
--

log off;

drop table t01;
drop table s01;

log LOG001;

create table t01(a int not null, b int, c int);
create table s01(a int not null, b int, c int);

cqd comp_int_74 '1';

-- Test the condition where child(0) of TSJ is a scan.
prepare x from delete from t01 where t01.a = 1;

-- Test the condition where child(0) of TSJ is a join (used to cause an 
-- exception because child(0) is assumed to be a scan. 
prepare x from delete from t01 where t01.a in (select a from s01);

drop table t01 cascade;
drop table s01 cascade;

--
-- test FileScanRule::nextSubstitute() on condition
--
-- else if(	OptDefaults::indexEliminationLevel() != OptDefaults::MINIMUM AND
--  	NOT isStream AND
-- 	bef->selectionPred().isEmpty() AND viableIndexes.entries() >1)

create table t01(a int not null, b int, c int, primary key(a));
create index i1t01 on t01(a, b);
create index i2t01 on t01(b);

create table s01(a int not null, b int, c int, primary key(a));

prepare x from select s.a from s01 s, (select t.b from t01 t group by t.b) x(u);

--
-- test CQD OPTIMIZER_HEURISTIC_3 'on'
--

cqd OPTIMIZER_HEURISTIC_3 'on';
cqd query_cache '0';
prepare x from delete  from t01 where t01.a in (select a from s01);
cqd OPTIMIZER_HEURISTIC_3 reset;

--
-- test function OPHuseEnforcerPlanPromotion().
--
cqd query_cache '0';
cqd COMP_BOOL_160 'ON';
cqd MEMORY_USAGE_SAFETY_NET '1';
cqd OPH_USE_ENFORCER_PLAN_PROMOTION 'on';

prepare x from select * from
t01 t  <<+ cardinality 10e14 >> ,
t01 t1 <<+ cardinality 10e15 >> ,
t01 t2 <<+ cardinality 10e14 >> ,
t01 t3 <<+ cardinality 10e15 >> ,
t01 t4 <<+ cardinality 10e14 >> ,
t01 t5 <<+ cardinality 10e15 >>
where
t.a  = t1.b and
t1.a = t2.b and
t2.a = t3.b and
t3.a = t4.b and
t4.a = t5.b;

cqd COMP_BOOL_160 reset;
cqd MEMORY_USAGE_SAFETY_NET reset;
cqd OPH_USE_ENFORCER_PLAN_PROMOTION reset;

--
-- test PartitioningFunction::useNodeMapFromReqOrChild()
--

cqd comp_bool_82 'on';

prepare x from select * from
t01 t  <<+ cardinality 10e14 >> ,
t01 t1 <<+ cardinality 10e15 >> ,
t01 t2 <<+ cardinality 10e14 >> ,
t01 t3 <<+ cardinality 10e15 >> ,
t01 t4 <<+ cardinality 10e14 >> ,
t01 t5 <<+ cardinality 10e15 >>
where
t.a  = t1.b and
t1.a = t2.b and
t2.a = t3.b and
t3.a = t4.b and
t4.a = t5.b;

cqd comp_bool_87 'on';

prepare x from select * from
t01 t  <<+ cardinality 10e14 >> ,
t01 t1 <<+ cardinality 10e15 >> ,
t01 t2 <<+ cardinality 10e14 >> ,
t01 t3 <<+ cardinality 10e15 >> ,
t01 t4 <<+ cardinality 10e14 >> ,
t01 t5 <<+ cardinality 10e15 >>
where
t.a  = t1.b and
t1.a = t2.b and
t2.a = t3.b and
t3.a = t4.b and
t4.a = t5.b;

cqd comp_bool_82 reset;
cqd comp_bool_87 reset;



#ifMX

?section clnup
drop table t01emp;
drop table t01dept;
#ifMX
drop table t01sample;
drop table t02sample;
#ifMX
log;
