Search Synonyms can show results that don't match filters

[melton-jason]

Describe the bug
The Search Synonyms checkbox in the QueryBuilder can result in Queries that return/show incorrect results. That is, results that don't match the filters specified in the Query.

The Query in v7.12.1.1 (1b3dec6):

incorrect_results.mov

The Query in Specify 6, which demonstrates the expected/correct results:

specify6_correct_query.mov

The Issue here is not specific to Determinations -> IsCurrent.
Any filter that is not explicitly on the tree relationship (Determinations -> taxon in this case) has the potential to be ignored.
This can also lead to Queries scoped to Record Sets to return records outside of the recordset.

Below are some even more egregious examples of the Issue:

projectnumber_example.mov

preparation_example.mov

To Reproduce
Steps to reproduce the behavior:

1. Determine a CollectionObject to some Taxon, A
2. On the same or a different CollectionObject, add a Determination to that Taxon that is not current
3. In the QueryBuilder on a CollectionObject Query, add a filter for Determinations -> Is Current -> Equal -> True and enable "Search Synonyms"
4. Run the Query
5. See that the non-current Determinations on Taxon A are returned

More general reproduction steps are:

1. Determine a CollectionObject to some Taxon, A
2. On the same or different CollectionObject, add another Determination to either Taxon A or a Taxon whose Accepted Taxon is A
3. (Optional) repeat step 2 as many times as desired
4. In the QueryBuilder, construct a CollectionObject query that will return one or more of the previously created/modified CollectionObjects

• Importantly, the filters do not have to apply to all previous CollectionObjects-- just a minimum of 1 has to match.

5. Make sure "Search Synonyms" checkbox is checked (enabled)
6. Run the Query
7. See that all of the previous CollectionObjects and/or related ToMany records are returned, even those that should not be

Expected behavior
Specify should always respect the Query's filters.

Please fill out the following information manually:

• OS: macOS Tahoe 26.3.1 (a) Apple M3 PRO
• Browser: Google Chrome 153.0.8010.53 (Official Build) (arm64)
• Specify 7 Version: v7.12.1.1 and main (2c3012d)

Additional context
Other related Search Synonym Issues: #8543, #8565.

Below is the resulting SQL Query sent to the database:

WITH target_taxon AS (
    SELECT taxon_1.`TaxonID` AS `TaxonID`,
        taxon_1.`AcceptedID` AS `AcceptedID`
    FROM collectionobject
        LEFT OUTER JOIN determination AS determination_1 ON collectionobject.`CollectionObjectID` = determination_1.`CollectionObjectID`
        LEFT OUTER JOIN taxon AS taxon_1 ON taxon_1.`TaxonID` = determination_1.`TaxonID`
    WHERE collectionobject.`CollectionID` = 4
            AND determination_1.`IsCurrent` = true
)
SELECT collectionobject.`CollectionObjectID`,
    CASE
        WHEN (
            collectionobject.`CatalogNumber` REGEXP '^-?[0-9]+(\\.[0-9]+)?$'
        ) THEN CAST(collectionobject.`CatalogNumber` AS DECIMAL(65))
        ELSE collectionobject.`CatalogNumber`
    END AS anon_1,
    determination_1.`IsCurrent` != 0 AS anon_2,
    IFNULL(taxon_1.`FullName`, '') AS blank_nulls_1
FROM collectionobject
    LEFT OUTER JOIN determination AS determination_1 ON collectionobject.`CollectionObjectID` = determination_1.`CollectionObjectID`
    LEFT OUTER JOIN taxon AS taxon_1 ON taxon_1.`TaxonID` = determination_1.`TaxonID`
WHERE taxon_1.`TaxonID` IN (
        SELECT ids.id
        FROM (
                SELECT accepted_roots.id AS id
                FROM (
                        SELECT coalesce(
                                target_taxon.`AcceptedID`,
                                target_taxon.`TaxonID`
                            ) AS id
                        FROM target_taxon
                    ) AS accepted_roots
                UNION
                SELECT taxon.`TaxonID` AS id
                FROM taxon
                WHERE taxon.`AcceptedID` IN (
                        SELECT accepted_roots.id
                        FROM (
                                SELECT coalesce(
                                        target_taxon.`AcceptedID`,
                                        target_taxon.`TaxonID`
                                    ) AS id
                                FROM target_taxon
                            ) AS accepted_roots
                    )
            ) AS ids
    );

The Issue here is that the filters of the Query (e.g., the filtering of IsCurrent Determinations) are only included within the target_taxon CTE.
Thus when joined with the "main" CollectionObject query, we are effectively only applying the Query's filter(s) to the tree relationship.
In more common terms, the Query is the equivalent of:
"Give me CollectionObjects, their Determinations, and their Determination's Taxon where their Determination's Taxon is in a list of TaxonIDs where the list is all CollectionObject -> determinations -> taxon -> ID where determinations -> isCurrent and CollectionObject -> collection -> ID = 4".

Another way of explaining what is happening: if there is a CollectionObject -> determinations -> taxon -> ID that meets the criteria of the query filters, the database will check other CollectionObjects and consider them "pass" the filter if their determinations -> taxon -> ID is the same.
It does not re-check the determinations -> isCurrent = true, collection -> ID = 4, etc. on the CollectionObject.