Re: Precedence of standard comparison operators

Noah Misch <noah(at)leadboat(dot)com> writes:
> SQL has two groups of IS tests with different precedence. The <boolean test>
> productions IS [NOT] {TRUE | FALSE | UNKNOWN} have precedence just lower than
> "<", and the <null predicate> productions IS [NOT] NULL have precedence equal
> to "<". (An implementation giving them the same precedence can conform,
> because conforming queries cannot notice the difference.)

I'm curious about your rationale for claiming that <null predicate> has
precedence exactly equal to "<" according to the spec. AFAICS the SQL
spec doesn't really tell us much about precedence of different subparts
of the grammar; at best you can infer that some things bind tighter than
others.

> I attempted to catalog the diverse precedence changes in commit c6b3c93:

> 1. Decrease precedence of "<=", ">=" and "<>" to match "<".

Check.

> 2. Increase precedence of, for example, "BETWEEN x AND Y" to match precedence
> with "BETWEEN" keyword instead of "AND" keyword. Make similar precedence
> changes to other multiple-keyword productions involving "AND", "NOT", etc.

Uh, no, I wouldn't have said that. I decreased BETWEEN's precedence,
along with IN's, to be less than OVERLAPS' precedence, matching the
precedence of LIKE/ILIKE/SIMILAR. (But see comment below about OVERLAPS.)
There was not any case where the AND would have determined its precedence
AFAICS.

> 3. Decrease precedence of IS [NOT] {TRUE | FALSE | UNKNOWN} to fall between
> NOT and "<".

Check.

> 4. Decrease precedence of IS [NOT] NULL and IS[NOT]NULL to match IS [NOT]
> {TRUE | FALSE | UNKNOWN}.

Check.

> 5. Forbid chains of "=" (make it nonassoc), and increase its precedence to
> match "<".

Check.

> 6. Decrease precedence of BETWEEN and IN keywords to match "LIKE".

Check.

>> It's
>> definitely weird that the IS tests bind more tightly than multicharacter
>> Ops but less tightly than + - * /.

> (1), (2) and (3) improve SQL conformance, and that last sentence seems to
> explain your rationale for (4).

The real reason for (4) is that it would be very difficult to get bison to
consider IS TRUE to have different precedence (against an operator on its
left) than IS NULL; we'd need even more lookahead mess than we have now.
They were not different precedence before, and they aren't now.

I did change ISNULL and NOTNULL to have exactly the same precedence as the
long forms IS NULL and IS NOT NULL, where before they were (for no good
reason AFAICS) slightly different precedence. I think that's justifiable
on the grounds that they should not act differently from the long forms.
In any case the SQL standard has nothing to teach us on the point, since
it doesn't admit these shorthands.

> I've been unable to explain (5) and (6).

I'm not following your concern about (5). The spec seems to clearly
put all six basic comparison operators on the same precedence level.
I believe that the reason our grammar had '=' as right-associative is
someone's idea that we might someday consider assignment as a regular
operator a la C, but that never has been true and seems unlikely to
become true in the future. There's certainly nothing in the spec
suggesting that '=' should be right-associative.

The reason for (6) was mainly that having IN/BETWEEN bind tighter than
LIKE doesn't seem to me to have any justification in the spec; moreover,
if it does bind tighter, we're delivering a boolean result to LIKE which
seems unlikely to be what anyone wants. So I figured we could simplify
things a bit by having one (or two really) fewer precedence levels.
Also, because said level is %nonassoc, this will now force people to
parenthesize if they do indeed want something like a BETWEEN as argument
of a LIKE, which seems like a good thing all round.

> Why in particular the following three precedence groups instead of
> combining them as in SQL or subdividing further as in PostgreSQL 9.4?

>> +%nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS
>> +%nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA
>> %nonassoc OVERLAPS

I think that the spec is fairly clear that the six comparison operators
bind looser than other operators. Now you could argue about whether LIKE
et al are "operators" but Postgres certainly treats them as such.

OVERLAPS is a special case in that it doesn't really need precedence at
all: both its arguments are required to be parenthesized. We could
possibly have removed it from the precedence hierarchy altogether, but
I didn't bother experimenting with that, just left it alone. But
because of that, "moving BETWEEN/IN below it" doesn't really change
anything.

regards, tom lane