| Bug #103609 | select #2 will be displayed twice when we explain the sql | ||
|---|---|---|---|
| Submitted: | 6 May 2021 12:16 | Modified: | 3 Jan 2024 7:58 |
| Reporter: | miao ma | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0, 8.0.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 May 2021 12:40]
MySQL Verification Team
Hello miao ma, Thank you for the report and test case. Observed that 8.0.24 release build is affected. regards, Umesh
[6 May 2021 12:40]
MySQL Verification Team
Please note that in order to sumit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html If you have any questions, please contact the MySQL community team (mailto:mysql-community_ww@oracle.com) or by visiting https://dev.mysql.com/community/
[3 Jan 2024 7:58]
Jon Stephens
Documented fix as follows in the MySQL 8.3.0 changelog:
If the argument to a window function contained a subquery, the
access path of that subquery was printed twice by EXPLAIN
FORMAT=TREE. We fix this by ensuring that we ignore duplicate
paths in such cases.
Closed.
Description: In this sql, the explain output will show the select #2 twice. explain format = tree SELECT LAST_VALUE((SELECT upper.j FROM t LIMIT 1)) OVER (PARTITION BY i) FROM t AS upper; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Window aggregate with buffering: last_value((select #2)) OVER (PARTITION BY upper.i ) -> Sort: upper.i (cost=0.75 rows=5) -> Table scan on upper (cost=0.75 rows=5) -> Select #2 (subquery in projection; dependent) -> Limit: 1 row(s) (cost=0.75 rows=1) -> Table scan on t (cost=0.75 rows=5) -> Select #2 (subquery in projection; dependent) -> Limit: 1 row(s) (cost=0.75 rows=1) -> Table scan on t (cost=0.75 rows=5) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ How to repeat: CREATE TABLE t (i char(10), j int); INSERT INTO t VALUES('A', 1); INSERT INTO t VALUES('A', 3); INSERT INTO t VALUES('A', 5); INSERT INTO t VALUES('B', 1); INSERT INTO t VALUES('B', 7); explain format = tree SELECT LAST_VALUE((SELECT upper.j FROM t LIMIT 1)) OVER (PARTITION BY i) FROM t AS upper; Suggested fix: --- a/sql/join_optimizer/explain_access_path.cc +++ b/sql/join_optimizer/explain_access_path.cc @@ -129,6 +129,7 @@ static bool MaterializeIsDoingDeduplication(TABLE *table) { static void GetAccessPathsFromItem(Item *item_arg, const char *source_text, vector<ExplainData::Child> *children) { + if (item_arg->m_is_window_function) return; WalkItem(item_arg, enum_walk::POSTFIX, [children, source_text](Item *item) { if (item->type() != Item::SUBSELECT_ITEM) { return false;