Description:
Sometimes the first explain for a query works, but the second one fails.
mysql> EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"buffer_result": {
"using_temporary_table": true,
"nested_loop": [
{
"table": {
"table_name": "a1",
"access_type": "ALL",
"rows": 1,
"filtered": 100
}
},
{
"table": {
"table_name": "a2",
"access_type": "ref",
"possible_keys": [
"c1"
],
"key": "c1",
"used_key_parts": [
"c1"
],
"key_length": "5",
"ref": [
"test.a1.c1"
],
"rows": 1,
"filtered": 100,
"using_index": true,
"attached_condition": "<if>(is_not_null_compl(a2), (`test`.`a1`.`c1` = (@`foo`)), true)"
}
},
{
"table": {
"table_name": "t2",
"access_type": "index",
"key": "c1",
"used_key_parts": [
"c1"
],
"key_length": "5",
"rows": 1,
"filtered": 100,
"using_index": true,
"using_join_buffer": "Block Nested Loop",
"attached_condition": "((`test`.`t2`.`c1` = (@`bar`)) and (`test`.`t2`.`c1` = (@`foo`)))"
}
}
]
}
}
}
1 row in set (0.00 sec)
mysql> EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"message": "Impossible WHERE noticed after reading const tables"
}
}
}
1 row in set (0.00 sec)
How to repeat:
CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar;
Description: Sometimes the first explain for a query works, but the second one fails. mysql> EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "buffer_result": { "using_temporary_table": true, "nested_loop": [ { "table": { "table_name": "a1", "access_type": "ALL", "rows": 1, "filtered": 100 } }, { "table": { "table_name": "a2", "access_type": "ref", "possible_keys": [ "c1" ], "key": "c1", "used_key_parts": [ "c1" ], "key_length": "5", "ref": [ "test.a1.c1" ], "rows": 1, "filtered": 100, "using_index": true, "attached_condition": "<if>(is_not_null_compl(a2), (`test`.`a1`.`c1` = (@`foo`)), true)" } }, { "table": { "table_name": "t2", "access_type": "index", "key": "c1", "used_key_parts": [ "c1" ], "key_length": "5", "rows": 1, "filtered": 100, "using_index": true, "using_join_buffer": "Block Nested Loop", "attached_condition": "((`test`.`t2`.`c1` = (@`bar`)) and (`test`.`t2`.`c1` = (@`foo`)))" } } ] } } } 1 row in set (0.00 sec) mysql> EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "table": { "message": "Impossible WHERE noticed after reading const tables" } } } 1 row in set (0.00 sec) How to repeat: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `c1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `c1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 EXPLAIN FORMAT=JSON INSERT INTO t1(c1,c2) SELECT a1.c1, a1.c2 FROM t1 a1 LEFT JOIN t1 a2 ON a1.c1=a2.c1 and a1.c1=@foo JOIN t2 on t2.c1=@foo WHERE t2.c1=@bar;