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

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            Description Re-filing this here from https://bugzilla.suse.com/show_bug.cgi?id=949520 :

            Consider a query:
            {{noformat}}
            SELECT * FROM Super su
              JOIN SubA sa on sa.id = su.id
            ORDER BY
              sa.id desc
            LIMIT 10
            {{noformat}}

            The join optimizer picks the join order of {{sa, su}}.
            Table {{sa}} has an index which allows to satisfy ORDER BY LIMIT clause:

            {noformat}
            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 | |
            +------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------+
            {noformat}

            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:

            {noformat}
            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 | |
            +------+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
            {noformat}

            ORDER BY optimizer no longer recognizes that index on sa.id produces the desired ordering.
            		 Re-filing this here from https://bugzilla.suse.com/show_bug.cgi?id=949520 :

            Consider a query:
            {noformat}
            SELECT * FROM Super su
              JOIN SubA sa on sa.id = su.id
            ORDER BY
              sa.id desc
            LIMIT 10
            {noformat}

            The join optimizer picks the join order of {{sa, su}}.
            Table {{sa}} has an index which allows to satisfy ORDER BY LIMIT clause:

            {noformat}
            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 | |
            +------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------+
            {noformat}

            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:

            {noformat}
            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 | |
            +------+-------------+-------+--------+---------------+---------+---------+---------------------+------+---------------------------------+
            {noformat}

            ORDER BY optimizer no longer recognizes that index on sa.id produces the desired ordering.
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Sprint 10.1.10 [ 24 ]
            psergei Sergei Petrunia made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Rank Ranked higher
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10 [ 24 ] 10.1.10, 10.2.0-4 [ 24, 29 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            sanja Oleksandr Byelkin made changes -
            Assignee Sergei Petrunia [ psergey ] Oleksandr Byelkin [ sanja ]
            sanja Oleksandr Byelkin made changes -
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Sergei Petrunia [ psergey ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4 [ 24, 29 ] 10.1.10, 10.2.0-4, 10.1.11 [ 24, 29, 30 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            nirbhay_c Nirbhay Choubey (Inactive) made changes -
            Labels order-by-optimization SUSE order-by-optimization
            psergei Sergei Petrunia made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11 [ 24, 29, 30 ] 10.1.10, 10.2.0-4, 10.1.11, 10.1.12 [ 24, 29, 30, 36 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11, 10.1.12 [ 24, 29, 30, 36 ] 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.1.14 [ 24, 29, 30, 36, 51 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.1.14 [ 24, 29, 30, 36, 51 ] 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1 [ 24, 29, 30, 36, 56 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1 [ 24, 29, 30, 36, 56 ] 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1, 10.2.1-2 [ 24, 29, 30, 36, 56, 63 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1, 10.2.1-2 [ 24, 29, 30, 36, 56, 63 ] 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1 [ 24, 29, 30, 36, 56 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked higher
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1 [ 24, 29, 30, 36, 56 ] 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1, 10.2.1-3 [ 24, 29, 30, 36, 56, 65 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Rank Ranked lower
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 10.1.10, 10.2.0-4, 10.1.11, 10.1.12, 10.2.1-1, 10.2.1-3 [ 24, 29, 30, 36, 56, 65 ] 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 [ 24, 29, 30, 36, 56, 65, 66 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Sprint 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 [ 24, 29, 30, 36, 56, 65, 66 ] 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 [ 24, 29, 30, 36, 56, 65, 66, 68 ]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.1.15 [ 22018 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 72234 ] MariaDB v4 [ 149741 ]

            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.