Обсуждение: 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] indicates. Regards, Jeff [1] http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS Quoting Alvaro Herrera <alvherre@commandprompt.com>: > 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 >
Вложения
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