I'm filing a task for my proposed core change since it looks like it'll need quite a few extension updates, and it's nice to have a task to link extension updates together and to refer to in release notes.
Following the SQL standard, PostgreSQL throws an error if you try to select fields that have ambiguous values according to the GROUP BY clause. In MySQL, this is optional -- by default it will just give you some random value from the selected rows.
> CREATE TABLE test (a INT, b INT); > INSERT INTO test VALUES (1, 1), (1, 2), (2, 1), (2, 2); > SELECT a, b FROM test GROUP BY a; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 1 | +------+------+ 2 rows in set (0.001 sec)
The value of b here is 1 by some accident of the order in which rows were scanned.
With sql_mode='ONLY_FULL_GROUP_BY', MySQL matches PostgreSQL's behaviour:
> SET sql_mode='ONLY_FULL_GROUP_BY'; > SELECT a, b FROM test GROUP BY a; ERROR 1055 (42000): 'mw.test.b' isn't in GROUP BY
PostgreSQL does not have a corresponding mode in which it is as lax as MySQL. So ONLY_FULL_GROUP_BY is the only way to get consistent behaviour between the two DBMSs. As I wrote in T164898, the maintenance burden of PostgreSQL support is minimised when they are as consistent as possible.
The initial change I'm proposing in this direction is to use ONLY_FULL_GROUP_BY during PHPUnit tests. Core tests are fine with this, since they are already run against PostgreSQL in CI, but some extension tests break.