Preferred Taxon/Taxon difference with "Full Name" = 'Any' filtering results incorrectly

[grantfitzsimmons]

Describe the bug
In this simple query, when the line Determinations --> Taxon --> Genus --> Full Name --> Any is present alongside the line Determinations --> Preferred Taxon --> Species --> Full Name --> Any, the search that should return a CO does not. It seems that this can be any higher rank in the taxon tree, and all result in no result being shown.

It seems that the mismatch between "Preferred Taxon" and "Taxon" may be causing an issue here.

To Reproduce
Steps to reproduce the behavior:

1. Go to nbm_mnb_2024_11_15 on the test panel
2. Go to https://nbm-mnb.specifycloud.org/specify/query/46/ (Simple query (Test 2))
3. Run the query and see that no results are returned
4. Remove the line Determinations --> Taxon --> Genus --> Full Name --> Any (or switch it to Determinations --> Preferred Taxon --> Genus --> Full Name --> Any)
5. Run the query and see that 1 result is now returned

Expected behavior
Since Determinations --> Taxon --> Genus --> Full Name --> Any does not add any additional filters, the single result should be returned in any case.

Screenshots
https://github.com/user-attachments/assets/9428dc33-4a90-430a-8577-7a772985396c

Crash Report

If the bug resulted in an error message, please click on "Download Error Message" and attach it here. If there is no error, please follow these instructions to download your System Information.

Please fill out the following information manually:

• OS: macOS
• Browser: Arc
• Specify 7 Version: v7.9.6.2
• Database Name: nbm_mnb on CA cloud
• Collection name: Fish Voucher Collection
• User Name: spadmin
• URL: https://nbm-mnb.specifycloud.org/specify/query/46/

Reported By
Amanda at NBM

[realVinayak]

Actually, this has the same cause as #4973. I debugged this issue, figured out the cause, came up with a solution, but was not satisfied with it.

Here's the issue:

specify7/specifyweb/stored_queries/query_construct.py

Line 43 in b69dac3

ancestors, treedefs = query.join_cache[(table, 'TreeRanks')]

It should, theoretically, be aware of the field too (since trees starting from determination->taxon will be different than determination->preferredtaxon). It's quite subtle bug to be honest. I can see why Ben added that cache mechanism. This bug will also only occur if preferredtaxon != taxon (because otherwise higher ranks are guaranteed to be different). Well, technically, they can be some intersection, but that's rare.

I'm fairly sure this bug won't happen if the preferredtaxon, and taxon, are siblings, and you are querying on a higher rank.

Another fun fact, if you remove all filters, this bug should also not occur.