RE: [HACKERS] Comparisons on NULLs (was Re: A small problem...) - Mailing list pgsql-hackers
From | Vince Vielhaber |
---|---|
Subject | RE: [HACKERS] Comparisons on NULLs (was Re: A small problem...) |
Date | |
Msg-id | XFMail.981103195855.vev@michvhf.com Whole thread Raw |
In response to | Comparisons on NULLs (was Re: A small problem...) (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
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 ==========================================================================
pgsql-hackers by date: