BUG #5841: rank()+1 fails, 1+rank() succeeds
The following bug has been logged online:
Bug reference: 5841
Logged by: Jeff Turner
Email address: jeff@biccard.com
PostgreSQL version: 9.0.1
Operating system: Ubuntu 10.10
Description: rank()+1 fails, 1+rank() succeeds
Details:
Given a table:
create table t (foo varchar, bar integer);
insert into t values ('a', 1), ('b', 2), ('c', 1);
This query succeeds:
select *, 1+rank() over (partition by bar) from t;
However, replace '1+rank()' with 'rank()+1' and it fails:
test=# select *, rank()+1 over (partition by bar) from t;
ERROR: syntax error at or near "over"
LINE 1: select *, rank()+1 over (partition by bar) from t;
Excerpts from Jeff Turner's message of lun ene 17 07:34:29 -0300 2011:
test=# select *, rank()+1 over (partition by bar) from t;
ERROR: syntax error at or near "over"
LINE 1: select *, rank()+1 over (partition by bar) from t;
The "over" stuff is part of the expression; you can't add the +1 in the
middle. This works:
select *, rank() over (partition by bar) + 1 from t;
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Thanks for clarifying. I've submitted a note on the interactive
version of the docs, and attached a small patch to make explicit that
a window function is followed _immediately_ by an OVER clause, as the
syntax[1]http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS indicates.
Regards,
Jeff
[1]: http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
Quoting Alvaro Herrera <alvherre@commandprompt.com>:
Show quoted text
Excerpts from Jeff Turner's message of lun ene 17 07:34:29 -0300 2011:
test=# select *, rank()+1 over (partition by bar) from t;
ERROR: syntax error at or near "over"
LINE 1: select *, rank()+1 over (partition by bar) from t;The "over" stuff is part of the expression; you can't add the +1 in the
middle. This works:select *, rank() over (partition by bar) + 1 from t;
--
Ãlvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachments:
doc_window_function.difftext/x-patch; charset=UTF-8; name=doc_window_function.diffDownload+3-3
On Tue, Jan 18, 2011 at 6:19 AM, <jeff@biccard.com> wrote:
Thanks for clarifying. I've submitted a note on the interactive version of
the docs, and attached a small patch to make explicit that a window function
is followed _immediately_ by an OVER clause, as the syntax[1] indicates.
I've committed the first hunk of this patch, as I think it's useful
and innocuous. The second half looks like bad grammar to me.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company