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

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.

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