Re: Precedence of standard comparison operators

Attached is a draft patch to bring the precedence of comparison operators
and IS tests into line with the SQL standard.  I have not yet looked into
producing warnings for changes in parsing decisions; but I was gratified
to discover that this patch results in none, nada, zero changes in any
of our regression tests.  So that's at least some evidence that it may
not be a huge problem in practice.  Pending writing some code for warnings,
I thought I'd throw this up in case anyone wants to try it on applications
they have handy.

Credit where credit is due dept: this is mostly the work of Serge Rielau
of Salesforce.

            regards, tom lane

diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 4b81b08..c88e7d9 100644
*** a/doc/src/sgml/syntax.sgml
--- b/doc/src/sgml/syntax.sgml
*************** CAST ( '<replaceable>string</replaceable
*** 984,993 ****
      associativity of the operators in <productname>PostgreSQL</>.
      Most operators have the same precedence and are left-associative.
      The precedence and associativity of the operators is hard-wired
!     into the parser.  This can lead to non-intuitive behavior; for
!     example the Boolean operators <literal><</> and
!     <literal>></> have a different precedence than the Boolean
!     operators <literal><=</> and <literal>>=</>.  Also, you will
      sometimes need to add parentheses when using combinations of
      binary and unary operators.  For instance:
  <programlisting>
--- 984,994 ----
      associativity of the operators in <productname>PostgreSQL</>.
      Most operators have the same precedence and are left-associative.
      The precedence and associativity of the operators is hard-wired
!     into the parser.
!    </para>
!
!    <para>
!     You will
      sometimes need to add parentheses when using combinations of
      binary and unary operators.  For instance:
  <programlisting>
*************** SELECT (5 !) - 6;
*** 1063,1087 ****
        </row>

        <row>
!        <entry><token>IS</token></entry>
!        <entry></entry>
!        <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS NULL</>, etc</entry>
!       </row>
!
!       <row>
!        <entry><token>ISNULL</token></entry>
!        <entry></entry>
!        <entry>test for null</entry>
!       </row>
!
!       <row>
!        <entry><token>NOTNULL</token></entry>
!        <entry></entry>
!        <entry>test for not null</entry>
!       </row>
!
!       <row>
!        <entry>(any other)</entry>
         <entry>left</entry>
         <entry>all other native and user-defined operators</entry>
        </row>
--- 1064,1070 ----
        </row>

        <row>
!        <entry>(any other operator)</entry>
         <entry>left</entry>
         <entry>all other native and user-defined operators</entry>
        </row>
*************** SELECT (5 !) - 6;
*** 1111,1125 ****
        </row>

        <row>
!        <entry><token><</token> <token>></token></entry>
         <entry></entry>
!        <entry>less than, greater than</entry>
        </row>

        <row>
!        <entry><token>=</token></entry>
!        <entry>right</entry>
!        <entry>equality, assignment</entry>
        </row>

        <row>
--- 1094,1109 ----
        </row>

        <row>
!        <entry><token><</token> <token>></token> <token>=</token> <token><=</token> <token>>=</token>
<token><></token>
! </entry>
         <entry></entry>
!        <entry>comparison operators</entry>
        </row>

        <row>
!        <entry><token>IS</token> <token>ISNULL</token> <token>NOTNULL</token></entry>
!        <entry></entry>
!        <entry><literal>IS TRUE</>, <literal>IS FALSE</>, <literal>IS NULL</>, etc</entry>
        </row>

        <row>
*************** SELECT (5 !) - 6;
*** 1159,1165 ****
  SELECT 3 OPERATOR(pg_catalog.+) 4;
  </programlisting>
      the <literal>OPERATOR</> construct is taken to have the default precedence
!     shown in <xref linkend="sql-precedence-table"> for <quote>any other</> operator.  This is true no matter
      which specific operator appears inside <literal>OPERATOR()</>.
     </para>
    </sect2>
--- 1143,1150 ----
  SELECT 3 OPERATOR(pg_catalog.+) 4;
  </programlisting>
      the <literal>OPERATOR</> construct is taken to have the default precedence
!     shown in <xref linkend="sql-precedence-table"> for
!     <quote>any other operator</>.  This is true no matter
      which specific operator appears inside <literal>OPERATOR()</>.
     </para>
    </sect2>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 36dac29..f98158c 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static Node *makeRecursiveViewSelect(cha
*** 532,537 ****
--- 532,538 ----
  %token <str>    IDENT FCONST SCONST BCONST XCONST Op
  %token <ival>    ICONST PARAM
  %token            TYPECAST DOT_DOT COLON_EQUALS
+ %token            LESS_EQUALS GREATER_EQUALS NOT_EQUALS

  /*
   * If you want to make any keyword changes, update the keyword table in
*************** static Node *makeRecursiveViewSelect(cha
*** 645,652 ****
  %left        OR
  %left        AND
  %right        NOT
! %right        '='
! %nonassoc    '<' '>'
  %nonassoc    LIKE ILIKE SIMILAR
  %nonassoc    ESCAPE
  %nonassoc    OVERLAPS
--- 646,653 ----
  %left        OR
  %left        AND
  %right        NOT
! %nonassoc    IS ISNULL NOTNULL    /* IS sets precedence for IS NULL, etc */
! %nonassoc    '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS
  %nonassoc    LIKE ILIKE SIMILAR
  %nonassoc    ESCAPE
  %nonassoc    OVERLAPS
*************** static Node *makeRecursiveViewSelect(cha
*** 676,684 ****
  %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        '^'
--- 677,682 ----
*************** a_expr:        c_expr                                    { $$ = $1; }
*** 11202,11207 ****
--- 11200,11211 ----
                  { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, ">", $1, $3, @2); }
              | a_expr '=' a_expr
                  { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "=", $1, $3, @2); }
+             | a_expr LESS_EQUALS a_expr
+                 { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "<=", $1, $3, @2); }
+             | a_expr GREATER_EQUALS a_expr
+                 { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, ">=", $1, $3, @2); }
+             | a_expr NOT_EQUALS a_expr
+                 { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "<>", $1, $3, @2); }

              | a_expr qual_Op a_expr                %prec Op
                  { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, $3, @2); }
*************** b_expr:        c_expr
*** 11566,11571 ****
--- 11570,11581 ----
                  { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, ">", $1, $3, @2); }
              | b_expr '=' b_expr
                  { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "=", $1, $3, @2); }
+             | b_expr LESS_EQUALS b_expr
+                 { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "<=", $1, $3, @2); }
+             | b_expr GREATER_EQUALS b_expr
+                 { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, ">=", $1, $3, @2); }
+             | b_expr NOT_EQUALS b_expr
+                 { $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "<>", $1, $3, @2); }
              | b_expr qual_Op b_expr                %prec Op
                  { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, $3, @2); }
              | qual_Op b_expr                    %prec Op
*************** MathOp:         '+'                                    { $$ = "+"; }
*** 12485,12490 ****
--- 12495,12503 ----
              | '<'                                    { $$ = "<"; }
              | '>'                                    { $$ = ">"; }
              | '='                                    { $$ = "="; }
+             | LESS_EQUALS                            { $$ = "<="; }
+             | GREATER_EQUALS                        { $$ = ">="; }
+             | NOT_EQUALS                            { $$ = "<>"; }
          ;

  qual_Op:    Op
diff --git a/src/backend/parser/scan.l b/src/backend/parser/scan.l
index a78ce03..7ce7a47 100644
*** a/src/backend/parser/scan.l
--- b/src/backend/parser/scan.l
*************** ident_cont        [A-Za-z\200-\377_0-9\$]
*** 331,339 ****
--- 331,344 ----

  identifier        {ident_start}{ident_cont}*

+ /* Assorted special-case operators and operator-like tokens */
  typecast        "::"
  dot_dot            \.\.
  colon_equals    ":="
+ less_equals        "<="
+ greater_equals    ">="
+ less_greater    "<>"
+ not_equals        "!="

  /*
   * "self" is the set of chars that should be returned as single-character
*************** other            .
*** 808,813 ****
--- 813,840 ----
                      return COLON_EQUALS;
                  }

+ {less_equals}    {
+                     SET_YYLLOC();
+                     return LESS_EQUALS;
+                 }
+
+ {greater_equals} {
+                     SET_YYLLOC();
+                     return GREATER_EQUALS;
+                 }
+
+ {less_greater}    {
+                     /* We accept both "<>" and "!=" as meaning NOT_EQUALS */
+                     SET_YYLLOC();
+                     return NOT_EQUALS;
+                 }
+
+ {not_equals}    {
+                     /* We accept both "<>" and "!=" as meaning NOT_EQUALS */
+                     SET_YYLLOC();
+                     return NOT_EQUALS;
+                 }
+
  {self}            {
                      SET_YYLLOC();
                      return yytext[0];
*************** other            .
*** 885,895 ****
                      if (nchars >= NAMEDATALEN)
                          yyerror("operator too long");

!                     /* Convert "!=" operator to "<>" for compatibility */
!                     if (strcmp(yytext, "!=") == 0)
!                         yylval->str = pstrdup("<>");
!                     else
!                         yylval->str = pstrdup(yytext);
                      return Op;
                  }

--- 912,918 ----
                      if (nchars >= NAMEDATALEN)
                          yyerror("operator too long");

!                     yylval->str = pstrdup(yytext);
                      return Op;
                  }

diff --git a/src/bin/psql/psqlscan.l b/src/bin/psql/psqlscan.l
index fb3fa11..a37cd2c 100644
*** a/src/bin/psql/psqlscan.l
--- b/src/bin/psql/psqlscan.l
*************** ident_cont        [A-Za-z\200-\377_0-9\$]
*** 355,363 ****
--- 355,368 ----

  identifier        {ident_start}{ident_cont}*

+ /* Assorted special-case operators and operator-like tokens */
  typecast        "::"
  dot_dot            \.\.
  colon_equals    ":="
+ less_equals        "<="
+ greater_equals    ">="
+ less_greater    "<>"
+ not_equals        "!="

  /*
   * "self" is the set of chars that should be returned as single-character
*************** other            .
*** 669,674 ****
--- 674,695 ----
                      ECHO;
                  }

+ {less_equals}    {
+                     ECHO;
+                 }
+
+ {greater_equals} {
+                     ECHO;
+                 }
+
+ {less_greater}    {
+                     ECHO;
+                 }
+
+ {not_equals}    {
+                     ECHO;
+                 }
+
      /*
       * These rules are specific to psql --- they implement parenthesis
       * counting and detection of command-ending semicolon.  These must
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 506a313..761cfab 100644
*** a/src/pl/plpgsql/src/pl_gram.y
--- b/src/pl/plpgsql/src/pl_gram.y
*************** static    void            check_raise_parameters(PLp
*** 227,232 ****
--- 227,233 ----
  %token <str>    IDENT FCONST SCONST BCONST XCONST Op
  %token <ival>    ICONST PARAM
  %token            TYPECAST DOT_DOT COLON_EQUALS
+ %token            LESS_EQUALS GREATER_EQUALS NOT_EQUALS

  /*
   * Other tokens recognized by plpgsql's lexer interface layer (pl_scanner.c).