Adding upstream version 1:10.11.6.upstream/1%10.11.6

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

1 files changed, 1203 insertions, 0 deletions

diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result
new file mode 100644
index 00000000..5572a4f3
--- /dev/null
+++ b/mysql-test/main/analyze_format_json.result
@@ -0,0 +1,1203 @@
+drop table if exists t0,t1,t2,t3;
+create table t0 (a int);
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+# r_filtered=30%, because 3 rows match: 0,1,2
+analyze format=json select * from t0 where a<3;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t0",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 30,
+          "attached_condition": "t0.a < 3"
+        }
+      }
+    ]
+  }
+}
+create table t1 (a int, b int, c int, key(a));
+insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B;
+analyze
+select * from t0, t1 where t1.a=t0.a and t0.a > 9;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	0.00	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	NULL	100.00	NULL	
+analyze format=json 
+select * from t0, t1 where t1.a=t0.a and t0.a > 9;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t0",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 0,
+          "attached_condition": "t0.a > 9 and t0.a is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ref",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "ref": ["test.t0.a"],
+          "r_loops": 0,
+          "rows": 1,
+          "r_rows": null,
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": null
+        }
+      }
+    ]
+  }
+}
+analyze
+select * from t0, t1 where t1.a=t0.a and t1.b<4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	1.00	100.00	40.00	Using where
+analyze format=json
+select * from t0, t1 where t1.a=t0.a and t1.b<4;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t0",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 100,
+          "attached_condition": "t0.a is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ref",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "ref": ["test.t0.a"],
+          "r_loops": 10,
+          "rows": 1,
+          "r_rows": 1,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 40,
+          "attached_condition": "t1.b < 4"
+        }
+      }
+    ]
+  }
+}
+analyze 
+select * from t1 tbl1, t1 tbl2 where tbl1.b<2 and tbl2.b>5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	tbl1	ALL	NULL	NULL	NULL	NULL	100	100.00	100.00	2.00	Using where
+1	SIMPLE	tbl2	ALL	NULL	NULL	NULL	NULL	100	100.00	100.00	94.00	Using where; Using join buffer (flat, BNL join)
+analyze format=json
+select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "tbl1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 100,
+          "r_rows": 100,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 20,
+          "attached_condition": "tbl1.b < 20"
+        }
+      },
+      {
+        "block-nl-join": {
+          "table": {
+            "table_name": "tbl2",
+            "access_type": "ALL",
+            "r_loops": 1,
+            "rows": 100,
+            "r_rows": 100,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_engine_stats": REPLACED,
+            "filtered": 100,
+            "r_filtered": 60,
+            "attached_condition": "tbl2.b < 60"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "1Kb",
+          "join_type": "BNL",
+          "r_loops": 20,
+          "r_filtered": 100,
+          "r_unpack_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_effective_rows": 60
+        }
+      }
+    ]
+  }
+}
+analyze format=json
+select * from t1 tbl1, t1 tbl2 where tbl1.b<20 and tbl2.b<60 and tbl1.c > tbl2.c;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "tbl1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 100,
+          "r_rows": 100,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 20,
+          "attached_condition": "tbl1.b < 20"
+        }
+      },
+      {
+        "block-nl-join": {
+          "table": {
+            "table_name": "tbl2",
+            "access_type": "ALL",
+            "r_loops": 1,
+            "rows": 100,
+            "r_rows": 100,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_engine_stats": REPLACED,
+            "filtered": 100,
+            "r_filtered": 60,
+            "attached_condition": "tbl2.b < 60"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "1Kb",
+          "join_type": "BNL",
+          "attached_condition": "tbl1.c > tbl2.c",
+          "r_loops": 20,
+          "r_filtered": 15.83333333,
+          "r_unpack_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_effective_rows": 60
+        }
+      }
+    ]
+  }
+}
+drop table t1;
+drop table t0;
+#
+# MDEV-7674: ANALYZE shows r_rows=0
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, key(a));
+insert into t2 values (0),(1);
+analyze format=json select * from t1 straight_join t2 force index(a) where t2.a=t1.a;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 100,
+          "attached_condition": "t1.a is not null"
+        }
+      },
+      {
+        "table": {
+          "table_name": "t2",
+          "access_type": "ref",
+          "possible_keys": ["a"],
+          "key": "a",
+          "key_length": "5",
+          "used_key_parts": ["a"],
+          "ref": ["test.t1.a"],
+          "r_loops": 10,
+          "rows": 2,
+          "r_rows": 0.2,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 100,
+          "using_index": true
+        }
+      }
+    ]
+  }
+}
+drop table t1,t2;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+#
+# MDEV-7679: ANALYZE crashes when printing WHERE when no default db
+#
+select database();
+database()
+test
+connect  con1,localhost,root,,*NO-ONE*;
+connection con1;
+select database();
+database()
+NULL
+analyze format=json select * from test.t1 where t1.a<5;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 50,
+          "attached_condition": "test.t1.a < 5"
+        }
+      }
+    ]
+  }
+}
+disconnect con1;
+connection default;
+drop table t1;
+#
+# MDEV-7812: ANALYZE FORMAT=JSON UPDATE/DELETE doesnt print
+# the r_total_time_ms
+#
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t3(a int);
+insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C;
+create table t1 (pk int primary key);
+insert into t1 select a from t3;
+alter table t1 add b int;
+analyze format=json
+update t1 set b=pk;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "update": 1,
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 1000,
+      "r_rows": 1000,
+      "r_filtered": 100,
+      "r_total_time_ms": "REPLACED",
+      "r_engine_stats": REPLACED
+    }
+  }
+}
+analyze format=json
+select * from t1 where pk < 10 and b > 4;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "range",
+          "possible_keys": ["PRIMARY"],
+          "key": "PRIMARY",
+          "key_length": "4",
+          "used_key_parts": ["pk"],
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 50,
+          "index_condition": "t1.pk < 10",
+          "attached_condition": "t1.b > 4"
+        }
+      }
+    ]
+  }
+}
+analyze format=json
+delete from t1 where pk < 10 and b > 4;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "delete": 1,
+      "table_name": "t1",
+      "access_type": "range",
+      "possible_keys": ["PRIMARY"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["pk"],
+      "rows": 10,
+      "r_rows": 10,
+      "r_filtered": 50,
+      "r_total_time_ms": "REPLACED",
+      "r_engine_stats": REPLACED,
+      "attached_condition": "t1.pk < 10 and t1.b > 4"
+    }
+  }
+}
+drop table t1, t3, t2;
+#
+# MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record
+#
+create table t3(a int);
+insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t4(a int);
+insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 C;
+create table t1 (lb1 int, rb1 int, lb2 int, rb2 int, c1 int, c2 int);
+insert into t1 values (1,2,10,20,15,15);
+insert into t1 values (3,5,10,20,15,15);
+insert into t1 values (10,20,10,20,15,15);
+insert into t1 values (10,20,1,2,15,15);
+insert into t1 values (10,20,10,20,1,3);
+create table t2 (key1 int, key2 int, key3 int, key4 int, col1 int,
+key(key1), key(key2), key(key3), key(key4));
+insert into t2 select a,a,a,a,a from t3;
+insert into t2 select 15,15,15,15,15 from t4;
+analyze format=json
+select * from t1, t2 where (t2.key1 between t1.lb1 and t1.rb1) and
+(t2.key2 between t1.lb2 and t1.rb2) and
+(t2.key3=t1.c1  OR t2.key4=t1.c2);
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 5,
+          "r_rows": 5,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 100
+        }
+      },
+      {
+        "range-checked-for-each-record": {
+          "keys": ["key1", "key2", "key3", "key4"],
+          "r_keys": {
+            "full_scan": 1,
+            "index_merge": 1,
+            "range": {
+              "key1": 2,
+              "key2": 1,
+              "key3": 0,
+              "key4": 0
+            }
+          },
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "possible_keys": ["key1", "key2", "key3", "key4"],
+            "r_loops": 5,
+            "rows": 1010,
+            "r_rows": 203.8,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_engine_stats": REPLACED,
+            "filtered": 100,
+            "r_filtered": 98.13542689
+          }
+        }
+      }
+    ]
+  }
+}
+drop table t1,t2,t3,t4;
+#
+# MDEV-7904: ANALYZE FORMAT=JSON SELECT .. UNION SELECT doesn't print r_rows for union output
+#
+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 * from t0;
+analyze format=json (select * from t1 tbl1 where a<5) union (select * from t1 tbl2 where a in (2,3));
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 5,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "tbl1",
+                  "access_type": "ALL",
+                  "r_loops": 1,
+                  "rows": 10,
+                  "r_rows": 10,
+                  "r_table_time_ms": "REPLACED",
+                  "r_other_time_ms": "REPLACED",
+                  "r_engine_stats": REPLACED,
+                  "filtered": 100,
+                  "r_filtered": 50,
+                  "attached_condition": "tbl1.a < 5"
+                }
+              }
+            ]
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "r_loops": 1,
+            "r_total_time_ms": "REPLACED",
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "tbl2",
+                  "access_type": "ALL",
+                  "r_loops": 1,
+                  "rows": 10,
+                  "r_rows": 10,
+                  "r_table_time_ms": "REPLACED",
+                  "r_other_time_ms": "REPLACED",
+                  "r_engine_stats": REPLACED,
+                  "filtered": 100,
+                  "r_filtered": 20,
+                  "attached_condition": "tbl2.a in (2,3)"
+                }
+              }
+            ]
+          }
+        }
+      ]
+    }
+  }
+}
+drop table t0, t1;
+#
+# MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3);
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (
+a int, 
+b int, 
+key (a)
+);
+insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B;
+# normal HAVING
+analyze format=json select a, max(b) as TOP from t2 group by a having TOP > a;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "having_condition": "TOP > t2.a",
+    "filesort": {
+      "sort_key": "t2.a",
+      "r_loops": 1,
+      "r_total_time_ms": "REPLACED",
+      "r_used_priority_queue": false,
+      "r_output_rows": 0,
+      "r_buffer_size": "REPLACED",
+      "r_sort_mode": "sort_key,rowid",
+      "temporary_table": {
+        "nested_loop": [
+          {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 256,
+              "r_rows": 256,
+              "r_table_time_ms": "REPLACED",
+              "r_other_time_ms": "REPLACED",
+              "r_engine_stats": REPLACED,
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
+      }
+    }
+  }
+}
+# HAVING is always TRUE (not printed)
+analyze format=json select a, max(b) as TOP from t2 group by a having 1<>2;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "filesort": {
+      "sort_key": "t2.a",
+      "r_loops": 1,
+      "r_total_time_ms": "REPLACED",
+      "r_used_priority_queue": false,
+      "r_output_rows": 256,
+      "r_buffer_size": "REPLACED",
+      "r_sort_mode": "sort_key,rowid",
+      "temporary_table": {
+        "nested_loop": [
+          {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 256,
+              "r_rows": 256,
+              "r_table_time_ms": "REPLACED",
+              "r_other_time_ms": "REPLACED",
+              "r_engine_stats": REPLACED,
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
+      }
+    }
+  }
+}
+# HAVING is always FALSE (intercepted by message)
+analyze format=json select a, max(b) as TOP from t2 group by a having 1=2;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "message": "Impossible HAVING"
+    }
+  }
+}
+# HAVING is absent
+analyze format=json select a, max(b) as TOP from t2 group by a;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "filesort": {
+      "sort_key": "t2.a",
+      "r_loops": 1,
+      "r_total_time_ms": "REPLACED",
+      "r_used_priority_queue": false,
+      "r_output_rows": 256,
+      "r_buffer_size": "REPLACED",
+      "r_sort_mode": "sort_key,rowid",
+      "temporary_table": {
+        "nested_loop": [
+          {
+            "table": {
+              "table_name": "t2",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 256,
+              "r_rows": 256,
+              "r_table_time_ms": "REPLACED",
+              "r_other_time_ms": "REPLACED",
+              "r_engine_stats": REPLACED,
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
+      }
+    }
+  }
+}
+drop table t0, t1, t2;
+#
+# MDEV-7267: Server crashes in Item_field::print on ANALYZE FORMAT=JSON	
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN * FROM t1, t2 WHERE b IN ( SELECT a FROM t1 );
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 2,
+          "r_rows": 2,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 100
+        }
+      },
+      {
+        "block-nl-join": {
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "r_loops": 1,
+            "rows": 2,
+            "r_rows": 2,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_engine_stats": REPLACED,
+            "filtered": 100,
+            "r_filtered": 0,
+            "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "65",
+          "join_type": "BNL",
+          "attached_condition": "<in_optimizer>(t2.b,t2.b in (subquery#2))",
+          "r_loops": 2,
+          "r_filtered": null,
+          "r_unpack_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_effective_rows": 0
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "r_loops": 1,
+          "r_total_time_ms": "REPLACED",
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "r_loops": 1,
+                "rows": 2,
+                "r_rows": 2,
+                "r_table_time_ms": "REPLACED",
+                "r_other_time_ms": "REPLACED",
+                "r_engine_stats": REPLACED,
+                "filtered": 100,
+                "r_filtered": 100
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+drop table t1,t2;
+#
+# MDEV-8864: Server crash #2 in Item_field::print on ANALYZE FORMAT=JSON
+#
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (2),(3);
+CREATE TABLE t3 (f3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (3),(4);
+ANALYZE FORMAT=JSON 
+SELECT GROUP_CONCAT(f3) AS gc, ( SELECT MAX(f1) FROM t1, t2 WHERE f2 = f3 ) sq
+FROM t2, t3
+WHERE f3 IN ( 1, 2 ) 
+GROUP BY sq ORDER BY gc;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "filesort": {
+      "sort_key": "group_concat(t3.f3 separator ',')",
+      "r_loops": 1,
+      "r_total_time_ms": "REPLACED",
+      "r_used_priority_queue": false,
+      "r_output_rows": 0,
+      "r_buffer_size": "REPLACED",
+      "r_sort_mode": "packed_sort_key,rowid",
+      "temporary_table": {
+        "filesort": {
+          "sort_key": "(subquery#2)",
+          "r_loops": 1,
+          "r_total_time_ms": "REPLACED",
+          "r_used_priority_queue": false,
+          "r_output_rows": 0,
+          "r_buffer_size": "REPLACED",
+          "r_sort_mode": "sort_key,rowid",
+          "temporary_table": {
+            "nested_loop": [
+              {
+                "table": {
+                  "table_name": "t2",
+                  "access_type": "ALL",
+                  "r_loops": 1,
+                  "rows": 2,
+                  "r_rows": 2,
+                  "r_table_time_ms": "REPLACED",
+                  "r_other_time_ms": "REPLACED",
+                  "r_engine_stats": REPLACED,
+                  "filtered": 100,
+                  "r_filtered": 100
+                }
+              },
+              {
+                "block-nl-join": {
+                  "table": {
+                    "table_name": "t3",
+                    "access_type": "ALL",
+                    "r_loops": 1,
+                    "rows": 2,
+                    "r_rows": 2,
+                    "r_table_time_ms": "REPLACED",
+                    "r_other_time_ms": "REPLACED",
+                    "r_engine_stats": REPLACED,
+                    "filtered": 100,
+                    "r_filtered": 0,
+                    "attached_condition": "t3.f3 in (1,2)"
+                  },
+                  "buffer_type": "flat",
+                  "buffer_size": "1",
+                  "join_type": "BNL",
+                  "r_loops": 2,
+                  "r_filtered": null,
+                  "r_unpack_time_ms": "REPLACED",
+                  "r_other_time_ms": "REPLACED",
+                  "r_effective_rows": 0
+                }
+              }
+            ],
+            "subqueries": [
+              {
+                "expression_cache": {
+                  "state": "uninitialized",
+                  "r_loops": 0,
+                  "query_block": {
+                    "select_id": 2,
+                    "nested_loop": [
+                      {
+                        "table": {
+                          "table_name": "t1",
+                          "access_type": "ALL",
+                          "r_loops": 0,
+                          "rows": 2,
+                          "r_rows": null,
+                          "r_engine_stats": REPLACED,
+                          "filtered": 100,
+                          "r_filtered": null
+                        }
+                      },
+                      {
+                        "block-nl-join": {
+                          "table": {
+                            "table_name": "t2",
+                            "access_type": "ALL",
+                            "r_loops": 0,
+                            "rows": 2,
+                            "r_rows": null,
+                            "r_engine_stats": REPLACED,
+                            "filtered": 100,
+                            "r_filtered": null
+                          },
+                          "buffer_type": "flat",
+                          "buffer_size": "65",
+                          "join_type": "BNL",
+                          "attached_condition": "t2.f2 = t3.f3",
+                          "r_loops": 0,
+                          "r_filtered": null,
+                          "r_unpack_time_ms": "REPLACED",
+                          "r_other_time_ms": "REPLACED",
+                          "r_effective_rows": null
+                        }
+                      }
+                    ]
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+drop table t1,t2,t3;
+#
+# MDEV-13286: Floating point exception in Filesort_tracker::print_json_members(Json_writer*)
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, c int);
+insert into t1 select a,a,a from t0;
+create table t2 as select * from t1;
+analyze format=json select a, (select t2.b from t2 where t2.a<t1.a order by t2.c limit 1) from t1 where t1.a<0;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 10,
+          "r_rows": 10,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 100,
+          "r_filtered": 0,
+          "attached_condition": "t1.a < 0"
+        }
+      }
+    ],
+    "subqueries": [
+      {
+        "expression_cache": {
+          "state": "uninitialized",
+          "r_loops": 0,
+          "query_block": {
+            "select_id": 2,
+            "nested_loop": [
+              {
+                "read_sorted_file": {
+                  "r_rows": null,
+                  "filesort": {
+                    "sort_key": "t2.c",
+                    "r_loops": null,
+                    "r_limit": null,
+                    "r_used_priority_queue": null,
+                    "r_output_rows": null,
+                    "r_sort_mode": "sort_key,rowid",
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "r_loops": 0,
+                      "rows": 10,
+                      "r_rows": null,
+                      "r_engine_stats": REPLACED,
+                      "filtered": 100,
+                      "r_filtered": null,
+                      "attached_condition": "t2.a < t1.a"
+                    }
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    ]
+  }
+}
+drop table t0,t1,t2;
+#
+# MDEV-30806: ANALYZE FORMAT=JSON: better support for BNL and BNL-H joins
+#
+create table t10 (
+a int,
+b int
+);
+insert into t10 select seq, seq/3 from seq_0_to_999;
+create table t11 (
+a int,
+b int
+);
+insert into t11 select seq, seq/5 from seq_0_to_999;
+analyze table t10,t11 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t10	analyze	status	Engine-independent statistics collected
+test.t10	analyze	status	OK
+test.t11	analyze	status	Engine-independent statistics collected
+test.t11	analyze	status	OK
+analyze format=json
+select * from t10, t11
+where
+t10.a < 700 and
+t11.a < 100
+and t10.b=t11.b;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t11",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 1000,
+          "r_rows": 1000,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 10,
+          "r_filtered": 10,
+          "attached_condition": "t11.a < 100"
+        }
+      },
+      {
+        "block-nl-join": {
+          "table": {
+            "table_name": "t10",
+            "access_type": "ALL",
+            "r_loops": 1,
+            "rows": 1000,
+            "r_rows": 1000,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_engine_stats": REPLACED,
+            "filtered": 70,
+            "r_filtered": 70,
+            "attached_condition": "t10.a < 700"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "1Kb",
+          "join_type": "BNL",
+          "attached_condition": "t10.b = t11.b",
+          "r_loops": 100,
+          "r_filtered": 0.424285714,
+          "r_unpack_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_effective_rows": 700
+        }
+      }
+    ]
+  }
+}
+set @tmp=@@join_cache_level, join_cache_level=6;
+analyze format=json
+select * from t10, t11
+where
+t10.a < 700 and
+t11.a < 100
+and t10.b=t11.b;
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "t11",
+          "access_type": "ALL",
+          "r_loops": 1,
+          "rows": 1000,
+          "r_rows": 1000,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_engine_stats": REPLACED,
+          "filtered": 10,
+          "r_filtered": 10,
+          "attached_condition": "t11.a < 100 and t11.b is not null"
+        }
+      },
+      {
+        "block-nl-join": {
+          "table": {
+            "table_name": "t10",
+            "access_type": "hash_ALL",
+            "key": "#hash#$hj",
+            "key_length": "5",
+            "used_key_parts": ["b"],
+            "ref": ["test.t11.b"],
+            "r_loops": 1,
+            "rows": 1000,
+            "r_rows": 1000,
+            "r_table_time_ms": "REPLACED",
+            "r_other_time_ms": "REPLACED",
+            "r_engine_stats": REPLACED,
+            "filtered": 70,
+            "r_filtered": 70,
+            "attached_condition": "t10.a < 700"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "3Kb",
+          "join_type": "BNLH",
+          "attached_condition": "t10.b = t11.b",
+          "r_loops": 100,
+          "r_filtered": 100,
+          "r_unpack_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "r_effective_rows": 2.97
+        }
+      }
+    ]
+  }
+}
+set join_cache_level=@tmp;
+drop table t10, t11;