Bug #69567 EXPLAIN FORMAT=JSON lists subquery in optimized_away_subqueries, but it is run
Submitted: 25 Jun 2013 8:44 Modified: 16 Jun 2016 20:30
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any

[25 Jun 2013 8:44] Sergey Petrunya
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;
[26 Jun 2013 13:40] Umesh Shastry
Hello Sergey,

Thank you for the bug report.

Thanks,
Umesh
[16 Jun 2016 20:30] Sergey Petrunya
Not repeatable on 5.7.12