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!  (Pantelis Theodosiou <ypercube@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Milos Musicki
Дата:
Сообщение: Possible bug report
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Possible bug report