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.gritt...@wicourts.gov] Sent: Fri 6/17/2011 11:50 PM To: Pilling, Michael; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work "Michael Pilling" <michael.pill...@dsto.defence.gov.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.
