[MDEV-25032] Window functions without column references get removed from ORDER BY Created: 2021-03-02  Updated: 2021-07-07  Resolved: 2021-07-07

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Vicențiu Ciorbaru Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: None


 Description    

create table t1 (id int, first_name varchar(100), last_name varchar(100), score double);
 
create index t1_name on t1 (first_name, last_name);
 
insert into t1 values
 
(1, 'Alice', 'Fowler', 5),
 
(2, 'John', 'Doe', 6),
 
(3, 'John', 'Smith', 6),
 
(4, 'John', 'Smith', 6),
 
(5, 'John', 'Smith', 7),
 
(6, 'John', 'Elton', 8.1),
 
(7, 'Bob',  'Trasc', 9),
 
(8, 'Silvia', 'Ganush', 10);
 
select first_name fn, last_name ln, row_number() over () rn
 
from t1
 
order by rn desc;


fn	ln	rn
 
Alice	Fowler	1
 
Bob	Trasc	2
 
John	Smith	3
 
John	Smith	4
 
John	Smith	5
 
John	Elton	6
 
John	Doe	7
 
Silvia	Ganush	8

The DESC keyword is being ignored.

 Comments   
Comment by Vicențiu Ciorbaru [ 2021-03-02 ]

Preliminary analysis:

1. The ORDER BY clause drops the window function column as it reports no used tables.

This is apparent when adding a column to the window function's OVER () clause, causing it to report used tables properly:

select first_name fn, last_name ln, row_number() over (ORDER BY first_name) rn
 
from t1
 
order by rn desc;
 
fn	ln	rn
 
Silvia	Ganush	8
 
John	Doe	7
 
John	Elton	6
 
John	Smith	5
 
John	Smith	4
 
John	Smith	3
 
Bob	Trasc	2
 
Alice	Fowler	1

Potential fixes:
a. Change remove_const to properly skip window functions.
b. Change row_number() to report that it's using tables.

Comment by Vicențiu Ciorbaru [ 2021-03-02 ]

Hi Sergei!

Can you please review this fix for window functions.

I chose to go with option 2 (according to my first comment on the issue), as option 1 didn't semantically fit, see patch comment.

https://github.com/MariaDB/server/commit/f4428ec3a2e5c256b3c4af7c9a3aea3892ec7d65

Comment by Sergei Petrunia [ 2021-03-04 ]

Ok to push.

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