#
# Bug #29846292 ROLLBACK OF BIG TRANSACTION DUE TO CONFLICT REMAINS IN HUNG STATE
#
# This file is based on blob_partial_update.test, with following modifications:
# - the json's members being changed are longer than LOB_SMALL_CHANGE_THRESHOLD,
#   so that the "small change" optimization (which stores modifications inside
#   undo log record itself instead of creating new versions of blob via partial
#   updates) does not kick in, and the partial update logic is really tested
# - each query is run twice - once with ROLLBACK, once with COMMIT - so that we
#   can check the behaviour of rollback()
# - we mostly modify item [1] instead of item [0] to force rollback() logic to
#   apply changes on the middle of the blob (instead of the front), which helped
#   to expose wrong assumptions about loop invariants
--source include/have_innodb_max_16k.inc
--source include/have_debug.inc

USE test;

CREATE TABLE tab(j1 JSON);

--disable_query_log
set @chars128 = repeat('x',128);
set @data_1 = repeat('abcdefghijklmnopqrstuvwxyz1234', 6006);
set @json_doc_1 = concat('["', @data_1, '", "Sven', @chars128 ,'", "', @data_1, '"]');
INSERT INTO tab VALUES
(@json_doc_1);
ANALYZE TABLE tab;
--echo # Sven
select json_extract(j1, '$[1]') from tab;

let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Viswa',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # Show Viswa
select json_extract(j1, '$[1]') from tab;
let $sql_to_do=UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Nath',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # Show Nath
select json_extract(j1, '$[1]') from tab;

--echo # Modify column type from JSON to LONGTEXT

ALTER TABLE tab MODIFY j1 LONGTEXT ;


let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setlongtext',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Check JSON functions works on TEXT datatype
SELECT JSON_STORAGE_SIZE(j1) FROM tab;

SELECT JSON_STORAGE_FREE(j1) FROM tab;

--echo # Show Setlongtext
SELECT json_extract(j1, '$[1]') FROM tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Replongtext',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc


ANALYZE TABLE tab;

--echo # Check JSON functions works on TEXT datatype
SELECT JSON_STORAGE_SIZE(j1) FROM tab;

SELECT JSON_STORAGE_FREE(j1) FROM tab;

--echo # Show Replongtext
SELECT json_extract(j1, '$[1]') from tab;

--echo When column modify from LONGTEXT to JSON
ALTER TABLE tab MODIFY j1 JSON ;


let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setjson',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc


ANALYZE TABLE tab;

--echo # Show Setjson
SELECT  json_extract(j1, '$[1]') FROM tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repjson',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc


ANALYZE TABLE tab;

--echo # Show Repjson
SELECT json_extract(j1, '$[1]') FROM tab;

--echo To COMPRESSED
ALTER TABLE tab ROW_FORMAT = COMPRESSED ;

SHOW CREATE TABLE tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setcomp',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Show Setcomp
SELECT  json_extract(j1, '$[1]') FROM tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repcomp',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # Show Repcomp
SELECT json_extract(j1, '$[1]') FROM tab;

--echo COMPRESSED to DYNAMIC
ALTER TABLE tab ROW_FORMAT = DYNAMIC;

ANALYZE TABLE tab;

SHOW CREATE TABLE tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setdyna',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Show Setdyna
SELECT  json_extract(j1, '$[1]') FROM tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repdyna',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Show Repdyna
SELECT json_extract(j1, '$[1]') FROM tab;

--echo DYNAMIC to REDUNDANT
ALTER TABLE tab ROW_FORMAT = REDUNDANT ;

SHOW CREATE TABLE tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setredu',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Show Setredu
SELECT  json_extract(j1, '$[1]') FROM tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repredu',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # Show Repredu
SELECT json_extract(j1, '$[1]') FROM tab;

--echo REDUNDANT to COMPACT
ALTER TABLE tab ROW_FORMAT = COMPACT ;

SHOW CREATE TABLE tab;

ANALYZE TABLE tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setcomp',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Show Setcomp
SELECT  json_extract(j1, '$[1]') FROM tab;


let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repcomp',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # Show Repcomp
SELECT json_extract(j1, '$[1]') FROM tab;

--echo # Create a view on JSON column
CREATE VIEW view_tab AS SELECT * FROM tab;


let $sql_to_do= UPDATE view_tab SET j1 = JSON_SET(j1, '$[1]', concat('Setview',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

ANALYZE TABLE tab;

--echo # Check JSON functions works on views
SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;

SELECT JSON_STORAGE_FREE(j1) FROM view_tab;

--echo # Show Setview
SELECT  json_extract(j1, '$[1]') FROM view_tab;


let $sql_to_do= UPDATE view_tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repview',@chars128));
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # Show Repview
SELECT json_extract(j1, '$[1]') FROM view_tab;

ANALYZE TABLE tab;

--echo # Check JSON functions works on views
SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;

SELECT JSON_STORAGE_FREE(j1) FROM view_tab;

--echo # Modify Compression to ZLIB
ALTER TABLE tab COMPRESSION = 'ZLIB';

SHOW CREATE TABLE tab;

delimiter |;

--echo # Check partial update through procedure
CREATE PROCEDURE proc_json_update()
begin

UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Reppro',@chars128));

end|

delimiter ;|

CALL proc_json_update();

ANALYZE TABLE tab;

--echo # Show Reppro
SELECT  json_extract(j1, '$[1]') FROM tab;

--echo # Check JSON functions on views
SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;

SELECT JSON_STORAGE_FREE(j1) FROM view_tab;

DROP PROCEDURE proc_json_update;

--echo # Modify Compression to 'LZ4'
ALTER TABLE tab COMPRESSION = 'LZ4';

SHOW CREATE TABLE tab;

delimiter |;

--echo # Check partial update through procedure
CREATE PROCEDURE proc_json_update()
begin
  START TRANSACTION;
  UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('SetLZ',@chars128));
  ROLLBACK;
  START TRANSACTION;
  UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('SetLZ',@chars128));
  COMMIT;
end|

delimiter ;|

set debug = '+d,lob_print_partial_update_hit';

CALL proc_json_update();

ANALYZE TABLE tab;

--echo # Show SetLZ
SELECT  json_extract(j1, '$[1]') FROM tab;

--echo # Check pattern for LOB partial update exist
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;

let SEARCH_PATTERN= LOB partial update;
--source include/search_pattern.inc

--echo # Check JSON functions does not work on BLOB type
CREATE TABLE tab1(id INT PRIMARY KEY, j1 JSON);

INSERT INTO tab1 VALUES(1, '{"a":"a","b":"b"}');

INSERT INTO tab1 VALUES(2, NULL);

ALTER TABLE tab1 modify j1 BLOB;

SHOW CREATE TABLE tab1;

--error ER_INVALID_JSON_CHARSET
UPDATE tab1 SET j1 = JSON_SET(j1, '$.a', 'x');

--error ER_INVALID_JSON_CHARSET
SELECT JSON_STORAGE_SIZE(j1) FROM tab1;

--error ER_INVALID_JSON_CHARSET
SELECT JSON_STORAGE_FREE(j1) FROM tab1;

--echo # Check partial update with Generated columns
CREATE TABLE tab2(id INT PRIMARY KEY, c1 JSON, c2 JSON GENERATED ALWAYS AS (c1) STORED);

INSERT INTO tab2 VALUES(1, '{"a":"a","b":"b"}', DEFAULT);

ANALYZE TABLE tab2;

--skip_if_hypergraph  # Depends on the query plan.
--error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
EXPLAIN FORMAT=JSON UPDATE tab2 SET c2 = JSON_SET(c2, '$.a', 'x');

--echo # Check partial update with partition table
CREATE TABLE tab3 (a INT, j1 JSON, j2 JSON) PARTITION BY LINEAR HASH(a) PARTITIONS 3;

SHOW CREATE TABLE tab3;

INSERT INTO tab3 VALUES(1, '{"a":"a","b":"b"}', '{"a":"a","b":"b"}');

INSERT INTO tab3 VALUES(2, NULL, NULL);

INSERT INTO tab3 VALUES(1, '{"a":"aa","b":"bb"}', '{"a":"aa","b":"bb"}');

INSERT INTO tab3 VALUES(2, '["Sachin","Tendulkar"]','["Virat","Kohili"]');

INSERT INTO tab3 VALUES(3, '[100 ,{"A1": "Amarnath","B1": "Binni"}]','[200,{"A2": "Azar","S1": "Srikanth"}]');

INSERT INTO tab3 VALUES(3, '{"Chetan": [300,400],"Sasri": [500,600]}','{"Dhoni": [700,800],"Sidhu": [900,100]}');

SELECT * FROM tab3 order by a;

--echo Check with JSON_SET

let $sql_to_do= UPDATE tab3 SET j1 = JSON_SET(j1, '$.a', 'x'),
j2 = JSON_SET(j2, '$.a', 'y'),
j1 = JSON_SET(j1, '$.b', 'z');
--source suite/innodb/include/explain_then_rollback_and_commit.inc

SELECT * FROM tab3 order by a;

--echo Check with JSON_REPLACE

let $sql_to_do= UPDATE tab3 SET j1 = JSON_REPLACE(j1,'$[1].A1', 'PartRe') ;
--source suite/innodb/include/explain_then_rollback_and_commit.inc

--echo # show PartRe
SELECT json_extract(j1, '$[1].A1') FROM tab3;

SELECT * FROM tab3 order by a;

# clean up
DROP TABLE tab2;

DROP TABLE tab3;

DROP PROCEDURE proc_json_update;

DROP VIEW view_tab;

DROP TABLE tab;

DROP TABLE tab1;
set debug = '-d,lob_print_partial_update_hit';