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: CPU Architecture:Any

[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] MySQL Verification Team
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.