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

ONLY_FULL_GROUP_BY not restricting ORDER BY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 10.5.8
    • Fix Version/s: N/A
    • Component/s: Parser
    • Labels:
      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

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

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration