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

Thanks Craig,

The real problem here then is that the documentation showing
the boolean comparison operators does not mention this quirk, which I
accept may be a standard quirk but it's still a quirk. You just
wouldn't go looking for that flag unless you had any inkling that
it might exist.

And indeed the parser does not generate warnings either.

Regards,
Michael

-----Original Message-----
From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
Sent: Sun 6/19/2011 6:30 PM
To: Kevin Grittner
Cc: Pilling, Michael; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work

On 06/17/2011 10:20 PM, Kevin Grittner wrote:
> "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.

Yep, and if they want to continue working that way, they can use the
flag intended for compatibility with Microsoft Access that makes NULL =
NULL result in 't' instead of NULL.

http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS

Note that this flag is very specifically limited to equality
comparisions using the '=' operator. It won't make NULL behave as a
value in any other way. For example, 1 > NULL will still return NULL.

--
Craig Ringer

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.