pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17357: When working with the window_function, the Where Clause does not work properly!
2+ messages / 2 participants
[nested] [flat]
* BUG #17357: When working with the window_function, the Where Clause does not work properly!
@ 2022-01-07 00:51 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-01-07 00:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: muhammadxonnajimov@outlook.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17357: When working with the window_function, the Where Clause does not work properly!
@ 2022-01-07 11:39 Pantelis Theodosiou <ypercube@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Pantelis Theodosiou @ 2022-01-07 11:39 UTC (permalink / raw)
To: muhammadxonnajimov@outlook.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Jan 7, 2022 at 11:06 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> 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.
>
This is not a bug.
Your ORDER BY inside the window function is not deterministic:
over(
partition by priority order by priority desc
)
The rows with same priority are ordered based on the (same) priority value
so the optimizer is free to choose any order to assign row numbers. In one
query it chooses to some order, in the second it chooses a different one.
Try using a deterministic ordering and you'll get consistent results, eg:
over(
partition by priority order by id desc
)
or
over(
partition by priority order by id
)
Best regards
Pantelis Theodosiou
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-01-07 11:39 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-07 00:51 BUG #17357: When working with the window_function, the Where Clause does not work properly! PG Bug reporting form <noreply@postgresql.org>
2022-01-07 11:39 ` Pantelis Theodosiou <ypercube@gmail.com>
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