MDEV-10000 Add support for FORMAT=JSONbb-10.8-MDEV-10000

Makes possible calls to SHOW EXPLAIN and EXPLAIN FOR CONNECTION
providing output in JSON format:
- SHOW EXPLAIN FORMAT=JSON FOR $con
- EXPLAIN FORMAT=JSON FOR CONNECTION $con

11 files changed, 1347 insertions, 18 deletions

diff --git a/mysql-test/main/show_explain.test b/mysql-test/main/show_explain.test
index c8ce804de64..612a875536b 100644
--- a/mysql-test/main/show_explain.test
+++ b/mysql-test/main/show_explain.test
@@ -137,7 +137,6 @@ send explain select max(c) from t1 where a < 10;
 connection default;
 --source include/wait_condition.inc
 evalp show explain for $thr2;
-# evalp explain for connection $thr2;
 connection con1;
 reap;
 
diff --git a/mysql-test/main/show_explain_json.result b/mysql-test/main/show_explain_json.result
new file mode 100644
index 00000000000..a6b4b67401c
--- /dev/null
+++ b/mysql-test/main/show_explain_json.result
@@ -0,0 +1,1294 @@
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+SET @old_debug= @@session.debug;
+set debug_sync='RESET';
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
+alter table t1 add b int, add c int, add filler char(32);
+update t1 set b=a, c=a, filler='fooo';
+alter table t1 add key(a), add key(b);
+show explain format=JSON for;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+explain format=JSON for connection;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+show explain FORMAT=json for 123456789;
+ERROR HY000: Unknown thread id: 123456789
+explain FORMAT=json for connection 123456789;
+ERROR HY000: Unknown thread id: 123456789
+show explain FORMAT=json for (select max(a) from t0);
+ERROR HY000: You may only use constant expressions in this statement
+explain FORMAT=json for connection (select max(a) from t0);
+ERROR HY000: You may only use constant expressions in this statement
+connect  con1, localhost, root,,;
+connection con1;
+SET @old_debug= @@session.debug;
+connection default;
+show explain format=JSON for $thr2;
+ERROR HY000: Target is not running an EXPLAINable command
+explain format=json for connection $thr2;
+ERROR HY000: Target is not running an EXPLAINable command
+show explain format=json for $thr1;
+ERROR HY000: Target is not running an EXPLAINable command
+explain FORMAT=JSON for connection $thr1;
+ERROR HY000: Target is not running an EXPLAINable command
+show explain FORMAT=HTML for $thr1;
+ERROR HY000: Unknown EXPLAIN format name: 'HTML'
+explain FORMAT=XML for connection $thr1;
+ERROR HY000: Unknown EXPLAIN format name: 'XML'
+connection con1;
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select count(*) from t1 where a < 100000;
+connection default;
+show explain FORMAT=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "rows": 999,
+          "filtered": 100,
+          "attached_condition": "t1.a < 100000",
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select count(*) from t1 where a < 100000
+connection con1;
+count(*)
+1000
+select max(c) from t1 where a < 10;
+connection default;
+explain FORMAT=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "rows": 10,
+          "filtered": 100,
+          "index_condition": "t1.a < 10"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select max(c) from t1 where a < 10
+connection con1;
+max(c)
+9
+select max(c) from t1 where a < 10;
+connection default;
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "rows": 10,
+          "filtered": 100,
+          "index_condition": "t1.a < 10"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select max(c) from t1 where a < 10
+connection con1;
+max(c)
+9
+# We can catch EXPLAIN, too.
+set @show_expl_tmp= @@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
+explain select max(c) from t1 where a < 10;
+connection default;
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "rows": 10,
+          "filtered": 100,
+          "index_condition": "t1.a < 10",
+          "mrr_type": "Rowid-ordered scan"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	explain select max(c) from t1 where a < 10
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Rowid-ordered scan
+# Same for EXPLAIN FOR CONNECTION
+explain select max(c) from t1 where a < 10;
+connection default;
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "range",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "rows": 10,
+          "filtered": 100,
+          "index_condition": "t1.a < 10",
+          "mrr_type": "Rowid-ordered scan"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	explain select max(c) from t1 where a < 10
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Rowid-ordered scan
+set optimizer_switch= @show_expl_tmp;
+SET debug_dbug=@old_debug;
+# UNION, first branch 
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+explain select a from t0 A union select a+1 from t0 B;
+connection default;
+show explain format = JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "A",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100
+                }
+              }
+            ]
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "B",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100
+                }
+              }
+            ]
+          }
+        }
+      ]
+    }
+  }
+}
+Warnings:
+Note	1003	explain select a from t0 A union select a+1 from t0 B
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+SET debug_dbug=@old_debug;
+# UNION, second branch
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+explain select a from t0 A union select a+1 from t0 B;
+connection default;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "A",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100
+                }
+              }
+            ]
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "B",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100
+                }
+              }
+            ]
+          }
+        }
+      ]
+    }
+  }
+}
+Warnings:
+Note	1003	explain select a from t0 A union select a+1 from t0 B
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+SET debug_dbug=@old_debug;
+# Uncorrelated  subquery, select
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 B) from t0 A where a<1;
+connection default;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	select a, (select max(a) from t0 B) from t0 A where a<1
+connection con1;
+a	(select max(a) from t0 B)
+0	9
+SET debug_dbug=@old_debug;
+# Uncorrelated  subquery, explain
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+explain select a, (select max(a) from t0 B) from t0 A where a<1;
+connection default;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	explain select a, (select max(a) from t0 B) from t0 A where a<1
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+SET debug_dbug=@old_debug;
+# correlated  subquery, select
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+connection con1;
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+SET debug_dbug=@old_debug;
+# correlated  subquery, explain
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+explain select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	explain select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+SET debug_dbug=@old_debug;
+# correlated  subquery, select, while inside the subquery
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100,
+          "attached_condition": "a.a < 1"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "b",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "b.a + a.a < 10"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+connection con1;
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+SET debug_dbug=@old_debug;
+# correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100,
+          "attached_condition": "a.a < 1"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "b",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "b.a + a.a < 10"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+connection con1;
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+SET debug_dbug=@old_debug;
+# correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "a",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100,
+          "attached_condition": "a.a < 1"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "b",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "b.a + a.a < 10"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+connection con1;
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+SET debug_dbug=@old_debug;
+# Try to do SHOW EXPLAIN for a query that runs a  SET command:
+#   I've found experimentally that select_id==2 here...
+# 
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+set @foo= (select max(a) from t0 where sin(a) >0);
+connection default;
+show explain format=JSON for $thr2;
+ERROR HY000: Target is not running an EXPLAINable command
+kill query $thr2;
+connection con1;
+ERROR 70100: Query execution was interrupted
+SET debug_dbug=@old_debug;
+#
+# Attempt SHOW EXPLAIN for an UPDATE
+#
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "update": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "update": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+connection con1;
+#
+# EXPLAIN FOR CONNECTION for an UPDATE
+#
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "update": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "update": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+connection con1;
+drop table t2;
+SET debug_dbug=@old_debug;
+#
+# Attempt SHOW EXPLAIN for a DELETE
+# 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+connection con1;
+drop table t2;
+SET debug_dbug=@old_debug;
+#
+# Attempt EXPLAIN FOR CONNECTION for a DELETE
+# 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "delete": 1,
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "attached_condition": "(subquery#2) > 3"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t0",
+                "access_type": "ALL",
+                "rows": 10,
+                "filtered": 100,
+                "attached_condition": "t2.a + t0.a < 3"
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+connection con1;
+drop table t2;
+SET debug_dbug=@old_debug;
+#
+# Multiple SHOW EXPLAIN calls for one select 
+# 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+connection default;
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t0",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "t2.a + t0.a < 3"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t0",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "t2.a + t0.a < 3"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t0",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "t2.a + t0.a < 3"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+connection con1;
+a	SUBQ
+0	0
+1	0
+2	0
+drop table t2;
+SET debug_dbug=@old_debug;
+#
+# Multiple EXPLAIN FOR CONNECTION calls for one select 
+# 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+connection default;
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t0",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "t2.a + t0.a < 3"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t0",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "t2.a + t0.a < 3"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+explain format=JSON for connection $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ALL",
+          "rows": 3,
+          "filtered": 100
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t0",
+                  "access_type": "ALL",
+                  "rows": 10,
+                  "filtered": 100,
+                  "attached_condition": "t2.a + t0.a < 3"
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+connection con1;
+a	SUBQ
+0	0
+1	0
+2	0
+drop table t2;
+SET debug_dbug=@old_debug;
+#
+# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+#
+explain select * from t0 order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+select * from t0 order by a;
+connection default;
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "read_sorted_file": {
+          "filesort": {
+            "sort_key": "t0.a",
+            "table": {
+              "table_name": "t0",
+              "access_type": "ALL",
+              "rows": 10,
+              "filtered": 100
+            }
+          }
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select * from t0 order by a
+connection con1;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+SET debug_dbug=@old_debug;
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary"
+#
+connection default;
+explain select distinct a from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+connection con1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+connection default;
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "temporary_table": {
+      "nested_loop": [
+        {
+          "table": {
+            "table_name": "t0",
+            "access_type": "ALL",
+            "rows": 10,
+            "filtered": 100
+          }
+        }
+      ]
+    }
+  }
+}
+Warnings:
+Note	1003	select distinct a from t0
+connection con1;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+SET debug_dbug=@old_debug;
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+#
+connection default;
+explain select distinct a from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+connection con1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+connection default;
+show explain format=json for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "temporary_table": {
+      "nested_loop": [
+        {
+          "table": {
+            "table_name": "t0",
+            "access_type": "ALL",
+            "rows": 10,
+            "filtered": 100
+          }
+        }
+      ]
+    }
+  }
+}
+Warnings:
+Note	1003	select distinct a from t0
+connection con1;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+SET debug_dbug=@old_debug;
+#
+# Check if queries in non-default charsets work.
+#
+set names cp1251;
+select charset('ãû');
+charset('ãû')
+cp1251
+select hex('ãû');
+hex('ãû')
+E3FB
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_start';
+select * from t0 where length('ãû') = a;
+connection default;
+set names utf8;
+show explain format=JSON for $thr2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t0",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100,
+          "attached_condition": "t0.a = <cache>(octet_length('гы'))"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	select * from t0 where length('гы') = a
+set names default;
+connection con1;
+a
+2
+SET debug_dbug=@old_debug;
+set names default;
+drop table if exists t0,t1,t2;
+Warnings:
+Note	1051	Unknown table 'test.t2'
+# End
+connection default;
+disconnect con1;
+set debug_sync='RESET';
diff --git a/sql/handler.h b/sql/handler.h
index fe61666bf20..f0ccb048a21 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1006,7 +1006,8 @@ enum enum_schema_tables
   SCH_ENABLED_ROLES,
   SCH_ENGINES,
   SCH_EVENTS,
-  SCH_EXPLAIN,
+  SCH_EXPLAIN_TABULAR,
+  SCH_EXPLAIN_JSON,
   SCH_FILES,
   SCH_GLOBAL_STATUS,
   SCH_GLOBAL_VARIABLES,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index d983fe9a332..7ddff6d627b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8837,6 +8837,8 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List<Item> &values,
       continue;
 
     value=v++;
+    /* Ensure the end of the list of values is not reached */
+    DBUG_ASSERT(value);
 
     bool vers_sys_field= table->versioned() && field->vers_sys_field();
 
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 4fd4e6d3b77..722fcd8a151 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -215,7 +215,7 @@ int Explain_query::print_explain(select_result_sink *output,
 }
 
 
-void Explain_query::print_explain_json(select_result_sink *output,
+int Explain_query::print_explain_json(select_result_sink *output,
                                        bool is_analyze)
 {
   Json_writer writer;
@@ -230,7 +230,7 @@ void Explain_query::print_explain_json(select_result_sink *output,
     /* Start printing from node with id=1 */
     Explain_node *node= get_node(1);
     if (!node)
-      return; /* No query plan */
+      return 1; /* No query plan */
     node->print_explain_json(this, &writer, is_analyze);
   }
 
@@ -243,6 +243,7 @@ void Explain_query::print_explain_json(select_result_sink *output,
                       Item_string(thd, buf->ptr(), buf->length(), cs),
                       thd->mem_root);
   output->send_data(item_list);
+  return 0;
 }
 
 
diff --git a/sql/sql_explain.h b/sql/sql_explain.h
index 88fae9d4f1a..94d0f667cf9 100644
--- a/sql/sql_explain.h
+++ b/sql/sql_explain.h
@@ -478,7 +478,7 @@ public:
   /* Return tabular EXPLAIN output as a text string */
   bool print_explain_str(THD *thd, String *out_str, bool is_analyze);
 
-  void print_explain_json(select_result_sink *output, bool is_analyze);
+  int print_explain_json(select_result_sink *output, bool is_analyze);
 
   /* If true, at least part of EXPLAIN can be printed */
   bool have_query_plan() { return insert_plan || upd_del_plan|| get_node(1) != NULL; }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b5f8cf4a886..0bbe5b02460 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5825,12 +5825,15 @@ bool st_select_lex::is_merged_child_of(st_select_lex *ancestor)
 */
 
 int LEX::print_explain(select_result_sink *output, uint8 explain_flags,
-                       bool is_analyze, bool *printed_anything)
+                       bool is_analyze, bool is_json_format, bool *printed_anything)
 {
   int res;
   if (explain && explain->have_query_plan())
   {
-    res= explain->print_explain(output, explain_flags, is_analyze);
+    if (is_json_format)
+      res= explain->print_explain_json(output, is_analyze);
+    else
+      res= explain->print_explain(output, explain_flags, is_analyze);
     *printed_anything= true;
   }
   else
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 14cf90caa04..1ec855253a6 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3792,7 +3792,7 @@ public:
   bool save_prep_leaf_tables();
 
   int print_explain(select_result_sink *output, uint8 explain_flags,
-                    bool is_analyze, bool *printed_anything);
+                    bool is_analyze, bool is_json_format, bool *printed_anything);
   void restore_set_statement_var();
 
   void init_last_field(Column_definition *field, const LEX_CSTRING *name,
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index d01f84fe7d1..0247b11fbfa 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2998,7 +2998,8 @@ void Show_explain_request::call_in_target_thread()
   DBUG_ASSERT(current_thd == target_thd);
   set_current_thd(request_thd);
   if (target_thd->lex->print_explain(explain_buf, 0 /* explain flags*/,
-                                     false /*TODO: analyze? */, &printed_anything))
+                                     false /*TODO: analyze? */, 
+                                     is_json_format, &printed_anything))
   {
     failed_to_produce= TRUE;
   }
@@ -3119,7 +3120,8 @@ void select_result_text_buffer::save_to(String *res)
   Store the SHOW EXPLAIN output in the temporary table.
 */
 
-int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
+int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond, 
+                      bool json_format)
 {
   const char *calling_user;
   THD *tmp;
@@ -3164,6 +3166,7 @@ int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
     bool timed_out;
     int timeout_sec= 30;
     Show_explain_request explain_req;
+    explain_req.is_json_format= json_format;
     select_result_explain_buffer *explain_buf;
     
     explain_buf= new select_result_explain_buffer(thd, table->table);
@@ -3226,6 +3229,18 @@ int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
 }
 
 
+int fill_show_explain_tabular(THD *thd, TABLE_LIST *table, COND *cond)
+{
+  return fill_show_explain(thd, table, cond, false /*json_format*/);
+}
+
+
+int fill_show_explain_json(THD *thd, TABLE_LIST *table, COND *cond) 
+{
+  return fill_show_explain(thd, table, cond, true /*json_format*/);
+}
+
+
 struct processlist_callback_arg
 {
   processlist_callback_arg(THD *thd_arg, TABLE *table_arg):
@@ -9615,7 +9630,7 @@ ST_FIELD_INFO keycache_fields_info[]=
 };
 
 
-ST_FIELD_INFO show_explain_fields_info[]=
+ST_FIELD_INFO show_explain_tabular_fields_info[]=
 {
   Column("id",            SLonglong(3),                  NULLABLE, "id"),
   Column("select_type",   Varchar(19),                   NOT_NULL, "select_type"),
@@ -9631,6 +9646,13 @@ ST_FIELD_INFO show_explain_fields_info[]=
 };
 
 
+ST_FIELD_INFO show_explain_json_fields_info[]= 
+{
+  Column("EXPLAIN", Longtext(MAX_FIELD_VARCHARLENGTH), NOT_NULL, "EXPLAIN"),
+  CEnd()
+};
+
+
 ST_FIELD_INFO check_constraints_fields_info[]=
 {
   Column("CONSTRAINT_CATALOG", Catalog(), NOT_NULL, OPEN_FULL_TABLE),
@@ -9691,8 +9713,10 @@ ST_SCHEMA_TABLE schema_tables[]=
   {"EVENTS", Show::events_fields_info, 0,
    0, make_old_format, 0, -1, -1, 0, 0},
 #endif
-  {"EXPLAIN", Show::show_explain_fields_info, 0, fill_show_explain,
+  {"EXPLAIN", Show::show_explain_tabular_fields_info, 0, fill_show_explain_tabular,
   make_old_format, 0, -1, -1, TRUE /*hidden*/ , 0},
+  {"EXPLAIN_JSON", Show::show_explain_json_fields_info, 0, fill_show_explain_json,
+   make_old_format, 0, -1, -1, TRUE /*hidden*/, 0},
   {"FILES", Show::files_fields_info, 0,
    hton_fill_schema_table, 0, 0, -1, -1, 0, 0},
   {"GLOBAL_STATUS", Show::variables_fields_info, 0,
diff --git a/sql/sql_show.h b/sql/sql_show.h
index 3d7a4d1146c..8c8ae416699 100644
--- a/sql/sql_show.h
+++ b/sql/sql_show.h
@@ -164,6 +164,9 @@ public:
   THD *target_thd;  /* thd that we're running SHOW EXPLAIN for */
   THD *request_thd; /* thd that run SHOW EXPLAIN command */
   
+  bool is_json_format= false; /* set to TRUE if you need the result in JSON 
+                              format, FALSE - in traditional tabular */
+
   /* If true, there was some error when producing EXPLAIN output. */
   bool failed_to_produce;
    
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 74757970411..6df21338881 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13945,12 +13945,13 @@ show_param:
             Lex->spname= $3;
             Lex->sql_command = SQLCOM_SHOW_CREATE_EVENT;
           }
-        | describe_command FOR_SYM expr
+        | describe_command opt_format_json FOR_SYM expr
           {
             Lex->sql_command= SQLCOM_SHOW_EXPLAIN;
-            if (unlikely(prepare_schema_table(thd, Lex, 0, SCH_EXPLAIN)))
+            if (unlikely(prepare_schema_table(thd, Lex, 0, 
+			      Lex->explain_json ? SCH_EXPLAIN_JSON : SCH_EXPLAIN_TABULAR)))
               MYSQL_YYABORT;
-            add_value_to_list(thd, $3);
+            add_value_to_list(thd, $4);
           }
         | IDENT_sys remember_tok_start wild_and_where
            {
@@ -14123,12 +14124,13 @@ opt_describe_column:
         ;
 
 explain_for_connection:
-          describe_command FOR_SYM CONNECTION_SYM expr
+          describe_command opt_format_json FOR_SYM CONNECTION_SYM expr
           {
             Lex->sql_command= SQLCOM_SHOW_EXPLAIN;
-            if (unlikely(prepare_schema_table(thd, Lex, 0, SCH_EXPLAIN)))
+            if (unlikely(prepare_schema_table(thd, Lex, 0, 
+			    Lex->explain_json ? SCH_EXPLAIN_JSON : SCH_EXPLAIN_TABULAR)))
               MYSQL_YYABORT;
-            add_value_to_list(thd, $4);
+            add_value_to_list(thd, $5);
           }
 		;