Discrepancy between the counts of query results in 6 and 7

[grantfitzsimmons]

Describe the bug

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.

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.)

Reported by: Leo at the Queensland Herbarium on the Speciforum

To Reproduce
Steps to reproduce the behavior:

1. Use the queensland_2025_03_17 database as leo in both Specify 6 and 7
2. Go to the 20250117fullextract query in both
3. Run the query
4. See the results discrepancy between both

Expected behavior
The count should be the same in both versions

SQL Queries

Specify 6

Returns a count of 448,671 total.

SELECT 
    collection0_.CollectionObjectID AS col_0_0_,
    collection0_.CatalogNumber AS col_1_0_,
    taxon2_.FullName AS col_2_0_,
    taxon2_.TaxonID AS col_3_0_,
    taxon2_.TaxonID AS col_4_0_,
    taxon2_.TaxonID AS col_5_0_,
    taxon2_.Text1 AS col_6_0_,
    taxon2_.Text3 AS col_7_0_,
    taxon2_.Text2 AS col_8_0_,
    agent3_.AgentID AS col_9_0_,
    determinat1_.IsCurrent AS col_10_0_,
    collecting4_.StartDate AS col_11_0_,
    collecting4_.StartDatePrecision AS col_12_0_,
    CASE 
        WHEN collecting4_.StartDatePrecision IN (1, 2, 3) 
        THEN YEAR(collecting4_.StartDate) 
        ELSE NULL 
    END AS col_13_0_,
    collecting4_.CollectingEventID AS col_14_0_,
    locality5_.LocalityName AS col_15_0_,
    locality5_.Longitude1 AS col_16_0_,
    locality5_.Latitude1 AS col_17_0_,
    locality5_.OriginalElevationUnit AS col_18_0_,
    collecting4_.Remarks AS col_19_0_,
    geography6_.GeographyID AS col_20_0_,
    collection0_.OCR AS col_21_0_,
    taxon2_.Integer1 AS col_22_0_,
    collecting7_.Text13 AS col_23_0_,
    collecting7_.Text11 AS col_24_0_
FROM 
    collectionobject collection0_
LEFT OUTER JOIN 
    determination determinat1_ ON collection0_.CollectionObjectID = determinat1_.CollectionObjectID
LEFT OUTER JOIN 
    taxon taxon2_ ON determinat1_.TaxonID = taxon2_.TaxonID
LEFT OUTER JOIN 
    agent agent3_ ON determinat1_.DeterminerID = agent3_.AgentID
LEFT OUTER JOIN 
    collectingevent collecting4_ ON collection0_.CollectingEventID = collecting4_.CollectingEventID
LEFT OUTER JOIN 
    locality locality5_ ON collecting4_.LocalityID = locality5_.LocalityID
LEFT OUTER JOIN 
    geography geography6_ ON locality5_.GeographyID = geography6_.GeographyID
LEFT OUTER JOIN 
    collectingeventattribute collecting7_ ON collecting4_.CollectingEventAttributeID = collecting7_.CollectingEventAttributeID
WHERE 
    determinat1_.IsCurrent = 1 
    AND YEAR(collecting4_.StartDate) >= 1950 
    AND (CASE 
            WHEN collecting4_.StartDatePrecision IN (1, 2, 3) 
            THEN YEAR(collecting4_.StartDate) 
            ELSE NULL 
         END IS NOT NULL)
    AND (locality5_.OriginalElevationUnit IN ('1', '2', '3')) 
    AND (geography6_.NodeNumber BETWEEN 203 AND 218) 
    AND collection0_.CollectionMemberID = 4 
LIMIT 50000;

Specify 7

Returns a count of 30,085 total.

SELECT 
    collectionobject.`CollectionObjectID` AS `collectionobject_CollectionObjectID`,
    collectionobject.`CatalogNumber` AS `collectionobject_CatalogNumber`,
    taxon_1.`FullName` AS `taxon_1_FullName`,
    CASE 
        WHEN (taxon_1.`TaxonTreeDefID` = 1 AND taxon_1.`TaxonTreeDefItemID` = 2) THEN taxon_1.`FullName`
        WHEN (taxon_2.`TaxonTreeDefID` = 1 AND taxon_2.`TaxonTreeDefItemID` = 2) THEN taxon_2.`FullName`
        WHEN (taxon_3.`TaxonTreeDefID` = 1 AND taxon_3.`TaxonTreeDefItemID` = 2) THEN taxon_3.`FullName`
        WHEN (taxon_4.`TaxonTreeDefID` = 1 AND taxon_4.`TaxonTreeDefItemID` = 2) THEN taxon_4.`FullName`
        WHEN (taxon_5.`TaxonTreeDefID` = 1 AND taxon_5.`TaxonTreeDefItemID` = 2) THEN taxon_5.`FullName`
        WHEN (taxon_6.`TaxonTreeDefID` = 1 AND taxon_6.`TaxonTreeDefItemID` = 2) THEN taxon_6.`FullName`
        WHEN (taxon_7.`TaxonTreeDefID` = 1 AND taxon_7.`TaxonTreeDefItemID` = 2) THEN taxon_7.`FullName`
        WHEN (taxon_8.`TaxonTreeDefID` = 1 AND taxon_8.`TaxonTreeDefItemID` = 2) THEN taxon_8.`FullName`
        WHEN (taxon_9.`TaxonTreeDefID` = 1 AND taxon_9.`TaxonTreeDefItemID` = 2) THEN taxon_9.`FullName`
        WHEN (taxon_10.`TaxonTreeDefID` = 1 AND taxon_10.`TaxonTreeDefItemID` = 2) THEN taxon_10.`FullName`
        WHEN (taxon_11.`TaxonTreeDefID` = 1 AND taxon_11.`TaxonTreeDefItemID` = 2) THEN taxon_11.`FullName`
        WHEN (taxon_12.`TaxonTreeDefID` = 1 AND taxon_12.`TaxonTreeDefItemID` = 2) THEN taxon_12.`FullName`
        WHEN (taxon_13.`TaxonTreeDefID` = 1 AND taxon_13.`TaxonTreeDefItemID` = 2) THEN taxon_13.`FullName`
        WHEN (taxon_14.`TaxonTreeDefID` = 1 AND taxon_14.`TaxonTreeDefItemID` = 2) THEN taxon_14.`FullName`
        WHEN (taxon_15.`TaxonTreeDefID` = 1 AND taxon_15.`TaxonTreeDefItemID` = 2) THEN taxon_15.`FullName`
        WHEN (taxon_16.`TaxonTreeDefID` = 1 AND taxon_16.`TaxonTreeDefItemID` = 2) THEN taxon_16.`FullName`
        WHEN (taxon_17.`TaxonTreeDefID` = 1 AND taxon_17.`TaxonTreeDefItemID` = 2) THEN taxon_17.`FullName`
        WHEN (taxon_18.`TaxonTreeDefID` = 1 AND taxon_18.`TaxonTreeDefItemID` = 2) THEN taxon_18.`FullName`
        WHEN (taxon_19.`TaxonTreeDefID` = 1 AND taxon_19.`TaxonTreeDefItemID` = 2) THEN taxon_19.`FullName`
        WHEN (taxon_20.`TaxonTreeDefID` = 1 AND taxon_20.`TaxonTreeDefItemID` = 2) THEN taxon_20.`FullName`
    END AS anon_1,
    CASE 
        WHEN (taxon_1.`TaxonTreeDefID` = 1 AND taxon_1.`TaxonTreeDefItemID` = 6) THEN taxon_1.`FullName`
        WHEN (taxon_2.`TaxonTreeDefID` = 1 AND taxon_2.`TaxonTreeDefItemID` = 6) THEN taxon_2.`FullName`
        WHEN (taxon_3.`TaxonTreeDefID` = 1 AND taxon_3.`TaxonTreeDefItemID` = 6) THEN taxon_3.`FullName`
        WHEN (taxon_4.`TaxonTreeDefID` = 1 AND taxon_4.`TaxonTreeDefItemID` = 6) THEN taxon_4.`FullName`
        WHEN (taxon_5.`TaxonTreeDefID` = 1 AND taxon_5.`TaxonTreeDefItemID` = 6) THEN taxon_5.`FullName`
        WHEN (taxon_6.`TaxonTreeDefID` = 1 AND taxon_6.`TaxonTreeDefItemID` = 6) THEN taxon_6.`FullName`
        WHEN (taxon_7.`TaxonTreeDefID` = 1 AND taxon_7.`TaxonTreeDefItemID` = 6) THEN taxon_7.`FullName`
        WHEN (taxon_8.`TaxonTreeDefID` = 1 AND taxon_8.`TaxonTreeDefItemID` = 6) THEN taxon_8.`FullName`
        WHEN (taxon_9.`TaxonTreeDefID` = 1 AND taxon_9.`TaxonTreeDefItemID` = 6) THEN taxon_9.`FullName`
        WHEN (taxon_10.`TaxonTreeDefID` = 1 AND taxon_10.`TaxonTreeDefItemID` = 6) THEN taxon_10.`FullName`
        WHEN (taxon_11.`TaxonTreeDefID` = 1 AND taxon_11.`TaxonTreeDefItemID` = 6) THEN taxon_11.`FullName`
        WHEN (taxon_12.`TaxonTreeDefID` = 1 AND taxon_12.`TaxonTreeDefItemID` = 6) THEN taxon_12.`FullName`
        WHEN (taxon_13.`TaxonTreeDefID` = 1 AND taxon_13.`TaxonTreeDefItemID` = 6) THEN taxon_13.`FullName`
        WHEN (taxon_14.`TaxonTreeDefID` = 1 AND taxon_14.`TaxonTreeDefItemID` = 6) THEN taxon_14.`FullName`
        WHEN (taxon_15.`TaxonTreeDefID` = 1 AND taxon_15.`TaxonTreeDefItemID` = 6) THEN taxon_15.`FullName`
        WHEN (taxon_16.`TaxonTreeDefID` = 1 AND taxon_16.`TaxonTreeDefItemID` = 6) THEN taxon_16.`FullName`
        WHEN (taxon_17.`TaxonTreeDefID` = 1 AND taxon_17.`TaxonTreeDefItemID` = 6) THEN taxon_17.`FullName`
        WHEN (taxon_18.`TaxonTreeDefID` = 1 AND taxon_18.`TaxonTreeDefItemID` = 6) THEN taxon_18.`FullName`
        WHEN (taxon_19.`TaxonTreeDefID` = 1 AND taxon_19.`TaxonTreeDefItemID` = 6) THEN taxon_19.`FullName`
        WHEN (taxon_20.`TaxonTreeDefID` = 1 AND taxon_20.`TaxonTreeDefItemID` = 6) THEN taxon_20.`FullName`
    END AS anon_2,
    CASE 
        WHEN (taxon_1.`TaxonTreeDefID` = 1 AND taxon_1.`TaxonTreeDefItemID` = 11) THEN taxon_1.`FullName`
        WHEN (taxon_2.`TaxonTreeDefID` = 1 AND taxon_2.`TaxonTreeDefItemID` = 11) THEN taxon_2.`FullName`
        WHEN (taxon_3.`TaxonTreeDefID` = 1 AND taxon_3.`TaxonTreeDefItemID` = 11) THEN taxon_3.`FullName`
        WHEN (taxon_4.`TaxonTreeDefID` = 1 AND taxon_4.`TaxonTreeDefItemID` = 11) THEN taxon_4.`FullName`
        WHEN (taxon_5.`TaxonTreeDefID` = 1 AND taxon_5.`TaxonTreeDefItemID` = 11) THEN taxon_5.`FullName`
        WHEN (taxon_6.`TaxonTreeDefID` = 1 AND taxon_6.`TaxonTreeDefItemID` = 11) THEN taxon_6.`FullName`
        WHEN (taxon_7.`TaxonTreeDefID` = 1 AND taxon_7.`TaxonTreeDefItemID` = 11) THEN taxon_7.`FullName`
        WHEN (taxon_8.`TaxonTreeDefID` = 1 AND taxon_8.`TaxonTreeDefItemID` = 11) THEN taxon_8.`FullName`
        WHEN (taxon_9.`TaxonTreeDefID` = 1 AND taxon_9.`TaxonTreeDefItemID` = 11) THEN taxon_9.`FullName`
        WHEN (taxon_10.`TaxonTreeDefID` = 1 AND taxon_10.`TaxonTreeDefItemID` = 11) THEN taxon_10.`FullName`
        WHEN (taxon_11.`TaxonTreeDefID` = 1 AND taxon_11.`TaxonTreeDefItemID` = 11) THEN taxon_11.`FullName`
        WHEN (taxon_12.`TaxonTreeDefID` = 1 AND taxon_12.`TaxonTreeDefItemID` = 11) THEN taxon_12.`FullName`
        WHEN (taxon_13.`TaxonTreeDefID` = 1 AND taxon_13.`TaxonTreeDefItemID` = 11) THEN taxon_13.`FullName`
        WHEN (taxon_14.`TaxonTreeDefID` = 1 AND taxon_14.`TaxonTreeDefItemID` = 11) THEN taxon_14.`FullName`
        WHEN (taxon_15.`TaxonTreeDefID` = 1 AND taxon_15.`TaxonTreeDefItemID` = 11) THEN taxon_15.`FullName`
        WHEN (taxon_16.`TaxonTreeDefID` = 1 AND taxon_16.`TaxonTreeDefItemID` = 11) THEN taxon_16.`FullName`
        WHEN (taxon_17.`TaxonTreeDefID` = 1 AND taxon_17.`TaxonTreeDefItemID` = 11) THEN taxon_17.`FullName`
        WHEN (taxon_18.`TaxonTreeDefID` = 1 AND taxon_18.`TaxonTreeDefItemID` = 11) THEN taxon_18.`FullName`
        WHEN (taxon_19.`TaxonTreeDefID` = 1 AND taxon_19.`TaxonTreeDefItemID` = 11) THEN taxon_19.`FullName`
        WHEN (taxon_20.`TaxonTreeDefID` = 1 AND taxon_20.`TaxonTreeDefItemID` = 11) THEN taxon_20.`FullName`
    END AS anon_3,
    taxon_1.`Text1` AS `taxon_1_Text1`,
    taxon_1.`Text3` AS `taxon_1_Text3`,
    taxon_1.`Text2` AS `taxon_1_Text2`,
    IFNULL(
        CASE 
            IFNULL(agent_1.`AgentType`, '') 
            WHEN '0' THEN IFNULL(agent_1.`LastName`, '') 
            WHEN '1' THEN CONCAT(IFNULL(agent_1.`LastName`, ''), IFNULL(CONCAT(', ', agent_1.`FirstName`), '')) 
            WHEN '2' THEN IFNULL(agent_1.`LastName`, '') 
            WHEN '3' THEN IFNULL(agent_1.`LastName`, '') 
        END, 
        ''
    ) AS blank_nulls_1,
    determination_1.`IsCurrent` != 0 AS anon_4,
    DATE_FORMAT(
        collectingevent_1.`StartDate`, 
        CASE collectingevent_1.`StartDatePrecision` 
            WHEN 2 THEN '%m/%Y' 
            WHEN 3 THEN '%Y' 
            ELSE '%d/%m/%Y' 
        END
    ) AS date_format_1,
    EXTRACT(YEAR FROM collectingevent_1.`StartDate`) AS anon_5,
    '<Aggregator not defined.>' AS param_10,
    locality_1.`LocalityName` AS `locality_1_LocalityName`,
    locality_1.`Longitude1` AS `locality_1_Longitude1`,
    locality_1.`Latitude1` AS `locality_1_Latitude1`,
    locality_1.`OriginalElevationUnit` AS `locality_1_OriginalElevationUnit`,
    collectingevent_1.`Remarks` AS `collectingevent_1_Remarks`,
    CASE 
        WHEN (geography_1.`GeographyTreeDefID` = 1 AND geography_1.`GeographyTreeDefItemID` = 4) THEN geography_1.`FullName`
        WHEN (geography_2.`GeographyTreeDefID` = 1 AND geography_2.`GeographyTreeDefItemID` = 4) THEN geography_2.`FullName`
        WHEN (geography_3.`GeographyTreeDefID` = 1 AND geography_3.`GeographyTreeDefItemID` = 4) THEN geography_3.`FullName`
        WHEN (geography_4.`GeographyTreeDefID` = 1 AND geography_4.`GeographyTreeDefItemID` = 4) THEN geography_4.`FullName`
        WHEN (geography_5.`GeographyTreeDefID` = 1 AND geography_5.`GeographyTreeDefItemID` = 4) THEN geography_5.`FullName`
    END AS anon_6,
    collectionobject.`OCR` AS `collectionobject_OCR`,
    taxon_1.`Integer1` AS `taxon_1_Integer1`,
    collectingeventattribute_1.`Text13` AS `collectingeventattribute_1_Text13`,
    collectingeventattribute_1.`Text11` AS `collectingeventattribute_1_Text11`,
    locality_1.`Latitude2` AS `locality_1_Latitude2`,
    locality_1.`Longitude2` AS `locality_1_Longitude2`,
    locality_1.`LatLongType` AS `locality_1_LatLongType`,
    locality_1.`LatLongAccuracy` AS `locality_1_LatLongAccuracy`,
    locality_1.`LocalityID` AS `locality_1_LocalityID`
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`
LEFT OUTER JOIN 
    taxon AS taxon_2 ON taxon_1.`ParentID` = taxon_2.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_3 ON taxon_2.`ParentID` = taxon_3.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_4 ON taxon_3.`ParentID` = taxon_4.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_5 ON taxon_4.`ParentID` = taxon_5.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_6 ON taxon_5.`ParentID` = taxon_6.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_7 ON taxon_6.`ParentID` = taxon_7.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_8 ON taxon_7.`ParentID` = taxon_8.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_9 ON taxon_8.`ParentID` = taxon_9.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_10 ON taxon_9.`ParentID` = taxon_10.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_11 ON taxon_10.`ParentID` = taxon_11.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_12 ON taxon_11.`ParentID` = taxon_12.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_13 ON taxon_12.`ParentID` = taxon_13.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_14 ON taxon_13.`ParentID` = taxon_14.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_15 ON taxon_14.`ParentID` = taxon_15.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_16 ON taxon_15.`ParentID` = taxon_16.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_17 ON taxon_16.`ParentID` = taxon_17.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_18 ON taxon_17.`ParentID` = taxon_18.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_19 ON taxon_18.`ParentID` = taxon_19.`TaxonID`
LEFT OUTER JOIN 
    taxon AS taxon_20 ON taxon_19.`ParentID` = taxon_20.`TaxonID`
LEFT OUTER JOIN 
    agent AS agent_1 ON agent_1.`AgentID` = determination_1.`DeterminerID`
LEFT OUTER JOIN 
    collectingevent AS collectingevent_1 ON collectingevent_1.`CollectingEventID` = collectionobject.`CollectingEventID`
LEFT OUTER JOIN 
    locality AS locality_1 ON locality_1.`LocalityID` = collectingevent_1.`LocalityID`
LEFT OUTER JOIN 
    geography AS geography_1 ON geography_1.`GeographyID` = locality_1.`GeographyID`
LEFT OUTER JOIN 
    geography AS geography_2 ON geography_1.`ParentID` = geography_2.`GeographyID`
LEFT OUTER JOIN 
    geography AS geography_3 ON geography_2.`ParentID` = geography_3.`GeographyID`
LEFT OUTER JOIN 
    geography AS geography_4 ON geography_3.`ParentID` = geography_4.`GeographyID`
LEFT OUTER JOIN 
    geography AS geography_5 ON geography_4.`ParentID` = geography_5.`GeographyID`
LEFT OUTER JOIN 
    collectingeventattribute AS collectingeventattribute_1 ON collectingeventattribute_1.`CollectingEventAttributeID` = collectingevent_1.`CollectingEventAttributeID`
WHERE 
    collectionobject.`CollectionID` = 4 
    AND determination_1.`IsCurrent` = true 
    AND EXTRACT(YEAR FROM collectingevent_1.`StartDate`) >= '1950' 
    AND locality_1.`OriginalElevationUnit` IN ('1', '2. >50m - 100m', '3. >100m - 2km') 
    AND (
        CASE 
            WHEN (geography_1.`GeographyTreeDefID` = 1 AND geography_1.`GeographyTreeDefItemID` = 4) THEN geography_1.`FullName`
            WHEN (geography_2.`GeographyTreeDefID` = 1 AND geography_2.`GeographyTreeDefItemID` = 4) THEN geography_2.`FullName`
            WHEN (geography_3.`GeographyTreeDefID` = 1 AND geography_3.`GeographyTreeDefItemID` = 4) THEN geography_3.`FullName`
            WHEN (geography_4.`GeographyTreeDefID` = 1 AND geography_4.`GeographyTreeDefItemID` = 4) THEN geography_4.`FullName`
            WHEN (geography_5.`GeographyTreeDefID` = 1 AND geography_5.`GeographyTreeDefItemID` = 4) THEN geography_5.`FullName`
        END LIKE CONCAT(CONCAT('%', 'Queensland'), '%')
    ) 
    AND (
        taxon_1.`TaxonTreeDefID` IN (1) 
        OR taxon_1.`TaxonTreeDefID` IN (1) 
        OR taxon_1.`TaxonTreeDefID` IN (1) 
        OR geography_1.`GeographyTreeDefID` IN (1)
    )
LIMIT 0, 40;

Screenshots

Crash Report

We’re running Specify 6.8.03 and Specify v7.9.6.2.

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

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/different-results-when-running-same-query-in-specify/2301/6

[grantfitzsimmons]

For the record– from Leo on Discourse:

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

[grantfitzsimmons]

Hi @leobrimblecombe,

Thanks so much for following up!

This issue needs investigation by one of our developers, but our team has been busy with other projects. I've added another comment with the details from your response in April.

SQL Queries

As for your request to provide a direct SQL query, here they are below for the CHKQLD query:

Specify 7

SELECT
  group_concat (DISTINCT collectionobject.`CollectionObjectID`) AS group_concat_1,
  taxon_1.`GroupNumber` AS `taxon_1_GroupNumber`,
  CASE
    WHEN (taxon_1.`TaxonTreeDefItemID` = 11) THEN taxon_1.`Name`
    WHEN (taxon_2.`TaxonTreeDefItemID` = 11) THEN taxon_2.`Name`
    WHEN (taxon_3.`TaxonTreeDefItemID` = 11) THEN taxon_3.`Name`
    WHEN (taxon_4.`TaxonTreeDefItemID` = 11) THEN taxon_4.`Name`
    WHEN (taxon_5.`TaxonTreeDefItemID` = 11) THEN taxon_5.`Name`
    WHEN (taxon_6.`TaxonTreeDefItemID` = 11) THEN taxon_6.`Name`
    WHEN (taxon_7.`TaxonTreeDefItemID` = 11) THEN taxon_7.`Name`
    WHEN (taxon_8.`TaxonTreeDefItemID` = 11) THEN taxon_8.`Name`
    WHEN (taxon_9.`TaxonTreeDefItemID` = 11) THEN taxon_9.`Name`
    WHEN (taxon_10.`TaxonTreeDefItemID` = 11) THEN taxon_10.`Name`
    WHEN (taxon_11.`TaxonTreeDefItemID` = 11) THEN taxon_11.`Name`
    WHEN (taxon_12.`TaxonTreeDefItemID` = 11) THEN taxon_12.`Name`
    WHEN (taxon_13.`TaxonTreeDefItemID` = 11) THEN taxon_13.`Name`
    WHEN (taxon_14.`TaxonTreeDefItemID` = 11) THEN taxon_14.`Name`
    WHEN (taxon_15.`TaxonTreeDefItemID` = 11) THEN taxon_15.`Name`
    WHEN (taxon_16.`TaxonTreeDefItemID` = 11) THEN taxon_16.`Name`
    WHEN (taxon_17.`TaxonTreeDefItemID` = 11) THEN taxon_17.`Name`
    WHEN (taxon_18.`TaxonTreeDefItemID` = 11) THEN taxon_18.`Name`
    WHEN (taxon_19.`TaxonTreeDefItemID` = 11) THEN taxon_19.`Name`
    WHEN (taxon_20.`TaxonTreeDefItemID` = 11) THEN taxon_20.`Name`
  END AS anon_1,
  taxon_1.`Text11` AS `taxon_1_Text11`,
  taxon_1.`Text7` AS `taxon_1_Text7`,
  taxon_1.`Text20` AS `taxon_1_Text20`,
  taxon_1.`Text1` AS `taxon_1_Text1`,
  taxon_1.`Text3` AS `taxon_1_Text3`,
  taxon_1.`Text2` AS `taxon_1_Text2`,
  taxon_1.`Integer1` AS `taxon_1_Integer1`
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`
  LEFT OUTER JOIN taxon AS taxon_2 ON taxon_1.`ParentID` = taxon_2.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_3 ON taxon_2.`ParentID` = taxon_3.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_4 ON taxon_3.`ParentID` = taxon_4.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_5 ON taxon_4.`ParentID` = taxon_5.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_6 ON taxon_5.`ParentID` = taxon_6.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_7 ON taxon_6.`ParentID` = taxon_7.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_8 ON taxon_7.`ParentID` = taxon_8.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_9 ON taxon_8.`ParentID` = taxon_9.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_10 ON taxon_9.`ParentID` = taxon_10.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_11 ON taxon_10.`ParentID` = taxon_11.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_12 ON taxon_11.`ParentID` = taxon_12.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_13 ON taxon_12.`ParentID` = taxon_13.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_14 ON taxon_13.`ParentID` = taxon_14.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_15 ON taxon_14.`ParentID` = taxon_15.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_16 ON taxon_15.`ParentID` = taxon_16.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_17 ON taxon_16.`ParentID` = taxon_17.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_18 ON taxon_17.`ParentID` = taxon_18.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_19 ON taxon_18.`ParentID` = taxon_19.`TaxonID`
  LEFT OUTER JOIN taxon AS taxon_20 ON taxon_19.`ParentID` = taxon_20.`TaxonID`
  LEFT OUTER JOIN collectingevent AS collectingevent_1 ON collectingevent_1.`CollectingEventID` = collectionobject.`CollectingEventID`
  LEFT OUTER JOIN locality AS locality_1 ON locality_1.`LocalityID` = collectingevent_1.`LocalityID`
  LEFT OUTER JOIN geography AS geography_1 ON geography_1.`GeographyID` = locality_1.`GeographyID`
  LEFT OUTER JOIN geography AS geography_2 ON geography_1.`ParentID` = geography_2.`GeographyID`
  LEFT OUTER JOIN geography AS geography_3 ON geography_2.`ParentID` = geography_3.`GeographyID`
  LEFT OUTER JOIN geography AS geography_4 ON geography_3.`ParentID` = geography_4.`GeographyID`
  LEFT OUTER JOIN geography AS geography_5 ON geography_4.`ParentID` = geography_5.`GeographyID`
  LEFT OUTER JOIN collectingeventattribute AS collectingeventattribute_1 ON collectingeventattribute_1.`CollectingEventAttributeID` = collectingevent_1.`CollectingEventAttributeID`
WHERE
  collectionobject.`CollectionID` = 4
  AND determination_1.`IsCurrent` = true
  AND CASE
    WHEN (geography_1.`GeographyTreeDefItemID` = 4) THEN geography_1.`Name`
    WHEN (geography_2.`GeographyTreeDefItemID` = 4) THEN geography_2.`Name`
    WHEN (geography_3.`GeographyTreeDefItemID` = 4) THEN geography_3.`Name`
    WHEN (geography_4.`GeographyTreeDefItemID` = 4) THEN geography_4.`Name`
    WHEN (geography_5.`GeographyTreeDefItemID` = 4) THEN geography_5.`Name`
  END = 'Queensland'
  AND collectingeventattribute_1.`Text13` NOT IN (
    'Cultivated',
    'Possibly cultivated',
    'Presumably cultivated',
    'Unknown'
  )
  AND taxon_1.`RankID` > '180'
  AND (
    taxon_1.`TaxonTreeDefID` IN (1)
    OR taxon_1.`TaxonTreeDefID` IS NULL
    OR geography_1.`GeographyTreeDefID` IN (1)
    OR geography_1.`GeographyTreeDefID` IS NULL
  )
GROUP BY
  taxon_1.`GroupNumber`,
  CASE
    WHEN (taxon_1.`TaxonTreeDefItemID` = 11) THEN taxon_1.`Name`
    WHEN (taxon_2.`TaxonTreeDefItemID` = 11) THEN taxon_2.`Name`
    WHEN (taxon_3.`TaxonTreeDefItemID` = 11) THEN taxon_3.`Name`
    WHEN (taxon_4.`TaxonTreeDefItemID` = 11) THEN taxon_4.`Name`
    WHEN (taxon_5.`TaxonTreeDefItemID` = 11) THEN taxon_5.`Name`
    WHEN (taxon_6.`TaxonTreeDefItemID` = 11) THEN taxon_6.`Name`
    WHEN (taxon_7.`TaxonTreeDefItemID` = 11) THEN taxon_7.`Name`
    WHEN (taxon_8.`TaxonTreeDefItemID` = 11) THEN taxon_8.`Name`
    WHEN (taxon_9.`TaxonTreeDefItemID` = 11) THEN taxon_9.`Name`
    WHEN (taxon_10.`TaxonTreeDefItemID` = 11) THEN taxon_10.`Name`
    WHEN (taxon_11.`TaxonTreeDefItemID` = 11) THEN taxon_11.`Name`
    WHEN (taxon_12.`TaxonTreeDefItemID` = 11) THEN taxon_12.`Name`
    WHEN (taxon_13.`TaxonTreeDefItemID` = 11) THEN taxon_13.`Name`
    WHEN (taxon_14.`TaxonTreeDefItemID` = 11) THEN taxon_14.`Name`
    WHEN (taxon_15.`TaxonTreeDefItemID` = 11) THEN taxon_15.`Name`
    WHEN (taxon_16.`TaxonTreeDefItemID` = 11) THEN taxon_16.`Name`
    WHEN (taxon_17.`TaxonTreeDefItemID` = 11) THEN taxon_17.`Name`
    WHEN (taxon_18.`TaxonTreeDefItemID` = 11) THEN taxon_18.`Name`
    WHEN (taxon_19.`TaxonTreeDefItemID` = 11) THEN taxon_19.`Name`
    WHEN (taxon_20.`TaxonTreeDefItemID` = 11) THEN taxon_20.`Name`
  END,
  taxon_1.`Text11`,
  taxon_1.`Text7`,
  taxon_1.`Text20`,
  taxon_1.`Text1`,
  taxon_1.`Text3`,
  taxon_1.`Text2`,
  taxon_1.`Integer1`
ORDER BY
  taxon_1.`GroupNumber` ASC,
  CASE
    WHEN (taxon_1.`TaxonTreeDefItemID` = 11) THEN taxon_1.`Name`
    WHEN (taxon_2.`TaxonTreeDefItemID` = 11) THEN taxon_2.`Name`
    WHEN (taxon_3.`TaxonTreeDefItemID` = 11) THEN taxon_3.`Name`
    WHEN (taxon_4.`TaxonTreeDefItemID` = 11) THEN taxon_4.`Name`
    WHEN (taxon_5.`TaxonTreeDefItemID` = 11) THEN taxon_5.`Name`
    WHEN (taxon_6.`TaxonTreeDefItemID` = 11) THEN taxon_6.`Name`
    WHEN (taxon_7.`TaxonTreeDefItemID` = 11) THEN taxon_7.`Name`
    WHEN (taxon_8.`TaxonTreeDefItemID` = 11) THEN taxon_8.`Name`
    WHEN (taxon_9.`TaxonTreeDefItemID` = 11) THEN taxon_9.`Name`
    WHEN (taxon_10.`TaxonTreeDefItemID` = 11) THEN taxon_10.`Name`
    WHEN (taxon_11.`TaxonTreeDefItemID` = 11) THEN taxon_11.`Name`
    WHEN (taxon_12.`TaxonTreeDefItemID` = 11) THEN taxon_12.`Name`
    WHEN (taxon_13.`TaxonTreeDefItemID` = 11) THEN taxon_13.`Name`
    WHEN (taxon_14.`TaxonTreeDefItemID` = 11) THEN taxon_14.`Name`
    WHEN (taxon_15.`TaxonTreeDefItemID` = 11) THEN taxon_15.`Name`
    WHEN (taxon_16.`TaxonTreeDefItemID` = 11) THEN taxon_16.`Name`
    WHEN (taxon_17.`TaxonTreeDefItemID` = 11) THEN taxon_17.`Name`
    WHEN (taxon_18.`TaxonTreeDefItemID` = 11) THEN taxon_18.`Name`
    WHEN (taxon_19.`TaxonTreeDefItemID` = 11) THEN taxon_19.`Name`
    WHEN (taxon_20.`TaxonTreeDefItemID` = 11) THEN taxon_20.`Name`
  END ASC,
  taxon_1.`Text20` ASC

Specify 6

select distinct
  taxon2_.GroupNumber as col_0_0_,
  taxon2_.TaxonID as col_1_0_,
  taxon2_.Text11 as col_2_0_,
  taxon2_.Text7 as col_3_0_,
  taxon2_.Text20 as col_4_0_,
  taxon2_.Text1 as col_5_0_,
  taxon2_.Text3 as col_6_0_,
  taxon2_.Text2 as col_7_0_,
  taxon2_.Integer1 as col_8_0_
from
  collectionobject collection0_
  left outer join determination determinat1_ on collection0_.CollectionObjectID = determinat1_.CollectionObjectID
  left outer join taxon taxon2_ on determinat1_.TaxonID = taxon2_.TaxonID
  left outer join collectingevent collecting3_ on collection0_.CollectingEventID = collecting3_.CollectingEventID
  left outer join locality locality4_ on collecting3_.LocalityID = locality4_.LocalityID
  left outer join geography geography5_ on locality4_.GeographyID = geography5_.GeographyID
  left outer join collectingeventattribute collecting6_ on collecting3_.CollectingEventAttributeID = collecting6_.CollectingEventAttributeID
where
  determinat1_.IsCurrent = 1
  and (geography5_.NodeNumber between 203 and 218)
  and (
    collecting6_.Text13 not in (
      'Cultivated',
      'Possibly cultivated',
      'Presumably cultivated',
      'Unknown'
    )
    or collecting6_.Text13 is null
  )
  and taxon2_.RankID > 180
  and collection0_.CollectionMemberID = 4

Explanation

To get the SQL run on the database when a query is constructed by Specify, you can run the following command immediately following you executing the query in either version:

SHOW FULL PROCESSLIST;

From here, you should see the results of all ongoing processes in MariaDB, including the SQL query constructed by Specify in the Info column:

Id	User	Host	db	Command	Time	State	Info	Progress
68	masteruser	localhost:55220	queensland_2025_03_17	Query	0	Sending data	select distinct taxon2_.GroupNumber as col_0_0_, taxon2_.TaxonID as col_1_0_, taxon2_.Text11 as col_2_0_, taxon2_.Text7 as col_3_0_, taxon2_.Text20 as col_4_0_, taxon2_.Text1 as col_5_0_, taxon2_.Text3 as col_6_0_, taxon2_.Text2 as col_7_0_, taxon2_.Integer1 as col_8_0_ from collectionobject collection0_ left outer join determination determinat1_ on collection0_.CollectionObjectID=determinat1_.CollectionObjectID left outer join taxon taxon2_ on determinat1_.TaxonID=taxon2_.TaxonID left outer join collectingevent collecting3_ on collection0_.CollectingEventID=collecting3_.CollectingEventID left outer join locality locality4_ on collecting3_.LocalityID=locality4_.LocalityID left outer join geography geography5_ on locality4_.GeographyID=geography5_.GeographyID left outer join collectingeventattribute collecting6_ on collecting3_.CollectingEventAttributeID=collecting6_.CollectingEventAttributeID where determinat1_.IsCurrent=1 and (geography5_.NodeNumber between 203 and 218) and (collecting6_.Text13 not in ('Cultivated' , 'Possibly cultivated' , 'Presumably cultivated' , 'Unknown') or collecting6_.Text13 is null) and taxon2_.RankID>180 and collection0_.CollectionMemberID=4 limit 50000	0

From here, you can reformat the query and save it for future use!

[grantfitzsimmons]

@specify/developers Can anyone explain the discrepancy here?

[melton-jason]

Whether intentional or not, there is a difference between the Specify 6 and Specify 7 querying logic.
The difference here lies in handling empty (NULL) values when using the negated IN filter.

When using the NOT IN filter, Specify 6 will include empty (NULL) values for fields, while Specify 7 will exclude empty values for fields.

Here are some videos demonstrating the bug (using the same query in Specify 6 and 7):

not_in_behavior.mov

With the probably more common use case of using a PickList:

Screen.Recording.2025-06-12.at.1.29.29.PM.mov

I think Specify 7 should probably adopt the behavior Specify 6 in this case, or allow users to Specify an EMPTY value in their IN filter:

From a Slack message to @grantfitzsimmons:

I would assume the Specify 6 behavior would be generally more intuitive? That is, I don't think users are considering NULL values as distinctly separate.
Logically, I think they would probably expect all records not returned by IN whatever to be returned by NOT IN whatever: I doubt they would (or should!) recognize that "empty" values are separate cases.

The only real concern I would have with this approach is that NULL is a distinct value in NOT IN, and it is impossible to be included with an IN filter; IN and NOT IN are not directly inverses of each other.

But I don't really think this is too much a concern: if they want to actively do something like IN val1, val2, NULL, I think it probably is intuitive for them to use an OR filter and add IS EMPTY (the difference is that they are actively trying to search for NULL values, as opposed to NOT IN for which it might be implicit that NULL should be included)

The best approach would be to open this up to users and get their feedback on which approach they prefer.

[grantfitzsimmons]

Second, we've been observing some differences in query results between Specify 6 & 7 when using the Distinct option. With the Distinct option off, higher counts are observed in Specify 6 query results than the same query in Specify 7. The records that are found in Specify 6 results but not Specify 7 have missing information in fields that were included in the query. Any thoughts on why this behavior is occurring?

University of Michigan observed the same query count discrepancy and had the following to say:

The GitHub write-up exactly explains the observed behavior in question!

From my perspective, it makes perfect sense that the records with missing data in fields with an "IN" operator don't appear in query results. However, from a user perspective, this inconsistency is odd and the Specify 6 behavior is preferable. Please record this as a vote for the more intuitive Specify 6 behavior to be applied to Specify 7 as well.

[grantfitzsimmons]

This was discussed in today's TAC meeting as an issue impacting several institutions (including Michigan, Florida).

[bhumikaguptaa]

Received a report of this same underlying issue from another user, but this time it is affecting the negated "Contains" operator.

Vanessa Delnavaz, the Santa Barbara Museum
Link to the Ticket - https://specifysoftware.bolddesk.com/agent/tickets/92

I've looked into why the export is being limited, and it's due to a bug in our query system.

Essentially, when the query is built to exclude records with an "endangered" status, it should return everything that isn't marked as endangered. This includes records with values like "concerned" and all the records where that field is simply empty.

However, due to the bug, the query is incorrectly filtering out all of the records where the cell is empty. This is why you are only seeing the 340 records that have the specific "concerned" value, instead of the complete dataset.

We have a formal bug report written up for this, and our development team is aware of the issue. For now, you are correct that the export is limited to those 340 records. As soon as the bug is resolved, you will be able to export all of your records as intended.

Here is the link to the GitHub issue: #6446

Please let us know if there any questions.

Best,