Bug #70553 EXPLAIN UPDATE shows "Using join buffer" while it is not used
Submitted: 8 Oct 2013 14:23 Modified: 6 Jan 2015 16:01
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.14, 5.7.2-m12 OS:Any
Assigned to:
Triage: Needs Triage: D3 (Medium)

[8 Oct 2013 14:23] Sergey Petrunya
Description:
EXPLAIN UPDATE shows "Using join buffer" where actual execution does not use join buffering.

How to repeat:
Run these commands:

create table t1 (a int);
insert into t1 values 
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (a int, b int);
insert into t2 select a, a from t1;
insert into t2 select a+10, a+10 from t1;

explain update t1, t2 set t2.b=12345 where t1.a=t2.a;

MySQL [j1]> explain update t1, t2 set t2.b=12345 where t1.a=t2.a;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 | NULL                                               |
|  1 | UPDATE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

^^ EXPLAIN shows that join buffering will be used.

MySQL [j1]> flush status;
Query OK, 0 rows affected (0.00 sec)

MySQL [j1]> update t1, t2 set t2.b=12345 where t1.a=t2.a;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

MySQL [j1]> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 10    |
| Handler_read_rnd_next      | 232   |

^^ The value of Handler_read_rnd_next, 232 ~= 10*20 suggest that the join buffering was not used for execution.  Debugging also shows that join buffering is not used for the actual UPDATE statement.
[8 Oct 2013 14:38] Miguel Solorzano
Thank you for the bug report.
[6 Jan 2015 16:01] Paul Dubois
Noted in 5.7.6 changelog.

EXPLAIN output could show "Using join buffer" in some cases when join
buffering was not used.