[MDEV-27395] Named windows do not work with MEDIAN() window function. Created: 2021-12-31  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.6.5, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Oliver Jones Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu

Issue Links:
Relates
relates to MDEV-12985 support percentile and median window ... Closed
relates to MDEV-25640 window functions: named windows aren'... Open

 Description   

It's not possible to use a named WINDOW with the MEDIAN() window function.

This does not work, but should. It gets 1064 near 'types AS med
FROM items
WINDOW types AS (PARTITION BY type)
)
SEL...' at line 7 */

CREATE OR REPLACE TEMPORARY TABLE items (type INTEGER, value INTEGER);
 
INSERT INTO items (type, value) VALUES (1,1), (1,3), (1,4), (2,10), (2,32), (2,42);
 
WITH descriptive_statistics AS (
 
    SELECT TYPE,
 
           ROW_NUMBER() OVER types AS rownum,
 
           MIN(value) OVER types  AS MIN,
 
           MAX(value) OVER types AS MAX,
 
           AVG(value) OVER types AS AVG,
 
           MEDIAN(VALUE) OVER types AS med   // <-gacks here. 
	   FROM items
 
    WINDOW types AS (PARTITION BY type)
 
)
 
SELECT VERSION(), TYPE, MIN, MAX, AVG, med
 
FROM descriptive_statistics 
WHERE rownum = 1

This does work:

CREATE TEMPORARY TABLE items (type INTEGER, value INTEGER);
 
INSERT INTO items (type, value) VALUES (1,1), (1,3), (1,4), (2,10), (2,32), (2,42);
 
WITH descriptive_statistics AS (
 
    SELECT TYPE,
 
           ROW_NUMBER() OVER types AS rownum,
 
           MIN(value) OVER types  AS MIN,
 
           MAX(value) OVER types AS MAX,
 
           AVG(value) OVER types AS AVG,
 
           MEDIAN(VALUE) OVER (PARTITION BY type) AS Med
 
	   FROM items
 
    WINDOW types AS (PARTITION BY type)
 
)
 
SELECT VERSION(), TYPE, MIN, MAX, AVG, med
 
FROM descriptive_statistics 
WHERE rownum = 1



 Comments   
Comment by Alice Sherepa [ 2022-01-03 ]

Thanks for the report!
The same applies to PERCENTILE_CONT and PERCENTILE_DISC

Comment by Oliver Jones [ 2022-01-04 ]

You're welcome. Thanks for the great RDBMS!

Generated at Thu Feb 08 09:52:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.