Gecko [ www.jdcpatala.org ]

Name	Size	Permission
bugs_no_prot_all.result	155.55 KB	-rw-r--r--
bugs_no_prot_none.result	122.25 KB	-rw-r--r--
bugs_ps_prot_all.result	147.92 KB	-rw-r--r--
bugs_ps_prot_none.result	115.04 KB	-rw-r--r--
charset.result	25.33 KB	-rw-r--r--
debug.result	1.56 KB	-rw-r--r--
eq_range_statistics.result	54.83 KB	-rw-r--r--
filesort_pack.result	42.27 KB	-rw-r--r--
filesort_pq.result	296.33 KB	-rw-r--r--
fulltext.result	4.29 KB	-rw-r--r--
general2_no_prot.result	185.58 KB	-rw-r--r--
general2_ps_prot.result	182.02 KB	-rw-r--r--
general_no_prot_all.result	422.31 KB	-rw-r--r--
general_no_prot_none.result	389.16 KB	-rw-r--r--
general_ps_prot_all.result	417.13 KB	-rw-r--r--
general_ps_prot_none.result	384.65 KB	-rw-r--r--
range_no_prot.result	258.23 KB	-rw-r--r--
range_ps_prot.result	242.39 KB	-rw-r--r--
security_no_prot.result	33.18 KB	-rw-r--r--
security_ps_prot.result	34.37 KB	-rw-r--r--
subquery_no_prot.result	140.82 KB	-rw-r--r--
subquery_ps_prot.result	133.14 KB	-rw-r--r--
temp_table.result	38.33 KB	-rw-r--r--

Code Editor : subquery_no_prot.result

SET optimizer_trace_max_mem_size=1048576;
SET end_markers_in_json=on;
SET optimizer_trace="enabled=on,one_line=off";
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t1 VALUES (2);
INSERT INTO t2 VALUES (1,7),(2,7);
# Subselect execute is traced every time it is executed
SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=on";
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
(SELECT a FROM t1 WHERE t1.a=t2.a)	a
NULL	1
2	2

SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`t2`.`a` AS `a` from `t2`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`t2`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": "..."
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t2`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t2`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 2,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t1`.`a` = `t2`.`a`)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t1`.`a` = `t2`.`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_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              }
            ] /* rows_estimation */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "('2' = `t2`.`a`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0

# Subselect execute is traced only the first time it is executed
SET @@optimizer_trace_features="greedy_search=off,repeated_subselect=off";
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2;
(SELECT a FROM t1 WHERE t1.a=t2.a)	a
NULL	1
2	2

SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT (SELECT a FROM t1 WHERE t1.a=t2.a), a FROM t2	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = `t2`.`a`)) AS `(SELECT a FROM t1 WHERE t1.a=t2.a)`,`t2`.`a` AS `a` from `t2`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`t2`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t2`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": "..."
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t2`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t2`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 2,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`t1`.`a` = `t2`.`a`)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`t1`.`a` = `t2`.`a`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`t1`.`a` = `t2`.`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_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              }
            ] /* rows_estimation */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "('2' = `t2`.`a`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
                {
                  "join_execution": {
                    "select#": 2,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0

DROP TABLE t1,t2;
SET @@optimizer_trace_features="default";
CREATE TABLE t1 (a FLOAT(5,4) zerofill);
CREATE TABLE t2 (a FLOAT(5,4),b FLOAT(2,0));
SELECT t1.a
FROM t1
WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
t1.a= (SELECT a FROM t2 LIMIT 1) ;
a

SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT t1.a
FROM t1
WHERE t1.a= (SELECT b FROM t2 LIMIT 1) AND NOT
t1.a= (SELECT a FROM t2 LIMIT 1)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select `t2`.`b` from `t2` limit 1"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "join_preparation": {
              "select#": 3,
              "steps": [
                {
                  "expanded_query": "/* select#3 */ select `t2`.`a` from `t2` limit 1"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select `t1`.`a` AS `a` from `t1` where ((`t1`.`a` = (/* select#2 */ select `t2`.`b` from `t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`t1`.`a` = (/* select#2 */ select `t2`.`b` from `t2` limit 1)) and (`t1`.`a` <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 2,
                              "steps": [
                                {
                                  "table_dependencies": [
                                    {
                                      "table": "`t2`",
                                      "row_may_be_null": false,
                                      "map_bit": 0,
                                      "depends_on_map_bits": [
                                      ] /* depends_on_map_bits */
                                    }
                                  ] /* table_dependencies */
                                },
                                {
                                  "rows_estimation": [
                                    {
                                      "table": "`t2`",
                                      "rows": 1,
                                      "cost": 1,
                                      "table_type": "system",
                                      "empty": true
                                    }
                                  ] /* rows_estimation */
                                }
                              ] /* steps */,
                              "empty_result": {
                                "cause": "no matching row in const table"
                              } /* empty_result */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 2,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    },
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                        ] /* steps */
                      } /* subselect_execution */
                    }
                  ] /* subselect_evaluation */,
                  "resulting_condition": "((NULL <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `t2` limit 1), `t1`.`a`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "((NULL <> (/* select#3 */ select `t2`.`a` from `t2` limit 1)) and multiple equal((/* select#2 */ select NULL from `t2` limit 1), `t1`.`a`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": null
                }
              ] /* steps */
            } /* condition_processing */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "Impossible WHERE"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 3,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`t2`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t2`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              }
            ] /* rows_estimation */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0

SELECT 1 FROM DUAL
WHERE NOT EXISTS
(SELECT * FROM t2 WHERE a = 50 AND b = 3);
1
1

SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT 1 FROM DUAL
WHERE NOT EXISTS
(SELECT * FROM t2 WHERE a = 50 AND b = 3)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3))"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL  where (not(exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3)))))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(not(exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3)))))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3)))))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(exists(/* select#2 */ select 1 from `t2` where ((`t2`.`a` = 50) and (`t2`.`b` = 3)))))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 2,
                              "steps": [
                                {
                                  "condition_processing": {
                                    "condition": "WHERE",
                                    "original_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))",
                                    "steps": [
                                      {
                                        "transformation": "equality_propagation",
                                        "resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
                                      },
                                      {
                                        "transformation": "constant_propagation",
                                        "resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
                                      },
                                      {
                                        "transformation": "trivial_condition_removal",
                                        "resulting_condition": "((`t2`.`a` = 50) and (`t2`.`b` = 3))"
                                      }
                                    ] /* steps */
                                  } /* condition_processing */
                                },
                                {
                                  "substitute_generated_columns": {
                                  } /* substitute_generated_columns */
                                },
                                {
                                  "table_dependencies": [
                                    {
                                      "table": "`t2`",
                                      "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": "`t2`",
                                      "rows": 1,
                                      "cost": 1,
                                      "table_type": "system",
                                      "empty": true
                                    }
                                  ] /* rows_estimation */
                                }
                              ] /* steps */,
                              "empty_result": {
                                "cause": "no matching row in const table"
                              } /* empty_result */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 2,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    }
                  ] /* subselect_evaluation */,
                  "resulting_condition": null
                }
              ] /* steps */
            } /* condition_processing */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0

SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b);
1
1

SELECT * FROM information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT DISTINCT(a) FROM t2 GROUP BY a ORDER BY b)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "transformations_to_subquery": [
                    "removed_ordering",
                    "removed_distinct",
                    "removed_grouping"
                  ] /* transformations_to_subquery */
                },
                {
                  "expanded_query": "/* select#2 */ select `t2`.`a` from `t2`"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select 1 AS `1` from DUAL  where (not(exists(/* select#2 */ select `t2`.`a` from `t2`)))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(not(exists(/* select#2 */ select `t2`.`a` from `t2`)))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(exists(/* select#2 */ select `t2`.`a` from `t2`)))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(exists(/* select#2 */ select `t2`.`a` from `t2`)))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 2,
                              "steps": [
                                {
                                  "table_dependencies": [
                                    {
                                      "table": "`t2`",
                                      "row_may_be_null": false,
                                      "map_bit": 0,
                                      "depends_on_map_bits": [
                                      ] /* depends_on_map_bits */
                                    }
                                  ] /* table_dependencies */
                                },
                                {
                                  "rows_estimation": [
                                    {
                                      "table": "`t2`",
                                      "rows": 1,
                                      "cost": 1,
                                      "table_type": "system",
                                      "empty": true
                                    }
                                  ] /* rows_estimation */
                                }
                              ] /* steps */,
                              "empty_result": {
                                "cause": "no matching row in const table"
                              } /* empty_result */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 2,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    }
                  ] /* subselect_evaluation */,
                  "resulting_condition": null
                }
              ] /* steps */
            } /* condition_processing */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0

DROP TABLE t1,t2;
#
# BUG#12905521 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON SELECT
# DISTINCT/MIN/JOIN/SUBQ QUERY
#
CREATE TABLE t1 (
pk INTEGER,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key,col_int_key)
) ENGINE=MYISAM;
CREATE TABLE t2 (
pk INTEGER,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key,col_int_key)
) ENGINE=MYISAM;
CREATE TABLE t3 (
pk INTEGER,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_time_key TIME,
col_datetime_nokey DATETIME,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_time_key),
KEY (col_varchar_key,col_int_key)
) ENGINE=MYISAM;
CREATE TABLE t4 (
pk INTEGER,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_date_key DATE,
col_date_nokey DATE,
col_time_key TIME,
col_time_nokey TIME,
col_datetime_key DATETIME,
col_datetime_nokey DATETIME,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key,col_int_key)
) ENGINE=MYISAM;
INSERT IGNORE INTO t4 (
col_int_key,col_int_nokey,
col_date_key,col_date_nokey,
col_time_key,col_time_nokey,
col_datetime_key,col_datetime_nokey,
col_varchar_key,col_varchar_nokey
) VALUES
(8,7,'2008-10-02','2008-10-02','04:07:22.028954','04:07:22.028954','2001-10-08 00:00:00','2001-10-08 00:00:00','g','g');
Warnings:
Warning	1364	Field 'pk' doesn't have a default value
CREATE TABLE t5 (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_date_key DATE,
col_date_nokey DATE,
col_time_key TIME,
col_time_nokey TIME,
col_datetime_key DATETIME,
col_datetime_nokey DATETIME,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_varchar_key,col_int_key)
) ENGINE=MYISAM;
INSERT INTO t5 (
col_int_key,col_int_nokey,
col_date_key,col_date_nokey,
col_time_key,col_time_nokey,
col_datetime_key,col_datetime_nokey,
col_varchar_key,col_varchar_nokey
) VALUES
(8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
(7,8,'2008-05-03','2008-05-03','10:19:31.050677','10:19:31.050677','2007-10-06 17:56:40.056051','2007-10-06 17:56:40.056051','d','d'),
(8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
set @old_opt_switch=@@optimizer_switch;
select distinct
alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
(alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
(sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
(sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
alias1.`col_int_key` not in (214) group by field1,field2,field3,
field4,field5,field6;
field1	field2	field3	field4	field5	field6
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select distinct
alias1.`col_varchar_key` as field1 ,alias1.`col_date_key` as
field2 ,( select min( sq1_alias1.`col_varchar_nokey` ) as sq1_field1 from ( t1
as sq1_alias1 inner join ( t5 as sq1_alias2 left join t5 as sq1_alias3 on
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) on
(sq1_alias3.`col_varchar_nokey` = sq1_alias2.`col_varchar_key` ) ) where
exists ( select distinct c_sq1_alias2.`col_int_nokey` as c_sq1_field1 from (
t3 as c_sq1_alias1 right join t4 as c_sq1_alias2 on (c_sq1_alias2.`col_int_nokey` = c_sq1_alias1.`pk` ) ) where
c_sq1_alias2.`col_varchar_key` = sq1_alias2.`col_varchar_nokey` ) ) as field3
,( select max( sq2_alias1.`pk` ) as sq2_field1 from t5 as sq2_alias1 ) as
field4 ,alias2.`col_varchar_nokey` as field5 ,alias2.`col_varchar_nokey` as
field6 from ( t5 as alias1 right outer join ( ( ( select sq3_alias2.* from ( t5 as sq3_alias1 ,t4 as sq3_alias2 ) ) as alias2 right join t4
as alias3 on (alias3.`col_varchar_key` = alias2.`col_varchar_key` ) ) ) on
(alias3.`col_int_key` = alias2.`pk` ) ) where ( alias1.`col_varchar_nokey` in
( select sq4_alias1.`col_varchar_key` as sq4_field1 from ( t3 as sq4_alias1
inner join ( t2 as sq4_alias2 right outer join t3 as sq4_alias3 on
(sq4_alias3.`pk` = sq4_alias2.`col_int_key` ) ) on
(sq4_alias3.`col_varchar_nokey` = sq4_alias2.`col_varchar_key` ) ) where
sq4_alias2.`col_int_key` < alias1.`col_int_nokey` and
sq4_alias3.`col_varchar_nokey` <> alias1.`col_varchar_key` ) ) and
alias1.`col_int_key` not in (214) group by field1,field2,field3,
field4,field5,field6	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 5,
              "steps": [
                {
                  "expanded_query": "/* select#5 */ select `sq3_alias2`.`pk` AS `pk`,`sq3_alias2`.`col_int_nokey` AS `col_int_nokey`,`sq3_alias2`.`col_int_key` AS `col_int_key`,`sq3_alias2`.`col_date_key` AS `col_date_key`,`sq3_alias2`.`col_date_nokey` AS `col_date_nokey`,`sq3_alias2`.`col_time_key` AS `col_time_key`,`sq3_alias2`.`col_time_nokey` AS `col_time_nokey`,`sq3_alias2`.`col_datetime_key` AS `col_datetime_key`,`sq3_alias2`.`col_datetime_nokey` AS `col_datetime_nokey`,`sq3_alias2`.`col_varchar_key` AS `col_varchar_key`,`sq3_alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from (`t5` `sq3_alias1` join `t4` `sq3_alias2`)"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "derived": {
              "table": "``.`` `alias2`",
              "select#": 5,
              "merged": true
            } /* derived */
          },
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "join_preparation": {
                    "select#": 3,
                    "steps": [
                      {
                        "transformations_to_subquery": [
                          "removed_distinct"
                        ] /* transformations_to_subquery */
                      },
                      {
                        "expanded_query": "/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
                      }
                    ] /* steps */
                  } /* join_preparation */
                },
                {
                  "expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "join_preparation": {
              "select#": 4,
              "steps": [
                {
                  "expanded_query": "/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "join_preparation": {
              "select#": 6,
              "steps": [
                {
                  "expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`))"
                },
                {
                  "transformation": {
                    "select#": 6,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": false
                  } /* transformation */
                },
                {
                  "transformation": {
                    "select#": 6,
                    "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 `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from (`t1` `sq1_alias1` join (`t5` `sq1_alias2` left join `t5` `sq1_alias3` on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) on((`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) where exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from (`t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`))) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from ((`t4` `alias3` left join ((`t5` `sq3_alias1` join `t4` `sq3_alias2`)) on((`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))) left join `t5` `alias1` on((`alias3`.`col_int_key` = `sq3_alias2`.`pk`))) where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from (`t3` `sq4_alias1` join (`t3` `sq4_alias3` left join `t2` `sq4_alias2` on((`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))) on((`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`))) where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`)))) and (`alias1`.`col_int_key` <> 214)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "outer_join_to_inner_join",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))"
            } /* transformations_to_nested_joins */
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
            } /* transformations_to_nested_joins */
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "outer_join_to_inner_join",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `t1` `sq1_alias1` join `t5` `sq1_alias2` join `t5` `sq1_alias3` where (exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))"
            } /* transformations_to_nested_joins */
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "outer_join_to_inner_join",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `alias1`.`col_varchar_key` AS `field1`,`alias1`.`col_date_key` AS `field2`,(/* select#2 */ select min(`sq1_alias1`.`col_varchar_nokey`) AS `sq1_field1` from `t1` `sq1_alias1` join `t5` `sq1_alias2` join `t5` `sq1_alias3` where (exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))) AS `field3`,(/* select#4 */ select max(`sq2_alias1`.`pk`) AS `sq2_field1` from `t5` `sq2_alias1`) AS `field4`,`sq3_alias2`.`col_varchar_nokey` AS `field5`,`sq3_alias2`.`col_varchar_nokey` AS `field6` from `t4` `alias3` join `t5` `sq3_alias1` join `t4` `sq3_alias2` join `t5` `alias1` where (<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and (`alias3`.`col_int_key` = `sq3_alias2`.`pk`) and (`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`)) group by `field1`,`field2`,`field3`,`field4`,`field5`,`field6`"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and (`alias3`.`col_int_key` = `sq3_alias2`.`pk`) and (`alias3`.`col_varchar_key` = `sq3_alias2`.`col_varchar_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<in_optimizer>(`alias1`.`col_varchar_nokey`,<exists>(/* select#6 */ select `sq4_alias1`.`col_varchar_key` AS `sq4_field1` from `t3` `sq4_alias1` join `t3` `sq4_alias3` join `t2` `sq4_alias2` where ((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`)))) and (`alias1`.`col_int_key` <> 214) and multiple equal(`alias3`.`col_int_key`, `sq3_alias2`.`pk`) and multiple equal(`alias3`.`col_varchar_key`, `sq3_alias2`.`col_varchar_key`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t5` `alias1`",
                "row_may_be_null": true,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t5` `sq3_alias1`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t4` `sq3_alias2`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t4` `alias3`",
                "row_may_be_null": false,
                "map_bit": 3,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t4` `sq3_alias2`",
                "field": "pk",
                "equals": "`alias3`.`col_int_key`",
                "null_rejecting": true
              },
              {
                "table": "`t4` `sq3_alias2`",
                "field": "col_varchar_key",
                "equals": "`alias3`.`col_varchar_key`",
                "null_rejecting": true
              },
              {
                "table": "`t4` `alias3`",
                "field": "col_varchar_key",
                "equals": "`sq3_alias2`.`col_varchar_key`",
                "null_rejecting": true
              },
              {
                "table": "`t4` `alias3`",
                "field": "col_int_key",
                "equals": "`sq3_alias2`.`pk`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t5` `alias1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3,
                    "cost": 4.7342
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "col_int_key",
                      "usable": true,
                      "key_parts": [
                        "col_int_key"
                      ] /* key_parts */
                    },
                    {
                      "index": "col_varchar_key",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "impossible_range": true
                } /* range_analysis */,
                "rows": 0,
                "cause": "impossible_where_condition"
              },
              {
                "table": "`t5` `sq3_alias1`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t4` `sq3_alias2`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              },
              {
                "table": "`t4` `alias3`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`t4` `sq3_alias2`",
                  "`t4` `alias3`",
                  "`t5` `alias1`"
                ] /* plan_prefix */,
                "table": "`t5` `sq3_alias1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.6342,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 2.6342,
                "chosen": true
              }
            ] /* considered_execution_plans */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 6,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and (`sq4_alias3`.`col_varchar_nokey` = `sq4_alias2`.`col_varchar_key`) and (`sq4_alias3`.`pk` = `sq4_alias2`.`col_int_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and multiple equal(`sq4_alias3`.`col_varchar_nokey`, `sq4_alias2`.`col_varchar_key`) and multiple equal(`sq4_alias3`.`pk`, `sq4_alias2`.`col_int_key`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and multiple equal(`sq4_alias3`.`col_varchar_nokey`, `sq4_alias2`.`col_varchar_key`) and multiple equal(`sq4_alias3`.`pk`, `sq4_alias2`.`col_int_key`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`sq4_alias2`.`col_int_key` < `alias1`.`col_int_nokey`) and (`sq4_alias3`.`col_varchar_nokey` <> `alias1`.`col_varchar_key`) and (<cache>(`alias1`.`col_varchar_nokey`) = `sq4_alias1`.`col_varchar_key`) and multiple equal(`sq4_alias3`.`col_varchar_nokey`, `sq4_alias2`.`col_varchar_key`) and multiple equal(`sq4_alias3`.`pk`, `sq4_alias2`.`col_int_key`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t3` `sq4_alias1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2` `sq4_alias2`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t3` `sq4_alias3`",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t3` `sq4_alias1`",
                "field": "col_varchar_key",
                "equals": "<cache>(`alias1`.`col_varchar_nokey`)",
                "null_rejecting": false
              },
              {
                "table": "`t2` `sq4_alias2`",
                "field": "col_varchar_key",
                "equals": "`sq4_alias3`.`col_varchar_nokey`",
                "null_rejecting": true
              },
              {
                "table": "`t2` `sq4_alias2`",
                "field": "col_int_key",
                "equals": "`sq4_alias3`.`pk`",
                "null_rejecting": false
              },
              {
                "table": "`t3` `sq4_alias3`",
                "field": "pk",
                "equals": "`sq4_alias2`.`col_int_key`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t3` `sq4_alias1`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              },
              {
                "table": "`t2` `sq4_alias2`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              },
              {
                "table": "`t3` `sq4_alias3`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              }
            ] /* rows_estimation */
          },
          {
            "transformation": {
              "select#": 6,
              "from": "IN (SELECT)",
              "to": "materialization",
              "possible": false,
              "cause": "correlated"
            } /* transformation */
          },
          {
            "transformation": {
              "select#": 6,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "put_1_in_SELECT_list": true
            } /* transformation */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 4,
        "steps": [
        ] /* steps */,
        "empty_result": {
          "cause": "Select tables optimized away"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 2,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`) and (`sq1_alias3`.`col_varchar_nokey` = `sq1_alias2`.`col_varchar_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`sq1_alias3`.`col_varchar_nokey`, `sq1_alias2`.`col_varchar_key`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`sq1_alias3`.`col_varchar_nokey`, `sq1_alias2`.`col_varchar_key`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(exists(/* select#3 */ select `c_sq1_alias2`.`col_int_nokey` AS `c_sq1_field1` from `t4` `c_sq1_alias2` left join `t3` `c_sq1_alias1` on((`c_sq1_alias2`.`col_int_nokey` = `c_sq1_alias1`.`pk`)) where (`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)) and multiple equal(`sq1_alias3`.`col_varchar_nokey`, `sq1_alias2`.`col_varchar_key`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1` `sq1_alias1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t5` `sq1_alias2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t5` `sq1_alias3`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t5` `sq1_alias2`",
                "field": "col_varchar_key",
                "equals": "`sq1_alias3`.`col_varchar_nokey`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1` `sq1_alias1`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              },
              {
                "table": "`t5` `sq1_alias2`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t5` `sq1_alias3`",
                "table_scan": {
                  "rows": 3,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`t1` `sq1_alias1`"
                ] /* plan_prefix */,
                "table": "`t5` `sq1_alias3`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.6342,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 2.6342,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1` `sq1_alias1`",
                      "`t5` `sq1_alias3`"
                    ] /* plan_prefix */,
                    "table": "`t5` `sq1_alias2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "col_varchar_key",
                          "rows": 2,
                          "cost": 7.2,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 3,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 3,
                          "cost": 3.8343,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 33.333,
                    "rows_for_plan": 3,
                    "cost_for_plan": 6.4685,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                  "`t1` `sq1_alias1`"
                ] /* plan_prefix */,
                "table": "`t5` `sq1_alias2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "col_varchar_key",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan",
                      "resulting_rows": 3,
                      "cost": 2.6342,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 2.6342,
                "pruned_by_heuristic": true
              }
            ] /* considered_execution_plans */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 3,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`c_sq1_alias2`.`col_varchar_key` = `sq1_alias2`.`col_varchar_nokey`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t3` `c_sq1_alias1`",
                "row_may_be_null": true,
                "map_bit": 0,
                "depends_on_map_bits": [
                  1
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t4` `c_sq1_alias2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t3` `c_sq1_alias1`",
                "field": "pk",
                "equals": "`c_sq1_alias2`.`col_int_nokey`",
                "null_rejecting": true
              },
              {
                "table": "`t4` `c_sq1_alias2`",
                "field": "col_varchar_key",
                "equals": "`sq1_alias2`.`col_varchar_nokey`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t3` `c_sq1_alias1`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              },
              {
                "table": "`t4` `c_sq1_alias2`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              }
            ] /* rows_estimation */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "('g' = `sq1_alias2`.`col_varchar_nokey`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
set optimizer_switch=@old_opt_switch;
drop table t1,t2,t3,t4,t5;
#
# BUG#12905758 - ASSERT IN OPT_TRACE_STMT::SYNTAX_ERROR ON
# SELECT/SUBQ/SUM QUERY
#
CREATE TABLE t1 (
pk INTEGER AUTO_INCREMENT,
col_int_nokey INTEGER,
col_int_key INTEGER,
col_date_key DATE,
col_date_nokey DATE,
col_time_key TIME,
col_time_nokey TIME,
col_datetime_key DATETIME,
col_datetime_nokey DATETIME,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY (col_varchar_key,col_int_key)
) ENGINE=MYISAM;
INSERT INTO t1 (
col_int_key,col_int_nokey,
col_date_key,col_date_nokey,
col_time_key,col_time_nokey,
col_datetime_key,col_datetime_nokey,
col_varchar_key,col_varchar_nokey
) VALUES
(8,NULL,'2000-12-03','2000-12-03','22:55:23.019225','22:55:23.019225','2005-07-20 00:00:00','2005-07-20 00:00:00','x','x'),
(8,6,'2000-09-20','2000-09-20','14:11:27.044095','14:11:27.044095','2003-06-13 23:19:49.018300','2003-06-13 23:19:49.018300','c','c');
CREATE TABLE t2 (I INTEGER);
select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
(192,18) order by field1 desc;
field1
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
select ( select sum( subquery1_t1.`col_int_nokey` ) as subquery1_field1 from
t1 as subquery1_t1 ) as field1 from ( t1 as table1 straight_join t1 as table2
on (table2.`col_varchar_key` = table1.`col_varchar_key` ) ) where (
table2.`col_int_nokey` <> any ( select 5 from t2 ) ) and table1.`pk` in
(192,18) order by field1 desc	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "join_preparation": {
              "select#": 3,
              "steps": [
                {
                  "expanded_query": "/* select#3 */ select 5 from `t2`"
                },
                {
                  "transformation": {
                    "select#": 3,
                    "from": "IN (SELECT)",
                    "to": "EXISTS (CORRELATED SELECT)",
                    "chosen": true,
                    "evaluating_constant_where_conditions": [
                    ] /* evaluating_constant_where_conditions */
                  } /* transformation */
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from (`t1` `table1` straight_join `t1` `table2` on((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))) where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18))) order by `field1` desc"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select (/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`) AS `field1` from `t1` `table1` straight_join `t1` `table2` where (<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and (`table2`.`col_varchar_key` = `table1`.`col_varchar_key`)) order by `field1` desc"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and (`table2`.`col_varchar_key` = `table1`.`col_varchar_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)) and multiple equal(`table2`.`col_varchar_key`, `table1`.`col_varchar_key`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1` `table1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1` `table2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                  0
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1` `table1`",
                "field": "col_varchar_key",
                "equals": "`table2`.`col_varchar_key`",
                "null_rejecting": true
              },
              {
                "table": "`t1` `table2`",
                "field": "col_varchar_key",
                "equals": "`table1`.`col_varchar_key`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1` `table1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 2,
                    "cost": 4.5225
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "pk"
                      ] /* key_parts */
                    },
                    {
                      "index": "col_varchar_key",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "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": [
                          "18 <= pk <= 18",
                          "192 <= pk <= 192"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2,
                        "cost": 4.41,
                        "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": 2,
                      "ranges": [
                        "18 <= pk <= 18",
                        "192 <= pk <= 192"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 2,
                    "cost_for_plan": 4.41,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              },
              {
                "table": "`t1` `table2`",
                "table_scan": {
                  "rows": 2,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1` `table1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "col_varchar_key",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 2,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "PRIMARY"
                      } /* range_details */,
                      "resulting_rows": 2,
                      "cost": 4.81,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2,
                "cost_for_plan": 4.81,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1` `table1`"
                    ] /* plan_prefix */,
                    "table": "`t1` `table2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "col_varchar_key",
                          "rows": 2,
                          "cost": 4.8,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 2,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 2,
                          "cost": 2.8226,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 4,
                    "cost_for_plan": 7.6326,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))) and (`table1`.`pk` in (192,18)))",
              "attached_conditions_computation": [
                {
                  "table": "`t1` `table2`",
                  "rechecking_index_usage": {
                    "recheck_reason": "not_first_table",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 2,
                        "cost": 4.5225
                      } /* table_scan */,
                      "potential_range_indexes": [
                        {
                          "index": "PRIMARY",
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "col_varchar_key",
                          "usable": true,
                          "key_parts": [
                            "col_varchar_key",
                            "col_int_key"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indexes */,
                      "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": "col_varchar_key",
                            "chosen": false,
                            "cause": "depends_on_unread_values"
                          }
                        ] /* range_scan_alternatives */,
                        "analyzing_roworder_intersect": {
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        } /* analyzing_roworder_intersect */
                      } /* analyzing_range_alternatives */
                    } /* range_analysis */
                  } /* rechecking_index_usage */
                }
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1` `table1`",
                  "attached": "(`table1`.`pk` in (192,18))"
                },
                {
                  "table": "`t1` `table2`",
                  "attached": "((`table2`.`col_varchar_key` = `table1`.`col_varchar_key`) and <nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)))))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`field1` desc",
              "items": [
                {
                  "item": "(/* select#2 */ select sum(`subquery1_t1`.`col_int_nokey`) AS `subquery1_field1` from `t1` `subquery1_t1`)",
                  "subselect_evaluation": [
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 2,
                              "steps": [
                                {
                                  "table_dependencies": [
                                    {
                                      "table": "`t1` `subquery1_t1`",
                                      "row_may_be_null": false,
                                      "map_bit": 0,
                                      "depends_on_map_bits": [
                                      ] /* depends_on_map_bits */
                                    }
                                  ] /* table_dependencies */
                                },
                                {
                                  "rows_estimation": [
                                    {
                                      "table": "`t1` `subquery1_t1`",
                                      "table_scan": {
                                        "rows": 2,
                                        "cost": 2
                                      } /* table_scan */
                                    }
                                  ] /* rows_estimation */
                                },
                                {
                                  "considered_execution_plans": [
                                    {
                                      "plan_prefix": [
                                      ] /* plan_prefix */,
                                      "table": "`t1` `subquery1_t1`",
                                      "best_access_path": {
                                        "considered_access_paths": [
                                          {
                                            "rows_to_scan": 2,
                                            "access_type": "scan",
                                            "resulting_rows": 2,
                                            "cost": 2.4225,
                                            "chosen": true
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "condition_filtering_pct": 100,
                                      "rows_for_plan": 2,
                                      "cost_for_plan": 2.4225,
                                      "chosen": true
                                    }
                                  ] /* considered_execution_plans */
                                },
                                {
                                  "attaching_conditions_to_tables": {
                                    "original_condition": null,
                                    "attached_conditions_computation": [
                                    ] /* attached_conditions_computation */,
                                    "attached_conditions_summary": [
                                      {
                                        "table": "`t1` `subquery1_t1`",
                                        "attached": null
                                      }
                                    ] /* attached_conditions_summary */
                                  } /* attaching_conditions_to_tables */
                                },
                                {
                                  "refine_plan": [
                                    {
                                      "table": "`t1` `subquery1_t1`"
                                    }
                                  ] /* refine_plan */
                                }
                              ] /* steps */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 2,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    }
                  ] /* subselect_evaluation */,
                  "uses_only_constant_tables": true
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": ""
            } /* clause_processing */
          },
          {
            "refine_plan": [
              {
                "table": "`t1` `table1`",
                "pushed_index_condition": "(`table1`.`pk` in (192,18))",
                "table_condition_attached": null
              },
              {
                "table": "`t1` `table2`",
                "unknown_key_1": {
                  "constant_condition_in_bnl": "<nop>(<in_optimizer>(`table2`.`col_int_nokey`,<exists>(/* select#3 */ select 5 from `t2` where <if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true))))"
                }
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 3,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "<if>(outer_field_is_not_null, (<cache>(`table2`.`col_int_nokey`) <> 5), true)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t2`",
                "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": "`t2`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": true
              }
            ] /* rows_estimation */
          },
          {
            "transformation": {
              "select#": 3,
              "from": "IN (SELECT)",
              "to": "materialization",
              "possible": false,
              "cause": "not an IN predicate"
            } /* transformation */
          },
          {
            "transformation": {
              "select#": 3,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "put_1_in_SELECT_list": true
            } /* transformation */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "no matching row in const table"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1,t2;

#
# Tracing of semijoin loosescan
#
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, filler char(100), key(a,b));
insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
create table t2 as select * from t1;
set @old_opt_switch=@@optimizer_switch;
set optimizer_switch="firstmatch=off,materialization=off,duplicateweedout=off";
set @old_opt_prune_level=@@optimizer_prune_level;
set optimizer_prune_level=0;
explain select * from t2 where a in (select b from t1 where a=3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	5	const	8	100.00	Using index; LooseScan
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	100	10.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 3))
# Equality-propagation involving inner field => 1st sj equality is bound
explain select * from t2 where (b+0,a+0) in (select a,b from t1 where a=3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a	a	5	const	8	100.00	Using index; LooseScan
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = 3) and ((`test`.`t2`.`b` + 0) = 3) and ((`test`.`t2`.`a` + 0) = `test`.`t1`.`b`))
# Equality-propagation involving outer field => 3rd sj equality is bound.
explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1) and filler='abc';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	a	a	10	NULL	100	100.00	Using index; LooseScan
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t2`.`filler` = 'abc') and (`test`.`t2`.`filler` = (`test`.`t1`.`a` * 3)))
SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
"recalculate_access_paths_and_cost": {
                          "tables": [
                            {
                              "table": "`t1`",
                              "best_access_path": {
                                "considered_access_paths": [
                                  {
                                    "access_type": "ref",
                                    "index": "a",
                                    "usable": false,
                                    "chosen": false
                                  },
                                  {
                                    "rows_to_scan": 100,
                                    "access_type": "scan",
                                    "resulting_rows": 100,
                                    "cost": 24.661,
                                    "chosen": true
                                  }
                                ] /* considered_access_paths */
                              } /* best_access_path */,
                              "unknown_key_1": {
                                "searching_loose_scan_index": {
                                  "indexes": [
                                    {
                                      "index": "a",
                                      "covering_scan": {
                                        "cost": 4.1935,
                                        "chosen": true
                                      } /* covering_scan */
                                    }
                                  ] /* indexes */
                                } /* searching_loose_scan_index */
                              }
                            },
                            {
                              "table": "`t2`",
                              "best_access_path": {
                                "considered_access_paths": [
                                  {
                                    "rows_to_scan": 100,
                                    "access_type": "scan",
                                    "using_join_cache": true,
                                    "buffers_needed": 1,
                                    "resulting_rows": 10,
                                    "cost": 222.74,
                                    "chosen": true
                                  }
                                ] /* considered_access_paths */
                              } /* best_access_path */
                            }
                          ] /* tables */
                        } 
# Remove the condition on 'filler' => 3rd sj equality is not bound.
explain select * from t2 where (b,a,filler) in (select a,b,a*3 from t1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	100	100.00	Using where
1	SIMPLE	t1	NULL	ref	a	a	10	test.t2.b,test.t2.a	1	100.00	Using index; Start temporary; End temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`b` = `test`.`t2`.`a`) and (`test`.`t1`.`a` = `test`.`t2`.`b`) and (`test`.`t2`.`filler` = (`test`.`t2`.`b` * 3)))
SELECT show_json_object('"searching_loose_scan_index": {', TRACE)
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
show_json_object('"searching_loose_scan_index": {', TRACE)
"searching_loose_scan_index": {
                                  "indexes": [
                                    {
                                      "index": "a",
                                      "index_handles_needed_semijoin_equalities": false
                                    }
                                  ] /* indexes */
                                } 
# Equality-propagation involving outer field => 3rd sj equality is bound.
explain select * from t2 as t3, t2
where t2.filler=t3.filler and
(t2.b,t2.a,t2.filler) in (select a,b,a*3 from t1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	100	100.00	NULL
1	SIMPLE	t1	NULL	index	a	a	10	NULL	100	100.00	Using where; Using index; LooseScan
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler` AS `filler`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` `t3` semi join (`test`.`t1`) join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t2`.`filler` = `test`.`t3`.`filler`) and (`test`.`t3`.`filler` = (`test`.`t1`.`a` * 3)))
SELECT show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
show_json_object('"recalculate_access_paths_and_cost": {', TRACE)
"recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "a",
                                        "usable": false,
                                        "chosen": false
                                      },
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "resulting_rows": 100,
                                        "cost": 2466.1,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_1": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "a",
                                          "covering_scan": {
                                            "cost": 4.1935,
                                            "chosen": true
                                          } /* covering_scan */
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                },
                                {
                                  "table": "`t2`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "buffers_needed": 5,
                                        "resulting_rows": 100,
                                        "cost": 200026,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } 
# In plan t3-t1-t2, 3rd outer expression is dependent only on
# previous tables => 3rd sj equality is bound.
# If t1 is before t3, 3rd sj equality is not bound.
explain select * from t2 as t3 left join t2 on t2.filler+10=t3.filler+20
where (t2.b,t2.a,t3.filler+2) in (select a,b,a*3 from t1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	100	100.00	NULL
1	SIMPLE	t1	NULL	index	a	a	10	NULL	100	100.00	Using where; Using index; LooseScan
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	100	1.00	Using where; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`filler` AS `filler`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`filler` AS `filler` from `test`.`t2` `t3` semi join (`test`.`t1`) join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t1`.`a`) and ((`test`.`t3`.`filler` + 2) = (`test`.`t1`.`a` * 3)) and ((`test`.`t2`.`filler` + 10) = (`test`.`t3`.`filler` + 20)))
SELECT show_json_object('"considered_execution_plans": [', TRACE)
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
show_json_object('"considered_execution_plans": [', TRACE)
"considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t2` `t3`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 100,
                      "access_type": "scan",
                      "resulting_rows": 100,
                      "cost": 24.661,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100,
                "cost_for_plan": 24.661,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t2` `t3`"
                    ] /* plan_prefix */,
                    "table": "`t2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 2004.9,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10000,
                    "cost_for_plan": 2029.5,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t2` `t3`",
                          "`t2`"
                        ] /* plan_prefix */,
                        "table": "`t1`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12000,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 100,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 9,
                              "resulting_rows": 100,
                              "cost": 200044,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10000,
                        "cost_for_plan": 14030,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 18032,
                            "rows": 10000,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "chosen": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t2` `t3`"
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "usable": false,
                          "chosen": false
                        },
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 2004.9,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10000,
                    "cost_for_plan": 2029.5,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t2` `t3`",
                          "`t1`"
                        ] /* plan_prefix */,
                        "table": "`t2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 100,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 5,
                              "resulting_rows": 100,
                              "cost": 200026,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 1,
                        "rows_for_plan": 10000,
                        "cost_for_plan": 202055,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "LooseScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "a",
                                        "usable": false,
                                        "chosen": false
                                      },
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "resulting_rows": 100,
                                        "cost": 2466.1,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_1": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "a",
                                          "covering_scan": {
                                            "cost": 4.1935,
                                            "chosen": true
                                          } /* covering_scan */
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                },
                                {
                                  "table": "`t2`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "buffers_needed": 5,
                                        "resulting_rows": 100,
                                        "cost": 200026,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 202054,
                            "rows": 100,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 204077,
                            "rows": 100,
                            "duplicate_tables_left": false,
                            "chosen": false
                          }
                        ] /* semijoin_strategy_choice */,
                        "chosen": true,
                        "cause": "previous_plan_used_disabled_strategy"
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 100,
                      "access_type": "scan",
                      "resulting_rows": 100,
                      "cost": 24.661,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100,
                "cost_for_plan": 24.661,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t2`"
                    ] /* plan_prefix */,
                    "table": "`t2` `t3`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 2004.9,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10000,
                    "cost_for_plan": 2029.5,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t2`",
                          "`t2` `t3`"
                        ] /* plan_prefix */,
                        "table": "`t1`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "a",
                              "rows": 1,
                              "cost": 12000,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 100,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 9,
                              "resulting_rows": 100,
                              "cost": 200044,
                              "chosen": false
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10000,
                        "cost_for_plan": 14030,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 18032,
                            "rows": 10000,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "chosen": false,
                        "cause": "plan_uses_disabled_strategy"
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t2`"
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "a",
                          "rows": 1,
                          "cost": 120,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 2004.9,
                          "chosen": false
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 100,
                    "cost_for_plan": 144.66,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t2`",
                          "`t1`"
                        ] /* plan_prefix */,
                        "table": "`t2` `t3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 100,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 100,
                              "cost": 2004.9,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10000,
                        "cost_for_plan": 2149.5,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "LooseScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "a",
                                        "rows": 1,
                                        "cost": 120,
                                        "chosen": true
                                      },
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "resulting_rows": 100,
                                        "cost": 2466.1,
                                        "chosen": false
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_2": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "a",
                                          "index_handles_needed_semijoin_equalities": false
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "chosen": false
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 6151.5,
                            "rows": 10000,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "chosen": false,
                        "cause": "plan_uses_disabled_strategy"
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "a",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 100,
                      "access_type": "scan",
                      "resulting_rows": 100,
                      "cost": 24.661,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 100,
                "cost_for_plan": 24.661,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1`"
                    ] /* plan_prefix */,
                    "table": "`t2` `t3`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 2004.7,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 10000,
                    "cost_for_plan": 2029.3,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1`",
                          "`t2` `t3`"
                        ] /* plan_prefix */,
                        "table": "`t2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 100,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 5,
                              "resulting_rows": 100,
                              "cost": 200026,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 1,
                        "rows_for_plan": 10000,
                        "cost_for_plan": 202055,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "LooseScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "a",
                                        "usable": false,
                                        "chosen": false
                                      },
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "resulting_rows": 100,
                                        "cost": 24.661,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_3": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "a",
                                          "index_handles_needed_semijoin_equalities": false
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "chosen": false
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 204077,
                            "rows": 100,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t1`"
                    ] /* plan_prefix */,
                    "table": "`t2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 100,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 100,
                          "cost": 2004.7,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 1,
                    "rows_for_plan": 100,
                    "cost_for_plan": 2029.3,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1`",
                          "`t2`"
                        ] /* plan_prefix */,
                        "table": "`t2` `t3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 100,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 100,
                              "cost": 2004.9,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 10000,
                        "cost_for_plan": 4034.2,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "LooseScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "a",
                                        "usable": false,
                                        "chosen": false
                                      },
                                      {
                                        "rows_to_scan": 100,
                                        "access_type": "scan",
                                        "resulting_rows": 100,
                                        "cost": 24.661,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_4": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "a",
                                          "index_handles_needed_semijoin_equalities": false
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "chosen": false
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 6056.2,
                            "rows": 100,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "chosen": false,
                        "cause": "plan_uses_disabled_strategy"
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "final_semijoin_strategy": "LooseScan",
                "recalculate_access_paths_and_cost": {
                  "tables": [
                    {
                      "table": "`t1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "a",
                            "usable": false,
                            "chosen": false
                          },
                          {
                            "rows_to_scan": 100,
                            "access_type": "scan",
                            "resulting_rows": 100,
                            "cost": 2466.1,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "unknown_key_5": {
                        "searching_loose_scan_index": {
                          "indexes": [
                            {
                              "index": "a",
                              "covering_scan": {
                                "cost": 4.1935,
                                "chosen": true
                              } /* covering_scan */
                            }
                          ] /* indexes */
                        } /* searching_loose_scan_index */
                      }
                    },
                    {
                      "table": "`t2`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 100,
                            "access_type": "scan",
                            "using_join_cache": true,
                            "buffers_needed": 9,
                            "resulting_rows": 100,
                            "cost": 200044,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */
                    }
                  ] /* tables */
                } /* recalculate_access_paths_and_cost */
              }
            ] 
drop table t0,t1,t2;
#
# Discover bound equality thanks to equality propagation
# specific of ON clause.
#
CREATE TABLE t1 (
a int(11) DEFAULT NULL,
b varchar(100) DEFAULT NULL,
c int(11) DEFAULT NULL,
KEY b_c_a (b,c,a)
) ENGINE=InnoDB;
explain select *
from t1 left join t1 as t2
on (t2.a= t1.a and (t2.a,t2.b) in (select a,b from t1 as t3))
where t1.a < 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	b_c_a	113	NULL	1	100.00	Using where; Using index
1	SIMPLE	t3	NULL	index	b_c_a	b_c_a	113	NULL	1	100.00	Using where; Using index; LooseScan
1	SIMPLE	t2	NULL	ref	b_c_a	b_c_a	103	test.t3.b	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join (`test`.`t1` `t2` semi join (`test`.`t1` `t3`)) on(((`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and 1)) where (`test`.`t1`.`a` < 5)
drop table t1;
#
# Show that loosescan planning is not dependent on order of
# creation of indexes anymore.
#
create table it(a int, b int, index a_b (a,b), index a (a))
engine=InnoDB;
insert into it values(1,1),(2,3),(4,3);
select * from it as ot
where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
a	b
2	3
4	3
select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
from information_schema.optimizer_trace;
TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
0
drop table it;
create table it(a int, b int, index a (a),index a_b (a,b))
engine=InnoDB;
insert into it values(1,1),(2,3),(4,3);
select * from it as ot
where (ot.a,ot.b) in (select it.a,it.b from it where it.b=3);
a	b
2	3
4	3
select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
from information_schema.optimizer_trace;
TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
0
drop table it;
#
# Show that we reject LooseScan if no handled key parts
#
CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=INNODB;
CREATE TABLE t2 (a INT, b INT) ENGINE=INNODB;
EXPLAIN SELECT * FROM t2 AS t3, t2
WHERE t2.b=t3.b AND  
(t2.b) IN (SELECT b*3 FROM t1 WHERE a=10);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	t1	NULL	ref	a	a	5	const	1	100.00	Using where; Start temporary; End temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` `t3` semi join (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = 10) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` = (`test`.`t1`.`b` * 3)))
SELECT TRACE LIKE '%"some_index_part_used": false%'
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
TRACE LIKE '%"some_index_part_used": false%'
1
DROP TABLE t1,t2;
#
# Show that we detect a hole in sequence of key parts
#
CREATE TABLE ot1 (a INTEGER);
INSERT INTO ot1 VALUES (0),(1),(3),(7);
CREATE TABLE it1 (a VARCHAR(1), b INTEGER, KEY (a,b));
INSERT INTO it1 VALUES ('a',7), ('b',7);
CREATE TABLE it2 (a VARCHAR(1), b INTEGER, KEY (a,b));
INSERT INTO it2 VALUES ('a',7), ('b',7);
explain SELECT * FROM ot1
WHERE a IN (
SELECT it1.b
FROM it1 JOIN it2
ON it1.a = it2.a
);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	it1	NULL	index	a	a	9	NULL	2	100.00	Using index; Start temporary
1	SIMPLE	ot1	NULL	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	it2	NULL	index	a	a	9	NULL	2	50.00	Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`ot1`.`a` AS `a` from `test`.`ot1` semi join (`test`.`it1` join `test`.`it2`) where ((`test`.`it2`.`a` = `test`.`it1`.`a`) and (`test`.`ot1`.`a` = `test`.`it1`.`b`))
SELECT TRACE LIKE '%"index_can_remove_duplicates": false%'
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
TRACE LIKE '%"index_can_remove_duplicates": false%'
1
DROP TABLE ot1, it1, it2;
#
# Show that handled keyparts cannot be on prefix
#
create table t1 (a int, b varchar(100), key a_b (a,b));
insert into t1 values(25,'111111'),(25,'1111112');
explain select * from t1 as t2 where t2.b in (select b from t1 where a=25);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a_b	a_b	5	const	1	100.00	Using index; LooseScan
1	SIMPLE	t2	NULL	index	NULL	a_b	108	NULL	2	50.00	Using where; Using index; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t2` semi join (`test`.`t1`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 25))
select * from t1 as t2 where t2.b in (select b from t1 where a=25);
a	b
25	111111
25	1111112
alter table t1 drop key a_b, add key a_b_prefix (a,b(2));
explain select * from t1 as t2 where t2.b in (select b from t1 where a=25);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	a_b_prefix	a_b_prefix	5	const	1	100.00	Start temporary
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t2` semi join (`test`.`t1`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t1`.`a` = 25))
select * from t1 as t2 where t2.b in (select b from t1 where a=25);
a	b
25	111111
25	1111112
select TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
from information_schema.optimizer_trace;
TRACE LIKE '%"index_handles_needed_semijoin_equalities": false%'
1
drop table t1;
set optimizer_switch=@old_opt_switch;
set optimizer_prune_level=@old_opt_prune_level;
drop function show_json_object;

${this.title}

Code Editor : subquery_no_prot.result