Re: Failure to coerce unknown type to specific type

Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I don't have a lot of sympathy for that one. coalesce(null, null)
>> isn't legal at all per SQL spec

> I don't get that from my reading of the SQL spec. A COALESCE
> clause is (and always has been) considered a short form of the CASE
> clause (not to be mistaken for a function, for example). The spec
> section 6.11 1) c) very explicitly requires
> COALESCE(NULL, NULL)
> be the exact equivalent of
> CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END

Sure, and that isn't legal per spec either (see below).

> Yet in PostgreSQL the long form of the CASE clause returns the same
> thing as a bare NULL, while the short form (COALESCE) gives an
> error.

Hm? I get the same thing for either variant:

regression=# create table x (d date);
CREATE TABLE
regression=# insert into x values (coalesce(null, null));
ERROR: column "d" is of type date but expression is of type text
LINE 1: insert into x values (coalesce(null, null));
^
HINT: You will need to rewrite or cast the expression.
regression=# insert into x values ( CASE WHEN NULL IS NOT NULL THEN NULL ELSE NULL END );
ERROR: column "d" is of type date but expression is of type text
LINE 1: insert into x values ( CASE WHEN NULL IS NOT NULL THEN NULL ...
^
HINT: You will need to rewrite or cast the expression.

The reason these things aren't legal per spec is that the spec says that
a bare NULL keyword is a <contextually typed value specification> a/k/a
<implicitly typed value specification>, and those are only valid in
situations where a type can be inferred from the *immediate* context.
For example,
insert into x values (null);
is legal because the source of an INSERT can be a
<contextually typed table value constructor> which is a VALUES clause
that can contain a <contextually typed value specification>. On the
other hand, result subexpressions of a CASE are just <value expression>s,
and you will not find any production that allows a bare NULL literal
to be a <value expression>. So far as I can find in SQL:2008, the
only contexts where <contextually typed anything> is syntactically
legal are (1) INSERT, MERGE, and UPDATE source expressions, (2) CAST
source expressions, and (3) table-column DEFAULT expressions, all of
which have a well-defined target type available from the immediately
surrounding semantic context.

In short, the text of the spec only allows a bare NULL literal as
the immediate argument of one of those constructs. Allowing it in
any other context is an extension.

The spec doesn't have a notion of unknown-type literal in the same way
we do, but we've modeled our handling of those as being like bare NULL
literals for type resolution purposes.

Our choice has been to resolve as text in situations where there is no
other info available from immediate context. I agree with the spec
that it would be a bad idea to allow "action at a distance" in the
sense of allowing such info to propagate through multiple levels
of semantic context.

regards, tom lane