Re: Re: very big problem with NULL

Alexey Borzov <borz_off@rdw.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