Re: Precedence of standard comparison operators

On Sun, Aug 09, 2015 at 04:48:22PM -0400, Tom Lane wrote:
> 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.

Both <null predicate> and <comparison predicate> are in the set of productions
that take <row value predicand> arguments and appear only in <predicate>.
Passing a production in that set as an argument to a production in that set
requires parentheses. To restate (non-associative) "precedence equal" more
pedantically, there exists no conforming query whose interpretation hinges on
the relative precedence of "IS NULL" and "<".

> > 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.

Ah. I read this patch hunk carelessly:

> > > @@ -11420,7 +11436,7 @@ a_expr: c_expr { $$ = $1; }
> > > {
> > > $$ = (Node *) makeSimpleA_Expr(AEXPR_OF, "<>", $1, (Node *) $6, @2);
> > > }
> > > - | a_expr BETWEEN opt_asymmetric b_expr AND b_expr %prec BETWEEN
> > > + | a_expr BETWEEN opt_asymmetric b_expr AND a_expr %prec BETWEEN

That's allowing additional productions in the final BETWEEN operand, not
changing precedence.

> > 5. Forbid chains of "=" (make it nonassoc), and increase its precedence to
> > match "<".
> > 6. Decrease precedence of BETWEEN and IN keywords to match "LIKE".

> > 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.
> [snipped rest of explanation]

No particular concern beyond wanting to know the rationale; thanks for writing
one. Getting this wrong a second time would be awfully sad, so I'm being more
cautious than usual.

> > 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.

To my knowledge, SQL is agnostic about whether LIKE "is an operator". The six
comparison operators bind looser than <common value expression> syntax like
"*" and "||", tighter than IS TRUE, and indistinguishable from <predicate>
syntax like IS DISTINCT FROM and LIKE.

> 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.

Ah, quite right. SQL OVERLAPS takes various forms of two-column input, but
PostgreSQL OVERLAPS is more particular. I like your subsequent proposal to
remove OVERLAPS from the order of precedence.

Thanks,
nm