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.