MDEV-7811: EXPLAIN/ANALYZE FORMAT=JSON should show subquery cache10.1-MDEV-7811

2 files changed, 244 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result
index e5a2fe12526..2cf1961ec5b 100644
--- a/mysql-test/r/subselect_cache.result
+++ b/mysql-test/r/subselect_cache.result
@@ -39,6 +39,240 @@ Handler_read_prev	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	31
+analyze format=json
+select a, (select d from t2 where b=c) from t1;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 10,
+      "r_rows": 10,
+      "r_total_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "r_loops": 4,
+          "r_total_time_ms": "REPLACED",
+          "expression_cache": {
+            "state": "ENABLED",
+            "r_hit": 6,
+            "r_miss": 4,
+            "r_loops": 10,
+            "r_hit_ratio": 60
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "r_loops": 4,
+            "rows": 4,
+            "r_rows": 4,
+            "r_total_time_ms": "REPLACED",
+            "filtered": 100,
+            "r_filtered": 18.75,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
+analyze format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 10,
+      "r_rows": 10,
+      "r_total_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union3,4>",
+            "access_type": "ALL",
+            "r_loops": 4,
+            "r_rows": 1,
+            "expression_cache": {
+              "state": "ENABLED",
+              "r_hit": 6,
+              "r_miss": 4,
+              "r_loops": 10,
+              "r_hit_ratio": 60
+            },
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "r_loops": 4,
+                  "r_total_time_ms": "REPLACED",
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "r_loops": 4,
+                    "rows": 4,
+                    "r_rows": 4,
+                    "r_total_time_ms": "REPLACED",
+                    "filtered": 100,
+                    "r_filtered": 18.75,
+                    "attached_condition": "(t1.b = t2.c)"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 4,
+                  "table": {
+                    "message": "No tables used"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      },
+      {
+        "query_block": {
+          "select_id": 2,
+          "r_loops": 4,
+          "r_total_time_ms": "REPLACED",
+          "expression_cache": {
+            "state": "ENABLED",
+            "r_hit": 6,
+            "r_miss": 4,
+            "r_loops": 10,
+            "r_hit_ratio": 60
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "r_loops": 4,
+            "rows": 4,
+            "r_rows": 4,
+            "r_total_time_ms": "REPLACED",
+            "filtered": 100,
+            "r_filtered": 18.75,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
+explain format=json
+select a, (select d from t2 where b=c) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 10,
+      "filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "expression_cache": {
+            "state": "UNINITIALYZED"
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "rows": 4,
+            "filtered": 100,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
+explain format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 10,
+      "filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union3,4>",
+            "access_type": "ALL",
+            "expression_cache": {
+              "state": "UNINITIALYZED"
+            },
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "rows": 4,
+                    "filtered": 100,
+                    "attached_condition": "(t1.b = t2.c)"
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 4,
+                  "table": {
+                    "message": "No tables used"
+                  }
+                }
+              }
+            ]
+          }
+        }
+      },
+      {
+        "query_block": {
+          "select_id": 2,
+          "expression_cache": {
+            "state": "UNINITIALYZED"
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "rows": 4,
+            "filtered": 100,
+            "attached_condition": "(t1.b = t2.c)"
+          }
+        }
+      }
+    ]
+  }
+}
 set optimizer_switch='subquery_cache=off';
 flush status;
 select a, (select d from t2 where b=c) from t1;
diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test
index 1276e546030..21247541fb6 100644
--- a/mysql-test/t/subselect_cache.test
+++ b/mysql-test/t/subselect_cache.test
@@ -24,6 +24,16 @@ select a, (select d from t2 where b=c) from t1;
 show status like "subquery_cache%";
 show status like '%Handler_read%';
 
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+analyze format=json
+select a, (select d from t2 where b=c) from t1;
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/
+analyze format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
+explain format=json
+select a, (select d from t2 where b=c) from t1;
+explain format=json
+select a, (select d from t2 where b=c), (select d from t2 where b=c union select 1 order by 1 limit 1) from t1;
 set optimizer_switch='subquery_cache=off';
 flush status;