Re: Failure to coerce unknown type to specific type

I wrote:
> 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.

On doing a more thorough search, I see that I missed one reference:
the result expression(s) of a CASE construct are defined as

    <result> ::= <result expression>
                 | NULL
    <result expression> ::= <value expression>

which might seem to put the lie to my thesis, except that in the
Syntax Rules we read

    At least one <result> in a <case specification> shall specify
    a <result expression>.

That means this is legal:

    CASE WHEN ... THEN 42 ELSE NULL END;

but this isn't:

    CASE WHEN ... THEN NULL ELSE NULL END;

So the core point stands.  The spec doesn't allow a bare NULL literal
anywhere that its type can't be determined from the most closely nested
semantic context.

It's a bit weird that they encode this as a syntax rule rather than a
semantic rule, but that's what they did.

            regards, tom lane