Bug #38056 EXPLAIN doesn't report "Using where" for a LEFT JOIN query with ON expression
Submitted: 11 Jul 2008 19:14 Modified: 3 Nov 2014 14:22
Reporter: Sergey Petrunya Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1, 6.0-bk, OS:Any
Assigned to: CPU Architecture:Any

[11 Jul 2008 19:14] Sergey Petrunya
Description:
EXPLAIN doesn't report "Using where" for a LEFT JOIN query with ON expression.

How to repeat:
CREATE TABLE t1 ( c1 integer );
INSERT INTO t1 VALUES ( 1 );
INSERT INTO t1 VALUES ( 2 );
INSERT INTO t1 VALUES ( 3 );
INSERT INTO t1 VALUES ( 6 );
CREATE TABLE t2 ( c2 integer );
INSERT INTO t2 VALUES ( 1 );
INSERT INTO t2 VALUES ( 4 );
INSERT INTO t2 VALUES ( 5 );
INSERT INTO t2 VALUES ( 6 );

mysql> explain  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       | 
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

And we see that the EXPLAIN shows no trace of the ON expression. This is wrong.

Suggested fix:
We should either make EXPLAIN distinguish between ON and WHERE, or just always print "Using WHERE" whenever there is a condition attached to the table.
[11 Jul 2008 19:41] Sveta Smirnova
Thank you for the report.

Verified as described.
[11 Jul 2008 19:43] Sveta Smirnova
All versions are affected.
[5 Dec 2010 18:03] Valeriy Kravchuk
5.1.54 is still affected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( c1 integer );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES ( 1 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ( 2 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ( 3 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ( 6 );
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE t2 ( c2 integer );
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO t2 VALUES ( 1 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES ( 4 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES ( 5 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES ( 6 );
Query OK, 1 row affected (0.01 sec)

mysql> explain  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
[11 Jul 2013 12:03] Hartmut Holzgraefe
Still present in 5.5.30:

  +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
  |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------+

but seems to be fixed in 5.6.12

+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 | NULL                                               |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
[3 Nov 2014 14:22] Erlend Dahl
[14 Oct 2014 23:51] Haixiang Li
5.7.6/5.6.16, it is fixed:
--------------------------

1) 5.7.6
--------
mysql> explain  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+--------------------------------------------
--------+
| id | select_type | table | partitions | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra
        |
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+--------------------------------------------
--------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL
  | NULL |    4 |   100.00 | NULL
        |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL
  | NULL |    4 |   100.00 | Using where; Using join buffer (Block Neste
d Loop) |
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+--------------------------------------------
--------+
2 rows in set, 1 warning (0.00 sec)

2) 5.6.16
---------
mysql> explain  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
+----+-------------+-------+------+---------------+------+---------+------+---
---+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  |
rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+---
---+----------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |
 4 | NULL                                               |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |
 4 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+---
---+----------------------------------------------------+
2 rows in set (0.10 sec)

[15 Oct 2014 0:08] Roy Lyseng

What happens when join buffering is turned off?
Please also show what EXPLAIN JSON displays.

[15 Oct 2014 2:47] Haixiang Li

5.7.6:
------

1 join buffer turns on:
----------------------
explain  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+--------------------------------------------
--------+
| id | select_type | table | partitions | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra
        |
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+--------------------------------------------
--------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL
  | NULL |    4 |   100.00 | NULL
        |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL
  | NULL |    4 |   100.00 | Using where; Using join buffer (Block Neste
d Loop) |
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+--------------------------------------------
--------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN FORMAT=JSON SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
...
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6.16"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 4,
          "filtered": 100,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.80",
            "prefix_cost": "1.80",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "c1"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 16,
          "filtered": 100,
          "using_join_buffer": "Block Nested Loop",
          "cost_info": {
            "read_cost": "1.16",
            "eval_cost": "3.20",
            "prefix_cost": "6.16",
            "data_read_per_join": "128"
          },
          "used_columns": [
            "c2"
          ],
          "attached_condition": "<if>(is_not_null_compl(t2), (`xx`.`t1`.`c1`
= `xx`.`t2`.`c2`), true)"
        }
      }
    ]
  }
} |
...

2 join buffer turns off:
------------------------
SET SESSION optimizer_switch='block_nested_loop=off';

mysql> EXPLAIN SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  |
key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL
  | NULL |    4 |   100.00 | NULL        |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL
  | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+-------
--+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN FORMAT=JSON SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 ;
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "9.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "ALL",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 4,
          "filtered": 100,
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "0.80",
            "prefix_cost": "1.80",
            "data_read_per_join": "32"
          },
          "used_columns": [
            "c1"
          ]
        }
      },
      {
        "table": {
          "table_name": "t2",
          "access_type": "ALL",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 16,
          "filtered": 100,
          "cost_info": {
            "read_cost": "4.00",
            "eval_cost": "3.20",
            "prefix_cost": "9.00",
            "data_read_per_join": "128"
          },
          "used_columns": [
            "c2"
          ],
          "attached_condition": "<if>(is_not_null_compl(t2), (`xx`.`t1`.`c1`
= `xx`.`t2`.`c2`), true)"
        }
      }
    ]
  }
} |

[26 Oct 2014 16:47] Haixiang Li

5.7.6:
------
This bug can not be reproduced in 5.7.6.
QEP show 'Using where' in Extra column.