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

On 20/06/2011 7:43 AM, Pilling, Michael wrote:
> 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.

What URL are you looking at?

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

certainly does. Are you looking at documentation for a really ancient
version like 6.x ?

> You just
> wouldn't go looking for that flag unless you had any inkling that
> it might exist.

You shouldn't use it, either. As documented, it's an ugly hack to work
around a deficiency in MS Access, which doesn't use SQL correctly. You
may not like how NULL comparisons work, but I *strongly* recommend that
you get used to it because trying to ignore it *will* cause you problems
down the track, and there won't be any other flags to flip to change the
behaviour to how you want it.

SQL's 3-value logic isn't always popular and isn't as logically
consistent as I'd like when you get into messy things like arrays.
Unfortunately, it's not practical to just rip it out of the system
because it's so fundamentally linked into the relational calculus, how
outer joins work, how aggregates work, etc.

See: http://en.wikipedia.org/wiki/Null_(SQL)
See: http://en.wikipedia.org/wiki/Three-valued_logic

> And indeed the parser does not generate warnings either.

Why would it?

You might argue that performing an equality comparison to a literal NULL
is probably a mistake. Unfortunately, many queries are written by query
generators that will quite happily substitute null into placeholders.
This is often correct and will return the expected result so long as you
know what it means, eg:

WHERE a = NULL OR b = 1;

will return 't' if b is 1, and false (or null, which evaluates to false
for WHERE clauses) when b is not 1, irrespective of the value of 'a'.
This may well be the application author's intent, and it's certainly valid.

Maybe Pg should have a warning when " = NULL " is seen that can be
emitted at INFO log level, the same log level as the notices about
implicit index creation etc. I doubt you'll find anyone enthusiastic
about implementing it, though, and the added parser time cost hardly
seems worth it.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/