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

Explain shows an execution plan different from actually executed

    XMLWordPrintable

Details

    Description

      For the following query

      (select a from t1 limit 2) order by a desc
      

      EXPLAIN shows

      MariaDB [test]> explain (select a from t1 limit 2) order by a desc;
      +------+-------------+-------+------+---------------+------+---------+------+------+-------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------+
      

      though the execution of the query in debugger clearly indicates that first a temporary table is created for (select a from t1 limit 2) then the rows from this temporary are sorted.
      So the expected output from EXPLAIN would be

      MariaDB [test]> explain (select a from t1 limit 2) order by a desc;
      +------+--------------+----------+------+---------------+------+---------+------+------+----------------+
      | id   | select_type  | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      +------+--------------+----------+------+---------------+------+---------+------+------+----------------+
      |    1 | SIMPLE       | t1       | ALL  | NULL          | NULL | NULL    | NULL |    3 |                |
      | NULL | UNION RESULT | <union1> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using filesort |
      +------+--------------+----------+------+---------------+------+---------+------+------+----------------+
      

      The full test case is:

      create table t1 (a int);
      insert into t1 values (3), (7), (1);
      explain (select a from t1 limit 2) order by a desc;
      drop table t1;
      

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.