**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](https://discourse.specifysoftware.org/t/different-results-when-running-same-query-in-specify/2301?u=grant)
**To Reproduce**
Steps to reproduce the behavior:
1. Use the `queensland_2025_03_17` [database](https://drive.google.com/file/d/1I4nU72QEd_e4s_TT0n5TqczuuY9r-UkH/view?usp=drive_link) 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.
```sql
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.
```sql
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**
<img width="1916" alt="Image" src="https://github.com/user-attachments/assets/d6a22332-1d83-4127-9eb7-0591cc5924de" />
**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](https://discourse.specifysoftware.org/uploads/short-url/77YNHXMVuCTJGopwKeWvQSNRIFK.txt) (1.0 MB)