Documents with a duplicate `prettyUrl` inside a nested Site resolve non-deterministically to the wrong Document

[AnnaR-Test]

Affected Version

2025.4 - LTS

Affected capability

Documents

Steps to reproduce

1. Create a Pimcore Site A with root document /section-a and a real domain (e.g. section-a.example.com).
2. Inside that tree, create a second Pimcore Site B whose root document is nested under Site A, e.g. /section-a/sub-site, with its own domain (e.g. sub-site.example.com).
3. Create two published Document\Page documents, one in each site, both using the same prettyUrl, e.g. /impressum:
   • /section-a/footer/sockel/impressum (belongs to Site A) — prettyUrl = /impressum
   • /section-a/sub-site/footer/sockel/impressum (belongs to Site B) — prettyUrl = /impressum
4. Visit Site A's real domain at the pretty URL, e.g. https://section-a.example.com/impressum.

The bug is masked or exposed purely by the alphabetical ordering of the documents.path column, because the underlying query (see Root Cause below) has no ORDER BY and MySQL's index range scan returns rows in ascending path order. Whichever candidate document's full path sorts first wins — regardless of which one actually matches the requesting Site/domain.

For example:

• Sub-site folder a-sub-site (path segment starts with a) sorts before footer (starts with f) → the nested Site B document wins the alphabetical race → bug reproduces.
• Renaming that same sub-site folder to something starting with a letter later than f (e.g. sub-site, starts with s) flips the outcome → Site A's own document wins the race instead → bug does not reproduce, even though the code path and structure are otherwise identical.

Actual Behavior

Depending on the alphabetical ordering of the path segments below the site roots, the document belonging to the nested Site B is served instead — even though the request domain unambiguously matches Site A.

Expected Behavior

The document belonging to Site A (/section-a/footer/sockel/impressum) should be resolved and rendered, since the request was made against Site A's domain.

[AnnaR-Test]

I just discovered, that it is not affected by the Alphabet. But the Order how the Querry shows the result.
SELECT documents.id FROM documents LEFT JOIN documents_page ON documents.id = documents_page.id WHERE documents.path LIKE '%/section-a%' AND documents_page.prettyUrl = '/impressum';