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

Explain for query using derived table specified with a table value constructor shows wrong number of rows

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3(EOL)
    • 10.3.10
    • Optimizer
    • None

    Description

      If a derived table is specified by only table value constructors then the expected number of rows displayed by EXPLAIN is always 2.

      MariaDB [test]> explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                |
      		 
      |    2 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      MariaDB [test]> explain select * from (values (1,11), (7,77), (3,31), (4,42)) t;   
      +------+-------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      |    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                |
      		 
      |    2 | DERIVED     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      		 
      +------+-------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      2MariaDB [test]> explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
      		 
      +------+--------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      | id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      		 
      +------+--------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      |    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 |                |
      		 
      |    2 | DERIVED      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      		 
      |    3 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      		 
      | NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                |
      		 
      +------+--------------+------------+------+---------------+------+---------+------+------+----------------+

      If the specification of a derived table uses not only table value constructors the EXPLAIN
      still shows a wrong number of expected rows in the derived table

      MariaDB [test]> create table t1 (a int);
      		 
      MariaDB [test]> insert into t1 values (9), (3), (2);
      		 
      Records: 3  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> explain select * from (values (7), (5), (8), (1) union select * from t1) t;
      		 
      +------+--------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      | id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      		 
      +------+--------------+------------+------+---------------+------+---------+------+------+----------------+
      		 
      |    1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |                |
      		 
      |    2 | DERIVED      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      		 
      |    3 | UNION        | t1         | ALL  | NULL          | NULL | NULL    | NULL |    3 |                |
      		 
      | NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                |
      		 
      +------+--------------+------------+------+---------------+------+---------+------+------+----------------+

      Attachments

        Activity

          Transition Time In Source Status Execution Times
          Igor Babaev (Inactive) made transition -
          Open In Progress
          		1m 12s 1
          Igor Babaev (Inactive) made transition -
          In Progress Closed
          		1d 5h 1
          Igor Babaev (Inactive) made transition -
          Closed Stalled
          		6d 13h 33m 1
          Igor Babaev (Inactive) made transition -
          Stalled Closed
          		1d 3h 58m 1

          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.