Can we use ANSI SQL and compile it to different dialects?

[niquola]

• Can we use ANSI SQL and compile it to different dialects?
  By Brian Kaney

[niquola]

Appache Calcite is a promising project which provides translation of ANSI SQL to different dialects

[niquola]

Here is another project to consider - https://github.com/google/zetasql

[ir4y]

This solution was mentioned on the call https://github.com/tobymao/sqlglot

[niquola]

This article looks relevant to what we are trying to achieve - https://wwwiti.cs.uni-magdeburg.de/iti_db/publikationen/ps/auto/SKS+08.pdf

[niquola]

I see one key problem with ANSI SQL - ANSI SQL does not natively support complex data types and define the syntax for the chained dot or index operators for accessing nested fields

[niquola]

To use ANSI SQL for SQL on FHIR, we should extend it with chained dot and index operators support to access nested data.

[gotdan]

Are there use cases where indexed access to a FHIR array is useful? In the examples I can think of, items would have to be retrieved by matching element values. For example, returning the observation component with a code for systolic, or returning the address with a use of home and a period without an end date.

[gotdan]

Also, I'm not sure we'd be able to handle dot notation in a translator. For example, the rules that duckdb uses to resolve these references require knowledge of the target schema (https://duckdb.org/docs/archive/0.3.2/sql/data_types/struct):

"Referring to structs with dot notation can be ambiguous with referring to schemas and tables. In general, DuckDB looks for columns first, then for struct keys within columns. DuckDB resolves references in these orders, using the first match to occur:

No dots:
SELECT part1 FROM tbl
part1 is a column

One dot:
SELECT part1.part2 FROM tbl
part1 is a table, part2 is a column
part1 is a column, part2 is a property of that column

Two (or more) dots:
SELECT part1.part2.part3 FROM tbl
part1 is a schema, part2 is a table, part3 is a column
part1 is a table, part2 is a column, part3 is a property of that column
part1 is a column, part2 is a property of that column, part3 is a property of that column

Any extra parts (e.g. .part4.part5 etc) are always treated as properties"

[bkaney]

To use ANSI SQL for SQL on FHIR, we should extend it with chained dot and index operators support to access nested data.

If this is required, perhaps we can look at the Restricted Subset of FHIRPath: https://hl7.org/fhir/fhirpath.html#simple

[niquola]

And be sure it could be implemented in popular databases! Make it even simpler :)