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": [
    {
      "view": {
        "database": "test",
        "view": "sq1_alias1",
        "in_select#": 1,
        "select#": 5,
        "merged": true
      } /* view */
    },
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "join_preparation": {
              "select#": 2,
              "steps": [
                {
                  "join_preparation": {
                    "select#": 3,
                    "steps": [
                      {
                        "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 */
                }
              ] /* 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`))) 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`)) 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)) 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))))))"
          }
        ] /* 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` 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`)) 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)) 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))))))",
              "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` 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`)) 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)) 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))))))"
                },
                {
                  "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` 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`)) 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)) 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))))))"
                },
                {
                  "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` 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`)) 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)) 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))))))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "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_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`)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "transformation": {
              "select#": 2,
              "from": "IN (SELECT)",
              "to": "semijoin",
              "chosen": true,
              "evaluating_constant_semijoin_conditions": [
                {
                  "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 */
                },
                {
                  "subselect_execution": {
                    "select#": 3,
                    "steps": [
                    ] /* steps */
                  } /* subselect_execution */
                }
              ] /* 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` = 7))"
            } /* transformations_to_nested_joins */
          },
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(1 and (`where_subselect_19033`.`field1` = 7))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "subselect_evaluation": [
                  ] /* subselect_evaluation */,
                  "resulting_condition": "(1 and multiple equal(7, `where_subselect_19033`.`field1`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(1 and multiple equal(7, `where_subselect_19033`.`field1`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "multiple equal(7, `where_subselect_19033`.`field1`)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "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 */
          },
          {
            "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": [
                          {
                            "access_type": "scan",
                            "rows": 1,
                            "cost": 2.2049,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "cost_for_plan": 2.2049,
                      "rows_for_plan": 1,
                      "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": [
                    {
                      "access_type": "scan",
                      "rows": 1,
                      "cost": 2.2049,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.2049,
                "rows_for_plan": 1,
                "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`",
                "access_type": "index_scan"
              }
            ] /* 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`))))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "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 */
          },
          {
            "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": [
                                          {
                                            "access_type": "scan",
                                            "rows": 4,
                                            "cost": 2.8068,
                                            "chosen": true
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "cost_for_plan": 2.8068,
                                      "rows_for_plan": 4,
                                      "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`",
                                      "access_type": "index_scan"
                                    }
                                  ] /* 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": [
                                          {
                                            "access_type": "scan",
                                            "rows": 4,
                                            "cost": 2.8068,
                                            "chosen": true
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "cost_for_plan": 2.8068,
                                      "rows_for_plan": 4,
                                      "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`",
                                      "access_type": "index_scan"
                                    }
                                  ] /* 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 */
          },
          {
            "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
                          },
                          {
                            "access_type": "scan",
                            "rows": 4,
                            "cost": 2.8068,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "cost_for_plan": 2.8068,
                      "rows_for_plan": 4,
                      "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": 4.493,
                                "chosen": true
                              },
                              {
                                "access_type": "scan",
                                "using_join_cache": true,
                                "rows": 3,
                                "cost": 4.607,
                                "chosen": true
                              }
                            ] /* considered_access_paths */
                          } /* best_access_path */,
                          "cost_for_plan": 7.4138,
                          "rows_for_plan": 12,
                          "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
                          },
                          {
                            "access_type": "scan",
                            "rows": 4,
                            "cost": 2.8068,
                            "chosen": true
                          }
                        ] /* considered_access_paths */
                      } /* best_access_path */,
                      "cost_for_plan": 2.8068,
                      "rows_for_plan": 4,
                      "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": 4.493,
                                "chosen": true
                              },
                              {
                                "access_type": "scan",
                                "using_join_cache": true,
                                "rows": 3,
                                "cost": 4.607,
                                "chosen": true
                              }
                            ] /* considered_access_paths */
                          } /* best_access_path */,
                          "cost_for_plan": 7.4138,
                          "rows_for_plan": 12,
                          "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": [
                    {
                      "access_type": "scan",
                      "rows": 6,
                      "cost": 3.219,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 3.219,
                "rows_for_plan": 6,
                "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": 6.5395,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 3,
                          "cost": 5.8074,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 9.0265,
                    "rows_for_plan": 18,
                    "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": 18.819,
                              "chosen": true
                            },
                            {
                              "access_type": "scan",
                              "using_join_cache": true,
                              "rows": 3,
                              "cost": 13.009,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "cost_for_plan": 22.036,
                        "rows_for_plan": 54,
                        "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": 6.5395,
                                        "chosen": true
                                      },
                                      {
                                        "access_type": "scan",
                                        "rows": 3,
                                        "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": 12.679,
                                        "chosen": true
                                      },
                                      {
                                        "access_type": "scan",
                                        "rows": 3,
                                        "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": 16.233,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 36.036,
                            "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": 6.5395,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 3,
                          "cost": 5.8074,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 9.0265,
                    "rows_for_plan": 18,
                    "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": 18.819,
                              "chosen": true
                            },
                            {
                              "access_type": "scan",
                              "using_join_cache": true,
                              "rows": 3,
                              "cost": 13.009,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "cost_for_plan": 22.036,
                        "rows_for_plan": 54,
                        "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": 6.5395,
                                        "chosen": true
                                      },
                                      {
                                        "access_type": "scan",
                                        "rows": 3,
                                        "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": 12.679,
                                        "chosen": true
                                      },
                                      {
                                        "access_type": "scan",
                                        "rows": 3,
                                        "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": 16.233,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 36.036,
                            "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
                    },
                    {
                      "access_type": "scan",
                      "rows": 4,
                      "cost": 2.8068,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.8068,
                "rows_for_plan": 4,
                "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": [
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 6,
                          "cost": 6.8192,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 9.626,
                    "rows_for_plan": 24,
                    "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": 24.958,
                              "chosen": true
                            },
                            {
                              "access_type": "scan",
                              "using_join_cache": true,
                              "rows": 3,
                              "cost": 16.61,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "cost_for_plan": 26.236,
                        "rows_for_plan": 72,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 43.836,
                            "rows": 6,
                            "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": 4.493,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 3,
                          "cost": 4.607,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 7.4138,
                    "rows_for_plan": 12,
                    "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": [
                            {
                              "access_type": "scan",
                              "using_join_cache": true,
                              "rows": 6,
                              "cost": 16.42,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "cost_for_plan": 23.834,
                        "rows_for_plan": 72,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "MaterializeScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`where_subselect_20070`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "rows": 6,
                                        "cost": 6.8194,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 19.433,
                            "rows": 6,
                            "duplicate_tables_left": true,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 41.434,
                            "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` `table2`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "col_int_key",
                      "rows": 2e308,
                      "cost": 0,
                      "chosen": false
                    },
                    {
                      "access_type": "scan",
                      "rows": 4,
                      "cost": 2.8068,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "cost_for_plan": 2.8068,
                "rows_for_plan": 4,
                "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": [
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 6,
                          "cost": 6.8192,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 9.626,
                    "rows_for_plan": 24,
                    "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": 24.958,
                              "chosen": true
                            },
                            {
                              "access_type": "scan",
                              "using_join_cache": true,
                              "rows": 3,
                              "cost": 16.61,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "cost_for_plan": 26.236,
                        "rows_for_plan": 72,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 43.836,
                            "rows": 6,
                            "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": 4.493,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "using_join_cache": true,
                          "rows": 3,
                          "cost": 4.607,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "cost_for_plan": 7.4138,
                    "rows_for_plan": 12,
                    "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": [
                            {
                              "access_type": "scan",
                              "using_join_cache": true,
                              "rows": 6,
                              "cost": 16.42,
                              "chosen": true
                            }
                          ] /* considered_access_paths */
                        } /* best_access_path */,
                        "cost_for_plan": 23.834,
                        "rows_for_plan": 72,
                        "semijoin_strategy_choice": [
                          {
                            "strategy": "MaterializeScan",
                            "recalculate_access_paths_and_cost": {
                              "tables": [
                                {
                                  "table": "`where_subselect_20070`",
                                  "best_access_path": {
                                    "considered_access_paths": [
                                      {
                                        "access_type": "scan",
                                        "using_join_cache": true,
                                        "rows": 6,
                                        "cost": 6.8194,
                                        "chosen": true
                                      }
                                    ] /* considered_access_paths */
                                  } /* best_access_path */
                                }
                              ] /* tables */
                            } /* recalculate_access_paths_and_cost */,
                            "cost": 19.433,
                            "rows": 6,
                            "duplicate_tables_left": true,
                            "chosen": true
                          },
                          {
                            "strategy": "DuplicatesWeedout",
                            "cost": 41.434,
                            "rows": 6,
                            "duplicate_tables_left": false,
                            "chosen": false
                          }
                        ] /* 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_indices": [
                        {
                          "index": "col_int_key",
                          "usable": true,
                          "key_parts": [
                            "col_int_key"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indices */,
                      "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": "unknown"
                          }
                        ] /* 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`",
                "access_type": "table_scan"
              },
              {
                "table": "``.`<subquery3>`"
              },
              {
                "table": "`t1` `table1`",
                "access_type": "index_scan"
              },
              {
                "table": "`t1` `table2`",
                "access_type": "index_scan"
              }
            ] /* 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 */
                                },
                                {
                                  "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_indices": [
                                          {
                                            "index": "a",
                                            "usable": true,
                                            "key_parts": [
                                              "a"
                                            ] /* key_parts */
                                          },
                                          {
                                            "index": "a_2",
                                            "usable": true,
                                            "key_parts": [
                                              "a",
                                              "c"
                                            ] /* key_parts */
                                          }
                                        ] /* potential_range_indices */,
                                        "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.1031,
                                              "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": 2.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",
                                            "cause": "covering_index_better_than_full_scan",
                                            "chosen": false
                                          }
                                        ] /* considered_access_paths */
                                      } /* best_access_path */,
                                      "cost_for_plan": 1.2,
                                      "rows_for_plan": 1,
                                      "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 */
                                },
                                {
                                  "refine_plan": [
                                    {
                                      "table": "`t1`"
                                    }
                                  ] /* refine_plan */
                                },
                                {
                                  "added_back_ref_condition": "((`t1`.`a` <=> 1))"
                                },
                                {
                                  "reconsidering_access_paths_for_index_ordering": {
                                    "clause": "ORDER BY",
                                    "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 */
                                }
                              ] /* 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#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;
#
# 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),('b',4),('c',1),('d',1),
('c',4),('d',3),('e',2),('f',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;
EXPLAIN SELECT * FROM t1 WHERE a IN ('a', 'b') AND b = 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	PRIMARY,b_idx	b_idx	35	NULL	2	Using index condition; Using where
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;
