-- Test: TEST107 (Executor)
-- @@@ 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: Sort tests with scratch overflow
-- Expected files: EXPECTED107
-- Table created: t107helper t107100k
-- Limitations: 

obey TEST107(clenup);

log LOG107 clear;

control query default ATTEMPT_ESP_PARALLELISM 'off';
control query default query_cache '0';
control query default EXE_DIAGNOSTIC_EVENTS 'on';

obey TEST107(setup);

obey TEST107(dml);
#ifLINUX
obey TEST107(cif_dml);
#ifLINUX
log;

-- before cleanup, do a showplan to get better code coverage.
obey TEST107(dml1);


obey TEST107(clenup);

exit;

?section clenup
-----------------
drop table t107helper;
drop table t107100k cascade;
drop table t107t2 cascade;
drop table t107t3 cascade;

?section setup
---------------
create table t107helper (a int not null, primary key(a));
insert into  t107helper values (1);

create table t107100k
  (uniq int not null,
   c100K  int,
   str1  varchar(4000),
   primary key (uniq)
  )
#ifndef SEABASE_REGRESS
hash partition by(uniq)
(add location $$partition1$$, add location $$partition2$$, add location $$partition3$$)
#endif
;

#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t107100k
  select
   0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
   0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
  from t107helper
    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 as x1
  ;
update statistics for table t107100k on  every column sample 1000 rows;
#ifLINUX
create table t107t2
  (uniq int not null,
   c100K int,
   c100kchar char(10),
   vc1 varchar(800),
   fc1 char(255),
   vc2 varchar(500),
   fc2 char(200),
   vc3 varchar(600),
   int1 INT,
   lint1 LARGEINT,
   primary key (uniq)
  )
#ifndef SEABASE_REGRESS
hash partition by(uniq)
(add location $$partition1$$, add location $$partition2$$, add location $$partition3$$)
#endif
;

control query default comp_bool_140 'on';
#ifdef SEABASE_REGRESS
upsert using load into
#else
insert into
#endif
t107t2(uniq,c100k,c100kchar,vc1,fc1,vc2,fc2,vc3,int1,lint1)
select 
  uniq,
  uniq c100k,
  lpad(rtrim(cast(uniq as char(10))),10,'0') c100kchar,
  case when mod(uniq,2) = 0 then 
  repeat('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',10)
  else
  repeat('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',5)
  end vc1,
  'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy' fc1,
  case when mod(uniq,2) =0 then
  repeat('zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz',6)
  else
   repeat('zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz',3) 
  end vc2,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' fc2,

  'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' vc3,
  uniq + 1000 int1,
  uniq*uniq lint1 FROM t107100k;

control query default comp_bool_140 reset;
update statistics for table t107t2 on  every column sample 1000 rows;

create table t107t3
  (i    int ,
   str1 char(100000),
   str2 char(100000),
   str3 char(100000),
   str4 char(100000),
   str5 char(100000),
   str6 char(100000)
  );
insert into t107t3 values(1,'a','b','c','d','e','f'), (2,'a','b','c','d','e','f');

#ifLINUX

?section dml
-------------
-- Test #1: In memory sort ,double & copy
prepare xx from SELECT [last 1] c100k from t107100k order by c100k;
execute xx;

-- Test #1A: uses TOPN sort
prepare xx from SELECT [first 10] c100k from t107100k order by c100k;
execute xx;

-- Test#1B: negetive test, zero records to sort.
prepare xx from SELECT c100k from t107100k where c100k < 0 order by c100k;
execute xx;

-- Test#1C: negetive test, zero records to sort, TOPN
prepare xx from SELECT [first 10] c100k from t107100k where c100k < 0 order by c100k;
execute xx;

-- Test #2: SQL buffer overflow, overflow and merge
set envvar SCRATCH_IO_PENDING 280;
control query default GEN_SORT_MAX_NUM_BUFFERS  '1';
prepare xx from SELECT [last 1] c100k, str1 from t107100k order by 1,2;
execute xx;

-- Test #2a: sortRecLen > 512kb scratchIOBlock, overflow.
cqd GEN_SORT_MAX_NUM_BUFFERS  '1';
cqd GEN_SORT_NUM_BUFFERS '1';
cqd GEN_SORT_MAX_BUFFER_SIZE '650000';
cqd COMPRESSED_INTERNAL_FORMAT 'OFF';
cqd COMPRESSED_INTERNAL_FORMAT_BMO 'OFF';
SELECT [last 0] i, str1, str2, str3, str4, str5, str6  from t107t3 order by 1;
cqd GEN_SORT_MAX_NUM_BUFFERS reset;
cqd GEN_SORT_NUM_BUFFERS reset;
cqd GEN_SORT_MAX_BUFFER_SIZE reset;
cqd COMPRESSED_INTERNAL_FORMAT reset;
cqd COMPRESSED_INTERNAL_FORMAT_BMO reset;


-- Test #3 - causes intermediate merge
set envvar SCRATCH_IO_PENDING 50;
control query default GEN_SORT_MAX_NUM_BUFFERS  '20';
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default GEN_SORT_MAX_NUM_BUFFERS  '1';
control query default SORT_MAX_HEAP_SIZE_MB    '1';
prepare xx from SELECT [last 1] c100k, str1 from t107100k order by 1,2;
execute xx;

?section dml1
-------------
-- showplan example
showplan SELECT [last 1] c100k from t107100k order by c100k;


?section cif_dml
---------------------------------------

control query default COMPRESSED_INTERNAL_FORMAT 'ON';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'ON';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '100'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default SORT_MAX_HEAP_SIZE_MB    reset;

prepare s from SELECT  [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2   where c100k <=100000 order by c100kchar,vc2,fc2;

execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'OFF';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'OFF';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; -- 200 rows approx NSK
control query default GEN_SORT_MAX_NUM_BUFFERS  '100'; -- total of 8000 rows per run NSK
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default SORT_MAX_HEAP_SIZE_MB    reset;

prepare s from SELECT [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2 where c100k <=100000 order by c100kchar,vc2,fc2;
execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'ON';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'ON';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '20'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default SORT_MAX_HEAP_SIZE_MB    reset;

prepare s from SELECT  [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2   where c100k <=100000 order by c100kchar,vc2,fc2;
execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'OFF';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'OFF';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '20'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default SORT_MAX_HEAP_SIZE_MB    reset;

prepare s from SELECT  [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2  where c100k <=100000 order by c100kchar,vc2,fc2;
execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'ON';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'ON';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '10'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default EXE_BMO_DISABLE_CMP_HINTS_OVERFLOW_SORT 'ON';
control query default SORT_MAX_HEAP_SIZE_MB    '2';

prepare s from SELECT  [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2   where c100k <=100000 order by c100kchar,vc2,fc2;
execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'OFF';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'OFF';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '10'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default EXE_BMO_DISABLE_CMP_HINTS_OVERFLOW_SORT 'ON';
control query default SORT_MAX_HEAP_SIZE_MB    '2';

prepare s from SELECT [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2  where c100k <=100000 order by c100kchar,vc2,fc2;
execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'OFF';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'ON';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '10'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default EXE_BMO_DISABLE_CMP_HINTS_OVERFLOW_SORT 'ON';
control query default SORT_MAX_HEAP_SIZE_MB    '2';

prepare s from SELECT [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2  where c100k <=100000 order by c100kchar,vc2,fc2;
execute s;

control query default COMPRESSED_INTERNAL_FORMAT 'ON';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'OFF';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '10'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default EXE_BMO_DISABLE_CMP_HINTS_OVERFLOW_SORT 'ON';
control query default SORT_MAX_HEAP_SIZE_MB    '2';

prepare s from SELECT [last 1] c100kchar, vc1, vc2,fc1,vc3,fc2,int1,lint1 from t107t2  where c100k <=100000 order by c100kchar,vc2,fc2;

execute s;
-----------------------------
control query default COMPRESSED_INTERNAL_FORMAT 'ON';
control query default COMPRESSED_INTERNAL_FORMAT_BMO 'ON';

control query default GEN_SORT_MAX_BUFFER_SIZE '3276800'; 
control query default GEN_SORT_MAX_NUM_BUFFERS  '20'; 
control query default SORT_MEMORY_QUOTA_SYSTEM 'OFF';
control query default SORT_MAX_HEAP_SIZE_MB    reset;

prepare s from SELECT [last 1] c100kchar, fc1,fc2,int1,lint1 from t107t2  where c100k <=100000 order by c100kchar,fc2;
execute s;

prepare s from SELECT [last 1] int1,lint1 from t107t2  where c100k <=100000 order by int1;
execute s;

prepare s from SELECT [last 1] int1,lint1 from t107t2  where c100k <=100000 order by int1,lint1;
execute s;


prepare s from SELECT [last 1] vc1 || c100kchar, vc2 , vc3 from t107t2  where c100k <=100000 order by 1,2;
execute s;
