-- @@@ 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 @@@
>>obey opttest02(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
+>;

--- SQL operation complete.
>>
>>-- 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
+>;

--- SQL operation complete.
>>
>>-- 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
+>;

--- SQL operation complete.
>>
>>-- 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
+>;

--- SQL operation complete.
>>
>>
>>
>>obey opttest02(cpara);
>>control query default ATTEMPT_ESP_PARALLELISM 'ON';

--- SQL operation complete.
>>control query default DEF_NUM_LOCAL_SMP_CPUS 
+>#ifMX
+>'4'
+>#ifMX
+>#ifMP
+>'1'
+>#ifMP
+>;

--- SQL operation complete.
>>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER 
+>#ifMX
+>'1'
+>#ifMX
+>#ifMP
+>'4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>----------------
>>-- HASH JOINS
>>----------------
>>
>>obey opttest02(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));

--- SQL operation complete.
>>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;

--- SQL command prepared.
>>
>>execute P;

SINT32_UNIQ  UINT32_UNIQ  SINT32_100   (EXPR)  
-----------  -----------  -----------  --------

        304          304            3  AUAAAAAA
         32           32            3  JAAAAAAA
        316          316            4  DWAAAAAA
         77           77            3  JAAAAAAA
        326          326            2  JQAAAAAA
         96           96            1  KYAAAAAA
        330          330            2  KLAAAAAA
        105          105            1  JGAAAAAA
        348          348            0  FKAAAAAA
        110          110            3  AVAAAAAA
        370          370            2  CGAAAAAA
        117          117            1  ACAAAAAA
        423          423            3  JUAAAAAA
        136          136            4  CXAAAAAA
        435          435            3  GUAAAAAA
        139          139            1  HUAAAAAA
        440          440            1  GTAAAAAA
        463          463            4  FJAAAAAA
        500          500            1  ECAAAAAA
        503          503            0  GFAAAAAA
        514          514            0  HEAAAAAA
        520          520            2  IOAAAAAA
        543          543            2  ENAAAAAA
        548          548            4  HXAAAAAA
        167          167            0  ARAAAAAA
        200          200            0  BRAAAAAA
        203          203            4  LTAAAAAA
        232          232            0  CTAAAAAA
        276          276            4  DOAAAAAA
        295          295            2  EQAAAAAA

--- 30 row(s) selected.
>>
>>obey opttest02(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
+>                          )
+>         );

--- SQL operation complete.
>>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;

--- SQL command prepared.
>>
>>execute P;

--- 0 row(s) selected.
>>
>>obey opttest02(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
+>                          )
+>         );

--- SQL operation complete.
>>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;

--- SQL command prepared.
>>
>>execute P;

--- 0 row(s) selected.
>>
>>obey opttest02(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));

--- SQL operation complete.
>>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;

--- SQL command prepared.
>>
>>execute P;

--- 0 row(s) selected.
>>
>>obey opttest02(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));

--- SQL operation complete.
>>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;

--- SQL command prepared.
>>
>>execute P;

--- 0 row(s) selected.
>>
>>
>>
>>obey opttest02(droptemp);
>>drop table $$p0$$;

--- SQL operation complete.
>>drop table $$p1$$;

--- SQL operation complete.
>>drop table $$t2$$;

--- SQL operation complete.
>>drop table $$t3$$;

--- SQL operation complete.
>>
>>log;
