Inefficient Tree JOINS causing Too Many Tables Error in Queries

[bronwyncombs]

Describe the bug
A clear and concise description of what the bug is.

Users are running into the following issue much more in v7.12.1+: #3369
For Auburn and PRI, the queries are for reports/labels and had worked previously. All the queries contain complex mappings.

KU Ento: (entomology.ku)
query includes host taxon relationship

Brzoska Tiger beetles.json | Specify 7 Crash Report - 2026-09-14T16_33_24.434Z.txt

dev comment:

There's 63 JOINs: two over the 61 limit.

44 of those JOINs are for Taxon alone.

Their Taxon has 22 ranks, so when they include a Taxon node/relationship in their query, Specify includes 22 JOINs: one for each rank of the tree.
The problem with this query for Ento is that they have two relationships to Taxon mapped:

CO -> determinations -> taxon
CO -> collectingEvent -> collectingEventAttribute -> hostTaxon

For each of the mapped relationships, Specify will add the 22 JOINs (one for each rank of their tree).
So there's 22 JOINs for determination and 22 for host taxon 😿

---

Auburn Inverts: (aumnh-inverts)

query includes Taxon>FullName and PreferredTaxon>FullName fields for each rank in tree

Label Generator.json | Specify 7 Crash Report - 2026-09-14T16_55_45.453Z.txt

user comment:

When I take out all the 'Taxon' fields, it still won't work. When I take out the 'Preferred Taxon' fields, it works. I like to keep the 'Preferred Taxon' information on labels because it helps people who use the collection find what they're looking for.

---

PRI: (pricollections)

query includes 7 chrono and litho strat fields with four joins each; also a few taxon>fullName and preferredTaxon>fullName fields

Label Query1.json | Specify 7 Crash Report - 2026-09-14T16_54_46.303Z.txt

Label Query1 in v7.12.0.7:

Label Query1 in v7.12.1.1:

Please fill out the following information manually:

• OS: macOS
• Browser: chrome
• Specify 7 Version: v7.12.1.1

Reported By
KU Entomology 1325
Auburn Inverts 809
PRI Collections 1321

Related

PR: #8510
Issue: #3369

[bronwyncombs]

@rijulpoudel is reviewing this issue to confirm whether there is a regression or if there was just a UX coincidence

[rijulpoudel]

I confirmed that this issue is a regression and reproduced it with all three reported databases and their provided queries.

All three databases passed for v7.12.0.7 and failed for v7.12.1. I isolated the regression to commit Fix stored query tree rank join caching · specify/specify7@03bf3df · GitHub, “Fix stored query tree rank join caching,” which entered main through PR #8009.

The commit is absent from v7.12.0.7 and present in v7.12.1. For verification, I tested a branch based on v7.12.1 with only that commit excluded, and all three queries passed.

So, this confirms the regression rather than coincidence.

[melton-jason]

Thank you for the investigation @rijulpoudel!

You are right regarding which commit introduced the change.
I'll provide a little more context here:

It looks like the changes were supposed to come from a PR which fixes #7436 (Specifically the Host Taxon data being incorrect is relevant here. See #8560): #7509.
In #7509, we essentially included a fix for #8560 by expanding out the tree hierarchy for every relationship to a tree (functionally disabling the tree rank cache).
However, for queries which had multiple relationships to the same tree mapped this greatly limited the number of available JOINs (i.e., number of relationships) within Queries. This is because for each relationship to a tree, Specify would include a number of JOINs to the query equal to the number of ranks in the Tree (as opposed to the prior behavior of only adding a single JOIN for relationships to mapped to the same Tree after the first. e.g., Specify adds the Tree Ranks JOINs to the Query once, regardless of how many relationships are mapped to the same tree).

The changes were correctly reverted in #7981. See https://github.com/specify/specify7/pull/7981/changes#diff-35176df91a4a4d798439b8424997711474013872bbd2d5efef601914bc423511R41

However, when the changes were merged into main, there was an additional commit, 03bf3df, which effectively reverted the changes of #7981.