Re: Re: very big problem with NULL

Alexey Borzov <borz_off(at)rdw(dot)ru> writes:
> TL> NULL does not equal NULL, either. Read the SQL spec, or any of the
> TL> many prior coverings of this territory in our list archives ...

> Er, how about this stuff?

> newweb=# select null=null;
> ?column?
> ----------
> t
> (1 row)

That's in the archives, too: there's a special hack to convert the
literal sequence "= NULL" into the SQL-legal "IS NULL" operator.
Wihout that hack, Microsoft Access' forms code does not work (seems
no one at M$ can read the spec :-().

In fact I was just arguing a few days ago on pghackers that it's time
to remove that hack, because it confuses too many people... I seem to
have lost the argument (again), but it's still a pet peeve.

Here's an example:

regression=# create table foo (f1 int);
CREATE
regression=# insert into foo values (1);
INSERT 412352 1
regression=# insert into foo values (null);
INSERT 412353 1
regression=# select f1 = f1 from foo;
?column?
----------
t

(2 rows)

regression=# create view vfoo as select f1 = null from foo;
CREATE

-- Peeking at the view definition shows how the parser interpreted this:

regression=# \d vfoo
View "vfoo"
Attribute | Type | Modifier
-----------+---------+----------
?column? | boolean |
View definition: SELECT (foo.f1 ISNULL) FROM foo;

regression=# select * from vfoo;
?column?
----------
f
t
(2 rows)

regards, tom lane