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

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

          Transition Time In Source Status Execution Times
          Igor Babaev (Inactive) made transition -
          Open In Progress
          5h 6m 1
          Igor Babaev (Inactive) made transition -
          In Progress In Review
          45s 1
          Oleksandr Byelkin made transition -
          In Review Stalled
          11h 4m 1
          Igor Babaev (Inactive) made transition -
          Stalled Closed
          13h 54m 1

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.