MDEV-6111: Optimizer Trace: add tracing for semi-join optimizations

Added:
- "semijoin_strategy_choice" element (actions in advance_sj_state(), name
  matches the name in MySQL)

- semijoin_table_pullout element.

1 files changed, 263 insertions, 10 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 518da2888cc..ab07db38d05 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -2663,6 +2663,11 @@ explain extended select * from t1 where a in (select pk from t10)	{
             ]
           },
           {
+            "semijoin_table_pullout": {
+              "pulled_out_tables": []
+            }
+          },
+          {
             "execution_plan_for_potential_materialization": {
               "steps": [
                 {
@@ -2705,6 +2710,7 @@ explain extended select * from t1 where a in (select pk from t10)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6066,
+                "semijoin_strategy_choice": [],
                 "rest_of_plan": [
                   {
                     "plan_prefix": ["t1"],
@@ -2720,7 +2726,27 @@ explain extended select * from t1 where a in (select pk from t10)	{
                       ]
                     },
                     "rows_for_plan": 30,
-                    "cost_for_plan": 10.629
+                    "cost_for_plan": 10.629,
+                    "semijoin_strategy_choice": [
+                      {
+                        "strategy": "FirstMatch",
+                        "records": 3,
+                        "read_time": 10.629
+                      },
+                      {
+                        "strategy": "SJ-Materialization",
+                        "records": 3,
+                        "read_time": 5.2786
+                      },
+                      {
+                        "strategy": "DuplicateWeedout",
+                        "records": 3,
+                        "read_time": 27.129
+                      },
+                      {
+                        "chosen_strategy": "SJ-Materialize"
+                      }
+                    ]
                   }
                 ]
               },
@@ -2739,6 +2765,7 @@ explain extended select * from t1 where a in (select pk from t10)	{
                 },
                 "rows_for_plan": 10,
                 "cost_for_plan": 4.022,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               }
             ]
@@ -2746,7 +2773,7 @@ explain extended select * from t1 where a in (select pk from t10)	{
           {
             "fix_semijoin_strategies_for_picked_join_order": [
               {
-                "semi_join_strategy": "sj_materialize",
+                "semi_join_strategy": "SJ-Materialization",
                 "join_order": [
                   {
                     "table": "t10"
@@ -4082,6 +4109,11 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
             ]
           },
           {
+            "semijoin_table_pullout": {
+              "pulled_out_tables": []
+            }
+          },
+          {
             "execution_plan_for_potential_materialization": {
               "steps": [
                 {
@@ -4159,6 +4191,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "rest_of_plan": [
                   {
                     "plan_prefix": ["t1"],
@@ -4175,6 +4208,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                     },
                     "rows_for_plan": 9,
                     "cost_for_plan": 6.4103,
+                    "semijoin_strategy_choice": [],
                     "rest_of_plan": [
                       {
                         "plan_prefix": ["t1", "t_inner_1"],
@@ -4190,7 +4224,27 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                           ]
                         },
                         "rows_for_plan": 27,
-                        "cost_for_plan": 13.815
+                        "cost_for_plan": 13.815,
+                        "semijoin_strategy_choice": [
+                          {
+                            "strategy": "FirstMatch",
+                            "records": 3,
+                            "read_time": 26.667
+                          },
+                          {
+                            "strategy": "SJ-Materialization",
+                            "records": 3,
+                            "read_time": 7.2154
+                          },
+                          {
+                            "strategy": "DuplicateWeedout",
+                            "records": 3,
+                            "read_time": 18.315
+                          },
+                          {
+                            "chosen_strategy": "SJ-Materialize"
+                          }
+                        ]
                       }
                     ]
                   },
@@ -4209,6 +4263,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                     },
                     "rows_for_plan": 9,
                     "cost_for_plan": 6.4103,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   }
                 ]
@@ -4228,6 +4283,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -4245,6 +4301,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               }
             ]
@@ -4252,7 +4309,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
           {
             "fix_semijoin_strategies_for_picked_join_order": [
               {
-                "semi_join_strategy": "sj_materialize",
+                "semi_join_strategy": "SJ-Materialization",
                 "join_order": [
                   {
                     "table": "t_inner_1"
@@ -4512,6 +4569,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
             ]
           },
           {
+            "semijoin_table_pullout": {
+              "pulled_out_tables": []
+            }
+          },
+          {
+            "semijoin_table_pullout": {
+              "pulled_out_tables": []
+            }
+          },
+          {
             "execution_plan_for_potential_materialization": {
               "steps": []
             }
@@ -4533,6 +4600,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "rest_of_plan": [
                   {
                     "plan_prefix": ["t_outer_1"],
@@ -4549,6 +4617,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 9,
                     "cost_for_plan": 6.4103,
+                    "semijoin_strategy_choice": [],
                     "rest_of_plan": [
                       {
                         "plan_prefix": ["t_outer_1", "t_inner_1"],
@@ -4565,6 +4634,21 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 81,
                         "cost_for_plan": 24.626,
+                        "semijoin_strategy_choice": [
+                          {
+                            "strategy": "FirstMatch",
+                            "records": 3,
+                            "read_time": 26.759
+                          },
+                          {
+                            "strategy": "DuplicateWeedout",
+                            "records": 3,
+                            "read_time": 37.226
+                          },
+                          {
+                            "chosen_strategy": "FirstMatch"
+                          }
+                        ],
                         "rest_of_plan": [
                           {
                             "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
@@ -4581,6 +4665,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 27,
                             "cost_for_plan": 34.174,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -4602,6 +4687,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 81,
                                 "cost_for_plan": 52.379,
+                                "semijoin_strategy_choice": [],
                                 "rest_of_plan": [
                                   {
                                     "plan_prefix": [
@@ -4623,7 +4709,22 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                       ]
                                     },
                                     "rows_for_plan": 729,
-                                    "cost_for_plan": 200.19
+                                    "cost_for_plan": 200.19,
+                                    "semijoin_strategy_choice": [
+                                      {
+                                        "strategy": "FirstMatch",
+                                        "records": 27,
+                                        "read_time": 251.56
+                                      },
+                                      {
+                                        "strategy": "DuplicateWeedout",
+                                        "records": 27,
+                                        "read_time": 313.59
+                                      },
+                                      {
+                                        "chosen_strategy": "FirstMatch"
+                                      }
+                                    ]
                                   }
                                 ]
                               },
@@ -4647,6 +4748,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 243,
                                 "cost_for_plan": 84.79,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_heuristic": true
                               }
                             ]
@@ -4666,6 +4768,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 9,
                             "cost_for_plan": 30.564,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -4687,6 +4790,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 81,
                                 "cost_for_plan": 48.779,
+                                "semijoin_strategy_choice": [],
                                 "rest_of_plan": [
                                   {
                                     "plan_prefix": [
@@ -4709,6 +4813,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                     },
                                     "rows_for_plan": 729,
                                     "cost_for_plan": 196.59,
+                                    "semijoin_strategy_choice": [
+                                      {
+                                        "strategy": "DuplicateWeedout",
+                                        "records": 27,
+                                        "read_time": 309.99
+                                      },
+                                      {
+                                        "chosen_strategy": "DuplicateWeedout"
+                                      }
+                                    ],
                                     "pruned_by_cost": true
                                   }
                                 ]
@@ -4733,6 +4847,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 81,
                                 "cost_for_plan": 48.779,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_heuristic": true
                               }
                             ]
@@ -4752,6 +4867,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 27,
                             "cost_for_plan": 34.174,
+                            "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           }
                         ]
@@ -4771,6 +4887,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 81,
                         "cost_for_plan": 24.626,
+                        "semijoin_strategy_choice": [],
                         "rest_of_plan": [
                           {
                             "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"],
@@ -4787,6 +4904,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 729,
                             "cost_for_plan": 172.44,
+                            "semijoin_strategy_choice": [
+                              {
+                                "strategy": "DuplicateWeedout",
+                                "records": 27,
+                                "read_time": 285.84
+                              },
+                              {
+                                "chosen_strategy": "DuplicateWeedout"
+                              }
+                            ],
                             "pruned_by_cost": true
                           },
                           {
@@ -4804,6 +4931,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 243,
                             "cost_for_plan": 75.231,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -4825,6 +4953,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 514.65,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               },
                               {
@@ -4847,6 +4976,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 514.65,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
                             ]
@@ -4866,6 +4996,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 729,
                             "cost_for_plan": 172.44,
+                            "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           }
                         ]
@@ -4885,6 +5016,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 27,
                         "cost_for_plan": 13.815,
+                        "semijoin_strategy_choice": [],
                         "rest_of_plan": [
                           {
                             "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"],
@@ -4901,6 +5033,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 243,
                             "cost_for_plan": 64.431,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -4922,6 +5055,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 503.85,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               },
                               {
@@ -4944,6 +5078,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 503.85,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
                             ]
@@ -4963,6 +5098,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 243,
                             "cost_for_plan": 64.431,
+                            "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           },
                           {
@@ -4980,6 +5116,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 243,
                             "cost_for_plan": 64.431,
+                            "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           }
                         ]
@@ -4999,6 +5136,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 81,
                         "cost_for_plan": 24.626,
+                        "semijoin_strategy_choice": [],
                         "rest_of_plan": [
                           {
                             "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"],
@@ -5015,6 +5153,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 729,
                             "cost_for_plan": 172.44,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -5036,6 +5175,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 611.85,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               },
                               {
@@ -5058,6 +5198,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 6561,
                                 "cost_for_plan": 1486.7,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
                             ]
@@ -5077,6 +5218,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 243,
                             "cost_for_plan": 75.231,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -5098,6 +5240,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 514.65,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               },
                               {
@@ -5120,6 +5263,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 2187,
                                 "cost_for_plan": 514.65,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
                             ]
@@ -5139,6 +5283,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 729,
                             "cost_for_plan": 172.44,
+                            "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           }
                         ]
@@ -5160,6 +5305,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 27,
                     "cost_for_plan": 10.021,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   },
                   {
@@ -5177,6 +5323,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 27,
                     "cost_for_plan": 10.021,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   },
                   {
@@ -5194,6 +5341,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 9,
                     "cost_for_plan": 6.4103,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   },
                   {
@@ -5211,6 +5359,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 27,
                     "cost_for_plan": 10.021,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   }
                 ]
@@ -5230,6 +5379,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -5247,6 +5397,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 9,
                 "cost_for_plan": 3.8154,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -5264,6 +5415,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 9,
                 "cost_for_plan": 3.8154,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -5281,6 +5433,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -5298,6 +5451,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 9,
                 "cost_for_plan": 3.8154,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               }
             ]
@@ -5305,7 +5459,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
           {
             "fix_semijoin_strategies_for_picked_join_order": [
               {
-                "semi_join_strategy": "firstmatch",
+                "semi_join_strategy": "FirstMatch",
                 "join_order": [
                   {
                     "table": "t_inner_4"
@@ -5336,7 +5490,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 ]
               },
               {
-                "semi_join_strategy": "firstmatch",
+                "semi_join_strategy": "FirstMatch",
                 "join_order": [
                   {
                     "table": "t_inner_1"
@@ -5622,6 +5776,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
             ]
           },
           {
+            "semijoin_table_pullout": {
+              "pulled_out_tables": []
+            }
+          },
+          {
+            "semijoin_table_pullout": {
+              "pulled_out_tables": []
+            }
+          },
+          {
             "execution_plan_for_potential_materialization": {
               "steps": [
                 {
@@ -5754,6 +5918,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "rest_of_plan": [
                   {
                     "plan_prefix": ["t_outer_1"],
@@ -5770,6 +5935,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 9,
                     "cost_for_plan": 6.4103,
+                    "semijoin_strategy_choice": [],
                     "rest_of_plan": [
                       {
                         "plan_prefix": ["t_outer_1", "t_inner_1"],
@@ -5786,6 +5952,26 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 81,
                         "cost_for_plan": 24.626,
+                        "semijoin_strategy_choice": [
+                          {
+                            "strategy": "FirstMatch",
+                            "records": 3,
+                            "read_time": 26.759
+                          },
+                          {
+                            "strategy": "SJ-Materialization",
+                            "records": 3,
+                            "read_time": 8.1256
+                          },
+                          {
+                            "strategy": "DuplicateWeedout",
+                            "records": 3,
+                            "read_time": 37.226
+                          },
+                          {
+                            "chosen_strategy": "SJ-Materialize"
+                          }
+                        ],
                         "rest_of_plan": [
                           {
                             "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
@@ -5802,6 +5988,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 27,
                             "cost_for_plan": 15.541,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -5823,6 +6010,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 81,
                                 "cost_for_plan": 33.746,
+                                "semijoin_strategy_choice": [],
                                 "rest_of_plan": [
                                   {
                                     "plan_prefix": [
@@ -5844,7 +6032,27 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                       ]
                                     },
                                     "rows_for_plan": 729,
-                                    "cost_for_plan": 181.56
+                                    "cost_for_plan": 181.56,
+                                    "semijoin_strategy_choice": [
+                                      {
+                                        "strategy": "FirstMatch",
+                                        "records": 27,
+                                        "read_time": 232.93
+                                      },
+                                      {
+                                        "strategy": "SJ-Materialization",
+                                        "records": 27,
+                                        "read_time": 22.262
+                                      },
+                                      {
+                                        "strategy": "DuplicateWeedout",
+                                        "records": 27,
+                                        "read_time": 294.96
+                                      },
+                                      {
+                                        "chosen_strategy": "SJ-Materialize"
+                                      }
+                                    ]
                                   }
                                 ]
                               },
@@ -5868,6 +6076,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 243,
                                 "cost_for_plan": 66.156,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
                             ]
@@ -5887,6 +6096,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 9,
                             "cost_for_plan": 11.931,
+                            "semijoin_strategy_choice": [],
                             "rest_of_plan": [
                               {
                                 "plan_prefix": [
@@ -5908,6 +6118,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 81,
                                 "cost_for_plan": 30.146,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               },
                               {
@@ -5930,6 +6141,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                                 },
                                 "rows_for_plan": 81,
                                 "cost_for_plan": 30.146,
+                                "semijoin_strategy_choice": [],
                                 "pruned_by_cost": true
                               }
                             ]
@@ -5949,6 +6161,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                             },
                             "rows_for_plan": 27,
                             "cost_for_plan": 15.541,
+                            "semijoin_strategy_choice": [],
                             "pruned_by_heuristic": true
                           }
                         ]
@@ -5968,6 +6181,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 81,
                         "cost_for_plan": 24.626,
+                        "semijoin_strategy_choice": [],
                         "pruned_by_cost": true
                       },
                       {
@@ -5985,6 +6199,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 27,
                         "cost_for_plan": 13.815,
+                        "semijoin_strategy_choice": [],
                         "pruned_by_heuristic": true
                       },
                       {
@@ -6002,6 +6217,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                         },
                         "rows_for_plan": 81,
                         "cost_for_plan": 24.626,
+                        "semijoin_strategy_choice": [],
                         "pruned_by_cost": true
                       }
                     ]
@@ -6021,6 +6237,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 27,
                     "cost_for_plan": 10.021,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   },
                   {
@@ -6038,6 +6255,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 27,
                     "cost_for_plan": 10.021,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   },
                   {
@@ -6055,6 +6273,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 9,
                     "cost_for_plan": 6.4103,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   },
                   {
@@ -6072,6 +6291,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                     },
                     "rows_for_plan": 27,
                     "cost_for_plan": 10.021,
+                    "semijoin_strategy_choice": [],
                     "pruned_by_heuristic": true
                   }
                 ]
@@ -6091,6 +6311,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -6108,6 +6329,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 9,
                 "cost_for_plan": 3.8154,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -6125,6 +6347,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 9,
                 "cost_for_plan": 3.8154,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -6142,6 +6365,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 3,
                 "cost_for_plan": 2.6051,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               },
               {
@@ -6159,6 +6383,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 },
                 "rows_for_plan": 9,
                 "cost_for_plan": 3.8154,
+                "semijoin_strategy_choice": [],
                 "pruned_by_heuristic": true
               }
             ]
@@ -6166,7 +6391,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
           {
             "fix_semijoin_strategies_for_picked_join_order": [
               {
-                "semi_join_strategy": "sj_materialize",
+                "semi_join_strategy": "SJ-Materialization",
                 "join_order": [
                   {
                     "table": "t_inner_4"
@@ -6177,7 +6402,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
                 ]
               },
               {
-                "semi_join_strategy": "sj_materialize",
+                "semi_join_strategy": "SJ-Materialization",
                 "join_order": [
                   {
                     "table": "t_inner_1"
@@ -7026,4 +7251,32 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
     }
 ]
 DROP TABLE t1;
+#
+# Test for Semi-Join table pullout element
+#
+create table t1 (a int primary key, b int);
+insert into t1 (a) values (1),(2),(3),(4),(5);
+create table t2 (a int primary key, b int);
+insert into t2 (a) values (1),(2),(3),(4),(5);
+create table t3 (a int);
+insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+explain 
+select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	5	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout'))
+[
+    
+    {
+        "pulled_out_tables": 
+        [
+            "t2",
+            "t1"
+        ]
+    }
+]
+drop table t1,t2,t3;
 set optimizer_trace='enabled=off';