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/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
