RE: [HACKERS] Comparisons on NULLs (was Re: A small problem...)

On 04-Nov-98 Tom Lane wrote:
> darcy(at)druid(dot)net (D'Arcy J.M. Cain) writes:
>>> but I can see the reasonableness of defining "3 != NULL" as TRUE.
>
>> Actually I see it as FALSE. That's what I was suggesting earlier. All
>> comparisons to null should be false no matter what the sense of the
>> test.
>
> Hmm. That yields extremely unintuitive results for = and !=. That is,
>
> SELECT * FROM t WHERE b = NULL;
>
> will never return any rows, even if there are some where b is null;
> and
>
> SELECT * FROM t WHERE b != NULL;
>
> will never return any rows, even if there are some where b isn't null.
>
> If this is the definition then you cannot select rows with null entries
> using the same syntax as for selecting rows with particular non-null
> values, which is what I thought the point of the CREATE FUNCTION example
> was.
>
>> That way you can always decide in the select statement whether
>> you want a particular comparison to null to be included or not.
>> [D'Arcy proposes that these ops need not give the same result:
>> SELECT * FROM t WHERE i1 < i2;
>> SELECT * FROM t WHERE NOT (i1 >= i2);
>
> Ugh. I think it'd be a lot more intuitive to write something like
>
> SELECT * FROM t WHERE i1 < 33 OR i1 IS NULL;
>
> But getting this to work without introducing unintended consequences
> might be pretty tricky too. If "NULL < 33" returns NULL, as I'd prefer,
> then OR has to be non-strict, and in fact NULL OR 't' has to give 't'.
> That looks pretty reasonable at first glance, but there are probably
> other examples where it does something undesirable.
>
> Maybe, the boolean-combining operators (AND, OR, NOT) can safely be
> made non-strict (treating NULL as FALSE), but I'm wary of that.
>
> We probably ought to go re-read the IEEE float math specs. What I think
> you are getting at is almost the same as their distinction between
> "NaN-aware" and "non-NaN-aware" comparison operators, but I've forgotten
> the details of how those work. (And I have to leave in a minute, so I
> can't look them up right now...)

I looked at this earlier, but it was me that had to leave then I forgot
all about it till now. Now it's confusing.

Looking at this (and *please* let's not get into IS vs = yet):

SELECT * FROM t WHERE b = NULL;

I first looked at this from within a C program. Consider the input coming
from a form and constructing the select statement from it's submission
values:

sprintf(buf,"SELECT * FROM t WHERE a = %d AND b = '%s'",abc,xyz);

If I understand what you're saying above, if xyz is NULL and b is NULL
then it doesn't matter what a is 'cuze it'll never return any results.

I'll shut up now in case I'm misintrepreting this..

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev(at)michvhf(dot)com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"There is no outfit less entitled to lecture me about bloat
than the federal government" -- Tony Snow
==========================================================================