[MDEV-17017] Explain for query using derived table specified with a table value constructor shows wrong number of rows - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
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

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2018-08-18 22:42

Igor Babaev (Inactive) made changes - 2018-08-18 22:43

Field	Original Value	New Value
Status	Open [ 1 ]	In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2018-08-19 04:06

Description

If a derived table is specified by only table value constructors then the expected number of rows displayed by EXPLAIN is always 2.
{noformat}
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 (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 (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 | |
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
{noformat}
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
{noformat}
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 | |
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
{noformat}

If a derived table is specified by only table value constructors then the expected number of rows displayed by EXPLAIN is always 2.
{noformat}
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 | |
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
{noformat}
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
{noformat}
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 | |
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
{noformat}

Igor Babaev (Inactive) made changes - 2018-08-20 03:43

Fix Version/s		10.3.10 [ 23140 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Igor Babaev (Inactive) made changes - 2018-08-26 17:16

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Igor Babaev (Inactive) made changes - 2018-08-27 21:15

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 88987 ]

MariaDB v4 [ 154822 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-08-18 22:42

Updated:: 2018-08-28 09:11

Resolved:: 2018-08-27 21:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration