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.
