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

ONLY_FULL_GROUP_BY doesn't prevent ORDER BY from using a column missing in GROUP BY

    XMLWordPrintable

    Details

      Description

      Please fix "Fix Version/s" as needed, if needed, I'm not sure in which versions it can and should be fixed. MySQL apparently fixed it in 5.6+, but whether it was before or after 5.6 became GA, I can't say.

      set sql_mode=only_full_group_by;
       
      create table t (a int, b int, c int);
      insert into t values (1,2,3),(4,5,6);
      select a, min(b) from t group by a order by c, a;
       
      drop table t;
      

      10.7 f5ecaf23

      set sql_mode=only_full_group_by;
      create table t (a int, b int, c int);
      insert into t values (1,2,3),(4,5,6);
      select a, min(b) from t group by a order by c, a;
      a	min(b)
      1	2
      4	5
      

      Active versions of MySQL prohibit it:

      MySQL 5.6.51

      set sql_mode=only_full_group_by;
      create table t (a int, b int, c int);
      insert into t values (1,2,3),(4,5,6);
      bug.t                                    [ fail ]
              Test ended at 2021-12-19 20:40:36
       
      CURRENT_TEST: bug.t
      mysqltest: At line 5: query 'select a, min(b) from t group by a order by c, a' failed: 1055: 'test.t.c' isn't in GROUP BY
      

      So does PostgreSQL:

      psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
      Type "help" for help.
       
      postgres=# drop table t;
      ERROR:  table "t" does not exist
      postgres=# create table t (a int, b int, c int);
      CREATE TABLE
      postgres=# insert into t values (1,2,3),(4,5,6);
      INSERT 0 2
      postgres=# select a, min(b) from t group by a order by c, a;
      ERROR:  column "t.c" must appear in the GROUP BY clause or be used in an aggregate function
      LINE 1: select a, min(b) from t group by a order by c, a;
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.