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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3
    • Fix Version/s: 10.3.10
    • Component/s: Optimizer
    • Labels:
      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

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: