Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards

On 7 Jun 2001, Tom Ivar Helbekkmo wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
>
> > That's the nature of the hack we're talking about. It's a grammar
> > level hack to turn a specific sequence of tokens (= NULL) into IS
> > NULL due to a client's generated queries.
>
> Aha! Sorry -- I jumped in late in the discussion without checking
> back to see how it started...
>
> OK, I've already said that I like the cleanliness and orthogonality of
> NULL is a missing data value, UNKNOWN is a missing truth value, both
> propagate in expressions, comparisons with NULL generate UNKNOWN, and
> you can use the special comparisons IS [NOT] NULL and IS [NOT] UNKNOWN
> to get plain, two-valued Boolean truth values out of them.
>
> The Microsoft compatibility hack is ugly, and should be either a)
> removed, b) expanded to include the other comparison operators and
> documented as a PostgreSQL proprietary extension, or c) made into a
> special feature that's turned on at will by a SET command. I would
> applaud a), approve of c), and be dismayed by b).

c is the most likely thing to happen probably.

> > I think adding IS UNKNOWN would probably be trivial (I think the
> > code is basically there in IS NULL.)
>
> But if it's implemented, shouldn't the code also differentiate between
> UNKNOWN and NULL, by not (as now) using the latter to represent the
> former? Or do I misunderstand how it's handled now?

Within what's there (using null as unknown), the two tests are nearly
identical and would probably be just a grammar change. Creating a
separate unknown would be more difficult, and I'm not sure it's necessary
to make the distinction. NULL is an unknown value, I'm not sure that
you'd need a separate unknown value specifically for booleans.