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.

Tue, 3 Nov 1998 09:06:38 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] A small problem with the new inet and cidr typesg
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

Tue, 03 Nov 1998 10:46:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] A small problem with the new inet and cidr typesg 
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.

Tue, 3 Nov 1998 11:57:36 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] A small problem with the new inet and cidr typesg
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

Tue, 03 Nov 1998 19:26:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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 entitled to lecture me about bloat
               than the federal government"  -- Tony Snow
==========================================================================



Tue, 03 Nov 1998 19:58:55 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
RE: [HACKERS] Comparisons on NULLs (was Re: A small problem...)
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.

Tue, 3 Nov 1998 23:07:49 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: Comparisons on NULLs (was Re: A small problem...)
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 


Wed, 4 Nov 1998 01:22:16 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: Comparisons on NULLs (was Re: A small problem...)
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.

Wed, 4 Nov 1998 00:59:14 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] Re: Comparisons on NULLs (was Re: A small problem...)
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 


Wed, 4 Nov 1998 02:21:15 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: Comparisons on NULLs (was Re: A small problem...)
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 stealth
 their Linux boxes so they look like Microsoft servers that somehow
 miraculously fail to suck."         -- Eric Raymond

Tue, 3 Nov 1998 22:40:32 -0800 (PST)
dg@informix.com (David Gould)
Re: [HACKERS] Re: Comparisons on NULLs (was Re: A small problem...)
> 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

Wed, 04 Nov 1998 07:01:18 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] A small problem with the new inet and cidr typesg
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.

Wed, 4 Nov 1998 08:00:12 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] Re: Comparisons on NULLs (was Re: A small problem...)