Обсуждение: Re: [HACKERS] A small problem with the new inet and cidr typesg

Thus spake Jan Wieck
> D'Arcy J.M. Cain wrote:
> > There may be cases where a function of a null is not null as some people
> > have pointed out but so far no one has come up with a practical example.
> 
>     CREATE FUNCTION cnt_t1_b(text) RETURNS int4 AS
>         'SELECT count(*) FROM t1 WHERE b = $1'
>         LANGUAGE 'sql';
> 
>     BTW:  It does not work with NULL argument currently, but IMHO
>     it should count the number of rows where b is NULL.

Well, this is the first example that I have seen that wouldn't work
unless a function with a null argument actually called the function but
you do realize that this wouldn't work anyway, right?  The following
is a parse error.
   SELECT count(*) FROM t1 WHERE b = null;

Mind you, I think that's a weakness but I don't know what the issues
are with respect to the code or the standard.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

darcy@druid.net (D'Arcy J.M. Cain) writes:
> you do realize that this wouldn't work anyway, right?  The following
> is a parse error.

>     SELECT count(*) FROM t1 WHERE b = null;

> Mind you, I think that's a weakness but I don't know what the issues
> are with respect to the code or the standard.

I believe the accepted spelling of that query is
     SELECT count(*) FROM t1 WHERE b IS NULL;

(or IS NOT NULL).  I don't know either what the SQL standard has to say
about the issue --- does it expect "= NULL" to be a synonym for "IS NULL"?

The CREATE FUNCTION example does seem to illustrate that it'd be nice
if "=" and "!=" worked on NULL values.  I'd still object to trying
to define an order that includes NULL, so "3 < NULL" should return NULL,
but I can see the reasonableness of defining "3 != NULL" as TRUE.
        regards, tom lane

Thus spake Tom Lane
> darcy@druid.net (D'Arcy J.M. Cain) writes:
> > you do realize that this wouldn't work anyway, right?  The following
> > is a parse error.
> 
> >     SELECT count(*) FROM t1 WHERE b = null;
> 
> I believe the accepted spelling of that query is
> 
>       SELECT count(*) FROM t1 WHERE b IS NULL;

Well, yes.  That's my point.  The problem is to specify that syntax if
the test is against null and the previous if not.  Using PL is one way
but it would be nice to have a pure sql way to do it too.

> The CREATE FUNCTION example does seem to illustrate that it'd be nice
> if "=" and "!=" worked on NULL values.  I'd still object to trying
> to define an order that includes NULL, so "3 < NULL" should return NULL,
> 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.  That way you can always decide in the select statement whether
you want a particular comparison to null to be included or not.  For
example, say I have a table of IP numbers and some are null.  If I
need to find all IPs that are less than some other IP then I can do
   SELECT * FROM t WHERE i1 < i2;

But let's say that in another case I needed the same test except I
wanted to include those rows where one or the other was null.  Then
I do this.
   SELECT * FROM t WHERE NOT (i1 >= i2);

See, the "i1 < i2" test is nominally the same as the "NOT (i1 >= i2)"
one but if operators consistently returned FALSE when given nulls
then you can use one or the other depending on what output you needed.

Just a thought.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

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...)
        regards, tom lane

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

Thus spake Tom Lane
> >> 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;

Hmmm.  That would be a problem.  Of course, we could treat the null
value at the higher level too.  I guess that's why we have the "IS
NULL" syntax in the first place.  It is different than comparing the
actual values.

Marc, how long can we hold 6.4 while we work this all out?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

On Tue, 3 Nov 1998, D'Arcy J.M. Cain wrote:

> Thus spake Tom Lane
> > >> 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;
> 
> Hmmm.  That would be a problem.  Of course, we could treat the null
> value at the higher level too.  I guess that's why we have the "IS
> NULL" syntax in the first place.  It is different than comparing the
> actual values.
> 
> Marc, how long can we hold 6.4 while we work this all out?
How long can we hold *what*?  Is this a new bug that didn't exist
in previous version of PostgreSQL?  

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

Thus spake The Hermit Hacker
> > Marc, how long can we hold 6.4 while we work this all out?
> 
>     How long can we hold *what*?  Is this a new bug that didn't exist
> in previous version of PostgreSQL?  

Jeez Marc, you must be working too hard.  I'll have to remember to
sprinkle those smileys a little better next time.  :-)

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

On Wed, 4 Nov 1998, D'Arcy J.M. Cain wrote:

> Thus spake The Hermit Hacker
> > > Marc, how long can we hold 6.4 while we work this all out?
> > 
> >     How long can we hold *what*?  Is this a new bug that didn't exist
> > in previous version of PostgreSQL?  
> 
> Jeez Marc, you must be working too hard.  I'll have to remember to
> sprinkle those smileys a little better next time.  :-)
*roll eyes* *groan* *wipe brow*

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

D'Arcy J.M. Cain <darcy@{druid|vex}.net> writes:
> Thus spake Tom Lane
> > >> 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;
> 
> Hmmm.  That would be a problem.  Of course, we could treat the null
> value at the higher level too.  I guess that's why we have the "IS
> NULL" syntax in the first place.  It is different than comparing the
> actual values.

Not sure how serious this discussion is, so if I have wandered into the
middle of a joke, just kick me ...

That said,

SELECT * FROM t WHERE b = NULL;

_should not_ return any rows. NULL is not "=" to anything, not even another
NULL. NULL is also not ">", or "<", or "!=" to anything either.

So, "NULL = NULL" is false as is "NULL != NULL".

This indeed is why we have "IS NULL" and "IS NOT NULL".

-dg

David Gould            dg@informix.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
"Samba is a huge win ... ; it enables open-source techies to stealththeir Linux boxes so they look like Microsoft
serversthat somehowmiraculously fail to suck."         -- Eric Raymond
 

> I believe the accepted spelling of that query is
>       SELECT count(*) FROM t1 WHERE b IS NULL;
> (or IS NOT NULL).  I don't know either what the SQL standard has to 
> say about the issue --- does it expect "= NULL" to be a synonym for 
> "IS NULL"?

afaik SQL92 does not define/allow "= NULL". However, our friends at M$
use this syntax in queries generated by M$Access, presumably pulling
their usual BS in altering standards to reduce interoperability.

Someone very recently brought this up, and the "= NULL" will be
synonymous with "IS NULL" in the next release (and patches are likely to
be available beforehand).

> The CREATE FUNCTION example does seem to illustrate that it'd be nice
> if "=" and "!=" worked on NULL values.  I'd still object to trying
> to define an order that includes NULL, so "3 < NULL" should return 
> NULL, but I can see the reasonableness of defining "3 != NULL" as 
> TRUE.

Sorry, got to go with D'Arcy on this one. C.J. Date in his recent book
"A Guide to the SQL Standard" points out the inconsistancies within
SQL92 regarding tri-value booleans and nulls. However, it is the case
that one can mostly assume that any comparison involving a NULL will
return false. Null usually means "don't know", not "isn't", but
expressions are unfortunately required to resolve to true or false.
                   - Tom

Thus spake David Gould
> D'Arcy J.M. Cain <darcy@{druid|vex}.net> writes:
> > Hmmm.  That would be a problem.  Of course, we could treat the null
> > value at the higher level too.  I guess that's why we have the "IS
> > NULL" syntax in the first place.  It is different than comparing the
> > actual values.
> 
> SELECT * FROM t WHERE b = NULL;
> 
> _should not_ return any rows. NULL is not "=" to anything, not even another
> NULL. NULL is also not ">", or "<", or "!=" to anything either.
> 
> So, "NULL = NULL" is false as is "NULL != NULL".
> 
> This indeed is why we have "IS NULL" and "IS NOT NULL".

But no one really has a use for a statement that can never return a row.
If we need that we always have "-- select 1;" (1/2 :-)) so why not let
"= NULL" be a synonym for "IS NULL" and "NOT = NULL" be a synonym for "IS
NOT NULL?"

Well, other than the fact  that M$ does it?  (Other 1/2 of that :-))

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.