-- Test: TEST0106 (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: SQL Query Cancel
-- Expected files: EXPECTED106
-- Table created:
-- Limitations:
-- To do:
-- Revision history:

-- temporarily turning off ddl xns for this test suite.
-- it runs into a hang issue in dtm due to cancel processing.
cqd ddl_transactions 'OFF';

sh echo > cumulative_GET_STATS106; 
sh echo > LOG106;
sh echo > T106_INTERLEAVED_LOG;
sh rm ${REGRRUNDIR}/test106_synch ; 

sh sqlci -i"TEST106(setup)";

log LOG106;
obey TEST106(dml);

select msg, 'FUNKY_OPT_UNIQUE', ts
  from T106_interleaved_log order by ts;

obey TEST106(clnup);

log;

exit;

?section clnup

drop table t106a;
drop table t106c;
log T106_INTERLEAVED_LOG;
select * from T106_interleaved_log 
  order by ts;
drop table T106_interleaved_log;

?section setup

set schema $$TEST_SCHEMA$$;

obey TEST106(clnup);

log LOG106 clear;

create table T106_interleaved_log (
                   ts timestamp(6) not null,
                   msg char(60) ) no partition;
 

create table t106a
  (uniq int not null,
   c10K int ,
   c1K   int,
   c100  int,
   c10   int,
   c1    int,
   primary key (uniq)
  )
salt using 4 partitions ;

upsert using load into t106a
  select
  0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100) 
    + (10 * x10) +( 1 * x1),
  0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (10 * x10) + (1 * x1),
  0 + (1 * x1),
  0
  from (values(1)) as t1
    transpose 0,1                 as x100000
    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
  ;

update statistics for table t106a on every column;

create table t106c
  (uniq int not null,
   c10K int ,
   c1K   int,
   c100  int,
   c10   int,
   c1    int,
   primary key (uniq)
  )
salt using 4 partitions ;

?section dml

sh rm cancel_cmd cancel_log106_all LOCK_LOG106;

obey TEST106(cursor_close_early);

obey TEST106(uniq_query_test);

obey TEST106(positive_test1);

obey TEST106(positive_test2);

obey TEST106(update_test1);

obey TEST106(delete_test1);

obey TEST106(insert_test1);

obey TEST106(insert_test2);

obey TEST106(upsert_test1);

obey TEST106(cpu_bound_esps);

obey TEST106(escalation1);

#ifdef SEABASE_CANCEL_TBD

obey TEST106(positive_test1_pname);

obey TEST106(positive_test2_pname);

obey TEST106(cpu_bound_esps_pname);

obey TEST106(escalation1_pname);

obey TEST106(positive_test1_nid_pid);

obey TEST106(positive_test2_nid_pid);

obey TEST106(cpu_bound_esps_nid_pid);

obey TEST106(escalation1_nid_pid);
#endif

obey TEST106(showplan1);

obey TEST106(explain1);

obey TEST106(invalid_qid);

obey TEST106(invalid_pname);

obey TEST106(invalid_nid_pid);

obey TEST106(qc1314);

obey TEST106(qc1351);

-- this one removes histograms for t106a so do it at the end.
obey TEST106(ustats_test);


?section cursor_close_early
-- From bug 2583

set envvar sqlci_cursor '1';

declare c1 cursor for select * from t106a A, t106a B;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;
close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;
close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;
close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

?section uniq_query_test
-- By default, cannot cancel unique queries. And since we 
-- do not have locks in trafodion, it doesn't make sense to
-- try to cancel a unique query. 
-- TBD -- add tests to show the unique query does not register
-- with cancel broker

---------- unique select query ---------------------

prepare s1 from select 'unique query row' from t106a where uniq = 44;

---------- unique delete query ---------------------

prepare s1 from delete from t106a where uniq = 44;

---------- unique update query ---------------------

prepare s1 from update t106a set c10k = 12 where uniq = 46;

---------- unique insert query ---------------------

prepare s1 from insert into t106a values (44, 2, 3, 4, 5, 6);

?section positive_test1

-- Test canceling a query with and without the 
-- CANCEL_QUERY_ALLOWED.

------- long running, cpu-bound select query -------

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
control query default ATTEMPT_ESP_PARALLELISM 'off';

prepare s1 from select count(*) from t106a A, t106a B 
where A.uniq < 3000;

control query default ATTEMPT_ESP_PARALLELISM 'system';
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1, launch canceler, finish');

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1, execute, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1, execute, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section positive_test2

-- Test canceling a query with and without the 
-- CANCEL_QUERY_ALLOWED.

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

control query shape expr(sort_groupby(esp_exchange(cut)));

prepare s1 from select count(*) from t106a A, t106a B 
where A.uniq < 3000;

control query shape cut;

control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2, execute s1, start');

------- long running, cpu-bound select query -------
execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2, execute s1, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section update_test1

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

prepare s1 from update t106a set c100 = c100 + 1; 

control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'update_test1, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'update_test1, launch canceler, finish');

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values 
(CURRENT_TIMESTAMP (6),
'update_test1, execute, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'update_test1, execute, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section delete_test1

prepare s1 from delete from t106a;

obey TEST106(make_cancel_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'delete_test1, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'delete_test1, launch canceler, finish');

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values 
(CURRENT_TIMESTAMP (6),
'delete_test1, execute, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'delete_test1, execute, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section insert_test1

prepare s1 from insert into t106c select * from t106a;
obey TEST106(make_cancel_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'insert_test1, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'insert_test1, launch canceler, finish');

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values 
(CURRENT_TIMESTAMP (6),
'insert_test1, execute, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'insert_test1, execute, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section insert_test2

prepare s1 from insert into t106c 
  select
  0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100)
    + (10 * x10) +( 1 * x1),
  0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (10 * x10) + (1 * x1),
  0 + (1 * x1),
  0
  from (values(1)) as t1
    transpose 0,1                 as x100000
    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
  ;


obey TEST106(make_cancel_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'insert_test2, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'insert_test2, launch canceler, finish');

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values 
(CURRENT_TIMESTAMP (6),
'insert_test2, execute, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'insert_test2, execute, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section upsert_test1

prepare s1 from 
upsert using load into t106c 
  select
  0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100)
    + (10 * x10) +( 1 * x1),
  0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (10 * x10) + (1 * x1),
  0 + (1 * x1),
  0
  from (values(1)) as t1
    transpose 0,1,2,3,4,5,6,7,8,9 as x100000
    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
  ;


obey TEST106(make_cancel_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'upsert_test1, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'upsert_test1, launch canceler, finish');

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values 
(CURRENT_TIMESTAMP (6),
'upsert_test1, execute, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'upsert_test1, execute, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section cpu_bound_esps

-- Test cancel when esps are CPU-bound.

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2;
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;
insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps, execute s1, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps, execute s1, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section escalation1

-- Test cancel escalation.  Simulate unresponsive ESPs with COMP_INT_39.

control query default COMP_INT_39 '4';
control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2;
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';
control query default COMP_INT_39 'reset';

obey TEST106(make_cancel_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1, execute s1, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1, execute s1, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section ustats_test

upsert using load into t106a
  select
  200000 + 
  0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100)
    + (10 * x10) +( 1 * x1),
  0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (10 * x10) + (1 * x1),
  0 + (1 * x1),
  0
  from (values(1)) as t1
    transpose 0,1                 as x100000
    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
  ;
upsert using load into t106a
  select
  400000 + 
  0 + (100000 * x100000) + (10000 * x10000) + (1000 * x1000) + (100 * x100)
    + (10 * x10) +( 1 * x1),
  0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (100 * x100) + (10 * x10) + (1 * x1),
  0 + (10 * x10) + (1 * x1),
  0 + (1 * x1),
  0
  from (values(1)) as t1
    transpose 0,1                 as x100000
    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
  ;


update statistics for table t106a clear;

prepare s1 from
update statistics for table t106a on
c10k, c1k, c100, c10, c1, uniq
,  (uniq, c10k)
,  (uniq, c1k)
,  (uniq, c100)
,  (uniq, c10)
,  (uniq, c1)
,  (c10k, c1k)
,  (c10k, c100)
,  (c10k, c10)
,  (c10k, c1)
,  (c1k,  c100)
,  (c1k,  c10)
,  (c1k,  c1)
,  (c100, c10)
,  (c100, c1)
,  (c10,  c1)
,  (uniq, c10k, c1k)
,  (uniq, c10k, c100)
,  (uniq, c10k, c10)
,  (uniq, c10k, c1)
,  (uniq, c1k,  c100)
,  (uniq, c1k,  c10)
,  (uniq, c1k,  c1)
,  (uniq, c100, c10)
,  (uniq, c100, c1)
,  (uniq, c10,  c1)
,  (c10k, c1k,  c100)
,  (c10k, c1k,  c10)
,  (c10k, c1k,  c1)
,  (c10k, c100, c10)
,  (c10k, c100, c1)
,  (c10k, c10,  c1)
,  (c1k, c100, c10)
,  (c1k, c100, c1)
,  (c1k, c10,  c1)
,  (uniq, c10k, c1k,  c100)
,  (uniq, c10k, c1k,  c10)
,  (uniq, c10k, c1k,  c1)
,  (uniq, c10k, c100, c10)
,  (uniq, c10k, c100, c1)
,  (uniq, c10k, c10,  c1)
,  (uniq, c1k, c100, c10)
,  (uniq, c1k, c100, c1)
,  (uniq, c1k, c10,  c1)
,  (c10k, c1k, c100, c10)
,  (c10k, c1k, c100, c1)
,  (c10k, c1k, c10,  c1)
;

obey TEST106(make_cancel_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'ustats_test, launch canceler, start');

sh sqlci -i"TEST106(do_cancel)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'ustats_test, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'ustats_test, execute s1, start');

execute s1;

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6),
'ustats_test, execute s1, finished');

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section positive_test1_nid_pid

-- Test canceling a query with and without the 
-- CANCEL_QUERY_ALLOWED.

------- long running, cpu-bound select query -------

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
control query default ATTEMPT_ESP_PARALLELISM 'off';

prepare s1 from select count(*) from t106a A, t106a B 
where A.uniq < 3000;

control query default ATTEMPT_ESP_PARALLELISM 'system';
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_nid_pid_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_nid_pid, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_nid_pid)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_nid_pid, launch canceler, finished');

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_nid_pid, execute s1, start');

execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_nid_pid, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section positive_test2_nid_pid

-- Test canceling a query with and without the 
-- CANCEL_QUERY_ALLOWED.

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

control query shape expr(sort_groupby(esp_exchange(cut)));

prepare s1 from select count(*) from t106a A, t106a B 
where A.uniq < 3000;

control query shape cut;

control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_nid_pid_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_nid_pid, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_nid_pid)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_nid_pid, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_nid_pid, execute s1, start');

------- long running, cpu-bound select query -------
execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_nid_pid, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section cpu_bound_esps_nid_pid

-- Test cancel when esps are CPU-bound.

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2;
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_nid_pid_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_nid_pid, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_nid_pid)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_nid_pid, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_nid_pid, execute s1, start');

execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_nid_pid, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section escalation1_nid_pid

-- Test cancel escalation.  Simulate unresponsive ESPs with COMP_INT_39.

control query default COMP_INT_39 '4';
control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2;
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';
control query default COMP_INT_39 'reset';

obey TEST106(make_cancel_nid_pid_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_nid_pid, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_nid_pid)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_nid_pid, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_nid_pid, execute s1, start');

execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_nid_pid, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section positive_test1_pname

-- Test canceling a query with and without the 
-- CANCEL_QUERY_ALLOWED.

------- long running, cpu-bound select query -------

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
control query default ATTEMPT_ESP_PARALLELISM 'off';

prepare s1 from select count(*) from t106a A, t106a B 
where A.uniq < 3000;

control query default ATTEMPT_ESP_PARALLELISM 'system';
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_pname_stmt);

select 'subject launch canceler - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_pname, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_pname)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_pname, launch canceler, finished');

select 'subject synch cancel - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

select 'subject execute s1 - FUNKY_OPT_UNIQUE',
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_pname, execute s1, start');

execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test1_pname, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section positive_test2_pname

-- Test canceling a query with and without the 
-- CANCEL_QUERY_ALLOWED.

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';

control query shape expr(sort_groupby(esp_exchange(cut)));

prepare s1 from select count(*) from t106a A, t106a B 
where A.uniq < 3000;

control query shape cut;

control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_pname_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_pname, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_pname)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_pname, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_pname, execute s1, start');

------- long running, cpu-bound select query -------
execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'positive_test2_pname, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section cpu_bound_esps_pname

-- Test cancel when esps are CPU-bound.

control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2;
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';

obey TEST106(make_cancel_pname_stmt);

insert into T106_interleaved_log values
(CURRENT_TIME (6), 
'cpu_bound_esps_pname, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_pname)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_pname, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_pname, execute s1, start');

execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'cpu_bound_esps_pname, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section escalation1_pname

-- Test cancel escalation.  Simulate unresponsive ESPs with COMP_INT_39.

control query default COMP_INT_39 '4';
control query default QUERY_LIMIT_SQL_PROCESS_CPU '20';
prepare s1 from select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2;
control query default QUERY_LIMIT_SQL_PROCESS_CPU 'reset';
control query default COMP_INT_39 'reset';

obey TEST106(make_cancel_pname_stmt);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_pname, launch canceler, start');

sh sqlci -i"TEST106(do_cancel_pname)" & ;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_pname, launch canceler, finished');

obey TEST106(synch_cancel);

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_pname, execute s1, start');

execute s1;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'escalation1_pname, execute s1, finished');

select 'subject - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;

obey TEST106(synch_cancel);

obey TEST106(append_cancel_log);

?section showplan1

log; log SP106 clear;

showplan control query cancel qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

showplan control query activate qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

showplan control query suspend qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

showplan control query suspend qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force;

#ifdef SEABASE_CANCEL_TBD
showplan control query cancel pid 0, 17677;
showplan control query cancel pid $Z00034G;
#endif

log;

sh echo "cancel-specific showplan output"   >> LOG106; 
sh grep "Contents of EX_CANCEL" SP106       >> LOG106;
sh grep "For ComTdbCancel" SP106            >> LOG106;
sh grep "^qid_ =" SP106                     >> LOG106;
sh grep "^action_ =" SP106                  >> LOG106;
sh grep "^cancelNid_" SP106                 >> LOG106;
sh grep "^cancelPname_" SP106               >> LOG106;
sh grep "^comment_ =" SP106                 >> LOG106;

log LOG106;

?section explain1

log; log EXPLAIN106 clear;

explain control query cancel qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

explain control query suspend qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

explain control query suspend qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force;

explain control query activate qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

#ifdef SEABASE_CANCEL_TBD
explain control query cancel pid 0, 17677;
explain control query cancel pid $Z00034G;
#endif


log;

sh echo "cancel-specific explain output"   >> LOG106; 
sh grep "CONTROL_RUNNING_QUERY" EXPLAIN106 >> LOG106; 
sh grep "control_action"        EXPLAIN106 >> LOG106; 
sh grep "nid_pid"               EXPLAIN106 >> LOG106; 
sh grep "min_blocking_interval" EXPLAIN106 >> LOG106; 
sh grep "process_name"          EXPLAIN106 >> LOG106; 


log LOG106;

?section invalid_qid
control query cancel qid foo;

set envvar HP_FAKE_ERROR_201;

control query cancel qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

reset envvar HP_FAKE_ERROR_201;

control query cancel qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

control query suspend qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

control query suspend qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1, force;

control query activate qid
MXID11000026954212168745269419968000000000217DEFAULT_MXCI_USER00_22_S1;

?section invalid_pname
control query cancel pid $ZXXXXX;

?section invalid_nid_pid
control query cancel pid 44,66666;

?section qc1314

-- Test case for QC 1314.  Exercise IPC timeout in 
-- ex_root_tcb::cancel.

set envvar SQLMX_CANCEL_TIMEOUT '10';

-- Simulate unresponsive ESPs with COMP_INT_39.

control query default COMP_INT_39 '4';

set envvar sqlci_cursor '1';

control query shape esp_exchange(cut);

declare c1 cursor for
select C.c10k, count(*) from t106a A, t106a B, t106a C
group by c.c10k order by 2
for read uncommitted access;

control query shape cut;

control query default COMP_INT_39 'reset';
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;

close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;

close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;

close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;

close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

open c1;

close c1;
values('FUNKY_OPT_UNIQUE', current_timestamp);

?section qc1351
log LOG106;
prepare s1 from select * from t106a;
obey TEST106(make_cancel_stmt);
obey cancel_cmd;

?section synch_cancel

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'foreground awaits synch_cancel');

-- wait until other mxci is ready
sh sh $scriptsdir/executor/synch_106.ksh;

select 'done with synch_cancel - FUNKY_OPT_UNIQUE', 
       CURRENT_TIMESTAMP (6) from (values (1)) as t1;
log;
sh rm ${REGRRUNDIR}/test106_synch ; 

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'foreground finished synch_cancel');

log LOG106;

?section do_cancel
set schema $$TEST_SCHEMA$$;
log CANCEL_LOG106 clear;
------------------------------------------;
------ Contents of cancel session --------;
------------------------------------------;
insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel has begun');

sh touch ${REGRRUNDIR}/test106_synch;

select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

sh sleep 13;

select 'canceler woke up - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

set session default CANCEL_ESCALATION_INTERVAL '5';
log;
log GET_STATS106 clear;
set session default RTS_TIMEOUT '10';
obey get_stats_cmd;
log;
sh cat GET_STATS106 >> cumulative_GET_STATS106;
sh grep "Execute End Time" GET_STATS106 >> CANCEL_LOG106;
log CANCEL_LOG106;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel will cancel');

obey cancel_cmd;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel has canceled');

select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;
log;
sh cat CANCEL_LOG106 >> cancel_log106_all;

sh touch ${REGRRUNDIR}/test106_synch;

?section do_cancel_pname
set schema $$TEST_SCHEMA$$;

log CANCEL_LOG106 clear;
------------------------------------------;
------ Contents of cancel session --------;
------------------------------------------;
insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel_pname has begun');

sh touch ${REGRRUNDIR}/test106_synch;

select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

sh sleep 13;

select 'canceler woke up - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

set session default CANCEL_ESCALATION_INTERVAL '5';
log;
log GET_STATS106 clear;
obey get_stats_cmd;
log;
sh cat GET_STATS106 >> cumulative_GET_STATS106;
sh grep "Execute End Time" GET_STATS106 >> CANCEL_LOG106;
log CANCEL_LOG106;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel_pname will cancel');

obey cancel_pname_cmd;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel_pname has canceled');

select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;
log;
sh cat CANCEL_LOG106 >> cancel_log106_all;

sh touch ${REGRRUNDIR}/test106_synch;

?section do_cancel_nid_pid
set schema $$TEST_SCHEMA$$;

log CANCEL_LOG106 clear;
------------------------------------------;
------ Contents of cancel session --------;
------------------------------------------;
insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel_nid_pid has begun');

sh touch ${REGRRUNDIR}/test106_synch;

select 'canceler going to sleep - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

sh sleep 13;

select 'canceler woke up - FUNKY_OPT_UNIQUE', 
CURRENT_TIMESTAMP (6) from (values (1)) as t1;

set session default CANCEL_ESCALATION_INTERVAL '5';
log;
log GET_STATS106 clear;
obey get_stats_cmd;
log;
sh cat GET_STATS106 >> cumulative_GET_STATS106;
sh grep "Execute End Time" GET_STATS106 >> CANCEL_LOG106;
log CANCEL_LOG106;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel_nid_pid will cancel');

obey cancel_nid_pid_cmd;

insert into T106_interleaved_log values
(CURRENT_TIMESTAMP (6), 
'do_cancel_nid_pid has canceled');

select 'canceler - FUNKY_OPT_UNIQUE', CURRENT_TIMESTAMP (6) from (values (1)) as t1;
log;
sh cat CANCEL_LOG106 >> cancel_log106_all;

sh touch ${REGRRUNDIR}/test106_synch;

?section make_suicide_script
-- Make a shell script to stop this mxci.
sh gtacl -c 'status *,term' | grep mxci | cut -c 12-19 | sed "s/^/sleep 8; gtacl -c 'stop/g" | sed "s/$/'/g">stop_cmd ; 
sh chmod +x stop_cmd;


?section make_cancel_stmt 
log;
log QIDLOG clear;
display qid for s1;

log;

sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/control query cancel qid /g" | sed "s/$/;/g"  > cancel_cmd; 
sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g"  > get_stats_cmd; 


log LOG106;

?section make_cancel_pname_stmt 
log;
log QIDLOG clear;
display qid for s1;

log;

sh echo "control query default CANCEL_MINIMUM_BLOCKING_INTERVAL '1';" > cancel_pname_cmd;
sh grep "^  Pin:" QIDLOG | cut -c 8- | sed "s/^/sqps | grep sqlci | grep /g" | sh | cut -c 42-50 | sed "s/^/control query cancel pid /g" >> cancel_pname_cmd;
sh echo ";" >> cancel_pname_cmd;
sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g"  > get_stats_cmd;

log LOG106;

?section make_cancel_nid_pid_stmt 
log;
log QIDLOG clear;
display qid for s1;

log;

sh echo "control query default CANCEL_MINIMUM_BLOCKING_INTERVAL '1';" > cancel_nid_pid_cmd;
sh grep "^  Pin:" QIDLOG | cut -c 8- | sed "s/^/control query cancel pid 0,/g" >> cancel_nid_pid_cmd;
sh echo ";" >> cancel_nid_pid_cmd;
sh grep "^QID is " QIDLOG | cut -c 8- | sed "s/^/get statistics for qid /g" | sed "s/$/;/g"  > get_stats_cmd;

log LOG106;

?section append_cancel_log
log;
sh sleep 2;
sh cat CANCEL_LOG106 >> LOG106;
log LOG106;

?section append_lock_log
log;
sh sleep 2;
sh cat LOCK_LOG106 >> LOG106;
log LOG106;