A 'true' MM relation (select / group with MM, but not inline
with foreign_field - used in core for pages/tt_content table
to sys_category via sys_category_record_mm) has two sorting
fields: 'sorting' for the local (sys_category) side, and
'sorting_foreign' for the foreign (pages / tt_content) side.

When categories are added to a tt_content element,
'sorting_foreign' is set to the given order of sys_category
elements. 'sorting' is set to 0. When then opening a
sys_category record that has multiple records pointing to
it with 0 as sorting, the returned order of records is
non-deterministic and depends on implicit DB engine fallbacks.

This also confuses the reference index, which checks
refindex integrity always from the 'local' side.

The patch adds 'uid_foreign' as second order-by field to
force deterministic ordering. There is still a possible
collision in multi table relations (more than one foreign
table uses the mm table like pages AND tt_content to
sys_category with foreign records having the same uid) and
if the mm table allows "multi" relations. Those scenarios
however have no explicit TCA configuration (only implicit
via MM_match_fields), need a deeper investigation and
possibly further detail patches later.

The patch should for now fix a functional test that is
flaky with postgres.

Change-Id: I3c89d0e67f8a4065354f9df173020ca0080e0d57
Resolves: #93075
Releases: master, 10.4
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/67110


Reviewed-by: Andreas Fernandez <a.fernandez@scripting-base.de>
Reviewed-by: Oliver Bartsch <bo@cedev.de>
Reviewed-by: Benni Mack <benni@typo3.org>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: TYPO3com <noreply@typo3.com>
Tested-by: Oliver Bartsch <bo@cedev.de>
Tested-by: Benni Mack <benni@typo3.org>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>