[MDEV-23866] LAG() Window Function Error with ONLY_FULL_GROUP_BY Created: 2020-10-01  Updated: 2020-10-18

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.4.12, 10.5.5, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Minor
Reporter: Brandon Kirsch Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: None
Environment:

10.4.12-MariaDB is running on Centos 7.8.2003
10.5.5-MariaDB-1:10.5.5+maria~focal is running in Docker using the official MariaDB image


 Description   

The LAG() window function incorrectly triggers an error when the SQL_MODE "ONLY_FULL_GROUP_BY" is enabled.

The error message reads:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
SQLState: 42000
ErrorCode: 1140

This error should not occur because LAG() is not an aggregate function - it is a window function - and so there is no requirement for a GROUP BY clause.

Example

-- Works as expected
 
set @@sql_mode = '';
 
with cte as (
 
	select 1 as id
 
)
 
select id, lag(id) over (order by id) from cte;
 
 
 
-- Throws unexpected error: "Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause"
 
set @@sql_mode = 'ONLY_FULL_GROUP_BY';
 
with cte as (
 
	select 1 as id
 
)
 
select id, lag(id) over (order by id) from cte;



 Comments   
Comment by Elena Stepanova [ 2020-10-18 ]

Thanks for the report.

A version of the same without CTE (if needed for comparison with other databases):

create table t1 (id int);
 
set sql_mode='';
 
select id, lag(id) over (order by id) from t1;
 
set sql_mode='ONLY_FULL_GROUP_BY';
 
select id, lag(id) over (order by id) from t1;
 
drop table t1;


At line 5: query 'select id, lag(id) over (order by id) from t1' failed: 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

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