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

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> |
<intervalvalue 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> |
<subtypetreatment> | <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> |
<uniquepredicate> | <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