TYPO3 Core

The test is based on the assumption that a relational database can easily sort across a M:N relation. This is not the case, sorting across M:N relations using a series of joins is an indeterministic operation at best and not supported by any standard compliant database.

The reason for this is that GROUP BY evaluation happens before ORDER BY. Under the assumption that a Post has two categories assigned the row which should be the result of the aggregation can not reliably be determined by the database and a random record based on the joined rows would have to be chosen. In one query this could be the Post joined to the first category, in another the Post that is joined to the second category.

As this is not deterministic this will lead to failures on most databases. MySQL has an operating mode which enables this random selection (which was enabled by default until MySQL 5.7.5) and as such would execute the query without a hard error.

To exemplify this assume the following join result: (before grouping and ordering)

post.uid | post.title | category.title
--------------------------------------
4        | Post 4     | Cat 2
3        | Post 3     | Cat 2
1        | Post 1     | Cat 1
2        | Post 2     | Cat 5
4        | Post 4     | Cat 3
4        | Post 4     | Cat 1

What would the correct reduction be after the grouping step?
First rows?

post.uid | post.title | category.title
--------------------------------------
4        | Post 4     | Cat 2
3        | Post 3     | Cat 2
1        | Post 1     | Cat 1
2        | Post 2     | Cat 5

Last rows?

post.uid | post.title | category.title
--------------------------------------
3        | Post 3     | Cat 2
1        | Post 1     | Cat 1
2        | Post 2     | Cat 5
4        | Post 4     | Cat 1

A random one?

post.uid | post.title | category.title
--------------------------------------
3        | Post 3     | Cat 2
1        | Post 1     | Cat 1
2        | Post 2     | Cat 5
4        | Post 4     | Cat 3

Given the uncertainties in this step the outcome of the order by could be that Post 4 is listed with Cat 1,2 or 3 and all would be "correct" by definition as there is no rule as to how to reduce this.

As this is a limitation of the database engines/the undefined expected outcome I would remove this test entirely.

If this works on a MySQL database that has the partial group by extension activated and the user can live with possibly random results give them the results, otherwise let the query fail with an appropriate message from the database:

MySQL:

TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'test_ftb66966d.sys_category.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

PostgreSQL:

TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException: SQLSTATE[42803]: Grouping error: 7 ERROR:  column "sys_category.title" must appear in the GROUP BY clause or be used in an aggregate function