-- @@@ 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 @@@
--======================================================--
-- test verifies:
-- 1) optimizer chooses parallel insert-select plan for PH query
--======================================================--

?section explainIt
--  Prepared query for displaying chosen plan using EXPLAIN. --
prepare explainIt from
  select [first 4] operator, op_cost
  from (select
         substring(cast(SEQ_NUM+100 as char(3)),2,2),
         substring(operator,1,16),        
         cast(case when operator_cost = 0.0 then 'ZERO'
                   when operator_cost < 0.0 then 'NEGATIVE'
                   else 'POSITIVE' end as char(8))
         from table (explain(NULL,'XX'))
       ) as t(s, operator, op_cost)
  order by s desc;

?section setSCH
set schema cat.sch;

drop table mylineitem;
Create table mylineitem  (
   l_orderkey          int                not null not droppable, 
   l_partkey           int                not null not droppable, 
   l_suppkey           int                not null not droppable, 
   l_linenumber        int                not null not droppable, 
   l_quantity          numeric(12,2)      not null not droppable, 
   l_extendedprice     numeric(12,2)      not null not droppable, 
primary key (l_orderkey,l_linenumber)  not droppable) 
location $$partition1$$
hash2 partition by (l_orderkey)
(
   add location  $$partition2$$ ,
   add location  $$partition3$$ ,
   add location  $$partition4$$ ,
   add location  $$partition5$$ ,
   add location  $$partition6$$ ,
   add location  $$partition7$$ ,
   add location  $$partition8$$ 
)
store by primary key 
;

drop table duptable;
Create table duptable  (
   l_orderkey          int                not null not droppable, 
   l_partkey           int                not null not droppable, 
   l_suppkey           int                not null not droppable, 
   l_linenumber        int                not null not droppable, 
   l_quantity          numeric(12,2)      not null not droppable, 
   l_extendedprice     numeric(12,2)      not null not droppable, 
primary key (l_orderkey,l_linenumber)  not droppable) 
location $$partition1$$
hash2 partition by (l_orderkey)
(
   add location  $$partition2$$ ,
   add location  $$partition3$$ ,
   add location  $$partition4$$ ,
   add location  $$partition5$$ ,
   add location  $$partition6$$ ,
   add location  $$partition7$$ ,
   add location  $$partition8$$ 
)
store by primary key 
;

insert into Mylineitem
  select   
   0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (10 * x10) + (1 * x1),
   0 + (1 * x1),
   0
  from (values(1)) as starter  
    transpose 0,1,2,3,4,5,6,7,8,9 as x10000
    transpose 0,1,2,3,4,5,6,7,8,9 as x1000
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;
update statistics for table mylineitem on every column sample 1000 rows;

?section startLog
--  Start logging output. --
LOG aoptdml05 clear;

?section cdefs
-- Control query defaults 
control query default DEF_NUM_SMP_CPUS '4';

?section tests
prepare xx from 
insert into duptable (select * from mylineitem);
execute explainIt;
-- should get a parallel insert select plan with positive costs
-- for all operators except root


?section stopLog
--  Stop logging output. --
LOG;
