Describe the bug
A SMART-on-FHIR compartment-scoped search (e.g. a patient-scoped token with patient/*.read) that sorts by a search parameter value returns an empty result set, even though the identical search without _sort returns matches. Sorting by _lastUpdated works correctly; the failure occurs when sorting by an indexed search parameter (e.g. _sort=date on Observation), which takes the sort-value emission path in the SQL query generator.
FHIR Version?
R4/R4B (likely all versions; verified on R4)
Data provider?
SQL Server
To Reproduce
Steps to reproduce the behavior:
- Configure a SMART patient-scoped request context: scope
patient/*.read, AccessControlContext.CompartmentId = <patient id>, CompartmentResourceType = Patient (equivalent to a patient-launch token).
- Search
Observation?_tag=<some tag> (or any Observation search matching resources in the patient's compartment where the observations have effectiveDateTime values) — the in-compartment observations are returned.
- Add
_sort=date to the same search.
This is also reproducible with the integration test harness (SmartSearchTests-style setup in test/Microsoft.Health.Fhir.Shared.Tests.Integration/Features/Smart): the same query returns matches without _sort and zero results with _sort=date, despite every matched resource having a date value.
Expected behavior
The same compartment matches are returned, ordered by the sort parameter (with the documented two-phase handling for resources missing the sort value).
Actual behavior
Zero results are returned. No error is raised — the bundle is simply empty, so SMART apps sorting clinical data (e.g. observations by date) silently see no data.
Additional context
- The suspected area is the interplay between the SMART compartment UNION CTEs (
SmartCompartmentSearchExpression → SqlCompartmentSearchRewriter) and the sort-value table expression in SqlQueryGenerator (HandleTableKindSort, SortExpressionsByQueryLogic, _unionAggregateCTEIndex chaining), and/or the SortQuerySecondPhase logic in SqlServerSearchService.
_sort=-_lastUpdated with the same compartment context works, so the defect is specific to the sort-value (isSortValueNeeded) path.- Found while adding regression tests for SMART
_include/_revinclude compartment enforcement; it reproduces without any _include/_revinclude parameters and predates those changes.
Describe the bug
A SMART-on-FHIR compartment-scoped search (e.g. a patient-scoped token with
patient/*.read) that sorts by a search parameter value returns an empty result set, even though the identical search without_sortreturns matches. Sorting by_lastUpdatedworks correctly; the failure occurs when sorting by an indexed search parameter (e.g._sort=dateon Observation), which takes the sort-value emission path in the SQL query generator.FHIR Version?
R4/R4B (likely all versions; verified on R4)
Data provider?
SQL Server
To Reproduce
Steps to reproduce the behavior:
patient/*.read,AccessControlContext.CompartmentId = <patient id>,CompartmentResourceType = Patient(equivalent to a patient-launch token).Observation?_tag=<some tag>(or any Observation search matching resources in the patient's compartment where the observations haveeffectiveDateTimevalues) — the in-compartment observations are returned._sort=dateto the same search.This is also reproducible with the integration test harness (
SmartSearchTests-style setup intest/Microsoft.Health.Fhir.Shared.Tests.Integration/Features/Smart): the same query returns matches without_sortand zero results with_sort=date, despite every matched resource having a date value.Expected behavior
The same compartment matches are returned, ordered by the sort parameter (with the documented two-phase handling for resources missing the sort value).
Actual behavior
Zero results are returned. No error is raised — the bundle is simply empty, so SMART apps sorting clinical data (e.g. observations by date) silently see no data.
Additional context
SmartCompartmentSearchExpression→SqlCompartmentSearchRewriter) and the sort-value table expression inSqlQueryGenerator(HandleTableKindSort,SortExpressionsByQueryLogic,_unionAggregateCTEIndexchaining), and/or theSortQuerySecondPhaselogic inSqlServerSearchService._sort=-_lastUpdatedwith the same compartment context works, so the defect is specific to the sort-value (isSortValueNeeded) path._include/_revincludecompartment enforcement; it reproduces without any_include/_revincludeparameters and predates those changes.