xref: /dports/databases/mysql57-client/mysql-5.7.36/mysql-test/suite/opt_trace/r/general2_no_prot.result

set optimizer_trace_max_mem_size=1048576;
set end_markers_in_json=on;
set optimizer_trace="enabled=on";
# check that if a sub-statement should not be traced,
# it is not traced even if inside a traced top statement

set optimizer_trace_offset=0, optimizer_trace_limit=100;
create function f1(arg char(1)) returns int
begin
declare res int;
declare dummy varchar(1);
select 1 into res from dual;
select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
select 2 into res from dual;
return 3;
end|
select f1("c")|
f1("c")
3

select * from information_schema.OPTIMIZER_TRACE|
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select f1("c")	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
set res@1 NULL	{
  "steps": [
  ] /* steps */
}	0	0
set dummy@2 NULL	{
  "steps": [
  ] /* steps */
}	0	0
select 1 into res from dual	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 1 AS `1`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
select 2 into res from dual	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 2 AS `2`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
freturn 3 3	{
  "steps": [
  ] /* steps */
}	0	0
set optimizer_trace_offset=default, optimizer_trace_limit=default;
drop function f1;
# check that if a tracing gets disabled in a routine's  body,
# substatements are not traced

set optimizer_trace_offset=0, optimizer_trace_limit=100;
create function f1(arg char(1)) returns int
begin
declare res int;
declare dummy varchar(1);
set optimizer_trace="enabled=off";
select 1 into res from dual;
select TRACE+NULL into dummy from information_schema.OPTIMIZER_TRACE limit 1;
select 2 into res from dual;
return 3;
end|
select f1("c")|
f1("c")
3

select * from information_schema.OPTIMIZER_TRACE|
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select f1("c")	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `f1`('c') AS `f1(\"c\")`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
set res@1 NULL	{
  "steps": [
  ] /* steps */
}	0	0
set dummy@2 NULL	{
  "steps": [
  ] /* steps */
}	0	0
set optimizer_trace_offset=default, optimizer_trace_limit=default;
select @@optimizer_trace;
@@optimizer_trace
enabled=off,one_line=off
set optimizer_trace="enabled=on";
drop function f1;

# Check that if a sub-statement reads OPTIMIZER_TRACE,
# thus reading the unfinished trace of its caller statement,
# there is no crash.

create temporary table optt
(id int primary key auto_increment,
QUERY varchar(200),
TRACE text);
create table t1 (a int, key(a));
insert into t1 values(2);
set optimizer_trace_offset=0, optimizer_trace_limit=100;
create function f1(arg char(1)) returns int
begin
declare res int;
insert into optt select NULL, QUERY, TRACE from information_schema.OPTIMIZER_TRACE;
return 3;
end|
select * from t1 where a in (select f1("c") from t1)|
a

set optimizer_trace="enabled=off";
this should find unfinished traces
select count(*) from optt where TRACE NOT LIKE "%] /* steps */\n}";
count(*)
1
select count(*)<>0 from optt;
count(*)<>0
1
this should not
select count(*) from information_schema.OPTIMIZER_TRACE where TRACE NOT LIKE "%] /* steps */\n}";
count(*)
0
select count(*)<>0 from information_schema.OPTIMIZER_TRACE;
count(*)<>0
1
set optimizer_trace_offset=default, optimizer_trace_limit=default;
drop temporary table optt;
drop function f1;
drop table t1;
set optimizer_trace="enabled=on";

# check of crash with I_S.VIEWS (TABLE_LIST::alias==NULL)

create table t1(a int, b int);
create view v1 as select a from t1;
select VIEW_DEFINITION from information_schema.VIEWS
where TABLE_SCHEMA="test" and TABLE_NAME="v1";
VIEW_DEFINITION
select `test`.`t1`.`a` AS `a` from `test`.`t1`
drop table t1;
drop view v1;

# check for readable display of BIT values

create table t1 (a bit(5), key(a));
insert into t1 values(b'00000'),(b'01101');
select cast(a as unsigned) from t1 where a > b'01100';
cast(a as unsigned)
13
select TRACE from information_schema.OPTIMIZER_TRACE;
TRACE
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select cast(`t1`.`a` as unsigned) AS `cast(a as unsigned)` from `t1` where (`t1`.`a` > 0x0c)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t1`.`a` > 0x0c)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t1`.`a` > 0x0c)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t1`.`a` > 0x0c)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t1`.`a` > 0x0c)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2,
                    "cost": 4.5034
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "a",
                      "usable": true,
                      "key_parts": [
                        "a"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "best_covering_index_scan": {
                    "index": "a",
                    "cost": 1.4175,
                    "chosen": true
                  } /* best_covering_index_scan */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "a",
                        "ranges": [
                          "12 < a"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 2,
                        "cost": 1.4275,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2.4034,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.4034,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`t1`.`a` > 0x0c)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "(`t1`.`a` > 0x0c)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
drop table t1;

# check that trace lists all pushed down ON conditions

create table t1 (i int not null);
insert into t1 values (0),    (2),(3),(4);
create table t2 (i int not null);
insert into t2 values (0),(1),    (3),(4);
create table t3 (i int not null);
insert into t3 values (0),(1),(2),    (4);
select * from
t1 LEFT JOIN
( t2 LEFT JOIN
( t3
)
ON t3.i = t2.i
)
ON t2.i = t1.i
WHERE t3.i IS NULL
;
i	i	i
3	3	NULL
2	NULL	NULL
select TRACE from information_schema.OPTIMIZER_TRACE;
TRACE
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`i` AS `i`,`t2`.`i` AS `i`,`t3`.`i` AS `i` from (`t1` left join (`t2` left join `t3` on((`t3`.`i` = `t2`.`i`))) on((`t2`.`i` = `t1`.`i`))) where isnull(`t3`.`i`)"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `t1`.`i` AS `i`,`t2`.`i` AS `i`,`t3`.`i` AS `i` from `t1` left join (`t2` left join `t3` on((`t3`.`i` = `t2`.`i`))) on((`t2`.`i` = `t1`.`i`)) where isnull(`t3`.`i`)"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "isnull(`t3`.`i`)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "isnull(`t3`.`i`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "isnull(`t3`.`i`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "isnull(`t3`.`i`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t3`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                  0,
                  1
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 4,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 4,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t3`",
                "table_scan": {
                  "rows": 4,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 4,
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.8068,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 4,
                "cost_for_plan": 2.8068,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1`"
                    ] /* plan_prefix */,
                    "table": "`t2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 4,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 4,
                          "cost": 5.207,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 16,
                    "cost_for_plan": 8.0138,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1`",
                          "`t2`"
                        ] /* plan_prefix */,
                        "table": "`t3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 4,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 1,
                              "cost": 5.8083,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 16,
                        "cost_for_plan": 13.822,
                        "chosen": true
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "isnull(`t3`.`i`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": null
                },
                {
                  "table": "`t2`",
                  "attached": "<if>(is_not_null_compl(t2..t3), (`t2`.`i` = `t1`.`i`), true)"
                },
                {
                  "table": "`t3`",
                  "attached": "(<if>(found_match(t2..t3), <if>(found_match(t3), isnull(`t3`.`i`), true), true) and <if>(is_not_null_compl(t3), (`t3`.`i` = `t1`.`i`), true))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              },
              {
                "table": "`t2`"
              },
              {
                "table": "`t3`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
drop table t1,t2,t3;

# test of tracing a query with an HAVING condition, in
# ps-protocol, does not crash

CREATE TABLE t1 (f1 INT, f2 VARCHAR(1));
INSERT INTO t1 VALUES (16,'f');
INSERT INTO t1 VALUES (16,'f');
CREATE TABLE t2 (f1 INT, f2 VARCHAR(1));
INSERT INTO t2 VALUES (13,'f');
INSERT INTO t2 VALUES (20,'f');
CREATE TABLE t3 (f1 INT, f2 VARCHAR(1));
INSERT INTO t3 VALUES (7,'f');
EXPLAIN SELECT t1.f2 FROM t1
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2  = t2.f2  ) ON t3 .f2  = t2 .f2
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
ORDER BY f2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
1	PRIMARY	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using join buffer (Block Nested Loop)
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; Using join buffer (Block Nested Loop)
2	SUBQUERY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f2` AS `f2` from `test`.`t1` join `test`.`t2` join `test`.`t3` where (`test`.`t2`.`f2` = `test`.`t3`.`f2`) having (not(<in_optimizer>(<cache>(('v','i')),<exists>(/* select#2 */ select 1,1 from `test`.`t1` having (((<cache>('v') = `test`.`t1`.`f2`) or isnull(`test`.`t1`.`f2`)) and ((<cache>('i') = min(`test`.`t1`.`f2`)) or isnull(min(`test`.`t1`.`f2`))) and <is_not_null_test>(`test`.`t1`.`f2`) and <is_not_null_test>(min(`test`.`t1`.`f2`))))))) order by `test`.`t1`.`f2`
SELECT t1.f2 FROM t1
STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2  = t2.f2  ) ON t3 .f2  = t2 .f2
HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
ORDER BY f2;
f2
f
f
f
f
select TRACE from information_schema.OPTIMIZER_TRACE;
TRACE
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1`"
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": false
                  } /* transformation */
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "EXISTS (CORRELATED SELECT)",
                    "chosen": true,
                    "evaluating_constant_having_conditions": [
                    ] /* evaluating_constant_having_conditions */
                  } /* transformation */
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select `t1`.`f2` AS `f2` from (`t1` straight_join (`t2` join `t3` on((`t3`.`f2` = `t2`.`f2`))) on((`t3`.`f2` = `t2`.`f2`))) having (not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`))))))) order by `t1`.`f2`"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `t1`.`f2` AS `f2` from `t1` join `t2` join `t3` where ((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`)) having (not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`))))))) order by `t1`.`f2`"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`t3`.`f2` = `t2`.`f2`) and (`t3`.`f2` = `t2`.`f2`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(`t3`.`f2`, `t2`.`f2`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(`t3`.`f2`, `t2`.`f2`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "condition_processing": {
              "condition": "HAVING",
              "original_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))",
              "steps": [
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(<in_optimizer>(('v','i'),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t3`",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t3`",
                "table_scan": {
                  "rows": 1,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2.4098,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.4098,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1`"
                    ] /* plan_prefix */,
                    "table": "`t3`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 1,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 1,
                          "cost": 2.4049,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 2,
                    "cost_for_plan": 4.8147,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1`",
                          "`t3`"
                        ] /* plan_prefix */,
                        "table": "`t2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 2,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 2,
                              "cost": 2.8099,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 4,
                        "cost_for_plan": 7.6246,
                        "sort_cost": 4,
                        "new_cost_for_plan": 11.625,
                        "chosen": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t1`"
                    ] /* plan_prefix */,
                    "table": "`t2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 2,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 2,
                          "cost": 2.8098,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 4,
                    "cost_for_plan": 5.2196,
                    "pruned_by_heuristic": true
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`t2`.`f2` = `t3`.`f2`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": null
                },
                {
                  "table": "`t3`",
                  "attached": null
                },
                {
                  "table": "`t2`",
                  "attached": "(`t2`.`f2` = `t3`.`f2`)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t1`.`f2`",
              "items": [
                {
                  "item": "`t1`.`f2`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t1`.`f2`"
            } /* clause_processing */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              },
              {
                "table": "`t3`"
              },
              {
                "table": "`t2`"
              }
            ] /* refine_plan */
          },
          {
            "sort_using_internal_table": {
              "condition_for_sort": "(not(<in_optimizer>(<cache>(('v','i')),<exists>(/* select#2 */ select `t1`.`f2`,min(`t1`.`f2`) from `t1` having (((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))))))",
              "having_after_sort": null
            } /* sort_using_internal_table */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 2,
        "steps": [
          {
            "condition_processing": {
              "condition": "HAVING",
              "original_condition": "(((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))",
              "steps": [
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(((<cache>('v') = `t1`.`f2`) or isnull(`t1`.`f2`)) and ((<cache>('i') = min(`t1`.`f2`)) or isnull(min(`t1`.`f2`))) and <is_not_null_test>(`t1`.`f2`) and <is_not_null_test>(min(`t1`.`f2`)))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2.4098,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.4098,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "materialization",
              "has_nullable_expressions": true,
              "treat_UNKNOWN_as_FALSE": false,
              "possible": false,
              "cause": "cannot_handle_partial_matches"
            } /* transformation */
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "put_1_in_SELECT_list": true
            } /* transformation */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 3,
                "key_length": 0,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 349525
              } /* tmp_table_info */
            } /* creating_tmp_table */
          },
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "intermediate_tmp_table",
                "field": "f2"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
              {
                "subselect_execution": {
                  "select#": 2,
                  "steps": [
                    {
                      "join_execution": {
                        "select#": 2,
                        "steps": [
                        ] /* steps */
                      } /* join_execution */
                    }
                  ] /* steps */
                } /* subselect_execution */
              },
              {
                "subselect_execution": {
                  "select#": 2,
                  "steps": [
                  ] /* steps */
                } /* subselect_execution */
              },
              {
                "subselect_execution": {
                  "select#": 2,
                  "steps": [
                  ] /* steps */
                } /* subselect_execution */
              },
              {
                "subselect_execution": {
                  "select#": 2,
                  "steps": [
                  ] /* steps */
                } /* subselect_execution */
              }
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 4,
              "examined_rows": 4,
              "number_of_tmp_files": 0,
              "sort_buffer_size": "NNN",
              "sort_mode": "<sort_key, rowid>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
DROP TABLES t1,t2,t3;

# Test that tracing a query with a materialized FROM-clause
# derived table using a GROUP BY, does not crash

create table t1 (a int, b int);
insert into t1 values (1,1), (2,null), (3, 4);
select max(x) from (select sum(a) as x from t1 group by b) as teeone;
max(x)
3
select TRACE from information_schema.OPTIMIZER_TRACE;
TRACE
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select sum(`t1`.`a`) AS `x` from `t1` group by `t1`.`b`"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "derived": {
              "table": " `teeone`",
              "select#": 2,
              "materialized": true
            } /* derived */
          },
          {
            "expanded_query": "/* select#1 */ select max(`teeone`.`x`) AS `max(x)` from (/* select#2 */ select sum(`t1`.`a`) AS `x` from `t1` group by `t1`.`b`) `teeone`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
                {
                  "substitute_generated_columns": {
                  } /* substitute_generated_columns */
                },
                {
                  "table_dependencies": [
                    {
                      "table": "`t1`",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    }
                  ] /* table_dependencies */
                },
                {
                  "rows_estimation": [
                    {
                      "table": "`t1`",
                      "table_scan": {
                        "rows": 3,
                        "cost": 2
                      } /* table_scan */
                    }
                  ] /* rows_estimation */
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 3,
                            "access_type": "scan",
                            "resulting_rows": 3,
                            "cost": 2.6066,
                            "chosen": true,
                            "use_tmp_table": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 3,
                      "cost_for_plan": 2.6066,
                      "sort_cost": 3,
                      "new_cost_for_plan": 5.6066,
                      "chosen": true
                    }
                  ] /* considered_execution_plans */
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": null,
                    "attached_conditions_computation": [
                    ] /* attached_conditions_computation */,
                    "attached_conditions_summary": [
                      {
                        "table": "`t1`",
                        "attached": null
                      }
                    ] /* attached_conditions_summary */
                  } /* attaching_conditions_to_tables */
                },
                {
                  "clause_processing": {
                    "clause": "GROUP BY",
                    "original_clause": "`t1`.`b`",
                    "items": [
                      {
                        "item": "`t1`.`b`"
                      }
                    ] /* items */,
                    "resulting_clause_is_simple": true,
                    "resulting_clause": "`t1`.`b`"
                  } /* clause_processing */
                },
                {
                  "refine_plan": [
                    {
                      "table": "`t1`"
                    }
                  ] /* refine_plan */
                }
              ] /* steps */
            } /* join_optimization */
          },
          {
            "table_dependencies": [
              {
                "table": " `teeone`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": " `teeone`",
                "table_scan": {
                  "rows": 3,
                  "cost": 10
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": " `teeone`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 10.75,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 10.75,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": " `teeone`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": " `teeone`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": " `teeone`",
                "row_length": 16,
                "key_length": 0,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 65536
              } /* tmp_table_info */
            } /* creating_tmp_table */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
                {
                  "creating_tmp_table": {
                    "tmp_table_info": {
                      "table": "intermediate_tmp_table",
                      "row_length": 22,
                      "key_length": 5,
                      "unique_constraint": false,
                      "location": "memory (heap)",
                      "row_limit_estimate": 47662
                    } /* tmp_table_info */
                  } /* creating_tmp_table */
                },
                {
                  "filesort_information": [
                    {
                      "direction": "asc",
                      "table": "intermediate_tmp_table",
                      "field": "b"
                    }
                  ] /* filesort_information */,
                  "filesort_priority_queue_optimization": {
                    "usable": false,
                    "cause": "not applicable (no LIMIT)"
                  } /* filesort_priority_queue_optimization */,
                  "filesort_execution": [
                  ] /* filesort_execution */,
                  "filesort_summary": {
                    "rows": 3,
                    "examined_rows": 3,
                    "number_of_tmp_files": 0,
                    "sort_buffer_size": "NNN",
                    "sort_mode": "<sort_key, rowid>"
                  } /* filesort_summary */
                }
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
drop table t1;

# To have no crash above, we had to restore the ref_array at
# end of JOIN::exec(). This impacts how the query looks like,
# but not too much, as seen in the error message below.
# Comes from func_gconcat.test.

CREATE TABLE t1(f1 int);
INSERT INTO t1 values (0),(0);
set optimizer_trace="enabled=off";
SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
set optimizer_trace="enabled=on";
SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
DROP TABLE t1;

# Check that SQL PREPARE and SQL EXECUTE each produce one trace.

set optimizer_trace_offset=0, optimizer_trace_limit=100;
prepare stmt from "select 1";
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select 1	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 1 AS `1`"
          }
        ] /* steps */
      } /* join_preparation */
    }
  ] /* steps */
}	0	0
set optimizer_trace_offset=0, optimizer_trace_limit=100;
execute stmt;
1
1
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select 1	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 1 AS `1`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
deallocate prepare stmt;
set optimizer_trace_offset=default, optimizer_trace_limit=default;

# Test of SELECTs in IF in stored routine.
# Same test for CASE WHEN.

create table t1 (a int);
create procedure p1()
begin
if exists(select 1) then
insert into t1 values(1);
end if;
if exists(select 2) then
insert into t1 values(2);
end if;
if (select count(*) from t1) then
insert into t1 values(3);
end if;
set @a=(select count(a) from t1 where a>0);
case (select count(a) from t1 where a>1)
when 2 then set @b=2;
else set @b=3;
end case;
end|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
set @old_max=@@optimizer_trace_max_mem_size;
set optimizer_trace_max_mem_size=40000;
call p1();
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
set @old_max=@@optimizer_trace_max_mem_size	{
  "steps": [
  ] /* steps */
}	0	0
set optimizer_trace_max_mem_size=40000	{
  "steps": [
  ] /* steps */
}	0	0
call p1()	{
  "steps": [
  ] /* steps */
}	0	0
jump_if_not 2(2) exists(select 1)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select 1"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
insert into t1 values(1)	{
  "steps": [
  ] /* steps */
}	0	0
jump_if_not 4(4) exists(select 2)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select 2"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
insert into t1 values(2)	{
  "steps": [
  ] /* steps */
}	0	0
jump_if_not 6(6) (select count(0) from `test`.`t1`)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select count(0) from `t1`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
              ] /* steps */,
              "empty_result": {
                "cause": "Select tables optimized away"
              } /* empty_result */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
insert into t1 values(3)	{
  "steps": [
  ] /* steps */
}	0	0
SET @a=(select count(a) from t1 where a>0)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 0)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "(`t1`.`a` > 0)",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "(`t1`.`a` > 0)"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "(`t1`.`a` > 0)"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "(`t1`.`a` > 0)"
                      }
                    ] /* steps */
                  } /* condition_processing */
                },
                {
                  "substitute_generated_columns": {
                  } /* substitute_generated_columns */
                },
                {
                  "table_dependencies": [
                    {
                      "table": "`t1`",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    }
                  ] /* table_dependencies */
                },
                {
                  "ref_optimizer_key_uses": [
                  ] /* ref_optimizer_key_uses */
                },
                {
                  "rows_estimation": [
                    {
                      "table": "`t1`",
                      "table_scan": {
                        "rows": 3,
                        "cost": 2
                      } /* table_scan */
                    }
                  ] /* rows_estimation */
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 3,
                            "access_type": "scan",
                            "resulting_rows": 1,
                            "cost": 2.6051,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 1,
                      "cost_for_plan": 2.6051,
                      "chosen": true
                    }
                  ] /* considered_execution_plans */
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": "(`t1`.`a` > 0)",
                    "attached_conditions_computation": [
                    ] /* attached_conditions_computation */,
                    "attached_conditions_summary": [
                      {
                        "table": "`t1`",
                        "attached": "(`t1`.`a` > 0)"
                      }
                    ] /* attached_conditions_summary */
                  } /* attaching_conditions_to_tables */
                },
                {
                  "refine_plan": [
                    {
                      "table": "`t1`"
                    }
                  ] /* refine_plan */
                }
              ] /* steps */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
set_case_expr (15) 0 (select count(`a`) from `test`.`t1` where (`a` > 1))	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select count(`t1`.`a`) from `t1` where (`t1`.`a` > 1)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "(`t1`.`a` > 1)",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "(`t1`.`a` > 1)"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "(`t1`.`a` > 1)"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "(`t1`.`a` > 1)"
                      }
                    ] /* steps */
                  } /* condition_processing */
                },
                {
                  "substitute_generated_columns": {
                  } /* substitute_generated_columns */
                },
                {
                  "table_dependencies": [
                    {
                      "table": "`t1`",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    }
                  ] /* table_dependencies */
                },
                {
                  "ref_optimizer_key_uses": [
                  ] /* ref_optimizer_key_uses */
                },
                {
                  "rows_estimation": [
                    {
                      "table": "`t1`",
                      "table_scan": {
                        "rows": 3,
                        "cost": 2
                      } /* table_scan */
                    }
                  ] /* rows_estimation */
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 3,
                            "access_type": "scan",
                            "resulting_rows": 1,
                            "cost": 2.6051,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 1,
                      "cost_for_plan": 2.6051,
                      "chosen": true
                    }
                  ] /* considered_execution_plans */
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": "(`t1`.`a` > 1)",
                    "attached_conditions_computation": [
                    ] /* attached_conditions_computation */,
                    "attached_conditions_summary": [
                      {
                        "table": "`t1`",
                        "attached": "(`t1`.`a` > 1)"
                      }
                    ] /* attached_conditions_summary */
                  } /* attaching_conditions_to_tables */
                },
                {
                  "refine_plan": [
                    {
                      "table": "`t1`"
                    }
                  ] /* refine_plan */
                }
              ] /* steps */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
jump_if_not_case_when 11(15) (case_expr@0 = 2)	{
  "steps": [
  ] /* steps */
}	0	0
SET @b=2	{
  "steps": [
  ] /* steps */
}	0	0
select * from t1;
a
1
2
3
select @a,@b;
@a	@b
3	2
set optimizer_trace_max_mem_size=@old_max;
drop procedure p1;
drop table t1;

# Test of tracing of DO.

set optimizer_trace_offset=0, optimizer_trace_limit=100;
do (select 42);
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
do (select 42)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select 42"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select 42 AS `(select 42)`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0

# Test of tracing of subquery used in parameter of routine call

create table t1(a int);
insert into t1 values(1),(2);
create procedure p1(x int)
begin
declare b int;
set b=(select 2+x from dual);
end|
set optimizer_trace_offset=0, optimizer_trace_limit=100;
call p1((select a from t1 limit 1));
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
call p1((select a from t1 limit 1))	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` limit 1"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
                {
                  "table_dependencies": [
                    {
                      "table": "`t1`",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    }
                  ] /* table_dependencies */
                },
                {
                  "rows_estimation": [
                    {
                      "table": "`t1`",
                      "table_scan": {
                        "rows": 2,
                        "cost": 2
                      } /* table_scan */
                    }
                  ] /* rows_estimation */
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 2,
                            "access_type": "scan",
                            "resulting_rows": 2,
                            "cost": 2.4034,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 2,
                      "cost_for_plan": 2.4034,
                      "chosen": true
                    }
                  ] /* considered_execution_plans */
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": null,
                    "attached_conditions_computation": [
                    ] /* attached_conditions_computation */,
                    "attached_conditions_summary": [
                      {
                        "table": "`t1`",
                        "attached": null
                      }
                    ] /* attached_conditions_summary */
                  } /* attaching_conditions_to_tables */
                },
                {
                  "refine_plan": [
                    {
                      "table": "`t1`"
                    }
                  ] /* refine_plan */
                }
              ] /* steps */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
set b@1 NULL	{
  "steps": [
  ] /* steps */
}	0	0
set b@1 (select (2 + x@0))	{
  "steps": [
    {
      "join_preparation": {
        "select#": 2,
        "steps": [
          {
            "expanded_query": "/* select#2 */ select (2 + x@0)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "subselect_execution": {
        "select#": 2,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_optimization */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* subselect_execution */
    }
  ] /* steps */
}	0	0
drop procedure p1;
drop table t1;
set optimizer_trace_offset=default, optimizer_trace_limit=default;

# Test that printing expanded query does not alter query's
# results.
# Comes from ctype_utf8mb4_heap.test

create table t1 (f1 varchar(1) not null) default charset utf8mb4;
insert into t1 values (''), ('');
select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
concat(concat(_latin1'->',f1),_latin1'<-')
-><-
-><-
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select concat(concat(_latin1'->',f1),_latin1'<-') from t1	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select concat(concat('->',`t1`.`f1`),'<-') AS `concat(concat(_latin1'->',f1),_latin1'<-')` from `t1`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2.4098,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.4098,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1;

# Bug#12546331 - SEGFAULT IN SUBSELECT_INDEXSUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE

CREATE TABLE t1 (
col_int_nokey INT,
col_int_key INT,
col_varchar_key varchar(1),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t1 VALUES
(NULL,8,'x'),
(8,7,'d'),
(1,1,'r'),
(9,7,'f'),
(4,9,'y'),
(3,NULL,'u'),
(2,1,'m'),
(NULL,9,NULL),
(2,2,'o'),
(NULL,9,'w'),
(6,2,'m'),
(7,4,'q'),
(2,0,NULL),
(5,4,'d'),
(7,8,'g'),
(6,NULL,'x'),
(6,NULL,'f'),
(2,0,'p'),
(9,NULL,'j'),
(6,8,'c')
;
CREATE TABLE t2 (
col_int_nokey INT,
col_int_key INT,
col_varchar_key varchar(1),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
INSERT INTO t2 VALUES
(2,4,'v'),
(150,62,'v'),
(NULL,7,'c'),
(2,1,NULL),
(5,0,'x'),
(3,7,'i'),
(1,7,'e'),
(4,1,'p'),
(NULL,7,'s'),
(2,1,'j'),
(6,5,'z'),
(6,2,'c'),
(8,0,'a'),
(2,1,'q'),
(6,8,'y'),
(8,1,NULL),
(3,1,'r'),
(3,9,'v'),
(9,1,NULL),
(6,5,'r')
;
SELECT col_int_nokey
FROM (
SELECT *
FROM t2
WHERE col_varchar_key > 'a'
    OR ( 7 , 5 ) NOT IN (
SELECT col_int_nokey , col_int_key
FROM t1 )
) AS alias1;
col_int_nokey
2
150
NULL
2
5
3
1
4
NULL
2
6
6
8
2
6
8
3
3
9
6
DROP TABLE t1;
DROP TABLE t2;

BUG#12552262 - INVALID JSON WITH TWO CALLS TO TEST_QUICK_SELECT

CREATE TABLE t1 (
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
col_int_key INT,
KEY col_int_key (col_int_key)
);
CREATE TABLE t2 (
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
col_int_key INT,
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_int_key (col_int_key)
);
INSERT INTO t2 VALUES ('qykbaqfyhz','l',NULL);
CREATE TABLE t3 (
col_int_key INT,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
);
INSERT INTO t3 VALUES (0,'s','it');
INSERT INTO t3 VALUES (9,'IQTHK','JCAQM');
SELECT table2.col_int_key
FROM t3 AS table1
LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
table3.col_varchar_10_utf8_key
;
col_int_key
NULL
NULL
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT table2.col_int_key
FROM t3 AS table1
LEFT JOIN t1 AS table2 ON table1.col_int_key < table2.col_int_key
LEFT JOIN t2 AS table3 ON table2.col_varchar_10_latin1_key >=
table3.col_varchar_10_utf8_key	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `table2`.`col_int_key` AS `col_int_key` from ((`t3` `table1` left join `t1` `table2` on((`table1`.`col_int_key` < `table2`.`col_int_key`))) left join `t2` `table3` on((convert(`table2`.`col_varchar_10_latin1_key` using utf8) >= `table3`.`col_varchar_10_utf8_key`)))"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `table2`.`col_int_key` AS `col_int_key` from `t3` `table1` left join `t1` `table2` on((`table1`.`col_int_key` < `table2`.`col_int_key`)) left join `t2` `table3` on((convert(`table2`.`col_varchar_10_latin1_key` using utf8) >= `table3`.`col_varchar_10_utf8_key`))"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": null,
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": null
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "table_dependencies": [
              {
                "table": "`t3` `table1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1` `table2`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2` `table3`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                  0,
                  1
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t3` `table1`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t1` `table2`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              },
              {
                "table": "`t2` `table3`",
                "table_scan": {
                  "rows": 1,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`t1` `table2`"
                ] /* plan_prefix */,
                "table": "`t3` `table1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2.4107,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.4107,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1` `table2`",
                      "`t3` `table1`"
                    ] /* plan_prefix */,
                    "table": "`t2` `table3`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 1,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 1,
                          "cost": 2.405,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 2,
                    "cost_for_plan": 4.8157,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "condition_on_constant_tables": "1",
            "condition_value": true
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "1",
              "attached_conditions_computation": [
                {
                  "table": "`t2` `table3`",
                  "rechecking_index_usage": {
                    "recheck_reason": "not_first_table",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 1,
                        "cost": 4.3049
                      } /* table_scan */,
                      "potential_range_indexes": [
                        {
                          "index": "col_varchar_10_utf8_key",
                          "usable": true,
                          "key_parts": [
                            "col_varchar_10_utf8_key"
                          ] /* key_parts */
                        },
                        {
                          "index": "col_int_key",
                          "usable": false,
                          "cause": "not_applicable"
                        }
                      ] /* potential_range_indexes */,
                      "best_covering_index_scan": {
                        "index": "col_varchar_10_utf8_key",
                        "cost": 1.2,
                        "chosen": true
                      } /* best_covering_index_scan */,
                      "setup_range_conditions": [
                        {
                          "impossible_condition": {
                            "cause": "comparison_with_null_always_false"
                          } /* impossible_condition */
                        }
                      ] /* setup_range_conditions */,
                      "impossible_range": true
                    } /* range_analysis */,
                    "without_ON_clause": {
                      "range_analysis": {
                        "table_scan": {
                          "rows": 1,
                          "cost": 4.3049
                        } /* table_scan */,
                        "potential_range_indexes": [
                          {
                            "index": "col_varchar_10_utf8_key",
                            "usable": true,
                            "key_parts": [
                              "col_varchar_10_utf8_key"
                            ] /* key_parts */
                          },
                          {
                            "index": "col_int_key",
                            "usable": false,
                            "cause": "not_applicable"
                          }
                        ] /* potential_range_indexes */,
                        "best_covering_index_scan": {
                          "index": "col_varchar_10_utf8_key",
                          "cost": 1.2,
                          "chosen": true
                        } /* best_covering_index_scan */,
                        "group_index_range": {
                          "chosen": false,
                          "cause": "not_single_table"
                        } /* group_index_range */
                      } /* range_analysis */
                    } /* without_ON_clause */
                  } /* rechecking_index_usage */
                }
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t3` `table1`",
                  "attached": null
                },
                {
                  "table": "`t2` `table3`",
                  "attached": "<if>(is_not_null_compl(table3), (convert(NULL using utf8) >= `table3`.`col_varchar_10_utf8_key`), true)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t3` `table1`"
              },
              {
                "table": "`t2` `table3`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
DROP TABLE t1,t2,t3;

Tests of tracing of the "eq_ref optimization" of plan search

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, key(a));
create table t2 (a int, b int, key(a));
create table t3 (a int, b int, key(a));
insert into t1 select a,a from t0;
insert into t2 select a,a from t0;
insert into t3 select a,a from t0;
set @old_opt_switch=@@optimizer_switch;
explain select *
from t0 where a in
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t0	NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
2	DEPENDENT SUBQUERY	t1	NULL	index	a	a	5	NULL	10	100.00	Using where; Using index
2	DEPENDENT SUBQUERY	t2	NULL	ref	a	a	5	test.t1.a	1	100.00	Using index
2	DEPENDENT SUBQUERY	t3	NULL	ref	a	a	5	test.t1.a	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t0`.`a` AS `a` from `test`.`t0` where <in_optimizer>(`test`.`t0`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (<cache>(`test`.`t0`.`a`) = (`test`.`t1`.`a` + `test`.`t1`.`a`)))))
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
explain select *
from t0 where a in
(select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": false
                  } /* transformation */
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "EXISTS (CORRELATED SELECT)",
                    "chosen": true,
                    "evaluating_constant_where_conditions": [
                    ] /* evaluating_constant_where_conditions */
                  } /* transformation */
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select `t0`.`a` AS `a` from `t0` where <in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from (`t1` left join (`t2` join `t3`) on(((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`)))) where (<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`))))"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "outer_join_to_inner_join",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t0`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t0`",
                "table_scan": {
                  "rows": 10,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t0`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 4.0171,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 4.0171,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t0`",
                  "attached": "<in_optimizer>(`t0`.`a`,<exists>(/* select#2 */ select (`t2`.`a` + `t3`.`a`) from `t1` join `t2` join `t3` where ((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t0`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 2,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and (`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((<cache>(`t0`.`a`) = (`t2`.`a` + `t3`.`a`)) and multiple equal(`t2`.`a`, `t1`.`a`, `t3`.`a`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t3`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1`",
                "field": "a",
                "equals": "`t2`.`a`",
                "null_rejecting": true
              },
              {
                "table": "`t1`",
                "field": "a",
                "equals": "`t3`.`a`",
                "null_rejecting": true
              },
              {
                "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
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 10,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 10,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t3`",
                "table_scan": {
                  "rows": 10,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "a",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 4.022,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 4.022,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1`"
                    ] /* plan_prefix */,
                    "table": "`t2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rows": 1,
                          "cost": 12,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 10,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 10,
                          "cost": 22.022,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10,
                    "cost_for_plan": 16.022,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1`",
                          "`t2`"
                        ] /* plan_prefix */,
                        "table": "`t3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 10,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 10,
                              "cost": 22.023,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "added_to_eq_ref_extension": true,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10,
                        "cost_for_plan": 28.022,
                        "chosen": true
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "a",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 4.022,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 4.022,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t2`"
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rows": 1,
                          "cost": 12,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 10,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 10,
                          "cost": 22.022,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10,
                    "cost_for_plan": 16.022,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t2`",
                          "`t1`"
                        ] /* plan_prefix */,
                        "table": "`t3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 10,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 10,
                              "cost": 22.023,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "added_to_eq_ref_extension": true,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10,
                        "cost_for_plan": 28.022,
                        "pruned_by_cost": true
                      },
                      {
                        "plan_prefix": [
                          "`t2`",
                          "`t1`"
                        ] /* plan_prefix */,
                        "table": "`t3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 10,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 10,
                              "cost": 22.023,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10,
                        "cost_for_plan": 28.022,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t2`"
                    ] /* plan_prefix */,
                    "table": "`t3`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rows": 1,
                          "cost": 12,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 10,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 10,
                          "cost": 22.022,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10,
                    "cost_for_plan": 16.022,
                    "pruned_by_heuristic": true
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t3`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "a",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "resulting_rows": 10,
                      "cost": 4.022,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10,
                "cost_for_plan": 4.022,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t3`"
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rows": 1,
                          "cost": 12,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 10,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 10,
                          "cost": 22.022,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10,
                    "cost_for_plan": 16.022,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t3`",
                          "`t1`"
                        ] /* plan_prefix */,
                        "table": "`t2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 10,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 10,
                              "cost": 22.023,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "added_to_eq_ref_extension": true,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10,
                        "cost_for_plan": 28.022,
                        "pruned_by_cost": true
                      },
                      {
                        "plan_prefix": [
                          "`t3`",
                          "`t1`"
                        ] /* plan_prefix */,
                        "table": "`t2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 10,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 10,
                              "cost": 22.023,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10,
                        "cost_for_plan": 28.022,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t3`"
                    ] /* plan_prefix */,
                    "table": "`t2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rows": 1,
                          "cost": 12,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 10,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 10,
                          "cost": 22.022,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10,
                    "cost_for_plan": 16.022,
                    "pruned_by_heuristic": true
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "put_1_in_SELECT_list": true
            } /* transformation */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`t2`.`a` = `t1`.`a`) and (`t3`.`a` = `t1`.`a`) and (<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`)))",
              "attached_conditions_computation": [
                {
                  "table": "`t1`",
                  "rechecking_index_usage": {
                    "recheck_reason": "not_first_table",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 10,
                        "cost": 14
                      } /* table_scan */,
                      "potential_range_indexes": [
                        {
                          "index": "a",
                          "usable": true,
                          "key_parts": [
                            "a"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indexes */,
                      "best_covering_index_scan": {
                        "index": "a",
                        "cost": 3.2093,
                        "chosen": true
                      } /* best_covering_index_scan */,
                      "setup_range_conditions": [
                      ] /* setup_range_conditions */,
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_single_table"
                      } /* group_index_range */
                    } /* range_analysis */
                  } /* rechecking_index_usage */
                }
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "(<cache>(`t0`.`a`) = (`t1`.`a` + `t1`.`a`))"
                },
                {
                  "table": "`t2`",
                  "attached": null
                },
                {
                  "table": "`t3`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              },
              {
                "table": "`t2`"
              },
              {
                "table": "`t3`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
          {
            "join_explain": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_explain */
          }
        ] /* steps */
      } /* join_explain */
    }
  ] /* steps */
}	0	0
set optimizer_switch=@old_opt_switch;
drop table t0,t1,t2,t3;
CREATE TABLE t1 (
OBJECTID int(11) NOT NULL default '0',
SORTORDER int(11) NOT NULL auto_increment,
KEY t1_SortIndex (SORTORDER),
KEY t1_IdIndex (OBJECTID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t2 (
ID int(11) default NULL,
PARID int(11) default NULL,
UNIQUE KEY t2_ID_IDX (ID),
KEY t2_PARID_IDX (PARID)
) engine=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1000,0),(1001,0),(1002,0),(1003,0),(1008,1),(1009,1),(1010,1),(1011,1),(1016,2);
CREATE TABLE t3 (
ID int(11) default NULL,
DATA decimal(10,2) default NULL,
UNIQUE KEY t3_ID_IDX (ID)
) engine=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp;
497	ID	NULL
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select 497, tmp.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as tmp	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from ((`t1` join `t2` on((`t1`.`OBJECTID` = `t2`.`ID`))) join `t3` on((`t1`.`OBJECTID` = `t3`.`ID`))) group by `t2`.`PARID` order by `DATA` desc"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "derived": {
              "table": " `tmp`",
              "select#": 2,
              "materialized": true
            } /* derived */
          },
          {
            "expanded_query": "/* select#1 */ select 497 AS `497`,`tmp`.`ID` AS `ID`,NULL AS `NULL` from (/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from ((`t1` join `t2` on((`t1`.`OBJECTID` = `t2`.`ID`))) join `t3` on((`t1`.`OBJECTID` = `t3`.`ID`))) group by `t2`.`PARID` order by `DATA` desc) `tmp`"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#2 */ select 497 AS `ID`,max(`t3`.`DATA`) AS `DATA` from `t1` join `t2` join `t3` where ((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`)) group by `t2`.`PARID` order by `DATA` desc"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "join_optimization": {
              "select#": 2,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "((`t1`.`OBJECTID` = `t3`.`ID`) and (`t1`.`OBJECTID` = `t2`.`ID`))",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "(multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`))"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "multiple equal(`t1`.`OBJECTID`, `t3`.`ID`, `t2`.`ID`)"
                      }
                    ] /* steps */
                  } /* condition_processing */
                },
                {
                  "substitute_generated_columns": {
                  } /* substitute_generated_columns */
                },
                {
                  "table_dependencies": [
                    {
                      "table": "`t1`",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    },
                    {
                      "table": "`t2`",
                      "row_may_be_null": false,
                      "map_bit": 1,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    },
                    {
                      "table": "`t3`",
                      "row_may_be_null": false,
                      "map_bit": 2,
                      "depends_on_map_bits": [
                      ] /* depends_on_map_bits */
                    }
                  ] /* table_dependencies */
                },
                {
                  "ref_optimizer_key_uses": [
                    {
                      "table": "`t1`",
                      "field": "OBJECTID",
                      "equals": "`t3`.`ID`",
                      "null_rejecting": true
                    },
                    {
                      "table": "`t1`",
                      "field": "OBJECTID",
                      "equals": "`t2`.`ID`",
                      "null_rejecting": true
                    },
                    {
                      "table": "`t2`",
                      "field": "ID",
                      "equals": "`t1`.`OBJECTID`",
                      "null_rejecting": false
                    },
                    {
                      "table": "`t2`",
                      "field": "ID",
                      "equals": "`t3`.`ID`",
                      "null_rejecting": true
                    },
                    {
                      "table": "`t3`",
                      "field": "ID",
                      "equals": "`t1`.`OBJECTID`",
                      "null_rejecting": false
                    },
                    {
                      "table": "`t3`",
                      "field": "ID",
                      "equals": "`t2`.`ID`",
                      "null_rejecting": true
                    }
                  ] /* ref_optimizer_key_uses */
                },
                {
                  "rows_estimation": [
                    {
                      "table": "`t1`",
                      "rows": 1,
                      "cost": 1,
                      "table_type": "system",
                      "empty": true
                    },
                    {
                      "table": "`t2`",
                      "const_keys_added": {
                        "keys": [
                          "t2_PARID_IDX"
                        ] /* keys */,
                        "cause": "group_by"
                      } /* const_keys_added */,
                      "range_analysis": {
                        "table_scan": {
                          "rows": 9,
                          "cost": 5.9198
                        } /* table_scan */,
                        "potential_range_indexes": [
                          {
                            "index": "t2_ID_IDX",
                            "usable": false,
                            "cause": "not_applicable"
                          },
                          {
                            "index": "t2_PARID_IDX",
                            "usable": true,
                            "key_parts": [
                              "PARID"
                            ] /* key_parts */
                          }
                        ] /* potential_range_indexes */,
                        "setup_range_conditions": [
                        ] /* setup_range_conditions */,
                        "group_index_range": {
                          "chosen": false,
                          "cause": "not_single_table"
                        } /* group_index_range */
                      } /* range_analysis */
                    },
                    {
                      "table": "`t3`",
                      "table_scan": {
                        "rows": 8,
                        "cost": 2
                      } /* table_scan */
                    }
                  ] /* rows_estimation */
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                        "`t1`"
                      ] /* plan_prefix */,
                      "table": "`t3`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "t3_ID_IDX",
                            "rows": 1,
                            "cost": 1.2,
                            "chosen": true
                          },
                          {
                            "access_type": "scan",
                            "cost": 3.6,
                            "rows": 8,
                            "chosen": false,
                            "cause": "cost"
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 1,
                      "cost_for_plan": 1.2,
                      "rest_of_plan": [
                        {
                          "plan_prefix": [
                            "`t1`",
                            "`t3`"
                          ] /* plan_prefix */,
                          "table": "`t2`",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "ref",
                                "index": "t2_ID_IDX",
                                "rows": 2,
                                "cost": 2.4,
                                "chosen": true
                              },
                              {
                                "access_type": "scan",
                                "cost": 3.8,
                                "rows": 9,
                                "chosen": false,
                                "cause": "cost"
                              }
                            ] /* considered_access_paths */
                          } /* best_access_path */,
                          "added_to_eq_ref_extension": false
                        },
                        {
                          "plan_prefix": [
                            "`t1`",
                            "`t3`"
                          ] /* plan_prefix */,
                          "table": "`t2`",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "ref",
                                "index": "t2_ID_IDX",
                                "rows": 2,
                                "cost": 2.4,
                                "chosen": true
                              },
                              {
                                "access_type": "scan",
                                "cost": 3.8,
                                "rows": 9,
                                "chosen": false,
                                "cause": "cost"
                              }
                            ] /* considered_access_paths */
                          } /* best_access_path */,
                          "condition_filtering_pct": 100,
                          "rows_for_plan": 2,
                          "cost_for_plan": 3.6,
                          "chosen": true
                        }
                      ] /* rest_of_plan */
                    },
                    {
                      "plan_prefix": [
                        "`t1`"
                      ] /* plan_prefix */,
                      "table": "`t2`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "t2_ID_IDX",
                            "rows": 1,
                            "cost": 1.2,
                            "chosen": true
                          },
                          {
                            "access_type": "scan",
                            "cost": 3.8,
                            "rows": 9,
                            "chosen": false,
                            "cause": "cost"
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 1,
                      "cost_for_plan": 1.2,
                      "pruned_by_heuristic": true
                    }
                  ] /* considered_execution_plans */
                }
              ] /* steps */,
              "empty_result": {
                "cause": "no matching row in const table"
              } /* empty_result */
            } /* join_optimization */
          },
          {
            "table_dependencies": [
              {
                "table": " `tmp`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": " `tmp`",
                "table_scan": {
                  "rows": 2,
                  "cost": 10
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": " `tmp`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 10.5,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 10.5,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": " `tmp`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": " `tmp`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": " `tmp`",
                "row_length": 10,
                "key_length": 0,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 104857
              } /* tmp_table_info */
            } /* creating_tmp_table */
          },
          {
            "join_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* join_execution */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1,t2,t3;
CREATE TABLE t1 (
t1_id bigint(21) NOT NULL auto_increment,
_field_72 varchar(128) DEFAULT '' NOT NULL,
_field_95 varchar(32),
_field_115 tinyint(4) DEFAULT '0' NOT NULL,
_field_122 tinyint(4) DEFAULT '0' NOT NULL,
_field_126 tinyint(4),
_field_134 tinyint(4),
PRIMARY KEY (t1_id),
UNIQUE _field_72 (_field_72),
KEY _field_115 (_field_115),
KEY _field_122 (_field_122)
);
INSERT INTO t1 VALUES (1,'admin','21232f297a57a5a743894a0e4a801fc3',0,1,NULL,NULL);
INSERT INTO t1 VALUES (2,'hroberts','7415275a8c95952901e42b13a6b78566',0,1,NULL,NULL);
INSERT INTO t1 VALUES (3,'guest','d41d8cd98f00b204e9800998ecf8427e',1,0,NULL,NULL);
CREATE TABLE t2 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
PRIMARY KEY (seq_0_id,seq_1_id)
);
INSERT INTO t2 VALUES (1,1);
INSERT INTO t2 VALUES (2,1);
INSERT INTO t2 VALUES (2,2);
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Warnings:
Warning	3090	Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
CREATE TABLE t3 (
t3_id bigint(21) NOT NULL auto_increment,
_field_131 varchar(128),
_field_133 tinyint(4) DEFAULT '0' NOT NULL,
_field_135 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
_field_137 tinyint(4),
_field_139 datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
_field_140 blob,
_field_142 tinyint(4) DEFAULT '0' NOT NULL,
_field_145 tinyint(4) DEFAULT '0' NOT NULL,
_field_148 tinyint(4) DEFAULT '0' NOT NULL,
PRIMARY KEY (t3_id),
KEY _field_133 (_field_133),
KEY _field_135 (_field_135),
KEY _field_139 (_field_139),
KEY _field_142 (_field_142),
KEY _field_145 (_field_145),
KEY _field_148 (_field_148)
);
INSERT INTO t3 VALUES (1,'test job 1',0,'0000-00-00 00:00:00',0,'1999-02-25 22:43:32','test\r\njob\r\n1',0,0,0);
INSERT INTO t3 VALUES (2,'test job 2',0,'0000-00-00 00:00:00',0,'1999-02-26 21:08:04','',0,0,0);
SET sql_mode = default;
CREATE TABLE t4 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
PRIMARY KEY (seq_0_id,seq_1_id)
);
INSERT INTO t4 VALUES (1,1);
INSERT INTO t4 VALUES (2,1);
CREATE TABLE t5 (
t5_id bigint(21) NOT NULL auto_increment,
_field_149 tinyint(4),
_field_156 varchar(128) DEFAULT '' NOT NULL,
_field_157 varchar(128) DEFAULT '' NOT NULL,
_field_158 varchar(128) DEFAULT '' NOT NULL,
_field_159 varchar(128) DEFAULT '' NOT NULL,
_field_160 varchar(128) DEFAULT '' NOT NULL,
_field_161 varchar(128) DEFAULT '' NOT NULL,
PRIMARY KEY (t5_id),
KEY _field_156 (_field_156),
KEY _field_157 (_field_157),
KEY _field_158 (_field_158),
KEY _field_159 (_field_159),
KEY _field_160 (_field_160),
KEY _field_161 (_field_161)
);
INSERT INTO t5 VALUES (1,0,'tomato','','','','','');
INSERT INTO t5 VALUES (2,0,'cilantro','','','','','');
CREATE TABLE t6 (
seq_0_id bigint(21) DEFAULT '0' NOT NULL,
seq_1_id bigint(21) DEFAULT '0' NOT NULL,
PRIMARY KEY (seq_0_id,seq_1_id)
);
INSERT INTO t6 VALUES (1,1);
INSERT INTO t6 VALUES (1,2);
INSERT INTO t6 VALUES (2,2);
CREATE TABLE t7 (
t7_id bigint(21) NOT NULL auto_increment,
_field_143 tinyint(4),
_field_165 varchar(32),
_field_166 smallint(6) DEFAULT '0' NOT NULL,
PRIMARY KEY (t7_id),
KEY _field_166 (_field_166)
);
INSERT INTO t7 VALUES (1,0,'High',1);
INSERT INTO t7 VALUES (2,0,'Medium',2);
INSERT INTO t7 VALUES (3,0,'Low',3);
select
replace(t3._field_140, "\r","^M"),
t3_id,
min(t3._field_131),
min(t3._field_135),
min(t3._field_139),
min(t3._field_137),
min(link_alias_142._field_165),
min(link_alias_133._field_72),
min(t3._field_145),
min(link_alias_148._field_156),
replace(min(t3._field_140), "\r","^M"),
t3.t3_id
from
t3 left join t4 on t4.seq_0_id = t3.t3_id
left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
left join t6 on t6.seq_0_id = t3.t3_id
left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
left join t2 on t2.seq_0_id = t3.t3_id
left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
where
t3.t3_id in (1)
group by
t3.t3_id
order by
link_alias_142._field_166,
_field_139,
link_alias_133._field_72,
_field_135,
link_alias_148._field_156
;
replace(t3._field_140, "\r","^M")	t3_id	min(t3._field_131)	min(t3._field_135)	min(t3._field_139)	min(t3._field_137)	min(link_alias_142._field_165)	min(link_alias_133._field_72)	min(t3._field_145)	min(link_alias_148._field_156)	replace(min(t3._field_140), "\r","^M")	t3_id
test^M
job^M
1	1	test job 1	0000-00-00 00:00:00	1999-02-25 22:43:32	0	High	admin	0	tomato	test^M
job^M
1	1
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select
replace(t3._field_140, "\r","^M"),
t3_id,
min(t3._field_131),
min(t3._field_135),
min(t3._field_139),
min(t3._field_137),
min(link_alias_142._field_165),
min(link_alias_133._field_72),
min(t3._field_145),
min(link_alias_148._field_156),
replace(min(t3._field_140), "\r","^M"),
t3.t3_id
from
t3 left join t4 on t4.seq_0_id = t3.t3_id
left join t7 link_alias_142 on t4.seq_1_id = link_alias_142.t7_id
left join t6 on t6.seq_0_id = t3.t3_id
left join t1 link_alias_133 on t6.seq_1_id = link_alias_133.t1_id
left join t2 on t2.seq_0_id = t3.t3_id
left join t5 link_alias_148 on t2.seq_1_id = link_alias_148.t5_id
where
t3.t3_id in (1)
group by
t3.t3_id
order by
link_alias_142._field_166,
_field_139,
link_alias_133._field_72,
_field_135,
link_alias_148._field_156	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select replace(`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id`,min(`t3`.`_field_131`) AS `min(t3._field_131)`,min(`t3`.`_field_135`) AS `min(t3._field_135)`,min(`t3`.`_field_139`) AS `min(t3._field_139)`,min(`t3`.`_field_137`) AS `min(t3._field_137)`,min(`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`t3`.`_field_145`) AS `min(t3._field_145)`,min(`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id` from ((((((`t3` left join `t4` on((`t4`.`seq_0_id` = `t3`.`t3_id`))) left join `t7` `link_alias_142` on((`t4`.`seq_1_id` = `link_alias_142`.`t7_id`))) left join `t6` on((`t6`.`seq_0_id` = `t3`.`t3_id`))) left join `t1` `link_alias_133` on((`t6`.`seq_1_id` = `link_alias_133`.`t1_id`))) left join `t2` on((`t2`.`seq_0_id` = `t3`.`t3_id`))) left join `t5` `link_alias_148` on((`t2`.`seq_1_id` = `link_alias_148`.`t5_id`))) where (`t3`.`t3_id` = 1) group by `t3`.`t3_id` order by `link_alias_142`.`_field_166`,`t3`.`_field_139`,`link_alias_133`.`_field_72`,`t3`.`_field_135`,`link_alias_148`.`_field_156`"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select replace(`t3`.`_field_140`,'\\r','^M') AS `replace(t3._field_140, \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id`,min(`t3`.`_field_131`) AS `min(t3._field_131)`,min(`t3`.`_field_135`) AS `min(t3._field_135)`,min(`t3`.`_field_139`) AS `min(t3._field_139)`,min(`t3`.`_field_137`) AS `min(t3._field_137)`,min(`link_alias_142`.`_field_165`) AS `min(link_alias_142._field_165)`,min(`link_alias_133`.`_field_72`) AS `min(link_alias_133._field_72)`,min(`t3`.`_field_145`) AS `min(t3._field_145)`,min(`link_alias_148`.`_field_156`) AS `min(link_alias_148._field_156)`,replace(min(`t3`.`_field_140`),'\\r','^M') AS `replace(min(t3._field_140), \"\\r\",\"^M\")`,`t3`.`t3_id` AS `t3_id` from `t3` left join `t4` on((`t4`.`seq_0_id` = `t3`.`t3_id`)) left join `t7` `link_alias_142` on((`t4`.`seq_1_id` = `link_alias_142`.`t7_id`)) left join `t6` on((`t6`.`seq_0_id` = `t3`.`t3_id`)) left join `t1` `link_alias_133` on((`t6`.`seq_1_id` = `link_alias_133`.`t1_id`)) left join `t2` on((`t2`.`seq_0_id` = `t3`.`t3_id`)) left join `t5` `link_alias_148` on((`t2`.`seq_1_id` = `link_alias_148`.`t5_id`)) where (`t3`.`t3_id` = 1) group by `t3`.`t3_id` order by `link_alias_142`.`_field_166`,`t3`.`_field_139`,`link_alias_133`.`_field_72`,`t3`.`_field_135`,`link_alias_148`.`_field_156`"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t3`.`t3_id` = 1)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(1, `t3`.`t3_id`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t3`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t4`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t7` `link_alias_142`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                  0,
                  1
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t6`",
                "row_may_be_null": true,
                "map_bit": 3,
                "depends_on_map_bits": [
                  0,
                  1,
                  2
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1` `link_alias_133`",
                "row_may_be_null": true,
                "map_bit": 4,
                "depends_on_map_bits": [
                  0,
                  1,
                  2,
                  3
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2`",
                "row_may_be_null": true,
                "map_bit": 5,
                "depends_on_map_bits": [
                  0,
                  1,
                  2,
                  3,
                  4
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t5` `link_alias_148`",
                "row_may_be_null": true,
                "map_bit": 6,
                "depends_on_map_bits": [
                  0,
                  1,
                  2,
                  3,
                  4,
                  5
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t3`",
                "field": "t3_id",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`t4`",
                "field": "seq_0_id",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`t7` `link_alias_142`",
                "field": "t7_id",
                "equals": "`t4`.`seq_1_id`",
                "null_rejecting": true
              },
              {
                "table": "`t6`",
                "field": "seq_0_id",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`t1` `link_alias_133`",
                "field": "t1_id",
                "equals": "`t6`.`seq_1_id`",
                "null_rejecting": true
              },
              {
                "table": "`t2`",
                "field": "seq_0_id",
                "equals": "1",
                "null_rejecting": false
              },
              {
                "table": "`t5` `link_alias_148`",
                "field": "t5_id",
                "equals": "`t2`.`seq_1_id`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t3`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              },
              {
                "table": "`t4`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2,
                    "cost": 4.5083
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "seq_0_id",
                        "seq_1_id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "best_covering_index_scan": {
                    "index": "PRIMARY",
                    "cost": 1.4435,
                    "chosen": true
                  } /* best_covering_index_scan */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "1 <= seq_0_id <= 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 1.21,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 1,
                      "ranges": [
                        "1 <= seq_0_id <= 1"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              },
              {
                "table": "`t7` `link_alias_142`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t6`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3,
                    "cost": 4.7125
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "seq_0_id",
                        "seq_1_id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "best_covering_index_scan": {
                    "index": "PRIMARY",
                    "cost": 1.687,
                    "chosen": true
                  } /* best_covering_index_scan */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "1 <= seq_0_id <= 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 1.21,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 1,
                      "ranges": [
                        "1 <= seq_0_id <= 1"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              },
              {
                "table": "`t1` `link_alias_133`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t2`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3,
                    "cost": 4.7125
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "seq_0_id",
                        "seq_1_id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "best_covering_index_scan": {
                    "index": "PRIMARY",
                    "cost": 1.687,
                    "chosen": true
                  } /* best_covering_index_scan */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "1 <= seq_0_id <= 1"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 1.21,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 1,
                      "ranges": [
                        "1 <= seq_0_id <= 1"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.21,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              },
              {
                "table": "`t5` `link_alias_148`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`t3`"
                ] /* plan_prefix */,
                "table": "`t4`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "PRIMARY"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t3`",
                      "`t4`"
                    ] /* plan_prefix */,
                    "table": "`t7` `link_alias_142`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 1.2,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "cost": 2.6,
                          "rows": 3,
                          "chosen": false,
                          "cause": "cost"
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "added_to_eq_ref_extension": true,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.4,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t3`",
                          "`t4`",
                          "`t7` `link_alias_142`"
                        ] /* plan_prefix */,
                        "table": "`t6`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "PRIMARY",
                              "rows": 1,
                              "cost": 1.2,
                              "chosen": true
                            },
                            {
                              "access_type": "range",
                              "range_details": {
                                "used_index": "PRIMARY"
                              } /* range_details */,
                              "chosen": false,
                              "cause": "heuristic_index_cheaper"
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "added_to_eq_ref_extension": true,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 1,
                        "cost_for_plan": 3.6,
                        "rest_of_plan": [
                          {
                            "plan_prefix": [
                              "`t3`",
                              "`t4`",
                              "`t7` `link_alias_142`",
                              "`t6`"
                            ] /* plan_prefix */,
                            "table": "`t1` `link_alias_133`",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "eq_ref",
                                  "index": "PRIMARY",
                                  "rows": 1,
                                  "cost": 1.2,
                                  "chosen": true
                                },
                                {
                                  "access_type": "scan",
                                  "cost": 2.6,
                                  "rows": 3,
                                  "chosen": false,
                                  "cause": "cost"
                                }
                              ] /* considered_access_paths */
                            } /* best_access_path */,
                            "added_to_eq_ref_extension": true,
                            "condition_filtering_pct": 100,
                            "rows_for_plan": 1,
                            "cost_for_plan": 4.8,
                            "rest_of_plan": [
                              {
                                "plan_prefix": [
                                  "`t3`",
                                  "`t4`",
                                  "`t7` `link_alias_142`",
                                  "`t6`",
                                  "`t1` `link_alias_133`"
                                ] /* plan_prefix */,
                                "table": "`t2`",
                                "best_access_path": {
                                  "considered_access_paths": [
                                    {
                                      "access_type": "ref",
                                      "index": "PRIMARY",
                                      "rows": 1,
                                      "cost": 1.2,
                                      "chosen": true
                                    },
                                    {
                                      "access_type": "range",
                                      "range_details": {
                                        "used_index": "PRIMARY"
                                      } /* range_details */,
                                      "chosen": false,
                                      "cause": "heuristic_index_cheaper"
                                    }
                                  ] /* considered_access_paths */
                                } /* best_access_path */,
                                "added_to_eq_ref_extension": true,
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 1,
                                "cost_for_plan": 6,
                                "rest_of_plan": [
                                  {
                                    "plan_prefix": [
                                      "`t3`",
                                      "`t4`",
                                      "`t7` `link_alias_142`",
                                      "`t6`",
                                      "`t1` `link_alias_133`",
                                      "`t2`"
                                    ] /* plan_prefix */,
                                    "table": "`t5` `link_alias_148`",
                                    "best_access_path": {
                                      "considered_access_paths": [
                                        {
                                          "access_type": "eq_ref",
                                          "index": "PRIMARY",
                                          "rows": 1,
                                          "cost": 1.2,
                                          "chosen": true
                                        },
                                        {
                                          "access_type": "scan",
                                          "cost": 2.4,
                                          "rows": 2,
                                          "chosen": false,
                                          "cause": "cost"
                                        }
                                      ] /* considered_access_paths */
                                    } /* best_access_path */,
                                    "added_to_eq_ref_extension": true,
                                    "condition_filtering_pct": 100,
                                    "rows_for_plan": 1,
                                    "cost_for_plan": 7.2,
                                    "chosen": true
                                  }
                                ] /* rest_of_plan */
                              }
                            ] /* rest_of_plan */
                          }
                        ] /* rest_of_plan */
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "condition_on_constant_tables": "1",
            "condition_value": true
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "1",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t4`",
                  "attached": null
                },
                {
                  "table": "`t7` `link_alias_142`",
                  "attached": null
                },
                {
                  "table": "`t6`",
                  "attached": null
                },
                {
                  "table": "`t1` `link_alias_133`",
                  "attached": null
                },
                {
                  "table": "`t2`",
                  "attached": null
                },
                {
                  "table": "`t5` `link_alias_148`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`link_alias_142`.`_field_166`,'1999-02-25 22:43:32',`link_alias_133`.`_field_72`,'0000-00-00 00:00:00',`link_alias_148`.`_field_156`",
              "items": [
                {
                  "item": "`link_alias_142`.`_field_166`"
                },
                {
                  "item": "'1999-02-25 22:43:32'",
                  "uses_only_constant_tables": true
                },
                {
                  "item": "`link_alias_133`.`_field_72`"
                },
                {
                  "item": "'0000-00-00 00:00:00'",
                  "uses_only_constant_tables": true
                },
                {
                  "item": "`link_alias_148`.`_field_156`"
                }
              ] /* items */,
              "resulting_clause_is_simple": false,
              "resulting_clause": "`link_alias_142`.`_field_166`,`link_alias_133`.`_field_72`,`link_alias_148`.`_field_156`"
            } /* clause_processing */
          },
          {
            "clause_processing": {
              "clause": "GROUP BY",
              "original_clause": "'1'",
              "items": [
                {
                  "item": "'1'",
                  "uses_only_constant_tables": true
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": ""
            } /* clause_processing */
          },
          {
            "refine_plan": [
              {
                "table": "`t4`"
              },
              {
                "table": "`t7` `link_alias_142`"
              },
              {
                "table": "`t6`"
              },
              {
                "table": "`t1` `link_alias_133`"
              },
              {
                "table": "`t2`"
              },
              {
                "table": "`t5` `link_alias_148`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1,t2,t3,t4,t5,t6,t7;
#
# Tracing of ORDER BY & GROUP BY simplification.
#
CREATE TABLE t1 (
pk INT, col_int_key INT,
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1)
);
INSERT INTO t1 VALUES
(10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'),
(15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'),
(20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'),
(25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w');
CREATE TABLE t2 (
pk INT, col_int_key INT,
col_varchar_key VARCHAR(1), col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES
(1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'),
(6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'),
(11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'),
(16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'),
(21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'),
(26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'),
(31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'),
(36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'),
(41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'),
(46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'),
(51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'),
(56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'),
(61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'),
(66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'),
(71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'),
(76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'),
(81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'),
(86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'),
(91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'),
(96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o');
SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk ;
SUM(alias2.col_varchar_nokey)	field2
0	1
0	2
0	3
0	4
0	5
0	6
0	7
0	9
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: 'f'
Warning	1292	Truncated incorrect DOUBLE value: 'e'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warning	1292	Truncated incorrect DOUBLE value: 'f'
Warning	1292	Truncated incorrect DOUBLE value: 'y'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warning	1292	Truncated incorrect DOUBLE value: 'b'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warning	1292	Truncated incorrect DOUBLE value: 'd'
Warning	1292	Truncated incorrect DOUBLE value: 'f'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warning	1292	Truncated incorrect DOUBLE value: 'b'
Warning	1292	Truncated incorrect DOUBLE value: 'e'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
Warning	1292	Truncated incorrect DOUBLE value: 'p'
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT SUM(alias2.col_varchar_nokey) , alias2.pk AS field2 FROM t1 AS alias1
STRAIGHT_JOIN t2 AS alias2 ON alias2.pk = alias1.col_int_key WHERE alias1.pk
GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select sum(`alias2`.`col_varchar_nokey`) AS `SUM(alias2.col_varchar_nokey)`,`alias2`.`pk` AS `field2` from (`t1` `alias1` straight_join `t2` `alias2` on((`alias2`.`pk` = `alias1`.`col_int_key`))) where `alias1`.`pk` group by `field2` order by `alias1`.`col_int_key`,`alias2`.`pk`"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select sum(`alias2`.`col_varchar_nokey`) AS `SUM(alias2.col_varchar_nokey)`,`alias2`.`pk` AS `field2` from `t1` `alias1` straight_join `t2` `alias2` where (`alias1`.`pk` and (`alias2`.`pk` = `alias1`.`col_int_key`)) group by `field2` order by `alias1`.`col_int_key`,`alias2`.`pk`"
            } /* transformations_to_nested_joins */
          },
          {
            "functional_dependencies_of_GROUP_columns": {
              "all_columns_of_table_map_bits": [
                1
              ] /* all_columns_of_table_map_bits */,
              "columns": [
                "test.alias2.pk",
                "test.alias1.col_int_key"
              ] /* columns */
            } /* functional_dependencies_of_GROUP_columns */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`alias1`.`pk` and (`alias2`.`pk` = `alias1`.`col_int_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`alias1`.`pk` and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`alias1`.`pk` and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`alias1`.`pk` and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1` `alias1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2` `alias2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t2` `alias2`",
                "field": "pk",
                "equals": "`alias1`.`col_int_key`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1` `alias1`",
                "table_scan": {
                  "rows": 20,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t2` `alias2`",
                "const_keys_added": {
                  "keys": [
                    "PRIMARY"
                  ] /* keys */,
                  "cause": "group_by"
                } /* const_keys_added */,
                "range_analysis": {
                  "table_scan": {
                    "rows": 100,
                    "cost": 24.588
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "pk"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_single_table"
                  } /* group_index_range */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1` `alias1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 20,
                      "access_type": "scan",
                      "resulting_rows": 18,
                      "cost": 6.0977,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 18,
                "cost_for_plan": 6.0977,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1` `alias1`"
                    ] /* plan_prefix */,
                    "table": "`t2` `alias2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 21.6,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 362.49,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 18,
                    "cost_for_plan": 27.698,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and `alias1`.`pk`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1` `alias1`",
                  "attached": "(`alias1`.`pk` and (`alias1`.`col_int_key` is not null))"
                },
                {
                  "table": "`t2` `alias2`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`",
              "items": [
                {
                  "item": "`alias1`.`col_int_key`"
                },
                {
                  "item": "`alias2`.`pk`",
                  "eq_ref_to_preceding_items": true
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`alias1`.`col_int_key`"
            } /* clause_processing */
          },
          {
            "clause_processing": {
              "clause": "GROUP BY",
              "original_clause": "`field2`",
              "items": [
                {
                  "item": "`alias2`.`pk`"
                }
              ] /* items */,
              "resulting_clause_is_simple": false,
              "resulting_clause": "`field2`"
            } /* clause_processing */
          },
          {
            "refine_plan": [
              {
                "table": "`t1` `alias1`"
              },
              {
                "table": "`t2` `alias2`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "table": "intermediate_tmp_table",
                "row_length": 18,
                "key_length": 4,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 58254
              } /* tmp_table_info */
            } /* creating_tmp_table */
          },
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "intermediate_tmp_table",
                "field": "col_int_key"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 8,
              "examined_rows": 8,
              "number_of_tmp_files": 0,
              "sort_buffer_size": "NNN",
              "sort_mode": "<sort_key, rowid>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
DROP TABLE t1,t2;
#
# Trace of "condition on constant tables"
#
create table t1(a int) engine=myisam;
insert into t1 values(26);
create table t2(b int primary key, c int) engine=myisam;
insert into t2 values(1,100),(2,200),(3,300);
select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2;
a	b	c
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from t1,t2 where t1.a+t2.c=cos(10) and t2.b=2	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t2`.`c` AS `c` from `t1` join `t2` where (((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and (`t2`.`b` = 2))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(((`t1`.`a` + `t2`.`c`) = cos(10)) and multiple equal(2, `t2`.`b`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t2`",
                "field": "b",
                "equals": "2",
                "null_rejecting": false
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              },
              {
                "table": "`t2`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ] /* rows_estimation */
          },
          {
            "condition_on_constant_tables": "(('26' + '200') = cos(10))",
            "condition_value": false
          }
        ] /* steps */,
        "empty_result": {
          "cause": "Impossible WHERE noticed after reading const tables"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1,t2;
#
# Trace of non-default db
#
create table t1(a int);
insert into t1 values(1),(2),(3);
create database mysqltest2;
create table mysqltest2.t2(a int);
insert into mysqltest2.t2 values(1),(2);
select * from t1,mysqltest2.t2;
a	a
1	1
1	2
2	1
2	2
3	1
3	2
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select * from t1,mysqltest2.t2	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`a` AS `a`,`mysqltest2`.`t2`.`a` AS `a` from `t1` join `mysqltest2`.`t2`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`mysqltest2`.`t2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`mysqltest2`.`t2`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`mysqltest2`.`t2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2,
                      "access_type": "scan",
                      "resulting_rows": 2,
                      "cost": 2.4034,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 2.4034,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`mysqltest2`.`t2`"
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 3,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 3,
                          "cost": 3.2052,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 6,
                    "cost_for_plan": 5.6086,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.6051,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 2.6051,
                "pruned_by_heuristic": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`mysqltest2`.`t2`",
                  "attached": null
                },
                {
                  "table": "`t1`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`mysqltest2`.`t2`"
              },
              {
                "table": "`t1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1;
drop database mysqltest2;