MySQL Bugs: #93339: EXPLAIN Join Types range description

Bug #93339	EXPLAIN Join Types range description
Submitted:	26 Nov 2018 9:15	Modified:	27 Nov 2018 14:19
Reporter:	hongyu dong (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi, in the MySQL documentation The description of the range type in the section on EXPLAIN Join Types does not seem to be correct:
"range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

SELECT * FROM tbl_name
  WHERE key_column = 10;
"
"=" Query in the explain output result type should be ref 

How to repeat:
mysql> show create table geek;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geek  | CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from geek where c =10;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | geek  | NULL       | ref  | c             | c    | 4       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

HI,

Thank you for your documentation bug report.

However, I do not think that anything is missing from our Reference Manual. Let me explain ...

If the condition in the WHERE clause would have covered several rows, you would have seen `ref` in the EXPLAIN output. However, since during optimising state, only one row is  found, then the entire table in your example is replaced with one constant, covering all values in that filtered row.

I do think that this is fully covered in our Reference Manual for 5.7, as well as for 8.0, but if I am wrong, please let me know.

But the description in the official documentation says that the equivalent query is also a Range type.

If it is the equivalent query execution plan output type column should be `ref`，will not be `Range`

Hi,

What you are writing about is definitely correct. However, you have missed that part of our Reference Manual, a chapter on the Oprimiser, which states loud and clear what happens if number of found rows is just one.

Not a bug.