diff options
author | Lena Startseva <lena.startseva@mariadb.com> | 2022-09-19 12:26:59 +0700 |
---|---|---|
committer | Lena Startseva <lena.startseva@mariadb.com> | 2023-02-17 15:01:25 +0000 |
commit | 190e31401561389a1c6ede272c629d37e1d05652 (patch) | |
tree | 94529fa12af7f86322045bee46d64c69dfc9f46f /mysql-test/suite/json/t/json_table.test | |
parent | efc4614112e8f252ac83320e0ad7035836b8a383 (diff) | |
download | mariadb-git-bb-10.11-MDEV-7487-rebased-against-11.0-MDEV-29390-test-2.tar.gz |
MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suitesbb-10.11-MDEV-7487-rebased-against-11.0-MDEV-29390-test-2
Created tests for "delete" based on update_use_source.test
For the update_use_source.test tests, data recovery in the table has been changed
from a rollback transaction to a complete delete and re-insert of the data with
optimize table. Cases are now being checked on three engines.
Added tests for update/delete with LooseScan and DuplicateWeedout optimization strategies
Added tests for engine MEMORY on delete and update
Added tests for multi-update with JSON_TABLE
Added tests for multi-update and multi-delete for engine Connect
Diffstat (limited to 'mysql-test/suite/json/t/json_table.test')
-rw-r--r-- | mysql-test/suite/json/t/json_table.test | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 05db8f66a59..8612ab991ad 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -995,3 +995,97 @@ COLUMNS --echo # --echo # End of 10.9 tests --echo # + +--echo # +--echo # MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites +--echo # + +--echo # Multi-update with JSON_TABLE + +create table t1 ( name varchar(10), + color varchar(10), + price decimal(8,2), + instock BOOLEAN); + +insert into t1 values ("Laptop", "black", 20000, 1); +insert into t1 values ("Jacket", "brown", 5000, 1); +insert into t1 values ("Jeans", "blue", 5000, 1); + +select * from t1; + +set @json=' +[ + {"name":"Laptop", "color":"black", "price":"1000", "ordered":"3"}, + {"name":"Jeans", "color":"blue", "ordered":"0"}, + {"name":"Phone", "color":"red", "ordered":"0"} +]'; + +select * from json_table(@json, '$[*]' + columns( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' ) +) as jt; + +explain update t1, JSON_TABLE(@json,'$[*]' + COLUMNS ( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' + )) AS jt1 + SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=3; + +update t1, JSON_TABLE(@json,'$[*]' + COLUMNS ( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' + )) AS jt1 + SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=2; + +select * from t1; + +explain update t1 + SET t1.instock=2 where t1.name in ( + select jt1.name from json_table(@json, '$[*]' + columns( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' ) + ) as jt1); + + +update t1 + SET t1.instock=2 where t1.name in ( + select jt1.name from json_table(@json, '$[*]' + columns( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' ) + ) as jt1); + +select * from t1; + + +-- error ER_NON_UPDATABLE_TABLE +update t1, JSON_TABLE(@json,'$[*]' + COLUMNS ( + name varchar(10) path '$.name', + color varchar(10) path '$.color', + price decimal(8,2) path '$.price', + ordered boolean path '$.ordered' + )) AS jt1 + SET t1.instock=0, jt1.ordered=1 where t1.name=jt1.name; + +select * from t1; + +drop table t1; + +--echo # +--echo # End of 11.0 tests +--echo # |