From c6b3c939b7e0f1d35f4ed4996e71420a993810d2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 11 Mar 2015 13:22:52 -0400 Subject: Make operator precedence follow the SQL standard more closely. While the SQL standard is pretty vague on the overall topic of operator precedence (because it never presents a unified BNF for all expressions), it does seem reasonable to conclude from the spec for that OR has the lowest precedence, then AND, then NOT, then IS tests, then the six standard comparison operators, then everything else (since any non-boolean operator in a WHERE clause would need to be an argument of one of these). We were only sort of on board with that: most notably, while "<" ">" and "=" had properly low precedence, "<=" ">=" and "<>" were treated as generic operators and so had significantly higher precedence. And "IS" tests were even higher precedence than those, which is very clearly wrong per spec. Another problem was that "foo NOT SOMETHING bar" constructs, such as "x NOT LIKE y", were treated inconsistently because of a bison implementation artifact: they had the documented precedence with respect to operators to their right, but behaved like NOT (i.e., very low priority) with respect to operators to their left. Fixing the precedence issues is just a small matter of rearranging the precedence declarations in gram.y, except for the NOT problem, which requires adding an additional lookahead case in base_yylex() so that we can attach a different token precedence to NOT LIKE and allied two-word operators. The bulk of this patch is not the bug fix per se, but adding logic to parse_expr.c to allow giving warnings if an expression has changed meaning because of these precedence changes. These warnings are off by default and are enabled by the new GUC operator_precedence_warning. It's believed that very few applications will be affected by these changes, but it was agreed that a warning mechanism is essential to help debug any that are. --- doc/src/sgml/config.sgml | 23 +++++++++++++ doc/src/sgml/syntax.sgml | 84 +++++++++++++++++++++++------------------------- 2 files changed, 63 insertions(+), 44 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 07214bfd76a..d0f43c64af3 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6816,6 +6816,29 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' + + operator_precedence_warning (boolean) + + operator_precedence_warning configuration parameter + + + + + When on, the parser will emit a warning for any construct that might + have changed meanings since PostgreSQL 9.4 as a result + of changes in operator precedence. This is useful for auditing + applications to see if precedence changes have broken anything; but it + is not meant to be kept turned on in production, since it will warn + about some perfectly valid, standard-compliant SQL code. + The default is off. + + + + See for more information. + + + + quote_all_identifiers (boolean) diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 8283e252582..ff2c3e2b9a3 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -984,10 +984,11 @@ CAST ( 'string' AS type ) associativity of the operators in 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 < and - > have a different precedence than the Boolean - operators <= and >=. Also, you will + into the parser. + + + + You will sometimes need to add parentheses when using combinations of binary and unary operators. For instance: @@ -1008,7 +1009,7 @@ SELECT (5 !) - 6; - Operator Precedence (decreasing) + Operator Precedence (highest to lowest) @@ -1063,41 +1064,11 @@ SELECT (5 !) - 6; - IS - - IS TRUE, IS FALSE, IS NULL, etc - - - - ISNULL - - test for null - - - - NOTNULL - - test for not null - - - - (any other) + (any other operator) left all other native and user-defined operators - - IN - - set membership - - - - BETWEEN - - range containment - - OVERLAPS @@ -1105,21 +1076,23 @@ SELECT (5 !) - 6; - LIKE ILIKE SIMILAR + BETWEEN IN LIKE ILIKE SIMILAR - string pattern matching + range containment, set membership, string matching - < > + < > = <= >= <> + - less than, greater than + comparison operators - = - right - equality, assignment + IS ISNULL NOTNULL + + IS TRUE, IS FALSE, IS + NULL, IS DISTINCT FROM, etc @@ -1159,9 +1132,32 @@ SELECT (5 !) - 6; SELECT 3 OPERATOR(pg_catalog.+) 4; the OPERATOR construct is taken to have the default precedence - shown in for any other operator. This is true no matter + shown in for + any other operator. This is true no matter which specific operator appears inside OPERATOR(). + + + + PostgreSQL versions before 9.5 used slightly different + operator precedence rules. In particular, <= + >= and <> used to be treated as + generic operators; IS tests used to have higher priority; + and NOT BETWEEN and related constructs acted inconsistently, + being taken in some cases as having the precedence of NOT + rather than BETWEEN. These rules were changed for better + compliance with the SQL standard and to reduce confusion from + inconsistent treatment of logically equivalent constructs. In most + cases, these changes will result in no behavioral change, or perhaps + in no such operator failures which can be resolved by adding + parentheses. However there are corner cases in which a query might + change behavior without any parsing error being reported. If you are + concerned about whether these changes have silently broken something, + you can test your application with the configuration + parameter turned on + to see if any warnings are logged. + + -- cgit v1.2.3