TYPO3 Core

Consider a table tx_ext_domain_model_thing, which has a field categories for sys_categories.
The category relations are stored in the Core's MM table.

The generated TCA for the categories field contains MM_matchFields as follows:

'MM_match_fields' => [
    'tablenames' => $tableName,
    'fieldname' => $fieldName,
],

Running the following extbase query should result in a list of tx_ext_domain_model_thing Models ordered by their first assigned category's title (if any assigned):

$q->setOrderings([
    'categories.title' => ...
]);

What actually happens is that the sorting seems to be rather "random".

The reason is the executed SQL statement, which is:

SELECT tx_ext_domain_model_thing.uid, tx_ext_domain_model_thing.title, sys_category.title, sys_category_record_mm.* FROM tx_ext_domain_model_thing
  LEFT JOIN sys_category_record_mm AS sys_category_record_mm ON tx_ext_domain_model_thing.uid=sys_category_record_mm.uid_foreign
  LEFT JOIN sys_category AS sys_category ON sys_category_record_mm.uid_local=sys_category.uid AND sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing' AND sys_category_record_mm.fieldname = 'categories'
WHERE 1=1 AND (tx_ext_domain_model_thing.sys_language_uid IN (0,-1))
      AND (((sys_category.sys_language_uid IN (0,-1))) OR sys_category.uid IS NULL)
      AND tx_ext_domain_model_thing.deleted=0
      AND ((sys_category.deleted=0) OR sys_category.uid IS NULL)
ORDER BY sys_category.title ASC

As you can see the important part sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing' is attached to the second LEFT JOIN. This means that the first LEFT JOIN with the MM table actually includes all records without any limitation to the matchFields.

The correct query would be:

SELECT tx_ext_domain_model_thing.uid, tx_ext_domain_model_thing.title, sys_category.title, sys_category_record_mm.* FROM tx_ext_domain_model_thing
  LEFT JOIN sys_category_record_mm AS sys_category_record_mm ON tx_ext_domain_model_thing.uid=sys_category_record_mm.uid_foreign AND sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing' AND sys_category_record_mm.fieldname = 'categories'
  LEFT JOIN sys_category AS sys_category ON sys_category_record_mm.uid_local=sys_category.uid
WHERE 1=1 AND (tx_ext_domain_model_thing.sys_language_uid IN (0,-1))
      AND (((sys_category.sys_language_uid IN (0,-1))) OR sys_category.uid IS NULL)
      AND tx_ext_domain_model_thing.deleted=0
      AND ((sys_category.deleted=0) OR sys_category.uid IS NULL)
ORDER BY sys_category.title ASC

\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::addUnionStatement() is building this code and the fix is a one line change.