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

Bug #115016	Predefined window can't be reused with partition by in an over clause
Submitted:	15 May 2024 13:53	Modified:	1 Jul 2024 11:18
Reporter:	Sébastien F.	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

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.

*/

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.

Hi M. MySQL Verification Team,

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

Thx,

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.