SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — November 2, 2023
Nov 2, 2023

Topics discussed:

  • alias was renamed back to name and made required. The original hope was to infer a column name from the FHIRPath expression, but that fell apart: database identifiers have length limits, sanitising special characters still leaves collisions, and appending _1 and _2 to fix them is more confusing than useful. Being able to look at a view and know exactly what you will get won the argument. It is a breaking change, and the group spent real time on the meta-question of how to stop making them — the tone was that a change worth years of avoided confusion is worth breaking now, but the licence to keep doing it is running out.
  • Union landed as: it sits next to select, it is an array, and every branch must produce the same columns — same names, same order — or it is an error. If you genuinely want different columns per branch you set one to null explicitly. The framing that stuck was 'you should not be required to fail if the conditions are not met, you should be required to succeed if they are'. DuckDB's UNION ALL BY NAME is roughly the behaviour described.
  • Column types across union branches were deliberately left undefined. Engines already disagree — some coerce two numeric types into a common one, some refuse — and that felt too implementation-specific to legislate. The deeper worry was that the group is straddling two type systems, FHIR's and the database's, and that mapping between them is a much larger topic than one meeting.
  • The interaction between siblings got nailed down as: everything at the same level is a cartesian product, unless it is constrained by a forEach or forEachOrNull; parent-child relationships bubble up instead. A union should behave exactly like another select in that respect — the parent shouldn't care which it is. Someone floated writing an operational semantics, a small pseudo-code model, because talking through the tree in prose confuses even the people who designed it.
  • Two smaller things: union is being renamed unionAll, and the spec's constraint saying you may only have one of forEach/forEachOrNull/union is simply a mistake — the exclusion should be between the two forEach variants, and union can sit alongside them. On column names, consensus was that duplicates are an error, except inside a union where reusing a name is the whole point; a comment arrived in chat arguing that reusing names makes sense but requiring uniqueness would be a mistake, and that thread was taken back to Zulip.