From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: muhammadxonnajimov@outlook.com
Subject: BUG #17357: When working with the window_function, the Where Clause does not work properly!
Date: Fri, 07 Jan 2022 00:51:40 +0000
Message-ID: <17357-02bf57d4d89bf972@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, muhammadxonnajimov@outlook.com
Subject: Re: BUG #17357: When working with the window_function, the Where Clause does not work properly!
In-Reply-To: <17357-02bf57d4d89bf972@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox