Assertion failed: unexpected NULL value

[ahartman]

Dear Gwendal,

Please find attached my app and the entry in Library/Containers that holds the database. I commented out everything not necessary for you in the app. You will see a menu ‘Commands’, bringing you to a button ‘get timeline’; that button will trigger the error in DBModel().getPatientTimeline().

I hope this helps you, let me know if need something else.

Met vriendelijke groeten, Kind regards, Mes meilleures salutations, André Hartman

Archief.zip

[ahartman]

Dear Gwendal, This morning my app crashes at startup, please find Crash Report attached. If I take the code and run from Xcode, it will not crash.

This has to do with the same PatientTimeLine function that has the NULL value issues. Hope this helps. Regards, André Hartman Crash Report.txt

[groue]

🎁 Thank you very much @ahartman,

This sample project and data indeed reveals a bug in GRDB. This bug prevents a normal error to be reported to your app. Next version will fix this.

Until then, I commented out the assertion, and the error is now correctly reported to your app (caught in DBModel.getPatientTimeline()):

could not decode Date from database value NULL
column: "minVisitVisitCreated"
column index: 2
row: [id:2289 patientName:"Aaliyah" minVisitVisitCreated:NULL maxVisitVisitDate:NULL]
sql: SELECT
       "patient"."id", "patient"."patientName",
       MIN("visit"."visitCreated") AS "minVisitVisitCreated",
       MAX("visit"."visitDate") AS "maxVisitVisitDate"
     FROM "patient"
     LEFT JOIN "visit"
       ON ("visit"."patientId" = "patient"."id")
       AND ("visit"."visitCalendar" IN (?, ?)) AND ("visit"."visitCalendar" IN (?, ?))
     GROUP BY "patient"."id"
     ORDER BY "patient"."patientName"
arguments: ["Marieke", "Marieke nieuwe", "Marieke", "Marieke nieuwe"]

So the database contains NULL in the minVisitVisitCreated column. This can happen when MIN("visit"."visitCreated") is NULL (if there is no filtered visit, for example).

This NULL value is decoded into PatientTimelineInfo, which has non-null dates:

struct PatientTimelineInfo: Decodable, FetchableRecord {
    var patient: PatientInfo.Patient
    var minVisitVisitCreated: Date
    var maxVisitVisitDate: Date
}

The fix (in your app) is to modify PatientTimelineInfo so that its dates are optional:

struct PatientTimelineInfo: Decodable, FetchableRecord {
    var patient: PatientInfo.Patient
    var minVisitVisitCreated: Date? // nil when there is no visit
    var maxVisitVisitDate: Date?    // nil when there is no visit
}

I'll take care of the bug in GRDB that prevented the correct error to be reported in the first place. 👍

[ahartman]

Dear Gwendal,

I am happy I could help you finding a bug in your code.

Making the dates optional would be a fix, but the better fix from a functional point of view is to add the having clause .having(filteredVisits.isEmpty == false)

Kind regards, André Hartman

Op 20 mrt 2024, om 18:57 heeft Gwendal Roué @.***> het volgende geschreven:

🎁 Thank you very much @ahartman https://github.com/ahartman,

This sample project and data indeed reveals a bug in GRDB. This bug prevents a normal error to be reported to your app. Next version will fix this.

Until then, I commented out the assertion, and the error is now correctly reported to your app (caught in DBModel.getPatientTimeline()):

could not decode Date from database value NULL column: "minVisitVisitCreated" column index: 2 row: [id:2289 patientName:"Aaliyah" minVisitVisitCreated:NULL maxVisitVisitDate:NULL] sql: SELECT "patient"."id", "patient"."patientName", MIN("visit"."visitCreated") AS "minVisitVisitCreated", MAX("visit"."visitDate") AS "maxVisitVisitDate" FROM "patient" LEFT JOIN "visit" ON ("visit"."patientId" = "patient"."id") AND ("visit"."visitCalendar" IN (?, ?)) AND ("visit"."visitCalendar" IN (?, ?)) GROUP BY "patient"."id" ORDER BY "patient"."patientName" arguments: ["Marieke", "Marieke nieuwe", "Marieke", "Marieke nieuwe"] So the database contains NULL in the minVisitVisitCreated column. This can happen when MIN("visit"."visitCreated") is NULL (if there is no visit, for example).

This NULL value is decoded into PatientTimelineInfo, which has non-null dates:

struct PatientTimelineInfo: Decodable, FetchableRecord { var patient: PatientInfo.Patient var minVisitVisitCreated: Date var maxVisitVisitDate: Date } The fix (in your app) is to modify PatientTimelineInfo so that its dates are optional:

struct PatientTimelineInfo: Decodable, FetchableRecord { var patient: PatientInfo.Patient var minVisitVisitCreated: Date? // nil when there is no visit var maxVisitVisitDate: Date? // nil when there is no visit } I'll take care of the bug in GRDB that prevented the correct error to be reported in the first place. 👍

— Reply to this email directly, view it on GitHub https://github.com/groue/GRDB.swift/issues/1512#issuecomment-2010266995, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAFIBPCTJYI43Q4YLYKZDQTYZHEZ3AVCNFSM6AAAAABE3OYRKWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAMJQGI3DMOJZGU. You are receiving this because you were mentioned.

[groue]

Oh, yes, thanks for reminding me about this.

So I restored the .having(filteredVisits.isEmpty == false) line.

It runs correctly.

The SQL looks valid, if not ideal (note the repetition of the "visit"."visitCalendar" IN (?, ?) condition, which is superfluous):

SELECT
  "patient"."id", "patient"."patientName",
  MIN("visit"."visitCreated") AS "minVisitVisitCreated",
  MAX("visit"."visitDate") AS "maxVisitVisitDate"
FROM "patient"
LEFT JOIN "visit"
  ON ("visit"."patientId" = "patient"."id")
  AND ("visit"."visitCalendar" IN (?, ?))
  AND ("visit"."visitCalendar" IN (?, ?))
  AND ("visit"."visitCalendar" IN (?, ?))
GROUP BY "patient"."id"
HAVING COUNT(DISTINCT "visit"."rowid") > 0
ORDER BY "patient"."patientName"

Would you please remind me what prevents you from restoring the having clause? Now that I have a running app, it's easier to reproduce!

[ahartman]

Gwendal,

Nothing prevents me from using the .having clause. I had some issues with it that I forgot. I am now using it and that brings the desired result.

Functionally there should be no patients without visits. However, there can be patients with only canceled visits; those visits are filtered out. That may result in patients with no VALID visits and those are filtered with the having clause.

And again, SQL is a very subtle science.

Á la prochaine.

André Hartman

Op 20 mrt 2024, om 20:00 heeft Gwendal Roué @.***> het volgende geschreven:

Oh, yes, thanks for reminding me about this.

So I restored the .having(filteredVisits.isEmpty == false) line.

It runs correctly.

The SQL looks valid, if not ideal (note the repetition of the "visit"."visitCalendar" IN (?, ?) condition, which is superfluous):

SELECT "patient"."id", "patient"."patientName", MIN("visit"."visitCreated") AS "minVisitVisitCreated", MAX("visit"."visitDate") AS "maxVisitVisitDate" FROM "patient" LEFT JOIN "visit" ON ("visit"."patientId" = "patient"."id") AND ("visit"."visitCalendar" IN (?, ?)) AND ("visit"."visitCalendar" IN (?, ?)) AND ("visit"."visitCalendar" IN (?, ?)) GROUP BY "patient"."id" HAVING COUNT(DISTINCT "visit"."rowid") > 0 ORDER BY "patient"."patientName" Would you please remind me what prevents you from restoring the having clause? Now that I have a running app, it's easier to reproduce!

— Reply to this email directly, view it on GitHub https://github.com/groue/GRDB.swift/issues/1512#issuecomment-2010379222, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAFIBPCWOIFHIGBMEAXJAGDYZHMFFAVCNFSM6AAAAABE3OYRKWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAMJQGM3TSMRSGI. You are receiving this because you were mentioned.