Several records of resources with same resource Id returned leading to duplication

[SebaMutuku]

Describe the bug
When editing or updating resources in the db, severral resources with the same last updated exist on the db. When searching for the resources on code at

fhirEngine.search<Resource>(search)

the query

SELECT * FROM (
SELECT  rie.index_name, rie.index_value, re.resourceId,  re.serializedResource
FROM ResourceEntity re
JOIN ReferenceIndexEntity rie
ON re.resourceUuid = rie.resourceUuid
LEFT JOIN DateIndexEntity b
ON re.resourceType = b.resourceType AND re.resourceUuid = b.resourceUuid AND b.index_name = '_lastUpdated'
LEFT JOIN DateTimeIndexEntity c
ON re.resourceType = c.resourceType AND re.resourceUuid = c.resourceUuid AND c.index_name = '_lastUpdated'
WHERE rie.resourceType = 'Encounter'  AND rie.index_name = 'subject'  AND rie.index_value IN ('Patient/607155f4-44a9-4dde-a429-5102a80b4420') AND re.resourceType = 'Encounter'
ORDER BY b.index_from DESC, c.index_from DESC
)

Result - Records 2 & 4 are similar

1 | subject | Patient/607155f4-44a9-4dde-a429-5102a80b4420 | c4c69f24-187a-4ce0-aa13-060b95d64297 | {"resourceType":"Encounter","id":"c4c69f24-187a-4ce0-aa13-060b95d64297","meta":{"lastUpdated":"2024-04-11T14:09:02.874+03:00","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d1-d2c4-4573-bdd1-5bbade37b886","display":"Practitioner Location"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/app-version","code":"1.1.0-diabetesCompassClinic","display":"Application Version"}]},"status":"finished","class":{"system":"http://terminology.hl7.org/CodeSystem/v3-ActCode","code":"AMB"},"serviceType":{"coding":[{"system":"http://terminology.hl7.org/CodeSystem/service-type","code":"335"}]},"subject":{"reference":"Patient/607155f4-44a9-4dde-a429-5102a80b4420"},"participant":[{"individual":{"reference":"Practitioner/49b72a3d-44cd-4a74-9459-4dc9f6b543fa"}}],"serviceProvider":{"reference":"Organization/41eae946-bdc4-4179-b404-6503ff12f59c"},"partOf":{"reference":"Encounter/patient-visit-2024-04-11-607155f4-44a9-4dde-a429-5102a80b4420"}}
-- | -- | -- | -- | --
2 | subject | Patient/607155f4-44a9-4dde-a429-5102a80b4420 | patient-visit-2024-04-11-607155f4-44a9-4dde-a429-5102a80b4420 | {"resourceType":"Encounter","id":"patient-visit-2024-04-11-607155f4-44a9-4dde-a429-5102a80b4420","meta":{"lastUpdated":"2024-04-11T14:09:02.778+03:00","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d1-d2c4-4573-bdd1-5bbade37b886","display":"Practitioner Location"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/app-version","code":"1.1.0-diabetesCompassClinic","display":"Application Version"}]},"status":"finished","class":{"system":"http://terminology.hl7.org/CodeSystem/v3-ActCode","code":"AMB"},"serviceType":{"coding":[{"system":"http://terminology.hl7.org/CodeSystem/service-type","code":"335"}]},"subject":{"reference":"Patient/607155f4-44a9-4dde-a429-5102a80b4420"},"participant":[{"individual":{"reference":"Practitioner/49b72a3d-44cd-4a74-9459-4dc9f6b543fa"}}],"period":{"start":"2024-04-11T00:00:00.00Z","end":"2024-04-11T00:00:00.00Z"},"serviceProvider":{"reference":"Organization/41eae946-bdc4-4179-b404-6503ff12f59c"}}
3 | subject | Patient/607155f4-44a9-4dde-a429-5102a80b4420 | b0ba8f6e-3cff-424d-b184-5dc6d796f4b9 | {"resourceType":"Encounter","id":"b0ba8f6e-3cff-424d-b184-5dc6d796f4b9","meta":{"lastUpdated":"2024-04-11T14:05:42.159+03:00","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d1-d2c4-4573-bdd1-5bbade37b886","display":"Practitioner Location"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/app-version","code":"1.1.0-diabetesCompassClinic","display":"Application Version"}]},"status":"finished","class":{"system":"http://terminology.hl7.org/CodeSystem/v3-ActCode","code":"AMB"},"serviceType":{"coding":[{"system":"http://terminology.hl7.org/CodeSystem/service-type","code":"335"}]},"subject":{"reference":"Patient/607155f4-44a9-4dde-a429-5102a80b4420"},"participant":[{"individual":{"reference":"Practitioner/49b72a3d-44cd-4a74-9459-4dc9f6b543fa"}}],"serviceProvider":{"reference":"Organization/41eae946-bdc4-4179-b404-6503ff12f59c"},"partOf":{"reference":"Encounter/patient-visit-2024-04-11-607155f4-44a9-4dde-a429-5102a80b4420"}}
4 | subject | Patient/607155f4-44a9-4dde-a429-5102a80b4420 | patient-visit-2024-04-11-607155f4-44a9-4dde-a429-5102a80b4420 | {"resourceType":"Encounter","id":"patient-visit-2024-04-11-607155f4-44a9-4dde-a429-5102a80b4420","meta":{"lastUpdated":"2024-04-11T14:09:02.778+03:00","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d1-d2c4-4573-bdd1-5bbade37b886","display":"Practitioner Location"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/app-version","code":"1.1.0-diabetesCompassClinic","display":"Application Version"}]},"status":"finished","class":{"system":"http://terminology.hl7.org/CodeSystem/v3-ActCode","code":"AMB"},"serviceType":{"coding":[{"system":"http://terminology.hl7.org/CodeSystem/service-type","code":"335"}]},"subject":{"reference":"Patient/607155f4-44a9-4dde-a429-5102a80b4420"},"participant":[{"individual":{"reference":"Practitioner/49b72a3d-44cd-4a74-9459-4dc9f6b543fa"}}],"period":{"start":"2024-04-11T00:00:00.00Z","end":"2024-04-11T00:00:00.00Z"},"serviceProvider":{"reference":"Organization/41eae946-bdc4-4179-b404-6503ff12f59c"}}
5 | subject | Patient/607155f4-44a9-4dde-a429-5102a80b4420 | af306880-090e-474a-ab16-46bd54a44525 | {"resourceType":"Encounter","id":"af306880-090e-474a-ab16-46bd54a44525","meta":{"lastUpdated":"2024-04-11T14:04:59.076+03:00","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d1-d2c4-4573-bdd1-5bbade37b886","display":"Practitioner Location"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/app-version","code":"1.1.0-diabetesCompassClinic","display":"Application Version"}]},"status":"finished","class":{"system":"http://terminology.hl7.org/CodeSystem/v3-ActCode","code":"HH"},"type":[{"coding":[{"system":"http://snomed.info/sct","code":"184047000","display":"Patient registration"}],"text":"Patient registration"}],"priority":{"coding":[{"system":"http://snomed.info/sct","code":"17621005","display":"Normal"}],"text":"Normal"},"subject":{"reference":"Patient/607155f4-44a9-4dde-a429-5102a80b4420"},"participant":[{"individual":{"reference":"Practitioner/49b72a3d-44cd-4a74-9459-4dc9f6b543fa"}}],"period":{"start":"2024-04-11T14:04:59+03:00","end":"2024-04-11T14:04:59+03:00"},"reasonCode":[{"coding":[{"system":"http://smartregsiter.org/","code":"client_registration","display":"Client Registration"}],"text":"Client Registration"}],"location":[{"status":"completed","period":{"start":"2024-04-11T14:04:59+03:00","end":"2024-04-11T14:04:59+03:00"}}],"serviceProvider":{"reference":"Organization/41eae946-bdc4-4179-b404-6503ff12f59c"}}

Component

fhirEngine.search<Resource>(search)

To Reproduce
Update any resource, save it in the app SQlite DB then try querying them. Several records will be returned depending on how many times you have made changes on the resources

Expected behavior
A single record of any resources should be returned when we make any changes to a resource.

Screenshots
An example is when updating a patient active status to false. Patients Adrew Kife returns two resources with same details.

[allan-on]

Additional context

• As mentioned above when resources are updated and updateIndicesForResource() executed, there'll be multiple DateTimeIndexEntity entries for that resource.

• In this additional example, duplicate resource records are returned from the getIncludeQuery that's used in searchForwardReferencedResources()
  This is a result of the LEFT JOIN DateTimeIndexEntity

[MJ1998]

Hey @SebaMutuku. You are probably right in why duplicate resources are being returned in search - because of duplicate DateTimeIndexEntity records. However, I am not able to observe duplicate DateTimeIndexEntity records when updating same resource locally.

Can you provide steps to replicate it ?
Is it happening when the resource is synced and then one is making local changes ?

[SebaMutuku]

Hello @MJ1998? attached is the SQLite DB for patient shown on the screenshot below

Patient ID 119c3d24-123b-4719-b39d-5e9be05f8e85
Check the record on LocalChangeEntity and to answer your question, this only happens on local changes. When you sync data to the server, the issue is solved.
resources_duplication_db.zip

[MJ1998]

LocalChangeEntity has one record per resource. Do you mean to check DateTimeIndexEntity table ?

[SebaMutuku]

@MJ1998 yes, that's the entry causing duplication. If you check it on this line https://github.com/google/android-fhir/blob/master/engine/src/main/java/com/google/android/fhir/search/MoreSearch.kt#L58 you will see what the query returns

[aditya-07]

@SebaMutuku @allan-on Can you please add the Search Query passed into the fhirEngine.search<Resource>(search).

Also, can you specify the engine version that's being used.

[SebaMutuku]

@aditya-07 are you available for us to have a debugging session? If yes, let me know when I can set one up.

[aditya-07]

@aditya-07 are you available for us to have a debugging session? If yes, let me know when I can set one up.

Yes, that sounds like a good idea. Feel free to setup one today (Wednesday) or tomorrow between 11am-6pm IST.

[SebaMutuku]

@aditya-07 here's the sample database we have had a look at together
resources_db.zip