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
-
Activity
Testing is in progress. So far no failures revealed, but it's not finished yet.
Update:
- Got a reply from Igor (the reviewer) with ok to push the patch
- The patch will be protected by an @@optimizer_switch flag
= it will be pushed into 10.1, with being off by default
= and into 10.2, with being ON by default.
The patch was pushed into MariaDB-10.1.
The fix is disabled by default in MariaDB, in order to set it one must use:
set optimizer_switch='orderby_uses_equalities=on'
|
The fix will be enabled by default in MariaDB 10.2
We will be able to enable the fix by default in 10.2 after it has been merged to 10.2. Filed MDEV-10174 to track that task.
Status update: