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.