Review main Merged

95751[BUGFIX] Respect uidInList order in database queries

Wouter Wolters CR +2 V +2 Normal +151 −1 updated 09/25/26, 20:29

Commit message

When `select.uidInList` is used without an explicit `orderBy`, the
records are now sorted by the position of their uid in the list,
using a portable `CASE` expression, instead of the undefined order
returned by the database. Sorting happens in SQL, so the order is
kept when `max` and `begin` are used.

In workspace previews, move pointers are sorted at the position of
their live record. An explicit `orderBy` still takes precedence,
and no sorting is added for `groupBy`, `DISTINCT` or aggregate select
fields, as this is invalid SQL on some database platforms.

The `CASE` expression is assembled manually for now and a `@todo`
marks it for a dedicated `ExpressionBuilder` method in a follow-up.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
    • Benni Mack +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Oliver Klee +1
    • Benni Mack +1
    • core-ci +1 CI

Issues 1

Files 2

Review log 1520

  1. Wouter Wolters uploaded patch set 1 09/09/26, 20:52

  2. core-ci voted on patch set 1 V +1 09/09/26, 21:00 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/108216

  3. Wouter Wolters marked it ready for review 09/14/26, 20:15

  4. Simon Schaufelberger voted on patch set 1 CR +1 V +1 09/22/26, 11:02

  5. core-ci voted on patch set 1 V -1 09/25/26, 08:39 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109231

  6. Wouter Wolters rebased onto patch set 2 CR +1 V +1 09/25/26, 10:22

  7. core-ci voted on patch set 2 V +1 09/25/26, 10:30 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109236

  8. Oliver Klee voted on patch set 2 V +1 09/25/26, 10:33

  9. Stefan Bürk commented on patch set 2 CR -1 09/25/26, 10:46

    On it ... want to check/investigate something ...
  10. Stefan Bürk commented on patch set 2 09/25/26, 17:35

    Reply

    ... still on it, try to add/implement my comments and verify them, then pushing them directly.

    ContentObjectRenderer.php:4580

    This guard is needed: without it, postgres fails for `count(*)` with error "must appear in the GROUP BY clause", and both postgres and mysql fail for `DISTINCT` and `groupBy`. Mirroring the `sanitizeSelectPart()` regex is fine for a bugfix that is backported. Just for the record, the regex is not exhaustive: * It misses `COUNT (*)`, `string_agg()`, `GROUP_CONCAT()` and similar. * `distinct` has no word boundary, so a column like `tx_distinct_flag` disables the ordering silently. That direction is harmless.

    ContentObjectRenderer.php:4694

    Technically not required here, but it shotens the generated `CASE` query part later. `CASE`returns the first matchin `WHEN` value, so a duplicate `uid` keeps its first position anyway and the same is also correct for `array_values()`, since gaps in the positions would not matter. Let's keep it.

    ContentObjectRenderer.php:4714

    We have the `ExpressionBuilder` available (`$expressionBuilder`), so we should use that instead of manully composing database query parts whereever possible. Counts for the comparision (`->eq()`) and also for the or par (`->or()`). `CASE <WHEN ... THEN ...>... ELSE ... END` is currently not available directly within the ExpressionBuilder, neiter from doctrine dbal nor the TYPO3 decoracted one; keep plain sql here, but add a `@todo` comment that this should be enriched in `ExpressionBuilder` and then replaced here (putting on my list of additional expression builder work anyway).

    ContentObjectRendererTest.php:4575

    ```suggestion 'first occurrence of duplicate uid defines order' => [ ['uidInList' => '20,30,20,10', 'max' => '3'], [20, 30, 10], ], ]; ``` We should also add a test case for duplicate uid usage in the list, which is covered by the array_unique() call in one place and that the outcome is as expected (albeit the php code would not be needed).

    ContentObjectRendererTest.php:4580

    I think we should name the test method (and thus the dataprovider) based on the behaviour it checks, for example `getRecordsRespectsUidInListOrder()`. Further, we should add more tests to increase the coverage, for example the `move pointer` branch and the aggregate/DSTICT/groupBy guard are completly untested.
  11. Stefan Bürk uploaded patch set 3 CR -1 V +1 09/25/26, 18:51

  12. Stefan Bürk commented on patch set 2 09/25/26, 18:59

    Reply

    Done

    ContentObjectRenderer.php:4714 Reply

    Done

    ContentObjectRendererTest.php:4575 Reply

    Done

    ContentObjectRendererTest.php:4580 Reply

    Done
  13. core-ci voted on patch set 3 V +1 09/25/26, 18:59 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109296

  14. Stefan Bürk commented on patch set 3 09/25/26, 19:00

    ContentObjectRendererTest.php:4580 Reply

    Done
  15. Stefan Bürk voted on patch set 3 CR +1 V +1 09/25/26, 19:00

  16. core-ci commented on patch set 3 09/25/26, 19:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109297

  17. Benni Mack voted on patch set 3 CR +1 V +1 09/25/26, 19:30

  18. Stefan Bürk voted on patch set 3 CR +2 V +2 09/25/26, 20:02

  19. Oliver Klee voted on patch set 3 V +1 09/25/26, 20:07

  20. Stefan Bürk merged patch set 4 09/25/26, 20:29