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 OS:Microsoft Windows
Assigned to: CPU Architecture:x86

[12 Nov 2019 11:38] Georgi Sotirov
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.
[12 Nov 2019 11:39] Georgi Sotirov
Visual explain of a query with window function

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

[12 Nov 2019 11:41] Georgi Sotirov
EXPLAIN FORMAT=JSON for the query

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

[20 Nov 2019 11:41] Ashwini Patil
Hello Georgi Sotirov,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil
[20 Nov 2019 11:42] Ashwini Patil
8.0.18 test results

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