-- @@@ 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 @@@
-- opttest02
--
-- Tests negative partitioning keys, etc.
--
-- Phil Koza, Mike Skarpelos
--
--


?section droptemp
drop table $$p0$$;
drop table $$p1$$;
drop table $$t2$$;
drop table $$t3$$;

?section crtemp
-- First key of first partition is a negative value
create table $$p0$$ (
  sInt16_10    smallint     signed,
  uInt16_10    smallint     unsigned,
  sInt32_100   integer      signed,
  uInt32_100   integer      unsigned,
  uInt32_50p   integer      unsigned,
  sInt32_50p   integer      signed,
  uInt32_uniq  integer      unsigned  not null ,
  sInt32_uniq  integer      signed    not null ,
  int64_100    largeint,
  int64_uniq   largeint               not null ,
  char_10      char(9),
  char_100     char(8),
  char_50p     char(1780),
  char_uniq    char(8)                not null ,
  uNum_10      numeric(9,2) unsigned,
  sNum_100     numeric(9,2) signed,
  uNum_50p     numeric(9,2) unsigned,
  sNum_uniq    numeric(9,2) signed    not null ,
  date_12      date,
  date_200     date,
  date_uniq    date                   not null ,
  varchar_100  varchar(16),
  varchar_uniq varchar(50)            not null , 

#ifMX
primary key (sInt32_uniq) not droppable)
store by primary key
location $$part2$$
partition ( 
   add first key (-200) location $$part3$$,
   add first key (400) location $$part4$$)
attribute buffered, audit
#ifMX

#ifMP
primary key (sInt32_uniq))
partition (
  $$part3$$.PTABt0 first key (-200),
  $$part4$$.PTABt0 first key (400))
buffered audit
#ifMP
;

-- First key of first partition is a negative value
create table $$p1$$ (
  sInt16_10    smallint     signed,
  uInt16_10    smallint     unsigned,
  char_10      char(9),
  sInt32_100   integer      signed,
  char_100     char(8),
  uInt32_100   integer      unsigned,
  char_50p     char(1780),
  uInt32_50p   integer      unsigned,
  sInt32_50p   integer      signed,
  char_uniq    char(9)                not null ,
  uInt32_uniq  integer      unsigned  not null ,
  sInt32_uniq  integer      signed    not null ,
  int64_100    largeint,
  int64_uniq   largeint               not null ,
  uNum_10      numeric(9,2) unsigned,
  sNum_100     numeric(9,2) signed,
  uNum_50p     numeric(9,2) unsigned,
  sNum_uniq    numeric(9,2) signed    not null ,
  date_12      date,
  date_200     date,
  date_uniq    date                   not null ,
  varchar_100  varchar(16),
  varchar_uniq varchar(50)            not null , 

#ifMX
primary key (int64_uniq) not droppable)
store by primary key
location $$part2$$
partition ( 
   add first key (-200) location $$part3$$,
   add first key (400) location $$part1$$)
attribute buffered, audit
#ifMX

#ifMP
primary key (int64_uniq))
partition (
  $$part3$$.PTABt1 first key (-200),
  $$part1$$.PTABt1 first key (400))
#ifMP
;

-- Part the same as PTAB10, only difference is this table
-- has a varchar key instead of a char key.

create table $$t2$$ (
  char_10      char(9),
  char_100     char(8),
  char_50p     char(1780),
  char_uniq    char(9)                not null ,
  sInt16_10    smallint     signed,
  uInt16_10    smallint     unsigned,
  sInt32_100   integer      signed,
  uInt32_100   integer      unsigned,
  uInt32_50p   integer      unsigned,
  sInt32_50p   integer      signed,
  uInt32_uniq  integer      unsigned  not null ,
  sInt32_uniq  integer      signed    not null ,
  int64_100    largeint,
  int64_uniq   largeint               not null ,
  uNum_10      numeric(9,2) unsigned,
  sNum_100     numeric(9,2) signed,
  uNum_50p     numeric(9,2) unsigned,
  sNum_uniq    numeric(9,2) signed    not null ,
  date_12      date,
  date_200     date,
  date_uniq    date                   not null ,
  varchar_100  varchar(16),
  varchar_uniq varchar(50)            not null ,

#ifMX
primary key (varchar_uniq) not droppable)
store by primary key
location $$part2$$
partition (   
   add first key ('IAAAAAAA') location $$part3$$,
   add first key ('QAAAAAAA') location $$part1$$)
attribute buffered, audit
#ifMX

#ifMP
primary key (varchar_uniq) )
partition (
  $$part3$$.PTABt2 first key ('IAAAAAAA'),
  $$part1$$.PTABt2 first key ('QAAAAAAA'))
buffered audit
#ifMP
;

-- Not part the same as PTAB10, and this table
-- has a varchar key instead of a char key.
create table $$t3$$ (
  char_10      char(9),
  char_100     char(8),
  char_50p     char(1780),
  char_uniq    char(9)                not null ,
  sInt16_10    smallint     signed,
  uInt16_10    smallint     unsigned,
  sInt32_100   integer      signed,
  uInt32_100   integer      unsigned,
  uInt32_50p   integer      unsigned,
  sInt32_50p   integer      signed,
  uInt32_uniq  integer      unsigned  not null ,
  sInt32_uniq  integer      signed    not null ,
  int64_100    largeint,
  int64_uniq   largeint               not null ,
  uNum_10      numeric(9,2) unsigned,
  sNum_100     numeric(9,2) signed,
  uNum_50p     numeric(9,2) unsigned,
  sNum_uniq    numeric(9,2) signed    not null ,
  date_12      date,
  date_200     date,
  date_uniq    date                   not null ,
  varchar_100  varchar(16),
  varchar_uniq varchar(50)            not null , 

#ifMX
primary key (varchar_uniq) not droppable)
store by primary key
location $$part2$$
partition ( 
   add first key ('IAAAAAAA') location $$part3$$,
   add first key ('PAAAAAAA') location $$part1$$)
attribute buffered, audit
#ifMX

#ifMP
primary key (varchar_uniq) )
partition (
  $$part3$$.PTABt3 first key ('IAAAAAAA'),
  $$part1$$.PTABt3 first key ('PAAAAAAA'))
buffered audit
#ifMP
;



?section cpara
control query default ATTEMPT_ESP_PARALLELISM 'ON';
control query default DEF_NUM_LOCAL_SMP_CPUS 
#ifMX
'4'
#ifMX
#ifMP
'1'
#ifMP
;
control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER 
#ifMX
'1'
#ifMX
#ifMP
'4'
#ifMP
;

----------------
-- HASH JOINS
----------------

?section qhj1
-- SQL/MP style PLAN1 parallel two-way join plan using hash join
-- both tables are partitioned 3 ways, but part keys do not match
-- Will have to use logical partitioning.
-- Numeric key columns.
----------------------------------------------------------------
--control query shape exchange(hybrid_hash_join(cut,cut));
control query shape exchange(hybrid_hash_join(
  exchange(scan('T03')),exchange(scan('T04')),plan1));
prepare P from
select  T03.sInt32_uniq,T04.uInt32_uniq, T04.sInt32_100,
        substring(T04.char_50p from 1 for 8)  
  from  $$P03$$ T03, $$P04$$ T04
  where     T03.sInt32_uniq = T04.uInt32_uniq
        and T03.sInt32_100 < 5;

execute P;

?section qhj2
-- SQL/MP style PLAN1 parallel two-way join plan using hash join
-- both tables are partitioned 3 ways, and part keys match exactly,
-- but are of different types.
-- Char and Varchar key columns.
----------------------------------------------------------------
control query shape
 exchange(hybrid_hash_join(
            exchange(scan('T10')),
            exchange(scan('Tt2')),
            plan1
                          )
         );
prepare P from
select T10.char_uniq,Tt2.varchar_uniq, Tt2.sInt32_100,
       substring(T10.char_50p from 1 for 8)  
  from $$P10$$ T10, $$t2$$ Tt2
  where    T10.char_uniq = Tt2.varchar_uniq
       and T10.sInt32_100 < 5;

execute P;

?section qhj3
-- SQL/MP style PLAN1 parallel two-way join plan using hash join
-- both tables are partitioned 3 ways, but part keys do not match
-- Will have to use logical partitioning.
-- Char and Varchar key columns.
----------------------------------------------------------------
control query shape
 exchange(hybrid_hash_join(
            exchange(scan('T10')),
            exchange(scan('Tt3')),
            plan1
                          )
         );
prepare P from
select T10.char_uniq,Tt3.varchar_uniq, Tt3.sInt32_100,
       substring(T10.char_50p from 1 for 8)  
  from $$P10$$ T10, $$t3$$ Tt3
  where    T10.char_uniq = Tt3.varchar_uniq
       and T10.sInt32_100 < 5;

execute P;

?section qhj4
-- SQL/MP style PLAN1 parallel two-way join plan using hash join
-- both tables are partitioned 3 ways, and part keys match exactly,
-- but are of different types.
-- Numeric key columns.
-- First key of first partition is a negative value
----------------------------------------------------------------
--control query shape exchange(hybrid_hash_join(cut,cut));
control query shape exchange(hybrid_hash_join(
  exchange(scan('Tt0')),exchange(scan('Tt1')),plan1));
prepare P from
select  Tt0.sInt32_uniq,Tt1.int64_uniq, Tt0.sInt32_100,
        substring(Tt0.char_50p from 1 for 8)  
  from  $$p0$$ Tt0, $$p1$$  Tt1
  where     Tt0.sInt32_uniq = Tt1.int64_uniq
        and Tt0.sInt32_100 < 5;

execute P;

?section qhj5
-- SQL/MP style PLAN1 parallel two-way join plan using hash join
-- both tables are partitioned 3 ways, but part keys do not match
-- Will have to use logical partitioning.
-- Numeric key columns.
-- First key of first partition of one table is a negative value
----------------------------------------------------------------
--control query shape exchange(hybrid_hash_join(cut,cut));
control query shape exchange(hybrid_hash_join(
  exchange(scan('Tt0')),exchange(scan('T01')),plan1));
prepare P from
select  Tt0.sInt32_uniq,T01.int64_uniq, Tt0.sInt32_100,
        substring(Tt0.char_50p from 1 for 8)  
  from  $$p0$$ Tt0, $$P01$$  T01
  where     Tt0.sInt32_uniq = T01.uInt32_uniq
        and Tt0.sInt32_100 < 5;

execute P;



