Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8989

ORDER BY optimizer ignores equality propagation

    XMLWordPrintable

Details

    • 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

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.