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

Explain shows an execution plan different from actually executed

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2(EOL), 10.3(EOL)
    • 10.2.39, 10.3.30
    • Optimizer
    • None

    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

          Ascending order - Click to sort in descending order
          igor Igor Babaev (Inactive) added a comment -

          This is another test case that produces a wrong explain:

          create table t2 (a int, b int);
          		 
          insert into t2 values (3,70), (7,10), (1,40), (4,30);
          		 
          explain (select b,a from t2 order by a limit 3) order by b desc;


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

          igor Igor Babaev (Inactive) added a comment - This is another test case that produces a wrong explain: create table t2 (a int , b int ); insert into t2 values (3,70), (7,10), (1,40), (4,30); explain ( select b,a from t2 order by a limit 3) order by b desc ; MariaDB [test]> explain (select b,a from t2 order by a limit 3) order by b desc; +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
          igor Igor Babaev (Inactive) added a comment -

          Roman, please click 'Review done' and assign the task back to me.
          Just in case you can look at the patch in bb-10.2-igor (https://buildbot.mariadb.org/#/grid?branch=bb-10.2-igor).
          Thanks.

          igor Igor Babaev (Inactive) added a comment - Roman, please click 'Review done' and assign the task back to me. Just in case you can look at the patch in bb-10.2-igor ( https://buildbot.mariadb.org/#/grid?branch=bb-10.2-igor ). Thanks.
          marko Marko Mäkelä added a comment -

          This was already pushed to 10.2, but a merge to 10.3 is not trivial (my attempts would break the table value constructor tests) and hence this must be fixed in 10.3 separately.

          marko Marko Mäkelä added a comment - This was already pushed to 10.2, but a merge to 10.3 is not trivial (my attempts would break the table value constructor tests) and hence this must be fixed in 10.3 separately.
          igor Igor Babaev (Inactive) added a comment -

          A fix for this bug was pushed into 10.2. An adjusted fix was pushed into 10.3

          igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.2. An adjusted fix was pushed into 10.3

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            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.