Re: BUG #6064: != NULL, <> NULL do not work [sec=UNCLASSIFIED]

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 =, <> and != saying Important: see difference from IS [NOT] NULL.
Perhaps one reason I didn't pick up on this subtle issue is that IS NULL and 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 operator in this language and only
going forward from the table, not realising I had to read any further.

I'd also add after "Do not write expression = NULL because NULL is not "equal to" NULL."
Do not write expression != NULL or <> NULL because NULL is not "not equal to" NULL.
because while implied, it's not obvious that because = doesn't work with NULL that != 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 when it comes across =,<> != 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(dot)Grittner(at)wicourts(dot)gov]
Sent: Fri 6/17/2011 11:50 PM
To: Pilling, Michael; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work

"Michael Pilling" <Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:

> A reasonable programmer would expect != NULL, <> NULL and IS NOT
> NULL to be synonyms.

Only if that programmer was not aware of the SQL standard and had
not worked much with a standard-conforming database.

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.

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.

This is most definitely not a bug in the software. The
documentation does cover it here:

http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html

Is there something you would add to that?

-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 you have received this email in error, you are requested to contact the sender and delete the email.