Remove ONLY_FULL_GROUP_BY from incompatible wpdb modes

[malthert]

as GROUP BY is non-deterministic otherwise, ONLY_FULL_GROUP_BY ensures this is not the case.
which is why this is enabled by default since MySQL 8

I guess it was disabled originally, as some queries would have to be updated in WP core.

I think however, this should be done now, as otherwise we're accumulating more and more technical debt.

[SergeyBiryukov]

Thanks for the ticket!

Introduced in [27072] / #26847, see comment:2:ticket:26847 for context.

Related: #44586, #48280, #48377, #49344

[OllieJones]

Investigated. Here's how this works.

WordPress turns off ONLY_FULL_GROUP_BY mode if it is on by default in the MariaDB / MySQL database server configuration. (It does the same for 'NO_ZERO_DATE', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'TRADITIONAL', and 'ANSI' modes).

If we stop turning off ONLY_FULL_GROUP_BY we cause various plugins (among them WooCommerce) to start throwing 1140 errors as well as other group by errors. So we can't just take this out of core without breaking plugins.

A plugin or theme author who wants to remediate their legacy use of MySQL's nonstandard permissive GROUP BY functionality can test by using the ​incompatible_sql_modes filter to remove the 'ONLY_FULL_GROUP_BY' element from the list of modes to turn off.

But they'd best avoid leaving that filter in place when releasing their code.