Re: COALESCE and NULLIF semantics

On Fri, Sep 11, 2009 at 01:37:00PM -0400, Tom Lane wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> > 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).

Yup; sounds right. The fact that the inputs to COALESCE here are just
simple NULL literals is making the example seem too simple. The general
case is that of them being an arbitrary expression and you somehow need
to get the DATE type all the way back up to the top literal and make
sure that no other branch uses it as anything else. This was what my
example with using a sub-select was about, maybe this would be better
though:

SELECT date '2001-1-1' < COALESCE(NULL,v)
FROM (SELECT NULL) x(v)
WHERE v = 10;

This should fail to type check; v is being treated as both a DATE and
an INT. Getting a compiler to do this is pretty easy (store the type
constraints some where else during type checking and then make sure they
all match at the end), but isn't the way PG works at the moment.

> 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.

Most of my experience says that type-inference actually makes things
easier. If you're dealing with dynamically *checked* languages then I
can see where you comment comes from; but for typed languages, where
everything has to be perfect at compile time, then this doesn't seem to
be true. This is why people who use Haskell tend to be the ones saying
things like "when it type checks you can be pretty certain it's going to
work". I'm not saying we should be going that complicated, just that
in my experience more complicated type systems imply simpler and more
understandable runtime behavior.

Implementation is a bit harder, but it won't be much more complicated
than what PG already has. It's already dealing with most of the issues
(in a somewhat ad-hoc way) and I'd expect that getting type-inference in
would help clean other things up a bit.

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