Re: COALESCE requires NULL from scalar subquery has a type

On Tue, Feb 9, 2016 at 5:42 AM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> On 8 February 2016 at 16:05, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> While explicit casting of literals can at times be annoying and seemingly
>> unncessary I wouldn't call it unintuitive.
>
> Well.... that very much depends on your definition of intuitive. If
> something is "seemingly unnecessary" I would say that's the same thing
> as "unintuitive", isn't it?
>
>> Typically, you cannot count on PostgreSQL to cast
>> "unknown" typed data to other types.
>
> I don't believe that I'm suggesting that Postgres should. As far as I
> can see, COALESCE takes values of type anyelement and attempts to
> decide if the types are the same: for example it's unexpectedly quite
> happy to take
>
> SELECT COALESCE('1', 0);
>
> because (I guess) it takes the "unknown" typed literal '1' and decides
> that it can coerce it into an int; note that it _won't_ do
> COALESCE('1'::text, 0) because that is explicitly typed...
>
> I'm not asking that it coerce an actual value with a genuinely unknown
> type to a text value: I'm simply suggesting that it's unnecessary for
> COALESCE to coerce an unknown-typed NULL into anything (even if you
> ignore that NULL is, as far as I know, equivalent, no matter what its
> type), because as far as COALESCE is concerned the NULL can be
> instantly ignored.

Adding special case behavior to coalesce() is probably not the answer.
coalesce() btw is itself something of a special case due to not being
a proper function. Special cases breed special surprises. I'm too
familiar with the status quo to care much anymore, but if you were to
fix this you'd be wanting to expand the scenarios where 'unknown' can
used.

There are some quirks with the type system but as they say familiarity
can breed contempt. Be advised of the enormous backwards
compatibility baggage here...history has been fairly unkind to
attempted improvements. Please don't take that as discouragement --
just setting the stage.

merlin