Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
-
None
Description
This is to track MySQL Bug# 69638.
The bug is reproducible in MySQL 5.6.13. MariaDB 5.5 is not affected.
The query plan in mysql-5.6 is:
MySQL [bug_having]> explain SELECT tid, (rand()*100) AS r FROM bad group by tid HAVING r < 20 ORDER BY r DESC LIMIT 15;
|
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+
|
| 1 | SIMPLE | bad | range | bad_tid | bad_tid | 8 | NULL | 113 | Using index for group-by; Using temporary; Using filesort |
|
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+
|
In MariaDB 5.5, it is:
MariaDB [bug_having]> explain SELECT tid, (rand()*100) AS r FROM bad group by tid HAVING r < 20 ORDER BY r DESC LIMIT 15;
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|
| 1 | SIMPLE | bad | index | NULL | bad_tid | 8 | NULL | 343 | Using index; Using temporary; Using filesort |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|
The bug is not related to "Using index for group-by" optimization, however. If I disable it (in gdb), MySQL 5.6 produces the same EXPLAIN as MariaDB 5.5 does, but the bug is still there.