Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

4 files changed, 205 insertions, 2 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 44957352..ed385f86 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -211,12 +211,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": "COST_REPLACED",
     "nested_loop": [
       {
         "table": {
           "table_name": "tt",
           "access_type": "ALL",
+          "loops": 1,
           "rows": 40,
+          "cost": "COST_REPLACED",
           "filtered": 100,
           "table_function": "json_table"
         }
@@ -1143,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/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result
index 2357d9d3..0be40a8a 100644
--- a/mysql-test/suite/json/r/json_table_mysql.result
+++ b/mysql-test/suite/json/r/json_table_mysql.result
@@ -189,12 +189,15 @@ EXPLAIN
 {
   "query_block": {
     "select_id": 1,
+    "cost": "COST_REPLACED",
     "nested_loop": [
       {
         "table": {
           "table_name": "tt",
           "access_type": "ALL",
+          "loops": 1,
           "rows": 40,
+          "cost": "COST_REPLACED",
           "filtered": 100,
           "table_function": "json_table"
         }
@@ -530,8 +533,7 @@ EXPLAIN SELECT * FROM t1 WHERE id IN
 (id INT PATH '$')) AS jt);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	jt	ALL	NULL	NULL	NULL	NULL	40	Table function: json_table
+1	PRIMARY	jt	ALL	NULL	NULL	NULL	NULL	40	Table function: json_table; Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 DROP TABLE t1;
 SELECT * FROM JSON_TABLE('"asdf"', '$' COLUMNS(
 tm TIME PATH '$',
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index 05db8f66..5a8fe984 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -69,6 +69,7 @@ insert into t1 select * from t1;
 
 set @save_optimizer_switch=@@optimizer_switch;
 set optimizer_switch='firstmatch=off';
+--sorted_result
 select * from 
   json_table('[{"color": "blue", "price": 50},
                      {"color": "red", "price": 100}]',
@@ -144,6 +145,7 @@ create view v2 as select * from   json_table('[{"co\\\\lor": "blue", "price": 50
 select * from v2;
 drop view v2;
 
+--source include/explain-no-costs.inc
 explain format=json select * from
   json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt;
 explain select * from
@@ -995,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 #
diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test
index 9f77ad96..3b0fb556 100644
--- a/mysql-test/suite/json/t/json_table_mysql.test
+++ b/mysql-test/suite/json/t/json_table_mysql.test
@@ -167,6 +167,7 @@ select * from
 
 #eval $query;
 #eval explain $query;
+--source include/explain-no-costs.inc
 explain format=json 
 select * from
   json_table(