ORDER BY optimizer ignores equality propagation

Description

Re-filing this here from https://bugzilla.suse.com/show_bug.cgi?id=949520 :

Consider a query:

1 2 3 4 5 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:

1 2 3 4 5 6 7 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:

1 2 3 4 5 6 7 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.

Environment

None

Status

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

External issue ID

None

External issue ID

None

Components

Sprint

None

Fix versions

Affects versions

10.1

Priority

Critical