opt_trace.result

SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE";
table_name	column_name
OPTIMIZER_TRACE	QUERY
OPTIMIZER_TRACE	TRACE
OPTIMIZER_TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE
OPTIMIZER_TRACE	INSUFFICIENT_PRIVILEGES
set optimizer_trace="enabled=on";
show variables like 'optimizer_trace';
Variable_name	Value
optimizer_trace	enabled=on
set optimizer_trace="enabled=off";
show variables like 'optimizer_trace';
Variable_name	Value
optimizer_trace	enabled=off
create table t1 (a int, b int);
insert into t1 values (1,2),(2,3);
create table t2 (b int);
insert into t2 values (1),(2);
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
analyze table t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
create function f1 (a int) returns INT
return 1;
create view v1 as select * from t1 where t1.a=1;
create view v2 as select * from t1 where t1.a=1 group by t1.b;
set optimizer_trace="enabled=on";
# Mergeable views/derived tables
select * from v1;
a	b
1	2
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from v1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "view": {
              "table": "v1",
              "select_id": 2,
              "algorithm": "merged"
            }
          },
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from v1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.a = 1",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(1, t1.a)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(1, t1.a)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(1, t1.a)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "selectivity_for_indexes": [],
                "selectivity_for_columns": [
                  {
                    "column_name": "a",
                    "ranges": ["1 <= a <= 1"],
                    "selectivity_from_histogram": 0.5
                  }
                ],
                "cond_selectivity": 0.5
              },
              {
                "table": "t1",
                "table_scan": {
                  "rows": 2,
                  "cost": 2.004394531
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 2.204394531,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 1,
                    "cost": 2.204394531,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 1,
                "cost_for_plan": 2.404394531,
                "estimated_join_cardinality": 1
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.a = 1"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": "t1.a = 1"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
select * from (select * from t1 where t1.a=1)q;
a	b
1	2
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from (select * from t1 where t1.a=1)q	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "derived": {
              "table": "q",
              "select_id": 2,
              "algorithm": "merged"
            }
          },
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from (/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1) q"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.a = 1",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(1, t1.a)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(1, t1.a)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(1, t1.a)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "selectivity_for_indexes": [],
                "selectivity_for_columns": [
                  {
                    "column_name": "a",
                    "ranges": ["1 <= a <= 1"],
                    "selectivity_from_histogram": 0.5
                  }
                ],
                "cond_selectivity": 0.5
              },
              {
                "table": "t1",
                "table_scan": {
                  "rows": 2,
                  "cost": 2.004394531
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 2.204394531,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 1,
                    "cost": 2.204394531,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 1,
                "cost_for_plan": 2.404394531,
                "estimated_join_cardinality": 1
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.a = 1"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": "t1.a = 1"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
# Non-Mergeable views
select * from v2;
a	b
1	2
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from v2	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "view": {
              "table": "v2",
              "select_id": 2,
              "algorithm": "materialized"
            }
          },
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select t1.a AS a,t1.b AS b from t1 where t1.a = 1 group by t1.b"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select v2.a AS a,v2.b AS b from v2"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "join_optimization": {
              "select_id": 2,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "t1.a = 1",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "multiple equal(1, t1.a)"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "multiple equal(1, t1.a)"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "multiple equal(1, t1.a)"
                      }
                    ]
                  }
                },
                {
                  "table_dependencies": [
                    {
                      "table": "t1",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": []
                    }
                  ]
                },
                {
                  "ref_optimizer_key_uses": []
                },
                {
                  "rows_estimation": [
                    {
                      "selectivity_for_indexes": [],
                      "selectivity_for_columns": [
                        {
                          "column_name": "a",
                          "ranges": ["1 <= a <= 1"],
                          "selectivity_from_histogram": 0.5
                        }
                      ],
                      "cond_selectivity": 0.5
                    },
                    {
                      "table": "t1",
                      "table_scan": {
                        "rows": 2,
                        "cost": 2.004394531
                      }
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t1",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 1,
                            "cost": 2.204394531,
                            "chosen": true,
                            "use_tmp_table": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 1,
                          "cost": 2.204394531,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 1,
                      "cost_for_plan": 2.404394531,
                      "cost_for_sorting": 1,
                      "estimated_join_cardinality": 1
                    }
                  ]
                },
                {
                  "best_join_order": ["t1"]
                },
                {
                  "substitute_best_equal": {
                    "condition": "WHERE",
                    "resulting_condition": "t1.a = 1"
                  }
                },
                {
                  "attaching_conditions_to_tables": {
                    "attached_conditions_computation": [],
                    "attached_conditions_summary": [
                      {
                        "table": "t1",
                        "attached": "t1.a = 1"
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "<derived2>",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "<derived2>",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "<derived2>",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 2,
                    "cost": 2,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 2,
                "cost_for_plan": 2.4,
                "estimated_join_cardinality": 2
              }
            ]
          },
          {
            "best_join_order": ["<derived2>"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "<derived2>",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": [
          {
            "join_execution": {
              "select_id": 2,
              "steps": []
            }
          }
        ]
      }
    }
  ]
}	0	0
drop table t1,t2;
drop view v1,v2;
drop function f1;
create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,1),(3,2),(4,3),
(5,3),(6,3),(7,3),(8,3),(9,3);
create table t2(a int, b int);
insert into t2 values (0,0),(1,1),(2,1),(3,2),(4,3),
(5,3),(6,3),(7,3),(8,3),(9,3);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
ANALYZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
create view v1 as select a from t1 group by b;
create view v2 as select a from t2;
# Mergeable view
explain select * from v2 ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from v2	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "view": {
              "table": "v2",
              "select_id": 2,
              "algorithm": "merged"
            }
          },
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select t2.a AS a from t2"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t2.a AS a from v2"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "t2",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t2",
                "table_scan": {
                  "rows": 10,
                  "cost": 2.021972656
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 2.021972656,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 10,
                    "cost": 2.021972656,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 10,
                "cost_for_plan": 4.021972656,
                "estimated_join_cardinality": 10
              }
            ]
          },
          {
            "best_join_order": ["t2"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t2",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
# Non-Mergeable view
explain select * from v1 ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	10	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from v1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "view": {
              "table": "v1",
              "select_id": 2,
              "algorithm": "materialized"
            }
          },
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select t1.a AS a from t1 group by t1.b"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select v1.a AS a from v1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "join_optimization": {
              "select_id": 2,
              "steps": [
                {
                  "table_dependencies": [
                    {
                      "table": "t1",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": []
                    }
                  ]
                },
                {
                  "rows_estimation": [
                    {
                      "table": "t1",
                      "table_scan": {
                        "rows": 10,
                        "cost": 2.021972656
                      }
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t1",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 10,
                            "cost": 2.021972656,
                            "chosen": true,
                            "use_tmp_table": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 10,
                          "cost": 2.021972656,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 10,
                      "cost_for_plan": 4.021972656,
                      "cost_for_sorting": 10,
                      "estimated_join_cardinality": 10
                    }
                  ]
                },
                {
                  "best_join_order": ["t1"]
                },
                {
                  "attaching_conditions_to_tables": {
                    "attached_conditions_computation": [],
                    "attached_conditions_summary": [
                      {
                        "table": "t1",
                        "attached": null
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "<derived2>",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "<derived2>",
                "table_scan": {
                  "rows": 10,
                  "cost": 10
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "<derived2>",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 10,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 10,
                    "cost": 10,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 10,
                "cost_for_plan": 12,
                "estimated_join_cardinality": 10
              }
            ]
          },
          {
            "best_join_order": ["<derived2>"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "<derived2>",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": [
          {
            "join_execution": {
              "select_id": 2,
              "steps": []
            }
          }
        ]
      }
    }
  ]
}	0	0
drop table t1,t2;
drop view v1,v2;
#
# print ref-keyues array
#
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, 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;
create table t2(a int, b int, c int , key(a));
insert into t2 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	Table is already up to date
analyze table t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	Table is already up to date
explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	100	Using where
1	SIMPLE	t2	ref	a	a	5	test.t1.b	1	Using where
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t2.a AS a,t2.b AS b,t2.c AS c from t1 join t2 where t1.a = t2.b + 2 and t2.a = t1.b"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.a = t2.b + 2 and t2.a = t1.b",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "t1.a = t2.b + 2 and multiple equal(t2.a, t1.b)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t2",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t1",
                "field": "a",
                "equals": "t2.b + 2",
                "null_rejecting": true
              },
              {
                "table": "t2",
                "field": "a",
                "equals": "t1.b",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 100,
                  "cost": 2.317382812
                }
              },
              {
                "table": "t2",
                "table_scan": {
                  "rows": 100,
                  "cost": 2.317382812
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 100,
                      "cost": 2.317382812,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 100,
                    "cost": 2.317382812,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 100,
                "cost_for_plan": 22.31738281,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t1"],
                    "table": "t2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "used_range_estimates": false,
                          "reason": "not available",
                          "rows": 1,
                          "cost": 200.0585794,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "resulting_rows": 100,
                          "cost": 2.317382812,
                          "chosen": false
                        }
                      ],
                      "chosen_access_method": {
                        "type": "ref",
                        "records": 1,
                        "cost": 200.0585794,
                        "uses_join_buffering": false
                      }
                    },
                    "rows_for_plan": 100,
                    "cost_for_plan": 242.3759623,
                    "estimated_join_cardinality": 100
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "t2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 100,
                      "cost": 2.317382812,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 100,
                    "cost": 2.317382812,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 100,
                "cost_for_plan": 22.31738281,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t2"],
                    "table": "t1",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "used_range_estimates": false,
                          "reason": "not available",
                          "rows": 1,
                          "cost": 200.0585794,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "resulting_rows": 100,
                          "cost": 2.317382812,
                          "chosen": false
                        }
                      ],
                      "chosen_access_method": {
                        "type": "ref",
                        "records": 1,
                        "cost": 200.0585794,
                        "uses_join_buffering": false
                      }
                    },
                    "rows_for_plan": 100,
                    "cost_for_plan": 242.3759623,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": ["t1", "t2"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t2.a = t1.b and t1.a = t2.b + 2"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": "t1.b is not null"
                },
                {
                  "table": "t2",
                  "attached": "t1.a = t2.b + 2"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t1,t2,t0;
#
# group_by min max optimization
#
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a));
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT DISTINCT a FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	4	NULL	5	Using index for group-by
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT DISTINCT a FROM t1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select distinct t1.a AS a from t1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 65536,
                    "cost": 13255.2
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "a",
                      "usable": true,
                      "key_parts": ["a"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "a",
                    "cost": 13377.39141,
                    "chosen": false,
                    "cause": "cost"
                  },
                  "group_index_range": {
                    "distinct_query": true,
                    "potential_group_range_indexes": [
                      {
                        "index": "a",
                        "covering": true,
                        "rows": 5,
                        "cost": 6.25
                      }
                    ]
                  },
                  "best_group_range_summary": {
                    "type": "index_group",
                    "index": "a",
                    "min_max_arg": null,
                    "min_aggregate": false,
                    "max_aggregate": false,
                    "distinct_aggregate": false,
                    "rows": 5,
                    "cost": 6.25,
                    "key_parts_used_for_access": ["a"],
                    "ranges": [],
                    "chosen": true
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "index_group",
                      "index": "a",
                      "min_max_arg": null,
                      "min_aggregate": false,
                      "max_aggregate": false,
                      "distinct_aggregate": false,
                      "rows": 5,
                      "cost": 6.25,
                      "key_parts_used_for_access": ["a"],
                      "ranges": []
                    },
                    "rows_for_plan": 5,
                    "cost_for_plan": 6.25,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "index_merge",
                      "resulting_rows": 5,
                      "cost": 6.25,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "index_merge",
                    "records": 5,
                    "cost": 6.25,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 5,
                "cost_for_plan": 7.25,
                "estimated_join_cardinality": 5
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t1;
# 
# With group by , where clause and MIN/MAX function
# 
CREATE TABLE t1 (a INT, b INT, c int, d int,  KEY(a,b,c,d));
INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (1,0,1,1), (3,2,3,3), (4,5,4,4);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	a	20	NULL	8	Using where; Using index for group-by
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select min(t1.d) AS `MIN(d)` from t1 where t1.b = 2 and t1.c = 3 group by t1.a"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.b = 2 and t1.c = 3",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(2, t1.b) and multiple equal(3, t1.c)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 7,
                    "cost": 5.429052734
                  },
                  "potential_range_indexes": [
                    {
                      "index": "a",
                      "usable": true,
                      "key_parts": ["a", "b", "c", "d"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "a",
                    "cost": 2.409226263,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "potential_group_range_indexes": [
                      {
                        "index": "a",
                        "covering": true,
                        "ranges": ["(2,3) <= (b,c) <= (2,3)"],
                        "rows": 8,
                        "cost": 2.2
                      }
                    ]
                  },
                  "best_group_range_summary": {
                    "type": "index_group",
                    "index": "a",
                    "min_max_arg": "d",
                    "min_aggregate": true,
                    "max_aggregate": false,
                    "distinct_aggregate": false,
                    "rows": 8,
                    "cost": 2.2,
                    "key_parts_used_for_access": ["a", "b", "c"],
                    "ranges": ["(2,3) <= (b,c) <= (2,3)"],
                    "chosen": true
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "index_group",
                      "index": "a",
                      "min_max_arg": "d",
                      "min_aggregate": true,
                      "max_aggregate": false,
                      "distinct_aggregate": false,
                      "rows": 8,
                      "cost": 2.2,
                      "key_parts_used_for_access": ["a", "b", "c"],
                      "ranges": ["(2,3) <= (b,c) <= (2,3)"]
                    },
                    "rows_for_plan": 8,
                    "cost_for_plan": 2.2,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "index_merge",
                      "resulting_rows": 8,
                      "cost": 2.2,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "index_merge",
                    "records": 8,
                    "cost": 2.2,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 8,
                "cost_for_plan": 3.8,
                "cost_for_sorting": 8,
                "estimated_join_cardinality": 8
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.b = 2 and t1.c = 3"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": "t1.b = 2 and t1.c = 3"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a));
INSERT INTO t1 values (1,'2001-01-01'),(1,'2001-01-02'),
(1,'2001-01-03'),(1,'2001-01-04'),
(2,'2001-01-01'),(2,'2001-01-02'),
(2,'2001-01-03'),(2,'2001-01-04'),
(3,'2001-01-01'),(3,'2001-01-02'),
(3,'2001-01-03'),(3,'2001-01-04'),
(4,'2001-01-01'),(4,'2001-01-02'),
(4,'2001-01-03'),(4,'2001-01-04');
set optimizer_trace='enabled=on';
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	8	NULL	9	Using where; Using index for group-by
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.`id` AS `id`,min(t1.a) AS `MIN(a)`,max(t1.a) AS `MAX(a)` from t1 where t1.a >= 20010104e0 group by t1.`id`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.a >= 20010104e0",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "t1.a >= 20010104e0"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "t1.a >= 20010104e0"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "t1.a >= 20010104e0"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 16,
                    "cost": 7.23125
                  },
                  "potential_range_indexes": [
                    {
                      "index": "id",
                      "usable": true,
                      "key_parts": ["id", "a"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "id",
                    "cost": 4.21171589,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "potential_group_range_indexes": [
                      {
                        "index": "id",
                        "covering": true,
                        "ranges": ["(2001-01-04) <= (a)"],
                        "rows": 9,
                        "cost": 2.35
                      }
                    ]
                  },
                  "best_group_range_summary": {
                    "type": "index_group",
                    "index": "id",
                    "min_max_arg": "a",
                    "min_aggregate": true,
                    "max_aggregate": true,
                    "distinct_aggregate": false,
                    "rows": 9,
                    "cost": 2.35,
                    "key_parts_used_for_access": ["id"],
                    "ranges": ["(2001-01-04) <= (a)"],
                    "chosen": true
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "index_group",
                      "index": "id",
                      "min_max_arg": "a",
                      "min_aggregate": true,
                      "max_aggregate": true,
                      "distinct_aggregate": false,
                      "rows": 9,
                      "cost": 2.35,
                      "key_parts_used_for_access": ["id"],
                      "ranges": ["(2001-01-04) <= (a)"]
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 2.35,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "index_merge",
                      "resulting_rows": 9,
                      "cost": 2.35,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "index_merge",
                    "records": 9,
                    "cost": 2.35,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 4.15,
                "cost_for_sorting": 9,
                "estimated_join_cardinality": 9
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.a >= 20010104e0"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": "t1.a >= 20010104e0"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	id	8	NULL	9	Using where; Using index for group-by
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.`id` AS `id`,t1.a AS a from t1 where t1.a = 20010104e0 group by t1.`id`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.a = 20010104e0",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "t1.a = 20010104e0"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "t1.a = 20010104e0"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "t1.a = 20010104e0"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 16,
                    "cost": 7.23125
                  },
                  "potential_range_indexes": [
                    {
                      "index": "id",
                      "usable": true,
                      "key_parts": ["id", "a"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "id",
                    "cost": 4.21171589,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "potential_group_range_indexes": [
                      {
                        "index": "id",
                        "covering": true,
                        "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
                        "rows": 9,
                        "cost": 2.35
                      }
                    ]
                  },
                  "best_group_range_summary": {
                    "type": "index_group",
                    "index": "id",
                    "min_max_arg": null,
                    "min_aggregate": false,
                    "max_aggregate": false,
                    "distinct_aggregate": false,
                    "rows": 9,
                    "cost": 2.35,
                    "key_parts_used_for_access": ["id", "a"],
                    "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"],
                    "chosen": true
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "index_group",
                      "index": "id",
                      "min_max_arg": null,
                      "min_aggregate": false,
                      "max_aggregate": false,
                      "distinct_aggregate": false,
                      "rows": 9,
                      "cost": 2.35,
                      "key_parts_used_for_access": ["id", "a"],
                      "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"]
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 2.35,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "index_merge",
                      "resulting_rows": 9,
                      "cost": 2.35,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "index_merge",
                    "records": 9,
                    "cost": 2.35,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 4.15,
                "cost_for_sorting": 9,
                "estimated_join_cardinality": 9
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.a = 20010104e0"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": "t1.a = 20010104e0"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t1;
#
# Late ORDER BY optimization
#
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k(a int primary key);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
create table t1  (
pk int not null,
a int,
b int,
c int,
filler char(100),
KEY a_a(c),
KEY a_c(a,c),
KEY a_b(a,b)
);
insert into t1
select a, a,a,a, 'filler-dataaa' from test.one_k;
update t1 set a=1 where pk between 0 and 180;
update t1 set b=2 where pk between 0 and 20;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
set optimizer_trace='enabled=on';
explain  select * from t1 where a=1 and b=2 order by c limit 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a_c,a_b	a_c	5	NULL	180	Using where
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain  select * from t1 where a=1 and b=2 order by c limit 1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b,t1.c AS c,t1.filler AS filler from t1 where t1.a = 1 and t1.b = 2 order by t1.c limit 1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.a = 1 and t1.b = 2",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(1, t1.a) and multiple equal(2, t1.b)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t1",
                "field": "a",
                "equals": "1",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "a",
                "equals": "1",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "b",
                "equals": "2",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,
                    "cost": 232.5644531
                  },
                  "potential_range_indexes": [
                    {
                      "index": "a_a",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "a_c",
                      "usable": true,
                      "key_parts": ["a", "c"]
                    },
                    {
                      "index": "a_b",
                      "usable": true,
                      "key_parts": ["a", "b"]
                    }
                  ],
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a_c",
                        "ranges": ["(1) <= (a) <= (1)"],
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 180,
                        "cost": 216.2943776,
                        "chosen": true
                      },
                      {
                        "index": "a_b",
                        "ranges": ["(1,2) <= (a,b) <= (1,2)"],
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 21,
                        "cost": 25.36242739,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "chosen": false,
                    "cause": "no group by or distinct"
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "a_b",
                      "rows": 21,
                      "ranges": ["(1,2) <= (a,b) <= (1,2)"]
                    },
                    "rows_for_plan": 21,
                    "cost_for_plan": 25.36242739,
                    "chosen": true
                  }
                }
              },
              {
                "table": "t1",
                "rowid_filters": [
                  {
                    "key": "a_b",
                    "build_cost": 0.886777098,
                    "rows": 21
                  },
                  {
                    "key": "a_c",
                    "build_cost": 10.52169992,
                    "rows": 180
                  }
                ]
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "a_b",
                    "selectivity_from_index": 0.021
                  }
                ],
                "selectivity_for_columns": [
                  {
                    "column_name": "a",
                    "ranges": ["1 <= a <= 1"],
                    "selectivity_from_histogram": 0.181
                  },
                  {
                    "column_name": "b",
                    "ranges": ["2 <= b <= 2"],
                    "selectivity_from_histogram": 0.021
                  }
                ],
                "cond_selectivity": 0.021
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "a_c",
                      "used_range_estimates": true,
                      "rows": 180,
                      "cost": 180.2743776,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "a_b",
                      "used_range_estimates": true,
                      "rows": 21,
                      "cost": 21.14242739,
                      "chosen": true
                    },
                    {
                      "type": "scan",
                      "chosen": false,
                      "cause": "cost"
                    }
                  ],
                  "chosen_access_method": {
                    "type": "ref",
                    "records": 21,
                    "cost": 21.14242739,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 21,
                "cost_for_plan": 25.34242739,
                "estimated_join_cardinality": 21
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.a = 1 and t1.b = 2"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "fanout": 1,
              "read_time": 21.14342739,
              "table": "t1",
              "rows_estimation": 21,
              "possible_keys": [
                {
                  "index": "a_a",
                  "can_resolve_order": true,
                  "updated_limit": 47,
                  "index_scan_time": 47,
                  "usable": false,
                  "cause": "cost"
                },
                {
                  "index": "a_c",
                  "can_resolve_order": true,
                  "updated_limit": 47,
                  "range_scan_time": 4.331020747,
                  "index_scan_time": 4.331020747,
                  "records": 180,
                  "chosen": true
                },
                {
                  "index": "a_b",
                  "can_resolve_order": false,
                  "cause": "not usable index for the query"
                }
              ]
            }
          },
          {
            "table": "t1",
            "range_analysis": {
              "table_scan": {
                "rows": 1000,
                "cost": 1.79769e308
              },
              "potential_range_indexes": [
                {
                  "index": "a_a",
                  "usable": false,
                  "cause": "not applicable"
                },
                {
                  "index": "a_c",
                  "usable": true,
                  "key_parts": ["a", "c"]
                },
                {
                  "index": "a_b",
                  "usable": false,
                  "cause": "not applicable"
                }
              ],
              "setup_range_conditions": [],
              "analyzing_range_alternatives": {
                "range_scan_alternatives": [
                  {
                    "index": "a_c",
                    "ranges": ["(1) <= (a) <= (1)"],
                    "rowid_ordered": false,
                    "using_mrr": false,
                    "index_only": false,
                    "rows": 180,
                    "cost": 216.2943776,
                    "chosen": true
                  }
                ],
                "analyzing_roworder_intersect": {
                  "cause": "too few roworder scans"
                },
                "analyzing_index_merge_union": []
              },
              "group_index_range": {
                "chosen": false,
                "cause": "no group by or distinct"
              },
              "chosen_range_access_summary": {
                "range_access_plan": {
                  "type": "range_scan",
                  "index": "a_c",
                  "rows": 180,
                  "ranges": ["(1) <= (a) <= (1)"]
                },
                "rows_for_plan": 180,
                "cost_for_plan": 216.2943776,
                "chosen": true
              }
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t1,ten,one_k;
#
# TABLE ELIMINATION
#
create table t1 (a int);
insert into t1 values (0),(1),(2),(3);
create table t0 as select * from t1;
create table t2 (a int primary key, b int)
as select a, a as b from t1 where a in (1,2);
create table t3 (a int primary key, b int)
as select a, a as b from t1 where a in (1,3);
set optimizer_trace='enabled=on';
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
analyze table t2;
Table	Op	Msg_type	Msg_text
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
analyze table t3;
Table	Op	Msg_type	Msg_text
test.t3	analyze	status	Engine-independent statistics collected
test.t3	analyze	status	OK
# table t2 should be eliminated
explain
select t1.a from t1 left join t2 on t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain
select t1.a from t1 left join t2 on t1.a=t2.a	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.a AS a from (t1 left join t2 on(t1.a = t2.a))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "build_equal_items": {
              "condition": "ON expr",
              "attached_to": "t2",
              "resulting_condition": "multiple equal(t1.a, t2.a)"
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t2",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": ["0"]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t2",
                "field": "a",
                "equals": "t1.a",
                "null_rejecting": true
              }
            ]
          },
          {
            "eliminated_tables": ["t2"]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 4,
                  "cost": 2.006835938
                }
              },
              {
                "table": "t2",
                "rows": 1,
                "cost": 1,
                "table_type": "const"
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": ["t2"],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.006835938,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 4,
                    "cost": 2.006835938,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 4,
                "cost_for_plan": 2.806835937,
                "estimated_join_cardinality": 4
              }
            ]
          },
          {
            "best_join_order": ["t2", "t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "1"
            }
          },
          {
            "condition_on_constant_tables": "1",
            "computing_condition": []
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
# no tables should be eliminated
explain select * from t1 left join t2 on t2.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using where
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from t1 left join t2 on t2.a=t1.a	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.a AS a,t2.a AS a,t2.b AS b from (t1 left join t2 on(t2.a = t1.a))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "build_equal_items": {
              "condition": "ON expr",
              "attached_to": "t2",
              "resulting_condition": "multiple equal(t2.a, t1.a)"
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t2",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": ["0"]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t2",
                "field": "a",
                "equals": "t1.a",
                "null_rejecting": true
              }
            ]
          },
          {
            "eliminated_tables": []
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 4,
                  "cost": 2.006835938
                }
              },
              {
                "table": "t2",
                "table_scan": {
                  "rows": 2,
                  "cost": 2.004394531
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.006835938,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 4,
                    "cost": 2.006835938,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 4,
                "cost_for_plan": 2.806835937,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t1"],
                    "table": "t2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 4,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "resulting_rows": 2,
                          "cost": 8.017578125,
                          "chosen": false
                        }
                      ],
                      "chosen_access_method": {
                        "type": "eq_ref",
                        "records": 1,
                        "cost": 4,
                        "uses_join_buffering": false
                      }
                    },
                    "rows_for_plan": 4,
                    "cost_for_plan": 7.606835937,
                    "estimated_join_cardinality": 4
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": ["t1", "t2"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "1"
            }
          },
          {
            "substitute_best_equal": {
              "condition": "ON expr",
              "attached_to": "t2",
              "resulting_condition": "t2.a = t1.a"
            }
          },
          {
            "condition_on_constant_tables": "1",
            "computing_condition": []
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                },
                {
                  "table": "t2",
                  "attached": "trigcond(trigcond(t1.a is not null))"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
# multiple tables are eliminated
explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.a AS a from (t1 left join (t2 join t3 on(t2.b = t3.b)) on(t2.a = t1.a and t3.a = t1.a))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "build_equal_items": {
              "condition": "ON expr",
              "attached_to": "t3",
              "resulting_condition": "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)"
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t2",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": ["0"]
              },
              {
                "table": "t3",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": ["0"]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t2",
                "field": "a",
                "equals": "t1.a",
                "null_rejecting": true
              },
              {
                "table": "t2",
                "field": "a",
                "equals": "t3.a",
                "null_rejecting": true
              },
              {
                "table": "t3",
                "field": "a",
                "equals": "t2.a",
                "null_rejecting": true
              },
              {
                "table": "t3",
                "field": "a",
                "equals": "t1.a",
                "null_rejecting": true
              }
            ]
          },
          {
            "eliminated_tables": ["t3", "t2"]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 4,
                  "cost": 2.006835938
                }
              },
              {
                "table": "t2",
                "rows": 1,
                "cost": 1,
                "table_type": "const"
              },
              {
                "table": "t3",
                "rows": 1,
                "cost": 1,
                "table_type": "const"
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": ["t3", "t2"],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.006835938,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 4,
                    "cost": 2.006835938,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 4,
                "cost_for_plan": 2.806835937,
                "estimated_join_cardinality": 4
              }
            ]
          },
          {
            "best_join_order": ["t3", "t2", "t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "1"
            }
          },
          {
            "condition_on_constant_tables": "1",
            "computing_condition": []
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t0, t1, t2, t3;
#
# IN subquery to sem-join is traced
#
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);
insert into t1 values (0,0),(1,1),(2,2);
create table t2 as select * from t1;
create table t11(a int, b int);
create table t10 (pk int, a int);
insert into t10 select a,a from t0;
create table t12 like t10;
insert into t12 select * from t10;
analyze table t1,t10;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t10	analyze	status	Engine-independent statistics collected
test.t10	analyze	status	OK
set optimizer_trace='enabled=on';
explain extended select * from t1 where a in (select pk from t10);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
2	MATERIALIZED	t10	ALL	NULL	NULL	NULL	NULL	10	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10`) where 1
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain extended select * from t1 where a in (select pk from t10)	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "materialization",
                    "sjm_scan_allowed": true,
                    "possible": true
                  }
                },
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  }
                },
                {
                  "expanded_query": "/* select#2 */ select t10.pk from t10"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "transformation": {
              "select_id": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }
          },
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "1 and t1.a = t10.pk",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "1 and multiple equal(t1.a, t10.pk)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "1 and multiple equal(t1.a, t10.pk)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(t1.a, t10.pk)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t10",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.006591797
                }
              },
              {
                "table": "t10",
                "table_scan": {
                  "rows": 10,
                  "cost": 2.021972656
                }
              }
            ]
          },
          {
            "semijoin_table_pullout": {
              "pulled_out_tables": []
            }
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": [
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t10",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 10,
                            "cost": 2.021972656,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 10,
                          "cost": 2.021972656,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 10,
                      "cost_for_plan": 4.021972656,
                      "estimated_join_cardinality": 10
                    }
                  ]
                }
              ]
            }
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.006591797,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.006591797,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.606591797,
                "semijoin_strategy_choice": [],
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t1"],
                    "table": "t10",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 10,
                          "cost": 2.021972656,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 10,
                        "cost": 2.021972656,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 30,
                    "cost_for_plan": 10.62856445,
                    "semijoin_strategy_choice": [
                      {
                        "strategy": "FirstMatch",
                        "records": 3,
                        "read_time": 10.62856445
                      },
                      {
                        "strategy": "SJ-Materialization",
                        "records": 3,
                        "read_time": 5.278564453
                      },
                      {
                        "strategy": "DuplicateWeedout",
                        "records": 3,
                        "read_time": 27.12856445
                      },
                      {
                        "chosen_strategy": "SJ-Materialization"
                      }
                    ],
                    "estimated_join_cardinality": 3
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "t10",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 2.021972656,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 10,
                    "cost": 2.021972656,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 10,
                "cost_for_plan": 4.021972656,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              }
            ]
          },
          {
            "fix_semijoin_strategies_for_picked_join_order": [
              {
                "semi_join_strategy": "SJ-Materialization",
                "join_order": [
                  {
                    "table": "t10"
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": ["t1", "<subquery2>"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "1"
            }
          },
          {
            "condition_on_constant_tables": "1",
            "computing_condition": []
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                },
                {
                  "table": "t10",
                  "attached": null
                },
                {
                  "table": "<subquery2>",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t0,t1,t11,t10,t12,t2;
#
# Selectivities for columns and indexes.
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
pk int,
a int,
b int, 
key pk(pk),
key pk_a(pk,a),
key pk_a_b(pk,a,b));
insert into t1 select a,a,a from t0;
ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES ();
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	Table is already up to date
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
set @save_use_stat_tables= @@use_stat_tables;
set @@optimizer_use_condition_selectivity=4;
set @@use_stat_tables= PREFERABLY;
set optimizer_trace='enabled=on';
explain select * from t1 where pk = 2 and a=5 and b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	pk,pk_a,pk_a_b	pk_a_b	15	const,const,const	1	Using index
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from t1 where pk = 2 and a=5 and b=1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select t1.pk AS pk,t1.a AS a,t1.b AS b from t1 where t1.pk = 2 and t1.a = 5 and t1.b = 1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(2, t1.pk) and multiple equal(5, t1.a) and multiple equal(1, t1.b)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t1",
                "field": "pk",
                "equals": "2",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "pk",
                "equals": "2",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "a",
                "equals": "5",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "pk",
                "equals": "2",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "a",
                "equals": "5",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "b",
                "equals": "1",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10,
                    "cost": 6.031738281
                  },
                  "potential_range_indexes": [
                    {
                      "index": "pk",
                      "usable": true,
                      "key_parts": ["pk"]
                    },
                    {
                      "index": "pk_a",
                      "usable": true,
                      "key_parts": ["pk", "a"]
                    },
                    {
                      "index": "pk_a_b",
                      "usable": true,
                      "key_parts": ["pk", "a", "b"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "pk_a_b",
                    "cost": 3.010739566,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "pk",
                        "ranges": ["(2) <= (pk) <= (2)"],
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 1.345585794,
                        "chosen": true
                      },
                      {
                        "index": "pk_a",
                        "ranges": ["(2,5) <= (pk,a) <= (2,5)"],
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 1.345829876,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "pk_a_b",
                        "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"],
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 0.346073957,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "intersecting_indexes": [
                        {
                          "index": "pk",
                          "index_scan_cost": 1.000585794,
                          "cumulated_index_scan_cost": 1.000585794,
                          "disk_sweep_cost": 0.90078125,
                          "cumulative_total_cost": 1.901367044,
                          "usable": true,
                          "matching_rows_now": 1,
                          "intersect_covering_with_this_index": false,
                          "chosen": true
                        },
                        {
                          "index": "pk_a",
                          "usable": false,
                          "cause": "does not reduce cost of intersect"
                        },
                        {
                          "index": "pk_a_b",
                          "usable": false,
                          "cause": "does not reduce cost of intersect"
                        }
                      ],
                      "clustered_pk": {
                        "clustered_pk_added_to_intersect": false,
                        "cause": "no clustered pk index"
                      },
                      "chosen": false,
                      "cause": "cost"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "chosen": false,
                    "cause": "no group by or distinct"
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "pk_a_b",
                      "rows": 1,
                      "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"]
                    },
                    "rows_for_plan": 1,
                    "cost_for_plan": 0.346073957,
                    "chosen": true
                  }
                }
              },
              {
                "table": "t1",
                "rowid_filters": [
                  {
                    "key": "pk",
                    "build_cost": 0.130585794,
                    "rows": 1
                  },
                  {
                    "key": "pk_a",
                    "build_cost": 0.130829876,
                    "rows": 1
                  },
                  {
                    "key": "pk_a_b",
                    "build_cost": 0.131073957,
                    "rows": 1
                  }
                ]
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "pk_a_b",
                    "selectivity_from_index": 0.1
                  }
                ],
                "selectivity_for_columns": [
                  {
                    "column_name": "a",
                    "ranges": ["5 <= a <= 5"],
                    "selectivity_from_histogram": 0.1
                  },
                  {
                    "column_name": "b",
                    "ranges": ["1 <= b <= 1"],
                    "selectivity_from_histogram": 0.1
                  }
                ],
                "cond_selectivity": 0.1
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "pk",
                      "used_range_estimates": true,
                      "rows": 1,
                      "cost": 1.125585794,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "pk_a",
                      "used_range_estimates": true,
                      "rows": 1,
                      "cost": 1.125829876,
                      "chosen": false,
                      "cause": "cost"
                    },
                    {
                      "access_type": "ref",
                      "index": "pk_a_b",
                      "used_range_estimates": true,
                      "rows": 1,
                      "cost": 0.126073957,
                      "chosen": true
                    },
                    {
                      "type": "scan",
                      "chosen": false,
                      "cause": "cost"
                    }
                  ],
                  "chosen_access_method": {
                    "type": "ref",
                    "records": 1,
                    "cost": 0.126073957,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 1,
                "cost_for_plan": 0.326073957,
                "estimated_join_cardinality": 1
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
set @@use_stat_tables= @save_use_stat_tables;
drop table t0,t1;
set optimizer_trace="enabled=off";
#
# Tests added to show that sub-statements are not traced
#
create table t1(a int);
insert into t1 values (1),(2),(3),(4);
create table t2(a int);
insert into t2 values (1),(2),(3),(4);
create function f1(a int) returns int
begin
declare a int default 0;
set a= a+ (select count(*) from t2);
return a;
end|
create function f2(a int) returns int
begin
declare a int default 0;
select count(*) from t2 into a;
return a;
end|
Warnings:
Warning	1287	'<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
set optimizer_trace='enabled=on';
select f1(a) from t1;
f1(a)
4
4
4
4
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select f1(a) from t1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select f1(t1.a) AS `f1(a)` from t1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 4,
                  "cost": 2.006835938
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.006835938,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 4,
                    "cost": 2.006835938,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 4,
                "cost_for_plan": 2.806835937,
                "estimated_join_cardinality": 4
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
select f2(a) from t1;
f2(a)
4
4
4
4
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select f2(a) from t1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select f2(t1.a) AS `f2(a)` from t1"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 4,
                  "cost": 2.006835938
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.006835938,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 4,
                    "cost": 2.006835938,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 4,
                "cost_for_plan": 2.806835937,
                "estimated_join_cardinality": 4
              }
            ]
          },
          {
            "best_join_order": ["t1"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table t1,t2;
drop function f1;
drop function f2;
set optimizer_trace='enabled=off';
#
# MDEV-18489: Limit the memory used by the optimizer trace
#
create table t1 (a int);
insert into t1 values (1),(2);
set optimizer_trace='enabled=on';
set @save_optimizer_trace_max_mem_size= @@optimizer_trace_max_mem_size;
select * from t1;
a
1
2
select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
length(trace)
2141
set optimizer_trace_max_mem_size=100;
select * from t1;
a
1
2
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from t1	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
        	2041	0
set optimizer_trace_max_mem_size=0;
select * from t1;
a
1
2
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from t1		2141	0
drop table t1;
set optimizer_trace='enabled=off';
set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size;
#
# MDEV-18527: Optimizer trace for DELETE query shows table:null
#
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t0 (a int, b int);
insert into t0 select a,a from ten;
alter table t0 add key(a);
set optimizer_trace=1;
explain delete from t0 where t0.a<3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	range	a	a	5	NULL	3	Using where
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain delete from t0 where t0.a<3	{
  "steps": [
    {
      "table": "t0",
      "range_analysis": {
        "table_scan": {
          "rows": 10,
          "cost": 6.021972656
        },
        "potential_range_indexes": [
          {
            "index": "a",
            "usable": true,
            "key_parts": ["a"]
          }
        ],
        "setup_range_conditions": [],
        "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            {
              "index": "a",
              "ranges": ["(NULL) < (a) < (3)"],
              "rowid_ordered": false,
              "using_mrr": false,
              "index_only": false,
              "rows": 3,
              "cost": 3.746757383,
              "chosen": true
            }
          ],
          "analyzing_index_merge_union": []
        },
        "group_index_range": {
          "chosen": false,
          "cause": "no join"
        },
        "chosen_range_access_summary": {
          "range_access_plan": {
            "type": "range_scan",
            "index": "a",
            "rows": 3,
            "ranges": ["(NULL) < (a) < (3)"]
          },
          "rows_for_plan": 3,
          "cost_for_plan": 3.746757383,
          "chosen": true
        }
      }
    }
  ]
}	0	0
drop table ten,t0;
set optimizer_trace='enabled=off';
#
# MDEV-18528: Optimizer trace support for multi-table UPDATE and DELETE
#
set optimizer_trace=1;
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t0 (a int, b int);
insert into t0 select a,a from ten;
alter table t0 add key(a);
create table t1 like t0;
insert into t1 select * from t0;
explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	range	a	a	5	NULL	3	Using where
1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "t0.a = t1.a and t1.a < 3",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "t1.a < 3 and multiple equal(t0.a, t1.a)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t0",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "t0",
                "field": "a",
                "equals": "t1.a",
                "null_rejecting": true
              },
              {
                "table": "t1",
                "field": "a",
                "equals": "t0.a",
                "null_rejecting": true
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "t0",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10,
                    "cost": 6.021972656
                  },
                  "potential_range_indexes": [
                    {
                      "index": "a",
                      "usable": true,
                      "key_parts": ["a"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "a",
                    "cost": 3.005857945,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a",
                        "ranges": ["(NULL) < (a) < (3)"],
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3,
                        "cost": 0.746757383,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not single_table"
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "a",
                      "rows": 3,
                      "ranges": ["(NULL) < (a) < (3)"]
                    },
                    "rows_for_plan": 3,
                    "cost_for_plan": 0.746757383,
                    "chosen": true
                  }
                }
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "a",
                    "selectivity_from_index": 0.3
                  }
                ],
                "selectivity_for_columns": [],
                "cond_selectivity": 0.3
              },
              {
                "table": "t1",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10,
                    "cost": 6.021972656
                  },
                  "potential_range_indexes": [
                    {
                      "index": "a",
                      "usable": true,
                      "key_parts": ["a"]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "a",
                    "cost": 3.005857945,
                    "chosen": true
                  },
                  "setup_range_conditions": [],
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a",
                        "ranges": ["(NULL) < (a) < (3)"],
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 3,
                        "cost": 0.746757383,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not single_table"
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "a",
                      "rows": 3,
                      "ranges": ["(NULL) < (a) < (3)"]
                    },
                    "rows_for_plan": 3,
                    "cost_for_plan": 0.746757383,
                    "chosen": true
                  }
                }
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "a",
                    "selectivity_from_index": 0.3
                  }
                ],
                "selectivity_for_columns": [],
                "cond_selectivity": 0.3
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t0",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "resulting_rows": 3,
                      "cost": 0.746757383,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "range",
                    "records": 3,
                    "cost": 0.746757383,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 1.346757383,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t0"],
                    "table": "t1",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "used_range_estimates": false,
                          "reason": "not better than ref estimates",
                          "rows": 1,
                          "cost": 3.001757383,
                          "chosen": true
                        },
                        {
                          "type": "scan",
                          "chosen": false,
                          "cause": "cost"
                        }
                      ],
                      "chosen_access_method": {
                        "type": "ref",
                        "records": 1,
                        "cost": 3.001757383,
                        "uses_join_buffering": false
                      }
                    },
                    "rows_for_plan": 3,
                    "cost_for_plan": 4.948514767,
                    "estimated_join_cardinality": 3
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "resulting_rows": 3,
                      "cost": 0.746757383,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "range",
                    "records": 3,
                    "cost": 0.746757383,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 1.346757383,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t1"],
                    "table": "t0",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rec_per_key_stats_missing": true,
                          "used_range_estimates": false,
                          "reason": "not better than ref estimates",
                          "rows": 2,
                          "cost": 3.003514767,
                          "chosen": true
                        },
                        {
                          "type": "scan",
                          "chosen": false,
                          "cause": "cost"
                        }
                      ],
                      "chosen_access_method": {
                        "type": "ref",
                        "records": 2,
                        "cost": 3.003514767,
                        "uses_join_buffering": false
                      }
                    },
                    "rows_for_plan": 6,
                    "cost_for_plan": 5.55027215,
                    "pruned_by_cost": true
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": ["t0", "t1"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t1.a = t0.a and t0.a < 3"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t0",
                  "attached": "t0.a < 3 and t0.a is not null"
                },
                {
                  "table": "t1",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
drop table ten,t0,t1;
set optimizer_trace='enabled=off';
#
# Merged to Materialized for derived tables
#
set optimizer_trace=1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
explain select * from (select rand() from t1)q;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from (select rand() from t1)q	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "derived": {
              "table": "q",
              "select_id": 2,
              "algorithm": "merged"
            }
          },
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select rand() AS `rand()` from t1"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select rand() AS `rand()` from (/* select#2 */ select rand() AS `rand()` from t1) q"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "derived": {
              "table": "q",
              "select_id": 2,
              "algorithm": "materialized",
              "cause": "Random function in the select"
            }
          },
          {
            "join_optimization": {
              "select_id": 2,
              "steps": [
                {
                  "table_dependencies": [
                    {
                      "table": "t1",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": []
                    }
                  ]
                },
                {
                  "rows_estimation": [
                    {
                      "table": "t1",
                      "table_scan": {
                        "rows": 3,
                        "cost": 2.005126953
                      }
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t1",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 3,
                            "cost": 2.005126953,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 3,
                          "cost": 2.005126953,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 3,
                      "cost_for_plan": 2.605126953,
                      "estimated_join_cardinality": 3
                    }
                  ]
                },
                {
                  "best_join_order": ["t1"]
                },
                {
                  "attaching_conditions_to_tables": {
                    "attached_conditions_computation": [],
                    "attached_conditions_summary": [
                      {
                        "table": "t1",
                        "attached": null
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "<derived2>",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "<derived2>",
                "table_scan": {
                  "rows": 3,
                  "cost": 3
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "<derived2>",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 3,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 3,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 3.6,
                "estimated_join_cardinality": 3
              }
            ]
          },
          {
            "best_join_order": ["<derived2>"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "<derived2>",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": [
          {
            "join_execution": {
              "select_id": 2,
              "steps": []
            }
          }
        ]
      }
    }
  ]
}	0	0
drop table t1;
set optimizer_trace='enabled=off';
#
# Semi-join nest
#
set optimizer_trace=1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
create table t2(a int);
insert into t2 values (1),(2),(3),(1),(2),(3),(1),(2),(3);
set @save_optimizer_switch= @@optimizer_switch;
explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	t_inner_1	ALL	NULL	NULL	NULL	NULL	3	
2	MATERIALIZED	t_inner_2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2)	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "materialization",
                    "sjm_scan_allowed": true,
                    "possible": true
                  }
                },
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  }
                },
                {
                  "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t1.a AS a from t1 where t1.a in (/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2)"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "transformation": {
              "select_id": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }
          },
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "1 and t1.a = t_inner_1.a",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "1 and multiple equal(t1.a, t_inner_1.a)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "1 and multiple equal(t1.a, t_inner_1.a)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(t1.a, t_inner_1.a)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_1",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_2",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              },
              {
                "table": "t_inner_1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              },
              {
                "table": "t_inner_2",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              }
            ]
          },
          {
            "semijoin_table_pullout": {
              "pulled_out_tables": []
            }
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": [
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t_inner_1",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 3,
                            "cost": 2.005126953,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 3,
                          "cost": 2.005126953,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 3,
                      "cost_for_plan": 2.605126953,
                      "rest_of_plan": [
                        {
                          "plan_prefix": ["t_inner_1"],
                          "table": "t_inner_2",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "scan",
                                "resulting_rows": 3,
                                "cost": 2.005126953,
                                "chosen": true
                              }
                            ],
                            "chosen_access_method": {
                              "type": "scan",
                              "records": 3,
                              "cost": 2.005126953,
                              "uses_join_buffering": true
                            }
                          },
                          "rows_for_plan": 9,
                          "cost_for_plan": 6.410253906,
                          "estimated_join_cardinality": 9
                        }
                      ]
                    },
                    {
                      "plan_prefix": [],
                      "table": "t_inner_2",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 3,
                            "cost": 2.005126953,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 3,
                          "cost": 2.005126953,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 3,
                      "cost_for_plan": 2.605126953,
                      "pruned_by_heuristic": true
                    }
                  ]
                }
              ]
            }
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t1"],
                    "table": "t_inner_1",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 3,
                          "cost": 2.005126953,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 3,
                        "cost": 2.005126953,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 6.410253906,
                    "semijoin_strategy_choice": [],
                    "rest_of_plan": [
                      {
                        "plan_prefix": ["t1", "t_inner_1"],
                        "table": "t_inner_2",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 3,
                              "cost": 2.005126953,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 3,
                            "cost": 2.005126953,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 27,
                        "cost_for_plan": 13.81538086,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "FirstMatch",
                            "records": 3,
                            "read_time": 33.86665039
                          },
                          {
                            "strategy": "SJ-Materialization",
                            "records": 3,
                            "read_time": 7.215380859
                          },
                          {
                            "strategy": "DuplicateWeedout",
                            "records": 3,
                            "read_time": 18.31538086
                          },
                          {
                            "chosen_strategy": "SJ-Materialization"
                          }
                        ],
                        "estimated_join_cardinality": 3
                      }
                    ]
                  },
                  {
                    "plan_prefix": ["t1"],
                    "table": "t_inner_2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 3,
                          "cost": 2.005126953,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 3,
                        "cost": 2.005126953,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 6.410253906,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "t_inner_1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              }
            ]
          },
          {
            "fix_semijoin_strategies_for_picked_join_order": [
              {
                "semi_join_strategy": "SJ-Materialization",
                "join_order": [
                  {
                    "table": "t_inner_1"
                  },
                  {
                    "table": "t_inner_2"
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": ["t1", "<subquery2>"]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "1"
            }
          },
          {
            "condition_on_constant_tables": "1",
            "computing_condition": []
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t1",
                  "attached": null
                },
                {
                  "table": "t_inner_1",
                  "attached": null
                },
                {
                  "table": "t_inner_2",
                  "attached": null
                },
                {
                  "table": "<subquery2>",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
# with Firstmatch, mostly for tracing fix_semijoin_strategies_for_picked_join_order
set optimizer_switch='materialization=off';
explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t_outer_1	ALL	NULL	NULL	NULL	NULL	3	
1	PRIMARY	t_inner_1	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary; Using join buffer (flat, BNL join)
1	PRIMARY	t_inner_2	ALL	NULL	NULL	NULL	NULL	9	End temporary; Using join buffer (incremental, BNL join)
1	PRIMARY	t_inner_4	ALL	NULL	NULL	NULL	NULL	3	Start temporary; Using join buffer (incremental, BNL join)
1	PRIMARY	t_outer_2	ALL	NULL	NULL	NULL	NULL	9	Using join buffer (incremental, BNL join)
1	PRIMARY	t_inner_3	ALL	NULL	NULL	NULL	NULL	9	Using where; End temporary; Using join buffer (incremental, BNL join)
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "materialization",
                    "sjm_scan_allowed": true,
                    "possible": true
                  }
                },
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  }
                },
                {
                  "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
                }
              ]
            }
          },
          {
            "join_preparation": {
              "select_id": 3,
              "steps": [
                {
                  "transformation": {
                    "select_id": 3,
                    "from": "IN (SELECT)",
                    "to": "materialization",
                    "sjm_scan_allowed": true,
                    "possible": true
                  }
                },
                {
                  "transformation": {
                    "select_id": 3,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  }
                },
                {
                  "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "transformation": {
              "select_id": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }
          },
          {
            "transformation": {
              "select_id": 3,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }
          },
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t_outer_1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t_outer_2",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_2",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_1",
                "row_may_be_null": false,
                "map_bit": 3,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_3",
                "row_may_be_null": false,
                "map_bit": 4,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_4",
                "row_may_be_null": false,
                "map_bit": 5,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t_outer_1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              },
              {
                "table": "t_outer_2",
                "table_scan": {
                  "rows": 9,
                  "cost": 2.015380859
                }
              },
              {
                "table": "t_inner_2",
                "table_scan": {
                  "rows": 9,
                  "cost": 2.015380859
                }
              },
              {
                "table": "t_inner_1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              },
              {
                "table": "t_inner_3",
                "table_scan": {
                  "rows": 9,
                  "cost": 2.015380859
                }
              },
              {
                "table": "t_inner_4",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              }
            ]
          },
          {
            "semijoin_table_pullout": {
              "pulled_out_tables": []
            }
          },
          {
            "semijoin_table_pullout": {
              "pulled_out_tables": []
            }
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": []
            }
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t_outer_1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_1",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 3,
                          "cost": 2.005126953,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 3,
                        "cost": 2.005126953,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 6.410253906,
                    "semijoin_strategy_choice": [],
                    "rest_of_plan": [
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_inner_2",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 9,
                              "cost": 2.015380859,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 9,
                            "cost": 2.015380859,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 81,
                        "cost_for_plan": 24.62563477,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "FirstMatch",
                            "records": 3,
                            "read_time": 44.75893555
                          },
                          {
                            "strategy": "DuplicateWeedout",
                            "records": 3,
                            "read_time": 37.22563477
                          },
                          {
                            "chosen_strategy": "DuplicateWeedout"
                          }
                        ],
                        "rest_of_plan": [
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
                            "table": "t_outer_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 27,
                            "cost_for_plan": 44.64101563,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_4",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 3,
                                      "cost": 2.005126953,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 3,
                                    "cost": 2.005126953,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 62.84614258,
                                "semijoin_strategy_choice": [],
                                "rest_of_plan": [
                                  {
                                    "plan_prefix": [
                                      "t_outer_1",
                                      "t_inner_1",
                                      "t_inner_2",
                                      "t_outer_2",
                                      "t_inner_4"
                                    ],
                                    "table": "t_inner_3",
                                    "best_access_path": {
                                      "considered_access_paths": [
                                        {
                                          "access_type": "scan",
                                          "resulting_rows": 9,
                                          "cost": 2.015380859,
                                          "chosen": true
                                        }
                                      ],
                                      "chosen_access_method": {
                                        "type": "scan",
                                        "records": 9,
                                        "cost": 2.015380859,
                                        "uses_join_buffering": true
                                      }
                                    },
                                    "rows_for_plan": 729,
                                    "cost_for_plan": 210.6615234,
                                    "semijoin_strategy_choice": [
                                      {
                                        "strategy": "FirstMatch",
                                        "records": 27,
                                        "read_time": 424.025293
                                      },
                                      {
                                        "strategy": "DuplicateWeedout",
                                        "records": 27,
                                        "read_time": 324.0615234
                                      },
                                      {
                                        "chosen_strategy": "DuplicateWeedout"
                                      }
                                    ],
                                    "estimated_join_cardinality": 27
                                  }
                                ]
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 243,
                                "cost_for_plan": 95.25639648,
                                "semijoin_strategy_choice": [],
                                "pruned_by_heuristic": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
                            "table": "t_inner_4",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 3,
                                  "cost": 2.005126953,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3,
                                "cost": 2.005126953,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 9,
                            "cost_for_plan": 41.03076172,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_inner_4"
                                ],
                                "table": "t_outer_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 59.24614258,
                                "semijoin_strategy_choice": [],
                                "rest_of_plan": [
                                  {
                                    "plan_prefix": [
                                      "t_outer_1",
                                      "t_inner_1",
                                      "t_inner_2",
                                      "t_inner_4",
                                      "t_outer_2"
                                    ],
                                    "table": "t_inner_3",
                                    "best_access_path": {
                                      "considered_access_paths": [
                                        {
                                          "access_type": "scan",
                                          "resulting_rows": 9,
                                          "cost": 2.015380859,
                                          "chosen": true
                                        }
                                      ],
                                      "chosen_access_method": {
                                        "type": "scan",
                                        "records": 9,
                                        "cost": 2.015380859,
                                        "uses_join_buffering": true
                                      }
                                    },
                                    "rows_for_plan": 729,
                                    "cost_for_plan": 207.0615234,
                                    "semijoin_strategy_choice": [
                                      {
                                        "strategy": "DuplicateWeedout",
                                        "records": 27,
                                        "read_time": 320.4615234
                                      },
                                      {
                                        "chosen_strategy": "DuplicateWeedout"
                                      }
                                    ],
                                    "estimated_join_cardinality": 27
                                  }
                                ]
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_inner_4"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 59.24614258,
                                "semijoin_strategy_choice": [],
                                "pruned_by_heuristic": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
                            "table": "t_inner_3",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 27,
                            "cost_for_plan": 44.64101563,
                            "semijoin_strategy_choice": [],
                            "pruned_by_heuristic": true
                          }
                        ]
                      },
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_outer_2",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 9,
                              "cost": 2.015380859,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 9,
                            "cost": 2.015380859,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 81,
                        "cost_for_plan": 24.62563477,
                        "semijoin_strategy_choice": [],
                        "rest_of_plan": [
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"],
                            "table": "t_inner_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 729,
                            "cost_for_plan": 172.4410156,
                            "semijoin_strategy_choice": [
                              {
                                "strategy": "DuplicateWeedout",
                                "records": 27,
                                "read_time": 285.8410156
                              },
                              {
                                "chosen_strategy": "DuplicateWeedout"
                              }
                            ],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_outer_2",
                                  "t_inner_2"
                                ],
                                "table": "t_inner_4",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 3,
                                      "cost": 2.005126953,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 3,
                                    "cost": 2.005126953,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 304.0461426,
                                "semijoin_strategy_choice": [],
                                "rest_of_plan": [
                                  {
                                    "plan_prefix": [
                                      "t_outer_1",
                                      "t_inner_1",
                                      "t_outer_2",
                                      "t_inner_2",
                                      "t_inner_4"
                                    ],
                                    "table": "t_inner_3",
                                    "best_access_path": {
                                      "considered_access_paths": [
                                        {
                                          "access_type": "scan",
                                          "resulting_rows": 9,
                                          "cost": 2.015380859,
                                          "chosen": true
                                        }
                                      ],
                                      "chosen_access_method": {
                                        "type": "scan",
                                        "records": 9,
                                        "cost": 2.015380859,
                                        "uses_join_buffering": true
                                      }
                                    },
                                    "rows_for_plan": 729,
                                    "cost_for_plan": 451.8615234,
                                    "semijoin_strategy_choice": [
                                      {
                                        "strategy": "DuplicateWeedout",
                                        "records": 27,
                                        "read_time": 565.2615234
                                      },
                                      {
                                        "chosen_strategy": "DuplicateWeedout"
                                      }
                                    ],
                                    "pruned_by_cost": true
                                  }
                                ]
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_outer_2",
                                  "t_inner_2"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 243,
                                "cost_for_plan": 336.4563965,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"],
                            "table": "t_inner_4",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 3,
                                  "cost": 2.005126953,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3,
                                "cost": 2.005126953,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 243,
                            "cost_for_plan": 75.23076172,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_outer_2",
                                  "t_inner_4"
                                ],
                                "table": "t_inner_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 514.6461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_outer_2",
                                  "t_inner_4"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 514.6461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"],
                            "table": "t_inner_3",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 729,
                            "cost_for_plan": 172.4410156,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_outer_2",
                                  "t_inner_3"
                                ],
                                "table": "t_inner_4",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 3,
                                      "cost": 2.005126953,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 3,
                                    "cost": 2.005126953,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 611.8461426,
                                "semijoin_strategy_choice": [
                                  {
                                    "strategy": "FirstMatch",
                                    "records": 81,
                                    "read_time": 2232.809033
                                  },
                                  {
                                    "chosen_strategy": "FirstMatch"
                                  }
                                ],
                                "pruned_by_cost": true
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_outer_2",
                                  "t_inner_3"
                                ],
                                "table": "t_inner_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 6561,
                                "cost_for_plan": 1486.656396,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          }
                        ]
                      },
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_inner_4",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 3,
                              "cost": 2.005126953,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 3,
                            "cost": 2.005126953,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 27,
                        "cost_for_plan": 13.81538086,
                        "semijoin_strategy_choice": [],
                        "rest_of_plan": [
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"],
                            "table": "t_outer_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 243,
                            "cost_for_plan": 64.43076172,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_4",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 503.8461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_4",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 503.8461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"],
                            "table": "t_inner_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 243,
                            "cost_for_plan": 64.43076172,
                            "semijoin_strategy_choice": [],
                            "pruned_by_heuristic": true
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"],
                            "table": "t_inner_3",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 243,
                            "cost_for_plan": 64.43076172,
                            "semijoin_strategy_choice": [],
                            "pruned_by_heuristic": true
                          }
                        ]
                      },
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_inner_3",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 9,
                              "cost": 2.015380859,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 9,
                            "cost": 2.015380859,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 81,
                        "cost_for_plan": 24.62563477,
                        "semijoin_strategy_choice": [],
                        "rest_of_plan": [
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"],
                            "table": "t_outer_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 729,
                            "cost_for_plan": 172.4410156,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_3",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_4",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 3,
                                      "cost": 2.005126953,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 3,
                                    "cost": 2.005126953,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 611.8461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_3",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 6561,
                                "cost_for_plan": 1486.656396,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"],
                            "table": "t_inner_4",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 3,
                                  "cost": 2.005126953,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3,
                                "cost": 2.005126953,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 243,
                            "cost_for_plan": 75.23076172,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_3",
                                  "t_inner_4"
                                ],
                                "table": "t_outer_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 514.6461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_3",
                                  "t_inner_4"
                                ],
                                "table": "t_inner_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 2187,
                                "cost_for_plan": 514.6461426,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"],
                            "table": "t_inner_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 729,
                            "cost_for_plan": 172.4410156,
                            "semijoin_strategy_choice": [],
                            "pruned_by_heuristic": true
                          }
                        ]
                      }
                    ]
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 9,
                          "cost": 2.015380859,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 9,
                        "cost": 2.015380859,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 27,
                    "cost_for_plan": 10.02050781,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_outer_2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 9,
                          "cost": 2.015380859,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 9,
                        "cost": 2.015380859,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 27,
                    "cost_for_plan": 10.02050781,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_4",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 3,
                          "cost": 2.005126953,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 3,
                        "cost": 2.005126953,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 6.410253906,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_3",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 9,
                          "cost": 2.015380859,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 9,
                        "cost": 2.015380859,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 27,
                    "cost_for_plan": 10.02050781,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "t_inner_1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 9,
                      "cost": 2.015380859,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 9,
                    "cost": 2.015380859,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 3.815380859,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_outer_2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 9,
                      "cost": 2.015380859,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 9,
                    "cost": 2.015380859,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 3.815380859,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_4",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_3",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 9,
                      "cost": 2.015380859,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 9,
                    "cost": 2.015380859,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 3.815380859,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              }
            ]
          },
          {
            "fix_semijoin_strategies_for_picked_join_order": [
              {
                "semi_join_strategy": "DuplicateWeedout"
              },
              {
                "semi_join_strategy": "DuplicateWeedout"
              }
            ]
          },
          {
            "best_join_order": [
              "t_outer_1",
              "t_inner_1",
              "t_inner_2",
              "t_inner_4",
              "t_outer_2",
              "t_inner_3"
            ]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a"
            }
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t_outer_1",
                  "attached": null
                },
                {
                  "table": "t_inner_1",
                  "attached": "t_inner_1.a = t_outer_1.a"
                },
                {
                  "table": "t_inner_2",
                  "attached": null
                },
                {
                  "table": "t_inner_4",
                  "attached": null
                },
                {
                  "table": "t_outer_2",
                  "attached": null
                },
                {
                  "table": "t_inner_3",
                  "attached": "t_inner_3.a = t_outer_2.a"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
set optimizer_switch='materialization=on';
explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t_outer_1	ALL	NULL	NULL	NULL	NULL	3	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
1	PRIMARY	t_outer_2	ALL	NULL	NULL	NULL	NULL	9	Using join buffer (flat, BNL join)
1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
2	MATERIALIZED	t_inner_1	ALL	NULL	NULL	NULL	NULL	3	
2	MATERIALIZED	t_inner_2	ALL	NULL	NULL	NULL	NULL	9	Using join buffer (flat, BNL join)
3	MATERIALIZED	t_inner_4	ALL	NULL	NULL	NULL	NULL	3	
3	MATERIALIZED	t_inner_3	ALL	NULL	NULL	NULL	NULL	9	Using join buffer (flat, BNL join)
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4)	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "join_preparation": {
              "select_id": 2,
              "steps": [
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "materialization",
                    "sjm_scan_allowed": true,
                    "possible": true
                  }
                },
                {
                  "transformation": {
                    "select_id": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  }
                },
                {
                  "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1"
                }
              ]
            }
          },
          {
            "join_preparation": {
              "select_id": 3,
              "steps": [
                {
                  "transformation": {
                    "select_id": 3,
                    "from": "IN (SELECT)",
                    "to": "materialization",
                    "sjm_scan_allowed": true,
                    "possible": true
                  }
                },
                {
                  "transformation": {
                    "select_id": 3,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  }
                },
                {
                  "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4"
                }
              ]
            }
          },
          {
            "expanded_query": "/* select#1 */ select t_outer_1.a AS a,t_outer_2.a AS a from t1 t_outer_1 join t2 t_outer_2 where t_outer_1.a in (/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1) and t_outer_2.a in (/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4)"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "transformation": {
              "select_id": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }
          },
          {
            "transformation": {
              "select_id": 3,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "converted_to_semi_join": true
            }
          },
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "1 and 1 and t_outer_1.a = t_inner_1.a and t_outer_2.a = t_inner_3.a",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "1 and 1 and multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(t_outer_1.a, t_inner_1.a) and multiple equal(t_outer_2.a, t_inner_3.a)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "t_outer_1",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "t_outer_2",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_2",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_1",
                "row_may_be_null": false,
                "map_bit": 3,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_3",
                "row_may_be_null": false,
                "map_bit": 4,
                "depends_on_map_bits": []
              },
              {
                "table": "t_inner_4",
                "row_may_be_null": false,
                "map_bit": 5,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": []
          },
          {
            "rows_estimation": [
              {
                "table": "t_outer_1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              },
              {
                "table": "t_outer_2",
                "table_scan": {
                  "rows": 9,
                  "cost": 2.015380859
                }
              },
              {
                "table": "t_inner_2",
                "table_scan": {
                  "rows": 9,
                  "cost": 2.015380859
                }
              },
              {
                "table": "t_inner_1",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              },
              {
                "table": "t_inner_3",
                "table_scan": {
                  "rows": 9,
                  "cost": 2.015380859
                }
              },
              {
                "table": "t_inner_4",
                "table_scan": {
                  "rows": 3,
                  "cost": 2.005126953
                }
              }
            ]
          },
          {
            "semijoin_table_pullout": {
              "pulled_out_tables": []
            }
          },
          {
            "semijoin_table_pullout": {
              "pulled_out_tables": []
            }
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": [
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t_inner_1",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 3,
                            "cost": 2.005126953,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 3,
                          "cost": 2.005126953,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 3,
                      "cost_for_plan": 2.605126953,
                      "rest_of_plan": [
                        {
                          "plan_prefix": ["t_inner_1"],
                          "table": "t_inner_2",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "scan",
                                "resulting_rows": 9,
                                "cost": 2.015380859,
                                "chosen": true
                              }
                            ],
                            "chosen_access_method": {
                              "type": "scan",
                              "records": 9,
                              "cost": 2.015380859,
                              "uses_join_buffering": true
                            }
                          },
                          "rows_for_plan": 27,
                          "cost_for_plan": 10.02050781,
                          "estimated_join_cardinality": 27
                        }
                      ]
                    },
                    {
                      "plan_prefix": [],
                      "table": "t_inner_2",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 9,
                            "cost": 2.015380859,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 9,
                          "cost": 2.015380859,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 9,
                      "cost_for_plan": 3.815380859,
                      "pruned_by_heuristic": true
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "t_inner_4",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 3,
                            "cost": 2.005126953,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 3,
                          "cost": 2.005126953,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 3,
                      "cost_for_plan": 2.605126953,
                      "rest_of_plan": [
                        {
                          "plan_prefix": ["t_inner_4"],
                          "table": "t_inner_3",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "scan",
                                "resulting_rows": 9,
                                "cost": 2.015380859,
                                "chosen": true
                              }
                            ],
                            "chosen_access_method": {
                              "type": "scan",
                              "records": 9,
                              "cost": 2.015380859,
                              "uses_join_buffering": true
                            }
                          },
                          "rows_for_plan": 27,
                          "cost_for_plan": 10.02050781,
                          "estimated_join_cardinality": 27
                        }
                      ]
                    },
                    {
                      "plan_prefix": [],
                      "table": "t_inner_3",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 9,
                            "cost": 2.015380859,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 9,
                          "cost": 2.015380859,
                          "uses_join_buffering": false
                        }
                      },
                      "rows_for_plan": 9,
                      "cost_for_plan": 3.815380859,
                      "pruned_by_heuristic": true
                    }
                  ]
                }
              ]
            }
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "t_outer_1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "rest_of_plan": [
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_1",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 3,
                          "cost": 2.005126953,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 3,
                        "cost": 2.005126953,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 6.410253906,
                    "semijoin_strategy_choice": [],
                    "rest_of_plan": [
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_inner_2",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 9,
                              "cost": 2.015380859,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 9,
                            "cost": 2.015380859,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 81,
                        "cost_for_plan": 24.62563477,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "FirstMatch",
                            "records": 3,
                            "read_time": 44.75893555
                          },
                          {
                            "strategy": "SJ-Materialization",
                            "records": 3,
                            "read_time": 8.125634766
                          },
                          {
                            "strategy": "DuplicateWeedout",
                            "records": 3,
                            "read_time": 37.22563477
                          },
                          {
                            "chosen_strategy": "SJ-Materialization"
                          }
                        ],
                        "rest_of_plan": [
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
                            "table": "t_outer_2",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 27,
                            "cost_for_plan": 15.54101562,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_4",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 3,
                                      "cost": 2.005126953,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 3,
                                    "cost": 2.005126953,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 33.74614258,
                                "semijoin_strategy_choice": [],
                                "rest_of_plan": [
                                  {
                                    "plan_prefix": [
                                      "t_outer_1",
                                      "t_inner_1",
                                      "t_inner_2",
                                      "t_outer_2",
                                      "t_inner_4"
                                    ],
                                    "table": "t_inner_3",
                                    "best_access_path": {
                                      "considered_access_paths": [
                                        {
                                          "access_type": "scan",
                                          "resulting_rows": 9,
                                          "cost": 2.015380859,
                                          "chosen": true
                                        }
                                      ],
                                      "chosen_access_method": {
                                        "type": "scan",
                                        "records": 9,
                                        "cost": 2.015380859,
                                        "uses_join_buffering": true
                                      }
                                    },
                                    "rows_for_plan": 729,
                                    "cost_for_plan": 181.5615234,
                                    "semijoin_strategy_choice": [
                                      {
                                        "strategy": "FirstMatch",
                                        "records": 27,
                                        "read_time": 394.925293
                                      },
                                      {
                                        "strategy": "SJ-Materialization",
                                        "records": 27,
                                        "read_time": 22.26152344
                                      },
                                      {
                                        "strategy": "DuplicateWeedout",
                                        "records": 27,
                                        "read_time": 294.9615234
                                      },
                                      {
                                        "chosen_strategy": "SJ-Materialization"
                                      }
                                    ],
                                    "estimated_join_cardinality": 27
                                  }
                                ]
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_outer_2"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 243,
                                "cost_for_plan": 66.15639648,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
                            "table": "t_inner_4",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 3,
                                  "cost": 2.005126953,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3,
                                "cost": 2.005126953,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 9,
                            "cost_for_plan": 11.93076172,
                            "semijoin_strategy_choice": [],
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_inner_4"
                                ],
                                "table": "t_outer_2",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 30.14614258,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              },
                              {
                                "plan_prefix": [
                                  "t_outer_1",
                                  "t_inner_1",
                                  "t_inner_2",
                                  "t_inner_4"
                                ],
                                "table": "t_inner_3",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "scan",
                                      "resulting_rows": 9,
                                      "cost": 2.015380859,
                                      "chosen": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "records": 9,
                                    "cost": 2.015380859,
                                    "uses_join_buffering": true
                                  }
                                },
                                "rows_for_plan": 81,
                                "cost_for_plan": 30.14614258,
                                "semijoin_strategy_choice": [],
                                "pruned_by_cost": true
                              }
                            ]
                          },
                          {
                            "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"],
                            "table": "t_inner_3",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 9,
                                  "cost": 2.015380859,
                                  "chosen": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 9,
                                "cost": 2.015380859,
                                "uses_join_buffering": true
                              }
                            },
                            "rows_for_plan": 27,
                            "cost_for_plan": 15.54101562,
                            "semijoin_strategy_choice": [],
                            "pruned_by_heuristic": true
                          }
                        ]
                      },
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_outer_2",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 9,
                              "cost": 2.015380859,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 9,
                            "cost": 2.015380859,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 81,
                        "cost_for_plan": 24.62563477,
                        "semijoin_strategy_choice": [],
                        "pruned_by_cost": true
                      },
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_inner_4",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 3,
                              "cost": 2.005126953,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 3,
                            "cost": 2.005126953,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 27,
                        "cost_for_plan": 13.81538086,
                        "semijoin_strategy_choice": [],
                        "pruned_by_heuristic": true
                      },
                      {
                        "plan_prefix": ["t_outer_1", "t_inner_1"],
                        "table": "t_inner_3",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "scan",
                              "resulting_rows": 9,
                              "cost": 2.015380859,
                              "chosen": true
                            }
                          ],
                          "chosen_access_method": {
                            "type": "scan",
                            "records": 9,
                            "cost": 2.015380859,
                            "uses_join_buffering": true
                          }
                        },
                        "rows_for_plan": 81,
                        "cost_for_plan": 24.62563477,
                        "semijoin_strategy_choice": [],
                        "pruned_by_cost": true
                      }
                    ]
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 9,
                          "cost": 2.015380859,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 9,
                        "cost": 2.015380859,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 27,
                    "cost_for_plan": 10.02050781,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_outer_2",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 9,
                          "cost": 2.015380859,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 9,
                        "cost": 2.015380859,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 27,
                    "cost_for_plan": 10.02050781,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_4",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 3,
                          "cost": 2.005126953,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 3,
                        "cost": 2.005126953,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 9,
                    "cost_for_plan": 6.410253906,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": ["t_outer_1"],
                    "table": "t_inner_3",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "scan",
                          "resulting_rows": 9,
                          "cost": 2.015380859,
                          "chosen": true
                        }
                      ],
                      "chosen_access_method": {
                        "type": "scan",
                        "records": 9,
                        "cost": 2.015380859,
                        "uses_join_buffering": true
                      }
                    },
                    "rows_for_plan": 27,
                    "cost_for_plan": 10.02050781,
                    "semijoin_strategy_choice": [],
                    "pruned_by_heuristic": true
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "t_inner_1",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 9,
                      "cost": 2.015380859,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 9,
                    "cost": 2.015380859,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 3.815380859,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_outer_2",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 9,
                      "cost": 2.015380859,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 9,
                    "cost": 2.015380859,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 3.815380859,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_4",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.005126953,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 3,
                    "cost": 2.005126953,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 3,
                "cost_for_plan": 2.605126953,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [],
                "table": "t_inner_3",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 9,
                      "cost": 2.015380859,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 9,
                    "cost": 2.015380859,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 9,
                "cost_for_plan": 3.815380859,
                "semijoin_strategy_choice": [],
                "pruned_by_heuristic": true
              }
            ]
          },
          {
            "fix_semijoin_strategies_for_picked_join_order": [
              {
                "semi_join_strategy": "SJ-Materialization",
                "join_order": [
                  {
                    "table": "t_inner_4"
                  },
                  {
                    "table": "t_inner_3"
                  }
                ]
              },
              {
                "semi_join_strategy": "SJ-Materialization",
                "join_order": [
                  {
                    "table": "t_inner_1"
                  },
                  {
                    "table": "t_inner_2"
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": [
              "t_outer_1",
              "<subquery2>",
              "t_outer_2",
              "<subquery3>"
            ]
          },
          {
            "substitute_best_equal": {
              "condition": "WHERE",
              "resulting_condition": "1"
            }
          },
          {
            "condition_on_constant_tables": "1",
            "computing_condition": []
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "t_outer_1",
                  "attached": null
                },
                {
                  "table": "t_inner_1",
                  "attached": null
                },
                {
                  "table": "t_inner_2",
                  "attached": null
                },
                {
                  "table": "<subquery2>",
                  "attached": null
                },
                {
                  "table": "t_outer_2",
                  "attached": null
                },
                {
                  "table": "t_inner_4",
                  "attached": null
                },
                {
                  "table": "t_inner_3",
                  "attached": null
                },
                {
                  "table": "<subquery3>",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
set @@optimizer_switch= @save_optimizer_switch;
drop table t1,t2;
#
# MDEV-18942: Json_writer::add_bool: Conditional jump or move depends on uninitialised value upon 
# fulltext search under optimizer trace
#
CREATE TABLE t1 (f VARCHAR(255), FULLTEXT(f));
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1 VALUES ('fooba'),('abcde'),('xyzab');
SET optimizer_trace = 'enabled=on';
SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba');
COUNT(*)
1
DROP VIEW v1;
DROP TABLE t1;
#
# MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
#
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k (a int);
insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
create table t1 ( a int, b int, key a_b(a,b));
insert into t1 select a,a from one_k;
set optimizer_trace='enabled=on';
explain select * from t1 force index (a_b) where a=2 and b=4;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a_b	a_b	10	const,const	1	Using index
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "a_b",
                "ranges": 
                [
                    "(2,4) <= (a,b) <= (2,4)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": true,
                "rows": 1,
                "cost": 0.345829876,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
explain select * from t1 where a >= 900 and b between 10 and 20;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a_b	a_b	10	NULL	107	Using where; Using index
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "a_b",
                "ranges": 
                [
                    "(900,10) <= (a,b)"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": true,
                "rows": 107,
                "cost": 21.63379668,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t0,t1;
create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	start_date	start_date	8	NULL	1000	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "start_date",
                "ranges": 
                [
                    "(2019-02-10,NULL) < (start_date,end_date)"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": false,
                "rows": 1000,
                "cost": 1203.877243,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1,one_k;
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int not null,
b int not null,
c int not null,
d int not null,
key a_b_c(a,b,c)
);
insert into t1 select a,a, a,a from ten;
explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	a_b_c	a_b_c	8	NULL	4	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "a_b_c",
                "ranges": 
                [
                    "(1) <= (a,b) < (4,50)"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": false,
                "rows": 4,
                "cost": 4.948710032,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table ten,t1;
# Ported test from MYSQL for ranges involving Binary column
CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
INSERT INTO t1 VALUES (2, NULL);
EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	17	const	1	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C) <= (b) <= (\xD9[\x943j\x99F\xA3\x9C\xF5\xB5\x8C\xFEw-\x8C)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.346171589,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	17	const	1	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(NULL) <= (b) <= (NULL)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.346171589,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1;
#
# MDEV-18880: Optimizer trace prints date in hexadecimal
#
CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
INSERT INTO t1 VALUES (1, 'ab\n');
INSERT INTO t1 VALUES (2, NULL);
set optimizer_trace=1;
EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	13	const	1	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(ab\x0A) <= (b) <= (ab\x0A)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.345927508,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
ALTER TABLE t1 modify column b BINARY(10) AFTER i;
EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	11	const	1	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(ab\x0A\x00\x00\x00\x00\x00\x00\x00) <= (b) <= (ab\x0A\x00\x00\x00\x00\x00\x00\x00)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.345878692,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	13	const	1	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(ab\x0A) <= (b) <= (ab\x0A)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.345927508,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1;
CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
INSERT INTO t1 VALUES (1, 'ab\n');
INSERT INTO t1 VALUES (2, NULL);
EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	11	const	1	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(ab\n) <= (b) <= (ab\n)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.345878692,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1;
CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
Warnings:
Note	1071	Specified key was too long; max key length is 1000 bytes
INSERT INTO t1 VALUES (1, 'ab\n');
INSERT INTO t1 VALUES (2, NULL);
set optimizer_trace=1;
EXPLAIN SELECT * FROM t1 WHERE b= 'ab\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	1003	const	1	Using where
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(ab\x0A) <= (b) <= (ab\x0A)"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.394255553,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1;
CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
INSERT INTO t1 VALUES (1, 'ab\n');
INSERT INTO t1 VALUES (2, 'ab\n');
set optimizer_trace=1;
EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i_b	i_b	13	const	2	Using index condition
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "i_b",
                "ranges": 
                [
                    "(ab\n) <= (b) <= (ab\n)"
                ],
                "rowid_ordered": true,
                "using_mrr": false,
                "index_only": false,
                "rows": 2,
                "cost": 2.546855016,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1;
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k (a int);
insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "range",
          "possible_keys": ["start_date"],
          "key": "start_date",
          "key_length": "8",
          "used_key_parts": ["start_date", "end_date"],
          "rows": 1000,
          "filtered": 100,
          "index_condition": "t1.start_date >= '2019-02-10' and t1.end_date < '2019-04-01'"
        }
      }
    ]
  }
}
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "start_date",
                "ranges": 
                [
                    "(2019-02-10,NULL) < (start_date,end_date)"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": false,
                "rows": 1000,
                "cost": 1203.877243,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
drop table t1, t0, one_k;
#
# MDEV-19776: Assertion `to_len >= 8' failed in convert_to_printable with optimizer trace enabled
#
CREATE TABLE t1 (f VARBINARY(16) NOT NULL, KEY(f));
INSERT INTO t1 VALUES ('a'),('b');
SET optimizer_trace = 'enabled=on';
DELETE FROM t1 WHERE f = 'x';
DROP TABLE t1;
#
# Print cost_for_plan and rows_for_plan for join prefix
#
create table t0(a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k (a int, b int, key(b));
insert into one_k select A.a + B.a*10 + C.a*100, A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
analyze table t0, one_k persistent for all;
Table	Op	Msg_type	Msg_text
test.t0	analyze	status	Engine-independent statistics collected
test.t0	analyze	status	OK
test.one_k	analyze	status	Engine-independent statistics collected
test.one_k	analyze	status	Table is already up to date
set @tmp_jcl=@@join_cache_level;
set join_cache_level=0;
set optimizer_trace=1;
# Check cost/row numbers when multiple tables are joined
#  (cost_for_plan is the same as best_access_path.cost for single-table SELECTs
#   but for joins using condition selectivity it is not as trivial. So,
#   now we are printing it)
explain select * from t0 A, one_k B where A.a<5 and B.a<800;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	10	Using where
1	SIMPLE	B	ALL	NULL	NULL	NULL	NULL	1000	Using where
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
[
    
    [
        
        {
            "plan_prefix": 
            [
            ],
            "table": "A",
            "best_access_path": 
            {
                "considered_access_paths": 
                [
                    
                    {
                        "access_type": "scan",
                        "resulting_rows": 4,
                        "cost": 3.217089844,
                        "chosen": true
                    }
                ],
                "chosen_access_method": 
                {
                    "type": "scan",
                    "records": 4,
                    "cost": 3.217089844,
                    "uses_join_buffering": false
                }
            },
            "rows_for_plan": 4,
            "cost_for_plan": 4.017089844,
            "rest_of_plan": 
            [
                
                {
                    "plan_prefix": 
                    [
                        "A"
                    ],
                    "table": "B",
                    "best_access_path": 
                    {
                        "considered_access_paths": 
                        [
                            
                            {
                                "access_type": "scan",
                                "resulting_rows": 800,
                                "cost": 176.7890625,
                                "chosen": true
                            }
                        ],
                        "chosen_access_method": 
                        {
                            "type": "scan",
                            "records": 800,
                            "cost": 176.7890625,
                            "uses_join_buffering": false
                        }
                    },
                    "rows_for_plan": 3200,
                    "cost_for_plan": 820.8061523,
                    "estimated_join_cardinality": 3200
                }
            ]
        },
        
        {
            "plan_prefix": 
            [
            ],
            "table": "B",
            "best_access_path": 
            {
                "considered_access_paths": 
                [
                    
                    {
                        "access_type": "scan",
                        "resulting_rows": 800,
                        "cost": 44.19726562,
                        "chosen": true
                    }
                ],
                "chosen_access_method": 
                {
                    "type": "scan",
                    "records": 800,
                    "cost": 44.19726562,
                    "uses_join_buffering": false
                }
            },
            "rows_for_plan": 800,
            "cost_for_plan": 204.1972656,
            "pruned_by_heuristic": true
        }
    ]
]
set join_cache_level=@tmp_jcl;
# This shows post-join selectivity
explain select * from t0 A, one_k B where A.a=B.b and B.a<800;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	10	Using where
1	SIMPLE	B	ref	b	b	5	test.A.a	1	Using where
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
[
    
    [
        
        {
            "plan_prefix": 
            [
            ],
            "table": "A",
            "best_access_path": 
            {
                "considered_access_paths": 
                [
                    
                    {
                        "access_type": "scan",
                        "resulting_rows": 10,
                        "cost": 2.017089844,
                        "chosen": true
                    }
                ],
                "chosen_access_method": 
                {
                    "type": "scan",
                    "records": 10,
                    "cost": 2.017089844,
                    "uses_join_buffering": false
                }
            },
            "rows_for_plan": 10,
            "cost_for_plan": 4.017089844,
            "rest_of_plan": 
            [
                
                {
                    "plan_prefix": 
                    [
                        "A"
                    ],
                    "table": "B",
                    "best_access_path": 
                    {
                        "considered_access_paths": 
                        [
                            
                            {
                                "access_type": "ref",
                                "index": "b",
                                "used_range_estimates": false,
                                "reason": "not available",
                                "rows": 1,
                                "cost": 20.00585794,
                                "chosen": true
                            },
                            
                            {
                                "access_type": "scan",
                                "resulting_rows": 800,
                                "cost": 44.19726562,
                                "chosen": false
                            }
                        ],
                        "chosen_access_method": 
                        {
                            "type": "ref",
                            "records": 1,
                            "cost": 20.00585794,
                            "uses_join_buffering": false
                        }
                    },
                    "rows_for_plan": 10,
                    "cost_for_plan": 26.02294779,
                    "selectivity": 0.8,
                    "estimated_join_cardinality": 8
                }
            ]
        },
        
        {
            "plan_prefix": 
            [
            ],
            "table": "B",
            "best_access_path": 
            {
                "considered_access_paths": 
                [
                    
                    {
                        "access_type": "scan",
                        "resulting_rows": 800,
                        "cost": 44.19726562,
                        "chosen": true
                    }
                ],
                "chosen_access_method": 
                {
                    "type": "scan",
                    "records": 800,
                    "cost": 44.19726562,
                    "uses_join_buffering": false
                }
            },
            "rows_for_plan": 800,
            "cost_for_plan": 204.1972656,
            "pruned_by_cost": true
        }
    ]
]
drop table t0, one_k;
#
# Assertion `to_len >= 8' failed in convert_to_printable
#
CREATE TABLE t1 ( a blob, KEY (a(255)));
insert into t1 values ('foo'), ('bar');
EXPLAIN SELECT * FROM t1 WHERE a= REPEAT('a', 0);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	258	const	1	Using where
SELECT * FROM t1 WHERE a= REPEAT('a', 0);
a
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
[
    
    {
        "range_scan_alternatives": 
        [
            
            {
                "index": "a",
                "ranges": 
                [
                    "() <= (a) <= ()"
                ],
                "rowid_ordered": false,
                "using_mrr": false,
                "index_only": false,
                "rows": 1,
                "cost": 1.357887479,
                "chosen": true
            }
        ],
        "analyzing_roworder_intersect": 
        {
            "cause": "too few roworder scans"
        },
        "analyzing_index_merge_union": 
        [
        ]
    }
]
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;
#
# MDEV-22401: Optimizer trace: multi-component range is not printed correctly
#
create table t1 (kp1 int, kp2 int, key(kp1, kp2));
insert into t1 values (1,1),(1,5),(5,1),(5,5);
set optimizer_trace=1;
select * from t1 force index(kp1) where (kp1=2 and kp2 >=4);
kp1	kp2
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
[
    
    [
        
        {
            "index": "kp1",
            "ranges": 
            [
                "(2,4) <= (kp1,kp2) <= (2)"
            ],
            "rowid_ordered": false,
            "using_mrr": false,
            "index_only": true,
            "rows": 1,
            "cost": 0.345829876,
            "chosen": true
        }
    ]
]
drop table t1;
#
# MDEV-21626: Optimizer misses the details about the picked join order
#
CREATE TABLE t1(a INT, b INT, key(a));
INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
CREATE TABLE t2(a INT, b INT, key(a));
INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
SET OPTIMIZER_TRACE=1;
EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
[
    
    [
        
        {
            "plan_prefix": 
            [
            ],
            "table": "t1",
            "best_access_path": 
            {
                "considered_access_paths": 
                [
                    
                    {
                        "access_type": "scan",
                        "resulting_rows": 10,
                        "cost": 2.021972656,
                        "chosen": true
                    }
                ],
                "chosen_access_method": 
                {
                    "type": "scan",
                    "records": 10,
                    "cost": 2.021972656,
                    "uses_join_buffering": false
                }
            },
            "rows_for_plan": 10,
            "cost_for_plan": 4.021972656,
            "rest_of_plan": 
            [
                
                {
                    "plan_prefix": 
                    [
                        "t1"
                    ],
                    "table": "t2",
                    "best_access_path": 
                    {
                        "considered_access_paths": 
                        [
                            
                            {
                                "access_type": "ref",
                                "index": "a",
                                "used_range_estimates": false,
                                "reason": "not available",
                                "rows": 1,
                                "cost": 20.00585794,
                                "chosen": true
                            },
                            
                            {
                                "access_type": "scan",
                                "resulting_rows": 100,
                                "cost": 2.219726562,
                                "chosen": false
                            }
                        ],
                        "chosen_access_method": 
                        {
                            "type": "ref",
                            "records": 1,
                            "cost": 20.00585794,
                            "uses_join_buffering": false
                        }
                    },
                    "rows_for_plan": 10,
                    "cost_for_plan": 26.0278306,
                    "cost_for_sorting": 10,
                    "estimated_join_cardinality": 10
                }
            ]
        },
        
        {
            "plan_prefix": 
            [
            ],
            "table": "t2",
            "best_access_path": 
            {
                "considered_access_paths": 
                [
                    
                    {
                        "access_type": "scan",
                        "resulting_rows": 100,
                        "cost": 2.219726562,
                        "chosen": true,
                        "use_tmp_table": true
                    }
                ],
                "chosen_access_method": 
                {
                    "type": "scan",
                    "records": 100,
                    "cost": 2.219726562,
                    "uses_join_buffering": false
                }
            },
            "rows_for_plan": 100,
            "cost_for_plan": 22.21972656,
            "rest_of_plan": 
            [
                
                {
                    "plan_prefix": 
                    [
                        "t2"
                    ],
                    "table": "t1",
                    "best_access_path": 
                    {
                        "considered_access_paths": 
                        [
                            
                            {
                                "access_type": "ref",
                                "index": "a",
                                "used_range_estimates": false,
                                "reason": "not available",
                                "rows": 1,
                                "cost": 200.0585794,
                                "chosen": true
                            },
                            
                            {
                                "access_type": "scan",
                                "resulting_rows": 10,
                                "cost": 2.021972656,
                                "chosen": true
                            }
                        ],
                        "chosen_access_method": 
                        {
                            "type": "scan",
                            "records": 10,
                            "cost": 2.021972656,
                            "uses_join_buffering": true
                        }
                    },
                    "rows_for_plan": 1000,
                    "cost_for_plan": 224.2416992,
                    "pruned_by_cost": true
                }
            ]
        }
    ]
]
DROP TABLE t1,t2;
#
# MDEV-22665: Print ranges in the optimizer trace created for non-indexed columns when
# optimizer_use_condition_selectivity >2
#
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
SET optimizer_trace=1;
ANALYZE TABLE t1 PERSISTENT FOR ALL;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	0.25	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns'))
[
    
    [
        
        {
            "column_name": "a",
            "ranges": 
            [
                "1 <= a <= 5"
            ],
            "selectivity_from_histogram": 0.05
        },
        
        {
            "column_name": "b",
            "ranges": 
            [
                "NULL < b <= 5"
            ],
            "selectivity_from_histogram": 0.05
        }
    ]
]
EXPLAIN EXTENDED SELECT * from t1 WHERE a != 5;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	98.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` <> 5
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns'))
[
    
    [
        
        {
            "column_name": "a",
            "ranges": 
            [
                "NULL < a < 5",
                "5 < a"
            ],
            "selectivity_from_histogram": 0.98
        }
    ]
]
EXPLAIN EXTENDED SELECT * from t1 WHERE b >= 10 and b < 25;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	100	14.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` >= 10 and `test`.`t1`.`b` < 25
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns'))
[
    
    [
        
        {
            "column_name": "b",
            "ranges": 
            [
                "10 <= b < 25"
            ],
            "selectivity_from_histogram": 0.14
        }
    ]
]
drop table t1;
#
# MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name
# (on optimized builds)
#
CREATE TABLE t1( a INT, b INT, PRIMARY KEY( a ) );
SELECT sum(b), row_number() OVER (order by b) FROM t1 WHERE a = 101;
sum(b)	row_number() OVER (order by b)
NULL	1
UPDATE t1 SET b=10 WHERE a=1;
SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
[
    
    [
        
        {
            "index": "PRIMARY",
            "ranges": 
            [
                "(1) <= (a) <= (1)"
            ],
            "rowid_ordered": true,
            "using_mrr": false,
            "index_only": false,
            "rows": 0,
            "cost": 0.145,
            "chosen": true
        }
    ]
]
DROP TABLE t1;
set optimizer_trace='enabled=off';
#
# MDEV-24975 Server consumes extra 4G memory upon querying INFORMATION_SCHEMA.OPTIIMIZER_TRACE
#
set max_session_mem_used=1024*1024*1024;
select count(*) from information_schema.optimizer_trace;
select * from information_schema.optimizer_trace;
set max_session_mem_used=default;
#
# MDEV-22380 Assertion `name.length == strlen(name.str)' failed in Item::print_item_w_name on SELECT w/ optimizer_trace enabled
#
SET optimizer_trace="enabled=on";
SELECT 'a\0' LIMIT 0;
a\x00
SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
query	trace
SELECT 'a\0' LIMIT 0	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select 'a\0' AS `a\x00` limit 0"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": []
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}
SET optimizer_trace=DEFAULT;
#
# MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object
#
CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY;
SET optimizer_trace=1;
INSERT INTO t1 VALUES (0,0);
SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d);
a
DROP TABLE t1;
#
# End of 10.4 tests
#
set optimizer_trace='enabled=on';
#
# Test many rows to see output of big cost numbers
#
select count(*) from seq_1_to_10000000;
count(*)
10000000
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select count(*) from seq_1_to_10000000	{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select count(0) AS `count(*)` from seq_1_to_10000000"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "seq_1_to_10000000",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "seq_1_to_10000000",
                "table_scan": {
                  "rows": 10000000,
                  "cost": 10000000
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "seq_1_to_10000000",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 10000000,
                      "cost": 10000000,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 10000000,
                    "cost": 10000000,
                    "uses_join_buffering": false
                  }
                },
                "rows_for_plan": 10000000,
                "cost_for_plan": 12000000,
                "estimated_join_cardinality": 10000000
              }
            ]
          },
          {
            "best_join_order": ["seq_1_to_10000000"]
          },
          {
            "attaching_conditions_to_tables": {
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "seq_1_to_10000000",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}	0	0
#
# MDEV-22891: Optimizer trace: const tables are not clearly visible
#
create table t0(a int primary key);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (pk int primary key, a int);
insert into t1 select a,a from t0;
create table t2 (pk int primary key, a int);
insert into t2 select a,a from t0;
create table t3 (pk int primary key, a int);
insert into t3 select a,a from t0;
explain
select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t3	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
1	SIMPLE	t2	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
from information_schema.optimizer_trace;
JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
[
    
    {
        "members": 
        [
            "t3",
            "t2"
        ]
    }
]
drop table t0, t1, t2, t3;
#
# MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace
#
create table t0 (a int);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
set @tmp=@@in_predicate_conversion_threshold;
set in_predicate_conversion_threshold=3;
explain select * from t0 where a in (1,2,3,4,5,6);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
1	PRIMARY	<derived3>	ref	key0	key0	4	test.t0.a	2	FirstMatch(t0)
3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
from information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
[
    
    {
        "item": "t0.a in (1,2,3,4,5,6)",
        "conversion": 
        [
            
            {
                "join_preparation": 
                {
                    "select_id": 2,
                    "steps": 
                    [
                        
                        {
                            "derived": 
                            {
                                "table": "tvc_0",
                                "select_id": 3,
                                "algorithm": "materialized"
                            }
                        },
                        
                        {
                            "transformation": 
                            {
                                "select_id": 2,
                                "from": "IN (SELECT)",
                                "to": "materialization",
                                "sjm_scan_allowed": true,
                                "possible": true
                            }
                        },
                        
                        {
                            "transformation": 
                            {
                                "select_id": 2,
                                "from": "IN (SELECT)",
                                "to": "semijoin",
                                "chosen": true
                            }
                        },
                        
                        {
                            "expanded_query": "/* select#2 */ select tvc_0._col_1 from (values (1),(2),(3),(4),(5),(6)) tvc_0"
                        }
                    ]
                }
            }
        ]
    }
]
explain select * from t0 where a in (1,2,3,4,5,a+1);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
from information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
[
    
    {
        "item": "t0.a in (1,2,3,4,5,t0.a + 1)",
        "done": false,
        "reason": "non-constant element in the IN-list"
    }
]
explain select * from t0 where a in ('1','2','3','4','5','6');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
from information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
[
    
    {
        "item": "t0.a in ('1','2','3','4','5','6')",
        "done": false,
        "reason": "type mismatch"
    }
]
set in_predicate_conversion_threshold=@tmp;
drop table t0;
#
# End of 10.5 tests
#
#
# MDEV-23677: Optimizer trace ... (test coverage)
#
create table t1(a int, b int, c int, primary key (a,b,c));
insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
create table t2 (a int, b int);
insert into t2 values (1,1),(2,2);
create table t3 (a int, b int, c int);
insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t1	ref	PRIMARY	PRIMARY	8	test.t3.a,test.t2.b	1	Using index
set @trace=(select trace from information_schema.optimizer_trace);
set @path= (select json_search(@trace, 'one', 'no predicate for first keypart'));
set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2);
select @sub_path;
@sub_path
$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0]
select 
json_detailed(json_extract(
@trace,
concat(@sub_path,'.best_access_path.considered_access_paths[0]')
)) as S;
S
{
    "access_type": "ref",
    "index": "PRIMARY",
    "rows": 1.79769e308,
    "cost": 1.79769e308,
    "chosen": false,
    "cause": "no predicate for first keypart"
}
drop table t1,t2,t3;
#
# MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field
#
create table t1 (a int, b int, c int);
insert into t1 values (1,1,1),(2,2,2);
create table t2 as select * from t1;
insert into t2 select * from t2;
create table t3 as select * from t2;
insert into t3 select * from t3;
# Check how HAVING is printed
explain 
select 
a,b, count(*)
from t1
where a=3
group by b,b
having a+b < 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
select
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
[
    
    {
        "condition": "WHERE",
        "resulting_condition": "t1.a = 3"
    },
    
    {
        "condition": "HAVING",
        "resulting_condition": "t1.a + t1.b < 10"
    }
]
# Check ON expression
explain 
select 
*
from t1 left join t2 on t2.a=t1.a and t2.a<3 
where 
t1.b > 5555;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
select
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
[
    
    {
        "condition": "WHERE",
        "resulting_condition": "t1.b > 5555"
    },
    
    {
        "condition": "ON expr",
        "attached_to": "t2",
        "resulting_condition": "t2.a = t1.a and t1.a < 3"
    }
]
# Check nested ON expression
explain 
select 
*
from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000
where 
t1.b > 5555;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (incremental, BNL join)
select
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.substitute_best_equal'))
[
    
    {
        "condition": "WHERE",
        "resulting_condition": "t1.b > 5555"
    },
    
    {
        "condition": "ON expr",
        "attached_to": "t2",
        "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000"
    }
]
# The next query is test for:
# MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing
select
json_detailed(json_extract(trace, '$**.condition_processing'))
from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.condition_processing'))
[
    
    {
        "condition": "WHERE",
        "original_condition": "t1.b > 5555",
        "steps": 
        [
            
            {
                "build_equal_items": 
                {
                    "condition": "ON expr",
                    "attached_to": "t3",
                    "resulting_condition": "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)"
                }
            },
            
            {
                "transformation": "equality_propagation",
                "resulting_condition": "t1.b > 5555"
            },
            
            {
                "transformation": "constant_propagation",
                "resulting_condition": "t1.b > 5555"
            },
            
            {
                "transformation": "trivial_condition_removal",
                "resulting_condition": "t1.b > 5555"
            }
        ]
    }
]
drop table t1,t2,t3;
#
# MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED
#
create table t1 (a int, b int, index idx_b(b)) engine=myisam;
insert into t1 values
(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3),
(9,3), (8,1), (4,5), (2,3);
create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam;
insert into t2 values
(7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'),
(4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'),
(11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'),
(17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'),
(24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'),
(31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'),
(37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'),
(54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'),
(61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v');
insert into t2 select a+10, b+10, concat(c,'f') from t2;
analyze table t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
test.t2	analyze	status	Engine-independent statistics collected
test.t2	analyze	status	OK
explain
select t1.a,t.s,t.m
from t1 join
(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
on t1.a=t.a
where t1.b < 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	range	idx_b	idx_b	5	NULL	4	Using index condition; Using where
1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	2	
2	LATERAL DERIVED	t2	ref	idx_a	idx_a	5	test.t1.a	1	
select
json_detailed(json_extract(trace, '$**.choose_best_splitting')) 
from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.choose_best_splitting'))
[
    
    [
        
        {
            "considered_execution_plans": 
            [
                
                {
                    "plan_prefix": 
                    [
                    ],
                    "table": "t2",
                    "best_access_path": 
                    {
                        "considered_access_paths": 
                        [
                            
                            {
                                "access_type": "ref",
                                "index": "idx_a",
                                "used_range_estimates": false,
                                "reason": "not available",
                                "rows": 1.8367,
                                "cost": 2.000585794,
                                "chosen": true
                            },
                            
                            {
                                "type": "scan",
                                "chosen": false,
                                "cause": "cost"
                            }
                        ],
                        "chosen_access_method": 
                        {
                            "type": "ref",
                            "records": 1.8367,
                            "cost": 2.000585794,
                            "uses_join_buffering": false
                        }
                    },
                    "rows_for_plan": 1.8367,
                    "cost_for_plan": 2.367925794,
                    "cost_for_sorting": 1.8367,
                    "estimated_join_cardinality": 1.8367
                }
            ]
        },
        
        {
            "best_splitting": 
            {
                "table": "t2",
                "key": "idx_a",
                "record_count": 4,
                "cost": 2.488945919,
                "unsplit_cost": 25.72361682
            }
        }
    ]
]
select
json_detailed(json_extract(trace, '$**.lateral_derived')) 
from
information_schema.optimizer_trace;
json_detailed(json_extract(trace, '$**.lateral_derived'))
[
    
    {
        "startup_cost": 9.955783677,
        "splitting_cost": 2.488945919,
        "records": 1
    }
]
drop table t1,t2;
#
# Test table functions.
#
CREATE TABLE t1(id INT, f1 JSON);
INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
(4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
SELECT * FROM t1 WHERE id IN
(SELECT id FROM t1 as tt2,
JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
id	f1
1	{"1": 1}
2	{"1": 2}
3	{"1": 3}
4	{"1": 4}
5	{"1": 5}
6	{"1": 6}
select json_detailed(json_extract(trace, '$**.best_join_order'))
from information_schema.OPTIMIZER_TRACE;
json_detailed(json_extract(trace, '$**.best_join_order'))
[
    
    [
        "t1",
        "<subquery2>"
    ]
]
DROP TABLE t1;
#
# MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
#
set optimizer_trace=0;
set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2;
seq
1
# The trace must not be empty:
select left(trace, 100) from information_schema.optimizer_trace;
left(trace, 100)
{
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
        
# End of 10.6 tests
set optimizer_trace='enabled=off';