Re: COALESCE and NULLIF semantics

On Fri, Sep 11, 2009 at 12:26:45PM -0500, Kevin Grittner wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > if that weren't true then we wouldn't be arguing about whether
> > COALESCE is wrong.
>
> 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 ...)
>
> We solved this by modifying our framework to pass down metadata about
> the values in addition to the values themselves.

You need a *much* more invasive change to fix this. PG's type checker
only looks one level deep when choosing what types to replace "unknown"
with; what you you want is full type-inference as it's only that which
will allow you to track back up the layers and assign consistent types
to arbitrary expressions like the above.

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