[#MDEV-13052] Window function aliases override table columns in OVER (ORDER BY)

[MDEV-13052] Window function aliases override table columns in OVER (ORDER BY) Created: 2017-06-10 Updated: 2023-04-27
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Optimizer - Window functions
Affects Version/s:	10.2.6, 10.2, 10.3, 10.4
Fix Version/s:	10.4

Type:

Bug

Priority:

Major

Reporter:

Vicențiu Ciorbaru

Assignee:

Vicențiu Ciorbaru

Resolution:

Unresolved

Votes:

Labels:

None

Description

The following examples works in PostGRES but fails in MariaDB:

create table t1 (pk varchar(10), a int);

insert into t1 values ('a', 1);

insert into t1 values ('b', 2);

insert into t1 values ('c', 3);

insert into t1 values ('d', 4);

select row_number() over (order by pk) pk, a from t1;

The select statement fails with:

Window function is not allowed in window specification

While this runs perfectly in PostGRES.

test=# select row_number() over (order by pk) pk, pk, a from test;

 pk | pk | a

----+----+---

  1 | a  | 1

  2 | b  | 2

Generated at Thu Feb 08 08:02:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.