Unable to retrieve Patient resource with numerous versions from search

tswelsh commented 2 months ago

Apologies in advance as this is a bit long and a bit vague. I wasn't sure whether it is a bug or a question, but something slightly dodgy appears to have happened so I plumped for bug.

Describe the bug We have a Patient resource that has had multiple updates over time. When searching for it using the identifier search param, the search returns no results even though it definitely should. I'm not sure if the fault here is with the SQL query that does the search, or if the data in this particular DB has got into a funny state.

The Patient resource in question has 12 versions. The query we are trying is equivalent to /Patient?identifier=https://local.id.system|123456. All 12 versions have the identifier, and retrieving the Patient directly by logical id also shows that it definitely has that identifier. Digging deeper, the generated SQL query looks like this:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

DECLARE @FilteredData AS TABLE (T1 smallint, Sid1 bigint, IsMatch bit, IsPartial bit, Row int)
;WITH
cte0 AS
(
    SELECT ResourceTypeId AS T1, ResourceSurrogateId AS Sid1
    FROM dbo.TokenSearchParam
    WHERE SearchParamId = 861
        AND SystemId = @p0
        AND Code = @p1 
        AND ResourceTypeId = 81 -- Patient
)
,cte1 AS
(
    SELECT DISTINCT TOP (@p2) T1, Sid1, 1 AS IsMatch, 0 AS IsPartial 
    FROM cte0
    ORDER BY T1 ASC, Sid1 ASC
)
/* HASH sNtlrPkDO22+3QwZFia3EKOx6dlmdmiMQYxXfq57GD4= params=@p0,@p1 */
SELECT DISTINCT r.ResourceTypeId, r.ResourceId, r.Version, r.IsDeleted, r.ResourceSurrogateId, r.RequestMethod, CAST(IsMatch AS bit) AS IsMatch, CAST(IsPartial AS bit) AS IsPartial, r.IsRawResourceMetaSet, r.SearchParamHash, r.RawResource
FROM dbo.Resource r
     JOIN cte1 ON r.ResourceTypeId = cte1.T1 AND r.ResourceSurrogateId = cte1.Sid1
WHERE IsHistory = 0 
ORDER BY r.ResourceTypeId ASC, r.ResourceSurrogateId ASC

The issue appears to stem from the fact that dbo.TokenSearchParam contains many rows for the parameter in question linked to the Patient resource at different versions - one for each version of the Patient resource. The parameter @p2 looks like it defaults to 11, so cte1 returns at most 11 rows. This means that the 12th (and current!) row doesn't get returned. All the rows in cte1 are associated with historical resource versions, so the WHERE IsHistory = 0 clause filters out everything. This default value of @p2 can be overridden by passing in a _count parameter with the original query - adding &_count=11 returns the expected resource (the source code adds one to the _count).

Is the TOP (@p2) part of cte1 necessary, or should it perhaps come in the final SELECT query? Or is our DB just in an unexpected state? When trying to reproduce I was unable to get multiple rows in dbo.TokenSearchParam; it looks like the existing row for the identifier is relinked to the latest version of the Patient resource. Should there ever be rows in dbo.TokenSearchParam that refer to historical versions?

FHIR Version? Stu3

Data provider? SQL Server

To Reproduce I have been unable to locally reproduce the database state that leads to this behaviour. The team that inputted the data have assured me that they were doing standard POST/PUT requests to input the Patient data - they certainly haven't been fiddling with the DB directly or anything like that.

Expected behavior The matching Patient resource to be returned from the search.

Actual behavior No resources are returned from the search.