Token unknown error on DATEDIFF(DAY ...) after Firebird migration

I have the following code made from when my database was HSQLDB:

SELECT
    "Item",
    DATEDIFF('DAY', CURRENT_DATE, "Expiry") AS "Days Remaining"
FROM "Expirations"

The date values in the Expiry column are in the YY-MM-DD format.

After Libreoffice Base threw up a message about how it plans to eventually migrate to Firebird, I accepted the migration on a backup, which now causes the above query to throw up the following error:

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 26
*'DAY'
caused by
'isc_dsql_prepare'
at ./connectivity/source/drivers/firebird/Util.cxx:69

I only just started getting comfortable with the basics of HSQLDB, so I'm now trying to pour through the Firebird documentation to understand what the issue is.

The documentation states the correct syntax is:

DATEDIFF (<args>)

<args> ::=
    <unit> FROM <moment1> TO <moment2>
  | <unit>, <moment1>, <moment2>

<unit> ::=
    YEAR | MONTH | WEEK | DAY
  | HOUR | MINUTE | SECOND | MILLISECOND
<momentN> ::= a DATE, TIME or TIMESTAMP expression

DAY on its own throws up a syntax error, so I have to use 'DAY'. That wasn't the issue, so I'm not sure why it's having an issue with that aspect of the arguments. The Expiry column in that table was created as a date, and I would assume CURRENT_DATE is also a date.

What am I missing?

Solution

The syntax for DATEDIFF in Firebird doesn't have the unit name in quotes, it is a bare token, not a string literal, so you need to use:

DATEDIFF(DAY, CURRENT_DATE, "Expiry")

DATEDIFF(DAY FROM CURRENT_DATE TO "Expiry")

Your problem seems specific to LibreOffice Base, and it looks like LibreOffice Base injects some sort of translation layer from HSQLDB syntax to Firebird syntax which doesn't handle this Firebird syntax and instead returns a syntax error. On the other hand, if you try to use the HSQLDB syntax, you run into a problem that the translation layer seems to evaluate CURRENT_DATE to a string literal (e.g. '2022-10-14' instead of just using CURRENT_DATE or providing a date literal like date '2022-10-14'), which then isn't accepted by Firebird (nor does the translation layer remove the quotes around 'day').

If you enable the "Run SQL command directly" option (button with word "SQL" and a green checkmark), the Firebird syntax works (but then HSQLDB-specific syntax will not work).

I recommend reporting a bug to LibreOffice.