Re: BUG #5028: CASE returns ELSE value always when type is"char"

On Wed, Sep 02, 2009 at 12:54:03PM -0500, Kevin Grittner wrote:
> Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> > If we did follow Kevin's request directly, should we also be
> > specifying the type of NULL?
>
> I don't *think* the SQL standard requires that, and barring that I
> don't see any compelling reason to type NULL.

The SQL standard certainly doesn't require it. It's just that you were
requiring the types of literals that happened to be enclosed in quotes
to have their type ascribed, so why not the NULL literal?

> One problem I do see
> with the current scheme, however, is that NULL *does* get typed to
> text when it makes no sense. In my view, a CASE expression which has
> only NULL for its return values, or an abbreviated form of CASE, such
> as COALESCE or NULLIF, should be evaluated exactly the same as if they
> were replaced by NULL itself. For example, COALESCE(NULL, NULL)
> currently yields NULL::text. In my view that's wrong. I view it as a
> bug, but that seems to be a hard sell here.

Yes, that's because PG does a bottom-up solve of the type constraints.
I think it should really result in an unknown type as well.

> Likewise, I think that in the query which started this thread, the
> cast to "char" is not sensible. I'm not sure how that could be
> resolved, but it doesn't seem correct to me.

All branches unify with the "char" type (i.e. they're all "char" or
unknown) and hence the result of the expression is determined to be of
type "char".

--
Sam http://samason.me.uk/