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;
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;