Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.4.12, 10.5.5, 10.4(EOL), 10.5
-
None
-
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; |
Thanks for the report.
A version of the same without CTE (if needed for comparison with other databases):