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

On 04-Nov-98 Tom Lane wrote:
> darcy@druid.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@michvhf.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
entitledto lecture me about bloat              than the federal government"  -- Tony Snow
 
==========================================================================