Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1(EOL)
-
10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1, 10.2.1-3, 10.2.1-4, 10.2.1-5
Description
Re-filing this here from https://bugzilla.suse.com/show_bug.cgi?id=949520 :
Consider a query:
SELECT * FROM Super su
|
JOIN SubA sa on sa.id = su.id
|
ORDER BY
|
sa.id desc
|
LIMIT 10
|
The join optimizer picks the join order of sa, su.
Table sa has an index which allows to satisfy ORDER BY LIMIT clause:
explain SELECT * FROM Super su JOIN SubA sa on su.id = sa.id ORDER BY sa.id desc LIMIT 10;
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------+
|
| 1 | SIMPLE | sa | index | PRIMARY | PRIMARY | 4 | NULL | 10 | |
|
| 1 | SIMPLE | su | eq_ref | PRIMARY | PRIMARY | 4 | Test_Database.sa.id | 1 | |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------+
|
Good so far.
Now, let's try to change ORDER BY sa.id into ORDER BY su.id. The query
has sa.id = su.id, both columns have identical data types, so there should
be no difference.
However, there is:
explain SELECT * FROM Super su JOIN SubA sa on su.id = sa.id ORDER BY su.id desc LIMIT 10;
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
|
| 1 | SIMPLE | sa | ALL | PRIMARY | NULL | NULL | NULL | 4000 | Using temporary; Using filesort |
|
| 1 | SIMPLE | su | eq_ref | PRIMARY | PRIMARY | 4 | Test_Database.sa.id | 1 | |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
|
ORDER BY optimizer no longer recognizes that index on sa.id produces the desired ordering.
Attachments
Issue Links
- relates to
-
MDEV-10174 Make the fix for MDEV-8989 enabled by default in 10.2
-
- Closed
-