core/sql/regress/core/TEST019

-- Test: TEST019 (Core)
-- @@@ 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 @@@
--
-- Functionality: Hash join and hash groupby overflow
-- Expected file: EXPECTED019
-- Filter file: FILTER019
-- Tables created: t019t1, t019t2, t019_starter
--                 t019_a, t019_b, t019_c, t019_d, t019_e, t019_f, t019_g, upsertx
-- Limitations: Only tests overflow in the master executor
-- Revision history:
--     (1/28/02) - Copied from fullstack/TEST019
--     (1/18/08) - Made the Hash-Join and Hash-Groupby to really overflow 
--                 (statistics gives a proof). Also testing inputs and
--                 before/after predicates and a having clause.
--    (10/27/08) - Fixed some test bugs; added overflow tests for anti-semi
--                 (including cluster splits and hash-loops) and hash-groupby
--                 (including resize of the hash table, and distict grouping).

?section ddl

obey test019(clnup);

-- CREATE database
?section crdb

----------
--  D D L
----------

create table t019t1(a1 int, b1 char(4000), c1 int);
create table t019t2(a2 int, b2 char(4000), c2 int);

create table t019left (i int);
create table t019right(j int);

create table t019_starter (a int not null, primary key(a) NOT DROPPABLE );
insert into  t019_starter values (1);

create table t019_a
  (uniq int not null,
   c100  int not null,  
   c10   int not null,
   c1    int not null,
   primary key (uniq)
   NOT DROPPABLE
  )
  STORE BY PRIMARY KEY ;

create table t019_b
  (uniq int not null,
   str1  varchar(50) not null,
   primary key (uniq)
   NOT DROPPABLE
  )
  STORE BY PRIMARY KEY ;

create table t019_c
  (uniq1 int not null,
   uniq2 int not null,
   str1  varchar(50) not null,
   c100  int not null,  
   c10   int not null,
   c1    int not null,
   primary key (uniq1,uniq2)
   NOT DROPPABLE
  )
  STORE BY PRIMARY KEY ;

create table t019_d like t019_c;

-- for HGB, no PK
create table t019_e
  (uniqb int not null,
   c1 int not null,
   str1  varchar(50) not null 
  ) no partition ;

-- for counting the number of result groups
create table t019_f (sums int not null) no partition ;

create table t019_g 
  (uniqb int not null,
   str1  varchar(50) not null) no partition ;

cqd hbase_serialization 'off' ;
create table upsertx(id int not null, a int, b int,
                          primary key (id));
create index upsertix1 on upsertx(a);
cqd hbase_serialization 'on' ;



-- INSERT queries
begin work;
insert into t019t1 values( 5138, 'this is row 0 of table t019t1',100);
insert into t019t1 values( 1757, 'this is row 1 of table t019t1',100);
insert into t019t1 values( 3086, 'this is row 2 of table t019t1',100);
insert into t019t1 values( 5345, 'this is row 3 of table t019t1',100);
insert into t019t1 values( 9999, 'this is row 4 of table t019t1',100);
insert into t019t1 values( 1717, 'this is row 5 of table t019t1',100);
insert into t019t1 values( 7022, 'this is row 6 of table t019t1',100);
insert into t019t1 values( 2264, 'this is row 7 of table t019t1',100);
insert into t019t1 values( 4947, 'this is row 8 of table t019t1',100);
insert into t019t1 values( 1247, 'this is row 9 of table t019t1',100);
insert into t019t1 values(   13, 'this is row 10 of table t019t1',100);
insert into t019t1 values( 3801, 'this is row 11 of table t019t1',100);
insert into t019t1 values( 2772, 'this is row 12 of table t019t1',100);
insert into t019t1 values( 3680, 'this is row 13 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 14 of table t019t1',100);
insert into t019t1 values( 5353, 'this is row 15 of table t019t1',100);
insert into t019t1 values( 7656, 'this is row 16 of table t019t1',100);
insert into t019t1 values( 6464, 'this is row 17 of table t019t1',100);
insert into t019t1 values( 7671, 'this is row 18 of table t019t1',100);
insert into t019t1 values( 7802, 'this is row 19 of table t019t1',100);
insert into t019t1 values( 8229, 'this is row 20 of table t019t1',100);
insert into t019t1 values( 1519, 'this is row 21 of table t019t1',100);
insert into t019t1 values( 6254, 'this is row 22 of table t019t1',100);
insert into t019t1 values( 3146, 'this is row 23 of table t019t1',100);
insert into t019t1 values( 3469, 'this is row 24 of table t019t1',100);
insert into t019t1 values( 9172, 'this is row 25 of table t019t1',100);
insert into t019t1 values( 5197, 'this is row 26 of table t019t1',100);
insert into t019t1 values( 4011, 'this is row 27 of table t019t1',100);
insert into t019t1 values( 6067, 'this is row 28 of table t019t1',100);
insert into t019t1 values( 7854, 'this is row 29 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 30 of table t019t1',100);
insert into t019t1 values( 8699, 'this is row 31 of table t019t1',100);
insert into t019t1 values( 8665, 'this is row 32 of table t019t1',100);
insert into t019t1 values( 6745, 'this is row 33 of table t019t1',100);
insert into t019t1 values( 7583, 'this is row 34 of table t019t1',100);
insert into t019t1 values( 5818, 'this is row 35 of table t019t1',100);
insert into t019t1 values( 3892, 'this is row 36 of table t019t1',100);
insert into t019t1 values( 3556, 'this is row 37 of table t019t1',100);
insert into t019t1 values( 2002, 'this is row 38 of table t019t1',100);
insert into t019t1 values( 8269, 'this is row 39 of table t019t1',100);
insert into t019t1 values( 4159, 'this is row 40 of table t019t1',100);
insert into t019t1 values( 4635, 'this is row 41 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 42 of table t019t1',100);
insert into t019t1 values( 1264, 'this is row 43 of table t019t1',100);
insert into t019t1 values( 2126, 'this is row 44 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 45 of table t019t1',100);
insert into t019t1 values( 7374, 'this is row 46 of table t019t1',100);
insert into t019t1 values( 4090, 'this is row 47 of table t019t1',100);
insert into t019t1 values( 7801, 'this is row 48 of table t019t1',100);
insert into t019t1 values( 7578, 'this is row 49 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 50 of table t019t1',100);
insert into t019t1 values(   13, 'this is row 51 of table t019t1',100);
insert into t019t1 values( 3187, 'this is row 52 of table t019t1',100);
insert into t019t1 values( 7569, 'this is row 53 of table t019t1',100);
insert into t019t1 values( 2429, 'this is row 54 of table t019t1',100);
insert into t019t1 values( 1819, 'this is row 55 of table t019t1',100);
insert into t019t1 values(   13, 'this is row 56 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 57 of table t019t1',100);
insert into t019t1 values( 3191, 'this is row 58 of table t019t1',100);
insert into t019t1 values(   13, 'this is row 59 of table t019t1',100);
insert into t019t1 values( 4418, 'this is row 60 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 61 of table t019t1',100);
insert into t019t1 values( 5722, 'this is row 62 of table t019t1',100);
insert into t019t1 values( 1834, 'this is row 63 of table t019t1',100);
insert into t019t1 values( 5697, 'this is row 64 of table t019t1',100);
insert into t019t1 values( 2520, 'this is row 65 of table t019t1',100);
insert into t019t1 values( 4958, 'this is row 66 of table t019t1',100);
insert into t019t1 values( 2367, 'this is row 67 of table t019t1',100);
insert into t019t1 values( 4769, 'this is row 68 of table t019t1',100);
insert into t019t1 values( 4060, 'this is row 69 of table t019t1',100);
insert into t019t1 values( 8729, 'this is row 70 of table t019t1',100);
insert into t019t1 values( 4269, 'this is row 71 of table t019t1',100);
insert into t019t1 values( 3560, 'this is row 72 of table t019t1',100);
insert into t019t1 values( 3819, 'this is row 73 of table t019t1',100);
insert into t019t1 values(   13, 'this is row 74 of table t019t1',100);
insert into t019t1 values( 4147, 'this is row 75 of table t019t1',100);
insert into t019t1 values( 1823, 'this is row 76 of table t019t1',100);
insert into t019t1 values( 6255, 'this is row 77 of table t019t1',100);
insert into t019t1 values(  971, 'this is row 78 of table t019t1',100);
insert into t019t1 values( 4008, 'this is row 79 of table t019t1',100);
insert into t019t1 values( 7606, 'this is row 80 of table t019t1',100);
insert into t019t1 values( 2448, 'this is row 81 of table t019t1',100);
insert into t019t1 values( 3428, 'this is row 82 of table t019t1',100);
insert into t019t1 values( 2299, 'this is row 83 of table t019t1',100);
insert into t019t1 values( 2978, 'this is row 84 of table t019t1',100);
insert into t019t1 values( 3045, 'this is row 85 of table t019t1',100);
insert into t019t1 values( 8872, 'this is row 86 of table t019t1',100);
insert into t019t1 values(   13, 'this is row 87 of table t019t1',100);
insert into t019t1 values( 6511, 'this is row 88 of table t019t1',100);
insert into t019t1 values( 3986, 'this is row 89 of table t019t1',100);
insert into t019t1 values( 6259, 'this is row 90 of table t019t1',100);
insert into t019t1 values( 6762, 'this is row 91 of table t019t1',100);
insert into t019t1 values( 7325, 'this is row 92 of table t019t1',100);
insert into t019t1 values( 9993, 'this is row 93 of table t019t1',100);
insert into t019t1 values( 7174, 'this is row 94 of table t019t1',100);
insert into t019t1 values( 8384, 'this is row 95 of table t019t1',100);
insert into t019t1 values( 9672, 'this is row 96 of table t019t1',100);
insert into t019t1 values( 7174, 'this is row 97 of table t019t1',100);
insert into t019t1 values( 7174, 'this is row 98 of table t019t1',100);
insert into t019t1 values( 7178, 'and this row should match',100);
insert into t019t1 values( 7178, 'and this row too should match',100);
insert into t019t1 values( 7178, 'and again should match',100);
insert into t019t2 values( 4147, 'this is row 0 of table t019t2',200);
insert into t019t2 values( 3560, 'this is row 1 of table t019t2',300);
insert into t019t2 values( 1834, 'this is row 2 of table t019t2',400);
insert into t019t2 values( 7606, 'this is row 3 of table t019t2',500);
insert into t019t2 values( 7178, 'this is row 4 of table t019t2',600);
insert into t019t2 values( 3801, 'this is row 5 of table t019t2',700);
insert into t019t2 values( 6255, 'this is row 6 of table t019t2',800);
insert into t019t2 values( 1953, 'this is row 7 of table t019t2',900);
insert into t019t2 values( 1819, 'this is row 8 of table t019t2',950);
insert into t019t2 values( 7174, 'this is row 9 of table t019t2',999);
commit work;

-- SELECT queries
?section dml

-- needed for code coverage for the logging code
control query default EXE_DIAGNOSTIC_EVENTS 'ON';

log LOG019 clear;

-- worst case scenario: force overflow (in the master only) on every request 
control query default EXE_TEST_HASH_FORCE_OVERFLOW_EVERY '1';
control query default ATTEMPT_ESP_PARALLELISM 'OFF';

control query default detailed_statistics 'ALL';
control query default QUERY_CACHE '0';

--------------------------------------------------------------------
--  hash joins with parameters, predicates and overflow handling
--------------------------------------------------------------------

-- shape of the following query
control query shape hybrid_hash_join(cut,cut);

-- hash join, with input param and before predicate
prepare stmt from
  select cast (a2 as smallint), cast (b2 as char(30)),
         cast (a1 as smallint), cast (b1 as char(30)) from
       t019t2
         left join
       t019t1 on a1 = a2 
             and c1 + c2 < ? ;    -- before-join predicate

-- before predicate should fail for some probes
-- query should return 10 rows, where 7 are null extended
execute stmt using 555;

-- test SHOWPLAN, the regression tool can report core file if generated
log;
SHOWPLAN
  select cast (a2 as smallint), cast (b2 as char(30)),
         cast (a1 as smallint), cast (b1 as char(30)) from
       t019t2
         left join
       t019t1 on a1 = a2
             and c1 + c2 < ? ;

log LOG019;

control query shape cut ; 

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)      -- VARIABLE_INFO 
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASHJ' and VAL3 > 0 ; 

-- shape of the following query
control query shape hybrid_hash_join(cut,cut);

-- hash join, with input param and after predicate
prepare stmt from
  select cast (a2 as smallint), cast (b2 as char(30)),
         cast (a1 as smallint), cast (b1 as char(30)) from
     t019t2 tt1
        join 
     t019t1 tt2
     on isnull(a1,9999) = a2
     where isnull(a1,2) < ? or a2 + a1 > ?  -- after-join predicate
  ;

-- the after predicate should fail for some probes
-- should return 11 rows
execute stmt using 4000, 14000;

-- test SHOWPLAN, the regression tool can report core file if generated
log;
SHOWPLAN
  select cast (a2 as smallint), cast (b2 as char(30)),
         cast (a1 as smallint), cast (b1 as char(30)) from
     t019t2 tt1
        join
     t019t1 tt2
     on isnull(a1,9999) = a2
     where isnull(a1,2) < ? or a2 + a1 > ?  -- after-join predicate
  ;

log LOG019;

control query shape cut ; 

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)      -- VARIABLE_INFO 
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASHJ' and VAL3 > 0 ; 

--------------------------------------------------------------------------
--  hash grouping with parameters, predicates, and overflow handling
--------------------------------------------------------------------------
#ifdef SEABASE_REGRESS
control query shape hash_groupby(scan);
#else
control query shape hash_groupby(partition_access(scan));
#endif
control query default GEN_HGBY_BUFFER_SIZE '57344';
control query default EXE_HGB_INITIAL_HT_SIZE  '57344';

-- a hash-groupby with a having-predicate using a parameter
prepare stmt from 
   select sum(a1), cast (b1 as char(30))
         from t019t1 group by b1 having sum(a1) >= ? ;

execute stmt using 9999 ; -- one row expected

-- test SHOWPLAN, the regression tool can report core file if generated
log;
SHOWPLAN
   select sum(a1), cast (b1 as char(30))
         from t019t1 group by b1 having sum(a1) >= ? ;
log LOG019;

control query shape cut ; 

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)      -- VARIABLE_INFO 
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASH_GRBY' and VAL3 > 0 ; 

-- try re-executing with a different input (9 rows expected)
execute stmt using 9993 ;

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASH_GRBY' and VAL3 > 0 ; 

-------------
-- Test FOJ
-------------
-- with an empty outer and overflow
insert into t019right values (1),(2),(3);
-- Should return 3 rows, null extended (see case 10-081201-3607)
select * from t019left full join t019right on i = j ;

-- with cluster split
-- control query default EXE_HJ_FORCE_CLUSTER_SPLIT 'ON';
insert into t019left values (1),(2),(3);
-- should return three rows, no null extensions (see case 10-081205-8182)
select * from t019left full join t019right on i = j ;

-- test SHOWPLAN, the regression tool can report core file if generated
log;
SHOWPLAN
insert into t019left values (1),(2),(3);

SHOWPLAN
select * from t019left full join t019right on i = j ;
log LOG019;

-------------------
-- now reset:
-------------------
--  No special overflow
control query default EXE_TEST_HASH_FORCE_OVERFLOW_EVERY reset;
-- control query default EXE_HJ_FORCE_CLUSTER_SPLIT 'OFF';
control query shape cut ; 

--------------------------------------------------------------------
-- 
--         Test Anti Semi Join Overflow
--
--------------------------------------------------------------------

-- populate tables

-- t019_a Cross Product with t019_b would give about 80 MB inner table 
#ifdef SEABASE_REGRESS
upsert using load into t019_a
#else
insert into t019_a
#endif
  select   
   0 + (100 * x100) + (10 * x10) + (1 * x1),   
   0 + (10 * x10) + (1 * x1),
   0 + (1 * x1),
   0
  from t019_starter  
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;

#ifdef SEABASE_REGRESS
upsert using load into t019_b
#else
insert into t019_b
#endif
  select   
   0 + (100 * x100) + (10 * x10) + (1 * x1),   
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
  from t019_starter  
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;

-- t019_c is the left table for the Anti-Semi-Join
#ifdef SEABASE_REGRESS
upsert using load into t019_c
#else
insert into t019_c
#endif
  select   
   0 + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (100 * x100) + (10 * x10) + (1 * x1),
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
   0 + (10 * x10) + (1 * x1),
   0 + (1 * x1),
   0
  from t019_starter  
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;

-- upsertx queries test index maintenance corruption for upsert
upsert into upsertx
select num, num+100, num+100
from (select 10*e1 + e0
      from (values (0)) seed(s)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0
      transpose 0,1,2,3,4,5,6,7,8,9 as e1) t(num);

upsert into upsertx
select 10*num, 10*num+200, 10*num+200
from (select e0
      from (values (0)) seed(s)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0) t(num);

select count(*) from upsertx;
set parserflags 1;
select count(*) from table(index_table UPSERTIX1);

-- test SHOWPLAN, the regression tool can report core file if generated
log;
SHOWPLAN
#ifdef SEABASE_REGRESS
upsert using load into t019_c
#else
insert into t019_c
#endif
  select  
   0 + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (100 * x100) + (10 * x10) + (1 * x1),
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
   0 + (10 * x10) + (1 * x1),
   0 + (1 * x1),
   0
  from t019_starter 
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;

log LOG019;

-- use internal CQD to force an overflow after 30 MB
control query default EXE_MEM_LIMIT_PER_BMO_IN_MB '30';

-- Ensure 100 rows from t019_c have no match in ( t019_a X t019_b )
update t019_c set c100 = c100 + 1 where c10 = 7 ;

-- push HJ to the limits -- force both cluster splits and hash-loops !!
control query default EXE_TEST_FORCE_CLUSTER_SPLIT_AFTER_MB '1';
control query default EXE_TEST_FORCE_HASH_LOOP_AFTER_NUM_BUFFERS '10';

#ifdef SEABASE_REGRESS
control query shape nested_join(sort(hybrid_hash_join(scan,
hybrid_hash_join(scan,scan))),insert);
#else
control query shape nested_join(sort(hybrid_hash_join(partition_access(scan),
hybrid_hash_join(partition_access(scan),partition_access(
scan)))),partition_access(insert));
#endif

prepare stmt from
  insert into t019_d
    ( select * from t019_c
      where
        (uniq1, c100, c10, c1, uniq2, str1) NOT IN 
                       (select * from t019_a, t019_b) ) ;

-- Use explain to check that indeed we got a hybrid_hash_anti_semi_join
-- explain options 'f' stmt;

-- 100 rows should be inserted -- anything less shows that ASJ overflow bug
execute stmt;

-- test SHOWPLAN, the regression tool can report core file if generated
log;
SHOWPLAN
  insert into t019_d
    ( select * from t019_c
      where
        (uniq1, c100, c10, c1, uniq2, str1) NOT IN
                       (select * from t019_a, t019_b) ) ;

log LOG019;

control query shape cut ; 

-- The filter019 file checks for overflow, splits and hash loops by
--   filtering VARIABLE_INFO for "TotWriteIOs" (also TotReadIOs or IOBytes
--   are good) and "ClusterSplits" and "HashLoops"
-- Query has two HJs -- the anti-semi overflows, the cross-product does not!
select VARIABLE_INFO
   from table(statistics(NULL,'STMT')) 
       where TDB_NAME = 'EX_HASHJ' and VAL3 > 0 ; 

control query default EXE_TEST_FORCE_CLUSTER_SPLIT_AFTER_MB reset;
control query default EXE_TEST_FORCE_HASH_LOOP_AFTER_NUM_BUFFERS reset;
----------------------------------------------------------------------
-- 
-- Test Groupby Overflow
--
----------------------------------------------------------------------

#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t019_e
  select   
    0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10) + x1,   
    0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),   
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
  from t019_starter  
    transpose 0,1,2,3,4,5,6,7,8,9 as x10000
    transpose 0,1,2,3,4,5,6,7,8,9 as x1000
    transpose 0,1,2,3,4,5,6,7,8,9 as x100
    transpose 0,1,2,3,4,5,6,7,8,9 as x10
    transpose 0,1,2,3,4,5,6,7,8,9 as x1
  ;

-- use internal CQD to force an overflow after 3 MB
control query default EXE_MEM_LIMIT_PER_BMO_IN_MB '3';

-- start very small to force several HT resizes to take place
control query default EXE_HGB_INITIAL_HT_SIZE '3584';

#ifdef SEABASE_REGRESS
control query shape nested_join(hash_groupby(scan), insert);
#else
control query shape nested_join(hash_groupby(partition_access(
scan )), partition_access(insert));
#endif


prepare stmt from
#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t019_f
    ( select sum(c1) from t019_e group by uniqb,str1 );

-- 100 K rows should be inserted
execute stmt;

control query shape cut;

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASH_GRBY' and VAL3 > 0 ; 

#ifdef SEABASE_REGRESS
control query shape nested_join(hash_groupby(scan), insert);
#else
control query shape nested_join(hash_groupby(partition_access(
scan )), partition_access(insert));
#endif

-- Test DISTINCT (HGB with no aggregates; such an HGB returns output in a non
-- blocking fashion until an overflow occurs)
prepare stmt from
#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t019_g
    ( select distinct uniqb,str1 from t019_e  );

-- should insert 100000 rows
execute stmt;

control query shape cut;

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASH_GRBY' and VAL3 > 0 ; 


-- Test solution 10-100831-2840: Extra duplicate rows returned by HGB Distinct

-- Create 9998 duplicate rows (last two are getting new unique values)
-- (These dups are evenly spread across the buffers to show that bug.) 
update t019_e set uniqb = uniqb + 25 where mod(uniqb,10) = 0;

-- Repeat the above test, this time with duplicates
purgedata t019_g;
#ifdef SEABASE_REGRESS
control query shape nested_join(hash_groupby(scan), insert);
#else
control query shape nested_join(hash_groupby(partition_access(
scan )), partition_access(insert));
#endif

-- Test DISTINCT (HGB with no aggregates; such an HGB returns output in a non
-- blocking fashion until an overflow occurs)
prepare stmt from
#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t019_g
    ( select distinct uniqb,str1 from t019_e  );

-- now should insert 90002 rows; anything higher shows that bug
execute stmt;

control query shape cut;

-- Statistics hold a proof that overflow took place:
--   VAL3 counts IOs , so after an overflow should return 1 below, else 0 !
select  count(VAL3)
      from table(statistics(NULL,'STMT'))	
	  where TDB_NAME = 'EX_HASH_GRBY' and VAL3 > 0 ; 

log;

-------------
-- This part is not logged; it is only used to improve code coverage for
-- bitmux, partial hgb, and table timeouts
-------------
control query default HGB_BITMUX 'ON';
control query default ATTEMPT_ESP_PARALLELISM 'MAXIMUM';
control query shape hash_groupby(cut);

prepare stmt from
   select [last 1] sum(T1.c1+T2.c1) 
        from t019_e T1, t019_e T2 where T1.c1 = T2.c1 
            group by T1.uniqb,T2.uniqb,T1.str1,T2.str1;

execute stmt;

-- Test for SHOWPLAN to make sure no core file is generated.
SHOWPLAN
   select [last 1] sum(T1.c1+T2.c1)
        from t019_e T1, t019_e T2 where T1.c1 = T2.c1
            group by T1.uniqb,T2.uniqb,T1.str1,T2.str1;

set table t019_e timeout 100;
set table * stream timeout 100;

execute stmt;

control query default HGB_BITMUX reset;
control query default ATTEMPT_ESP_PARALLELISM reset;
set table t019_e timeout reset;
set table * stream timeout reset;

------//cif - defragmentation -- code coverage -- 
cqd nested_joins 'off';
cqd merge_joins 'off';
cqd hash_joins 'on';
cqd attempt_esp_parallelism 'off';

control query shape cut;

create table tmptab1 store by (uniq1) as select t1.uniq1 * 1000 + t2.uniq1 as uniq1, t1.uniq2*1000 + t2.uniq2 as uniq2,
cast(repeat(t1.str1,7) as varchar(1500)) str1, t1.c100,t1.c10,t1.c1 from T019_D t1, T019_D t2;
update statistics for table tmptab1 on every column;
select [last 1] * from tmptab1 t1 join tmptab1 t2 on t1.uniq2=t2.uniq2 order by t1.uniq2,t2.uniq2;

control query shape sort(hash_groupby(cut));

select [last 1] t1.uniq2,t2.uniq2,t1.str1,t2.str1,count(*) from tmptab1 t1 join tmptab1 t2 on t1.uniq2=t2.uniq2 group by t1.uniq2,t2.uniq2,t1.str1,t2.str1 order by t1.uniq2,t2.uniq2;

cqd attempt_esp_parallelism reset;
control query shape  cut;
create table tmptab2 store by (uniq) as  
select t1.uniq2 uniq,t2.uniq2,t1.str1 str11 ,t2.str1 str12,count(*) cnt from tmptab1 t1 join tmptab1 t2 on t1.uniq2=t2.uniq2 group by t1.uniq2,t2.uniq2,t1.str1,t2.str1 ;


exit;

?section clnup
-- CLEANUP database
drop table tmptab1;
drop table tmptab2;
drop table t019t1 cascade;
drop table t019t2 cascade;

drop table t019left;
drop table t019right;

drop table t019_starter;

drop table t019_a;
drop table t019_b;
drop table t019_c;
drop table t019_d;

drop table t019_e;
drop table t019_f;
drop table t019_g;

drop table upsertx;