Re: Precedence of standard comparison operators

On Thu, Feb 19, 2015 at 10:48:34AM -0500, Tom Lane wrote:
> To wit, that the precedence of <= >= and <> is neither sane nor standards
> compliant.

> I claim that this behavior is contrary to spec as well as being
> unintuitive. Following the grammar productions in SQL99:

Between 1999 and 2006, SQL changed its representation of the grammar in this
area; I have appended to this message some of the key productions as of 2013.
I did not notice a semantic change, though.

> We have that right for = < > but not for the other three standard-mandated
> comparison operators. I think we should change the grammar so that all
> six act like < > do now, that is, they should have %nonassoc precedence
> just above NOT.
>
> Another thought, looking at this closely, is that we have the precedence
> of IS tests (IS NOT NULL etc) wrong as well: they should bind less tightly
> than user-defined ops, not more so.

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 attempted to catalog the diverse precedence changes in commit c6b3c93:

> @@ -647,13 +654,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
> %left OR
> %left AND
> %right NOT
> -%right '='
> -%nonassoc '<' '>'
> -%nonassoc LIKE ILIKE SIMILAR
> -%nonassoc ESCAPE
> +%nonassoc IS ISNULL NOTNULL /* IS sets precedence for IS NULL, etc */
> +%nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS
> +%nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA
> +%nonassoc ESCAPE /* ESCAPE must be just above LIKE/ILIKE/SIMILAR */
> %nonassoc OVERLAPS
> -%nonassoc BETWEEN
> -%nonassoc IN_P
> %left POSTFIXOP /* dummy for postfix Op rules */
> /*
> * To support target_el without AS, we must give IDENT an explicit priority
> @@ -678,9 +683,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
> %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */
> %nonassoc IDENT NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING
> %left Op OPERATOR /* multi-character ops and user-defined operators */
> -%nonassoc NOTNULL
> -%nonassoc ISNULL
> -%nonassoc IS /* sets precedence for IS NULL, etc */
> %left '+' '-'
> %left '*' '/' '%'
> %left '^'

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

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.

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

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

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

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

> 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). I've been unable to explain (5) and (6). 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

Thanks,
nm

<comparison predicate> ::=
<row value predicand> <comparison predicate part 2>

<comparison predicate part 2> ::=
<comp op> <row value predicand>

<row value predicand> ::=
<row value special case>
| <row value constructor predicand>

# Syntax Rules
# 1) The declared type of a <row value special case> shall be a row type.
<row value special case> ::=
<nonparenthesized value expression primary>

# Things with precedence higher than comparison.
<row value constructor predicand> ::=
<common value expression>
| <boolean predicand>
| <explicit row value constructor>

# numeric: addition, multiplication
# string: concat, collate clause
# datetime: addition, AT TIME ZONE
# interval: addition, division
# UDT: <value expression primary>
# reference: <value expression primary>
# collection: array/multiset
<common value expression> ::=
<numeric value expression>
| <string value expression>
| <datetime value expression>
| <interval value expression>
| <user-defined type value expression>
| <reference value expression>
| <collection value expression>

<boolean predicand> ::=
<parenthesized boolean value expression>
| <nonparenthesized value expression primary>

<parenthesized boolean value expression> ::=
<left paren> <boolean value expression> <right paren>

# Things unambiguous without parens.
<nonparenthesized value expression primary> ::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <window function>
| <scalar subquery>
| <case expression>
| <cast specification>
| <field reference>
| <subtype treatment>
| <method invocation>
| <static method invocation>
| <new specification>
| <attribute or method reference>
| <reference resolution>
| <collection value constructor>
| <array element reference>
| <multiset element reference>
| <next value expression>
| <routine invocation>
| <row pattern navigation operation>

<boolean value expression> ::=
<boolean term>
| <boolean value expression> OR <boolean term>

<boolean term> ::=
<boolean factor>
| <boolean term> AND <boolean factor>

<boolean factor> ::=
[ NOT ] <boolean test>

<boolean test> ::=
<boolean primary> [ IS [ NOT ] <truth value> ]

<boolean primary> ::=
<predicate>
| <boolean predicand>

<truth value> ::=
TRUE
| FALSE
| UNKNOWN

# Things with precedence equal to comparison.
<predicate> ::=
<comparison predicate>
| <between predicate>
| <in predicate>
| <like predicate>
| <similar predicate>
| <regex like predicate>
| <null predicate>
| <quantified comparison predicate>
| <exists predicate>
| <unique predicate>
| <normalized predicate>
| <match predicate>
| <overlaps predicate>
| <distinct predicate>
| <member predicate>
| <submultiset predicate>
| <set predicate>
| <type predicate>
| <period predicate>

<null predicate> ::=
<row value predicand> <null predicate part 2>

<null predicate part 2> ::=
IS [ NOT ] NULL