2946 lines
123 KiB
Plaintext
2946 lines
123 KiB
Plaintext
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, CHARSET utf8mb4;
|
|
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, CHARSET utf8mb4;
|
|
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, CHARSET utf8mb4;
|
|
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, CHARSET utf8mb4;
|
|
CREATE VIEW view_t4 AS SELECT * FROM t4;
|
|
CREATE TABLE where_subselect_19379 SELECT
|
|
(
|
|
SELECT SUM(sq1_alias1.pk) AS sq1_field1
|
|
FROM view_t4 AS sq1_alias1
|
|
INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
|
|
sq1_alias1.col_varchar_key )
|
|
) AS field1,
|
|
alias1.col_varchar_nokey AS field2
|
|
FROM (t2 AS alias1
|
|
LEFT JOIN ( t2 AS alias2
|
|
LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
|
|
alias2.col_varchar_key )
|
|
) ON (alias3.col_varchar_key = alias2.col_varchar_key)
|
|
)
|
|
WHERE ( alias2.col_varchar_key IN (
|
|
SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
|
|
FROM t3 AS sq2_alias1
|
|
WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
|
|
)
|
|
);
|
|
SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
|
|
(SELECT
|
|
(
|
|
SELECT SUM(sq1_alias1.pk) AS sq1_field1
|
|
FROM view_t4 AS sq1_alias1
|
|
INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
|
|
sq1_alias1.col_varchar_key )
|
|
) AS field1,
|
|
alias1.col_varchar_nokey AS field2
|
|
FROM (t2 AS alias1
|
|
LEFT JOIN ( t2 AS alias2
|
|
LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
|
|
alias2.col_varchar_key )
|
|
) ON (alias3.col_varchar_key = alias2.col_varchar_key)
|
|
)
|
|
WHERE ( alias2.col_varchar_key IN (
|
|
SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
|
|
FROM t3 AS sq2_alias1
|
|
WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
|
|
)
|
|
));
|
|
field1 field2
|
|
select * from information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT * FROM where_subselect_19379 WHERE (field1, field2) NOT IN
|
|
(SELECT
|
|
(
|
|
SELECT SUM(sq1_alias1.pk) AS sq1_field1
|
|
FROM view_t4 AS sq1_alias1
|
|
INNER JOIN t1 AS sq1_alias2 ON (sq1_alias2.col_varchar_key =
|
|
sq1_alias1.col_varchar_key )
|
|
) AS field1,
|
|
alias1.col_varchar_nokey AS field2
|
|
FROM (t2 AS alias1
|
|
LEFT JOIN ( t2 AS alias2
|
|
LEFT OUTER JOIN t2 AS alias3 ON (alias3.col_varchar_nokey =
|
|
alias2.col_varchar_key )
|
|
) ON (alias3.col_varchar_key = alias2.col_varchar_key)
|
|
)
|
|
WHERE ( alias2.col_varchar_key IN (
|
|
SELECT sq2_alias1.col_varchar_nokey AS sq2_field1
|
|
FROM t3 AS sq2_alias1
|
|
WHERE sq2_alias1.col_varchar_nokey <= alias1.col_varchar_key
|
|
)
|
|
)) {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))"
|
|
},
|
|
{
|
|
"transformation": {
|
|
"select#": 2,
|
|
"from": "IN (SELECT)",
|
|
"to": "semijoin",
|
|
"chosen": false
|
|
} /* 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 <in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "<in_optimizer>((`where_subselect_19379`.`field1`,`where_subselect_19379`.`field2`),<exists>(/* select#2 */ select (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) AS `field1`,`alias1`.`col_varchar_nokey` AS `field2` from `t2` `alias1` join `t2` `alias2` join `t2` `alias3` semi join (`t3` `sq2_alias1`) where (<if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field1`) = (/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))) or ((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`)) is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`where_subselect_19379`.`field2`) = `alias1`.`col_varchar_nokey`) or (`alias1`.`col_varchar_nokey` is null)), true) and (`sq2_alias1`.`col_varchar_nokey` <= `alias1`.`col_varchar_key`) and (`alias2`.`col_varchar_key` = `sq2_alias1`.`col_varchar_nokey`) and (`alias3`.`col_varchar_key` = `alias2`.`col_varchar_key`) and (`alias3`.`col_varchar_nokey` = `alias2`.`col_varchar_key`)) having (<if>(outer_field_is_not_null, <is_not_null_test>((/* select#3 */ select sum(`t4`.`pk`) AS `sq1_field1` from `t4` join `t1` `sq1_alias2` where (`sq1_alias2`.`col_varchar_key` = `t4`.`col_varchar_key`))), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`alias1`.`col_varchar_nokey`), true))) is false)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`where_subselect_19379`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`where_subselect_19379`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": true
|
|
}
|
|
] /* rows_estimation */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "no matching row in const table"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_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;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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#": 3,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#3 */ select `t2`.`col_int_key` from `t2`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select `where_subselect_19033`.`field1` AS `field1` from `where_subselect_19033` semi join (`t1`) where ((`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`where_subselect_19033`.`field1` = (/* select#3 */ select `t2`.`col_int_key` from `t2`)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "(multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal((/* select#3 */ select `t2`.`col_int_key` from `t2`), `where_subselect_19033`.`field1`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`where_subselect_19033`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"pulled_out_semijoin_tables": [
|
|
] /* pulled_out_semijoin_tables */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t2`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`where_subselect_19033`",
|
|
"rows": 1,
|
|
"cost": 1,
|
|
"table_type": "system",
|
|
"empty": false
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 1,
|
|
"cost": 0.5012
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"execution_plan_for_potential_materialization": {
|
|
"steps": [
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 1,
|
|
"cost": 0.6012,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.6012,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
}
|
|
] /* steps */
|
|
} /* execution_plan_for_potential_materialization */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
"`where_subselect_19033`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 1,
|
|
"cost": 0.6012,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.6012,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "FirstMatch",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 0.6012,
|
|
"rows": 1,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "MaterializeLookup",
|
|
"cost": 1.8012,
|
|
"rows": 1,
|
|
"duplicate_tables_left": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 1.8012,
|
|
"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": "true",
|
|
"condition_value": true
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "true",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE where_subselect_19033,t1,t2;
|
|
|
|
# BUG#12612201 - SEGFAULT IN
|
|
# SUBSELECT_UNIQUESUBQUERY_ENGINE::PRINT WITH OPTIMIZER TRACE
|
|
|
|
CREATE TABLE t1 (
|
|
col_int_key int(11) DEFAULT NULL,
|
|
col_varchar_key varchar(1) DEFAULT NULL,
|
|
col_varchar_nokey varchar(1) DEFAULT NULL
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select count(`t1`.`col_int_key`) from `t1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 4,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#4 */ select count(`t1`.`col_int_key`) from `t1`"
|
|
}
|
|
] /* 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` semi join (`t1` `table1` join `t1` `table2`) where ((`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 */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "((`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": "(((/* 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": "(((/* 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": 0.5017
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"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 */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* 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": 0.5017
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 4,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"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 */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 4,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "multiple equal(`where_subselect_20070`.`field1`, `table2`.`col_int_key`, `table1`.`col_int_key`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 1,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 2,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"field": "col_int_key",
|
|
"equals": "`where_subselect_20070`.`field1`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"field": "col_int_key",
|
|
"equals": "`table2`.`col_int_key`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"field": "col_int_key",
|
|
"equals": "`where_subselect_20070`.`field1`",
|
|
"null_rejecting": true
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"field": "col_int_key",
|
|
"equals": "`table1`.`col_int_key`",
|
|
"null_rejecting": true
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"pulled_out_semijoin_tables": [
|
|
] /* pulled_out_semijoin_tables */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"table_scan": {
|
|
"rows": 6,
|
|
"cost": 0.5048
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 0.5017
|
|
} /* table_scan */
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 0.5017
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"execution_plan_for_potential_materialization": {
|
|
"steps": [
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"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": 1.8233,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1017,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 2.725,
|
|
"chosen": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"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": 1.8233,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1017,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 2.725,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* considered_execution_plans */
|
|
}
|
|
] /* steps */
|
|
} /* execution_plan_for_potential_materialization */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 6,
|
|
"cost": 1.1048,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 6,
|
|
"cost_for_plan": 1.1048,
|
|
"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": 2.7349,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.9019,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 12,
|
|
"cost_for_plan": 3.8396,
|
|
"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": 5.4698,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 5.3021,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 25,
|
|
"rows_for_plan": 12,
|
|
"cost_for_plan": 9.1418,
|
|
"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": 5.4698,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 10.821,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 9.3094,
|
|
"rows": 6,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "MaterializeLookup",
|
|
"cost": 6.2297,
|
|
"rows": 6,
|
|
"duplicate_tables_left": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 11.942,
|
|
"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": 2.7349,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.9019,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 12,
|
|
"cost_for_plan": 3.8396,
|
|
"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": 5.4698,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 5.3021,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 25,
|
|
"rows_for_plan": 12,
|
|
"cost_for_plan": 9.1418,
|
|
"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": 5.4698,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 10.821,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 9.3094,
|
|
"rows": 6,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "MaterializeLookup",
|
|
"cost": 6.2297,
|
|
"rows": 6,
|
|
"duplicate_tables_left": false,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 11.942,
|
|
"rows": 6,
|
|
"duplicate_tables_left": false,
|
|
"chosen": false
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table1`"
|
|
] /* plan_prefix */,
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 2.9048,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 16.667,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 3.8065,
|
|
"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": 1.8233,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1018,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 5.6298,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 7.5298,
|
|
"rows": 1,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table1`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"rows": 2,
|
|
"cost": 1.8233,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1017,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 2.725,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "MaterializeScan",
|
|
"choice": "deferred"
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table1`",
|
|
"`t1` `table2`"
|
|
] /* plan_prefix */,
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 5.3049,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 16.667,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 8.0299,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_1": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "col_int_key",
|
|
"index_handles_needed_semijoin_equalities": false
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"strategy": "MaterializeScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 2.9048,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 7.8298,
|
|
"rows": 1,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 9.9299,
|
|
"rows": 1,
|
|
"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",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 0.9017,
|
|
"semijoin_strategy_choice": [
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table2`"
|
|
] /* plan_prefix */,
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 2.9048,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 16.667,
|
|
"rows_for_plan": 4,
|
|
"cost_for_plan": 3.8065,
|
|
"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": 1.8233,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1018,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 5.6298,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 7.5298,
|
|
"rows": 1,
|
|
"duplicate_tables_left": true,
|
|
"chosen": true
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"pruned_by_cost": true
|
|
}
|
|
] /* rest_of_plan */
|
|
},
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table2`"
|
|
] /* plan_prefix */,
|
|
"table": "`t1` `table1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"rows": 2,
|
|
"cost": 1.8233,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 4,
|
|
"cost": 2.1017,
|
|
"chosen": false
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 2.725,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "MaterializeScan",
|
|
"choice": "deferred"
|
|
}
|
|
] /* semijoin_strategy_choice */,
|
|
"rest_of_plan": [
|
|
{
|
|
"plan_prefix": [
|
|
"`t1` `table2`",
|
|
"`t1` `table1`"
|
|
] /* plan_prefix */,
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 5.3049,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 16.667,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 8.0299,
|
|
"semijoin_strategy_choice": [
|
|
{
|
|
"strategy": "LooseScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"access_type": "ref",
|
|
"index": "col_int_key",
|
|
"usable": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"rows_to_scan": 4,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"resulting_rows": 4,
|
|
"cost": 0.9017,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"unknown_key_2": {
|
|
"searching_loose_scan_index": {
|
|
"indexes": [
|
|
{
|
|
"index": "col_int_key",
|
|
"covering_scan": {
|
|
"cost": 0.2674,
|
|
"chosen": true
|
|
} /* covering_scan */
|
|
}
|
|
] /* indexes */
|
|
} /* searching_loose_scan_index */
|
|
}
|
|
},
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 5.3049,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 7.7956,
|
|
"rows": 1,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"strategy": "MaterializeScan",
|
|
"recalculate_access_paths_and_cost": {
|
|
"tables": [
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 6,
|
|
"filtering_effect": [
|
|
] /* filtering_effect */,
|
|
"final_filtering_effect": 1,
|
|
"access_type": "scan",
|
|
"using_join_cache": true,
|
|
"buffers_needed": 1,
|
|
"resulting_rows": 6,
|
|
"cost": 2.9048,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */
|
|
}
|
|
] /* tables */
|
|
} /* recalculate_access_paths_and_cost */,
|
|
"cost": 7.8298,
|
|
"rows": 1,
|
|
"duplicate_tables_left": false,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"strategy": "DuplicatesWeedout",
|
|
"cost": 9.9299,
|
|
"rows": 1,
|
|
"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": {
|
|
"columns": 2,
|
|
"row_length": 13,
|
|
"key_length": 14,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": true,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"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` `table1`",
|
|
"rechecking_index_usage": {
|
|
"recheck_reason": "not_first_table",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 4,
|
|
"cost": 3.0017
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "col_int_key",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"col_int_key"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "col_int_key",
|
|
"cost": 0.6674,
|
|
"chosen": true
|
|
} /* best_covering_index_scan */,
|
|
"setup_range_conditions": [
|
|
] /* setup_range_conditions */,
|
|
"group_index_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* group_index_range */,
|
|
"skip_scan_range": {
|
|
"chosen": false,
|
|
"cause": "not_single_table"
|
|
} /* skip_scan_range */,
|
|
"analyzing_range_alternatives": {
|
|
"range_scan_alternatives": [
|
|
{
|
|
"index": "col_int_key",
|
|
"ranges": [
|
|
"NULL < col_int_key"
|
|
] /* ranges */,
|
|
"index_dives_for_eq_ranges": true,
|
|
"rowid_ordered": false,
|
|
"using_mrr": false,
|
|
"index_only": true,
|
|
"rows": 4,
|
|
"cost": 0.6774,
|
|
"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 */
|
|
} /* rechecking_index_usage */
|
|
}
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"attached": "(`where_subselect_20070`.`field1` is not null)"
|
|
},
|
|
{
|
|
"table": "``.`<subquery3>`",
|
|
"attached": "(`<subquery3>`.`field1` = `where_subselect_20070`.`field1`)"
|
|
},
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"attached": "(`table1`.`col_int_key` is not null)"
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"attached": "(`table2`.`col_int_key` = `table1`.`col_int_key`)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`where_subselect_20070`",
|
|
"original_table_condition": "(`where_subselect_20070`.`field1` is not null)",
|
|
"final_table_condition ": "(`where_subselect_20070`.`field1` is not null)"
|
|
},
|
|
{
|
|
"table": "``.`<subquery3>`",
|
|
"original_table_condition": "(`<subquery3>`.`field1` = `where_subselect_20070`.`field1`)",
|
|
"final_table_condition ": null
|
|
},
|
|
{
|
|
"table": "`t1` `table1`",
|
|
"original_table_condition": "(`table1`.`col_int_key` is not null)",
|
|
"final_table_condition ": "(`table1`.`col_int_key` is not null)"
|
|
},
|
|
{
|
|
"table": "`t1` `table2`",
|
|
"original_table_condition": "(`table2`.`col_int_key` = `table1`.`col_int_key`)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`where_subselect_20070`"
|
|
},
|
|
{
|
|
"table": "``.`<subquery3>`"
|
|
},
|
|
{
|
|
"table": "`t1` `table1`"
|
|
},
|
|
{
|
|
"table": "`t1` `table2`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"materialize": {
|
|
"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 */
|
|
} /* materialize */
|
|
},
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"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)"
|
|
}
|
|
] /* 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)))"
|
|
}
|
|
] /* 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)))",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"subselect_evaluation": [
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": "(1 like (/* select#2 */ select `t1`.`a` from `t1` where (`t1`.`a` = 1)))"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"subselect_evaluation": [
|
|
{
|
|
"subselect_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"condition_processing": {
|
|
"condition": "WHERE",
|
|
"original_condition": "(`t1`.`a` = 1)",
|
|
"steps": [
|
|
{
|
|
"transformation": "equality_propagation",
|
|
"resulting_condition": "multiple equal(1, `t1`.`a`)"
|
|
},
|
|
{
|
|
"transformation": "constant_propagation",
|
|
"resulting_condition": "multiple equal(1, `t1`.`a`)"
|
|
},
|
|
{
|
|
"transformation": "trivial_condition_removal",
|
|
"resulting_condition": "multiple equal(1, `t1`.`a`)"
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"ref_optimizer_key_uses": [
|
|
{
|
|
"table": "`t1`",
|
|
"field": "a",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
},
|
|
{
|
|
"table": "`t1`",
|
|
"field": "a",
|
|
"equals": "1",
|
|
"null_rejecting": false
|
|
}
|
|
] /* ref_optimizer_key_uses */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"range_analysis": {
|
|
"table_scan": {
|
|
"rows": 1,
|
|
"cost": 2.45
|
|
} /* table_scan */,
|
|
"potential_range_indexes": [
|
|
{
|
|
"index": "a",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"a"
|
|
] /* key_parts */
|
|
},
|
|
{
|
|
"index": "a_2",
|
|
"usable": true,
|
|
"key_parts": [
|
|
"a",
|
|
"c"
|
|
] /* key_parts */
|
|
}
|
|
] /* potential_range_indexes */,
|
|
"best_covering_index_scan": {
|
|
"index": "a",
|
|
"cost": 0.35,
|
|
"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 */,
|
|
"skip_scan_range": {
|
|
"potential_skip_scan_indexes": [
|
|
{
|
|
"index": "a",
|
|
"usable": false,
|
|
"cause": "no_range_predicate"
|
|
},
|
|
{
|
|
"index": "a_2",
|
|
"usable": false,
|
|
"cause": "no_range_predicate"
|
|
}
|
|
] /* potential_skip_scan_indexes */
|
|
} /* skip_scan_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": false,
|
|
"index_only": true,
|
|
"rows": 1,
|
|
"cost": 0.36,
|
|
"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": 0.36,
|
|
"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": 0.35,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "ref",
|
|
"index": "a_2",
|
|
"rows": 1,
|
|
"cost": 0.35,
|
|
"chosen": false
|
|
},
|
|
{
|
|
"access_type": "scan",
|
|
"chosen": false,
|
|
"cause": "covering_index_better_than_full_scan"
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1,
|
|
"cost_for_plan": 0.35,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": "(`t1`.`a` = 1)",
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": "(`t1`.`a` = 1)"
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
{
|
|
"table": "`t1`",
|
|
"original_table_condition": "(`t1`.`a` = 1)",
|
|
"final_table_condition ": null
|
|
}
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 2,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} /* subselect_execution */
|
|
}
|
|
] /* subselect_evaluation */,
|
|
"resulting_condition": null
|
|
}
|
|
] /* steps */
|
|
} /* condition_processing */
|
|
}
|
|
] /* steps */,
|
|
"empty_result": {
|
|
"cause": "Impossible WHERE"
|
|
} /* empty_result */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #18346750 OPTIMIZER_TRACE & DBUG_PRINT CRASH IN ST_SELECT_LEX::PRINT
|
|
#
|
|
"Skipped since this engine does not support native partitioning.
|
|
#
|
|
# Bug#18791851 CRASH IN ST_SELECT_LEX::PRINT WITH OPTIMIZER_TRACE ON SUBQUERY
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_time_key time,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
CREATE TABLE t2 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
CREATE TABLE t3 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
CREATE TABLE t4 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_time_key time,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_key (col_varchar_key,col_int_key)
|
|
);
|
|
SELECT alias1.col_int_nokey AS field1,
|
|
alias2.col_varchar_key AS field2,
|
|
alias1.col_time_key AS field3,
|
|
MIN( alias1.col_int_nokey ) AS field4
|
|
FROM (
|
|
(
|
|
SELECT SQ1_alias1.*
|
|
FROM ( t1 AS SQ1_alias1, t2 AS SQ1_alias2 )
|
|
) AS alias1,
|
|
t4 AS alias2
|
|
)
|
|
WHERE
|
|
EXISTS (
|
|
SELECT DISTINCT SQ2_alias2.col_varchar_nokey AS SQ2_field1
|
|
FROM t2 AS SQ2_alias1
|
|
INNER JOIN (t4 AS SQ2_alias2
|
|
INNER JOIN t3 AS SQ2_alias3
|
|
ON SQ2_alias3.pk = SQ2_alias2.pk)
|
|
ON SQ2_alias3.col_varchar_key = SQ2_alias2.col_varchar_nokey
|
|
)
|
|
AND alias1.col_int_key = alias2.pk
|
|
HAVING alias1.col_int_nokey IN ( SELECT 2 FROM DUAL ) ;
|
|
field1 field2 field3 field4
|
|
DROP TABLE t1,t2,t3,t4;
|
|
CREATE TABLE t1(a INT);
|
|
SET @a:=(SELECT ROW(1, 2)=
|
|
ROW((SELECT 1 FROM t1 LEFT JOIN t1 t2 ON 1
|
|
HAVING 3 IN (SELECT 2 FROM DUAL)),
|
|
1));
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #18945693 CRASH IN PRINT_TABLE_ARRAY AT SQL/SQL_LEX.CC ON 2ND EXEC OF PREPARED STATEMENT
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1)
|
|
);
|
|
INSERT INTO t1 VALUES (13,7,3,'y','y'),(14,0,4,'c','c');
|
|
CREATE TABLE t2 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1)
|
|
);
|
|
INSERT INTO t2 VALUES (20,9,8,'e','e');
|
|
CREATE TABLE t3 (
|
|
pk INT NOT NULL,
|
|
col_int_nokey INT,
|
|
col_int_key INT,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1)
|
|
);
|
|
INSERT INTO t3 VALUES (1,1,7,'k','k');
|
|
PREPARE prep_stmt FROM "
|
|
SELECT
|
|
alias1.col_varchar_nokey AS field1,
|
|
MAX(alias1.pk) AS field2
|
|
FROM t3 AS alias1
|
|
GROUP BY field1
|
|
HAVING
|
|
1 > (
|
|
SELECT MAX(SQ4_alias1.col_varchar_nokey)
|
|
FROM t1 AS SQ4_alias1
|
|
RIGHT OUTER JOIN
|
|
(
|
|
t2 AS SQ4_alias2
|
|
JOIN t1 AS SQ4_alias3
|
|
ON SQ4_alias3.col_int_key = SQ4_alias2.col_int_nokey
|
|
)
|
|
ON SQ4_alias3.pk = SQ4_alias2.col_int_nokey
|
|
)
|
|
";
|
|
EXECUTE prep_stmt;
|
|
field1 field2
|
|
EXECUTE prep_stmt;
|
|
field1 field2
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# Bug#19063289 CRASH IN PRINT_TABLE_ARRAY ON 2ND EXECUTION OF PS WITH SUBQUERY AND VIEW
|
|
#
|
|
CREATE TABLE B (
|
|
pk INTEGER,
|
|
col_int_key INTEGER,
|
|
col_varchar_nokey VARCHAR(1)
|
|
);
|
|
CREATE TABLE C (
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_varchar_key VARCHAR(1),
|
|
col_varchar_nokey VARCHAR(1)
|
|
) ;
|
|
CREATE TABLE CC (
|
|
pk INTEGER,
|
|
col_int_key INTEGER
|
|
) ENGINE=MYISAM;
|
|
INSERT INTO CC VALUES (1, 0),(2, 7);
|
|
PREPARE stmt FROM "
|
|
SELECT
|
|
alias1.col_int_key AS field1 ,
|
|
COUNT(alias1.pk) AS field2
|
|
FROM
|
|
CC AS alias1
|
|
GROUP BY field1
|
|
HAVING
|
|
alias1.col_int_key >
|
|
(
|
|
SELECT
|
|
MAX( SQ2_alias2.col_int_key ) AS SQ2_field1
|
|
FROM
|
|
C AS SQ2_alias1 LEFT JOIN
|
|
( B AS SQ2_alias2 INNER JOIN C AS SQ2_alias3
|
|
ON 1)
|
|
ON 1
|
|
)
|
|
OR field2 < 2
|
|
";
|
|
EXECUTE stmt;
|
|
field1 field2
|
|
0 1
|
|
7 1
|
|
EXECUTE stmt;
|
|
field1 field2
|
|
0 1
|
|
7 1
|
|
DROP TABLE B,C,CC;
|
|
#
|
|
# Bug #23259872: OPTIMIZER CHOOSES TO USE NON PRIMARY
|
|
# INDEX, EVEN THOUGH COST IS HIGHER
|
|
#
|
|
CREATE TABLE t1 (
|
|
a TINYTEXT NOT NULL,
|
|
b TINYINT(3) UNSIGNED NOT NULL,
|
|
PRIMARY KEY (a(32),b)
|
|
) ENGINE=MyISAM, CHARSET utf8mb4;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (
|
|
a TINYTEXT NOT NULL,
|
|
b TINYINT(3) UNSIGNED NOT NULL,
|
|
PRIMARY KEY (a(32),b)
|
|
) ENGINE=INNODB, CHARSET utf8mb4;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES ('a',1),('a',2);
|
|
INSERT INTO t2 VALUES ('a',1),('a',2),('a',3),('b',1),('c',1),('c',4),('e',2);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 WHERE a='a' AND b in (1,2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (`test`.`t1`.`b` in (1,2)))
|
|
SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
SELECT @trace RLIKE "range_uses_more_keyparts";
|
|
@trace RLIKE "range_uses_more_keyparts"
|
|
1
|
|
EXPLAIN SELECT * FROM t2 WHERE a='a' AND b in (1,2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range PRIMARY PRIMARY 131 NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`a` = 'a') and (`test`.`t2`.`b` in (1,2)))
|
|
SELECT TRACE into @trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
SELECT @trace RLIKE "range_uses_more_keyparts";
|
|
@trace RLIKE "range_uses_more_keyparts"
|
|
1
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug #23227428: SQL PLAN IS NOT ACCORDING WITH OPTIMIZER_TRACE
|
|
#
|
|
CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 CHAR(12)) ENGINE=INNODB;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index NULL PRIMARY 4 NULL 1 100.00 Backward index scan
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` order by `test`.`t1`.`c1` desc limit 1
|
|
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
EXPLAIN SELECT c1, c2 FROM t1 ORDER BY c1 DESC LIMIT 1 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` order by `t1`.`c1` desc limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 1000,
|
|
"cost": 1.5
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 1000,
|
|
"access_type": "scan",
|
|
"resulting_rows": 1000,
|
|
"cost": 101.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 1000,
|
|
"cost_for_plan": 101.5,
|
|
"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 */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`t1`.`c1` desc",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`c1`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`c1` desc"
|
|
} /* simplifying_order_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"reconsidering_access_paths_for_index_ordering": {
|
|
"clause": "ORDER BY",
|
|
"steps": [
|
|
] /* steps */,
|
|
"index_order_summary": {
|
|
"table": "`t1`",
|
|
"index_provides_order": true,
|
|
"order_direction": "desc",
|
|
"index": "PRIMARY",
|
|
"plan_changed": true,
|
|
"access_type": "index"
|
|
} /* index_order_summary */
|
|
} /* reconsidering_access_paths_for_index_ordering */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_explain": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* join_explain */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1;
|
|
# Bug#29016383 - fil_report_invalid_page_access
|
|
CREATE TABLE t1 (
|
|
pk INTEGER PRIMARY KEY,
|
|
a INTEGER,
|
|
b VARCHAR(20)
|
|
);
|
|
CREATE TABLE t2 (
|
|
pk INTEGER PRIMARY KEY,
|
|
c VARCHAR(20),
|
|
KEY (c)
|
|
);
|
|
INSERT INTO t2 VALUES
|
|
(1,'koeiwsgpmfyvvuqvtjnc'),
|
|
(2,'pmfyvvuqvtjncdsvq'),
|
|
(3,'mfyv'),
|
|
(4,NULL);
|
|
SET end_markers_in_json=off;
|
|
SELECT alias1.*
|
|
FROM t2 AS alias1, t2 AS alias2
|
|
WHERE alias2.c IN (
|
|
WITH qn AS (SELECT b FROM t1 WHERE a = alias1.pk)
|
|
SELECT * FROM qn);
|
|
pk c
|
|
# Prove that the predicate from the WHERE clause of the CTE
|
|
# is decorrelated:
|
|
SELECT json_extract(trace,'$**.decorrelated_predicates')
|
|
FROM information_schema.optimizer_trace;
|
|
json_extract(trace,'$**.decorrelated_predicates')
|
|
NULL
|
|
DROP TABLE t1, t2;
|