Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23866

LAG() Window Function Error with ONLY_FULL_GROUP_BY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.4.12, 10.4, 10.5, 10.5.5
    • Fix Version/s: 10.4, 10.5
    • 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;
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            brandonkirsch Brandon Kirsch
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration