opt_trace/r/bugs_no_prot_all.result

set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off';
set optimizer_trace_max_mem_size=1048576;
SET end_markers_in_json=on;
SET optimizer_trace="enabled=on,one_line=off";
#
# BUG#12430646 - SEL_ARG::LEFT AND RIGHT POINTERS INCORRECTLY
#                USED. CRASHES OPTIMIZER TRACING
#
CREATE TABLE t1 (
a INT,
b CHAR(2),
c INT,
d INT,
KEY (c),
KEY (d,a,b(2)),
KEY (b(1))
);
INSERT INTO t1 VALUES (NULL, 'a', 1, 2), (NULL, 'a', 1, 2),
(1,    'a', 1, 2), (1,    'a', 1, 2);
CREATE TABLE t2 (
a INT,
c INT,
e INT,
KEY (e)
);
INSERT INTO t2 VALUES (1, 1, NULL), (1, 1, NULL);
SELECT 1
FROM t1, t2
WHERE t1.d <> '1' AND t1.b > '1'
AND t1.a = t2.a AND t1.c = t2.c;
1
1
1
1
1
DROP TABLE t1, t2;
#
# BUG#12595210 - JSON SYNTAX ERROR ASSERT ON WHERE FIELD NOT IN SUBQUERY
#
CREATE TABLE t1 (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_key VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
CREATE TABLE t2 (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_key VARCHAR(1) DEFAULT NULL,
col_varchar_nokey VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
CREATE TABLE t3 (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_key VARCHAR(1) DEFAULT NULL,
col_varchar_nokey VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
CREATE TABLE t4 (
pk INT NOT NULL AUTO_INCREMENT,
col_int_key INT DEFAULT NULL,
col_varchar_key VARCHAR(1) DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
CREATE VIEW view_t4 AS SELECT * FROM t4;
CREATE TABLE where_subselect_19379 SELECT
(
SELECT SUM(sq1_alias1.pk) AS sq1_field1
FROM view_t4 AS sq1_alias1
INNER JOIN t1 AS sq1_alias2 ON  (sq1_alias2.col_varchar_key =
sq1_alias1.col_varchar_key )
) AS field1,
alias1.col_varchar_nokey AS field2
FROM (t2 AS alias1
LEFT JOIN ( t2 AS alias2
LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
alias2.col_varchar_key )
) ON (alias3.col_varchar_key = alias2.col_varchar_key)
)
WHERE  ( alias2.col_varchar_key IN (
SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
FROM t3 AS sq2_alias1
WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
)
);
SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
(SELECT
(
SELECT SUM(sq1_alias1.pk) AS sq1_field1
FROM view_t4 AS sq1_alias1
INNER JOIN t1 AS sq1_alias2 ON  (sq1_alias2.col_varchar_key =
sq1_alias1.col_varchar_key )
) AS field1,
alias1.col_varchar_nokey AS field2
FROM (t2 AS alias1
LEFT JOIN ( t2 AS alias2
LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
alias2.col_varchar_key )
) ON (alias3.col_varchar_key = alias2.col_varchar_key)
)
WHERE  ( alias2.col_varchar_key IN (
SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
FROM t3 AS sq2_alias1
WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
)
));
field1	field2
select * from information_schema.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
(SELECT
(
SELECT SUM(sq1_alias1.pk) AS sq1_field1
FROM view_t4 AS sq1_alias1
INNER JOIN t1 AS sq1_alias2 ON  (sq1_alias2.col_varchar_key =
sq1_alias1.col_varchar_key )
) AS field1,
alias1.col_varchar_nokey AS field2
FROM (t2 AS alias1
LEFT JOIN ( t2 AS alias2
LEFT OUTER JOIN t2 AS alias3 ON  (alias3.col_varchar_nokey =
alias2.col_varchar_key )
) ON (alias3.col_varchar_key = alias2.col_varchar_key)
)
WHERE  ( alias2.col_varchar_key IN (
SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
FROM t3 AS sq2_alias1
WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
)
))	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "join_preparation": {
                    "select#": 3,
                    "steps": [
                      {
                        "join_preparation": {
                          "select#": 5,
                          "steps": [
                            {
                              "expanded_query": "/* select#5 */ select `t4`.`pk` AS `pk`,`t4`.`col_int_key` AS `col_int_key`,`t4`.`col_varchar_key` AS `col_varchar_key` from `t4`"
                            }
                          ] /* steps */
                        } /* join_preparation */
                      },
                      {
                        "view": {
                          "table": "`view_t4` `sq1_alias1`",
                          "select#": 5,
                          "merged": true
                        } /* view */
                      },
                      {
                        "expanded_query": "/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))"
                      }
                    ] /* steps */
                  } /* join_preparation */
                },
                {
                  "join_preparation": {
                    "select#": 4,
                    "steps": [
                      {
                        "expanded_query": "/* select#4 */ select `sq2_alias1`.`col_varchar_nokey` AS `sq2_field1` from `t3` `sq2_alias1` where (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`)"
                      },
                      {
                        "transformation": {
                          "select#": 4,
                          "from": "IN (SELECT)",
                          "to": "semijoin",
                          "chosen": true
                        } /* transformation */
                      }
                    ] /* steps */
                  } /* join_preparation */
                },
                {
                  "expanded_query": "/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from (`t2` `alias1` left join (`t2` `alias2` left join `t2` `alias3` on((`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`))) on((`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) where `alias2`.`col_varchar_key` in (/* select#4 */ select `sq2_alias1`.`col_varchar_nokey` AS `sq2_field1` from `t3` `sq2_alias1` where (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`))"
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": false
                  } /* transformation */
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "EXISTS (CORRELATED SELECT)",
                    "chosen": true,
                    "evaluating_constant_where_conditions": [
                    ] /* evaluating_constant_where_conditions */,
                    "evaluating_constant_having_conditions": [
                    ] /* evaluating_constant_having_conditions */
                  } /* transformation */
                },
                {
                  "transformation": {
                    "select#": 4,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true,
                    "evaluating_constant_semijoin_conditions": [
                    ] /* evaluating_constant_semijoin_conditions */
                  } /* transformation */
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select `where_subselect_19379`.`field1` AS `field1`,`where_subselect_19379`.`field2` AS `field2` from `where_subselect_19379` where (not(<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from (`t2` `alias1` left join (`t2` `alias2` left join `t2` `alias3` on((`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`))) on((`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`))) semi join (`t3` `sq2_alias1`) where (1 and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))))), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from ((`t4`) join `t1` `sq1_alias2` on((`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))))))"
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)"
            } /* transformations_to_nested_joins */
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "semijoin",
                "outer_join_to_inner_join",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` semi join (`t3` `sq2_alias1`) join `t2` `alias2` join `t2` `alias3` where (1 and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(not(<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` semi join (`t3` `sq2_alias1`) join `t2` `alias2` join `t2` `alias3` where (1 and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))))))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` semi join (`t3` `sq2_alias1`) join `t2` `alias2` join `t2` `alias3` where (1 and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))))))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` semi join (`t3` `sq2_alias1`) join `t2` `alias2` join `t2` `alias3` where (1 and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))))))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(not(<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` semi join (`t3` `sq2_alias1`) join `t2` `alias2` join `t2` `alias3` where (1 and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))))))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`where_subselect_19379`",
                "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": "`where_subselect_19379`",
                "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_optimization": {
        "select#": 2,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(1 and <if>(outer_field_is_not_null, ((<cache>(NULL) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(NULL) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 and <if>(outer_field_is_not_null, ((<cache>(NULL) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(NULL) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and multiple equal(`alias2`.`col_varchar_key`, `sq2_alias1`.`col_varchar_nokey`, `alias3`.`col_varchar_key`, `alias3`.`col_varchar_nokey`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 and <if>(outer_field_is_not_null, ((<cache>(NULL) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(NULL) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and multiple equal(`alias2`.`col_varchar_key`, `sq2_alias1`.`col_varchar_nokey`, `alias3`.`col_varchar_key`, `alias3`.`col_varchar_nokey`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<if>(outer_field_is_not_null, ((<cache>(NULL) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(NULL) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and multiple equal(`alias2`.`col_varchar_key`, `sq2_alias1`.`col_varchar_nokey`, `alias3`.`col_varchar_key`, `alias3`.`col_varchar_nokey`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "condition_processing": {
              "condition": "HAVING",
              "original_condition": "(<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))",
              "steps": [
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t2` `alias1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2` `alias2`",
                "row_may_be_null": true,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t2` `alias3`",
                "row_may_be_null": true,
                "map_bit": 2,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t3` `sq2_alias1`",
                "row_may_be_null": false,
                "map_bit": 3,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "pulled_out_semijoin_tables": [
            ] /* pulled_out_semijoin_tables */
          },
          {
            "rows_estimation": [
              {
                "table": "`t2` `alias1`",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                } /* table_scan */
              },
              {
                "table": "`t2` `alias2`",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                } /* table_scan */
              },
              {
                "table": "`t2` `alias3`",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                } /* table_scan */
              },
              {
                "table": "`t3` `sq2_alias1`",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": [
              ] /* steps */
            } /* execution_plan_for_potential_materialization */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t2` `alias1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t2` `alias1`"
                    ] /* plan_prefix */,
                    "table": "`t2` `alias2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 1,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 1,
                          "cost": 1.2,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.4,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t2` `alias1`",
                          "`t2` `alias2`"
                        ] /* plan_prefix */,
                        "table": "`t2` `alias3`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 1,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 1,
                              "cost": 1.2,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 1,
                        "cost_for_plan": 3.6001,
                        "semijoin_strategy_choice": [
                        ] /* semijoin_strategy_choice */,
                        "rest_of_plan": [
                          {
                            "plan_prefix": [
                              "`t2` `alias1`",
                              "`t2` `alias2`",
                              "`t2` `alias3`"
                            ] /* plan_prefix */,
                            "table": "`t3` `sq2_alias1`",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "rows_to_scan": 1,
                                  "access_type": "scan",
                                  "using_join_cache": true,
                                  "buffers_needed": 1,
                                  "resulting_rows": 1,
                                  "cost": 1.2001,
                                  "chosen": true
                                }
                              ] /* considered_access_paths */
                            } /* best_access_path */,
                            "condition_filtering_pct": 100,
                            "rows_for_plan": 1,
                            "cost_for_plan": 4.8001,
                            "semijoin_strategy_choice": [
                              {
                                "strategy": "FirstMatch",
                                "recalculate_access_paths_and_cost": {
                                  "tables": [
                                    {
                                      "table": "`t3` `sq2_alias1`",
                                      "best_access_path": {
                                        "considered_access_paths": [
                                          {
                                            "rows_to_scan": 1,
                                            "access_type": "scan",
                                            "using_join_cache": true,
                                            "buffers_needed": 1,
                                            "resulting_rows": 1,
                                            "cost": 1.2001,
                                            "chosen": true
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */
                                    }
                                  ] /* tables */
                                } /* recalculate_access_paths_and_cost */,
                                "cost": 4.8001,
                                "rows": 1,
                                "chosen": true
                              },
                              {
                                "strategy": "DuplicatesWeedout",
                                "cost": 7.2001,
                                "rows": 1,
                                "duplicate_tables_left": false,
                                "chosen": false
                              }
                            ] /* semijoin_strategy_choice */,
                            "chosen": true
                          }
                        ] /* rest_of_plan */
                      },
                      {
                        "plan_prefix": [
                          "`t2` `alias1`",
                          "`t2` `alias2`"
                        ] /* plan_prefix */,
                        "table": "`t3` `sq2_alias1`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 1,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 1,
                              "cost": 1.2,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 100,
                        "rows_for_plan": 1,
                        "cost_for_plan": 3.6001,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "FirstMatch",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t3` `sq2_alias1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "rows_to_scan": 1,
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "buffers_needed": 1,
                                        "resulting_rows": 1,
                                        "cost": 1.2,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 3.6001,
                            "rows": 1,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 6.0001,
                            "rows": 1,
                            "duplicate_tables_left": false,
                            "chosen": false
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_heuristic": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t2` `alias1`"
                    ] /* plan_prefix */,
                    "table": "`t2` `alias3`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 1,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 1,
                          "cost": 1.2,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.4,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "pruned_by_heuristic": true
                  },
                  {
                    "plan_prefix": [
                      "`t2` `alias1`"
                    ] /* plan_prefix */,
                    "table": "`t3` `sq2_alias1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 1,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 1,
                          "cost": 1.2,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.4,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "pruned_by_heuristic": true
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t2` `alias2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t2` `alias3`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "pruned_by_heuristic": true
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t3` `sq2_alias1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "pruned_by_heuristic": true
              },
              {
                "final_semijoin_strategy": "FirstMatch",
                "recalculate_access_paths_and_cost": {
                  "tables": [
                    {
                      "table": "`t3` `sq2_alias1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 1,
                            "access_type": "scan",
                            "using_join_cache": true,
                            "buffers_needed": 1,
                            "resulting_rows": 1,
                            "cost": 1.2001,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */
                    }
                  ] /* tables */
                } /* recalculate_access_paths_and_cost */
              }
            ] /* considered_execution_plans */
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "materialization",
              "has_nullable_expressions": true,
              "treat_UNKNOWN_as_FALSE": false,
              "possible": false,
              "cause": "cannot_handle_partial_matches"
            } /* transformation */
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "EXISTS (CORRELATED SELECT)",
              "put_1_in_SELECT_list": true
            } /* transformation */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`) and (`sq2_alias1`.`col_varchar_nokey` = `alias2`.`col_varchar_key`) and <if>(outer_field_is_not_null, ((<cache>(NULL) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(NULL) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true) and (`alias2`.`col_varchar_key` <= `alias1`.`col_varchar_key`))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t2` `alias1`",
                  "attached": "(<if>(outer_field_is_not_null, ((<cache>(NULL) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or isnull((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)))), true) and <if>(outer_field_is_not_null, ((<cache>(NULL) = `alias1`.`col_varchar_nokey`) or isnull(`alias1`.`col_varchar_nokey`)), true))"
                },
                {
                  "table": "`t2` `alias2`",
                  "attached": "(`alias2`.`col_varchar_key` <= `alias1`.`col_varchar_key`)"
                },
                {
                  "table": "`t2` `alias3`",
                  "attached": "((`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`))"
                },
                {
                  "table": "`t3` `sq2_alias1`",
                  "attached": "(`sq2_alias1`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t2` `alias1`"
              },
              {
                "table": "`t2` `alias2`"
              },
              {
                "table": "`t2` `alias3`"
              },
              {
                "table": "`t3` `sq2_alias1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_optimization": {
        "select#": 3,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "multiple equal(`sq1_alias2`.`col_varchar_key`, `t4`.`col_varchar_key`)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "multiple equal(`sq1_alias2`.`col_varchar_key`, `t4`.`col_varchar_key`)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(`sq1_alias2`.`col_varchar_key`, `t4`.`col_varchar_key`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t4`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1` `sq1_alias2`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`t4`",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                } /* table_scan */
              },
              {
                "table": "`t1` `sq1_alias2`",
                "table_scan": {
                  "rows": 1,
                  "cost": 1
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t4`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t4`"
                    ] /* plan_prefix */,
                    "table": "`t1` `sq1_alias2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 1,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 1,
                          "cost": 1.2,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.4,
                    "chosen": true
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1` `sq1_alias2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 1.2,
                "pruned_by_heuristic": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t4`",
                  "attached": null
                },
                {
                  "table": "`t1` `sq1_alias2`",
                  "attached": "(`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t4`"
              },
              {
                "table": "`t1` `sq1_alias2`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
drop table t1,t2,t3,t4,where_subselect_19379;
drop view view_t4;
#
# BUG#12607524 JSON PARSE ERROR ON SELECT ... FROM ... WHERE .. IN (SUBQUERY)
#
CREATE TABLE t1 (
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (8,'g');
CREATE TABLE t2 (
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (7,'x');
CREATE TABLE where_subselect_19033
SELECT
( SELECT col_int_key FROM t2 ) as field1
FROM t1
;
SELECT * FROM where_subselect_19033;
field1
7
SELECT field1
FROM where_subselect_19033
WHERE field1 IN
( SELECT
( SELECT col_int_key FROM t2 )
FROM t1
)
;
field1
7
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT field1
FROM where_subselect_19033
WHERE field1 IN
( SELECT
( SELECT col_int_key FROM t2 )
FROM t1
)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "join_preparation": {
                    "select#": 3,
                    "steps": [
                      {
                        "expanded_query": "/* select#3 */ select `t2`.`col_int_key` from `t2`"
                      }
                    ] /* steps */
                  } /* join_preparation */
                },
                {
                  "expanded_query": "/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1`"
                },
                {
                  "transformation": {
                    "select#": 2,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  } /* transformation */
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select `where_subselect_19033`.`field1` AS `field1` from `where_subselect_19033` where `where_subselect_19033`.`field1` in (/* select#2 */ select (/* select#3 */ select `t2`.`col_int_key` from `t2`) from `t1`)"
          },
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "chosen": true,
              "evaluating_constant_semijoin_conditions": [
              ] /* evaluating_constant_semijoin_conditions */
            } /* transformation */
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "semijoin"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `where_subselect_19033`.`field1` AS `field1` from `where_subselect_19033` semi join (`t1`) where (1 and (`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(1 and (`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 and multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(1 and multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`where_subselect_19033`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "pulled_out_semijoin_tables": [
            ] /* pulled_out_semijoin_tables */
          },
          {
            "subselect_execution": {
              "select#": 3,
              "steps": [
                {
                  "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": false
                          }
                        ] /* rows_estimation */
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": null,
                          "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#": 3,
                    "steps": [
                    ] /* steps */
                  } /* join_execution */
                }
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "rows_estimation": [
              {
                "table": "`where_subselect_19033`",
                "rows": 1,
                "cost": 1,
                "table_type": "system",
                "empty": false
              },
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 1,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": [
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "rows_to_scan": 1,
                            "access_type": "scan",
                            "resulting_rows": 1,
                            "cost": 2.2049,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 1,
                      "cost_for_plan": 2.2049,
                      "chosen": true
                    }
                  ] /* considered_execution_plans */
                }
              ] /* steps */
            } /* execution_plan_for_potential_materialization */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                  "`where_subselect_19033`"
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "scan",
                      "resulting_rows": 1,
                      "cost": 2.2049,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 2.2049,
                "semijoin_strategy_choice": [
                  {
                    "strategy": "FirstMatch",
                    "recalculate_access_paths_and_cost": {
                      "tables": [
                      ] /* tables */
                    } /* recalculate_access_paths_and_cost */,
                    "cost": 2.2049,
                    "rows": 1,
                    "chosen": true
                  },
                  {
                    "strategy": "MaterializeLookup",
                    "cost": 4.6049,
                    "rows": 1,
                    "duplicate_tables_left": false,
                    "chosen": false
                  },
                  {
                    "strategy": "DuplicatesWeedout",
                    "cost": 4.6049,
                    "rows": 1,
                    "duplicate_tables_left": false,
                    "chosen": false
                  }
                ] /* semijoin_strategy_choice */,
                "chosen": true
              },
              {
                "final_semijoin_strategy": "FirstMatch",
                "recalculate_access_paths_and_cost": {
                  "tables": [
                  ] /* tables */
                } /* recalculate_access_paths_and_cost */
              }
            ] /* considered_execution_plans */
          },
          {
            "condition_on_constant_tables": "1",
            "condition_value": true
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "1",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
DROP TABLE where_subselect_19033,t1,t2;

# BUG#12612201 - SEGFAULT IN
# SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE

CREATE TABLE t1 (
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar_nokey varchar(1) DEFAULT NULL
);
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar_nokey varchar(1) DEFAULT NULL,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (1,4,'v','v'),(20,5,'r','r');
CREATE TABLE t3 (
col_int_key int(11) DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar_nokey varchar(1) DEFAULT NULL
);
INSERT INTO t3 VALUES (NULL,'j','j'),(8,'c','c');
CREATE TABLE where_updatedelete_20769 select  count(  alias2 . col_varchar_key ) as field1
from (
(select sq1_alias1 . *
from ( t3 as sq1_alias1
straight_join t1 as sq1_alias2
on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
)
where  sq1_alias1 . col_int_key  in (
select   c_sq1_alias1 . pk as c_sq1_field1
from t2 as c_sq1_alias1
)
) as alias1
left outer join t1 as alias2
on (alias2 . col_varchar_key = alias1 . col_varchar_key  )
)
where (  alias2 . col_varchar_key  in (
select   sq2_alias1 . col_varchar_nokey as sq2_field1
from t2 as sq2_alias1
where  sq2_alias1 . col_int_key  in (
select distinct  c_sq2_alias1 . col_int_key as c_sq2_field1
from t3 as c_sq2_alias1
)
) )
or alias1 . col_int_key = 2
and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
order by alias1 . col_varchar_key  , field1
;
UPDATE where_updatedelete_20769 SET field1 = ( select  count(  alias2 . col_varchar_key ) as field1
from (
(select sq1_alias1 . *
from ( t3 as sq1_alias1
straight_join t1 as sq1_alias2
on (sq1_alias2 . col_varchar_key = sq1_alias1 . col_varchar_key)
)
where  sq1_alias1 . col_int_key  in (
select   c_sq1_alias1 . pk as c_sq1_field1
from t2 as c_sq1_alias1
)
) as alias1
left outer join t1 as alias2
on (alias2 . col_varchar_key = alias1 . col_varchar_key  )
)
where (  alias2 . col_varchar_key  in (
select   sq2_alias1 . col_varchar_nokey as sq2_field1
from t2 as sq2_alias1
where  sq2_alias1 . col_int_key  in (
select distinct  c_sq2_alias1 . col_int_key as c_sq2_field1
from t3 as c_sq2_alias1
)
) )
or alias1 . col_int_key = 2
and alias2 . col_varchar_nokey <= alias1 . col_varchar_nokey
order by alias1 . col_varchar_key  , field1
 );
DROP TABLE where_updatedelete_20769;
DROP TABLE t1,t2,t3;

# BUG#12710761 - INVALID JSON TRACE ON SUBQUERY IN IN-CLAUSE

CREATE TABLE t1 (col_int_key int, KEY col_int_key (col_int_key));
INSERT INTO t1 VALUES (0),(8),(1),(8);
CREATE TABLE where_subselect_20070
SELECT table2 .col_int_key AS field1,
( SELECT COUNT( col_int_key )
FROM t1
)
FROM t1 AS table1
JOIN t1 AS table2
ON table2 .col_int_key = table1 .col_int_key;
SELECT *
FROM where_subselect_20070
WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
SELECT table2 .col_int_key AS field1,
( SELECT COUNT( col_int_key )
FROM t1
)
FROM t1 AS table1
JOIN t1 AS table2
ON table2 .col_int_key = table1 .col_int_key
);
field1	( SELECT COUNT( col_int_key )
FROM t1
)
0	4
1	4
8	4
8	4
8	4
8	4
select * from information_schema.optimizer_trace;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT *
FROM where_subselect_20070
WHERE (field1, ( SELECT COUNT( col_int_key ) FROM t1 )) IN (
SELECT table2 .col_int_key AS field1,
( SELECT COUNT( col_int_key )
FROM t1
)
FROM t1 AS table1
JOIN t1 AS table2
ON table2 .col_int_key = table1 .col_int_key
)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 3,
              "steps": [
                {
                  "join_preparation": {
                    "select#": 4,
                    "steps": [
                      {
                        "expanded_query": "/* select#4 */ select count(`t1`.`col_int_key`) from `t1`"
                      }
                    ] /* steps */
                  } /* join_preparation */
                },
                {
                  "expanded_query": "/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from (`t1` `table1` join `t1` `table2` on((`table2`.`col_int_key` = `table1`.`col_int_key`)))"
                },
                {
                  "join_preparation": {
                    "select#": 2,
                    "steps": [
                      {
                        "expanded_query": "/* select#2 */ select count(`t1`.`col_int_key`) from `t1`"
                      }
                    ] /* steps */
                  } /* join_preparation */
                },
                {
                  "transformation": {
                    "select#": 3,
                    "from": "IN (SELECT)",
                    "to": "semijoin",
                    "chosen": true
                  } /* transformation */
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select `where_subselect_20070`.`field1` AS `field1`,`where_subselect_20070`.`( SELECT COUNT( col_int_key )\nFROM t1\n)` AS `( SELECT COUNT( col_int_key )\nFROM t1\n)` from `where_subselect_20070` where (`where_subselect_20070`.`field1`,(/* select#2 */ select count(`t1`.`col_int_key`) from `t1`)) in (/* select#3 */ select `table2`.`col_int_key` AS `field1`,(/* select#4 */ select count(`t1`.`col_int_key`) from `t1`) from (`t1` `table1` join `t1` `table2` on((`table2`.`col_int_key` = `table1`.`col_int_key`))))"
          },
          {
            "transformation": {
              "select#": 3,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "chosen": true,
              "evaluating_constant_semijoin_conditions": [
              ] /* evaluating_constant_semijoin_conditions */
            } /* transformation */
          },
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "semijoin",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ] /* transformations */,
              "expanded_query": "/* select#1 */ select `where_subselect_20070`.`field1` AS `field1`,`where_subselect_20070`.`( SELECT COUNT( col_int_key )\nFROM t1\n)` AS `( SELECT COUNT( col_int_key )\nFROM t1\n)` from `where_subselect_20070` semi join (`t1` `table1` join `t1` `table2`) where (1 and (`where_subselect_20070`.`field1` = `table2`.`col_int_key`) and ((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))"
            } /* transformations_to_nested_joins */
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(1 and (`where_subselect_20070`.`field1` = `table2`.`col_int_key`) and ((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 and ((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 and ((/* select#2 */ select count(`t1`.`col_int_key`) from `t1`) = (/* select#4 */ select count(`t1`.`col_int_key`) from `t1`)) and multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 2,
                              "steps": [
                                {
                                  "table_dependencies": [
                                    {
                                      "table": "`t1`",
                                      "row_may_be_null": false,
                                      "map_bit": 0,
                                      "depends_on_map_bits": [
                                      ] /* depends_on_map_bits */
                                    }
                                  ] /* table_dependencies */
                                },
                                {
                                  "rows_estimation": [
                                    {
                                      "table": "`t1`",
                                      "table_scan": {
                                        "rows": 4,
                                        "cost": 2
                                      } /* table_scan */
                                    }
                                  ] /* rows_estimation */
                                },
                                {
                                  "considered_execution_plans": [
                                    {
                                      "plan_prefix": [
                                      ] /* plan_prefix */,
                                      "table": "`t1`",
                                      "best_access_path": {
                                        "considered_access_paths": [
                                          {
                                            "rows_to_scan": 4,
                                            "access_type": "scan",
                                            "resulting_rows": 4,
                                            "cost": 2.8068,
                                            "chosen": true
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "condition_filtering_pct": 100,
                                      "rows_for_plan": 4,
                                      "cost_for_plan": 2.8068,
                                      "chosen": true
                                    }
                                  ] /* considered_execution_plans */
                                },
                                {
                                  "attaching_conditions_to_tables": {
                                    "original_condition": null,
                                    "attached_conditions_computation": [
                                    ] /* attached_conditions_computation */,
                                    "attached_conditions_summary": [
                                      {
                                        "table": "`t1`",
                                        "attached": null
                                      }
                                    ] /* attached_conditions_summary */
                                  } /* attaching_conditions_to_tables */
                                },
                                {
                                  "refine_plan": [
                                    {
                                      "table": "`t1`"
                                    }
                                  ] /* refine_plan */
                                }
                              ] /* steps */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 2,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    },
                    {
                      "subselect_execution": {
                        "select#": 4,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 4,
                              "steps": [
                                {
                                  "table_dependencies": [
                                    {
                                      "table": "`t1`",
                                      "row_may_be_null": false,
                                      "map_bit": 0,
                                      "depends_on_map_bits": [
                                      ] /* depends_on_map_bits */
                                    }
                                  ] /* table_dependencies */
                                },
                                {
                                  "rows_estimation": [
                                    {
                                      "table": "`t1`",
                                      "table_scan": {
                                        "rows": 4,
                                        "cost": 2
                                      } /* table_scan */
                                    }
                                  ] /* rows_estimation */
                                },
                                {
                                  "considered_execution_plans": [
                                    {
                                      "plan_prefix": [
                                      ] /* plan_prefix */,
                                      "table": "`t1`",
                                      "best_access_path": {
                                        "considered_access_paths": [
                                          {
                                            "rows_to_scan": 4,
                                            "access_type": "scan",
                                            "resulting_rows": 4,
                                            "cost": 2.8068,
                                            "chosen": true
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "condition_filtering_pct": 100,
                                      "rows_for_plan": 4,
                                      "cost_for_plan": 2.8068,
                                      "chosen": true
                                    }
                                  ] /* considered_execution_plans */
                                },
                                {
                                  "attaching_conditions_to_tables": {
                                    "original_condition": null,
                                    "attached_conditions_computation": [
                                    ] /* attached_conditions_computation */,
                                    "attached_conditions_summary": [
                                      {
                                        "table": "`t1`",
                                        "attached": null
                                      }
                                    ] /* attached_conditions_summary */
                                  } /* attaching_conditions_to_tables */
                                },
                                {
                                  "refine_plan": [
                                    {
                                      "table": "`t1`"
                                    }
                                  ] /* refine_plan */
                                }
                              ] /* steps */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 4,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    }
                  ] /* subselect_evaluation */,
                  "resulting_condition": "multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`where_subselect_20070`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1` `table1`",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              },
              {
                "table": "`t1` `table2`",
                "row_may_be_null": false,
                "map_bit": 2,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1` `table1`",
                "field": "col_int_key",
                "equals": "`where_subselect_20070`.`field1`",
                "null_rejecting": true
              },
              {
                "table": "`t1` `table1`",
                "field": "col_int_key",
                "equals": "`table2`.`col_int_key`",
                "null_rejecting": true
              },
              {
                "table": "`t1` `table2`",
                "field": "col_int_key",
                "equals": "`where_subselect_20070`.`field1`",
                "null_rejecting": true
              },
              {
                "table": "`t1` `table2`",
                "field": "col_int_key",
                "equals": "`table1`.`col_int_key`",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "pulled_out_semijoin_tables": [
            ] /* pulled_out_semijoin_tables */
          },
          {
            "rows_estimation": [
              {
                "table": "`where_subselect_20070`",
                "table_scan": {
                  "rows": 6,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t1` `table1`",
                "table_scan": {
                  "rows": 4,
                  "cost": 2
                } /* table_scan */
              },
              {
                "table": "`t1` `table2`",
                "table_scan": {
                  "rows": 4,
                  "cost": 2
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "execution_plan_for_potential_materialization": {
              "steps": [
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1` `table1`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "col_int_key",
                            "usable": false,
                            "chosen": false
                          },
                          {
                            "rows_to_scan": 4,
                            "access_type": "scan",
                            "resulting_rows": 4,
                            "cost": 2.8068,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 4,
                      "cost_for_plan": 2.8068,
                      "rest_of_plan": [
                        {
                          "plan_prefix": [
                            "`t1` `table1`"
                          ] /* plan_prefix */,
                          "table": "`t1` `table2`",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "ref",
                                "index": "col_int_key",
                                "rows": 2,
                                "cost": 5.693,
                                "chosen": true
                              },
                              {
                                "rows_to_scan": 4,
                                "access_type": "scan",
                                "using_join_cache": true,
                                "buffers_needed": 1,
                                "resulting_rows": 4,
                                "cost": 5.207,
                                "chosen": true
                              }
                            ] /* considered_access_paths */
                          } /* best_access_path */,
                          "condition_filtering_pct": 25,
                          "rows_for_plan": 4,
                          "cost_for_plan": 8.0138,
                          "chosen": true
                        }
                      ] /* rest_of_plan */
                    },
                    {
                      "plan_prefix": [
                      ] /* plan_prefix */,
                      "table": "`t1` `table2`",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "ref",
                            "index": "col_int_key",
                            "usable": false,
                            "chosen": false
                          },
                          {
                            "rows_to_scan": 4,
                            "access_type": "scan",
                            "resulting_rows": 4,
                            "cost": 2.8068,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 4,
                      "cost_for_plan": 2.8068,
                      "rest_of_plan": [
                        {
                          "plan_prefix": [
                            "`t1` `table2`"
                          ] /* plan_prefix */,
                          "table": "`t1` `table1`",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "ref",
                                "index": "col_int_key",
                                "rows": 2,
                                "cost": 5.693,
                                "chosen": true
                              },
                              {
                                "rows_to_scan": 4,
                                "access_type": "scan",
                                "using_join_cache": true,
                                "buffers_needed": 1,
                                "resulting_rows": 4,
                                "cost": 5.207,
                                "chosen": true
                              }
                            ] /* considered_access_paths */
                          } /* best_access_path */,
                          "condition_filtering_pct": 25,
                          "rows_for_plan": 4,
                          "cost_for_plan": 8.0138,
                          "pruned_by_cost": true
                        }
                      ] /* rest_of_plan */
                    }
                  ] /* considered_execution_plans */
                }
              ] /* steps */
            } /* execution_plan_for_potential_materialization */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`where_subselect_20070`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 6,
                      "access_type": "scan",
                      "resulting_rows": 6,
                      "cost": 3.219,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 6,
                "cost_for_plan": 3.219,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`where_subselect_20070`"
                    ] /* plan_prefix */,
                    "table": "`t1` `table1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "col_int_key",
                          "rows": 2,
                          "cost": 8.5395,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 4,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 4,
                          "cost": 6.8074,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 25,
                    "rows_for_plan": 6,
                    "cost_for_plan": 10.026,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`where_subselect_20070`",
                          "`t1` `table1`"
                        ] /* plan_prefix */,
                        "table": "`t1` `table2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "col_int_key",
                              "rows": 2,
                              "cost": 8.5395,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 4,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 4,
                              "cost": 6.8077,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 25,
                        "rows_for_plan": 6,
                        "cost_for_plan": 16.834,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "FirstMatch",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1` `table1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "rows": 2,
                                        "cost": 8.5395,
                                        "chosen": true
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 16.841,
                                        "chosen": false
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                },
                                {
                                  "table": "`t1` `table2`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "rows": 2,
                                        "cost": 17.079,
                                        "chosen": true
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 33.682,
                                        "chosen": false
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 28.838,
                            "rows": 6,
                            "chosen": true
                          },
                          {
                            "strategy": "MaterializeLookup",
                            "cost": 15.233,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 21.234,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": false
                          }
                        ] /* semijoin_strategy_choice */,
                        "chosen": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`where_subselect_20070`"
                    ] /* plan_prefix */,
                    "table": "`t1` `table2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "col_int_key",
                          "rows": 2,
                          "cost": 8.5395,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 4,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 4,
                          "cost": 6.8074,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 25,
                    "rows_for_plan": 6,
                    "cost_for_plan": 10.026,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`where_subselect_20070`",
                          "`t1` `table2`"
                        ] /* plan_prefix */,
                        "table": "`t1` `table1`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "col_int_key",
                              "rows": 2,
                              "cost": 8.5395,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 4,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 4,
                              "cost": 6.8077,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 25,
                        "rows_for_plan": 6,
                        "cost_for_plan": 16.834,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "FirstMatch",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1` `table2`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "rows": 2,
                                        "cost": 8.5395,
                                        "chosen": true
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 16.841,
                                        "chosen": false
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                },
                                {
                                  "table": "`t1` `table1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "rows": 2,
                                        "cost": 17.079,
                                        "chosen": true
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 33.682,
                                        "chosen": false
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 28.838,
                            "rows": 6,
                            "chosen": true
                          },
                          {
                            "strategy": "MaterializeLookup",
                            "cost": 15.233,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 21.234,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": false
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1` `table1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "col_int_key",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 4,
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.8068,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 4,
                "cost_for_plan": 2.8068,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1` `table1`"
                    ] /* plan_prefix */,
                    "table": "`where_subselect_20070`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 6,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 6,
                          "cost": 6.8192,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 16.667,
                    "rows_for_plan": 4,
                    "cost_for_plan": 9.626,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1` `table1`",
                          "`where_subselect_20070`"
                        ] /* plan_prefix */,
                        "table": "`t1` `table2`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "col_int_key",
                              "rows": 2,
                              "cost": 5.693,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 4,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 4,
                              "cost": 5.2074,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 25,
                        "rows_for_plan": 4,
                        "cost_for_plan": 14.833,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 17.833,
                            "rows": 1,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t1` `table1`"
                    ] /* plan_prefix */,
                    "table": "`t1` `table2`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "col_int_key",
                          "rows": 2,
                          "cost": 5.693,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 4,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 4,
                          "cost": 5.207,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 25,
                    "rows_for_plan": 4,
                    "cost_for_plan": 8.0138,
                    "semijoin_strategy_choice": [
                      {
                        "strategy": "MaterializeScan",
                        "choice": "deferred"
                      }
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1` `table1`",
                          "`t1` `table2`"
                        ] /* plan_prefix */,
                        "table": "`where_subselect_20070`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 6,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 6,
                              "cost": 6.8194,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 16.667,
                        "rows_for_plan": 4,
                        "cost_for_plan": 14.833,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "LooseScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1` `table1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "usable": false,
                                        "chosen": false
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 2.8068,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_1": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "col_int_key",
                                          "index_handles_needed_semijoin_equalities": false
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "chosen": false
                          },
                          {
                            "strategy": "MaterializeScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`where_subselect_20070`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "rows_to_scan": 6,
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "buffers_needed": 1,
                                        "resulting_rows": 6,
                                        "cost": 6.8194,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 18.433,
                            "rows": 1,
                            "duplicate_tables_left": true,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 17.833,
                            "rows": 1,
                            "duplicate_tables_left": false,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1` `table2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "col_int_key",
                      "usable": false,
                      "chosen": false
                    },
                    {
                      "rows_to_scan": 4,
                      "access_type": "scan",
                      "resulting_rows": 4,
                      "cost": 2.8068,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 4,
                "cost_for_plan": 2.8068,
                "semijoin_strategy_choice": [
                ] /* semijoin_strategy_choice */,
                "rest_of_plan": [
                  {
                    "plan_prefix": [
                      "`t1` `table2`"
                    ] /* plan_prefix */,
                    "table": "`where_subselect_20070`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 6,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 6,
                          "cost": 6.8192,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 16.667,
                    "rows_for_plan": 4,
                    "cost_for_plan": 9.626,
                    "semijoin_strategy_choice": [
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1` `table2`",
                          "`where_subselect_20070`"
                        ] /* plan_prefix */,
                        "table": "`t1` `table1`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "access_type": "ref",
                              "index": "col_int_key",
                              "rows": 2,
                              "cost": 5.693,
                              "chosen": true
                            },
                            {
                              "rows_to_scan": 4,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 4,
                              "cost": 5.2074,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 25,
                        "rows_for_plan": 4,
                        "cost_for_plan": 14.833,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 17.833,
                            "rows": 1,
                            "duplicate_tables_left": true,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  },
                  {
                    "plan_prefix": [
                      "`t1` `table2`"
                    ] /* plan_prefix */,
                    "table": "`t1` `table1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "col_int_key",
                          "rows": 2,
                          "cost": 5.693,
                          "chosen": true
                        },
                        {
                          "rows_to_scan": 4,
                          "access_type": "scan",
                          "using_join_cache": true,
                          "buffers_needed": 1,
                          "resulting_rows": 4,
                          "cost": 5.207,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 25,
                    "rows_for_plan": 4,
                    "cost_for_plan": 8.0138,
                    "semijoin_strategy_choice": [
                      {
                        "strategy": "MaterializeScan",
                        "choice": "deferred"
                      }
                    ] /* semijoin_strategy_choice */,
                    "rest_of_plan": [
                      {
                        "plan_prefix": [
                          "`t1` `table2`",
                          "`t1` `table1`"
                        ] /* plan_prefix */,
                        "table": "`where_subselect_20070`",
                        "best_access_path": {
                          "considered_access_paths": [
                            {
                              "rows_to_scan": 6,
                              "access_type": "scan",
                              "using_join_cache": true,
                              "buffers_needed": 1,
                              "resulting_rows": 6,
                              "cost": 6.8194,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "condition_filtering_pct": 16.667,
                        "rows_for_plan": 4,
                        "cost_for_plan": 14.833,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "LooseScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`t1` `table2`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "usable": false,
                                        "chosen": false
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 2.8068,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */,
                                  "unknown_key_2": {
                                    "searching_loose_scan_index": {
                                      "indexes": [
                                        {
                                          "index": "col_int_key",
                                          "covering_scan": {
                                            "cost": 1.0698,
                                            "chosen": true
                                          } /* covering_scan */
                                        }
                                      ] /* indexes */
                                    } /* searching_loose_scan_index */
                                  }
                                },
                                {
                                  "table": "`t1` `table1`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "ref",
                                        "index": "col_int_key",
                                        "rows": 2,
                                        "cost": 5.693,
                                        "chosen": true
                                      },
                                      {
                                        "rows_to_scan": 4,
                                        "access_type": "scan",
                                        "resulting_rows": 4,
                                        "cost": 11.227,
                                        "chosen": false
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                },
                                {
                                  "table": "`where_subselect_20070`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "rows_to_scan": 6,
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "buffers_needed": 1,
                                        "resulting_rows": 6,
                                        "cost": 11.62,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 19.182,
                            "rows": 1,
                            "chosen": true
                          },
                          {
                            "strategy": "MaterializeScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`where_subselect_20070`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "rows_to_scan": 6,
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "buffers_needed": 1,
                                        "resulting_rows": 6,
                                        "cost": 6.8194,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 18.433,
                            "rows": 1,
                            "duplicate_tables_left": false,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 17.833,
                            "rows": 1,
                            "duplicate_tables_left": false,
                            "chosen": true
                          }
                        ] /* semijoin_strategy_choice */,
                        "pruned_by_cost": true
                      }
                    ] /* rest_of_plan */
                  }
                ] /* rest_of_plan */
              },
              {
                "final_semijoin_strategy": "MaterializeLookup"
              }
            ] /* considered_execution_plans */
          },
          {
            "creating_tmp_table": {
              "tmp_table_info": {
                "row_length": 13,
                "key_length": 14,
                "unique_constraint": false,
                "location": "memory (heap)",
                "row_limit_estimate": 80659
              } /* tmp_table_info */
            } /* creating_tmp_table */
          },
          {
            "subselect_execution": {
              "select#": 2,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`<subquery3>`.`field1` = `where_subselect_20070`.`field1`) and (`table2`.`col_int_key` = `table1`.`col_int_key`))",
              "attached_conditions_computation": [
                {
                  "table": "`t1` `table2`",
                  "rechecking_index_usage": {
                    "recheck_reason": "not_first_table",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 4,
                        "cost": 4.9068
                      } /* table_scan */,
                      "potential_range_indexes": [
                        {
                          "index": "col_int_key",
                          "usable": true,
                          "key_parts": [
                            "col_int_key"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indexes */,
                      "best_covering_index_scan": {
                        "index": "col_int_key",
                        "cost": 1.8698,
                        "chosen": true
                      } /* best_covering_index_scan */,
                      "setup_range_conditions": [
                      ] /* setup_range_conditions */,
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_single_table"
                      } /* group_index_range */,
                      "analyzing_range_alternatives": {
                        "range_scan_alternatives": [
                          {
                            "index": "col_int_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": "`where_subselect_20070`",
                  "attached": "(`where_subselect_20070`.`field1` is not null)"
                },
                {
                  "table": "``.`<subquery3>`",
                  "attached": null
                },
                {
                  "table": "`t1` `table1`",
                  "attached": null
                },
                {
                  "table": "`t1` `table2`",
                  "attached": "(`table2`.`col_int_key` = `table1`.`col_int_key`)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`where_subselect_20070`"
              },
              {
                "table": "``.`<subquery3>`"
              },
              {
                "table": "`t1` `table1`"
              },
              {
                "table": "`t1` `table2`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          },
          {
            "subselect_execution": {
              "select#": 4,
              "steps": [
              ] /* steps */
            } /* subselect_execution */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
DROP TABLE where_subselect_20070,t1;
#
# Bug#13430443 - ASSERTION `NEW_TYPE[0] != 'U'' FAILED. WHEN
# OPTIMIZER_TRACE IS ENABLED
#
CREATE TABLE t1
(a INT,b INT,c INT, KEY(a),KEY (a,c)) ENGINE=INNODB;
SELECT 1 FROM t1 WHERE 1 LIKE
(SELECT a FROM t1 WHERE a = 1 ORDER BY c);
1
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
SELECT 1 FROM t1 WHERE 1 LIKE
(SELECT a FROM t1 WHERE a = 1 ORDER BY c)	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "expanded_query": "/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1) order by `t1`.`c`"
                }
              ] /* steps */
            } /* join_preparation */
          },
          {
            "expanded_query": "/* select#1 */ select 1 AS `1` from `t1` where (1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1) order by `t1`.`c`))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1) order by `t1`.`c`))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1) order by `t1`.`c`))"
                },
                {
                  "transformation": "constant_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1) order by `t1`.`c`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "subselect_evaluation": [
                    {
                      "subselect_execution": {
                        "select#": 2,
                        "steps": [
                          {
                            "join_optimization": {
                              "select#": 2,
                              "steps": [
                                {
                                  "condition_processing": {
                                    "condition": "WHERE",
                                    "original_condition": "(`t1`.`a` = 1)",
                                    "steps": [
                                      {
                                        "transformation": "equality_propagation",
                                        "resulting_condition": "multiple equal(1, `t1`.`a`)"
                                      },
                                      {
                                        "transformation": "constant_propagation",
                                        "resulting_condition": "multiple equal(1, `t1`.`a`)"
                                      },
                                      {
                                        "transformation": "trivial_condition_removal",
                                        "resulting_condition": "multiple equal(1, `t1`.`a`)"
                                      }
                                    ] /* 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": [
                                    {
                                      "table": "`t1`",
                                      "field": "a",
                                      "equals": "1",
                                      "null_rejecting": false
                                    },
                                    {
                                      "table": "`t1`",
                                      "field": "a",
                                      "equals": "1",
                                      "null_rejecting": false
                                    }
                                  ] /* ref_optimizer_key_uses */
                                },
                                {
                                  "rows_estimation": [
                                    {
                                      "table": "`t1`",
                                      "range_analysis": {
                                        "table_scan": {
                                          "rows": 1,
                                          "cost": 3.3
                                        } /* table_scan */,
                                        "potential_range_indexes": [
                                          {
                                            "index": "a",
                                            "usable": true,
                                            "key_parts": [
                                              "a"
                                            ] /* key_parts */
                                          },
                                          {
                                            "index": "a_2",
                                            "usable": true,
                                            "key_parts": [
                                              "a",
                                              "c"
                                            ] /* key_parts */
                                          }
                                        ] /* potential_range_indexes */,
                                        "best_covering_index_scan": {
                                          "index": "a_2",
                                          "cost": 1.2,
                                          "chosen": true
                                        } /* best_covering_index_scan */,
                                        "setup_range_conditions": [
                                        ] /* setup_range_conditions */,
                                        "group_index_range": {
                                          "chosen": false,
                                          "cause": "not_group_by_or_distinct"
                                        } /* group_index_range */,
                                        "analyzing_range_alternatives": {
                                          "range_scan_alternatives": [
                                            {
                                              "index": "a",
                                              "ranges": [
                                                "1 <= a <= 1"
                                              ] /* ranges */,
                                              "index_dives_for_eq_ranges": true,
                                              "rowid_ordered": true,
                                              "using_mrr": true,
                                              "index_only": false,
                                              "rows": 1,
                                              "cost": 2.2,
                                              "chosen": false,
                                              "cause": "cost"
                                            },
                                            {
                                              "index": "a_2",
                                              "ranges": [
                                                "1 <= a <= 1"
                                              ] /* ranges */,
                                              "index_dives_for_eq_ranges": true,
                                              "rowid_ordered": false,
                                              "using_mrr": false,
                                              "index_only": true,
                                              "rows": 1,
                                              "cost": 1.21,
                                              "chosen": false,
                                              "cause": "cost"
                                            }
                                          ] /* range_scan_alternatives */,
                                          "analyzing_roworder_intersect": {
                                            "usable": false,
                                            "cause": "too_few_roworder_scans"
                                          } /* analyzing_roworder_intersect */
                                        } /* analyzing_range_alternatives */
                                      } /* range_analysis */
                                    }
                                  ] /* rows_estimation */
                                },
                                {
                                  "considered_execution_plans": [
                                    {
                                      "plan_prefix": [
                                      ] /* plan_prefix */,
                                      "table": "`t1`",
                                      "best_access_path": {
                                        "considered_access_paths": [
                                          {
                                            "access_type": "ref",
                                            "index": "a",
                                            "rows": 1,
                                            "cost": 1.2,
                                            "chosen": true
                                          },
                                          {
                                            "access_type": "ref",
                                            "index": "a_2",
                                            "rows": 1,
                                            "cost": 1.2,
                                            "chosen": false
                                          },
                                          {
                                            "access_type": "scan",
                                            "chosen": false,
                                            "cause": "covering_index_better_than_full_scan"
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "condition_filtering_pct": 100,
                                      "rows_for_plan": 1,
                                      "cost_for_plan": 1.2,
                                      "chosen": true
                                    }
                                  ] /* considered_execution_plans */
                                },
                                {
                                  "attaching_conditions_to_tables": {
                                    "original_condition": "(`t1`.`a` = 1)",
                                    "attached_conditions_computation": [
                                    ] /* attached_conditions_computation */,
                                    "attached_conditions_summary": [
                                      {
                                        "table": "`t1`",
                                        "attached": null
                                      }
                                    ] /* attached_conditions_summary */
                                  } /* attaching_conditions_to_tables */
                                },
                                {
                                  "clause_processing": {
                                    "clause": "ORDER BY",
                                    "original_clause": "`t1`.`c`",
                                    "items": [
                                      {
                                        "item": "`t1`.`c`"
                                      }
                                    ] /* items */,
                                    "resulting_clause_is_simple": true,
                                    "resulting_clause": "`t1`.`c`"
                                  } /* clause_processing */
                                },
                                {
                                  "added_back_ref_condition": "((`t1`.`a` <=> 1))"
                                },
                                {
                                  "reconsidering_access_paths_for_index_ordering": {
                                    "clause": "ORDER BY",
                                    "steps": [
                                    ] /* steps */,
                                    "index_order_summary": {
                                      "table": "`t1`",
                                      "index_provides_order": true,
                                      "order_direction": "asc",
                                      "index": "a_2",
                                      "plan_changed": true,
                                      "access_type": "ref"
                                    } /* index_order_summary */
                                  } /* reconsidering_access_paths_for_index_ordering */
                                },
                                {
                                  "refine_plan": [
                                    {
                                      "table": "`t1`"
                                    }
                                  ] /* refine_plan */
                                }
                              ] /* steps */
                            } /* join_optimization */
                          },
                          {
                            "join_execution": {
                              "select#": 2,
                              "steps": [
                              ] /* steps */
                            } /* join_execution */
                          }
                        ] /* steps */
                      } /* subselect_execution */
                    }
                  ] /* subselect_evaluation */,
                  "resulting_condition": null
                }
              ] /* steps */
            } /* condition_processing */
          }
        ] /* steps */,
        "empty_result": {
          "cause": "Impossible WHERE"
        } /* empty_result */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}	0	0
DROP TABLE t1;
#
# Bug #18346750 OPTIMIZER_TRACE & DBUG_PRINT CRASH IN ST_SELECT_LEX::PRINT
#
CREATE TABLE t1 (a INT, PRIMARY KEY (a))
PARTITION BY KEY (a) PARTITIONS 2;
Warnings:
Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
INSERT INTO t1 VALUES (1),(2);
Warnings:
Warning	1287	The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
SELECT  1 FROM t1 ,t1 w
WHERE t1.a <=> (SELECT 22 FROM t1 GROUP BY (SELECT 1 FROM t1));
ERROR 21000: Subquery returns more than 1 row
SELECT TRACE LIKE "%select had some error%" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
TRACE LIKE "%select had some error%"
0
DROP TABLE t1;
#
# Bug#18791851 CRASH IN ST_SELECT_LEX::PRINT WITH OPTIMIZER_TRACE ON SUBQUERY
#
CREATE TABLE t1 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_time_key time,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
CREATE TABLE t2 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
CREATE TABLE t3 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
CREATE TABLE t4 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_time_key time,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1),
PRIMARY KEY (pk),
KEY col_varchar_key (col_varchar_key,col_int_key)
);
SELECT alias1.col_int_nokey AS field1,
alias2.col_varchar_key AS field2,
alias1.col_time_key AS field3,
MIN( alias1.col_int_nokey ) AS field4
FROM (
(
SELECT SQ1_alias1.*
FROM ( t1 AS SQ1_alias1, t2 AS SQ1_alias2 )
) AS alias1,
t4 AS alias2
)
WHERE
EXISTS (
SELECT DISTINCT  SQ2_alias2.col_varchar_nokey AS SQ2_field1
FROM t2 AS SQ2_alias1
INNER JOIN (t4 AS SQ2_alias2
INNER JOIN t3 AS SQ2_alias3
ON SQ2_alias3.pk = SQ2_alias2.pk)
ON SQ2_alias3.col_varchar_key = SQ2_alias2.col_varchar_nokey
)
AND alias1.col_int_key = alias2.pk
HAVING  alias1.col_int_nokey  IN ( SELECT 2 FROM DUAL ) ;
field1	field2	field3	field4
DROP TABLE t1,t2,t3,t4;
CREATE TABLE t1(a INT);
SET @a:=(SELECT ROW(1, 2)=
ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1
HAVING 3 IN (SELECT 2 FROM DUAL)),
1));
DROP TABLE t1;
#
# Bug #18945693 CRASH IN PRINT_TABLE_ARRAY AT SQL/SQL_LEX.CC ON 2ND EXEC OF PREPARED STATEMENT
#
CREATE TABLE t1 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1)
);
INSERT INTO t1 VALUES (13,7,3,'y','y'),(14,0,4,'c','c');
CREATE TABLE t2 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1)
);
INSERT INTO t2 VALUES (20,9,8,'e','e');
CREATE TABLE t3 (
pk INT NOT NULL,
col_int_nokey INT,
col_int_key INT,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1)
);
INSERT INTO t3 VALUES (1,1,7,'k','k');
PREPARE prep_stmt FROM "
SELECT
  alias1.col_varchar_nokey AS field1,
  MAX(alias1.pk) AS field2
FROM t3 AS alias1
GROUP BY field1
HAVING
  1 > (
       SELECT MAX(SQ4_alias1.col_varchar_nokey)
       FROM t1 AS SQ4_alias1
            RIGHT OUTER JOIN
            (
              t2 AS SQ4_alias2
              JOIN t1 AS SQ4_alias3
              ON SQ4_alias3.col_int_key = SQ4_alias2.col_int_nokey
            )
            ON SQ4_alias3.pk = SQ4_alias2.col_int_nokey
      )
";
EXECUTE prep_stmt;
field1	field2
EXECUTE prep_stmt;
field1	field2
DROP TABLE t1,t2,t3;
#
# Bug#19063289 CRASH IN PRINT_TABLE_ARRAY ON 2ND EXECUTION OF PS WITH SUBQUERY AND VIEW
#
CREATE TABLE B (
pk INTEGER,
col_int_key INTEGER,
col_varchar_nokey VARCHAR(1)
);
CREATE TABLE C (
col_int_nokey INTEGER,
col_int_key INTEGER,
col_varchar_key VARCHAR(1),
col_varchar_nokey VARCHAR(1)
) ;
CREATE TABLE CC (
pk INTEGER,
col_int_key INTEGER
) ENGINE=MYISAM;
INSERT INTO CC VALUES (1, 0),(2, 7);
PREPARE stmt FROM "
SELECT
alias1.col_int_key AS field1 ,
COUNT(alias1.pk) AS field2
FROM
CC AS alias1
GROUP BY field1
HAVING
alias1.col_int_key >
  (
    SELECT
    MAX(  SQ2_alias2.col_int_key ) AS SQ2_field1
    FROM
    C AS SQ2_alias1 LEFT  JOIN
       ( B AS SQ2_alias2 INNER JOIN C AS SQ2_alias3
         ON 1)
       ON 1
  )
OR field2 < 2
";
EXECUTE stmt;
field1	field2
0	1
7	1
EXECUTE stmt;
field1	field2
0	1
7	1
DROP TABLE B,C,CC;
#
# Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY
#                INDEX, EVEN THOUGH COST IS HIGHER
#
CREATE TABLE t1 (
a TINYTEXT NOT NULL,
b TINYINT(3) UNSIGNED NOT NULL,
PRIMARY KEY (a(32),b),
KEY b_idx(b)
) ENGINE=INNODB;
INSERT INTO t1 VALUES ('a',1),('a',2),('a',3),('b',1),('c',1),('c',4),('e',2);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET @optimizer_switch_saved=@@session.optimizer_switch;
SET @@session.optimizer_switch=default;
SELECT COUNT(*) FROM t1;
COUNT(*)
7
EXPLAIN SELECT * FROM t1 WHERE a IN ('a', 'b') AND b = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,b_idx	b_idx	35	NULL	2	100.00	Using index condition; Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 2) and (`test`.`t1`.`a` in ('a','b')))
SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SELECT @trace RLIKE "rerunning_range_optimizer_for_single_index";
@trace RLIKE "rerunning_range_optimizer_for_single_index"
1
SET @@session.optimizer_switch=@optimizer_switch_saved;
DROP TABLE t1;
#
# Bug #23227428: SQL PLAN IS NOT ACCORDING WITH OPTIMIZER_TRACE
#
CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 CHAR(12)) ENGINE=INNODB;
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	PRIMARY	4	NULL	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` order by `test`.`t1`.`c1` desc limit 1
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1	{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` order by `t1`.`c1` desc limit 1"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "table_scan": {
                  "rows": 1000,
                  "cost": 1
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1000,
                      "access_type": "scan",
                      "resulting_rows": 1000,
                      "cost": 201,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1000,
                "cost_for_plan": 201,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": null
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t1`.`c1` desc",
              "items": [
                {
                  "item": "`t1`.`c1`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t1`.`c1` desc"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`t1`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "PRIMARY",
                "plan_changed": true,
                "access_type": "index"
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`t1`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_explain */
    }
  ] /* steps */
}	0	0
DROP TABLE t1;