Bug #115016 Predefined window can't be reused with partition by in an over clause

• Submitted: 15 May 13:53
• Modified: 1 Jul 11:18
• Reporter: Sébastien F.
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[15 May 13:53] Sébastien F.

Description:
The docs about window definition and usage says :

> window_spec:
>    [window_name] [partition_clause] [order_clause] [frame_clause]
> [...]
> If an OVER clause uses OVER (window_name ...) rather than OVER window_name, the named window can be modified by the addition of other clauses. For example, this query defines a window that includes partitioning, and uses ORDER BY in the OVER clauses to modify the window in different ways
> [...]
> An OVER clause can only add properties to a named window, not modify them. If the named window definition includes a partitioning, ordering, or framing property, the OVER clause that refers to the window name cannot also include the same kind of property or an error occurs.

https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html

But it seems we cannot add a PARTITION BY to a predefined WINDOW in an OVER clause despite this WINDOW definition has not a PARTITION BY clause.

How to repeat:
with dataset (continent, country, quantity) as (
    values
        row ('Europa', 'France', 345),
        row ('Asia', 'Japan', 456),
        row ('Europa', 'Germany', 234)
)
select all
    continent, country, quantity, 
    rank() over (w partition by continent) as continent_rank,
    rank() over (w) as global_rank
from dataset
window w as (order by quantity asc)
;

/*

Awaited :

continent country quantity continent_rank global_rank
--------- ------ --------- -------------- -----------
Asia      Japan        456              1           3
Europa    Germany      234              1           1
Europa    France       345              2           2

Gives :

Error Code: 3581. A window which depends on another cannot define partitioning.

*/

[15 May 14:25] MySQL Verification Team

HI Monsieur Sebastien,

Thank you very much for your bug report.

This is a forum for the bug reports with repeatable test cases. Each such test case should consist of the set of SQL statements leading to the bug that is reporting.

Hence, provide us with a full test case and we shall be happy to proceed with processing of your report.

[30 Jun 13:31] Sébastien F.

Hi M. MySQL Verification Team,

The bug is repeatable with the SELECT statement given in the initial message.

Thx,

[1 Jul 11:18] MySQL Verification Team

Hi Mr. F.

You are quite correct.

The test case is fully repeatable.

However, this is already documented as not permissible in window functions:

This construct is not permitted because the OVER clause specifies PARTITION BY for a named window that already has PARTITION BY:

OVER (w PARTITION BY year)
... WINDOW w AS (PARTITION BY country)

Not a bug.