Description:
I run this query (which looks artificial, but I think the issue can be replicated on real world queries, too) :
explain format=json select * from one_k where a <500 or a+1 > (select max(ten.a) from ten where ten.a < one_k.a) order by a+1;
{
"query_block": {
"select_id": 1,
"ordering_operation": {
"using_filesort": true,
"table": {
"table_name": "one_k",
"access_type": "ALL",
"rows": 1000,
"filtered": 100,
"attached_condition": "((`test`.`one_k`.`a` < 500) or ((`test`.`one_k`.`a` + 1) > (/* select#2 */ select max(`test`.`ten`.`a`) from `test`.`ten` where (`test`.`ten`.`a` < `test`.`one_k`.`a`))))"
},
"optimized_away_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"table": {
"table_name": "ten",
"access_type": "ALL",
"rows": 10,
"filtered": 100,
"attached_condition": "(`test`.`ten`.`a` < `test`.`one_k`.`a`)"
}
}
}
]
}
}
}
The subquery is listed in "optimized_away_subqueries". However, when I run the SELECT in debugger, I see that the subquery is repeatedly executed:
Breakpoint 7, JOIN::exec (this=0x9d03430) at /home/psergey/dev2/mysql-5.6-ga/sql/sql_executor.cc:97
(gdb) p select_lex->select_number
$17 = 2
(gdb) c
Continuing.
Breakpoint 7, JOIN::exec (this=0x9d03430) at /home/psergey/dev2/mysql-5.6-ga/sql/sql_executor.cc:97
(gdb) p select_lex->select_number
$18 = 2
...
So, the subquery is not optimized away.
How to repeat:
create table ten (a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k (a int);
insert into one_k select A.a + 10*B.a +100*C.a from ten A, ten B, ten C;
explain format=json select * from one_k where a <500 or a+1 > (select max(ten.a) from ten where ten.a < one_k.a) order by a+1;