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

ONLY_FULL_GROUP_BY not restricting ORDER BY

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.5.8
    • N/A
    • Parser
    • None

    Description

      ONLY_FULL_GROUP_BY does not appear to be restricting ORDER BY when I would have thought it would. For example, in the following, the ORDER BY specifies a non-grouped column leading to non-determinate results:

      create table documentdb (timestamp datetime, filename text) select now() timestamp, 'foo' filename union all select now() - interval 2 minute, 'foo' union all select now() - interval 1 minute, 'bar';
      set session sql_mode=concat('only_full_group_by,',@@sql_mode);
      select filename from documentdb group by filename order by timestamp desc limit 10;
      

      produces

      foo
      bar
      

      mysql 8 gives an error, as I would have expected mariadb to do: "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'db_116865070.documentdb.timestamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              ysth Yitzchak
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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