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

    XMLWordPrintable

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

          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.