MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites

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

2 files changed, 195 insertions, 0 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 900348d8a13..ed385f86a31 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -1146,3 +1146,104 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp
 #
 # End of 10.9 tests
 #
+#
+# MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites
+#
+# 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;
+name	color	price	instock
+Laptop	black	20000.00	1
+Jacket	brown	5000.00	1
+Jeans	blue	5000.00	1
+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;
+name	color	price	ordered
+Laptop	black	1000.00	3
+Jeans	blue	NULL	0
+Phone	red	NULL	0
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	jt1	ALL	NULL	NULL	NULL	NULL	40	Table function: json_table; Using where
+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;
+name	color	price	instock
+Laptop	black	20000.00	1
+Jacket	brown	5000.00	1
+Jeans	blue	5000.00	1
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	jt1	ALL	NULL	NULL	NULL	NULL	40	Table function: json_table; Using where; FirstMatch(t1)
+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;
+name	color	price	instock
+Laptop	black	20000.00	2
+Jacket	brown	5000.00	1
+Jeans	blue	5000.00	2
+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;
+ERROR HY000: The target table jt1 of the UPDATE is not updatable
+select * from t1;
+name	color	price	instock
+Laptop	black	20000.00	2
+Jacket	brown	5000.00	1
+Jeans	blue	5000.00	2
+drop table t1;
+#
+# End of 11.0 tests
+#
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index 982922ff595..5a8fe984c79 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -997,3 +997,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 #