Bug #115927 ONLY_FULL_GROUP_BY cannot be disabled using sql_mode
Submitted: 26 Aug 2024 8:02 Modified: 26 Aug 2024 15:16
Reporter: Soner Sayakci Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.39 OS:Linux (Official Docker Image)
Assigned to: MySQL Verification Team CPU Architecture:Any (mysql:8.0.39)

[26 Aug 2024 8:02] Soner Sayakci
Description:
I tried to disable only full group by using sql_mode or directly as a start parameter to mysqld binary both ways seems not to work anymore.

This seems to work on MariaDB as expected, but not on MySQL.

MySQL 8: https://www.db-fiddle.com/f/qmPa2WLz2orDUtwntvtgSf/0
MariaDB 10.9: https://dbfiddle.uk/_8OfTNbP

The error:

Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

How to repeat:
CREATE TABLE t1
(
    `id`                binary(16) NOT NULL,
    `version_id`        binary(16) NOT NULL,
    `parent_id`         binary(16)              DEFAULT NULL,
    `parent_version_id` binary(16)              DEFAULT NULL,
    `purchase_unit`     decimal(11, 4) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`, `version_id`),
    KEY t1_parent (`parent_id`, `parent_version_id`),
    CONSTRAINT t1_parent FOREIGN KEY (`parent_id`, `parent_version_id`) REFERENCES t1 (`id`, `version_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

SET sql_mode='NO_ZERO_IN_DATE';

SELECT @@sql_mode;

SELECT
    `t1`.`id`
FROM
    `t1`
        LEFT JOIN `t1` `t1.parent` ON `t1`.`parent_id` = `t1.parent`.`id`
        AND `t1`.`parent_version_id` = `t1.parent`.`version_id`
ORDER BY
    MIN(
            IFNULL(
                    `t1`.`purchase_unit`, `t1.parent`.`purchase_unit`
            )
    ) ASC;

Suggested fix:
Allow this query to be executed again when the sql_mode allows it.
[26 Aug 2024 15:16] MySQL Verification Team
Hi,

This is not a bug. You can read more about the SQL standard wrt this issue in Bug #72512 

Thanks