Re: Precedence of standard comparison operators - Mailing list pgsql-hackers

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