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

ONLY_FULL_GROUP_BY is ignored for non-aggregated argument of the window function

Details

    Description

      When a column is not a part of GROUP BY expression / an argument of aggregate function and ONLY_FULL_GROUP_BY server SQL mode is enabled then the error 1055 must be generated.
      But when such non-aggregated column is an argument of the window function then ONLY_FULL_GROUP_BY server SQL mode is ignored and the query is executed w/o an error.

      Sample data:

      CREATE TABLE test (id INT, x INT);
      INSERT INTO test VALUES (1,1), (1,2), (2,3), (2,4);
      

      Query:

      SELECT id, SUM(x) sum_x, SUM(x) OVER (PARTITION BY id) wsum_x
      FROM test
      GROUP BY id;
      

      The error "Error 1055: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle.test.x' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" or similar must be reproted - but the query is executed successfully.

      Tested at MariaDB 10.3 and newer.

      Attachments

        Activity

          There are no comments yet on this issue.

          People

            psergei Sergei Petrunia
            Akina Владислав Сокол
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.