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

EXPLAIN for query based on table value constructor lacks info on used subqueries

    XMLWordPrintable

    Details

      Description

      EXPLAIN output for queries of the the form VALUES v1, ..., vn where all vi are row expressions of the same cardinality does not have any information on used subqueries.
      The following test case demonstrates the problem:

      create table t1 (a int);
      insert into t1 values (3), (7), (1);
      explain values (8), ((select * from t1 where a between 2 and 4));
      explain values  ((select * from t1 where a between 2 and 4)),  ((select * from t1 where a > 10));
      explain values (10,11), ((select * from t1 where a = 7) + 1, 21);
      drop table t1;
      

      When running this above explains the server returns:

      MariaDB [test]> explain values (8), ((select * from t1 where a between 2 and 4));
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> explain values  ((select * from t1 where a between 2 and 4)),  ((select * from t1 where a > 10));
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> explain values (10,11), ((select * from t1 where a = 7) + 1, 21);
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------------+
      1 row in set (0.001 sec)
      

      One can see that there are no plans for subqueries though all the queries can be excuted:

      MariaDB [test]> values (8), ((select * from t1 where a between 2 and 4));
      +------+
      | 8    |
      +------+
      |    8 |
      |    3 |
      +------+
      2 rows in set (0.002 sec)
       
      MariaDB [test]> values  ((select * from t1 where a between 2 and 4)),  ((select * from t1 where a > 10));
      +--------------------------------------------+
      | (select * from t1 where a between 2 and 4) |
      +--------------------------------------------+
      |                                          3 |
      |                                       NULL |
      +--------------------------------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> values (10,11), ((select * from t1 where a = 7) + 1, 21);
      +------+----+
      | 10   | 11 |
      +------+----+
      |   10 | 11 |
      |    8 | 21 |
      +------+----+
      2 rows in set (0.002 sec)
      

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: