BUG #17357: When working with the window_function, the Where Clause does not work properly!
От | PG Bug reporting form |
---|---|
Тема | BUG #17357: When working with the window_function, the Where Clause does not work properly! |
Дата | |
Msg-id | 17357-02bf57d4d89bf972@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17357: When working with the window_function, the Where Clause does not work properly!
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17357 Logged by: Muhammadxon Najimov Email address: muhammadxonnajimov@outlook.com PostgreSQL version: 14.1 Operating system: Fedora 35 (Workstation Edition) Description: Hi, 1. I created table like this: create table elements ( id serial primary key, priority smallint default 0 ); // CREATE TABLE [OK] 2. And insterted some rows: insert into elements (priority) values (1), (2), (3), (0), (0), (3), (2), (1), (0), (0), (0), (0), (0), (0), (0), (1), (1), (2), (2), (3) ; // INSERT 0 20 [OK] 3. Wrote Query select *, row_number() over( partition by priority order by priority desc ) as n from elements ; 4. and the result was as follows: // (20 rows) [OK] All is well. then I took the result to a virtual table: select * from ( select *, row_number() over( partition by priority order by priority desc ) as n from elements ) as tmp ; // (20 rows) [OK] The result is the same, but here WHERE clause is not working properly: select * from ( select *, row_number() over( partition by priority order by priority desc ) as n from elements ) as tmp WHERE n = 1 AND priority = 3 ; Is that logical? if you look at the picture above, the id number of the record with n = 1 and pr = 3 is not 3. Ok priority equals to 3 but n not equals 1.
В списке pgsql-bugs по дате отправления: