When runnning SELECT COALESCE(NULL, NULL); on Apache Calcite I get an error which tells that
ELSE clause or at least one THEN clause must be non-NULL
This happens because COALESCE function is transformed to CASE expression and the latter is validated for whether at least one THEN or ELSE operand is not null. If all are NULL, then the error is thrown. This is motivated by reasons described in a comment found in source code:
according to the sql standard we can not have all of the THEN statements and the ELSE returning null
One thing to note, is this query returns NULL when any of the parameters is explicitly cast, e.g. SELECT COALESCE(CAST(NULL AS VARCHAR), NULL);.
However I could not have found this anywhere. What I did found is following (see 'Abbreviations to Cope With null' section, the emphasize is mine):
Coalesce returns the first not-null parameter (or null, if all parameters are null).
This statement, provided above, seems to be in accordance with Oracle and PostreSQL docs.
My question is, should COALESCE containing literal NULLs only work as per SQL standard?
Whether anything should work only according to the SQL standard might be a matter of academic debate, but it just is not how databases work. Every database -- as far as I know -- has quirks, extensions, and exceptions to the standard. That simply is the fact of having different vendors provide such a language.
In most languages, I think that an untyped NULL ends up with a default type. I determine what it is by saving select NULL into a table (using whatever method is used for that database) and looking at the type.