BUG #17357: When working with the window_function, the Where Clause does not work properly!

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: muhammadxonnajimov(at)outlook(dot)com
Subject: BUG #17357: When working with the window_function, the Where Clause does not work properly!
Date: 2022-01-07 00:51:40
Message-ID: 17357-02bf57d4d89bf972@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17357
Logged by: Muhammadxon Najimov
Email address: muhammadxonnajimov(at)outlook(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2022-01-07 01:30:01 Re: Possible bug report
Previous Message Milos Musicki 2022-01-06 22:58:31 Possible bug report