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

ORDER BY optimizer ignores equality propagation

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

          relates to

          Task - A task that needs to be done. MDEV-10174 Make the fix for MDEV-8989 enabled by default in 10.2

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Sergei Petrunia made transition -
            Open In Progress
            		61d 1h 16m 1
            Oleksandr Byelkin made transition -
            In Progress In Review
            		24d 28m 1
            Oleksandr Byelkin made transition -
            In Review Stalled
            		3h 1m 1
            Sergei Petrunia made transition -
            Stalled Closed
            		139d 13h 4m 1

            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.