What happened?

Description

Follow-up to #15458 and also #14347 and #14348.

We had a persistent issue for a while where queries with relation parameters where returning more results than expected. Just found out that it's due to some unexpected behaviour when entries are propagated, and relation queries not taking the site into account.

Steps to reproduce

1. Create two sites, Site A and Site B.
2. Create a section Target (with a new empty type) that's active in both sites and set the propagation method to Only save entries to the site they were created in.
3. Create an Entries field Relation that allows selecting entries from the Target section with the following settings:
   1. Relate entries from a specific site? No
   2. Show the site menu: No
   3. Manage relations on a per-site basis: Yes
4. Create another section Source, with an entry type that has the Relation field and set the propagation method to Let each entry choose which sites it should be saved to.
5. Create an entry in the Target section in Site A.
6. Create an entry in the Source section in Site A and set the Relation to the entry created in the previous step.
7. Propagate the entry to Site B.
8. Go back to Site A and remove the relation to the newly created entry.

Now the propagated entry in Site B has a relation to an entry that only exists in Site A, despite the field being configured to only allow selecting entries from the same site as per the above options. This messes with element queries. If I have query for relations of the entry in the Target section in Site A, it will return the entry created in step 6, even though the entry in Site A is not related to that entry any more. This can even be seen in the entry index: Add a condition on Relation field with is related to and then select the target entry. This will show one entry, even though the entry is not related to that entry in this site any more.

Expected behavior

I think two things are going wrong here:

1. Propagation should not result in invalid states. In step 7, the relation to the entry in Site A is propagated as-is, even if the field settings don't allow the author to select that entry. During propagation, the relation should be changed to the target entry in the target site of the propagation. If the related entry does not exist in that site (as is the case in the example above), it should be removed from the Entries field for the propagated entry.
2. I don't think this is the only way to arrive at this invalid state. To prevent issues like this (this isn't the first time we ran into this problem, see the issues linked above), relationship queries should always take the target site of the query into account.

Actual behavior

• The propagation resulted in an invalid state that an author would not be able to create manually. The author has to notice this and fix it manually to prevent errors that are extremely hard to debug.
• Element queries should always take target/source site into account, to prevent this type of issue.

Craft CMS version

5.2.8

PHP version

8.3

Operating system and version

No response

Database type and version

MySQL 8

Image driver and version

No response

Installed plugins and versions