Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
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.
Attachments
Activity
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
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:
fn ln rnSilvia Ganush 8John Doe 7John Elton 6John Smith 5John Smith 4John Smith 3Bob Trasc 2Alice Fowler 1Potential fixes:
a. Change remove_const to properly skip window functions.
b. Change row_number() to report that it's using tables.