Re: BUG #17357: When working with the window_function, the Where Clause does not work properly! - Mailing list 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.