MDEV-27036: resolve duplicated key issues of JSON tracing outputs:

MDEV-27036: repeated "table" key resolve for print_explain_json

MDEV-27036: duplicated keys in best_access_path

MDEV-27036: Explain_aggr_filesort::print_json_members: resolve duplicated "filesort" member in Json object

MDEV-27036: Explain_basic_join::
            print_explain_json_interns fixed start_dups_weedout case for main.explain_json test

1 files changed, 491 insertions, 393 deletions

diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result
index fc643d1a8af..6c9b8ce2982 100644
--- a/mysql-test/main/analyze_format_json.result
+++ b/mysql-test/main/analyze_format_json.result
@@ -9,18 +9,22 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "filtered": 100,
-      "r_filtered": 30,
-      "attached_condition": "t0.a < 3"
-    }
+    "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",
+          "filtered": 100,
+          "r_filtered": 30,
+          "attached_condition": "t0.a < 3"
+        }
+      }
+    ]
   }
 }
 create table t1 (a int, b int, c int, key(a));
@@ -38,32 +42,38 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "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,
-      "filtered": 100,
-      "r_filtered": null
-    }
+    "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",
+          "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,
+          "filtered": 100,
+          "r_filtered": null
+        }
+      }
+    ]
   }
 }
 analyze
@@ -79,35 +89,41 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "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",
-      "filtered": 100,
-      "r_filtered": 40,
-      "attached_condition": "t1.b < 4"
-    }
+    "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",
+          "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",
+          "filtered": 100,
+          "r_filtered": 40,
+          "attached_condition": "t1.b < 4"
+        }
+      }
+    ]
   }
 }
 analyze 
@@ -123,36 +139,42 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "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",
-        "filtered": 100,
-        "r_filtered": 60,
-        "attached_condition": "tbl2.b < 60"
+    "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",
+          "filtered": 100,
+          "r_filtered": 20,
+          "attached_condition": "tbl1.b < 20"
+        }
       },
-      "buffer_type": "flat",
-      "buffer_size": "1Kb",
-      "join_type": "BNL",
-      "r_filtered": 100
-    }
+      {
+        "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",
+            "filtered": 100,
+            "r_filtered": 60,
+            "attached_condition": "tbl2.b < 60"
+          },
+          "buffer_type": "flat",
+          "buffer_size": "1Kb",
+          "join_type": "BNL",
+          "r_filtered": 100
+        }
+      }
+    ]
   }
 }
 analyze format=json
@@ -163,37 +185,43 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "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",
-        "filtered": 100,
-        "r_filtered": 60,
-        "attached_condition": "tbl2.b < 60"
+    "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",
+          "filtered": 100,
+          "r_filtered": 20,
+          "attached_condition": "tbl1.b < 20"
+        }
       },
-      "buffer_type": "flat",
-      "buffer_size": "1Kb",
-      "join_type": "BNL",
-      "attached_condition": "tbl1.c > tbl2.c",
-      "r_filtered": 15.83333333
-    }
+      {
+        "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",
+            "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_filtered": 15.83333333
+        }
+      }
+    ]
   }
 }
 drop table t1;
@@ -212,35 +240,41 @@ ANALYZE
     "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_table_time_ms": "REPLACED",
-      "r_other_time_ms": "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",
-      "filtered": 100,
-      "r_filtered": 100,
-      "using_index": true
-    }
+    "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",
+          "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",
+          "filtered": 100,
+          "r_filtered": 100,
+          "using_index": true
+        }
+      }
+    ]
   }
 }
 drop table t1,t2;
@@ -264,18 +298,22 @@ ANALYZE
     "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_table_time_ms": "REPLACED",
-      "r_other_time_ms": "REPLACED",
-      "filtered": 100,
-      "r_filtered": 50,
-      "attached_condition": "test.t1.a < 5"
-    }
+    "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",
+          "filtered": 100,
+          "r_filtered": 50,
+          "attached_condition": "test.t1.a < 5"
+        }
+      }
+    ]
   }
 }
 disconnect con1;
@@ -318,23 +356,27 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "filtered": 100,
-      "r_filtered": 50,
-      "index_condition": "t1.pk < 10",
-      "attached_condition": "t1.b > 4"
-    }
+    "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",
+          "filtered": 100,
+          "r_filtered": 50,
+          "index_condition": "t1.pk < 10",
+          "attached_condition": "t1.b > 4"
+        }
+      }
+    ]
   }
 }
 analyze format=json
@@ -388,42 +430,48 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "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
+    "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",
+          "filtered": 100,
+          "r_filtered": 100
         }
       },
-      "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",
-        "filtered": 100,
-        "r_filtered": 98.13542689
+      {
+        "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",
+            "filtered": 100,
+            "r_filtered": 98.13542689
+          }
+        }
       }
-    }
+    ]
   }
 }
 drop table t1,t2,t3,t4;
@@ -449,18 +497,22 @@ ANALYZE
             "select_id": 1,
             "r_loops": 1,
             "r_total_time_ms": "REPLACED",
-            "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",
-              "filtered": 100,
-              "r_filtered": 50,
-              "attached_condition": "tbl1.a < 5"
-            }
+            "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",
+                  "filtered": 100,
+                  "r_filtered": 50,
+                  "attached_condition": "tbl1.a < 5"
+                }
+              }
+            ]
           }
         },
         {
@@ -469,18 +521,22 @@ ANALYZE
             "operation": "UNION",
             "r_loops": 1,
             "r_total_time_ms": "REPLACED",
-            "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",
-              "filtered": 100,
-              "r_filtered": 20,
-              "attached_condition": "tbl2.a in (2,3)"
-            }
+            "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",
+                  "filtered": 100,
+                  "r_filtered": 20,
+                  "attached_condition": "tbl2.a in (2,3)"
+                }
+              }
+            ]
           }
         }
       ]
@@ -519,17 +575,21 @@ ANALYZE
       "r_buffer_size": "REPLACED",
       "r_sort_mode": "sort_key,rowid",
       "temporary_table": {
-        "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",
-          "filtered": 100,
-          "r_filtered": 100
-        }
+        "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",
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
       }
     }
   }
@@ -551,17 +611,21 @@ ANALYZE
       "r_buffer_size": "REPLACED",
       "r_sort_mode": "sort_key,rowid",
       "temporary_table": {
-        "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",
-          "filtered": 100,
-          "r_filtered": 100
-        }
+        "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",
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
       }
     }
   }
@@ -594,17 +658,21 @@ ANALYZE
       "r_buffer_size": "REPLACED",
       "r_sort_mode": "sort_key,rowid",
       "temporary_table": {
-        "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",
-          "filtered": 100,
-          "r_filtered": 100
-        }
+        "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",
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
       }
     }
   }
@@ -624,44 +692,24 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "r_total_time_ms": "REPLACED",
-    "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",
-      "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",
-        "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_filtered": null
-    },
-    "subqueries": [
+    "nested_loop": [
       {
-        "query_block": {
-          "select_id": 2,
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
           "r_loops": 1,
-          "r_total_time_ms": "REPLACED",
+          "rows": 2,
+          "r_rows": 2,
+          "r_table_time_ms": "REPLACED",
+          "r_other_time_ms": "REPLACED",
+          "filtered": 100,
+          "r_filtered": 100
+        }
+      },
+      {
+        "block-nl-join": {
           "table": {
-            "table_name": "t1",
+            "table_name": "t2",
             "access_type": "ALL",
             "r_loops": 1,
             "rows": 2,
@@ -669,8 +717,38 @@ ANALYZE
             "r_table_time_ms": "REPLACED",
             "r_other_time_ms": "REPLACED",
             "filtered": 100,
-            "r_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_filtered": null
+        }
+      }
+    ],
+    "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",
+                "filtered": 100,
+                "r_filtered": 100
+              }
+            }
+          ]
         }
       }
     ]
@@ -715,35 +793,41 @@ ANALYZE
           "r_buffer_size": "REPLACED",
           "r_sort_mode": "sort_key,rowid",
           "temporary_table": {
-            "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",
-              "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",
-                "filtered": 100,
-                "r_filtered": 0,
-                "attached_condition": "t3.f3 in (1,2)"
+            "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",
+                  "filtered": 100,
+                  "r_filtered": 100
+                }
               },
-              "buffer_type": "flat",
-              "buffer_size": "1",
-              "join_type": "BNL",
-              "r_filtered": null
-            },
+              {
+                "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",
+                    "filtered": 100,
+                    "r_filtered": 0,
+                    "attached_condition": "t3.f3 in (1,2)"
+                  },
+                  "buffer_type": "flat",
+                  "buffer_size": "1",
+                  "join_type": "BNL",
+                  "r_filtered": null
+                }
+              }
+            ],
             "subqueries": [
               {
                 "expression_cache": {
@@ -751,31 +835,37 @@ ANALYZE
                   "r_loops": 0,
                   "query_block": {
                     "select_id": 2,
-                    "table": {
-                      "table_name": "t1",
-                      "access_type": "ALL",
-                      "r_loops": 0,
-                      "rows": 2,
-                      "r_rows": null,
-                      "filtered": 100,
-                      "r_filtered": null
-                    },
-                    "block-nl-join": {
-                      "table": {
-                        "table_name": "t2",
-                        "access_type": "ALL",
-                        "r_loops": 0,
-                        "rows": 2,
-                        "r_rows": null,
-                        "filtered": 100,
-                        "r_filtered": null
+                    "nested_loop": [
+                      {
+                        "table": {
+                          "table_name": "t1",
+                          "access_type": "ALL",
+                          "r_loops": 0,
+                          "rows": 2,
+                          "r_rows": null,
+                          "filtered": 100,
+                          "r_filtered": null
+                        }
                       },
-                      "buffer_type": "flat",
-                      "buffer_size": "65",
-                      "join_type": "BNL",
-                      "attached_condition": "t2.f2 = t3.f3",
-                      "r_filtered": null
-                    }
+                      {
+                        "block-nl-join": {
+                          "table": {
+                            "table_name": "t2",
+                            "access_type": "ALL",
+                            "r_loops": 0,
+                            "rows": 2,
+                            "r_rows": null,
+                            "filtered": 100,
+                            "r_filtered": null
+                          },
+                          "buffer_type": "flat",
+                          "buffer_size": "65",
+                          "join_type": "BNL",
+                          "attached_condition": "t2.f2 = t3.f3",
+                          "r_filtered": null
+                        }
+                      }
+                    ]
                   }
                 }
               }
@@ -802,18 +892,22 @@ ANALYZE
     "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_table_time_ms": "REPLACED",
-      "r_other_time_ms": "REPLACED",
-      "filtered": 100,
-      "r_filtered": 0,
-      "attached_condition": "t1.a < 0"
-    },
+    "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",
+          "filtered": 100,
+          "r_filtered": 0,
+          "attached_condition": "t1.a < 0"
+        }
+      }
+    ],
     "subqueries": [
       {
         "expression_cache": {
@@ -821,27 +915,31 @@ ANALYZE
           "r_loops": 0,
           "query_block": {
             "select_id": 2,
-            "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,
+            "nested_loop": [
+              {
+                "read_sorted_file": {
                   "r_rows": null,
-                  "filtered": 100,
-                  "r_filtered": null,
-                  "attached_condition": "t2.a < t1.a"
+                  "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,
+                      "filtered": 100,
+                      "r_filtered": null,
+                      "attached_condition": "t2.a < t1.a"
+                    }
+                  }
                 }
               }
-            }
+            ]
           }
         }
       }