-- @@@ 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 opttest01;
>>-- opttest01
>>--
>>-- Phil Koza, Mike Skarpelos
>>--
>>-- Parallel execution, using the mini-Wisconsin style DB
>>-- Prepares, explains, and executes the queries
>>--
>>
>>
>>
>>?section cpara
>>control query default PARALLEL_EXECUTION '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.
>>
>>
>>control query default NESTED_JOIN_CONTROL 'OFF';

--- SQL operation complete.
>>
>>set define ATTEMPT_ESP_PARALLELISM 1;
>>
>>control query default DATA_FLOW_OPTIMIZATION 'OFF';

--- SQL operation complete.
>>control query default PARALLEL_NUM_ESPS '2';

--- SQL operation complete.
>>------------
>>-- select --
>>------------
>>
>>?section qse1
>>-- simple parallel select with a PA node
>>----------------------------------------
>>control query shape exchange(split_top_pa(anything));

--- SQL operation complete.
>>prepare P from
+>select uInt32_uniq,sInt32_uniq,char_uniq
+>  from     $$P03$$ T03
+>  where    uInt32_uniq < 20
+>  order by sInt32_uniq;

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

UINT32_UNIQ  SINT32_UNIQ  CHAR_UNIQ
-----------  -----------  ---------

         17           42  CVAAAAAA 
         14           89  WEAAAAAA 
         16           95  AUAAAAAA 
          8          107  PWAAAAAA 
         15          209  XNAAAAAA 
          7          269  KKAAAAAA 
         10          278  MBAAAAAA 
          3          336  RAAAAAAA 
         18          339  TXAAAAAA 
          1          398  VLAAAAAA 
         13          436  TTAAAAAA 
         19          445  NXAAAAAA 
         11          458  BWAAAAAA 
          0          459  PQAAAAAA 
          6          493  EQAAAAAA 
          9          508  ACAAAAAA 
          5          526  AEAAAAAA 
         12          561  QWAAAAAA 
          4          575  DIAAAAAA 
          2          589  DWAAAAAA 

--- 20 row(s) selected.
>>
>>?section qse2
>>-- simple parallel select with PAPA node
>>----------------------------------------
>>--control query shape exchange(anything);
>>prepare P from
+>select  sInt32_uniq,uInt32_uniq,char_uniq
+>  from  $$P04$$ T04 
+>  where sInt32_uniq < 20;

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

SINT32_UNIQ  UINT32_UNIQ  CHAR_UNIQ
-----------  -----------  ---------

          1          453  UEAAAAAA 
         13          491  UDAAAAAA 
         19          500  RTAAAAAA 
         11          513  IUAAAAAA 
          0          514  KDAAAAAA 
          6          548  DYAAAAAA 
          9          563  WPAAAAAA 
          5          581  CUAAAAAA 
         12           16  HAAAAAAA 
          4           30  BFAAAAAA 
          2           44  TWAAAAAA 
         17           97  LHAAAAAA 
         14          144  KSAAAAAA 
         16          150  FBAAAAAA 
          8          162  CJAAAAAA 
         15          264  FHAAAAAA 
          7          324  VNAAAAAA 
         10          333  JFAAAAAA 
          3          391  ECAAAAAA 
         18          394  LUAAAAAA 

--- 20 row(s) selected.
>>
>>--<pb>
>>-------------------
>>-- hash group by --
>>-------------------
>>
>>-- Full hash GB in DP2 is not allowed by the HashGroupByRule::topMatch
>>-- method, due to the possibility of overflow and DP2's inability
>>-- to handle it. So, no tests for full hash GB in DP2.
>>
>>?section qhgb1
>>-- Partial grouping in DP2 with final consolidation in the master
>>-- Synchronous reads
>>-------------------------------------------------------------------
>>control query shape hash_groupby(partition_access(hash_groupby(scan)));

--- SQL operation complete.
>>prepare P from
+>select distinct int64_6,uInt16_10
+>  from  $$P09$$ T09
+>  where     int64_6 IN (0,2,4,5)
+>        and uInt16_10 < 5;

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

INT64_6               UINT16_10
--------------------  ---------

                   0          0
                   0          1
                   0          2
                   0          3
                   0          4
                   2          0
                   2          1
                   2          2
                   2          3
                   2          4
                   4          0
                   4          1
                   4          2
                   4          3
                   4          4
                   5          0
                   5          1
                   5          2
                   5          3
                   5          4

--- 20 row(s) selected.
>>
>>?section qhgb2
>>-- Partial grouping in DP2 with final consolidation in the master
>>-- Parallel reads
>>-------------------------------------------------------------------
>>control query shape hash_groupby(split_top_pa(hash_groupby(scan)));

--- SQL operation complete.
>>prepare P from
+>select distinct int64_6,uInt16_10
+>  from  $$P09$$ T09
+>  where     int64_6 IN (0,2,4,5)
+>        and uInt16_10 < 5;

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

INT64_6               UINT16_10
--------------------  ---------

                   0          0
                   2          0
                   4          0
                   0          1
                   2          1
                   4          1
                   0          2
                   2          2
                   4          2
                   0          3
                   2          3
                   4          3
                   0          4
                   2          4
                   4          4
                   5          0
                   5          1
                   5          2
                   5          3
                   5          4

--- 20 row(s) selected.
>>
>>?section qhgb3
>>-- repartitioned groupby from table with 3 parts
>>---------------------------------------------------
>>control query shape exchange(hash_groupby(
+>  exchange(exchange(scan))));

--- SQL operation complete.
>>prepare P from
+>select uInt16_10
+>  from     $$P00$$ T00
+>  group by uInt16_10;

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

UINT16_10
---------

        9
        8
        1
        6
        7
        4
        3
        0
        5
        2

--- 10 row(s) selected.
>>
>>?section qhgb4
>>-- repartitioned groupby from table with 4 parts
>>---------------------------------------------------
>>control query shape exchange(hash_groupby(
+>  exchange(exchange(scan))));

--- SQL operation complete.
>>prepare P from
+>select uInt16_10
+>  from     $$P03$$ T03
+>  group by uInt16_10;

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

UINT16_10
---------

        7
        6
        1
        8
        9
        4
        3
        0
        5
        2

--- 10 row(s) selected.
>>
>>?section qhgb5
>>-- Logical partition grouping
>>-- 2 cpus and table has 3 partitions, so this is an unbalanced plan
>>-----------------------------------------------
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '2'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '2'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>control query shape exchange(hash_groupby(exchange(scan)));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60,sInt16_10
+>  from     $$P05$$
+>  group by sInt32_60,sInt16_10
+>  having       sInt32_60 IN (0,15,30,45)
+>           AND sInt16_10 < 5;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

SINT32_60    SINT16_10
-----------  ---------

          0          1
         15          2
         15          0
         15          1
         45          2
         45          4
         45          3
         30          4
         30          2
         30          0
         30          3
         30          1

--- 12 row(s) selected.
>>
>>?section qhgb6
>>-- Logical partition grouping
>>-- 2 cpus and table has 4 partitions, so this is a balanced plan
>>-----------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '2'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '2'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>control query shape exchange(hash_groupby(exchange(scan)));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60,sInt16_10
+>  from     $$P14$$
+>  group by sInt32_60,sInt16_10
+>  having       sInt32_60 IN (0,15,30,45)
+>           AND sInt16_10 < 5;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

SINT32_60    SINT16_10
-----------  ---------

         45          2
          0          1
         45          4
         15          2
         45          3
         15          0
         30          4
         15          1
         30          2
         30          0
         30          3
         30          1

--- 12 row(s) selected.
>>
>>?section qhgb7
>>-- repartitioned GB to get more parallelism
>>-- table is partitioned 3 ways, but there are 6 cpus
>>-----------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '6'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '6'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>-- control query shape exchange(hash_groupby(cut));
>>control query shape exchange(hash_groupby(
+>exchange(exchange(scan))));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60
+>  from     $$P05$$ T05
+>  group by sInt32_60;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

SINT32_60  
-----------

          0
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
         12
         13
         14
         15
         16
         17
         18
         19
         20
         21
         22
         23
         24
         25
         26
         27
         28
         29
         30
         31
         32
         33
         34
         35
         36
         37
         38
         39
         40
         41
         42
         43
         44
         45
         46
         47
         48
         49
         50
         51
         52
         53
         54
         55
         56
         57
         58
         59

--- 60 row(s) selected.
>>
>>?section qhgb8
>>-- repartitioned groupby on char and date columns
>>---------------------------------------------------
>>control query shape exchange(hash_groupby(
+>  exchange(exchange(scan))));

--- SQL operation complete.
>>prepare P from
+>select char_10,date_12
+>  from     $$P06$$ T06
+>  group by char_10, date_12
+>  having      char_10 like 'AB%'
+>           OR char_10 like 'AE%';

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

CHAR_10    DATE_12   
---------  ----------

AEAAAAAA   2100-01-12
AEAAAAAA   2100-01-03
ABAAAAAA   2100-01-12
AEAAAAAA   2100-01-09
ABAAAAAA   2100-01-11
ABAAAAAA   2100-01-04
AEAAAAAA   2100-01-04
AEAAAAAA   2100-01-01
ABAAAAAA   2100-01-09
AEAAAAAA   2100-01-10
ABAAAAAA   2100-01-01
ABAAAAAA   2100-01-06
ABAAAAAA   2100-01-08
AEAAAAAA   2100-01-08
ABAAAAAA   2100-01-05
AEAAAAAA   2100-01-05
ABAAAAAA   2100-01-10
AEAAAAAA   2100-01-06
ABAAAAAA   2100-01-03
AEAAAAAA   2100-01-07
ABAAAAAA   2100-01-07
AEAAAAAA   2100-01-02
ABAAAAAA   2100-01-02

--- 23 row(s) selected.
>>
>>--<pb>
>>-------------------
>>-- sort group by --
>>-------------------
>>
>>?section qsgb1 
>>-- Execute a sort groupby in DP2 on a table that has one partition 
>>------------------------------------------------------------------
>>control query shape partition_access(sort_groupby(scan));

--- SQL operation complete.
>>prepare P from
+>select count(*),min(sInt32_uniq),max(sInt32_uniq)
+>  from $$U00$$ T00;

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

(EXPR)                (EXPR)       (EXPR)     
--------------------  -----------  -----------

                 600            0          599

--- 1 row(s) selected.
>>
>>?section qsgb2 
>>-- Execute a sort groupby in DP2 on a table that has three partitions 
>>-- Grouping is on the partitioning key, so this should succeed
>>-- No sort should be needed.
>>-------------------------------------------------------------------
>>control query shape exchange(sort_groupby(scan));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60
+>  from     $$P05$$ T05
+>  group by sInt32_60;

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

SINT32_60  
-----------

          0
         20
          1
         21
          2
         22
          3
         23
          4
         24
          5
         25
          6
         26
          7
         27
          8
         28
          9
         29
         10
         30
         11
         31
         12
         32
         13
         33
         14
         34
         15
         35
         16
         36
         17
         37
         18
         38
         19
         39
         40
         41
         42
         43
         44
         45
         46
         47
         48
         49
         50
         51
         52
         53
         54
         55
         56
         57
         58
         59

--- 60 row(s) selected.
>>
>>?section qsgb3 
>>-- Partial grouping in DP2 with final consolidation in the master
>>-- Synchronous reads
>>-------------------------------------------------------------------
>>control query shape sort_groupby(partition_access(sort_groupby(scan)));

--- SQL operation complete.
>>prepare P from 
+>select int64_6,count(*)
+>  from $$P09$$
+>  where        int64_6 IN (0,2,4,5)
+>  group by int64_6;

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

INT64_6               (EXPR)              
--------------------  --------------------

                   0                   100
                   2                   100
                   4                   100
                   5                   100

--- 4 row(s) selected.
>>
>>?section qsgb4 
>>-- Partial grouping in DP2 with final consolidation in the master
>>-- parallel reads
>>-------------------------------------------------------------------
>>control query shape sort_groupby(sort(split_top_pa(sort_groupby(scan))));

--- SQL operation complete.
>>prepare P from 
+>select int64_6,count(*)
+>  from $$P09$$
+>  where        int64_6 IN (0,2,4,5)
+>  group by int64_6;

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

INT64_6               (EXPR)              
--------------------  --------------------

                   0                   100
                   2                   100
                   4                   100
                   5                   100

--- 4 row(s) selected.
>>
>>?section qsgb5 
>>-- repartitioned groupby from table with 3 parts
>>-- Sort is needed
>>---------------------------------------------------
>>control query shape exchange(sort_groupby(sort(
+>  exchange(exchange(scan)))));

--- SQL operation complete.
>>prepare P from
+>select uInt16_10
+>  from     $$P00$$ T00
+>  group by uInt16_10;

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

UINT16_10
---------

        1
        0
        3
        2
        5
        4
        7
        6
        9
        8

--- 10 row(s) selected.
>>
>>?section qsgb6 
>>-- repartitioned groupby from table with 4 parts
>>-- Sort is needed
>>---------------------------------------------------
>>control query shape exchange(sort_groupby(sort(
+>  exchange(exchange(scan)))));

--- SQL operation complete.
>>prepare P from
+>select uInt16_10
+>  from     $$P03$$ T03
+>  group by uInt16_10;

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

UINT16_10
---------

        1
        0
        3
        2
        5
        4
        7
        6
        9
        8

--- 10 row(s) selected.
>>
>>?section qsgb7 
>>-- Logical partition grouping
>>-- 2 cpus and table has 3 partitions, so this is an unbalanced plan
>>-- sort is needed because sInt16_10 is not a key column
>>-----------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '2'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '2'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>control query shape exchange(sort_groupby(sort(exchange(scan))));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60,sInt16_10
+>  from     $$P05$$
+>  group by sInt32_60,sInt16_10
+>  having       sInt32_60 IN (0,15,30,45)
+>           AND sInt16_10 < 5;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

SINT32_60    SINT16_10
-----------  ---------

         15          0
          0          1
         15          1
         15          2
         30          0
         30          1
         30          2
         45          2
         30          3
         45          3
         30          4
         45          4

--- 12 row(s) selected.
>>
>>?section qsgb8 
>>-- Logical partition grouping
>>-- 2 cpus and table has 4 partitions, so this is a balanced plan
>>-- sort is needed because sInt16_10 is not a key column
>>-----------------------------------------------
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '2'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '2'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>control query shape exchange(sort_groupby(sort(exchange(scan))));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60,sInt16_10
+>  from     $$P14$$
+>  group by sInt32_60,sInt16_10
+>  having       sInt32_60 IN (0,15,30,45)
+>           AND sInt16_10 < 5;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

SINT32_60    SINT16_10
-----------  ---------

         15          0
          0          1
         15          1
         15          2
         30          0
         30          1
         30          2
         45          2
         30          3
         45          3
         30          4
         45          4

--- 12 row(s) selected.
>>
>>?section qsgb9
>>-- repartitioned GB to get more parallelism
>>-- table is partitioned 3 ways, but there are 6 cpus
>>-- Sort is necessary, since a merge of sorted
>>-- streams of the repartitioned data cannot be done,
>>-- due to the risk of deadlock.
>>-----------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '6'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '6'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(sort_groupby(cut));
>>control query shape exchange(sort_groupby(
+>  sort(exchange(exchange(scan)))));

--- SQL operation complete.
>>prepare P from
+>select sInt32_60
+>  from     $$P05$$ T05
+>  group by sInt32_60;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

SINT32_60  
-----------

          0
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
         12
         13
         14
         15
         16
         17
         18
         19
         20
         21
         22
         23
         24
         25
         26
         27
         28
         29
         30
         31
         32
         33
         34
         35
         36
         37
         38
         39
         40
         41
         42
         43
         44
         45
         46
         47
         48
         49
         50
         51
         52
         53
         54
         55
         56
         57
         58
         59

--- 60 row(s) selected.
>>
>>?section qsgb10
>>-- repartitioned groupby on char and date columns
>>-- Sort is needed
>>---------------------------------------------------
>>control query shape exchange(sort_groupby(sort(
+>  exchange(exchange(scan)))));

--- SQL operation complete.
>>prepare P from
+>select char_10,date_12
+>  from     $$P06$$ T06
+>  group by char_10, date_12
+>  having   char_10 like 'A%';

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

CHAR_10    DATE_12   
---------  ----------

AAAAAAAA   2100-01-11
AAAAAAAA   2100-01-01
AAAAAAAA   2100-01-12
AAAAAAAA   2100-01-02
ABAAAAAA   2100-01-11
AAAAAAAA   2100-01-03
ABAAAAAA   2100-01-12
AAAAAAAA   2100-01-04
ACAAAAAA   2100-01-11
AAAAAAAA   2100-01-05
ACAAAAAA   2100-01-12
AAAAAAAA   2100-01-06
ADAAAAAA   2100-01-11
AAAAAAAA   2100-01-07
ADAAAAAA   2100-01-12
AAAAAAAA   2100-01-08
AEAAAAAA   2100-01-12
AAAAAAAA   2100-01-09
AAAAAAAA   2100-01-10
ABAAAAAA   2100-01-01
ABAAAAAA   2100-01-02
ABAAAAAA   2100-01-03
ABAAAAAA   2100-01-04
ABAAAAAA   2100-01-05
ABAAAAAA   2100-01-06
ABAAAAAA   2100-01-07
ABAAAAAA   2100-01-08
ABAAAAAA   2100-01-09
ABAAAAAA   2100-01-10
ACAAAAAA   2100-01-01
ACAAAAAA   2100-01-02
ACAAAAAA   2100-01-03
ACAAAAAA   2100-01-04
ACAAAAAA   2100-01-05
ACAAAAAA   2100-01-06
ACAAAAAA   2100-01-07
ACAAAAAA   2100-01-08
ACAAAAAA   2100-01-09
ACAAAAAA   2100-01-10
ADAAAAAA   2100-01-01
ADAAAAAA   2100-01-02
ADAAAAAA   2100-01-03
ADAAAAAA   2100-01-04
ADAAAAAA   2100-01-05
ADAAAAAA   2100-01-06
ADAAAAAA   2100-01-07
ADAAAAAA   2100-01-08
ADAAAAAA   2100-01-09
ADAAAAAA   2100-01-10
AEAAAAAA   2100-01-01
AEAAAAAA   2100-01-02
AEAAAAAA   2100-01-03
AEAAAAAA   2100-01-04
AEAAAAAA   2100-01-05
AEAAAAAA   2100-01-06
AEAAAAAA   2100-01-07
AEAAAAAA   2100-01-08
AEAAAAAA   2100-01-09
AEAAAAAA   2100-01-10

--- 59 row(s) selected.
>>
>>--<pb>
>>----------------
>>-- join plans --
>>----------------
>>
>>----------------
>>-- HASH JOINS
>>----------------
>>
>>?section qhj1
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join
>>-- both tables are partitioned 3 ways and part. keys match exactly
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T00')),exchange(scan('T01')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T00.sInt32_uniq,T01.uInt32_uniq, T01.sInt32_100,
+>        substring(T01.char_50p from 1 for 8)  
+>  from  $$P00$$ T00, $$P01$$ T01
+>  where     T00.sInt32_uniq = T01.uInt32_uniq
+>        and T01.sInt32_100 < 5;

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

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

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

--- 30 row(s) selected.
>>
>>?section qhj2
>>-- CPUS = 4
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join
>>-- tables first keys don't match but logical partitioning is possible,
>>-- since we are joining a 3-way partitioned table with a 4-way
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T01')),exchange(scan('T03')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T01.uInt32_uniq,T03.sInt32_uniq,T03.char_100
+>  from  $$P01$$ T01, $$P03$$ T03
+>  where     T01.uInt32_uniq = T03.sInt32_uniq
+>        and T03.char_100 <= 'ADAAAAAA';

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

UINT32_UNIQ  SINT32_UNIQ  CHAR_100
-----------  -----------  --------

         39           39  ADAAAAAA
         72           72  ADAAAAAA
         75           75  ADAAAAAA
         98           98  ABAAAAAA
        111          111  ACAAAAAA
        120          120  ABAAAAAA
        134          134  ABAAAAAA
        160          160  ACAAAAAA
        211          211  ACAAAAAA
        418          418  AAAAAAAA
        220          220  ABAAAAAA
        237          237  AAAAAAAA
        446          446  ABAAAAAA
        459          459  ABAAAAAA
        270          270  AAAAAAAA
        302          302  AAAAAAAA
        311          311  ADAAAAAA
        516          516  ACAAAAAA
        521          521  ADAAAAAA
        328          328  ACAAAAAA
        355          355  ACAAAAAA
        363          363  ADAAAAAA
        573          573  AAAAAAAA
        584          584  AAAAAAAA

--- 24 row(s) selected.
>>
>>?section qhj2b
>>-- CPUS = 3
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join
>>-- tables first keys don't match but logical partitioning is possible,
>>-- since we are joining a 3-way partitioned table with a 4-way
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>----------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '3'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '3'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T01')),exchange(scan('T03')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T01.uInt32_uniq,T03.sInt32_uniq,T03.char_100
+>  from  $$P01$$ T01, $$P03$$ T03
+>  where     T01.uInt32_uniq = T03.sInt32_uniq
+>        and T03.char_100 <= 'ADAAAAAA';

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

UINT32_UNIQ  SINT32_UNIQ  CHAR_100
-----------  -----------  --------

        211          211  ACAAAAAA
         39           39  ADAAAAAA
        418          418  AAAAAAAA
         72           72  ADAAAAAA
        220          220  ABAAAAAA
         75           75  ADAAAAAA
        237          237  AAAAAAAA
         98           98  ABAAAAAA
        446          446  ABAAAAAA
        111          111  ACAAAAAA
        459          459  ABAAAAAA
        120          120  ABAAAAAA
        270          270  AAAAAAAA
        134          134  ABAAAAAA
        302          302  AAAAAAAA
        160          160  ACAAAAAA
        311          311  ADAAAAAA
        516          516  ACAAAAAA
        521          521  ADAAAAAA
        328          328  ACAAAAAA
        355          355  ACAAAAAA
        363          363  ADAAAAAA
        573          573  AAAAAAAA
        584          584  AAAAAAAA

--- 24 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>?section qhj3
>>-- CPUS = 4
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join
>>-- tables first keys don't match but logical partitioning is possible,
>>-- since both tables are part 4 ways on the join columns, with 
>>-- only the partitioning boundaries (first keys) being different.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>--
>>-- With # of cpus = 3, should choose 3 ESP plan with logical partition
>>-- grouping on one side and logical subpartitioning on the other.
>>-- This should not lead to an inbalance, because the the final 
>>-- partition of the 4-way partitioned table is empty, and the
>>-- optimizer should be smart enough to group the empty partition
>>-- with the partition just before it - thus all 3 new partitions
>>-- will be the same size.
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T04')),exchange(scan('T03')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T04.uInt32_uniq,T03.sInt32_uniq,T03.char_100
+>  from  $$P04$$ T04, $$P03$$ T03
+>  where     T04.uInt32_uniq = T03.sInt32_uniq
+>        and T03.char_100 <= 'ADAAAAAA';

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

UINT32_UNIQ  SINT32_UNIQ  CHAR_100
-----------  -----------  --------

        418          418  AAAAAAAA
         39           39  ADAAAAAA
        446          446  ABAAAAAA
        211          211  ACAAAAAA
        459          459  ABAAAAAA
         72           72  ADAAAAAA
        516          516  ACAAAAAA
         75           75  ADAAAAAA
        521          521  ADAAAAAA
        220          220  ABAAAAAA
        573          573  AAAAAAAA
        237          237  AAAAAAAA
        584          584  AAAAAAAA
         98           98  ABAAAAAA
        111          111  ACAAAAAA
        120          120  ABAAAAAA
        270          270  AAAAAAAA
        134          134  ABAAAAAA
        160          160  ACAAAAAA
        302          302  AAAAAAAA
        311          311  ADAAAAAA
        328          328  ACAAAAAA
        355          355  ACAAAAAA
        363          363  ADAAAAAA

--- 24 row(s) selected.
>>
>>?section qhj3b
>>-- CPUS = 3
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join
>>-- tables first keys don't match but logical partitioning is possible,
>>-- since both tables are part 4 ways on the join columns, with 
>>-- only the partitioning boundaries (first keys) being different.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>--
>>-- With # of cpus = 3, should choose 3 ESP plan with logical partition
>>-- grouping on one side and logical subpartitioning on the other.
>>-- This should not lead to an inbalance, because the the final 
>>-- partition of the 4-way partitioned table is empty, and the
>>-- optimizer should be smart enough to group the empty partition
>>-- with the partition just before it - thus all 3 new partitions
>>-- will be the same size.
>>----------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '3'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '3'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T04')),exchange(scan('T03')),type1));

--- SQL operation complete.
>>prepare P from
+>select T04.uInt32_uniq,T03.sInt32_uniq,T03.char_100
+>  from $$P04$$ T04, $$P03$$ T03
+>  where T04.uInt32_uniq = T03.sInt32_uniq
+>        and T03.char_100 <= 'ADAAAAAA';

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

UINT32_UNIQ  SINT32_UNIQ  CHAR_100
-----------  -----------  --------

        418          418  AAAAAAAA
        446          446  ABAAAAAA
        459          459  ABAAAAAA
        516          516  ACAAAAAA
        521          521  ADAAAAAA
        573          573  AAAAAAAA
        584          584  AAAAAAAA
        211          211  ACAAAAAA
        220          220  ABAAAAAA
        237          237  AAAAAAAA
         39           39  ADAAAAAA
        270          270  AAAAAAAA
         72           72  ADAAAAAA
         75           75  ADAAAAAA
         98           98  ABAAAAAA
        302          302  AAAAAAAA
        111          111  ACAAAAAA
        311          311  ADAAAAAA
        120          120  ABAAAAAA
        328          328  ACAAAAAA
        134          134  ABAAAAAA
        355          355  ACAAAAAA
        160          160  ACAAAAAA
        363          363  ADAAAAAA

--- 24 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>?section qhj4
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join.
>>-- Should choose logical partitioning on PTAB05, since basing
>>-- parallelism on PTAB07 allows 4-way parallelism.
>>-- PTAB07 has a 2 column clustering key and a 2 column partitioning  
>>-- key, but PTAB05 has a 2 column clustering key and a 1 column
>>-- partitioning key. 
>>--
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T07')),exchange(scan('T05')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T07.int64_60,T05.sInt32_60,T07.sInt16_10,T05.uInt16_10,
+>        T05.int64_uniq
+>  from  $$P07$$ T07, $$P05$$ T05
+>  where     T07.int64_60 = T05.sInt32_60
+>        and T07.sInt16_10 = T05.uInt16_10
+>        and T05.int64_uniq < 60;

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

INT64_60              SINT32_60    SINT16_10  UINT16_10  INT64_UNIQ
--------------------  -----------  ---------  ---------  --------------------

                  20           20          8          8                    48
                   0            0          5          5                    47
                  31           31          2          2                    17
                   0            0          6          6                     3
                  22           22          2          2                    28
                   0            0          9          9                    18
                  23           23          1          1                    12
                   4            4          2          2                    27
                  23           23          4          4                    41
                   5            5          4          4                    39
                  34           34          1          1                    24
                   5            5          7          7                    54
                  34           34          3          3                    38
                   6            6          7          7                    23
                  24           24          4          4                    59
                   6            6          8          8                     1
                  24           24          5          5                     4
                   7            7          0          0                    52
                  35           35          4          4                    30
                   7            7          4          4                    32
                  25           25          8          8                    35
                  10           10          3          3                    51
                  25           25          9          9                     2
                  10           10          6          6                    13
                  35           35          9          9                    14
                  11           11          5          5                    19
                  36           36          5          5                    16
                  12           12          8          8                    11
                  37           37          3          3                    40
                  12           12          9          9                     0
                  27           27          4          4                    34
                  13           13          0          0                    56
                  37           37          7          7                     8
                  14           14          9          9                    57
                  28           28          3          3                    49
                  16           16          0          0                    25
                  38           38          4          4                    36
                  16           16          3          3                     6
                  28           28          6          6                    20
                  17           17          8          8                     9
                  29           29          4          4                    29
                  19           19          6          6                     5
                  29           29          8          8                    33
                  40           40          8          8                    42
                  41           41          6          6                    31
                  42           42          5          5                    43
                  43           43          7          7                    53
                  45           45          5          5                    46
                  47           47          9          9                    15
                  48           48          5          5                    44
                  48           48          9          9                    21
                  49           49          1          1                    50
                  49           49          3          3                    45
                  50           50          4          4                    26
                  53           53          9          9                     7
                  54           54          2          2                    37
                  54           54          8          8                    10
                  55           55          3          3                    22
                  55           55          4          4                    58
                  56           56          6          6                    55

--- 60 row(s) selected.
>>
>>?section qhj5
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join.
>>-- Must choose to logically partition PTAB06, or would
>>-- have to repartition both tables, since there is only one join
>>-- predicate column, but PTAB06 is partitioned on that column
>>-- and an additional column.
>>-- PTAB14's clustering key is on the join column and an additional
>>-- column, but it's partitioning key is on only the join predicate
>>-- column.
>>--
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T06')),exchange(scan('T14')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T06.uInt32_60,T14.sInt32_60,T06.sInt16_10,T14.uInt16_10,
+>        T14.sNum_100
+>  from  $$P06$$ T06, $$P14$$ T14
+>  where     T06.uInt32_60 = T14.sInt32_60
+>        and T14.sNum_100 = 0;

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

UINT32_60   SINT32_60    SINT16_10  UINT16_10  SNUM_100    
----------  -----------  ---------  ---------  ------------

        11           11          0          8           .00
        11           11          1          8           .00
        11           11          2          8           .00
        11           11          3          8           .00
        11           11          4          8           .00
        11           11          5          8           .00
        11           11          6          8           .00
        11           11          7          8           .00
        11           11          8          8           .00
        11           11          9          8           .00
        12           12          0          9           .00
        12           12          1          9           .00
        12           12          2          9           .00
        12           12          3          9           .00
        12           12          4          9           .00
        12           12          5          9           .00
        12           12          6          9           .00
        12           12          7          9           .00
        12           12          8          9           .00
        12           12          9          9           .00
        38           38          0          2           .00
        38           38          1          2           .00
        38           38          2          2           .00
        38           38          3          2           .00
        38           38          4          2           .00
        38           38          5          2           .00
        38           38          6          2           .00
        38           38          7          2           .00
        38           38          8          2           .00
        38           38          9          2           .00
        41           41          0          5           .00
        41           41          1          5           .00
        41           41          2          5           .00
        41           41          3          5           .00
        41           41          4          5           .00
        41           41          5          5           .00
        41           41          6          5           .00
        41           41          7          5           .00
        41           41          8          5           .00
        41           41          9          5           .00
        44           44          0          7           .00
        44           44          1          7           .00
        44           44          2          7           .00
        44           44          3          7           .00
        44           44          4          7           .00
        44           44          5          7           .00
        44           44          6          7           .00
        44           44          7          7           .00
        44           44          8          7           .00
        44           44          9          7           .00
        56           56          0          3           .00
        56           56          1          3           .00
        56           56          2          3           .00
        56           56          3          3           .00
        56           56          4          3           .00
        56           56          5          3           .00
        56           56          6          3           .00
        56           56          7          3           .00
        56           56          8          3           .00
        56           56          9          3           .00

--- 60 row(s) selected.
>>
>>?section qhj6
>>-- SQL/MP style PLAN1 parallel two-way join plan using hash join.
>>-- Should choose logical partitioning on PTAB14, since basing
>>-- parallelism on PTAB14 allows 4-way parallelism.
>>-- Both PTAB05 and PTAB14 have 2 column clustering keys 
>>-- and a 1 column partitioning key. 
>>--
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T05')),exchange(scan('T14')),type1));

--- SQL operation complete.
>>prepare P from
+>select  T05.sInt32_60,T14.sInt32_60,T05.uInt16_10,T14.uInt16_10,
+>        T14.int64_uniq
+>  from  $$P05$$ T05, $$P14$$ T14
+>  where     T05.sInt32_60 = T14.sInt32_60
+>        and T05.uInt16_10 = T14.uInt16_10
+>        and T14.int64_uniq < 60;

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

SINT32_60    SINT32_60    UINT16_10  UINT16_10  INT64_UNIQ          
-----------  -----------  ---------  ---------  --------------------

         20           20          8          8                    48
          0            0          5          5                    47
         22           22          2          2                    28
          0            0          6          6                     3
         23           23          1          1                    12
          0            0          9          9                    18
         23           23          4          4                    41
          4            4          2          2                    27
         24           24          4          4                    59
          5            5          4          4                    39
         24           24          5          5                     4
          5            5          7          7                    54
         25           25          8          8                    35
          6            6          7          7                    23
         25           25          9          9                     2
          6            6          8          8                     1
         40           40          8          8                    42
          7            7          0          0                    52
         27           27          4          4                    34
          7            7          4          4                    32
         41           41          6          6                    31
         10           10          3          3                    51
         28           28          3          3                    49
         10           10          6          6                    13
         42           42          5          5                    43
         11           11          5          5                    19
         28           28          6          6                    20
         12           12          8          8                    11
         29           29          4          4                    29
         12           12          9          9                     0
         43           43          7          7                    53
         13           13          0          0                    56
         29           29          8          8                    33
         14           14          9          9                    57
         31           31          2          2                    17
         16           16          0          0                    25
         45           45          5          5                    46
         16           16          3          3                     6
         47           47          9          9                    15
         17           17          8          8                     9
         34           34          1          1                    24
         19           19          6          6                     5
         34           34          3          3                    38
         48           48          5          5                    44
         48           48          9          9                    21
         49           49          1          1                    50
         49           49          3          3                    45
         35           35          4          4                    30
         35           35          9          9                    14
         50           50          4          4                    26
         36           36          5          5                    16
         37           37          3          3                    40
         37           37          7          7                     8
         38           38          4          4                    36
         53           53          9          9                     7
         54           54          2          2                    37
         54           54          8          8                    10
         55           55          3          3                    22
         55           55          4          4                    58
         56           56          6          6                    55

--- 60 row(s) selected.
>>
>>?section qhj7
>>-- SQL/MP style PLAN2 parallel two-way join plan using hash join
>>-- Both tables partitioned 3 ways.
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T10')),
+>  exchange(exchange(scan('T12'))),type2));

--- SQL operation complete.
>>prepare P from
+>select T12.int64_100,T10.char_uniq,T12.varchar_uniq
+>from $$P10$$ T10, $$P12$$ T12
+>where T10.char_uniq = T12.varchar_uniq
+>and T12.int64_100 < 10;

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

INT64_100             CHAR_UNIQ  VARCHAR_UNIQ
--------------------  ---------  --------------------------------------------------

                   9  IAAAAAAA   IAAAAAAA                                          
                   2  AVAAAAAA   AVAAAAAA                                          
                   9  JIAAAAAA   JIAAAAAA                                          
                   5  AXAAAAAA   AXAAAAAA                                          
                   2  JMAAAAAA   JMAAAAAA                                          
                   0  CLAAAAAA   CLAAAAAA                                          
                   5  KBAAAAAA   KBAAAAAA                                          
                   0  DPAAAAAA   DPAAAAAA                                          
                   7  QTAAAAAA   QTAAAAAA                                          
                   1  DWAAAAAA   DWAAAAAA                                          
                   4  LHAAAAAA   LHAAAAAA                                          
                   2  EGAAAAAA   EGAAAAAA                                          
                   0  LRAAAAAA   LRAAAAAA                                          
                   7  EHAAAAAA   EHAAAAAA                                          
                   1  MIAAAAAA   MIAAAAAA                                          
                   4  EOAAAAAA   EOAAAAAA                                          
                   5  NCAAAAAA   NCAAAAAA                                          
                   5  ETAAAAAA   ETAAAAAA                                          
                   6  TEAAAAAA   TEAAAAAA                                          
                   3  EUAAAAAA   EUAAAAAA                                          
                   7  NQAAAAAA   NQAAAAAA                                          
                   7  FFAAAAAA   FFAAAAAA                                          
                   8  ODAAAAAA   ODAAAAAA                                          
                   2  FQAAAAAA   FQAAAAAA                                          
                   3  UEAAAAAA   UEAAAAAA                                          
                   8  FXAAAAAA   FXAAAAAA                                          
                   0  ORAAAAAA   ORAAAAAA                                          
                   6  GTAAAAAA   GTAAAAAA                                          
                   1  PJAAAAAA   PJAAAAAA                                          
                   4  PTAAAAAA   PTAAAAAA                                          
                   6  XOAAAAAA   XOAAAAAA                                          
                   4  XRAAAAAA   XRAAAAAA                                          
                   3  XUAAAAAA   XUAAAAAA                                          

--- 33 row(s) selected.
>>
>>?section qhj8
>>-- SQL/MP style PLAN3 parallel two-way join plan using hash join
>>-- Must range repartition T05,                 
>>-- since it is not partitioned on the join columns.
>>-- Both tables partitioned 3 ways.
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(exchange(scan('T05'))),
+>  exchange(scan('T06')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>select  T05.int64_uniq,T06.uInt32_60,T05.uInt16_10,T06.sInt16_10
+>  from  $$P05$$ T05, $$P06$$ T06
+>  where     T05.int64_uniq = T06.uInt32_60
+>        and T05.uInt16_10 = T06.sInt16_10
+>        and T06.sint16_10 < 5;

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

INT64_UNIQ            UINT32_60   UINT16_10  SINT16_10
--------------------  ----------  ---------  ---------

                  27          27          2          2
                   6           6          3          3
                  39          39          4          4
                  12          12          1          1
                  52          52          0          0
                  17          17          2          2
                  32          32          4          4
                  50          50          1          1
                  45          45          3          3
                  51          51          3          3
                  26          26          4          4
                  56          56          0          0
                  37          37          2          2
                  22          22          3          3
                  58          58          4          4
                  25          25          0          0
                  28          28          2          2
                  41          41          4          4
                  59          59          4          4
                  34          34          4          4
                  49          49          3          3
                  29          29          4          4
                  24          24          1          1
                  38          38          3          3
                  30          30          4          4
                  40          40          3          3
                  36          36          4          4

--- 27 row(s) selected.
>>
>>?section qhj9
>>-- SQL/MP style PLAN3 parallel two-way join plan using hash join
>>-- Must range repartition T06,
>>-- since it is not partitioned on the join columns.
>>-- Both tables partitioned 3 ways.
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T05')),
+>  exchange(exchange(scan('T06'))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>select  T05.sInt32_60,T06.int64_uniq,T05.uInt16_10,T06.sInt16_10
+>  from  $$P05$$ T05, $$P06$$ T06
+>  where     T05.sInt32_60 = T06.int64_uniq
+>        and T05.uInt16_10 = T06.sInt16_10
+>        and T06.sint16_10 < 5;

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

SINT32_60    INT64_UNIQ            UINT16_10  SINT16_10
-----------  --------------------  ---------  ---------

         40                    40          3          3
          6                     6          3          3
         22                    22          3          3
         12                    12          1          1
         24                    24          1          1
         17                    17          2          2
         25                    25          0          0
         26                    26          4          4
         27                    27          2          2
         28                    28          2          2
         29                    29          4          4
         30                    30          4          4
         32                    32          4          4
         41                    41          4          4
         34                    34          4          4
         36                    36          4          4
         45                    45          3          3
         37                    37          2          2
         38                    38          3          3
         39                    39          4          4
         49                    49          3          3
         50                    50          1          1
         51                    51          3          3
         52                    52          0          0
         56                    56          0          0
         58                    58          4          4
         59                    59          4          4

--- 27 row(s) selected.
>>
>>?section qhj10
>>-- SQL/MP style PLAN3 parallel two-way join plan using hash join
>>-- Must hash repartition both children,
>>-- since neither is partitioned on the join columns. 
>>-- Both tables partitioned 3 ways.
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape exchange(hybrid_hash_join(
+>  exchange(exchange(scan('T05'))),
+>  exchange(exchange(scan('T06'))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>select T05.int64_uniq,T06.int64_uniq,T05.uInt16_10,T06.uNum_10
+>  from  $$P05$$ T05, $$P06$$ T06
+>  where     T05.int64_uniq = T06.int64_uniq
+>        and T05.uInt16_10 = T06.uNum_10
+>;

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

INT64_UNIQ            INT64_UNIQ            UINT16_10  UNUM_10    
--------------------  --------------------  ---------  -----------

                 137                   137          0          .00
                 386                   386          0          .00
                 166                   166          0          .00
                 322                   322          0          .00
                 299                   299          0          .00
                 286                   286          0          .00
                 220                   220          0          .00
                 141                   141          0          .00

--- 8 row(s) selected.
>>
>>
>>-- 3 WAY HASH JOINS
>>
>>?section qhj11
>>-- PLAN1 H.H. over PLAN1 H.H. three way join plan
>>-- All three tables are partitioned 3 ways and their partitioning
>>-- keys match exactly (3-3-3).
>>------------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(
>>--  hybrid_hash_join(cut,cut),cut));
>>control query shape exchange(hybrid_hash_join(
+>  hybrid_hash_join(exchange(scan('T00A')),
+>                   exchange(scan('T01')),
+>                   type1),
+>  exchange(scan('T00B')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T00A.sInt32_uniq,T01.uInt32_uniq,T00B.sInt32_uniq
+>  from  $$P00$$ T00A, $$P01$$ T01, $$P00$$ T00B
+>  where     T00A.sint32_uniq = T01.uInt32_uniq
+>        and T01.uInt32_uniq = T00B.sInt32_uniq
+>        and T01.uInt32_uniq < 30
+>        and T00B.sInt32_uniq < 30;

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

SINT32_UNIQ  UINT32_UNIQ  SINT32_UNIQ
-----------  -----------  -----------

          0            0            0
          1            1            1
          2            2            2
          3            3            3
          4            4            4
          5            5            5
          6            6            6
          7            7            7
          8            8            8
          9            9            9
         10           10           10
         11           11           11
         12           12           12
         13           13           13
         14           14           14
         15           15           15
         16           16           16
         17           17           17
         18           18           18
         19           19           19
         20           20           20
         21           21           21
         22           22           22
         23           23           23
         24           24           24
         25           25           25
         26           26           26
         27           27           27
         28           28           28
         29           29           29

--- 30 row(s) selected.
>>
>>?section qhj12
>>-- PLAN2 H.H. over PLAN1 H.H. three way join plan
>>-- 3-3-6
>>------------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(
>>--  hybrid_hash_join(cut,cut),cut));
>>control query shape exchange(hybrid_hash_join(
+>  hybrid_hash_join(exchange(scan('T00')),
+>                   exchange(scan('T01')),
+>                   type1),
+>  exchange(exchange(scan('T02'))),
+>  type2));

--- SQL operation complete.
>>prepare P from
+>Select  T00.sInt32_uniq,T01.uInt32_uniq,T02.sInt32_uniq,T01.uNum_10
+>  from  $$P00$$ T00, $$P01$$ T01, $$P02$$ T02
+>  where     T00.sint32_uniq = T01.uInt32_uniq
+>        and T00.sInt32_uniq = T02.sInt32_uniq
+>        and T01.uNum_10 = 0
+>        and T02.sInt32_uniq < 300;

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

SINT32_UNIQ  UINT32_UNIQ  SINT32_UNIQ  UNUM_10    
-----------  -----------  -----------  -----------

        231          231          231          .00
          1            1            1          .00
        250          250          250          .00
         20           20           20          .00
        252          252          252          .00
         22           22           22          .00
        282          282          282          .00
         32           32           32          .00
        294          294          294          .00
         42           42           42          .00
        296          296          296          .00
         43           43           43          .00
         56           56           56          .00
         60           60           60          .00
         75           75           75          .00
         81           81           81          .00
         82           82           82          .00
         91           91           91          .00
        103          103          103          .00
        105          105          105          .00
        111          111          111          .00
        148          148          148          .00
        149          149          149          .00
        150          150          150          .00
        156          156          156          .00
        158          158          158          .00
        163          163          163          .00
        173          173          173          .00
        177          177          177          .00
        185          185          185          .00
        196          196          196          .00
        197          197          197          .00

--- 32 row(s) selected.
>>
>>?section qhj13
>>-- PLAN1 H.H. over PLAN2 H.H. three way join plan
>>-- 3-6-3
>>------------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(
>>--  hybrid_hash_join(cut,cut),cut));
>>control query shape exchange(hybrid_hash_join(
+>  hybrid_hash_join(exchange(scan('T00')),
+>                   exchange(exchange(scan('T02'))),
+>                   type2),
+>  exchange(scan('T01')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T00.sInt32_uniq,T02.sInt32_uniq,T01.uInt32_uniq,T02.uNum_10
+>  from  $$P00$$ T00, $$P02$$ T02, $$P01$$ T01
+>  where     T00.sint32_uniq = T02.sInt32_uniq
+>        and T00.sInt32_uniq = T01.uInt32_uniq
+>        and T02.uNum_10 = 0
+>        and T01.uInt32_uniq < 300;

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

SINT32_UNIQ  SINT32_UNIQ  UINT32_UNIQ  UNUM_10    
-----------  -----------  -----------  -----------

        203          203          203          .00
         16           16           16          .00
        212          212          212          .00
         35           35           35          .00
        222          222          222          .00
         40           40           40          .00
        240          240          240          .00
         51           51           51          .00
        246          246          246          .00
         54           54           54          .00
        269          269          269          .00
         81           81           81          .00
        285          285          285          .00
        100          100          100          .00
        286          286          286          .00
        104          104          104          .00
        288          288          288          .00
        105          105          105          .00
        296          296          296          .00
        110          110          110          .00
        115          115          115          .00
        121          121          121          .00
        129          129          129          .00
        142          142          142          .00
        154          154          154          .00
        159          159          159          .00
        192          192          192          .00
        198          198          198          .00

--- 28 row(s) selected.
>>
>>?section qhj14
>>-- PLAN2 H.H. over PLAN2 H.H. three way join plan
>>-- 4-4-4
>>------------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(
>>--  hybrid_hash_join(cut,cut),cut));
>>control query shape exchange(hybrid_hash_join(
+>  hybrid_hash_join(exchange(scan('T03')),
+>                   exchange(exchange(scan('T04'))),
+>                   type2),
+>  exchange(exchange(scan('T07'))),
+>  type2));

--- SQL operation complete.
>>prepare P from
+>Select  T03.uInt32_uniq,T04.sInt32_uniq,T07.sInt32_uniq,
+>        T04.char_10,T07.uInt32_50p
+>  from  $$P03$$ T03, $$P04$$ T04, $$P07$$ T07
+>  where     T03.uint32_uniq = T04.sInt32_uniq
+>        and T04.sInt32_uniq = T07.sInt32_uniq
+>        and T04.char_10 = 'AAAAAAAA'
+>        and T07.uInt32_50p < 150;

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

UINT32_UNIQ  SINT32_UNIQ  SINT32_UNIQ  CHAR_10    UINT32_50P
-----------  -----------  -----------  ---------  ----------

          0            0            0  AAAAAAAA          113
        244          244          244  AAAAAAAA          144
        262          262          262  AAAAAAAA          128
         17           17           17  AAAAAAAA          145
        542          542          542  AAAAAAAA          105
        349          349          349  AAAAAAAA          133
         12           12           12  AAAAAAAA           59
         86           86           86  AAAAAAAA           39
        192          192          192  AAAAAAAA           22
         24           24           24  AAAAAAAA           30
        255          255          255  AAAAAAAA          133
        544          544          544  AAAAAAAA           26
         82           82           82  AAAAAAAA           34
        303          303          303  AAAAAAAA           16
        308          308          308  AAAAAAAA          134
        277          277          277  AAAAAAAA           72
        416          416          416  AAAAAAAA           54
         44           44           44  AAAAAAAA           28
        422          422          422  AAAAAAAA           29
        122          122          122  AAAAAAAA          115
         42           42           42  AAAAAAAA           89
        159          159          159  AAAAAAAA          127
        427          427          427  AAAAAAAA          120
         10           10           10  AAAAAAAA          103
         22           22           22  AAAAAAAA           55
        276          276          276  AAAAAAAA           52

--- 26 row(s) selected.
>>
>>?section qhj15
>>-- PLAN1 H.H. over PLAN3 H.H. 
>>-- Must range repartition T03,
>>-- since it has 4 parts and the other tables have 3 parts, and
>>-- it is not partitioned on the join columns.
>>-- 3-4-3
>>----------------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(
>>--  hybrid_hash_join(cut,cut),cut));
>>control query shape exchange(hybrid_hash_join(
+>  hybrid_hash_join(exchange(scan('T00')),
+>	           exchange(exchange(scan('T03'))),
+>                   type1),
+>  exchange(scan('T01')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select  T00.sInt32_uniq,T03.sNum_uniq,T01.uInt32_uniq,T03.uInt16_10
+>  from  $$P00$$ T00, $$P03$$ T03, $$P01$$ T01
+>  where     T00.sInt32_uniq = T03.sNum_uniq
+>        and T00.sInt32_uniq = T01.uInt32_uniq
+>        and T03.uInt16_10 IN (0,1,2,3,4);

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

SINT32_UNIQ  SNUM_UNIQ     UINT32_UNIQ  UINT16_10
-----------  ------------  -----------  ---------

          0           .00            0          0
          1          1.00            1          1
          2          2.00            2          2
          3          3.00            3          0

--- 4 row(s) selected.
>>
>>?section qhj16
>>-- PLAN3 H.H. over PLAN1 H.H. 
>>-- Must hash repartition output of first hash join (the plan 1)
>>-- and table T12, because they are not partitioned 
>>-- on the join columns. 
>>-- 3-4-3
>>----------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(hybrid_hash_join(
+>  exchange(hybrid_hash_join(exchange(scan('T10')),
+>	                      exchange(scan('T11')),
+>                            type1)),
+>  exchange(exchange(scan('T12'))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select  T11.char_10,T12.char_10,T10.char_uniq,T11.varchar_uniq,
+>        T10.varchar_uniq,T12.char_uniq
+>  from  $$P10$$ T10, $$P11$$ T11, $$P12$$ T12
+>  where     T10.char_uniq = T11.varchar_uniq
+>        and T10.varchar_uniq = T12.char_uniq  
+>        and T11.char_10 = 'AAAAAAAA'
+>        and T12.char_10 = 'AAAAAAAA';

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

CHAR_10    CHAR_10    CHAR_UNIQ  VARCHAR_UNIQ                                        VARCHAR_UNIQ                                        CHAR_UNIQ
---------  ---------  ---------  --------------------------------------------------  --------------------------------------------------  ---------

AAAAAAAA   AAAAAAAA   DWAAAAAA   DWAAAAAA                                            MFAAAAAA                                            MFAAAAAA 
AAAAAAAA   AAAAAAAA   UPAAAAAA   UPAAAAAA                                            BHAAAAAA                                            BHAAAAAA 
AAAAAAAA   AAAAAAAA   WLAAAAAA   WLAAAAAA                                            SNAAAAAA                                            SNAAAAAA 
AAAAAAAA   AAAAAAAA   WXAAAAAA   WXAAAAAA                                            RYAAAAAA                                            RYAAAAAA 

--- 4 row(s) selected.
>>
>>?section qhj17
>>-- PLAN3 H.H. over PLAN3 H.H. 
>>-- Must hash repartition all leafs, since none of the tables
>>-- are partitioned on the join columns.
>>-- 3-4-3
>>----------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(hybrid_hash_join(
+>  hybrid_hash_join(exchange(exchange(scan('T10'))),
+>                   exchange(exchange(scan('T11'))),
+>                   type1),
+>  exchange(exchange(scan('T12'))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select  T11.char_10,T12.char_10,T10.varchar_uniq,T11.char_uniq,
+>        T12.char_uniq
+>  from  $$P10$$ T10, $$P11$$ T11, $$P12$$ T12
+>  where     T10.varchar_uniq = T11.char_uniq
+>        and T10.varchar_uniq = T12.char_uniq  
+>        and T11.char_10 = 'AAAAAAAA'
+>        and T12.char_10 = 'AAAAAAAA';

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

CHAR_10    CHAR_10    VARCHAR_UNIQ                                        CHAR_UNIQ  CHAR_UNIQ
---------  ---------  --------------------------------------------------  ---------  ---------

AAAAAAAA   AAAAAAAA   GJAAAAAA                                            GJAAAAAA   GJAAAAAA 
AAAAAAAA   AAAAAAAA   HNAAAAAA                                            HNAAAAAA   HNAAAAAA 
AAAAAAAA   AAAAAAAA   AHAAAAAA                                            AHAAAAAA   AHAAAAAA 
AAAAAAAA   AAAAAAAA   TEAAAAAA                                            TEAAAAAA   TEAAAAAA 
AAAAAAAA   AAAAAAAA   MFAAAAAA                                            MFAAAAAA   MFAAAAAA 
AAAAAAAA   AAAAAAAA   JFAAAAAA                                            JFAAAAAA   JFAAAAAA 
AAAAAAAA   AAAAAAAA   GTAAAAAA                                            GTAAAAAA   GTAAAAAA 
AAAAAAAA   AAAAAAAA   RMAAAAAA                                            RMAAAAAA   RMAAAAAA 
AAAAAAAA   AAAAAAAA   QWAAAAAA                                            QWAAAAAA   QWAAAAAA 
AAAAAAAA   AAAAAAAA   JOAAAAAA                                            JOAAAAAA   JOAAAAAA 
AAAAAAAA   AAAAAAAA   WPAAAAAA                                            WPAAAAAA   WPAAAAAA 
AAAAAAAA   AAAAAAAA   FXAAAAAA                                            FXAAAAAA   FXAAAAAA 
AAAAAAAA   AAAAAAAA   KQAAAAAA                                            KQAAAAAA   KQAAAAAA 
AAAAAAAA   AAAAAAAA   NNAAAAAA                                            NNAAAAAA   NNAAAAAA 
AAAAAAAA   AAAAAAAA   KTAAAAAA                                            KTAAAAAA   KTAAAAAA 
AAAAAAAA   AAAAAAAA   LLAAAAAA                                            LLAAAAAA   LLAAAAAA 
AAAAAAAA   AAAAAAAA   MQAAAAAA                                            MQAAAAAA   MQAAAAAA 
AAAAAAAA   AAAAAAAA   LHAAAAAA                                            LHAAAAAA   LHAAAAAA 
AAAAAAAA   AAAAAAAA   KAAAAAAA                                            KAAAAAAA   KAAAAAAA 
AAAAAAAA   AAAAAAAA   HGAAAAAA                                            HGAAAAAA   HGAAAAAA 
AAAAAAAA   AAAAAAAA   OWAAAAAA                                            OWAAAAAA   OWAAAAAA 
AAAAAAAA   AAAAAAAA   BHAAAAAA                                            BHAAAAAA   BHAAAAAA 
AAAAAAAA   AAAAAAAA   OTAAAAAA                                            OTAAAAAA   OTAAAAAA 
AAAAAAAA   AAAAAAAA   LXAAAAAA                                            LXAAAAAA   LXAAAAAA 
AAAAAAAA   AAAAAAAA   WEAAAAAA                                            WEAAAAAA   WEAAAAAA 
AAAAAAAA   AAAAAAAA   RYAAAAAA                                            RYAAAAAA   RYAAAAAA 
AAAAAAAA   AAAAAAAA   QGAAAAAA                                            QGAAAAAA   QGAAAAAA 
AAAAAAAA   AAAAAAAA   IMAAAAAA                                            IMAAAAAA   IMAAAAAA 
AAAAAAAA   AAAAAAAA   GCAAAAAA                                            GCAAAAAA   GCAAAAAA 
AAAAAAAA   AAAAAAAA   KLAAAAAA                                            KLAAAAAA   KLAAAAAA 
AAAAAAAA   AAAAAAAA   SNAAAAAA                                            SNAAAAAA   SNAAAAAA 
AAAAAAAA   AAAAAAAA   UNAAAAAA                                            UNAAAAAA   UNAAAAAA 

--- 32 row(s) selected.
>>
>>?section qhj18
>>-- PLAN3 H.H. over PLAN3 H.H. 
>>-- Must hash repartition all children, including the output of 
>>-- the first hash join.
>>-- 3-4-3
>>----------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(hybrid_hash_join(
+>  exchange(hybrid_hash_join(exchange(exchange(scan('T10'))),
+>                            exchange(exchange(scan('T11'))),
+>                            type1)),
+>  exchange(exchange(scan('T12'))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select  T11.char_10,T12.char_10,T10.varchar_uniq,T11.char_uniq,
+>        T10.char_100,T12.char_uniq
+>  from  $$P10$$ T10, $$P11$$ T11, $$P12$$ T12
+>  where     T10.varchar_uniq = T11.char_uniq
+>        and T10.char_100 = T12.char_uniq  
+>        and T11.char_10 = 'AAAAAAAA'
+>        and T12.char_10 = 'AAAAAAAA';

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

CHAR_10    CHAR_10    VARCHAR_UNIQ                                        CHAR_UNIQ  CHAR_100  CHAR_UNIQ
---------  ---------  --------------------------------------------------  ---------  --------  ---------

AAAAAAAA   AAAAAAAA   HNAAAAAA                                            HNAAAAAA   AHAAAAAA  AHAAAAAA 
AAAAAAAA   AAAAAAAA   QWAAAAAA                                            QWAAAAAA   DTAAAAAA  DTAAAAAA 
AAAAAAAA   AAAAAAAA   JFAAAAAA                                            JFAAAAAA   CNAAAAAA  CNAAAAAA 

--- 3 row(s) selected.
>>
>>
>>-- HASH JOINS ON DATE AND CHARACTER COLUMNS
>>
>>?section qhj19
>>--================================================
>>--  Plan 1 hash join on a date partitioning key ==
>>-- each child with same number of partitions.   ==
>>--================================================
>>
>>-------------------------------
>>-- Force a plan 1 hash join. --
>>-------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape
+> exchange(hybrid_hash_join(
+>            exchange(scan('T13A')),
+>            exchange(scan('T13B')),
+>            type1
+>                          )
+>         );

--- SQL operation complete.
>>
>>---------------------------------------
>>--  Join on a date partitioning key. --
>>---------------------------------------
>>prepare P from
+>select T13A.date_uniq,T13B.date_uniq, T13B.sInt32_100,
+>       substring(T13B.char_50p from 1 for 8)  
+>  from $$P13$$ T13A, $$P13$$ T13B
+>  where    T13A.date_uniq = T13B.date_uniq
+>       and T13A.sInt32_100 < 5;

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

DATE_UNIQ   DATE_UNIQ   SINT32_100   (EXPR)  
----------  ----------  -----------  --------

2100-11-01  2100-11-01            3  AUAAAAAA
2101-04-09  2101-04-09            4  FJAAAAAA
2100-11-13  2100-11-13            4  DWAAAAAA
2101-05-16  2101-05-16            1  ECAAAAAA
2100-11-23  2100-11-23            2  JQAAAAAA
2101-05-19  2101-05-19            0  GFAAAAAA
2100-11-27  2100-11-27            2  KLAAAAAA
2101-05-30  2101-05-30            0  HEAAAAAA
2100-12-15  2100-12-15            0  FKAAAAAA
2101-06-05  2101-06-05            2  IOAAAAAA
2101-01-06  2101-01-06            2  CGAAAAAA
2101-06-28  2101-06-28            2  ENAAAAAA
2101-02-28  2101-02-28            3  JUAAAAAA
2101-07-03  2101-07-03            4  HXAAAAAA
2101-03-12  2101-03-12            3  GUAAAAAA
2101-03-17  2101-03-17            1  GTAAAAAA
2100-02-02  2100-02-02            3  JAAAAAAA
2100-06-17  2100-06-17            0  ARAAAAAA
2100-03-19  2100-03-19            3  JAAAAAAA
2100-07-20  2100-07-20            0  BRAAAAAA
2100-04-07  2100-04-07            1  KYAAAAAA
2100-07-23  2100-07-23            4  LTAAAAAA
2100-04-16  2100-04-16            1  JGAAAAAA
2100-08-21  2100-08-21            0  CTAAAAAA
2100-04-21  2100-04-21            3  AVAAAAAA
2100-10-04  2100-10-04            4  DOAAAAAA
2100-04-28  2100-04-28            1  ACAAAAAA
2100-10-23  2100-10-23            2  EQAAAAAA
2100-05-17  2100-05-17            4  CXAAAAAA
2100-05-20  2100-05-20            1  HUAAAAAA

--- 30 row(s) selected.
>>
>>?section qhj20
>>--================================================
>>--  Plan 1 hash join on a char partitioning key ==
>>-- each child with same number of partitions.   ==
>>--================================================
>>
>>-------------------------------
>>-- Force a plan 1 hash join. --
>>-------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape
+> exchange(hybrid_hash_join(
+>            exchange(scan('T10A')),
+>            exchange(scan('T10B')),
+>            type1
+>                          )
+>         );

--- SQL operation complete.
>>
>>---------------------------------------
>>--  Join on a char partitioning key. --
>>---------------------------------------
>>prepare P from
+>select T10A.char_uniq,T10B.char_uniq, T10B.sInt32_100,
+>       substring(T10A.char_50p from 1 for 8)  
+>  from $$P10$$ T10A, $$P10$$ T10B
+>  where    T10A.char_uniq = T10B.char_uniq
+>       and T10A.sInt32_100 < 5;

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

CHAR_UNIQ  CHAR_UNIQ  SINT32_100   (EXPR)  
---------  ---------  -----------  --------

IAAAAAAA   IAAAAAAA             0  BRAAAAAA
QXAAAAAA   QXAAAAAA             3  JUAAAAAA
IDAAAAAA   IDAAAAAA             4  LTAAAAAA
RKAAAAAA   RKAAAAAA             3  GUAAAAAA
JHAAAAAA   JHAAAAAA             0  CTAAAAAA
RPAAAAAA   RPAAAAAA             1  GTAAAAAA
LBAAAAAA   LBAAAAAA             4  DOAAAAAA
SNAAAAAA   SNAAAAAA             4  FJAAAAAA
LUAAAAAA   LUAAAAAA             2  EQAAAAAA
UAAAAAAA   UAAAAAAA             1  ECAAAAAA
MEAAAAAA   MEAAAAAA             3  AUAAAAAA
UDAAAAAA   UDAAAAAA             0  GFAAAAAA
MQAAAAAA   MQAAAAAA             4  DWAAAAAA
UOAAAAAA   UOAAAAAA             0  HEAAAAAA
NBAAAAAA   NBAAAAAA             2  JQAAAAAA
UUAAAAAA   UUAAAAAA             2  IOAAAAAA
NFAAAAAA   NFAAAAAA             2  KLAAAAAA
VSAAAAAA   VSAAAAAA             2  ENAAAAAA
NXAAAAAA   NXAAAAAA             0  FKAAAAAA
VXAAAAAA   VXAAAAAA             4  HXAAAAAA
OUAAAAAA   OUAAAAAA             2  CGAAAAAA
BHAAAAAA   BHAAAAAA             3  JAAAAAAA
DCAAAAAA   DCAAAAAA             3  JAAAAAAA
DVAAAAAA   DVAAAAAA             1  KYAAAAAA
EFAAAAAA   EFAAAAAA             1  JGAAAAAA
EKAAAAAA   EKAAAAAA             3  AVAAAAAA
ERAAAAAA   ERAAAAAA             1  ACAAAAAA
FLAAAAAA   FLAAAAAA             4  CXAAAAAA
FOAAAAAA   FOAAAAAA             1  HUAAAAAA
GRAAAAAA   GRAAAAAA             0  ARAAAAAA

--- 30 row(s) selected.
>>
>>?section qhj21
>>--====================================================
>>--  Plan 1 hash join on a date partitioning key.    ==
>>-- Each child has a differing number of partitions. ==
>>--====================================================
>>
>>-------------------------------
>>-- Force a plan 1 hash join. --
>>-------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape
+> exchange(hybrid_hash_join(
+>            exchange(scan('T12')),
+>            exchange(scan('T13')),
+>            type1
+>                          )
+>         );

--- SQL operation complete.
>>
>>---------------------------------------
>>--  Join on a date partitioning key. --
>>---------------------------------------
>>prepare P from
+>select T12.date_uniq,T13.date_uniq, T13.sInt32_100,
+>       substring(T13.char_50p from 1 for 8)  
+>  from $$P12$$ T12, $$P13$$ T13
+>  where    T12.date_uniq = T13.date_uniq
+>       and T12.sInt32_100 < 5;

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

DATE_UNIQ   DATE_UNIQ   SINT32_100   (EXPR)  
----------  ----------  -----------  --------

2100-07-20  2100-07-20            0  BRAAAAAA
2100-02-02  2100-02-02            3  JAAAAAAA
2101-02-28  2101-02-28            3  JUAAAAAA
2100-03-19  2100-03-19            3  JAAAAAAA
2100-07-23  2100-07-23            4  LTAAAAAA
2100-04-07  2100-04-07            1  KYAAAAAA
2101-03-12  2101-03-12            3  GUAAAAAA
2100-04-16  2100-04-16            1  JGAAAAAA
2100-08-21  2100-08-21            0  CTAAAAAA
2100-04-21  2100-04-21            3  AVAAAAAA
2101-03-17  2101-03-17            1  GTAAAAAA
2100-04-28  2100-04-28            1  ACAAAAAA
2100-10-04  2100-10-04            4  DOAAAAAA
2100-05-17  2100-05-17            4  CXAAAAAA
2101-04-09  2101-04-09            4  FJAAAAAA
2100-05-20  2100-05-20            1  HUAAAAAA
2100-10-23  2100-10-23            2  EQAAAAAA
2100-06-17  2100-06-17            0  ARAAAAAA
2101-05-16  2101-05-16            1  ECAAAAAA
2100-11-01  2100-11-01            3  AUAAAAAA
2101-05-19  2101-05-19            0  GFAAAAAA
2100-11-13  2100-11-13            4  DWAAAAAA
2101-05-30  2101-05-30            0  HEAAAAAA
2100-11-23  2100-11-23            2  JQAAAAAA
2101-06-05  2101-06-05            2  IOAAAAAA
2100-11-27  2100-11-27            2  KLAAAAAA
2101-06-28  2101-06-28            2  ENAAAAAA
2100-12-15  2100-12-15            0  FKAAAAAA
2101-07-03  2101-07-03            4  HXAAAAAA
2101-01-06  2101-01-06            2  CGAAAAAA

--- 30 row(s) selected.
>>
>>?section qhj22
>>--====================================================
>>--  Plan 1 hash join on a char partitioning key.    ==
>>-- Each child has a differing number of partitions. ==
>>--====================================================
>>
>>-------------------------------
>>-- Force a plan 1 hash join. --
>>-------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape
+> exchange(hybrid_hash_join(
+>            exchange(scan('T10')),
+>            exchange(scan('T11')),
+>            type1
+>                          )
+>         );

--- SQL operation complete.
>>
>>---------------------------------------
>>--  Join on a char partitioning key. --
>>---------------------------------------
>>prepare P from
+>select substring(T10.char_uniq from 1 for 8),
+>       substring(T11.varchar_uniq from 1 for 8),
+>       T11.sInt32_100,
+>       substring(T11.char_50p from 1 for 8)  
+>  from $$P10$$ T10, $$P11$$ T11
+>  where    T10.char_uniq = T11.varchar_uniq
+>       and T10.sInt32_100 < 5;

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

(EXPR)    (EXPR)    SINT32_100   (EXPR)  
--------  --------  -----------  --------

DCAAAAAA  DCAAAAAA            3  JAAAAAAA
EFAAAAAA  EFAAAAAA            1  JGAAAAAA
ERAAAAAA  ERAAAAAA            1  ACAAAAAA
FOAAAAAA  FOAAAAAA            1  HUAAAAAA
GRAAAAAA  GRAAAAAA            0  ARAAAAAA
QXAAAAAA  QXAAAAAA            3  JUAAAAAA
IDAAAAAA  IDAAAAAA            4  LTAAAAAA
RKAAAAAA  RKAAAAAA            3  GUAAAAAA
SNAAAAAA  SNAAAAAA            4  FJAAAAAA
LUAAAAAA  LUAAAAAA            2  EQAAAAAA
UDAAAAAA  UDAAAAAA            0  GFAAAAAA
VSAAAAAA  VSAAAAAA            2  ENAAAAAA

--- 12 row(s) selected.
>>
>>?section qhj23
>>--======================================================
>>--  Plan 3 hash join on a date column.  Neither child ==
>>-- is partitioned on the date column.  Each child has ==
>>-- a differing number of partitions.                  ==
>>--======================================================
>>
>>--------------------------------------------------------
>>-- Force a plan 1 hash join. Since join column is not --
>>-- the partitioning key, this corresponds to a plan 3 --
>>-- join.                                              --
>>--------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape
+>  exchange(hybrid_hash_join(
+>             exchange(exchange(scan('T01'))),
+>             exchange(exchange(scan('T03'))),
+>             type1
+>                           )
+>          );

--- SQL operation complete.
>>
>>--------------------------------------------------------------
>>--  Join on a date column which is not the partitioning key --
>>-- for either child.                                        --
>>--------------------------------------------------------------
>>prepare P from
+>select T01.date_uniq,T03.date_uniq, T03.sInt32_100,
+>       substring(T03.char_50p from 1 for 8)  
+>  from $$P01$$ T01, $$P03$$ T03
+>  where    T01.date_uniq = T03.date_uniq
+>       and T01.sInt32_100 < 5;

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

DATE_UNIQ   DATE_UNIQ   SINT32_100   (EXPR)  
----------  ----------  -----------  --------

2101-04-27  2101-04-27            3  GUAAAAAA
2101-03-27  2101-03-27            1  JGAAAAAA
2101-04-05  2101-04-05            1  GTAAAAAA
2101-05-20  2101-05-20            3  AVAAAAAA
2101-06-25  2101-06-25            4  FJAAAAAA
2101-07-03  2101-07-03            1  ACAAAAAA
2101-06-11  2101-06-11            1  ECAAAAAA
2101-01-30  2101-01-30            0  ARAAAAAA
2101-03-02  2101-03-02            2  IOAAAAAA
2101-08-16  2101-08-16            2  ENAAAAAA
2101-03-10  2101-03-10            0  CTAAAAAA
2101-05-03  2101-05-03            4  DOAAAAAA
2101-08-19  2101-08-19            4  DWAAAAAA
2100-08-03  2100-08-03            3  JUAAAAAA
2100-05-24  2100-05-24            3  JAAAAAAA
2100-11-05  2100-11-05            0  GFAAAAAA
2100-11-13  2100-11-13            3  JAAAAAAA
2100-01-19  2100-01-19            0  HEAAAAAA
2100-07-06  2100-07-06            1  KYAAAAAA
2100-05-13  2100-05-13            4  HXAAAAAA
2100-01-13  2100-01-13            4  CXAAAAAA
2100-06-25  2100-06-25            0  BRAAAAAA
2100-02-11  2100-02-11            1  HUAAAAAA
2100-02-03  2100-02-03            4  LTAAAAAA
2100-08-05  2100-08-05            2  EQAAAAAA
2100-11-06  2100-11-06            3  AUAAAAAA
2100-03-31  2100-03-31            2  JQAAAAAA
2100-02-13  2100-02-13            2  KLAAAAAA
2100-10-28  2100-10-28            0  FKAAAAAA
2100-06-04  2100-06-04            2  CGAAAAAA

--- 30 row(s) selected.
>>
>>?section qhj24
>>--======================================================
>>--  Plan 3 hash join on a char column.  Neither child ==
>>-- is partitioned on the char column.  Each child has ==
>>-- a differing number of partitions.                  ==
>>--======================================================
>>
>>--------------------------------------------------------
>>-- Force a plan 1 hash join. Since join column is not --
>>-- the partitioning key, this corresponds to a plan 3 --
>>-- join.                                              --
>>--------------------------------------------------------
>>--control query shape exchange(hybrid_hash_join(cut,cut));
>>control query shape
+>  exchange(hybrid_hash_join(
+>             exchange(exchange(scan('T01'))),
+>             exchange(exchange(scan('T03'))),
+>             type1
+>                           )
+>          );

--- SQL operation complete.
>>
>>--------------------------------------------------------------
>>--  Join on a char column which is not the partitioning key --
>>-- for either child.                                        --
>>--------------------------------------------------------------
>>prepare P from
+>select T01.char_uniq,T03.char_uniq, T03.sInt32_100,
+>       substring(T03.char_50p from 1 for 8)  
+>  from $$P01$$ T01, $$P03$$ T03
+>  where    T01.char_uniq = T03.char_uniq
+>       and T01.sInt32_100 < 5;

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

CHAR_UNIQ  CHAR_UNIQ  SINT32_100   (EXPR)  
---------  ---------  -----------  --------

XPAAAAAA   XPAAAAAA             3  JAAAAAAA
NQAAAAAA   NQAAAAAA             3  JAAAAAAA
MCAAAAAA   MCAAAAAA             1  KYAAAAAA
PPAAAAAA   PPAAAAAA             0  BRAAAAAA
MLAAAAAA   MLAAAAAA             1  JGAAAAAA
AKAAAAAA   AKAAAAAA             4  LTAAAAAA
DFAAAAAA   DFAAAAAA             3  AVAAAAAA
OKAAAAAA   OKAAAAAA             0  CTAAAAAA
JQAAAAAA   JQAAAAAA             1  ACAAAAAA
VCAAAAAA   VCAAAAAA             4  DOAAAAAA
XIAAAAAA   XIAAAAAA             4  CXAAAAAA
KOAAAAAA   KOAAAAAA             2  EQAAAAAA
RKAAAAAA   RKAAAAAA             1  HUAAAAAA
BLAAAAAA   BLAAAAAA             3  AUAAAAAA
MRAAAAAA   MRAAAAAA             0  ARAAAAAA
WBAAAAAA   WBAAAAAA             4  DWAAAAAA
XWAAAAAA   XWAAAAAA             2  JQAAAAAA
SSAAAAAA   SSAAAAAA             2  KLAAAAAA
KNAAAAAA   KNAAAAAA             0  FKAAAAAA
FLAAAAAA   FLAAAAAA             2  CGAAAAAA
VAAAAAAA   VAAAAAAA             3  JUAAAAAA
XOAAAAAA   XOAAAAAA             3  GUAAAAAA
JLAAAAAA   JLAAAAAA             1  GTAAAAAA
FNAAAAAA   FNAAAAAA             4  FJAAAAAA
RTAAAAAA   RTAAAAAA             1  ECAAAAAA
PKAAAAAA   PKAAAAAA             0  GFAAAAAA
KDAAAAAA   KDAAAAAA             0  HEAAAAAA
VPAAAAAA   VPAAAAAA             2  IOAAAAAA
EDAAAAAA   EDAAAAAA             2  ENAAAAAA
DYAAAAAA   DYAAAAAA             4  HXAAAAAA

--- 30 row(s) selected.
>>
>>
>>----------------
>>-- MERGE JOINS
>>----------------
>>
>>?section qmj1
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Both tables are partitioned 3 ways and part. keys match exactly.
>>-- Both tables must be sorted, since the char columns in the 2nd
>>-- join pred are not key columns.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(scan('T05A'))),sort(exchange(scan('T05B'))),type1));

--- SQL operation complete.
>>prepare P from
+>select T05A.sint32_60,T05B.sint32_60,T05A.char_100,T05B.varchar_100
+>  from  $$P05$$ T05A, $$P05$$ T05B
+>  where     T05A.sint32_60 = T05B.sint32_60
+>        and T05A.char_100 = T05B.varchar_100;

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

SINT32_60    SINT32_60    CHAR_100  VARCHAR_100     
-----------  -----------  --------  ----------------

          0            0  AMAAAAAA  AMAAAAAA        
          2            2  DFAAAAAA  DFAAAAAA        
          3            3  AJAAAAAA  AJAAAAAA        
          4            4  CUAAAAAA  CUAAAAAA        
          9            9  AKAAAAAA  AKAAAAAA        
         11           11  CMAAAAAA  CMAAAAAA        
         14           14  DDAAAAAA  DDAAAAAA        
         15           15  DQAAAAAA  DQAAAAAA        
         22           22  BVAAAAAA  BVAAAAAA        
         24           24  AJAAAAAA  AJAAAAAA        
         25           25  DOAAAAAA  DOAAAAAA        
         27           27  BRAAAAAA  BRAAAAAA        
         27           27  DNAAAAAA  DNAAAAAA        
         31           31  ADAAAAAA  ADAAAAAA        
         31           31  BGAAAAAA  BGAAAAAA        
         32           32  BTAAAAAA  BTAAAAAA        
         33           33  AUAAAAAA  AUAAAAAA        
         34           34  AKAAAAAA  AKAAAAAA        
         35           35  ACAAAAAA  ACAAAAAA        
         40           40  BMAAAAAA  BMAAAAAA        
         42           42  AQAAAAAA  AQAAAAAA        
         43           43  CPAAAAAA  CPAAAAAA        
         44           44  CTAAAAAA  CTAAAAAA        
         46           46  AIAAAAAA  AIAAAAAA        
         48           48  BAAAAAAA  BAAAAAAA        
         48           48  DRAAAAAA  DRAAAAAA        
         49           49  CFAAAAAA  CFAAAAAA        
         50           50  DVAAAAAA  DVAAAAAA        
         51           51  AGAAAAAA  AGAAAAAA        
         55           55  CJAAAAAA  CJAAAAAA        
         56           56  AVAAAAAA  AVAAAAAA        
         57           57  ATAAAAAA  ATAAAAAA        

--- 32 row(s) selected.
>>
>>?section qmj2
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Both tables are part. 3 ways and part. keys match exactly.
>>-- Neither table needs to be sorted.
>>-- Select scalar aggregates to keep the amount of output to a 
>>-- reasonable amount.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape groupby(exchange(merge_join(
+>  exchange(scan('T05A')),exchange(scan('T05B')),type1)));

--- SQL operation complete.
>>prepare P from
+>Select count(*), 
+>       min(T05A.sInt32_60 - T05B.sInt32_60),
+>       max(T05A.sInt32_60 - T05B.sInt32_60)
+>  from  $$P05$$ T05A, $$P05$$ T05B
+>  where T05A.sint32_60 = T05B.sint32_60;

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

(EXPR)                (EXPR)                (EXPR)              
--------------------  --------------------  --------------------

                6000                     0                     0

--- 1 row(s) selected.
>>
>>?section qmj2b
>>-- CPUS = 6
>>-- SQL/MP style PLAN3 parallel two-way join plan using sort merge join
>>-- Both tables are part. 3 ways and part. keys match exactly.
>>-- But, since there are 6 cpus, will need to repartition both
>>-- tables to get 6-way parallelism (3 partitions is not enough
>>-- to satisfy the requirement).
>>-- Both tables need to be sorted, since a merge of sorted streams of
>>-- the repartitioned output cannot be done, due to the risk of deadlock.
>>-- Select scalar aggregates to keep the amount of output to a 
>>-- reasonable amount.
>>----------------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '6'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '6'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape groupby(exchange(merge_join(
+>  sort(exchange(exchange(scan('T05A')))),
+>  sort(exchange(exchange(scan('T05B')))),type1)));

--- SQL operation complete.
>>prepare P from
+>Select count(*), 
+>       min(T05A.sInt32_60 - T05B.sInt32_60),
+>       max(T05A.sInt32_60 - T05B.sInt32_60)
+>from $$P05$$ T05A, $$P05$$ T05B
+>where T05A.sint32_60 = T05B.sint32_60;

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

(EXPR)                (EXPR)                (EXPR)              
--------------------  --------------------  --------------------

                6000                     0                     0

--- 1 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>
>>?section qmj3
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- tables don't match but logical partitioning is possible.
>>-- Both tables are partitioned 3 ways, but the first keys are different.
>>-- Neither table needs to be sorted.
>>-- Should choose 3 ESP plan with logical subpart.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  partition_access(scan('T05')),split_top_pa(scan('T06')),type1));

--- SQL operation complete.
>>prepare P from
+>select T05.sint32_60,T06.uint32_60,T05.uNum_10,T06.sInt16_10
+>  from  $$P05$$ T05, $$P06$$ T06
+>  where     T05.sint32_60 = T06.uint32_60
+>        and T05.uNum_10 >= T06.sInt16_10;

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

SINT32_60    UINT32_60   UNUM_10      SINT16_10
-----------  ----------  -----------  ---------

          0           0          .06          0
          0           0          .00          0
          0           0          .09          0
          0           0          .03          0
          0           0          .02          0
          0           0          .05          0
          0           0          .05          0
          0           0          .09          0
          0           0          .02          0
          0           0          .04          0
          1           1          .01          0
          1           1          .05          0
          1           1          .06          0
          1           1          .02          0
          1           1          .09          0
          1           1          .03          0
          1           1          .01          0
          1           1          .01          0
          1           1          .05          0
          1           1          .08          0
          2           2          .00          0
          2           2          .09          0
          2           2          .00          0
          2           2          .08          0
          2           2          .07          0
          2           2          .03          0
          2           2          .03          0
          2           2          .06          0
          2           2          .08          0
          2           2          .01          0
          3           3          .01          0
          3           3          .05          0
          3           3          .00          0
          3           3          .06          0
          3           3          .09          0
          3           3          .03          0
          3           3          .05          0
          3           3          .03          0
          3           3          .04          0
          3           3          .04          0
          4           4          .06          0
          4           4          .04          0
          4           4          .00          0
          4           4          .00          0
          4           4          .01          0
          4           4          .06          0
          4           4          .02          0
          4           4          .03          0
          4           4          .04          0
          4           4          .02          0
          5           5          .07          0
          5           5          .06          0
          5           5          .04          0
          5           5          .09          0
          5           5          .03          0
          5           5          .06          0
          5           5          .00          0
          5           5          .07          0
          5           5          .04          0
          5           5          .02          0
          6           6          .00          0
          6           6          .03          0
          6           6          .09          0
          6           6          .02          0
          6           6          .02          0
          6           6          .04          0
          6           6          .04          0
          6           6          .02          0
          6           6          .09          0
          6           6          .06          0
          7           7          .07          0
          7           7          .07          0
          7           7          .02          0
          7           7          .07          0
          7           7          .04          0
          7           7          .00          0
          7           7          .07          0
          7           7          .04          0
          7           7          .01          0
          7           7          .05          0
          8           8          .05          0
          8           8          .00          0
          8           8          .00          0
          8           8          .02          0
          8           8          .09          0
          8           8          .04          0
          8           8          .03          0
          8           8          .05          0
          8           8          .03          0
          8           8          .01          0
          9           9          .01          0
          9           9          .00          0
          9           9          .01          0
          9           9          .05          0
          9           9          .07          0
          9           9          .02          0
          9           9          .04          0
          9           9          .01          0
          9           9          .08          0
          9           9          .02          0
         10          10          .02          0
         10          10          .03          0
         10          10          .03          0
         10          10          .00          0
         10          10          .06          0
         10          10          .00          0
         10          10          .04          0
         10          10          .08          0
         10          10          .09          0
         10          10          .08          0
         11          11          .04          0
         11          11          .00          0
         11          11          .05          0
         11          11          .01          0
         11          11          .06          0
         11          11          .01          0
         11          11          .08          0
         11          11          .04          0
         11          11          .04          0
         11          11          .01          0
         12          12          .01          0
         12          12          .03          0
         12          12          .06          0
         12          12          .06          0
         12          12          .09          0
         12          12          .01          0
         12          12          .07          0
         12          12          .02          0
         12          12          .09          0
         12          12          .09          0
         13          13          .01          0
         13          13          .08          0
         13          13          .08          0
         13          13          .09          0
         13          13          .08          0
         13          13          .09          0
         13          13          .08          0
         13          13          .07          0
         13          13          .05          0
         13          13          .07          0
         14          14          .07          0
         14          14          .05          0
         14          14          .07          0
         14          14          .07          0
         14          14          .09          0
         14          14          .01          0
         14          14          .07          0
         14          14          .01          0
         14          14          .00          0
         14          14          .00          0
         15          15          .00          0
         15          15          .05          0
         15          15          .05          0
         15          15          .02          0
         15          15          .08          0
         15          15          .07          0
         15          15          .00          0
         15          15          .06          0
         15          15          .00          0
         15          15          .01          0
         16          16          .03          0
         16          16          .03          0
         16          16          .04          0
         16          16          .00          0
         16          16          .04          0
         16          16          .07          0
         16          16          .05          0
         16          16          .04          0
         16          16          .05          0
         16          16          .02          0
         17          17          .02          0
         17          17          .04          0
         17          17          .03          0
         17          17          .00          0
         17          17          .04          0
         17          17          .04          0
         17          17          .05          0
         17          17          .00          0
         17          17          .05          0
         17          17          .06          0
         18          18          .06          0
         18          18          .08          0
         18          18          .07          0
         18          18          .01          0
         18          18          .09          0
         18          18          .00          0
         18          18          .02          0
         18          18          .02          0
         18          18          .09          0
         18          18          .08          0
         19          19          .04          0
         19          19          .06          0
         19          19          .04          0
         19          19          .06          0
         19          19          .09          0
         19          19          .06          0
         19          19          .00          0
         19          19          .00          0
         19          19          .08          0
         19          19          .04          0
         20          20          .09          0
         20          20          .09          0
         20          20          .08          0
         20          20          .03          0
         20          20          .05          0
         20          20          .07          0
         20          20          .03          0
         20          20          .08          0
         20          20          .07          0
         20          20          .08          0
         21          21          .08          0
         21          21          .04          0
         21          21          .05          0
         21          21          .08          0
         21          21          .05          0
         21          21          .05          0
         21          21          .06          0
         21          21          .01          0
         21          21          .03          0
         21          21          .02          0
         22          22          .07          0
         22          22          .03          0
         22          22          .08          0
         22          22          .04          0
         22          22          .05          0
         22          22          .01          0
         22          22          .08          0
         22          22          .08          0
         22          22          .05          0
         22          22          .09          0
         23          23          .03          0
         23          23          .00          0
         23          23          .01          0
         23          23          .05          0
         23          23          .01          0
         23          23          .08          0
         23          23          .07          0
         23          23          .02          0
         23          23          .02          0
         23          23          .03          0
         24          24          .05          0
         24          24          .03          0
         24          24          .07          0
         24          24          .02          0
         24          24          .06          0
         24          24          .06          0
         24          24          .01          0
         24          24          .02          0
         24          24          .02          0
         24          24          .01          0
         25          25          .00          0
         25          25          .08          0
         25          25          .00          0
         25          25          .09          0
         25          25          .09          0
         25          25          .09          0
         25          25          .03          0
         25          25          .04          0
         25          25          .04          0
         25          25          .06          0
         26          26          .06          0
         26          26          .04          0
         26          26          .04          0
         26          26          .06          0
         26          26          .06          0
         26          26          .04          0
         26          26          .07          0
         26          26          .03          0
         26          26          .01          0
         26          26          .03          0
         27          27          .02          0
         27          27          .03          0
         27          27          .08          0
         27          27          .05          0
         27          27          .02          0
         27          27          .02          0
         27          27          .05          0
         27          27          .01          0
         27          27          .08          0
         27          27          .06          0
         28          28          .06          0
         28          28          .02          0
         28          28          .00          0
         28          28          .06          0
         28          28          .08          0
         28          28          .01          0
         28          28          .07          0
         28          28          .09          0
         28          28          .06          0
         28          28          .06          0
         29          29          .01          0
         29          29          .05          0
         29          29          .05          0
         29          29          .02          0
         29          29          .00          0
         29          29          .03          0
         29          29          .00          0
         29          29          .02          0
         29          29          .08          0
         29          29          .04          0
         30          30          .03          0
         30          30          .09          0
         30          30          .05          0
         30          30          .01          0
         30          30          .07          0
         30          30          .06          0
         30          30          .03          0
         30          30          .09          0
         30          30          .00          0
         30          30          .00          0
         31          31          .02          0
         31          31          .08          0
         31          31          .00          0
         31          31          .05          0
         31          31          .09          0
         31          31          .09          0
         31          31          .02          0
         31          31          .04          0
         31          31          .01          0
         31          31          .01          0
         32          32          .09          0
         32          32          .04          0
         32          32          .05          0
         32          32          .09          0
         32          32          .03          0
         32          32          .07          0
         32          32          .08          0
         32          32          .03          0
         32          32          .01          0
         32          32          .00          0
         33          33          .06          0
         33          33          .01          0
         33          33          .04          0
         33          33          .07          0
         33          33          .06          0
         33          33          .03          0
         33          33          .07          0
         33          33          .07          0
         33          33          .01          0
         33          33          .08          0
         34          34          .00          0
         34          34          .08          0
         34          34          .03          0
         34          34          .00          0
         34          34          .09          0
         34          34          .08          0
         34          34          .09          0
         34          34          .05          0
         34          34          .02          0
         34          34          .07          0
         35          35          .06          0
         35          35          .08          0
         35          35          .05          0
         35          35          .08          0
         35          35          .06          0
         35          35          .06          0
         35          35          .09          0
         35          35          .02          0
         35          35          .07          0
         35          35          .01          0
         36          36          .05          0
         36          36          .00          0
         36          36          .03          0
         36          36          .06          0
         36          36          .08          0
         36          36          .08          0
         36          36          .04          0
         36          36          .05          0
         36          36          .00          0
         36          36          .01          0
         37          37          .08          0
         37          37          .04          0
         37          37          .06          0
         37          37          .07          0
         37          37          .04          0
         37          37          .01          0
         37          37          .02          0
         37          37          .09          0
         37          37          .09          0
         37          37          .09          0
         38          38          .06          0
         38          38          .00          0
         38          38          .05          0
         38          38          .00          0
         38          38          .04          0
         38          38          .07          0
         38          38          .04          0
         38          38          .02          0
         38          38          .07          0
         38          38          .05          0
         39          39          .04          0
         39          39          .06          0
         39          39          .00          0
         39          39          .08          0
         39          39          .02          0
         39          39          .01          0
         39          39          .05          0
         39          39          .00          0
         39          39          .01          0
         39          39          .05          0
         40          40          .00          0
         40          40          .00          0
         40          40          .09          0
         40          40          .08          0
         40          40          .01          0
         40          40          .04          0
         40          40          .08          0
         40          40          .07          0
         40          40          .08          0
         40          40          .05          0
         41          41          .06          0
         41          41          .00          0
         41          41          .08          0
         41          41          .02          0
         41          41          .02          0
         41          41          .06          0
         41          41          .05          0
         41          41          .09          0
         41          41          .03          0
         41          41          .09          0
         42          42          .02          0
         42          42          .07          0
         42          42          .08          0
         42          42          .06          0
         42          42          .02          0
         42          42          .02          0
         42          42          .06          0
         42          42          .09          0
         42          42          .04          0
         42          42          .08          0
         43          43          .05          0
         43          43          .02          0
         43          43          .00          0
         43          43          .03          0
         43          43          .01          0
         43          43          .03          0
         43          43          .07          0
         43          43          .02          0
         43          43          .05          0
         43          43          .02          0
         44          44          .02          0
         44          44          .03          0
         44          44          .00          0
         44          44          .08          0
         44          44          .09          0
         44          44          .04          0
         44          44          .04          0
         44          44          .01          0
         44          44          .07          0
         44          44          .07          0
         45          45          .02          0
         45          45          .03          0
         45          45          .09          0
         45          45          .03          0
         45          45          .03          0
         45          45          .02          0
         45          45          .06          0
         45          45          .01          0
         45          45          .05          0
         45          45          .02          0
         46          46          .00          0
         46          46          .02          0
         46          46          .04          0
         46          46          .07          0
         46          46          .09          0
         46          46          .00          0
         46          46          .03          0
         46          46          .03          0
         46          46          .07          0
         46          46          .02          0
         47          47          .07          0
         47          47          .09          0
         47          47          .06          0
         47          47          .09          0
         47          47          .04          0
         47          47          .05          0
         47          47          .03          0
         47          47          .00          0
         47          47          .05          0
         47          47          .03          0
         48          48          .06          0
         48          48          .00          0
         48          48          .09          0
         48          48          .09          0
         48          48          .07          0
         48          48          .01          0
         48          48          .00          0
         48          48          .01          0
         48          48          .00          0
         48          48          .03          0
         49          49          .00          0
         49          49          .07          0
         49          49          .01          0
         49          49          .09          0
         49          49          .09          0
         49          49          .05          0
         49          49          .08          0
         49          49          .07          0
         49          49          .03          0
         49          49          .02          0
         50          50          .07          0
         50          50          .06          0
         50          50          .04          0
         50          50          .03          0
         50          50          .04          0
         50          50          .06          0
         50          50          .01          0
         50          50          .01          0
         50          50          .00          0
         50          50          .05          0
         51          51          .06          0
         51          51          .05          0
         51          51          .07          0
         51          51          .07          0
         51          51          .09          0
         51          51          .04          0
         51          51          .08          0
         51          51          .04          0
         51          51          .06          0
         51          51          .05          0
         52          52          .06          0
         52          52          .06          0
         52          52          .03          0
         52          52          .07          0
         52          52          .09          0
         52          52          .05          0
         52          52          .02          0
         52          52          .03          0
         52          52          .08          0
         52          52          .04          0
         53          53          .09          0
         53          53          .08          0
         53          53          .07          0
         53          53          .06          0
         53          53          .08          0
         53          53          .07          0
         53          53          .08          0
         53          53          .01          0
         53          53          .03          0
         53          53          .08          0
         54          54          .09          0
         54          54          .04          0
         54          54          .07          0
         54          54          .08          0
         54          54          .05          0
         54          54          .01          0
         54          54          .09          0
         54          54          .05          0
         54          54          .06          0
         54          54          .08          0
         55          55          .05          0
         55          55          .03          0
         55          55          .03          0
         55          55          .01          0
         55          55          .00          0
         55          55          .07          0
         55          55          .01          0
         55          55          .02          0
         55          55          .09          0
         55          55          .04          0
         56          56          .04          0
         56          56          .07          0
         56          56          .07          0
         56          56          .07          0
         56          56          .03          0
         56          56          .06          0
         56          56          .03          0
         56          56          .01          0
         56          56          .04          0
         56          56          .01          0
         57          57          .06          0
         57          57          .07          0
         57          57          .01          0
         57          57          .00          0
         57          57          .00          0
         57          57          .02          0
         57          57          .07          0
         57          57          .08          0
         57          57          .02          0
         57          57          .08          0
         58          58          .08          0
         58          58          .04          0
         58          58          .02          0
         58          58          .03          0
         58          58          .06          0
         58          58          .03          0
         58          58          .07          0
         58          58          .09          0
         58          58          .02          0
         58          58          .05          0
         59          59          .06          0
         59          59          .09          0
         59          59          .04          0
         59          59          .03          0
         59          59          .01          0
         59          59          .05          0
         59          59          .01          0
         59          59          .08          0
         59          59          .05          0
         59          59          .09          0

--- 600 row(s) selected.
>>
>>?section qmj4
>>-- CPUS=4
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join.
>>-- Tables don't match but logical partitioning is possible,       
>>-- since we are joining a 3-way partitioned table with a 4-way 
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>-- Neither table needs to be sorted.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  exchange(scan('T08')),exchange(scan('T09')),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.int64_6 = T09.int64_6
+>        and T08.sInt16_10 = T09.sInt16_10
+>        and T08.uInt16_10 = T09.uInt16_10
+>        and T08.sInt32_100 < 5;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   0                     0          9          9          6          6            1
                   0                     0          7          7          7          7            3
                   0                     0          3          3          2          2            3
                   1                     1          6          6          7          7            0
                   1                     1          3          3          6          6            4
                   1                     1          3          3          9          9            1
                   1                     1          1          1          0          0            3
                   1                     1          1          1          7          7            1
                   1                     1          0          0          5          5            1
                   2                     2          9          9          5          5            2
                   2                     2          7          7          6          6            4
                   2                     2          3          3          2          2            0
                   2                     2          0          0          0          0            0
                   2                     2          0          0          3          3            4
                   3                     3          7          7          0          0            2
                   3                     3          4          4          8          8            0
                   3                     3          3          3          0          0            2
                   3                     3          2          2          6          6            2
                   3                     3          1          1          6          6            4
                   3                     3          0          0          4          4            3
                   4                     4          6          6          3          3            4
                   4                     4          4          4          0          0            1
                   4                     4          3          3          5          5            3
                   4                     4          2          2          3          3            3
                   5                     5          4          4          3          3            2
                   5                     5          4          4          8          8            4
                   5                     5          2          2          0          0            2
                   5                     5          1          1          4          4            0
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0

--- 30 row(s) selected.
>>
>>?section qmj4b
>>-- CPUS=3
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join.
>>-- Tables don't match but logical partitioning is possible,       
>>-- since we are joining a 3-way partitioned table with a 4-way 
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>-- Neither table needs to be sorted.
>>----------------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '3'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '3'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  split_top_pa(scan('T08')),partition_access(scan('T09')),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from  $$P08$$ T08, $$P09$$ T09
+>  where T08.int64_6 = T09.int64_6
+>        and T08.sInt16_10 = T09.sInt16_10
+>        and T08.uInt16_10 = T09.uInt16_10
+>        and T08.sInt32_100 < 5;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   2                     2          9          9          5          5            2
                   0                     0          9          9          6          6            1
                   2                     2          7          7          6          6            4
                   0                     0          7          7          7          7            3
                   2                     2          3          3          2          2            0
                   0                     0          3          3          2          2            3
                   2                     2          0          0          0          0            0
                   1                     1          6          6          7          7            0
                   2                     2          0          0          3          3            4
                   1                     1          3          3          6          6            4
                   3                     3          7          7          0          0            2
                   1                     1          3          3          9          9            1
                   3                     3          4          4          8          8            0
                   1                     1          1          1          0          0            3
                   3                     3          3          3          0          0            2
                   1                     1          1          1          7          7            1
                   3                     3          2          2          6          6            2
                   1                     1          0          0          5          5            1
                   3                     3          1          1          6          6            4
                   3                     3          0          0          4          4            3
                   4                     4          6          6          3          3            4
                   4                     4          4          4          0          0            1
                   4                     4          3          3          5          5            3
                   4                     4          2          2          3          3            3
                   5                     5          4          4          3          3            2
                   5                     5          4          4          8          8            4
                   5                     5          2          2          0          0            2
                   5                     5          1          1          4          4            0
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0

--- 30 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>?section qmj5
>>-- CPUS=4
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must range repartition T08,
>>-- since it is not partitioned on the join columns.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- With # of cpus = 4, should choose 4 ESP plan 
>>-- With # of cpus = 3, should choose 3 ESP plan - BUT:
>>-- Should the optimizer use partition grouping on the 4 partition
>>-- table to get the number of partitions to be <= # of cpus, and 
>>-- then range repartition T08, OR 
>>-- should the optimizer hash repartition both tables? The partition
>>-- grouping plan will lead to an inbalance, so maybe the 
>>-- repartitioning plan is better.  The c.q. shape forces the 
>>-- first alternative - we'll have to try it both ways and see
>>-- which is better.
>>--
>>-- Only T08 must be sorted, unless the optimizer hash repartitions
>>-- both tables.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  exchange(scan('T09')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from  $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>;

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

SINT32_100   INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10
-----------  --------------------  ---------  ---------  ---------  ---------

          0                     0          6          6          7          7
          0                     0          4          4          8          8
          0                     0          3          3          2          2
          0                     0          1          1          4          4
          0                     0          0          0          0          0
          0                     0          0          0          3          3
          1                     1          9          9          6          6
          1                     1          4          4          0          0
          1                     1          3          3          9          9
          1                     1          1          1          7          7
          1                     1          0          0          0          0
          1                     1          0          0          5          5
          2                     2          9          9          5          5
          2                     2          7          7          0          0
          2                     2          4          4          3          3
          2                     2          3          3          0          0
          2                     2          2          2          0          0
          2                     2          2          2          6          6
          3                     3          7          7          7          7
          3                     3          3          3          2          2
          3                     3          3          3          5          5
          3                     3          2          2          3          3
          3                     3          1          1          0          0
          3                     3          0          0          4          4
          4                     4          7          7          6          6
          4                     4          6          6          3          3
          4                     4          4          4          8          8
          4                     4          3          3          6          6
          4                     4          1          1          6          6
          4                     4          0          0          3          3
          5                     5          8          8          0          0
          5                     5          8          8          3          3
          5                     5          7          7          6          6
          5                     5          6          6          0          0
          5                     5          6          6          7          7
          5                     5          2          2          2          2

--- 36 row(s) selected.
>>
>>?section qmj5b
>>-- CPUS=3
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must range repartition T08,
>>-- since it is not partitioned on the join columns.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- With # of cpus = 4, should choose 4 ESP plan 
>>-- With # of cpus = 3, should choose 3 ESP plan - BUT:
>>-- Should the optimizer use partition grouping on the 4 partition
>>-- table to get the number of partitions to be <= # of cpus, and 
>>-- then range repartition T08, OR 
>>-- should the optimizer hash repartition both tables? The partition
>>-- grouping plan will lead to an inbalance, so maybe the 
>>-- repartitioning plan is better.  The c.q. shape forces the 
>>-- first alternative - we'll have to try it both ways and see
>>-- which is better.
>>--
>>-- Only T08 must be sorted, unless the optimizer hash repartitions
>>-- both tables.
>>----------------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '3'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '3'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  exchange(scan('T09')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>;

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

SINT32_100   INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10
-----------  --------------------  ---------  ---------  ---------  ---------

          0                     0          6          6          7          7
          0                     0          4          4          8          8
          0                     0          3          3          2          2
          0                     0          1          1          4          4
          0                     0          0          0          0          0
          0                     0          0          0          3          3
          1                     1          9          9          6          6
          1                     1          4          4          0          0
          1                     1          3          3          9          9
          1                     1          1          1          7          7
          1                     1          0          0          0          0
          1                     1          0          0          5          5
          2                     2          9          9          5          5
          2                     2          7          7          0          0
          2                     2          4          4          3          3
          2                     2          3          3          0          0
          2                     2          2          2          0          0
          2                     2          2          2          6          6
          3                     3          7          7          7          7
          3                     3          3          3          2          2
          3                     3          3          3          5          5
          3                     3          2          2          3          3
          3                     3          1          1          0          0
          3                     3          0          0          4          4
          4                     4          7          7          6          6
          4                     4          6          6          3          3
          4                     4          4          4          8          8
          4                     4          3          3          6          6
          4                     4          1          1          6          6
          4                     4          0          0          3          3
          5                     5          8          8          0          0
          5                     5          8          8          3          3
          5                     5          7          7          6          6
          5                     5          6          6          0          0
          5                     5          6          6          7          7
          5                     5          2          2          2          2

--- 36 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>?section qmj6
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must range repartition T09,
>>-- since it is not partitioned on the join columns.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- Should choose 3 ESP plan.
>>--
>>-- Only right child must be sorted.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  exchange(scan('T08')),
+>  sort(exchange(exchange(scan('T09')))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.sInt32_100,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.int64_6   = T09.sInt32_100
+>        and T08.sInt16_10 = T09.sInt16_10
+>        and T08.uInt16_10 = T09.uInt16_10
+>;

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

INT64_6               SINT32_100   SINT16_10  SINT16_10  UINT16_10  UINT16_10
--------------------  -----------  ---------  ---------  ---------  ---------

                   2            2          9          9          5          5
                   2            2          7          7          0          0
                   2            2          4          4          3          3
                   2            2          3          3          0          0
                   2            2          2          2          0          0
                   2            2          2          2          6          6
                   3            3          7          7          7          7
                   3            3          3          3          2          2
                   3            3          3          3          5          5
                   3            3          2          2          3          3
                   3            3          1          1          0          0
                   3            3          0          0          4          4
                   4            4          7          7          6          6
                   4            4          6          6          3          3
                   4            4          4          4          8          8
                   4            4          3          3          6          6
                   4            4          1          1          6          6
                   4            4          0          0          3          3
                   5            5          8          8          0          0
                   5            5          8          8          3          3
                   5            5          7          7          6          6
                   5            5          6          6          0          0
                   5            5          6          6          7          7
                   5            5          2          2          2          2
                   0            0          6          6          7          7
                   0            0          4          4          8          8
                   0            0          3          3          2          2
                   0            0          1          1          4          4
                   0            0          0          0          0          0
                   0            0          0          0          3          3
                   1            1          9          9          6          6
                   1            1          4          4          0          0
                   1            1          3          3          9          9
                   1            1          1          1          7          7
                   1            1          0          0          0          0
                   1            1          0          0          5          5

--- 36 row(s) selected.
>>
>>?section qmj7
>>-- CPUS=4
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must hash repartition both children,
>>-- since neither is partitioned on the join columns.
>>-- Both children must be sorted.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- With # of cpus = 4, should choose 4 ESP plan 
>>-- With # of cpus = 3, should choose 3 ESP plan
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  sort(exchange(exchange(scan('T09')))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.uInt32_100,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.uInt32_100
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>;

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

SINT32_100   UINT32_100  SINT16_10  SINT16_10  UINT16_10  UINT16_10
-----------  ----------  ---------  ---------  ---------  ---------

          7           7          4          4          7          7
          0           0          1          1          4          4
         19          19          4          4          4          4
         10          10          1          1          4          4
         41          41          7          7          8          8
         12          12          0          0          2          2
         45          45          0          0          7          7
         12          12          4          4          9          9
         51          51          7          7          4          4
         16          16          5          5          1          1
         55          55          1          1          2          2
         22          22          7          7          0          0
         59          59          0          0          6          6
         22          22          8          8          1          1
         69          69          9          9          0          0
         30          30          9          9          3          3
         83          83          9          9          7          7
         38          38          9          9          8          8
         97          97          1          1          9          9
         42          42          7          7          9          9
         50          50          7          7          9          9
         54          54          3          3          9          9
         56          56          2          2          7          7
         66          66          0          0          5          5
         70          70          3          3          1          1
         70          70          9          9          1          1
         88          88          1          1          4          4

--- 27 row(s) selected.
>>
>>?section qmj7b
>>-- CPUS=3
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must hash repartition both children,
>>-- since neither is partitioned on the join columns.
>>-- Both children must be sorted.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- With # of cpus = 4, should choose 4 ESP plan 
>>-- With # of cpus = 3, should choose 3 ESP plan
>>----------------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '3'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '3'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  sort(exchange(exchange(scan('T09')))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.uInt32_100,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.uInt32_100
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>;

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

SINT32_100   UINT32_100  SINT16_10  SINT16_10  UINT16_10  UINT16_10
-----------  ----------  ---------  ---------  ---------  ---------

          7           7          4          4          7          7
          0           0          1          1          4          4
         19          19          4          4          4          4
         10          10          1          1          4          4
         41          41          7          7          8          8
         12          12          0          0          2          2
         45          45          0          0          7          7
         12          12          4          4          9          9
         51          51          7          7          4          4
         16          16          5          5          1          1
         55          55          1          1          2          2
         22          22          7          7          0          0
         59          59          0          0          6          6
         22          22          8          8          1          1
         69          69          9          9          0          0
         30          30          9          9          3          3
         83          83          9          9          7          7
         38          38          9          9          8          8
         97          97          1          1          9          9
         42          42          7          7          9          9
         50          50          7          7          9          9
         54          54          3          3          9          9
         56          56          2          2          7          7
         66          66          0          0          5          5
         70          70          3          3          1          1
         70          70          9          9          1          1
         88          88          1          1          4          4

--- 27 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>
>>-- 3 WAY MERGE JOINS
>>
>>?section qmj8
>>-- PLAN1 M.J. over PLAN1 M.J. three way join plan
>>-- All three tables are partitioned 3 ways and their partitioning
>>-- keys match exactly (3-3-3).
>>-- None of the children need to be sorted.
>>------------------------------------------------------------------------
>>--control query shape exchange(merge_join(merge_join(cut,cut),cut));
>>control query shape exchange(merge_join(
+>  merge_join(exchange(scan('T05A')),
+>             exchange(scan('T05B')),
+>             type1),
+>  exchange(scan('T05C')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T05A.sInt32_60,T05B.sInt32_60,T05C.sInt32_60,
+>       T05C.sNum_uniq,T05C.int64_uniq
+>  from $$P05$$ T05A, $$P05$$ T05B, $$P05$$ T05C
+>  where T05A.sInt32_60 = T05B.sInt32_60 
+>        and T05B.sInt32_60 = T05C.sInt32_60
+>        and T05B.sNum_uniq < .6
+>        and T05C.int64_uniq < 6;

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

SINT32_60    SINT32_60    SINT32_60    SNUM_UNIQ     INT64_UNIQ
-----------  -----------  -----------  ------------  --------------------

          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          0            0            0          1.67                     3
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
          6            6            6          2.74                     1
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         12           12           12           .99                     0
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         19           19           19           .29                     5
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         24           24           24           .38                     4
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2
         25           25           25          5.72                     2

--- 90 row(s) selected.
>>
>>
>>?section qmj9
>>-- PLAN1 M.J. over PLAN3 M.J. 
>>-- Must range repartition right child of the Plan 3 (T11),
>>-- since it is not partitioned on the join columns.
>>-- Select scalar aggregates to keep the amount of output to a 
>>-- reasonable amount.
>>-- Right child of the Plan 3 (T11) needs to be sorted.
>>-- 4-4-4
>>----------------------------------------------------------------
>>--control query shape groupby(exchange(merge_join(merge_join(cut,cut),cut)));
>>control query shape groupby(exchange(merge_join(
+>  merge_join(exchange(scan('T07A')),
+>             sort(exchange(exchange(scan('T11')))),
+>             type1),
+>  exchange(scan('T07B')),
+>  type1)));

--- SQL operation complete.
>>prepare P from
+>Select count(*), 
+>       min(T07A.int64_60 - T11.uInt32_100),
+>       max(T07A.int64_60 - T11.uInt32_100),
+>       min(T07A.sInt16_10 - T11.uInt16_10),
+>       max(T07A.sInt16_10 - T11.uInt16_10),
+>       min(T07A.int64_60 - T07B.int64_60),
+>       max(T07A.int64_60 - T07B.int64_60),
+>       min(T07A.sInt16_10 - T07B.sInt16_10),
+>       max(T07A.sInt16_10 - T07B.sInt16_10)
+>  from $$P07$$ T07A, $$P11$$ T11, $$P07$$ T07B
+>  where     T07A.int64_60 = T11.uInt32_100
+>        and T07A.sInt16_10 = T11.uInt16_10
+>        and T07A.int64_60 = T07B.int64_60
+>        and T07A.sInt16_10 = T07B.sInt16_10;

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

(EXPR)                (EXPR)                (EXPR)                (EXPR)       (EXPR)       (EXPR)                (EXPR)                (EXPR)       (EXPR)
--------------------  --------------------  --------------------  -----------  -----------  --------------------  --------------------  -----------  -----------

                 360                     0                     0            0            0                     0                     0            0            0

--- 1 row(s) selected.
>>
>>
>>?section qmj10
>>-- PLAN3 M.J. over PLAN3 M.J.
>>-- Must hash repartition all children, 
>>-- since none are partitioned on the join columns.
>>-- All children must be sorted.
>>-- 3-3-3
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(merge_join(cut,cut),cut));
>>control query shape exchange(merge_join(
+>  merge_join(sort(exchange(exchange(scan('T05')))),
+>             sort(exchange(exchange(scan('T06')))),
+>             type1),
+>  sort(exchange(exchange(scan('T08')))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T06.sInt32_100,T08.sInt16_10,T05.date_200,
+>       T06.date_200,T08.date_200
+>  from $$P05$$ T05, $$P06$$ T06, $$P08$$ T08
+>  where T05.date_200 = T06.date_200
+>        and T06.date_200   = T08.date_200
+>        and T06.sInt32_100 = 4
+>        and T08.sInt16_10  = 4;

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

SINT32_100   SINT16_10  DATE_200    DATE_200    DATE_200  
-----------  ---------  ----------  ----------  ----------

          4          4  2100-03-25  2100-03-25  2100-03-25
          4          4  2100-03-25  2100-03-25  2100-03-25
          4          4  2100-03-25  2100-03-25  2100-03-25
          4          4  2100-06-06  2100-06-06  2100-06-06
          4          4  2100-06-06  2100-06-06  2100-06-06
          4          4  2100-06-06  2100-06-06  2100-06-06

--- 6 row(s) selected.
>>
>>
>>-- MERGE JOINS + ORDER BY
>>
>>?section qmj11
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Tables don't match but logical partitioning is possible,
>>-- since we are joining 3-way partitioned table with 4-way 
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>--
>>-- Order by clause on all three key columns, in key column order.
>>-- Order by colunns and join columns are the same.
>>-- Neither table needs to be sorted.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  partition_access(scan('T08')),split_top_pa(scan('T09')),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.int64_6    = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>        and T08.sInt32_100 < 5
+>  order by T08.int64_6,T08.sInt16_10 DESC, T08.uInt16_10;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   0                     0          9          9          6          6            1
                   0                     0          7          7          7          7            3
                   0                     0          3          3          2          2            3
                   1                     1          6          6          7          7            0
                   1                     1          3          3          6          6            4
                   1                     1          3          3          9          9            1
                   1                     1          1          1          0          0            3
                   1                     1          1          1          7          7            1
                   1                     1          0          0          5          5            1
                   2                     2          9          9          5          5            2
                   2                     2          7          7          6          6            4
                   2                     2          3          3          2          2            0
                   2                     2          0          0          0          0            0
                   2                     2          0          0          3          3            4
                   3                     3          7          7          0          0            2
                   3                     3          4          4          8          8            0
                   3                     3          3          3          0          0            2
                   3                     3          2          2          6          6            2
                   3                     3          1          1          6          6            4
                   3                     3          0          0          4          4            3
                   4                     4          6          6          3          3            4
                   4                     4          4          4          0          0            1
                   4                     4          3          3          5          5            3
                   4                     4          2          2          3          3            3
                   5                     5          4          4          3          3            2
                   5                     5          4          4          8          8            4
                   5                     5          2          2          0          0            2
                   5                     5          1          1          4          4            0
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0

--- 30 row(s) selected.
>>
>>?section qmj12
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Tables don't match but logical partitioning is possible,
>>-- since we are joining 3-way partitioned table with 4-way 
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>--
>>-- Order by clause on all first two key columns, in key column order.
>>-- One more join column than o.b. columns - should be able to use the
>>-- predicate it is from as a join predicate.
>>-- Neither table needs to be sorted.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  partition_access(scan('T08')),split_top_pa(scan('T09')),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.int64_6    = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>        and T08.sInt32_100 < 5
+>  order by T08.int64_6,T08.sInt16_10 DESC;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   0                     0          9          9          6          6            1
                   0                     0          7          7          7          7            3
                   0                     0          3          3          2          2            3
                   1                     1          6          6          7          7            0
                   1                     1          3          3          6          6            4
                   1                     1          3          3          9          9            1
                   1                     1          1          1          0          0            3
                   1                     1          1          1          7          7            1
                   1                     1          0          0          5          5            1
                   2                     2          9          9          5          5            2
                   2                     2          7          7          6          6            4
                   2                     2          3          3          2          2            0
                   2                     2          0          0          0          0            0
                   2                     2          0          0          3          3            4
                   3                     3          7          7          0          0            2
                   3                     3          4          4          8          8            0
                   3                     3          3          3          0          0            2
                   3                     3          2          2          6          6            2
                   3                     3          1          1          6          6            4
                   3                     3          0          0          4          4            3
                   4                     4          6          6          3          3            4
                   4                     4          4          4          0          0            1
                   4                     4          3          3          5          5            3
                   4                     4          2          2          3          3            3
                   5                     5          4          4          3          3            2
                   5                     5          4          4          8          8            4
                   5                     5          2          2          0          0            2
                   5                     5          1          1          4          4            0
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0

--- 30 row(s) selected.
>>
>>?section qmj13
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Tables don't match but logical partitioning is possible,
>>-- since we are joining 3-way partitioned table with 4-way 
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>--
>>-- Order by clause on the first key column.
>>-- Two more join columns than o.b. columns - should be able to use the
>>-- predicates they are from as join predicates.
>>-- Neither table needs to be sorted.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  partition_access(scan('T08')),split_top_pa(scan('T09')),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from $$P08$$ T08, $$P09$$ T09
+>  where T08.int64_6        = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>        and T08.sInt32_100 < 5
+>  order by T08.int64_6;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   0                     0          9          9          6          6            1
                   0                     0          7          7          7          7            3
                   0                     0          3          3          2          2            3
                   1                     1          6          6          7          7            0
                   1                     1          3          3          6          6            4
                   1                     1          3          3          9          9            1
                   1                     1          1          1          0          0            3
                   1                     1          1          1          7          7            1
                   1                     1          0          0          5          5            1
                   2                     2          9          9          5          5            2
                   2                     2          7          7          6          6            4
                   2                     2          3          3          2          2            0
                   2                     2          0          0          0          0            0
                   2                     2          0          0          3          3            4
                   3                     3          7          7          0          0            2
                   3                     3          4          4          8          8            0
                   3                     3          3          3          0          0            2
                   3                     3          2          2          6          6            2
                   3                     3          1          1          6          6            4
                   3                     3          0          0          4          4            3
                   4                     4          6          6          3          3            4
                   4                     4          4          4          0          0            1
                   4                     4          3          3          5          5            3
                   4                     4          2          2          3          3            3
                   5                     5          4          4          3          3            2
                   5                     5          4          4          8          8            4
                   5                     5          2          2          0          0            2
                   5                     5          1          1          4          4            0
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0

--- 30 row(s) selected.
>>
>>?section qmj14
>>-- SQL/MP style PLAN3 parallel two-way join plan using sort merge join
>>--
>>-- Order by clause on the first key column, a non-key column, and 
>>-- the second key column.
>>-- The second o.b. column is also not a join predicate column, so
>>-- can only use one join predicate - the one on the first o.b. column.
>>-- Must repartition and sort both tables.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  sort(exchange(exchange(scan('T09')))),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.int64_6    = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>        and T08.sInt32_100 < 5
+>  order by T08.int64_6,T08.char_10,T08.sInt16_10 DESC;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   0                     0          3          3          2          2            3
                   0                     0          7          7          7          7            3
                   0                     0          9          9          6          6            1
                   1                     1          3          3          6          6            4
                   1                     1          1          1          0          0            3
                   1                     1          6          6          7          7            0
                   1                     1          3          3          9          9            1
                   1                     1          0          0          5          5            1
                   1                     1          1          1          7          7            1
                   2                     2          9          9          5          5            2
                   2                     2          0          0          0          0            0
                   2                     2          0          0          3          3            4
                   2                     2          3          3          2          2            0
                   2                     2          7          7          6          6            4
                   3                     3          0          0          4          4            3
                   3                     3          7          7          0          0            2
                   3                     3          3          3          0          0            2
                   3                     3          1          1          6          6            4
                   3                     3          4          4          8          8            0
                   3                     3          2          2          6          6            2
                   4                     4          4          4          0          0            1
                   4                     4          6          6          3          3            4
                   4                     4          3          3          5          5            3
                   4                     4          2          2          3          3            3
                   5                     5          1          1          4          4            0
                   5                     5          2          2          0          0            2
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0
                   5                     5          4          4          8          8            4
                   5                     5          4          4          3          3            2

--- 30 row(s) selected.
>>
>>?section qmj15
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Tables don't match but logical partitioning is possible,
>>-- since we are joining 3-way partitioned table with 4-way 
>>-- partitioned table on the key columns.
>>-- With # of cpus = 4, should choose 4 ESP plan with logical subpart.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical subpart
>>--
>>-- Order by clause on the second key column.
>>-- Will have to sort both tables to satisfy the order by.
>>-- Should be able to use all join predicates.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(scan('T08'))),
+>  sort(exchange(scan('T09'))),type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.int64_6, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10,
+>       T08.sInt32_100
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.int64_6 = T09.int64_6
+>        and T08.sInt16_10 = T09.sInt16_10
+>        and T08.uInt16_10 = T09.uInt16_10
+>        and T08.sInt32_100 < 5
+>  order by T08.sInt16_10 DESC;

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

INT64_6               INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10  SINT32_100
--------------------  --------------------  ---------  ---------  ---------  ---------  -----------

                   0                     0          9          9          6          6            1
                   2                     2          9          9          5          5            2
                   0                     0          7          7          7          7            3
                   2                     2          7          7          6          6            4
                   3                     3          7          7          0          0            2
                   1                     1          6          6          7          7            0
                   4                     4          6          6          3          3            4
                   3                     3          4          4          8          8            0
                   4                     4          4          4          0          0            1
                   5                     5          4          4          3          3            2
                   5                     5          4          4          8          8            4
                   0                     0          3          3          2          2            3
                   1                     1          3          3          6          6            4
                   1                     1          3          3          9          9            1
                   2                     2          3          3          2          2            0
                   3                     3          3          3          0          0            2
                   4                     4          3          3          5          5            3
                   3                     3          2          2          6          6            2
                   4                     4          2          2          3          3            3
                   5                     5          2          2          0          0            2
                   1                     1          1          1          0          0            3
                   1                     1          1          1          7          7            1
                   3                     3          1          1          6          6            4
                   5                     5          1          1          4          4            0
                   1                     1          0          0          5          5            1
                   2                     2          0          0          0          0            0
                   2                     2          0          0          3          3            4
                   3                     3          0          0          4          4            3
                   5                     5          0          0          0          0            1
                   5                     5          0          0          3          3            0

--- 30 row(s) selected.
>>
>>?section qmj16
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must range repartition T08,
>>-- since it is not partitioned on the join columns.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- With # of cpus = 4, should choose 4 ESP plan 
>>-- With # of cpus = 3, should choose 3 ESP plan - BUT:
>>-- Should the optimizer use partition grouping on the 4 partition
>>-- table to get the number of partitions to be <= # of cpus, and 
>>-- then range repartition T08, OR 
>>-- should the optimizer hash repartition both tables? The partition
>>-- grouping plan will lead to an inbalance, so maybe the 
>>-- repartitioning plan is better.  The c.q. shape forces the 
>>-- first alternative - we'll have to try it both ways and see
>>-- which is better.
>>--
>>-- Order by clause on the two left child columns that correspond 
>>-- to the first two columns of the right hand table primary key.
>>-- Only left child must be sorted.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  exchange(scan('T09')),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>  order by T08.sInt32_100,T08.sInt16_10 DESC
+>;

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

SINT32_100   INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10
-----------  --------------------  ---------  ---------  ---------  ---------

          0                     0          6          6          7          7
          0                     0          4          4          8          8
          0                     0          3          3          2          2
          0                     0          1          1          4          4
          0                     0          0          0          0          0
          0                     0          0          0          3          3
          1                     1          9          9          6          6
          1                     1          4          4          0          0
          1                     1          3          3          9          9
          1                     1          1          1          7          7
          1                     1          0          0          0          0
          1                     1          0          0          5          5
          2                     2          9          9          5          5
          2                     2          7          7          0          0
          2                     2          4          4          3          3
          2                     2          3          3          0          0
          2                     2          2          2          0          0
          2                     2          2          2          6          6
          3                     3          7          7          7          7
          3                     3          3          3          2          2
          3                     3          3          3          5          5
          3                     3          2          2          3          3
          3                     3          1          1          0          0
          3                     3          0          0          4          4
          4                     4          7          7          6          6
          4                     4          6          6          3          3
          4                     4          4          4          8          8
          4                     4          3          3          6          6
          4                     4          1          1          6          6
          4                     4          0          0          3          3
          5                     5          8          8          0          0
          5                     5          8          8          3          3
          5                     5          7          7          6          6
          5                     5          6          6          0          0
          5                     5          6          6          7          7
          5                     5          2          2          2          2

--- 36 row(s) selected.
>>
>>?section qmj17
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>--
>>-- Order by clause on the first key column, a non-key column, and 
>>-- the second key column.
>>-- The second o.b. column is also not a join predicate column, so
>>-- can only use one join predicate - the one on the first o.b. column.
>>-- Must repartition and sort both tables.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  sort(exchange(exchange(scan('T09')))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       'FUNKY_OPT_UNIQUE',
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>  order by T08.sInt32_100,T08.char_10,T08.sInt16_10 DESC
+>;

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

SINT32_100   INT64_6               SINT16_10  SINT16_10  (EXPR)            UINT16_10  UINT16_10  
-----------  --------------------  ---------  ---------  ----------------  ---------  ---------  

          0                     0          1          1  FUNKY_OPT_UNIQUE          4          4  
          0                     0          0          0  FUNKY_OPT_UNIQUE          0          0  
          0                     0          6          6  FUNKY_OPT_UNIQUE          7          7  
          0                     0          3          3  FUNKY_OPT_UNIQUE          2          2  
          0                     0          4          4  FUNKY_OPT_UNIQUE          8          8  
          0                     0          0          0  FUNKY_OPT_UNIQUE          3          3  
          1                     1          4          4  FUNKY_OPT_UNIQUE          0          0  
          1                     1          3          3  FUNKY_OPT_UNIQUE          9          9  
          1                     1          0          0  FUNKY_OPT_UNIQUE          0          0  
          1                     1          0          0  FUNKY_OPT_UNIQUE          5          5  
          1                     1          1          1  FUNKY_OPT_UNIQUE          7          7  
          1                     1          9          9  FUNKY_OPT_UNIQUE          6          6  
          2                     2          9          9  FUNKY_OPT_UNIQUE          5          5  
          2                     2          7          7  FUNKY_OPT_UNIQUE          0          0  
          2                     2          2          2  FUNKY_OPT_UNIQUE          0          0  
          2                     2          3          3  FUNKY_OPT_UNIQUE          0          0  
          2                     2          2          2  FUNKY_OPT_UNIQUE          6          6  
          2                     2          4          4  FUNKY_OPT_UNIQUE          3          3  
          3                     3          3          3  FUNKY_OPT_UNIQUE          2          2  
          3                     3          0          0  FUNKY_OPT_UNIQUE          4          4  
          3                     3          1          1  FUNKY_OPT_UNIQUE          0          0  
          3                     3          7          7  FUNKY_OPT_UNIQUE          7          7  
          3                     3          3          3  FUNKY_OPT_UNIQUE          5          5  
          3                     3          2          2  FUNKY_OPT_UNIQUE          3          3  
          4                     4          3          3  FUNKY_OPT_UNIQUE          6          6  
          4                     4          0          0  FUNKY_OPT_UNIQUE          3          3  
          4                     4          1          1  FUNKY_OPT_UNIQUE          6          6  
          4                     4          7          7  FUNKY_OPT_UNIQUE          6          6  
          4                     4          6          6  FUNKY_OPT_UNIQUE          3          3  
          4                     4          4          4  FUNKY_OPT_UNIQUE          8          8  
          5                     5          2          2  FUNKY_OPT_UNIQUE          2          2  
          5                     5          8          8  FUNKY_OPT_UNIQUE          0          0  
          5                     5          6          6  FUNKY_OPT_UNIQUE          7          7  
          5                     5          6          6  FUNKY_OPT_UNIQUE          0          0  
          5                     5          8          8  FUNKY_OPT_UNIQUE          3          3  
          5                     5          7          7  FUNKY_OPT_UNIQUE          6          6  

--- 36 row(s) selected.
>>
>>?section qmj18
>>-- Parallel two-way PLAN3 join plan using sort merge join
>>-- Must range repartition T08,
>>-- since it is not partitioned on the join columns.
>>-- T08 is partitioned 3 ways and T09 is partitioned 4 ways.
>>-- With # of cpus = 4, should choose 4 ESP plan 
>>-- With # of cpus = 3, should choose 3 ESP plan - BUT:
>>-- Should the optimizer use partition grouping on the 4 partition
>>-- table to get the number of partitions to be <= # of cpus, and 
>>-- then range repartition T08, OR 
>>-- should the optimizer hash repartition both tables? The partition
>>-- grouping plan will lead to an inbalance, so maybe the 
>>-- repartitioning plan is better.  The c.q. shape forces the 
>>-- first alternative - we'll have to try it both ways and see
>>-- which is better.
>>--
>>-- Order by clause on the two left child columns that correspond 
>>-- to the last two columns of the right hand table primary key.
>>-- Should be able to use all join predicates.
>>-- Must sort BOTH children, because the order by has forced us
>>-- to ask the right child for an order that does not match the key order 
>>-- of the right table.
>>----------------------------------------------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape exchange(merge_join(
+>  sort(exchange(exchange(scan('T08')))),
+>  sort(exchange(scan('T09'))),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>Select T08.sInt32_100, T09.int64_6,
+>       T08.sInt16_10,T09.sInt16_10,
+>       T08.uInt16_10,T09.uInt16_10
+>  from $$P08$$ T08, $$P09$$ T09
+>  where     T08.sInt32_100 = T09.int64_6
+>        and T08.sInt16_10  = T09.sInt16_10
+>        and T08.uInt16_10  = T09.uInt16_10
+>  order by T08.sInt16_10,T08.uInt16_10
+>;

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

SINT32_100   INT64_6               SINT16_10  SINT16_10  UINT16_10  UINT16_10
-----------  --------------------  ---------  ---------  ---------  ---------

          0                     0          0          0          0          0
          1                     1          0          0          0          0
          0                     0          0          0          3          3
          4                     4          0          0          3          3
          3                     3          0          0          4          4
          1                     1          0          0          5          5
          3                     3          1          1          0          0
          0                     0          1          1          4          4
          4                     4          1          1          6          6
          1                     1          1          1          7          7
          2                     2          2          2          0          0
          5                     5          2          2          2          2
          3                     3          2          2          3          3
          2                     2          2          2          6          6
          2                     2          3          3          0          0
          0                     0          3          3          2          2
          3                     3          3          3          2          2
          3                     3          3          3          5          5
          4                     4          3          3          6          6
          1                     1          3          3          9          9
          1                     1          4          4          0          0
          2                     2          4          4          3          3
          0                     0          4          4          8          8
          4                     4          4          4          8          8
          5                     5          6          6          0          0
          4                     4          6          6          3          3
          0                     0          6          6          7          7
          5                     5          6          6          7          7
          2                     2          7          7          0          0
          4                     4          7          7          6          6
          5                     5          7          7          6          6
          3                     3          7          7          7          7
          5                     5          8          8          0          0
          5                     5          8          8          3          3
          2                     2          9          9          5          5
          1                     1          9          9          6          6

--- 36 row(s) selected.
>>
>>
>>-- MERGE JOINS ON DATE AND CHARACTER COLUMNS
>>
>>?section qmj19
>>--=================================================
>>--  Plan 1 merge join on a date partitioning key ==
>>-- each child with same number of partitions.    ==
>>--=================================================
>>
>>--------------------------------
>>-- Force a plan 1 merge join. --
>>--------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape
+>  exchange(merge_join(
+>             exchange(scan('T13A')),
+>             exchange(scan('T13B')),
+>             type1
+>                     )
+>          );

--- SQL operation complete.
>>
>>---------------------------------------
>>--  Join on a date partitioning key. --
>>---------------------------------------
>>prepare P from
+>select T13A.date_uniq,T13B.date_uniq, T13B.sInt32_100,
+>       substring(T13B.char_50p from 1 for 8)  
+>  from $$P13$$ T13A, $$P13$$ T13B
+>  where    T13A.date_uniq = T13B.date_uniq
+>       and T13A.sInt32_100 < 5;

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

DATE_UNIQ   DATE_UNIQ   SINT32_100   (EXPR)  
----------  ----------  -----------  --------

2100-02-02  2100-02-02            3  JAAAAAAA
2100-03-19  2100-03-19            3  JAAAAAAA
2100-04-07  2100-04-07            1  KYAAAAAA
2100-04-16  2100-04-16            1  JGAAAAAA
2100-04-21  2100-04-21            3  AVAAAAAA
2100-04-28  2100-04-28            1  ACAAAAAA
2100-05-17  2100-05-17            4  CXAAAAAA
2100-05-20  2100-05-20            1  HUAAAAAA
2100-06-17  2100-06-17            0  ARAAAAAA
2100-07-20  2100-07-20            0  BRAAAAAA
2100-07-23  2100-07-23            4  LTAAAAAA
2100-08-21  2100-08-21            0  CTAAAAAA
2100-10-04  2100-10-04            4  DOAAAAAA
2100-10-23  2100-10-23            2  EQAAAAAA
2100-11-01  2100-11-01            3  AUAAAAAA
2100-11-13  2100-11-13            4  DWAAAAAA
2100-11-23  2100-11-23            2  JQAAAAAA
2100-11-27  2100-11-27            2  KLAAAAAA
2100-12-15  2100-12-15            0  FKAAAAAA
2101-01-06  2101-01-06            2  CGAAAAAA
2101-02-28  2101-02-28            3  JUAAAAAA
2101-03-12  2101-03-12            3  GUAAAAAA
2101-03-17  2101-03-17            1  GTAAAAAA
2101-04-09  2101-04-09            4  FJAAAAAA
2101-05-16  2101-05-16            1  ECAAAAAA
2101-05-19  2101-05-19            0  GFAAAAAA
2101-05-30  2101-05-30            0  HEAAAAAA
2101-06-05  2101-06-05            2  IOAAAAAA
2101-06-28  2101-06-28            2  ENAAAAAA
2101-07-03  2101-07-03            4  HXAAAAAA

--- 30 row(s) selected.
>>
>>?section qmj20
>>--=================================================
>>--  Plan 1 merge join on a char partitioning key ==
>>-- each child with same number of partitions.    ==
>>--=================================================
>>
>>--------------------------------
>>-- Force a plan 1 merge join. --
>>--------------------------------
>>--control query shape exchange(merge_join(cut,cut));
>>control query shape
+>  exchange(merge_join(
+>             exchange(scan('T10A')),
+>             exchange(scan('T10B')),
+>             type1
+>                     )
+>          );

--- SQL operation complete.
>>
>>---------------------------------------
>>--  Join on a char partitioning key. --
>>---------------------------------------
>>prepare P from
+>select T10A.char_uniq,T10B.char_uniq, T10B.sInt32_100,
+>       substring(T10A.char_50p from 1 for 8)  
+>  from $$P10$$ T10A, $$P10$$ T10B
+>  where    T10A.char_uniq = T10B.char_uniq
+>       and T10A.sInt32_100 < 5;

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

CHAR_UNIQ  CHAR_UNIQ  SINT32_100   (EXPR)  
---------  ---------  -----------  --------

IAAAAAAA   IAAAAAAA             0  BRAAAAAA
BHAAAAAA   BHAAAAAA             3  JAAAAAAA
IDAAAAAA   IDAAAAAA             4  LTAAAAAA
DCAAAAAA   DCAAAAAA             3  JAAAAAAA
JHAAAAAA   JHAAAAAA             0  CTAAAAAA
DVAAAAAA   DVAAAAAA             1  KYAAAAAA
LBAAAAAA   LBAAAAAA             4  DOAAAAAA
EFAAAAAA   EFAAAAAA             1  JGAAAAAA
LUAAAAAA   LUAAAAAA             2  EQAAAAAA
EKAAAAAA   EKAAAAAA             3  AVAAAAAA
MEAAAAAA   MEAAAAAA             3  AUAAAAAA
ERAAAAAA   ERAAAAAA             1  ACAAAAAA
MQAAAAAA   MQAAAAAA             4  DWAAAAAA
FLAAAAAA   FLAAAAAA             4  CXAAAAAA
NBAAAAAA   NBAAAAAA             2  JQAAAAAA
FOAAAAAA   FOAAAAAA             1  HUAAAAAA
NFAAAAAA   NFAAAAAA             2  KLAAAAAA
GRAAAAAA   GRAAAAAA             0  ARAAAAAA
NXAAAAAA   NXAAAAAA             0  FKAAAAAA
OUAAAAAA   OUAAAAAA             2  CGAAAAAA
QXAAAAAA   QXAAAAAA             3  JUAAAAAA
RKAAAAAA   RKAAAAAA             3  GUAAAAAA
RPAAAAAA   RPAAAAAA             1  GTAAAAAA
SNAAAAAA   SNAAAAAA             4  FJAAAAAA
UAAAAAAA   UAAAAAAA             1  ECAAAAAA
UDAAAAAA   UDAAAAAA             0  GFAAAAAA
UOAAAAAA   UOAAAAAA             0  HEAAAAAA
UUAAAAAA   UUAAAAAA             2  IOAAAAAA
VSAAAAAA   VSAAAAAA             2  ENAAAAAA
VXAAAAAA   VXAAAAAA             4  HXAAAAAA

--- 30 row(s) selected.
>>
>>----------------
>>-- NESTED JOINS
>>----------------
>>
>>?section qnj1
>>-- SQL/MP style PLAN2 parallel two-way join plan using nested loops join
>>-- Really a Plan 1
>>-- Both tables partitioned 3 ways
>>------------------------------------------------------------------------
>>--control query shape exchange(nested_join(cut,cut));
>>control query shape exchange(nested_join(partition_access(scan('T00')),
+>                                         split_top_pa(scan('T01')),
+>                                         type2));

--- SQL operation complete.
>>prepare P from
+>select T00.uInt16_10,T00.sInt32_uniq,T01.uInt32_uniq
+>  from $$P00$$ T00, $$P01$$ T01
+>  where     T00.sint32_uniq = T01.uInt32_uniq
+>        and T00.uInt16_10   = 5;

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

UINT16_10  SINT32_UNIQ  UINT32_UNIQ
---------  -----------  -----------

        5          214          214
        5           17           17
        5          217          217
        5           22           22
        5          220          220
        5           30           30
        5          259          259
        5           37           37
        5          265          265
        5           52           52
        5          268          268
        5           59           59
        5          275          275
        5           66           66
        5          276          276
        5           69           69
        5          281          281
        5           79           79
        5          288          288
        5           86           86
        5          301          301
        5          100          100
        5          306          306
        5          108          108
        5          349          349
        5          128          128
        5          350          350
        5          145          145
        5          357          357
        5          148          148
        5          363          363
        5          165          165
        5          382          382
        5          179          179
        5          408          408
        5          181          181
        5          411          411
        5          189          189
        5          421          421
        5          195          195
        5          422          422
        5          436          436
        5          438          438
        5          446          446
        5          448          448
        5          474          474
        5          482          482
        5          484          484
        5          485          485
        5          494          494
        5          498          498
        5          503          503
        5          510          510
        5          543          543
        5          546          546
        5          561          561
        5          562          562
        5          572          572
        5          583          583
        5          592          592

--- 60 row(s) selected.
>>
>>?section qnj2
>>-- CPUS=4
>>-- SQL/MP style PLAN2 parallel two-way join plan using nested loops join
>>-- T02 is partitioned 6 ways      
>>-- T01 is partitioned 3 ways
>>-- With # of cpus = 4, should choose 4 ESP plan with logical
>>-- partition grouping on T02 - this will result in an inbalanced plan.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical
>>-- partition grouping on T02 - this will result in a balanced plan.
>>-- In fact, in this case, the plan will end up really being a Type 1 plan
>>-- (like the previous query).
>>------------------------------------------------------------------------
>>--control query shape exchange(nested_join(cut,cut));
>>control query shape exchange(nested_join(split_top_pa(scan('T02')),
+>                                         split_top_pa(scan('T01')),
+>                                         type2));

--- SQL operation complete.
>>prepare P from
+>select T02.uInt16_10,T02.int64_uniq,T01.uInt32_uniq
+>  from $$P02$$ T02, $$P01$$ T01
+>  where     T02.int64_uniq = T01.uInt32_uniq
+>        and T02.uInt16_10  = 5;

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

UINT16_10  INT64_UNIQ            UINT32_UNIQ
---------  --------------------  -----------

        5                   301          301
        5                    17           17
        5                   408          408
        5                   100          100
        5                   306          306
        5                    22           22
        5                   411          411
        5                   108          108
        5                   349          349
        5                   214          214
        5                   421          421
        5                    30           30
        5                   503          503
        5                   128          128
        5                   350          350
        5                   217          217
        5                   422          422
        5                    37           37
        5                   510          510
        5                   145          145
        5                   357          357
        5                   220          220
        5                   436          436
        5                    52           52
        5                   543          543
        5                   148          148
        5                   363          363
        5                   259          259
        5                   438          438
        5                    59           59
        5                   546          546
        5                   165          165
        5                   382          382
        5                   265          265
        5                   446          446
        5                    66           66
        5                   561          561
        5                   179          179
        5                   448          448
        5                   268          268
        5                   562          562
        5                    69           69
        5                   474          474
        5                   181          181
        5                   572          572
        5                   275          275
        5                   482          482
        5                    79           79
        5                   583          583
        5                   189          189
        5                   484          484
        5                   276          276
        5                   592          592
        5                    86           86
        5                   485          485
        5                   195          195
        5                   494          494
        5                   281          281
        5                   498          498
        5                   288          288

--- 60 row(s) selected.
>>
>>?section qnj2b
>>-- CPUS=3
>>-- SQL/MP style PLAN2 parallel two-way join plan using nested loops join
>>-- T02 is partitioned 6 ways      
>>-- T01 is partitioned 3 ways
>>-- With # of cpus = 4, should choose 4 ESP plan with logical
>>-- partition grouping on T02 - this will result in an inbalanced plan.
>>-- With # of cpus = 3, should choose 3 ESP plan with logical
>>-- partition grouping on T02 - this will result in a balanced plan.
>>-- In fact, in this case, the plan will end up really being a Type 1 plan
>>-- (like the previous query).
>>------------------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '3'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '3'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(nested_join(cut,cut));
>>control query shape exchange(nested_join(exchange(scan('T02')),
+>                                         exchange(scan('T01')),
+>                                         type2));

--- SQL operation complete.
>>prepare P from
+>select T02.uInt16_10,T02.int64_uniq,T01.uInt32_uniq
+>  from $$P02$$ T02, $$P01$$ T01
+>  where     T02.int64_uniq = T01.uInt32_uniq
+>        and T02.uInt16_10  = 5;

--- SQL command prepared.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>execute P;

UINT16_10  INT64_UNIQ            UINT32_UNIQ
---------  --------------------  -----------

        5                   301          301
        5                    17           17
        5                   408          408
        5                   100          100
        5                   306          306
        5                    22           22
        5                   411          411
        5                   108          108
        5                   349          349
        5                   214          214
        5                   421          421
        5                    30           30
        5                   503          503
        5                   128          128
        5                   350          350
        5                   217          217
        5                   422          422
        5                    37           37
        5                   510          510
        5                   145          145
        5                   357          357
        5                   220          220
        5                   436          436
        5                    52           52
        5                   543          543
        5                   148          148
        5                   363          363
        5                   259          259
        5                   438          438
        5                    59           59
        5                   546          546
        5                   165          165
        5                   382          382
        5                   265          265
        5                   446          446
        5                    66           66
        5                   561          561
        5                   179          179
        5                   448          448
        5                   268          268
        5                   562          562
        5                    69           69
        5                   474          474
        5                   181          181
        5                   572          572
        5                   275          275
        5                   482          482
        5                    79           79
        5                   583          583
        5                   189          189
        5                   484          484
        5                   276          276
        5                   592          592
        5                    86           86
        5                   485          485
        5                   195          195
        5                   494          494
        5                   281          281
        5                   498          498
        5                   288          288

--- 60 row(s) selected.
>>
>>?section qnj3
>>-- SQL/MP style PLAN2 parallel two-way join plan using nested loops join
>>-- A True Plan 2
>>-- Both tables partitioned 3 ways
>>------------------------------------------------------------------------
>>--control query shape exchange(nested_join(cut,cut));
>>control query shape exchange(nested_join(partition_access(scan('T00')),
+>                                         split_top_pa(scan('T01')),
+>                                         type2));

--- SQL operation complete.
>>prepare P from
+>select T00.uInt16_10,T00.int64_uniq,T01.uInt32_uniq
+>  from $$P00$$ T00, $$P01$$ T01
+>  where     T00.int64_uniq = T01.uInt32_uniq
+>        and T00.uInt16_10  = 5;

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

UINT16_10  INT64_UNIQ            UINT32_UNIQ
---------  --------------------  -----------

        5                   391          391
        5                   238          238
        5                   453          453
        5                   332          332
        5                   180          180
        5                   566          566
        5                   287          287
        5                   113          113
        5                   176          176
        5                   157          157
        5                   253          253
        5                   250          250
        5                    11           11
        5                    78           78
        5                   242          242
        5                   498          498
        5                   569          569
        5                   340          340
        5                   405          405
        5                    71           71
        5                   452          452
        5                   259          259
        5                   112          112
        5                     2            2
        5                    90           90
        5                   147          147
        5                   375          375
        5                   173          173
        5                   380          380
        5                   580          580
        5                    66           66
        5                    81           81
        5                   578          578
        5                   585          585
        5                   572          572
        5                   417          417
        5                   272          272
        5                    42           42
        5                   595          595
        5                    89           89
        5                   430          430
        5                   245          245
        5                   544          544
        5                   492          492
        5                   124          124
        5                    84           84
        5                   360          360
        5                   539          539
        5                    44           44
        5                   117          117
        5                   328          328
        5                   510          510
        5                   594          594
        5                   557          557
        5                   177          177
        5                   403          403
        5                   234          234
        5                   313          313
        5                   361          361
        5                   224          224

--- 60 row(s) selected.
>>
>>?section qnj4
>>-- SQL/MP style PLAN2 parallel two-way join plan using nested join
>>-- Multiple requests to same inner table row
>>-- T05 is part 3 ways and T02 is part 6 ways
>>----------------------------------------------------------------
>>--control query shape exchange(nested_join(cut,cut));
>>control query shape exchange(nested_join(
+>  exchange(scan('T05')),
+>  exchange(scan('T02')),type2));

--- SQL operation complete.
>>prepare P from
+>select T05.uNum_10,T02.uInt32_100,T05.sInt32_60,T02.int64_uniq
+>  from $$P05$$ T05, $$P02$$ T02
+>  where     T05.sInt32_60  = T02.int64_uniq 
+>        and T02.uInt32_100 < 30
+>        and T05.uNum_10    = 0.09;

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

UNUM_10      UINT32_100  SINT32_60    INT64_UNIQ          
-----------  ----------  -----------  --------------------

        .09          29            2                     2
        .09          29            5                     5
        .09          11           10                    10
        .09           4           14                    14
        .09          10           25                    25
        .09          10           25                    25
        .09          10           25                    25
        .09          25           34                    34
        .09          25           34                    34
        .09          26           37                    37
        .09          26           37                    37
        .09          26           37                    37
        .09          11           41                    41
        .09          11           41                    41
        .09          25           42                    42
        .09           6           45                    45
        .09          11           51                    51
        .09          28           52                    52
        .09          16           54                    54
        .09          16           54                    54
        .09          19           59                    59
        .09          19           59                    59

--- 22 row(s) selected.
>>
>>?section qnj5
>>-- SQL/MP style PLAN2 parallel two-way join plan using simple hash join
>>-- (AKA nested join + materialize)
>>-- Materialize uses broadcast replication
>>-- Both tables partitioned 3 ways
>>-- Negative test case, we don't support broadcast replication underneath
>>-- the right leg of a nested join -- yet
>>------------------------------------------------------------------------
>>--control query shape exchange(nested_join(cut,cut));
>>control query shape 
+>  exchange(nested_join(exchange(scan('T00')),
+>           materialize(exchange(scan('T01'))),
+>           type2));

--- SQL operation complete.
>>prepare P from
+>select T00.uNum_10,T01.uNum_10,T00.uInt32_uniq,T01.uInt32_uniq
+>  from $$P00$$ T00, $$P01$$ T01
+>  where     T00.uint32_uniq = T01.uInt32_uniq
+>        and T00.uNum_10     = T01.uNum_10;

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

UNUM_10      UNUM_10      UINT32_UNIQ  UINT32_UNIQ
-----------  -----------  -----------  -----------

        .09          .09          377          377
        .02          .02           49           49
        .05          .05          176          176
        .00          .00          490          490
        .05          .05          360          360
        .03          .03          241          241
        .06          .06          423          423
        .04          .04          560          560
        .03          .03          362          362
        .02          .02           64           64
        .02          .02          469          469
        .07          .07           24           24
        .07          .07          561          561
        .00          .00          411          411
        .00          .00          196          196
        .04          .04           85           85
        .09          .09          356          356
        .04          .04          517          517
        .02          .02          357          357
        .01          .01          567          567
        .05          .05           11           11
        .00          .00          252          252
        .00          .00           56           56
        .06          .06          104          104
        .07          .07          385          385
        .05          .05          595          595
        .05          .05          313          313
        .00          .00          185          185
        .01          .01          119          119
        .05          .05          430          430
        .09          .09          464          464
        .05          .05          544          544
        .05          .05          224          224
        .08          .08          326          326
        .08          .08          341          341
        .00          .00          312          312
        .04          .04          568          568
        .03          .03           47           47
        .08          .08          181          181
        .08          .08           98           98
        .08          .08          134          134
        .08          .08          353          353
        .05          .05          112          112
        .02          .02           99           99
        .06          .06           27           27
        .06          .06          472          472
        .02          .02            4            4
        .08          .08          351          351
        .06          .06          590          590
        .04          .04           39           39
        .04          .04          386          386
        .06          .06          114          114
        .00          .00          294          294

--- 53 row(s) selected.
>>
>>?section qnj6
>>-- PLAN2 N.J. over PLAN2 N.J. three way join plan
>>-- 4-4-4
>>--------------------------------------------------------------------------
>>--control query shape exchange(nested_join(nested_join(cut,cut),cut));
>>control query shape exchange(nested_join(
+>  nested_join(partition_access(scan('T07')),
+>              split_top_pa(scan('T03')),
+>              type2),
+>  split_top_pa(scan('T04')),
+>  type2));

--- SQL operation complete.
>>prepare P from
+>Select T07.int64_uniq,T03.sInt32_uniq,T04.uInt32_uniq
+>  from $$P07$$ T07, $$P03$$ T03, $$P04$$ T04
+>  where     T07.int64_uniq = T03.sInt32_uniq
+>        and T07.int64_uniq = T04.uInt32_uniq
+>        and T07.int64_uniq < 30;

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

INT64_UNIQ            SINT32_UNIQ  UINT32_UNIQ
--------------------  -----------  -----------

                  17           17           17
                   3            3            3
                  24           24           24
                  18           18           18
                  14           14           14
                  27           27           27
                  16           16           16
                  23           23           23
                   8            8            8
                   1            1            1
                  15           15           15
                  13           13           13
                  21           21           21
                  19           19           19
                  26           26           26
                  11           11           11
                   7            7            7
                   0            0            0
                  10           10           10
                  25           25           25
                  22           22           22
                   6            6            6
                   9            9            9
                   5            5            5
                  28           28           28
                  12           12           12
                   4            4            4
                   2            2            2
                  20           20           20
                  29           29           29

--- 30 row(s) selected.
>>
>>
>>
>>---------------------------------------------------------------------
>>-- SUBQUERIES - i.e.
>>-- Joins with right child joins, right child scalar aggregates,
>>-- and materialize of joins and scalar aggregates
>>---------------------------------------------------------------------
>>
>>?section qsq1
>>-- Correlated subquery, which is a join -
>>-- Should choose a NJ + materialize plan for the subquery,
>>-- but should NOT materialize the result of the subquery.
>>-- Must choose a NJ for evaluating the subquery because it is correlated.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.uInt32_uniq,T03.int64_100
+>  from $$P03$$ T03
+>  where T03.int64_100 < 2             
+>        AND EXISTS  
+>            (Select *
+>             from $$P05$$ T05, $$P07$$ T07
+>             where     T05.sInt32_60  = T07.sInt32_100
+>                   and T07.sInt32_100 < 50
+>                   and T05.sInt32_60  = T03.uInt32_uniq
+>                   and T05.char_50p   <> T03.char_50p
+>            );

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

UINT32_UNIQ  INT64_100           
-----------  --------------------

         38                     0

--- 1 row(s) selected.
>>
>>?section qsq2
>>-- Correlated subquery, which is a join -
>>-- Should choose a Type 3 Hash Join plan for the subquery.
>>-- Should materialize the result of the subquery.
>>-- Must choose a NJ for evaluating the subquery because it is correlated.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.uInt32_uniq
+>  from $$P03$$ T03
+>  where EXISTS  
+>        (Select *
+>           from  $$P05$$ T05, $$P06$$ T06
+>           where     T05.int64_uniq = T06.int64_uniq
+>                 and T05.uInt16_10  = T06.sInt16_10
+>                 and T06.sInt16_10  < 5
+>                 and T05.sInt32_60  = T03.sInt32_uniq
+>                 and T06.char_50p   <> T03.char_50p
+>  );

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

UINT32_UNIQ
-----------

        217
        129
        236
        419
         34
        375
        173
        500
        475
        583
        516
        380
        599
         49
         77
        251
         20
        171
        580
        193
        490
        127
         61
        359
         29
        175
        198
        407
         33
        502
        367
         66
        494
        513
        244
        376
         81
        241
        560
        505
        536
        215
         17
        497
        349
        564
        168
        144
        476
        545
        296
        146
         73
        547
        499
        445
        462
        433
        108
         64

--- 60 row(s) selected.
>>
>>?section qsq3
>>-- Correlated subquery, which is a join -
>>-- Should choose a NJ with no materialize plan for the subquery,
>>-- but should NOT materialize the result of the subquery.
>>-- Must choose a NJ for evaluating the subquery because it is correlated.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.sInt32_uniq
+>  from $$P03$$ T03
+>  where T03.char_100 = 'AAAAAAAA'
+>        AND EXISTS  
+>            (Select *
+>               from $$P00$$ T00, $$P01$$ T01
+>               where     T00.sInt32_uniq = T01.sInt32_uniq
+>                     and T00.sInt32_uniq = T03.sInt32_uniq
+>  );

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

SINT32_UNIQ
-----------

        302
        237
        418
        270
        573
        584

--- 6 row(s) selected.
>>
>>?section qsq4
>>-- 2 correlated subqueries, which are joins -
>>-- Should choose a NJ with no materialize plan for the subqueries,
>>-- but should NOT materialize the result of the subqueries.
>>-- Must choose NJ for evaluating the subqueries because they are correlated.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.sInt32_uniq
+>  from $$P03$$ T03
+>  where T03.char_100 = 'AAAAAAAA'
+>        AND EXISTS  
+>        (Select *
+>           from $$P00$$ T00, $$P01$$ T01
+>           where     T00.sInt32_uniq = T01.sInt32_uniq
+>                 and T00.sInt32_uniq = T03.sInt32_uniq
+>        )
+>        AND EXISTS  
+>        (Select *
+>           from $$P00$$ T00, $$P01$$ T01
+>           where     T00.sInt32_uniq = T01.sInt32_uniq
+>                 and T00.sInt32_uniq = T03.sInt32_uniq
+>        );

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

SINT32_UNIQ
-----------

        302
        237
        418
        270
        573
        584

--- 6 row(s) selected.
>>
>>?section qsq5
>>-- Non-correlated subquery on a scalar aggregate on a single table.
>>-- Can choose any join method for evaluating the subquery.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.char_100
+>  from $$P03$$ T03
+>  where T03.char_100 = 
+>    (Select min(T00.char_10)
+>       from $$P00$$ T00
+>    );

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

CHAR_100
--------

AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA

--- 6 row(s) selected.
>>
>>?section qsq6
>>-- non-correlated subquery on a scalar aggregate on a join
>>-- Can choose any join method for evaluating the subquery.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.char_100
+>  from $$P03$$ T03
+>  where T03.char_100 = 
+>        (Select min(T00.char_10)
+>           from $$P00$$ T00, $$P01$$ T01
+>           where     T00.sInt32_uniq = T01.sInt32_uniq
+>                 and T01.sInt32_100 < 5
+>        );

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

CHAR_100
--------

AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA

--- 6 row(s) selected.
>>
>>?section qsq7
>>-- Correlated subquery on a scalar aggregate on a join
>>-- Must choose a NJ for evaluating the subquery because it is correlated.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.char_100
+>  from $$P03$$ T03
+>  where T03.char_100 = 
+>        (Select min(T00.char_10)
+>           from $$P00$$ T00, $$P01$$ T01
+>           where     T00.sInt32_uniq = T01.sInt32_uniq
+>                 and T03.sInt32_50p = T00.sInt32_50p
+>        );

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

CHAR_100
--------

BCAAAAAA
ACAAAAAA
BDAAAAAA
AEAAAAAA
BDAAAAAA
ACAAAAAA
ADAAAAAA
ABAAAAAA
ADAAAAAA
BCAAAAAA
AAAAAAAA
BDAAAAAA
BAAAAAAA
ABAAAAAA
ABAAAAAA
AAAAAAAA
ABAAAAAA
BAAAAAAA
BAAAAAAA
AAAAAAAA
ADAAAAAA
ABAAAAAA
ACAAAAAA
BAAAAAAA
AEAAAAAA
ACAAAAAA
BBAAAAAA
ADAAAAAA
ACAAAAAA
ADAAAAAA
AEAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
ABAAAAAA

--- 35 row(s) selected.
>>
>>?section qsq8
>>-- Correlated subquery on a join with a group by.
>>-- Group by in the outer query as well.
>>-- Must choose a NJ for evaluating the subquery because it is correlated.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T05.sInt32_60
+>  from $$P05$$ T05
+>  where T05.char_100 = 
+>        (Select T00.char_10
+>           from $$P00$$ T00, $$P01$$ T01
+>           where    T00.sInt32_uniq = T01.sInt32_uniq
+>           and      T05.date_12 = T00.date_12
+>           group by T00.char_10
+>           having T00.char_10 <= 'AAAAAAAA'
+>
+>        )
+>  group by T05.sInt32_60;

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

SINT32_60  
-----------

         23
         27
         30
         41
         57
         58

--- 6 row(s) selected.
>>
>>?section qsq9
>>-- uncorrelated IN subquery on a join with a group by.
>>-- Semi-join for the subquery.
>>-- Group by in the outer query as well.
>>-- Force nested join to evaluate the subquery.
>>-- Materialize the subquery result.
>>------------------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(groupby(nested_join(cut,materialize(cut))));

--- SQL operation complete.
>>prepare P from
+>select T05.sInt32_60
+>  from $$P05$$ T05
+>  where T05.char_100 IN 
+>        (Select T00.char_10
+>           from $$P00$$ T00, $$P01$$ T01
+>           where T00.sInt32_uniq = T01.sInt32_uniq
+>           group by T00.char_10
+>        )
+>  group by T05.sInt32_60;

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

SINT32_60  
-----------

         20
          1
         21
          3
         22
          4
         23
          6
         24
          7
         26
          8
         27
          9
         28
         11
         29
         12
         30
         13
         31
         14
         32
         15
         34
         16
         35
         17
         36
         18
         38
         41
         42
         43
         44
         45
         48
         51
         52
         53
         57
         58
         59

--- 43 row(s) selected.
>>
>>?section qsq10
>>-- Non-correlated =ANY subquery on a group by on a single table.
>>-- Semi-join for the subquery.
>>------------------------------------------------------------------------
>>control query shape exchange(cut);

--- SQL operation complete.
>>prepare P from
+>select T03.char_100
+>  from $$P03$$ T03
+>  where T03.char_100 =ANY 
+>    (Select T00.char_10
+>       from $$P00$$ T00
+>       group by T00.char_10
+>       having T00.char_10 like 'A%'
+>    );

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

CHAR_100
--------

AAAAAAAA
ACAAAAAA
ADAAAAAA
AEAAAAAA
ABAAAAAA
ADAAAAAA
ACAAAAAA
ADAAAAAA
AEAAAAAA
ADAAAAAA
ACAAAAAA
ABAAAAAA
ADAAAAAA
ACAAAAAA
AEAAAAAA
ABAAAAAA
AAAAAAAA
ABAAAAAA
ABAAAAAA
AEAAAAAA
ACAAAAAA
ACAAAAAA
ADAAAAAA
ABAAAAAA
AEAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
AAAAAAAA
AEAAAAAA

--- 30 row(s) selected.
>>
>>-----------
>>-- UNION --
>>-----------
>>
>>?section qu1
>>-- UNION (DISTINCT)
>>-- Both tables partitioned the same, partitioning key columns
>>-- match up in the select lists    
>>------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(groupby(union(cut,cut)));

--- SQL operation complete.
>>prepare P from
+>Select sInt32_uniq,uInt16_10
+>  from $$P00$$ T00
+>  where uInt16_10 = 5
+>union
+>Select uInt32_uniq,sInt16_10
+>  from $$P01$$ T01
+>  where sInt16_10 = 5;

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

SINT32_UNIQ           UINT16_10  
--------------------  -----------

                  17            5
                  22            5
                  30            5
                  37            5
                  52            5
                  59            5
                  66            5
                  69            5
                  79            5
                  86            5
                 100            5
                 108            5
                 128            5
                 145            5
                 148            5
                 165            5
                 179            5
                 181            5
                 189            5
                 195            5
                  10            5
                  13            5
                  23            5
                  24            5
                  38            5
                  40            5
                  48            5
                  50            5
                  76            5
                  84            5
                  87            5
                  96            5
                 105            5
                 112            5
                 163            5
                 164            5
                 174            5
                 185            5
                 194            5
                 219            5
                 214            5
                 224            5
                 408            5
                 232            5
                 217            5
                 239            5
                 411            5
                 254            5
                 220            5
                 261            5
                 421            5
                 268            5
                 259            5
                 271            5
                 422            5
                 281            5
                 265            5
                 288            5
                 436            5
                 302            5
                 310            5
                 438            5
                 330            5
                 347            5
                 350            5
                 275            5
                 367            5
                 446            5
                 381            5
                 276            5
                 383            5
                 448            5
                 391            5
                 397            5
                 474            5
                 482            5
                 301            5
                 484            5
                 306            5
                 485            5
                 349            5
                 494            5
                 498            5
                 357            5
                 503            5
                 363            5
                 510            5
                 382            5
                 543            5
                 546            5
                 561            5
                 562            5
                 572            5
                 583            5
                 592            5
                 416            5
                 419            5
                 461            5
                 467            5
                 470            5
                 477            5
                 478            5
                 483            5
                 490            5
                 508            5
                 551            5
                 552            5
                 559            5
                 565            5
                 584            5

--- 110 row(s) selected.
>>
>>?section qu2
>>-- UNION (DISTINCT)
>>-- Both tables partitioned the same, but the partitioning key columns
>>-- are not in the select lists
>>-- Will have to repartition both children on the select list
>>-- columns (since a GB will be generated to satisfy the union DISTINCT
>>-- and the GB columns will be the select list columns)
>>------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(groupby(union(cut,cut)));

--- SQL operation complete.
>>prepare P from
+>Select char_uniq, uInt16_10
+>  from $$P00$$ T00
+>  where uInt16_10 = 5
+>union
+>Select char_uniq, uInt16_10
+>  from $$P01$$ T01
+>  where uInt16_10 = 5;

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

CHAR_UNIQ  UINT16_10
---------  ---------

GXAAAAAA           5
DLAAAAAA           5
SQAAAAAA           5
LEAAAAAA           5
ASAAAAAA           5
XCAAAAAA           5
VXAAAAAA           5
MJAAAAAA           5
AVAAAAAA           5
QJAAAAAA           5
QQAAAAAA           5
VCAAAAAA           5
IXAAAAAA           5
QKAAAAAA           5
JYAAAAAA           5
HJAAAAAA           5
BTAAAAAA           5
DCAAAAAA           5
UVAAAAAA           5
PEAAAAAA           5
FVAAAAAA           5
TBAAAAAA           5
EJAAAAAA           5
NDAAAAAA           5
EUAAAAAA           5
PKAAAAAA           5
VUAAAAAA           5
EDAAAAAA           5
XUAAAAAA           5
JEAAAAAA           5
FTAAAAAA           5
BFAAAAAA           5
KIAAAAAA           5
LGAAAAAA           5
OYAAAAAA           5
DHAAAAAA           5
QVAAAAAA           5
VHAAAAAA           5
DPAAAAAA           5
QAAAAAAA           5
PYAAAAAA           5
GGAAAAAA           5
TTAAAAAA           5
VFAAAAAA           5
WTAAAAAA           5
XSAAAAAA           5
OXAAAAAA           5
XTAAAAAA           5
DXAAAAAA           5
NIAAAAAA           5
KTAAAAAA           5
CNAAAAAA           5
HOAAAAAA           5
CWAAAAAA           5
HNAAAAAA           5
SOAAAAAA           5
GNAAAAAA           5
HWAAAAAA           5
SMAAAAAA           5
QWAAAAAA           5

--- 60 row(s) selected.
>>
>>
>>?section qu3
>>-- UNION ALL
>>-- Both tables partitioned the same, but the partitioning key columns
>>-- are not in the select lists
>>-- should fail to generate a plan, due to limitations in the 
>>-- current parallel union code.
>>------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(groupby(union(cut,cut)));

--- SQL operation complete.
>>prepare P from
+>Select char_uniq, sInt32_100
+>  from $$P00$$ T00
+>  where sInt32_100 = 5
+>union all
+>Select char_uniq, sInt32_100
+>  from $$P01$$ T01
+>  where sInt32_100 = 5;

*** ERROR[2105] Unable to compile this query because of incompatible Control Query Shape (CQS) specifications.  Suggestion: Inspect the CQS in effect.

*** ERROR[8822] Unable to prepare the statement.

>>
>>execute P;

*** ERROR[15017] Statement P not found.

>>
>>?section qu4
>>-- UNION (DISTINCT)
>>-- Union of two joins 
>>-- Both joins must repartition both of their children
>>------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape 
+>    exchange(groupby(union(
+>                   hybrid_hash_join(
+>                   exchange(exchange(scan('T05'))),
+>                   exchange(exchange(scan('T06'))),
+>                   type1),
+>                   hybrid_hash_join(
+>                   exchange(exchange(scan('T05'))),
+>                   exchange(exchange(scan('T06'))),
+>                   type1)
+>                  )));

--- SQL operation complete.
>>prepare P from
+>select T05.int64_uniq,T06.int64_uniq,T05.sInt16_10,T06.uInt16_10
+>  from $$P05$$ T05, $$P06$$ T06
+>  where     T05.int64_uniq = T06.int64_uniq
+>        and T05.sInt16_10  = T06.uInt16_10
+>        and T06.sint16_10  < 5
+>union
+>select T05.int64_uniq,T06.int64_uniq,T05.sInt16_10,T06.uInt16_10
+>  from $$P05$$ T05, $$P06$$ T06
+>  where     T05.int64_uniq = T06.int64_uniq
+>        and T05.sInt16_10 = T06.uInt16_10
+>        and T06.sint16_10 < 5;

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

INT64_UNIQ            INT64_UNIQ            SINT16_10  UINT16_10
--------------------  --------------------  ---------  ---------

                 380                   380          5          5
                 296                   296          7          7
                 299                   299          7          7
                  30                    30          9          9
                 430                   430          5          5
                 508                   508          3          3
                  83                    83          1          1
                 323                   323          1          1
                  41                    41          3          3
                 127                   127          9          9
                 585                   585          5          5
                 231                   231          3          3
                 448                   448          9          9
                 253                   253          5          5
                 167                   167          1          1
                 377                   377          9          9
                 510                   510          5          5
                 382                   382          8          8
                 394                   394          4          4
                  86                    86          2          2
                  93                    93          6          6
                 200                   200          4          4
                 480                   480          4          4
                 269                   269          2          2
                 518                   518          8          8
                 150                   150          8          8
                 599                   599          6          6
                 422                   422          2          2
                 529                   529          2          2
                 104                   104          6          6
                 326                   326          8          8

--- 31 row(s) selected.
>>
>>-------------------------------------------------------------
>>-- 3-way joins, with combinations of the various join methods
>>-- (hash, merge, nested)
>>-------------------------------------------------------------
>>
>>?section q31
>>-- Type 1 Merge over Nested
>>-- 3-6-3
>>control query shape groupby(exchange(merge_join(
+>                                       nested_join(cut,cut),cut)));

--- SQL operation complete.
>>prepare P from
+>Select count(*), 
+>       min(T05A.sInt32_60 - T02.int64_uniq),
+>       max(T05A.sInt32_60 - T02.int64_uniq),
+>       min(T05A.sInt32_60 - T05B.sInt32_60),
+>       max(T05A.sInt32_60 - T05B.sInt32_60),
+>       max(T05A.int64_100)
+>  from $$P05$$ T05A, $$P02$$ T02, $$P05$$ T05B
+>  where     T05A.sInt32_60 = T02.int64_uniq
+>        and T05A.int64_100 < 50
+>        and T05A.sInt32_60 = T05B.sInt32_60;

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

(EXPR)                (EXPR)                (EXPR)                (EXPR)                (EXPR)                (EXPR)
--------------------  --------------------  --------------------  --------------------  --------------------  --------------------

                3000                     0                     0                     0                     0                    49

--- 1 row(s) selected.
>>
>>?section q32
>>-- Nested over Type 1 Merge with logical partitioning
>>-- 3-4-6
>>control query shape groupby(exchange(nested_join(
+>                                       merge_join(cut,cut),cut)));

--- SQL operation complete.
>>prepare P from
+>Select count(*), 
+>       min(T05.sInt32_60 - T09.int64_6),
+>       max(T05.sInt32_60 - T09.int64_6),
+>       min(T05.sInt32_60 - T02.int64_uniq),
+>       max(T05.sInt32_60 - T02.int64_uniq)
+>from $$P05$$ T05, $$P09$$ T09, $$P02$$ T02
+>  where     T05.sInt32_60 = T09.int64_6
+>        and T05.sInt32_60 = T02.int64_uniq;

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

(EXPR)                (EXPR)                (EXPR)                (EXPR)                (EXPR)
--------------------  --------------------  --------------------  --------------------  --------------------

                6000                     0                     0                     0                     0

--- 1 row(s) selected.
>>
>>?section q33
>>-- Type 3 hash join over nested join
>>-- 3-6-3
>>control query shape exchange(hybrid_hash_join(
+>                               exchange(nested_join(cut,cut)),cut));

--- SQL operation complete.
>>prepare P from
+>Select T05.int64_uniq, T02.int64_uniq, T06.int64_uniq, T06.sInt16_10
+>  from $$P05$$ T05, $$P02$$ T02, $$P06$$ T06
+>  where     T05.int64_uniq  = T02.int64_uniq
+>        and T05.int64_uniq  < 60
+>        and T05.int64_uniq  = T06.int64_uniq
+>        and T06.sInt16_10  <= 2;

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

INT64_UNIQ            INT64_UNIQ            INT64_UNIQ            SINT16_10
--------------------  --------------------  --------------------  ---------

                  28                    28                    28          2
                  27                    27                    27          2
                  12                    12                    12          1
                  52                    52                    52          0
                  17                    17                    17          2
                  56                    56                    56          0
                  50                    50                    50          1
                  25                    25                    25          0
                  24                    24                    24          1
                  37                    37                    37          2

--- 10 row(s) selected.
>>
>>?section q34
>>-- Nested join over Type 2 Hash
>>-- 3-6-3
>> control query shape exchange(nested_join(hybrid_hash_join(cut,cut),cut));

--- SQL operation complete.
>>prepare P from
+>Select T05.int64_uniq, T02.int64_uniq, T06.int64_uniq, T06.sInt16_10
+>  from $$P05$$ T05, $$P02$$ T02, $$P06$$ T06
+>  where     T05.int64_uniq = T02.int64_uniq
+>        and T05.int64_uniq < 60
+>        and T05.int64_uniq = T06.int64_uniq
+>        and T06.sInt16_10  <= 2;

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

INT64_UNIQ            INT64_UNIQ            INT64_UNIQ            SINT16_10
--------------------  --------------------  --------------------  ---------

                  27                    27                    27          2
                  52                    52                    52          0
                  56                    56                    56          0
                  25                    25                    25          0
                  28                    28                    28          2
                  12                    12                    12          1
                  17                    17                    17          2
                  24                    24                    24          1
                  50                    50                    50          1
                  37                    37                    37          2

--- 10 row(s) selected.
>>
>>?section q35
>>-- Type 2 Hash over Type 1 Merge
>>-- 4-4-4
>>control query shape exchange(hybrid_hash_join(merge_join(cut,cut),cut));

--- SQL operation complete.
>>prepare P from
+>Select T07A.int64_60,T07B.int64_60,T07A.sInt16_10,T07B.sInt16_10,
+>       T04.sInt32_uniq,T04.uInt32_50p
+>  from $$P07$$ T07A, $$P07$$ T07B, $$P04$$ T04
+>  where     T07A.int64_60  = T07B.int64_60
+>        and T07A.sInt16_10 = T07B.sInt16_10
+>        and T07A.int64_60  = T04.sInt32_uniq
+>        and T04.uInt32_50p < 30;

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

INT64_60              INT64_60              SINT16_10  SINT16_10  SINT32_UNIQ  UINT32_50P
--------------------  --------------------  ---------  ---------  -----------  ----------

                  39                    39          0          0           39          23
                  19                    19          0          0           19          11
                  39                    39          1          1           39          23
                  19                    19          1          1           19          11
                  39                    39          2          2           39          23
                  19                    19          2          2           19          11
                  39                    39          3          3           39          23
                  19                    19          3          3           19          11
                  39                    39          4          4           39          23
                  19                    19          4          4           19          11
                  39                    39          5          5           39          23
                  19                    19          5          5           19          11
                  39                    39          6          6           39          23
                  19                    19          6          6           19          11
                  39                    39          7          7           39          23
                  19                    19          7          7           19          11
                  39                    39          8          8           39          23
                  19                    19          8          8           19          11
                  39                    39          9          9           39          23
                  19                    19          9          9           19          11
                  44                    44          0          0           44          28
                  21                    21          0          0           21          23
                  44                    44          1          1           44          28
                  21                    21          1          1           21          23
                  44                    44          2          2           44          28
                  21                    21          2          2           21          23
                  44                    44          3          3           44          28
                  21                    21          3          3           21          23
                  44                    44          4          4           44          28
                  21                    21          4          4           21          23
                  44                    44          5          5           44          28
                  21                    21          5          5           21          23
                  44                    44          6          6           44          28
                  21                    21          6          6           21          23
                  44                    44          7          7           44          28
                  21                    21          7          7           21          23
                  44                    44          8          8           44          28
                  21                    21          8          8           21          23
                  44                    44          9          9           44          28
                  21                    21          9          9           21          23
                  50                    50          0          0           50          26
                  26                    26          0          0           26          14
                  50                    50          1          1           50          26
                  26                    26          1          1           26          14
                  50                    50          2          2           50          26
                  26                    26          2          2           26          14
                  50                    50          3          3           50          26
                  26                    26          3          3           26          14
                  50                    50          4          4           50          26
                  26                    26          4          4           26          14
                  50                    50          5          5           50          26
                  26                    26          5          5           26          14
                  50                    50          6          6           50          26
                  26                    26          6          6           26          14
                  50                    50          7          7           50          26
                  26                    26          7          7           26          14
                  50                    50          8          8           50          26
                  26                    26          8          8           26          14
                  50                    50          9          9           50          26
                  26                    26          9          9           26          14

--- 60 row(s) selected.
>>
>>?section q36
>>-- Type 1 Merge over Type 1 Hash
>>-- Sort of outer composite necessary
>>-- 4-4-4
>>control query shape exchange(merge_join(sort(hybrid_hash_join(cut,cut)),cut));

--- SQL operation complete.
>>prepare P from
+>Select T07A.int64_60,T07B.int64_60,T07A.sInt16_10,T07B.sInt16_10,
+>       T07C.int64_60,T07C.sInt16_10,T07B.uInt16_10,T07B.uInt32_50p
+>  from $$P07$$ T07A, $$P07$$ T07B, $$P07$$ T07C
+>  where     T07A.int64_60   = T07B.int64_60
+>        and T07A.sInt16_10  = T07B.sInt16_10
+>        and T07B.uInt32_50p < 150
+>        and T07B.int64_60   = T07C.int64_60
+>        and T07B.sInt16_10  = T07C.sInt16_10
+>        and T07B.uInt16_10  = T07C.sInt16_10
+>  order by T07B.int64_60, T07B.sInt16_10;

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

INT64_60              INT64_60              SINT16_10  SINT16_10  INT64_60              SINT16_10  UINT16_10  UINT32_50P
--------------------  --------------------  ---------  ---------  --------------------  ---------  ---------  ----------

                   0                     0          7          7                     0          7          7          43
                   7                     7          0          0                     7          0          0          33
                   7                     7          7          7                     7          7          7          76
                   7                     7          8          8                     7          8          8          81
                   8                     8          9          9                     8          9          9         144
                  10                    10          3          3                    10          3          3          38
                  12                    12          3          3                    12          3          3          63
                  12                    12          6          6                    12          6          6          30
                  14                    14          5          5                    14          5          5          40
                  16                    16          5          5                    16          5          5          93
                  16                    16          7          7                    16          7          7          45
                  17                    17          4          4                    17          4          4         149
                  19                    19          5          5                    19          5          5         118
                  25                    25          2          2                    25          2          2          75
                  26                    26          3          3                    26          3          3          50
                  26                    26          5          5                    26          5          5          45
                  26                    26          9          9                    26          9          9         121
                  29                    29          8          8                    29          8          8         137
                  34                    34          2          2                    34          2          2          52
                  34                    34          4          4                    34          4          4          13
                  40                    40          7          7                    40          7          7          18
                  44                    44          4          4                    44          4          4         148
                  46                    46          8          8                    46          8          8         100
                  47                    47          3          3                    47          3          3          99
                  49                    49          9          9                    49          9          9         143
                  56                    56          8          8                    56          8          8           5

--- 26 row(s) selected.
>>
>>?section q37
>>-- Type 1 Merge over Type 3 Hash
>>-- Sort of outer composite necessary
>>-- Type 3 Hash should range repart T01
>>-- 3-4-4
>>control query shape exchange(merge_join(sort(hybrid_hash_join(cut,cut)),cut));

--- SQL operation complete.
>>prepare P from
+>Select T01.int64_100,T07A.int64_60,T01.sInt16_10,T07A.sInt16_10,
+>       T07B.int64_60,T07B.sInt16_10,T07A.varchar_100
+>  from $$P01$$ T01, $$P07$$ T07A, $$P07$$ T07B
+>  where     T01.int64_100    = T07A.int64_60
+>        and T01.sInt16_10    = T07A.sInt16_10
+>        and T01.int64_100    = T07B.int64_60
+>        and T01.sInt16_10    = T07B.sInt16_10
+>        and T07A.varchar_100 < 'AK'
+>  order by T07B.int64_60;

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

INT64_100             INT64_60              SINT16_10  SINT16_10  INT64_60              SINT16_10  VARCHAR_100
--------------------  --------------------  ---------  ---------  --------------------  ---------  ----------------

                   0                     0          7          7                     0          7  ADAAAAAA        
                   3                     3          0          0                     3          0  AEAAAAAAAAAAAAAA
                   3                     3          1          1                     3          1  AJAAAAAA        
                   4                     4          1          1                     4          1  AGAAAAAA        
                   7                     7          3          3                     7          3  AFAAAAAA        
                   9                     9          0          0                     9          0  ABAAAAAAAAAAAAAA
                   9                     9          0          0                     9          0  ABAAAAAAAAAAAAAA
                  10                    10          3          3                    10          3  ABAAAAAA        
                  10                    10          3          3                    10          3  ABAAAAAA        
                  11                    11          3          3                    11          3  AIAAAAAA        
                  11                    11          3          3                    11          3  AIAAAAAA        
                  15                    15          5          5                    15          5  AGAAAAAA        
                  16                    16          2          2                    16          2  AIAAAAAAAAAAAAAA
                  16                    16          5          5                    16          5  ADAAAAAA        
                  17                    17          7          7                    17          7  AEAAAAAA        
                  21                    21          3          3                    21          3  AIAAAAAA        
                  21                    21          7          7                    21          7  AAAAAAAA        
                  24                    24          3          3                    24          3  AJAAAAAA        
                  26                    26          2          2                    26          2  AEAAAAAAAAAAAAAA
                  26                    26          2          2                    26          2  AEAAAAAAAAAAAAAA
                  28                    28          3          3                    28          3  ADAAAAAA        
                  31                    31          6          6                    31          6  ADAAAAAAAAAAAAAA
                  31                    31          9          9                    31          9  ADAAAAAA        
                  34                    34          2          2                    34          2  ABAAAAAAAAAAAAAA
                  35                    35          5          5                    35          5  ACAAAAAA        
                  35                    35          6          6                    35          6  AJAAAAAAAAAAAAAA
                  35                    35          6          6                    35          6  AJAAAAAAAAAAAAAA
                  36                    36          4          4                    36          4  ABAAAAAAAAAAAAAA
                  40                    40          4          4                    40          4  ACAAAAAAAAAAAAAA
                  42                    42          2          2                    42          2  AHAAAAAAAAAAAAAA
                  42                    42          2          2                    42          2  AHAAAAAAAAAAAAAA
                  42                    42          8          8                    42          8  AHAAAAAAAAAAAAAA
                  43                    43          7          7                    43          7  AFAAAAAA        
                  45                    45          0          0                    45          0  AEAAAAAAAAAAAAAA
                  45                    45          0          0                    45          0  AEAAAAAAAAAAAAAA
                  46                    46          9          9                    46          9  AIAAAAAA        
                  48                    48          4          4                    48          4  AJAAAAAAAAAAAAAA
                  49                    49          6          6                    49          6  AIAAAAAAAAAAAAAA
                  49                    49          6          6                    49          6  AIAAAAAAAAAAAAAA
                  49                    49          6          6                    49          6  AIAAAAAAAAAAAAAA
                  51                    51          4          4                    51          4  AAAAAAAAAAAAAAAA
                  51                    51          4          4                    51          4  AAAAAAAAAAAAAAAA
                  55                    55          5          5                    55          5  ADAAAAAA        
                  56                    56          8          8                    56          8  AFAAAAAAAAAAAAAA
                  56                    56          8          8                    56          8  AFAAAAAAAAAAAAAA
                  59                    59          9          9                    59          9  ACAAAAAA        

--- 46 row(s) selected.
>>
>>-------------------------------------------------------------
>>-- Combinations of joins, group by, order by
>>-------------------------------------------------------------
>>
>>?section qcombo1
>>-- Hash Join + GB
>>-- SQL/MP style PLAN3 parallel two-way join plan using hash join
>>-- Must range repartition T05,                 
>>-- since it is not partitioned on the join columns.
>>-- Both tables partitioned 3 ways.
>>-- Group by is on a non-key column, so must do a hash GB with 
>>-- repartitioning.
>>----------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(groupby(exchange(hybrid_hash_join(
+>  exchange(exchange(scan('T05'))),
+>  exchange(scan('T06')),
+>  type1))));

--- SQL operation complete.
>>prepare P from
+>select T05.uInt16_10,count(*)
+>  from $$P05$$ T05, $$P06$$ T06
+>  where     T05.int64_uniq = T06.uInt32_60
+>        and T05.uInt16_10  = T06.sInt16_10
+>        and T06.sint16_10  < 5
+>  group by T05.uInt16_10
+>;

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

UINT16_10  (EXPR)              
---------  --------------------

        1                     3
        3                     7
        2                     4
        4                    10
        0                     3

--- 5 row(s) selected.
>>
>>?section qcombo2
>>-- Merge Join + GB
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join
>>-- Both tables are partitioned on the join columns and match exactly.
>>-- Group by on leading key column - must repartition, since we did
>>-- not specify the second key column in the GB list.
>>-- Should do a hash group by, instead of sort groupby, even though
>>-- the order by includes the grouping column, because a merge of
>>-- sorted streams will not be possible to preserve the sort order
>>-- from the group by for the order by, due to the risk of deadlock.
>>-- Neither table needs to be sorted for the merge join.
>>-- Order by is on the leading key column, but a sort will be needed
>>-- because we must repartition for the GB.
>>----------------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape 
+> sort(exchange(groupby(exchange(merge_join(
+>   exchange(scan('T06A')),
+>   exchange(scan('T06B')),type1)))));

--- SQL operation complete.
>>prepare P from
+>select T06A.uInt32_60
+>  from $$P06$$ T06A, $$P06$$ T06B
+>  where     T06A.uInt32_60 = T06B.uint32_60
+>        and T06A.sInt16_10 = T06B.sInt16_10
+>  group by T06A.uInt32_60
+>  having   T06A.uInt32_60 IN (0,15,30,45)
+>  order by T06A.uInt32_60
+>;

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

UINT32_60 
----------

         0
        15
        30
        45

--- 4 row(s) selected.
>>
>>?section qcombo2b
>>-- CPUS = 6
>>-- Merge Join + GB
>>-- SQL/MP style PLAN1 parallel two-way join plan using sort merge join.
>>-- But, since there are 6 cpus, will need to repartition both
>>-- tables to get 6-way parallelism (3 partitions is not enough
>>-- to satisfy the requirement).
>>-- Both tables are partitioned on the join columns and match exactly.
>>-- Group by on leading key column - must repartition, since we did
>>-- not specify the second key column in the GB list.
>>-- Both tables need to be sorted for the merge join, since a 
>>-- merge of sorted streams cannot be done for the repartitioned data,
>>-- due to the risk of deadlock.
>>-- Should do a hash group by, instead of sort groupby, even though
>>-- the order by includes the grouping column, because a merge of
>>-- sorted streams will not be possible to preserve the sort order
>>-- from the group by for the order by, due to the risk of deadlock.
>>-- Neither table needs to be sorted for the merge join.
>>-- Order by is on the leading key column, but a sort will be needed
>>-- because we must repartition for the GB.
>>----------------------------------------------------------------------
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '6'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '6'
+>#ifMP
+>;

--- SQL operation complete.
>>
>>--control query shape exchange(cut);
>>control query shape 
+> sort(exchange(groupby(exchange(merge_join(
+>   sort(exchange(exchange(scan('T06A')))),
+>   sort(exchange(exchange(scan('T06B')))),type1)))));

--- SQL operation complete.
>>prepare P from
+>select T06A.uInt32_60
+>  from $$P06$$ T06A, $$P06$$ T06B
+>  where     T06A.uInt32_60 = T06B.uint32_60
+>        and T06A.sInt16_10 = T06B.sInt16_10
+>  group by T06A.uInt32_60
+>  having   T06A.uInt32_60 IN (0,15,30,45)
+>  order by T06A.uInt32_60
+>;

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

UINT32_60 
----------

         0
        15
        30
        45

--- 4 row(s) selected.
>>
>>#ifMX
>>control query default DEF_NUM_LOCAL_SMP_CPUS '4'
+>#ifMX
+>#ifMP
+>control query default DEF_NUM_NODES_IN_LOCAL_CLUSTER '4'
+>#ifMMP
+>;

--- SQL operation complete.
>>
>>?section qcombo3
>>-- Nested Join + GB
>>-- SQL/MP style PLAN2 parallel two-way join plan using nested loops join
>>-- Both tables partitioned 3 ways
>>-- Group by on one key column of the outer table - should pick sort GB.
>>------------------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(groupby(nested_join(
+>                                         partition_access(scan('T14')),
+>                                         split_top_pa(scan('T01')),
+>                                         type2)));

--- SQL operation complete.
>>prepare P from
+>select T14.sInt32_60,count(*)
+>  from $$P14$$ T14, $$P01$$ T01
+>  where     T14.sint32_uniq = T01.uInt32_uniq
+>        and T14.uNum_10 = 0
+>  group by T14.sInt32_60
+>  order by T14.sInt32_60;

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

SINT32_60    (EXPR)              
-----------  --------------------

          0                     1
          2                     2
          3                     1
          4                     2
          5                     1
          6                     1
          7                     1
          8                     2
          9                     1
         10                     2
         11                     1
         14                     2
         15                     3
         16                     1
         17                     2
         18                     1
         19                     2
         23                     1
         25                     2
         28                     1
         29                     2
         30                     2
         31                     1
         32                     1
         34                     2
         36                     2
         38                     2
         39                     2
         40                     2
         41                     1
         43                     1
         44                     1
         46                     2
         47                     1
         48                     3
         49                     1
         50                     1
         55                     1
         57                     2

--- 39 row(s) selected.
>>
>>?section qcombo4
>>-- 3 way join + GB
>>-- Type 3 hash join over nested join
>>-- 3-6-3
>>-- Group by - should do hash GB + repartitioning
>>-- control query shape exchange(cut);
>>control query shape exchange(groupby(exchange(hybrid_hash_join(
+>          exchange(nested_join(cut,cut)),cut))));

--- SQL operation complete.
>>prepare P from
+>Select T05.int64_uniq, T02.int64_uniq, T06.int64_uniq, T06.sInt16_10
+>  from $$P05$$ T05, $$P02$$ T02, $$P06$$ T06
+>  where     T05.int64_uniq = T02.int64_uniq
+>        and T05.int64_uniq < 60
+>        and T05.int64_uniq = T06.int64_uniq
+>        and T06.sInt16_10 <= 2
+>  group by T05.int64_uniq,T02.int64_uniq,T06.int64_uniq,T06.sInt16_10 
+>;

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

INT64_UNIQ            INT64_UNIQ            INT64_UNIQ            SINT16_10
--------------------  --------------------  --------------------  ---------

                  28                    28                    28          2
                  12                    12                    12          1
                  17                    17                    17          2
                  50                    50                    50          1
                  24                    24                    24          1
                  37                    37                    37          2
                  27                    27                    27          2
                  52                    52                    52          0
                  56                    56                    56          0
                  25                    25                    25          0

--- 10 row(s) selected.
>>
>>?section qcombo5
>>-- 3 way join + GB + Order By
>>-- Type 2 Nested over Type 1 Merge
>>-- 4-4-4
>>-- Group by - should do Sort GB
>>control query shape exchange(groupby(nested_join(merge_join(cut,cut),cut)));

--- SQL operation complete.
>>prepare P from
+>Select T07A.int64_60,T07B.int64_60,T07A.sInt16_10,T07B.sInt16_10,count(*)
+>  from $$P07$$ T07A, $$P07$$ T07B, $$P04$$ T04
+>  where     T07A.int64_60  = T07B.int64_60
+>        and T07A.sInt16_10 = T07B.sInt16_10
+>        and T07A.int64_60  = T04.sInt32_uniq
+>        and T04.uInt32_50p < 30
+>  group by T07A.sInt16_10,T07B.sInt16_10,
+>           T07A.int64_60,T07B.int64_60
+>  order by T07A.int64_60,T07A.sInt16_10
+>;

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

INT64_60              INT64_60              SINT16_10  SINT16_10  (EXPR)
--------------------  --------------------  ---------  ---------  --------------------

                  19                    19          0          0                     1
                  19                    19          1          1                     1
                  19                    19          2          2                     1
                  19                    19          3          3                     1
                  19                    19          4          4                     1
                  19                    19          5          5                     1
                  19                    19          6          6                     1
                  19                    19          7          7                     1
                  19                    19          8          8                     1
                  19                    19          9          9                     1
                  21                    21          0          0                     1
                  21                    21          1          1                     1
                  21                    21          2          2                     1
                  21                    21          3          3                     1
                  21                    21          4          4                     1
                  21                    21          5          5                     1
                  21                    21          6          6                     1
                  21                    21          7          7                     1
                  21                    21          8          8                     1
                  21                    21          9          9                     1
                  26                    26          0          0                     1
                  26                    26          1          1                     1
                  26                    26          2          2                     1
                  26                    26          3          3                     1
                  26                    26          4          4                     1
                  26                    26          5          5                     1
                  26                    26          6          6                     1
                  26                    26          7          7                     1
                  26                    26          8          8                     1
                  26                    26          9          9                     1
                  39                    39          0          0                     1
                  39                    39          1          1                     1
                  39                    39          2          2                     1
                  39                    39          3          3                     1
                  39                    39          4          4                     1
                  39                    39          5          5                     1
                  39                    39          6          6                     1
                  39                    39          7          7                     1
                  39                    39          8          8                     1
                  39                    39          9          9                     1
                  44                    44          0          0                     1
                  44                    44          1          1                     1
                  44                    44          2          2                     1
                  44                    44          3          3                     1
                  44                    44          4          4                     1
                  44                    44          5          5                     1
                  44                    44          6          6                     1
                  44                    44          7          7                     1
                  44                    44          8          8                     1
                  44                    44          9          9                     1
                  50                    50          0          0                     1
                  50                    50          1          1                     1
                  50                    50          2          2                     1
                  50                    50          3          3                     1
                  50                    50          4          4                     1
                  50                    50          5          5                     1
                  50                    50          6          6                     1
                  50                    50          7          7                     1
                  50                    50          8          8                     1
                  50                    50          9          9                     1

--- 60 row(s) selected.
>>
>>?section qcombo6
>>-- Select aggregate + order by
>>prepare P from
+>Select min(T00.char_10)
+>  from $$P00$$ T00
+>  order by 1
+>;

*** ERROR[2105] Unable to compile this query because of incompatible Control Query Shape (CQS) specifications.  Suggestion: Inspect the CQS in effect.

*** ERROR[8822] Unable to prepare the statement.

>>
>>execute P;

*** ERROR[15017] Statement P not found.

>>
>>?section qcombo7
>>-- Join with logical partitioning on a derived table that has a 
>>-- Full GB that can be pushed into DP2.
>>----------------------------------------------------------------
>>--control query shape exchange(cut);
>>control query shape exchange(hybrid_hash_join(
+>  exchange(scan('T14')),
+>  exchange(cut),
+>  type1));

--- SQL operation complete.
>>prepare P from
+>select T14.sInt32_60, T05key1
+>  from $$P14$$ T14, (select sInt32_60
+>                    from $$P05$$
+>                    group by sInt32_60) T05(T05key1)
+>  where     T14.sInt32_60 = T05key1
+>        and T14.int64_100 < 5
+>;

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

SINT32_60    T05KEY1    
-----------  -----------

         20           20
          0            0
         30           30
          1            1
         22           22
          4            4
         31           31
         16           16
         24           24
          7            7
         33           33
         17           17
         29           29
          7            7
         37           37
         18           18
         37           37
         10           10
         38           38
         19           19
         39           39
         14           14
         41           41
         42           42
         50           50
         55           55
         56           56
         57           57
         58           58
         59           59

--- 30 row(s) selected.
>>
>>log;
