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