Re: COALESCE and NULLIF semantics

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> I was thinking of changing what is currently done, for example, here:

> newc->coalescetype = select_common_type(pstate, newargs, "COALESCE",
> NULL);

> Is that so late as you say, or is there a reason that can't work?

It's what happens afterwards that's the problem --- try it and see.

> Yeah, I am. When you have queries built based on which fields on a
> QBE window are filled by a user, it's not hard to come up with a
> clause like:

> AND (somedate < COALESCE(NULL, NULL) OR ...)

Right. The only real way to fix that is to propagate the later
discovery that type 'date' would be preferred back to the inputs of the
COALESCE, which is what Sam Mason has been on about (IIUC). I'm afraid
that such a thing would make the behavior even more full of surprises
than what we have now. Resolving unknown from context is already
"action at a distance", as it were, and the longer the distance involved
the more chance for unexpected behavior. Not to mention the
implementation difficulties.

> We solved this by modifying our framework to pass down metadata about
> the values in addition to the values themselves. We were always able
> to look at an object's class to generate the correct literal type -- a
> Date object would generate a DATE '2009-09-11' format literal; but a
> NULL had been bare in that situation. We now generate CAST(NULL AS
> type) whenever we insert a NULL literal, so we are no longer burned by
> this. I'm just thinking that it would reduce pain for others.

Of course that's what the SQL spec would tell you to do anyway ;-)

regards, tom lane