Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3
-
None
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)
|