Re: Precedence of standard comparison operators

On 2/26/15 4:09 PM, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
>> On February 26, 2015 10:29:18 PM CET, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>> My suggestion was to treat this like the standard_conforming_string
>>> change. That is, warn for many years before changing.
>
>> I don't think scs is a good example to follow.
>
> Yeah. For one thing, there wouldn't be any way to suppress the warning
> other than to parenthesize your code, which I would find problematic
> because it would penalize standard-conforming queries. I'd prefer an
> arrangement whereby once you fix your code to be standard-conforming,
> you're done.
>
> A possible point of compromise would be to leave the warning turned on
> by default, at least until we get a better sense of how this would
> play out in the real world. I continue to suspect that we're making
> a mountain out of, if not a molehill, at least a hillock. I think most
> sane people would have parenthesized their queries to start with rather
> than go look up whether IS DISTINCT FROM binds tighter than <= ...

Question of sanity aside, I can tell you that many business queries are
written with little more sophistication than monkeys with typewriters,
where you keep the monkeys fed with bananas and paper until your query
results (not the SQL) looks like what someone expects it to look like.
Then the output of that version is held as sacrosanct, and any future
SQL changes are wrong unless they produce the expected result changes.
In my experience this happens because some poor business person just
needs to get their job done and either isn't allowed to bother the data
people or the data people are just too busy, so they're stuck doing it
themselves. From what I've seen, SQL written by developers is often a
bit better than this... but not a lot. And part of that salvation is
because application queries tend to be a lot less complex than
reporting/BI ones.

I don't have a great feel for how much of an impact this specific change
would have on that... the examples so far have all been pretty esoteric.
I suspect most people writing such "wonderful" SQL don't know about IS
DISTINCT FROM nor would they try doing things like bool_a > bool_b >=
bool_c. So there may actually be very little code to fix, but I think we
at least need a way for users to verify that.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com