MySQL Bugs: #97595: Wrong visual explain for queries using window functions in Workbench

Bug #97595	Wrong visual explain for queries using window functions in Workbench
Submitted:	12 Nov 2019 11:38	Modified:	20 Nov 2019 11:41
Reporter:	Georgi Sotirov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	8.0.18, 8.0.34	OS:	Windows
Assigned to:		CPU Architecture:	x86

Description:
The visual display plan for queries using window functions (see https://dev.mysql.com/doc/refman/8.0/en/window-functions.html) is wrong. Workbench shows only a gray rectangle "query_block#1" (see attached) with total cost and nothing else although traditional explain format and also JSON contain additional information about the query (e.g. tables access, joins, etc). I tried with different queries also with join between tables, but all of them produce the same wrong visual explain. If I remove the window function(s) the visual explain is OK.

I guess the problem could be triggered by the presence of the windowing element in JSON output (described at the bottom of page https://dev.mysql.com/doc/refman/8.0/en/window-function-optimization.html in the manual).

I'm not sure whether it's a regression or a new problem, because I simply haven't noticed it before and I'm currently not able to try it in older versions of Workbench.

How to repeat:
1. Taka a simple query like the one found in the manual:

SELECT val,
       ROW_NUMBER()   OVER w AS 'row_number',
       CUME_DIST()    OVER w AS 'cume_dist',
       PERCENT_RANK() OVER w AS 'percent_rank'
  FROM numbers
WINDOW w AS (ORDER BY val);

2. Paste in Workbench's SQL editor and click on EXPLAIN button. Make sure "Visual Explain" is selected.

3. The visual explain consist just of a gray rectangle labeled "query_block#1" with cost, but no table access or anything else.

Suggested fix:
Display complete visual explain.

Visual explain of a query with window function

Attachment: wb_explain_window_function.png (image/png, text), 1.57 KiB.

EXPLAIN FORMAT=JSON for the query

Attachment: wb_explain_window_function.json (application/json, text), 1.53 KiB.

Hello Georgi Sotirov,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

8.0.18 test results

Attachment: 97595_results.png (image/png, text), 365.73 KiB.

Bug #112640 marked as duplicate of this one