create schema sqlpatch_func;
set search_path = sqlpatch_func;
set rewrite_rule = 'magicset, partialpush, uniquecheck, disablerep, intargetlist, predpushforce';

-- create funciton to generate unique sql id for debug usage
CREATE FUNCTION query_get_unique_sql_id(text)
    RETURNS bigint
   AS '@libdir@/regress@DLSUFFIX@','query_get_unique_sql_id'
   LANGUAGE C IMMUTABLE STRICT not fenced;

-- create tables
create table src(a int);
insert into src values(1);

create table hint_t1(a int, b int, c int);
insert into hint_t1 select generate_series(1, 2000), generate_series(1, 1000), generate_series(1, 500) from src;
create index on hint_t1(a);

create table hint_t2(a int, b int, c int);
insert into hint_t2 select generate_series(1, 1000), generate_series(1, 500), generate_series(1, 100) from src;
create index on hint_t2(a);

create table hint_t3(a int, b int, c int);
insert into hint_t3 select generate_series(1, 100), generate_series(1, 50), generate_series(1, 25) from src;
create index on hint_t3(a);

create table hint_t4(a int, b int, c int);
insert into hint_t4 select generate_series(1, 10), generate_series(1, 5), generate_series(1, 2) from src;
create index on hint_t4(a);

create table hint_t5(a int, b int, c int);
insert into hint_t5 select generate_series(1, 5), generate_series(1, 5), generate_series(1, 2) from src;
create index on hint_t5(a);

analyze hint_t1;
analyze hint_t2;
analyze hint_t3;
analyze hint_t4;
analyze hint_t5;

create sequence seq;

CREATE OR REPLACE FUNCTION create_hint_by_query(query text, hint text) RETURNS name AS
$BODY$
DECLARE
    id bigint;
    patch_name name;
BEGIN
    select query_get_unique_sql_id(query) into id;
    select 'hint_'||nextval('seq') into patch_name;
    perform dbe_sql_util.create_hint_sql_patch(patch_name, id, hint);
    RETURN patch_name;
END
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_abort_by_query(query text) RETURNS name AS
$BODY$
DECLARE
    id bigint;
    patch_name name;
BEGIN
    select query_get_unique_sql_id(query) into id;
    select 'abort_'||nextval('seq') into patch_name;
    perform dbe_sql_util.create_abort_sql_patch(patch_name, id);
    RETURN patch_name;
END;
$BODY$
LANGUAGE plpgsql;

----
-- abort patch
----

-- dml
select * from create_abort_by_query('select * from hint_t1 where a = 1');
select * from hint_t1 where a = 1; --abort
select     *   from  hint_t1 t1 where a = 2; --abort ignore blank space and parameter change
select * from hint_t1 where b = 1;

select * from create_abort_by_query('insert into hint_t1 values (0,0,0)');
insert into hint_t1 values (0,0,0); --abort
insert into hint_t1 values (default); --unaffected
insert /*+ set(enable_seqscan off) */ into hint_t1 values (1,2,3); --abort ignore hint
select * from create_abort_by_query('insert into hint_t3 select * from hint_t4 where a = 1');
insert into hint_t3 select * from hint_t4 where a = 1; --abort
insert into hint_t3 select * from hint_t4 where b = 1; --unaffected

select * from create_abort_by_query('insert into hint_t1 values (0,0,0) on duplicate key update b = 1');
insert into hint_t1 values (5,6,7) on    duplicate key update b = 100; --abort
insert into hint_t1 values (5,6,7) on    duplicate key update a = 100; --abort by mistake

select * from create_abort_by_query('update hint_t1 set b = 100');
update hint_t1 set b = 200; --abort
begin;
update hint_t1 set c = 200; --good to go
rollback;

select * from create_abort_by_query('delete from hint_t1 where a = 1');
delete from hint_t1 where a = 1001; --abort
delete hint_t1 t1 where t1.a = 123;

select * from create_abort_by_query('merge into hint_t1 t1 using hint_t2 t2 on t2.a = t1.a when matched then update set t1.b = t2.b;');
merge into hint_t1 t1 using hint_t2 t2 on t2.a = t1.a when matched then update set t1.b = t2.b; --abort
merge into hint_t1 t1 using hint_t2 t2 on t2.a = t1.a when matched then update set t1.b = t2.b + 1; --abort by mistake
merge into hint_t1 t1 using hint_t2 t2 on t2.a = t1.a when matched then update set t1.b = t2.b + t2.c; --abort by mistake

-- ddl
select * from create_abort_by_query('create table test_abort(a int);');
create table test_abort(a int); --abort
create table  test_abort(a int); --ok for now

select * from create_abort_by_query('create sequence s2'); -- this won't work
create sequence s2;
drop sequence s2;
select * from create_abort_by_query('create sequence s2;'); -- this will work
create sequence s2;

select * from create_abort_by_query('set search_path = p1;');
set search_path = p1; -- abort

-- other utilities falls into to the same branch, no need to expand here

----
-- hint patch
----
select setval('seq', 1);
-- leading hint
select * from create_hint_by_query('select * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a', 'leading((t1 t2))');
explain(costs off) select * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a;
select * from dbe_sql_util.drop_sql_patch('hint_2');
select * from create_hint_by_query('select * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a', 'leading((t2 t1))');
explain(costs off) select /*+ leading((t1 t2)) */* from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a;

-- join hint
select * from create_hint_by_query('select * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a where t1.a < 50', 'mergejoin(t1 t2)');
explain(costs off) select * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a where t1.a < 50;
select * from dbe_sql_util.drop_sql_patch('hint_4');
select * from create_hint_by_query('select * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a where t1.a < 50', 'hashjoin(t1 t2)');
explain(costs off) select /*+ set(enable_hashjoin off) */ * from hint_t1 t1 join hint_t2 t2 on t1.a = t2.a where t1.a < 50;

-- scan hint
select * from create_hint_by_query('select * from hint_t5 t5 where t5.a = 1', 'tablescan(t5)');
explain(costs off) select * from hint_t5 t5 where t5.a = 3;
select * from dbe_sql_util.drop_sql_patch('hint_6');
select * from create_hint_by_query('select * from hint_t5 t5 where t5.a = 1', 'indexscan (t5) tablescan(t6)'); -- with invalid hint
explain(costs off) select * from hint_t5 t5 where t5.a = 100;

-- set hint
explain(costs off) select distinct(c) from hint_t2 where a < 30;
select * from create_hint_by_query('select distinct(c) from hint_t2 where a < 20', 'set(enable_hashagg off)');
explain(costs off) select distinct(c) from hint_t2 where a < 20;

-- rewrite hint
set rewrite_rule = '';
explain(costs off) select a from hint_t3 t1 where t1.b = (select max(b) from hint_t3 t2 where t2.a = t1.a);
select * from create_hint_by_query('select a from hint_t3 t1 where t1.b = (select max(b) from hint_t3 t2 where t2.a = t1.a);', 'set (rewrite_rule "magicset,disable_pullup_expr_sublink")');
explain(costs off) select a from hint_t3 t1 where t1.b = (select max(b) from hint_t3 t2 where t2.a = t1.a);
select * from dbe_sql_util.drop_sql_patch('hint_9');
set rewrite_rule = 'disable_pullup_expr_sublink';
select * from create_hint_by_query('select a from hint_t3 t1 where t1.b = (select max(b) from hint_t3 t2 where t2.a = t1.a);', 'set (rewrite_rule magicset)');
explain(costs off) select a from hint_t3 t1 where t1.b = (select max(b) from hint_t3 t2 where t2.a = t1.a);

----
-- cleanup
----
select dbe_sql_util.drop_sql_patch(patch_name) from gs_sql_patch;
drop schema sqlpatch_func cascade;