Bug #103609 select #2 will be displayed twice when we explain the sql
Submitted: 6 May 12:16 Modified: 6 May 12:40
Reporter: miao ma Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0, 8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[6 May 12:16] miao ma
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;
[6 May 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 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/