Re: BUG #3822: Nonstandard precedence for comparison operators

Tom Lane wrote:

> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> That said, bringing PostgreSQL into compliance with the standard
>> would undoubtedly break some people's existing applications.

I wonder how that compares to broken queries while migrating databases
from other systems. I'd bet there are more of the latter. We ran into
that while running a query like: SELECT ... WHERE column <>
another_column || 'literal';, variants of which I think can be
relatively common compared to e.g. applications that build a boolean
array using expr1 <> expr2 || boolean_value.

> The spec seems to barely have a notion of operator precedence at all ---

The precedence is given by the parse tree and is well defined. Perhaps
it may vary for a given operator depending on the context but it's
clearly different to the one PostgreSQL is using in the examples I gave.

> for example, all the variants of <predicate> are at the same precedence
> level, and if I'm reading it right they actively disallow ambiguous
> cases by requiring parentheses; note the way that <boolean primary>
> is defined. This entire arrangement breaks down as soon as you consider
> user-defined operators that yield boolean results. So I'm not
> particularly excited about the idea of slavish compliance with the spec
> in this area.

Note that we considered PostgreSQL precisely because of its high
standards compliance and this problem has been a bit disappointing, even
more given that I looked for information on what nonstandard bits did
PostgreSQL have and didn't see this.

In the case of user defined operators I'd expect them to have a fixed
precedence regardless of the semantics and that can be different
depending on the operator.

> Given that it's been this way for ten years and no one has complained
> before, I'm disinclined to change it, and even more disinclined to
> invest the effort that would be involved in letting the behavior vary
> at runtime.

It has often happened that a new version has caused the need of porting
code, that's not new. Users will very likely appreciate compliance with
the standard even with the hassle of porting the applications, specially
when the fixes, if they're necessary, can easily be made backwards
compatible by using parentheses. For that reason I don't think a runtime
selection of behaviour would be neecessary in this case.

-- Pedro Gimeno