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) created issue -
          igor Igor Babaev (Inactive) made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          igor Igor Babaev (Inactive) made changes -
          Summary Explain shows the plan different from what is executed. Explain shows an execution plan different from actually executed.
          igor Igor Babaev (Inactive) made changes -
          Fix Version/s 10.3 [ 22126 ]
          Affects Version/s 10.3 [ 22126 ]
          Description For the following query
          {code:sql}
          (select a from t1 limit 2) order by a desc
          {code}
          EXPLAIN shows
          {noformat}
          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 | |
          +------+-------------+-------+------+---------------+------+---------+------+------+-------+
          {noformat}
          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
          {noformat}
          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 |
          +------+--------------+----------+------+---------------+------+---------+------+------+----------------+
          {noformat}
          		For the following query
          {code:sql}
          (select a from t1 limit 2) order by a desc
          {code}
          EXPLAIN shows
          {noformat}
          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 | |
          +------+-------------+-------+------+---------------+------+---------+------+------+-------+
          {noformat}
          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
          {noformat}
          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 |
          +------+--------------+----------+------+---------------+------+---------+------+------+----------------+
          {noformat}
          The full test case is:
          {code:sql}
          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;
          {code}
          Summary Explain shows an execution plan different from actually executed. Explain shows an execution plan different from actually executed
          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.
          igor Igor Babaev (Inactive) made changes -
          Status In Progress [ 3 ] In Review [ 10002 ]
          igor Igor Babaev (Inactive) made changes -
          Assignee Igor Babaev [ igor ] Roman [ drrtuy ]
          drrtuy Roman made changes -
          Assignee Roman [ drrtuy ] Igor Babaev [ igor ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          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) made changes -
          Status Stalled [ 10000 ] In Progress [ 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

          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) made changes -
          Fix Version/s 10.2.39 [ 25731 ]
          Fix Version/s 10.3.30 [ 25732 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 121876 ] MariaDB v4 [ 159282 ]

          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.