Re: BUG #6064: != NULL, <> NULL do not work [sec=UNCLASSIFIED] - Mailing list pgsql-bugs

Hi Kevin,

Thanks for that. Point entirely taken. I think what I would add would be in=
 the table 9-1 of operators,
an extra column filled in only for =3D, <> and !=3D saying Important: see d=
ifference from IS [NOT] NULL.
Perhaps one reason I didn't pick up on this subtle issue is that IS NULL an=
d IS NOT NULL are not listed in this
table but they are comparison operators, just textual rather than symbolic =
ones in the grammar so they should be
in the table. I recall specifically looking up what is the not equal operat=
or in this language and only=20
going forward from the table, not realising I had to read any further.

I'd also add after "Do not write expression =3D NULL because NULL is not "e=
qual to" NULL."=20
Do not write expression !=3D NULL or <> NULL because NULL is not "not equal=
 to" NULL.
because while implied, it's not obvious that because =3D doesn't work with =
NULL that !=3D doesn't either.

Reading the note after this section saying before version 8.2 postgres was =
inconsistent with the SQL standard,
I think that really strengthens the case for the parser to issue warnings w=
hen it comes across =3D,<> !=3D used with
null and the transform_null_equals (boolean) compatibility flag isn't set.

BTW while I agree with you that "Only if they hadn't read the SQL standard"=
, how many people read the standard
of any language before they start programming in it? I may have read it 20 =
years ago but haven't recently.
It's not something you can rely on.

Best regards,
Michael

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Fri 6/17/2011 11:50 PM
To: Pilling, Michael; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6064: !=3D NULL, <> NULL do not work
=20
"Michael Pilling" <Michael.Pilling@dsto.defence.gov.au> wrote:
=20
> A reasonable programmer would expect !=3D NULL, <> NULL and IS NOT
> NULL to be synonyms.
=20
Only if that programmer was not aware of the SQL standard and had
not worked much with a standard-conforming database.
=20
NULL is conceptually intended to indicate "unknown" or "not
applicable".  If you have a person table with a date_of_birth
column, which contains NULL for a number of rows for which the date
of birth is unknown, can you say that all such people have the same
date of birth?  No; for any such person, the result of comparing
their date of birth to anyone else's (whether or not the other one
is NULL) is UNKNOWN.
=20
You might want to read up on IS [NOT] DISTINCT FROM.  In the SQL
language, while NULL is not known to be equal to NULL, you *can* say
that NULL IS NOT DISTINCT FROM NULL.
=20
This is most definitely not a bug in the software.  The
documentation does cover it here:
=20
http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html
=20
Is there something you would add to that?
=20
-Kevin


IMPORTANT: This email remains the property of the Department of Defence and=
 is subject to the jurisdiction of section 70 of the Crimes Act 1914. If yo=
u have received this email in error, you are requested to contact the sende=
r and delete the email.