date.trunc was added in #5729 with dialect-specific handling for BigQuery and MSSQL, but lacks an override for SQLite. SQLite does not have a DATE_TRUNC function, so the generic fallback generates invalid SQL:
$ prqlc compile --target sql.sqlite 'from events | select (event_time | date.trunc "day")'
SELECT
DATE_TRUNC('day', event_time)
FROM
events
This produces SQL that will fail at runtime on SQLite. Other dialects that lack DATE_TRUNC (like Postgres) correctly return an error for date.diff, establishing a pattern for handling unsupported operations.
Expected behavior: Either generate a SQLite-compatible equivalent (e.g., using strftime or date()) or return a clear error message like date.diff does for unsupported dialects.
Suggested fix: Add a SQLite override in std.sql.prql that either maps to strftime or returns null (to produce an "unsupported" error), similar to how date.diff is handled for Postgres/SQLite.
date.truncwas added in #5729 with dialect-specific handling for BigQuery and MSSQL, but lacks an override for SQLite. SQLite does not have aDATE_TRUNCfunction, so the generic fallback generates invalid SQL:This produces SQL that will fail at runtime on SQLite. Other dialects that lack
DATE_TRUNC(like Postgres) correctly return an error fordate.diff, establishing a pattern for handling unsupported operations.Expected behavior: Either generate a SQLite-compatible equivalent (e.g., using
strftimeordate()) or return a clear error message likedate.diffdoes for unsupported dialects.Suggested fix: Add a SQLite override in
std.sql.prqlthat either maps to strftime or returnsnull(to produce an "unsupported" error), similar to howdate.diffis handled for Postgres/SQLite.