Re: Make foo=null a warning by default.

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> On 16/07/18 04:40, David Fetter wrote:
>> Per a discussion with Andrew Gierth and Vik Fearing, both of whom
>> helped make this happen, please find attached a patch which makes it
>> possible to get SQL standard behavior for "= NULL", which is an error.
>> It's been upgraded to a warning, and can still be downgraded to
>> silence (off) and MS-SQL-compatible behavior (on).

> I don't agree with changing the default to 'warn'. "foo = NULL" is
> perfectly legal SQL, even if it's not very useful in practice.

I think that there's a very narrow argument to be made that SQL doesn't
allow a NULL literal with context-determined type in this context. But
we decided to generalize that restriction long ago, and suddenly deciding
to enforce it only in this one context makes no sense to me. The idea
that we would ever decide that it's an error seems flat out ridiculous.

TBH I'm not really excited about investing any work in this area at all.
Considering how seldom we hear any questions about transform_null_equals
anymore[1], I'm wondering if we couldn't just rip the "feature" out
entirely. But to the extent that we want to leave it in place, it's
100% for backwards compatibility, and that is a strong argument against
changing anything about it.

I'm also pretty dubious that issuing a warning here will accomplish much
to help anyone find bugs. There are too many small variants of the same
problem that it will not catch[2].

regards, tom lane

[1] In a quick search, the most recent discussion I could find was from
2011:
https://www.postgresql.org/message-id/flat/201110070729(dot)p977Tdcx075504(at)wwwmaster(dot)postgresql(dot)org
Before that it came up maybe once a year or so, but it's just fallen
off a cliff since then. I wonder whether Microsoft fixed Access to
not need it.

[2] Compare for instance the discussion about bug #6064,
https://www.postgresql.org/message-id/flat/4DFE481F020000250003E8EA%40gw.wicourts.gov
A very large fraction of the pre-2011 threads mentioning
transform_null_equals are essentially of the form "why didn't/can't
transform_null_equals fix this bad code for me?". Each of those cases
would also be a case that the proposed warning doesn't catch.