Different results when running same query in Specify

[leobrimblecombe]

Hello,
One of the scientists that is using Specify has noticed that the same query has completely differing results when run in Specify 6 vs 7.
In Specify 6 the query returns ~447k results, whilst in Specify 7 it returns on ~29k results.

Here is an example of the query in Specify 6:

VS Specify 7:

Is there an obvious change in functionality between Specify 6 & 7 that would be causing such a difference? The query is the exact same (ran from the saved queries on the same db under the same account.)

We’re running Specify 6.8.03 and Specify v7.9.6.2.
If there’s any other information that might be helpful let me know and I’ll happily supply it - i’m hoping that I’ve missed something simple in the way that the results grouping differs between 6 and 7.

Specify 7 System Information - 2025-01-28T05_35_33.520Z.txt (1.0 MB)

[Grant]

Thank you for your message and for the detailed description!

One of the scientists that is using Specify has noticed that the same query has completely differing results when run in Specify 6 vs 7.
In Specify 6 the query returns ~447k results, whilst in Specify 7 it returns on ~29k results.

There are a couple of likely reasons you are seeing a discrepancy in the query results:

1. Your Specify 6 instance is configured to search across collections, which does not change query behavior in Specify 7.
2. This query has filters in Specify 7 that are not visible or usable in Specify 6.

I believe the most likely reason is point 1, but to verify, could you check if the following lines are included in your Global Preferences app resource?

GLOBAL_SEARCH=true
GLOBAL_SEARCH_AVAIL=true

Allow Queries to Search All Collections in a Database

By default, queries only search the collection the user is logged into at the time. Databases can be configured to query across all of the collections in the database by modifying preferences. Note that this only changes the behavior in Specify 6. Specify 7: Go to the App Resources viewer and add the following two lines: GLOBAL_SEARCH=true GLOBAL_SEARCH_AVAIL=true Save and log out and back in. [Global Prefs] Specify 6: In Specify, click Help->About. [image] In the About window, do…

[leobrimblecombe]

Hi Grant,

Neither GLOBAL_SEARCH or GLOBAL_SEARCH_AVAIL are in the Global App Resources:

Adding them also does not seem to change the numbers in the query either - the count in Specify 7 with the two global preferences added remains at 29K records:

(I made sure to clear the cache after changing the global preferences)

The query was originally written in Specify 6 and wasn’t modified at all in Specify 7 before use (just ran directly from the saved query) - are there any filters that might be added in Specify 7 that would cause the discrepancy?

Cheers,
Leo

[Grant]

Hi @leobrimblecombe,

For us to investigate this discrepancy further, we would likely need to see a copy of your Specify database. Is it possible for you to share this with us?

[leobrimblecombe]

Hi Grant,
Sorry to bring up an old ticket after not following up, but I’ve found another query that has the same issue as this and was wondering if it could be looked at again.

I sent a copy of the Specify database when we were dealing with the Collection Object not saving back in March - would it be possible to try and see if there’s it’s replicable in there. We have since updated the aggregators for a few fields but the issue has not changed.

Unable to save Collection Object records in Specify 6

Hi @leobrimblecombe, Thanks so much for sharing a backup! I was able to recreate the behavior you described. It looks like the issue has to do with Specify being unable to construct the Collection Object field format. In the database currently, there is no table format defined, and this seems to be causing Specify 6 to block creating new objects. The interesting thing about this is that your DataObjFormatters app resource (which captures this format) has not been updated since April 8, 2019, s…

Cheers,
Leo

[Grant]

Hi @leobrimblecombe,

I recreated the discrepancy using the backup between Specify 6 and 7, and I created a GitHub issue to track it:

Discrepancy between the counts of query results in 6 and 7

The construction of the database query differs between versions. Upon closer examination, I found that Specify 6 uses significantly fewer joins (7 total) compared to Specify 7 (20 total). The query in Specify 6 is much simpler and focuses on direct relationships rather than filtering on higher taxonomy for all ranks included in the query.

I’ll need a developer to take a look at this sometime soon. Can you share some information about the other query you encountered this issue with? You can also export the query so we can take a look!

[leobrimblecombe]

Hi Grant,
Thanks for looking into it!

The other query that it came up on was our check Queensland query - exported from both Specify 6 & 7:
CHKQLD Specify 6.xml (4.5 KB)
CHKQLD1 S7.json (11.2 KB)

It’s a pretty similar structure to the other query with differing results.
I’ve actually been trying to rewrite it directly as a database query in SQL - would you be able to send me the what the Specify 6 and 7 database queries look like, like you did in the Github issue for the other query?

I really appreciate your help!
Cheers,
Leo