The following bug has been logged on the website:
Bug reference: 13829
Logged by: Henrik Pauli
Email address: henrik.pauli@uhusystems.com
PostgreSQL version: 9.3.9
Operating system: Linux (Fedora)
Description:
As a result of a discussion on IRC, I'm writing a bug report here. Maybe I
should have sent it to -hackers, but I do not currently have access to that
mailing list.
The convention with exponentation is the following (according to Wikipedia):
"Without parentheses to modify the order of calculation, by convention the
order is top-down, not bottom-up" -- in programming terms, it means that
exponentation is by default right-associative.
bc, Perl, Python, PHP agree that:
3^3^3
7625597484987
3^(3^3)
7625597484987
(3^3)^3
19683
However, PostgreSQL -- as indeed mentioned in the docs -- considers the
paren-less version more like the one where the left side is parenthesised:
=> select 3^3^3 as no_paren, (3^3)^3 as left_paren, 3^(3^3) as right_paren;
no_paren | left_paren | right_paren
----------+------------+---------------
19683 | 19683 | 7625597484987
(1 row)
Which goes against the mathematics, and most programming languages'
convention.
The only place where it is possible to notice this, as far as I could see,
is the precedence table on
http://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE
but it does not highlight neither that this unorthodoxy is due to compliance
with the SQL standard (as is common on many other documentation pages) nor
does it make note of it any other highly visible way to decrease the chance
of oversight in the user.
Clearly, changing the behaviour can cause breakage in existing programs, but
that is not unprecedented as 9.5 has recently changed operator precedence.
Chances are though that those programs were already in the wrong, and those
that weren't have already been using parentheses to fix the unexpected
behaviour. It is also likely that this operation is very rarely used (since
when used it results in incredibly large numbers very easily), as such both
the wrong behaviour, and the breakage introduced by the fixes have low
impact.
Please consider fixing the associativity so it aligns with the convention,
or at least raise a warning in the documentation about possible pitfalls.