From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jeremyevans0@gmail.com
Subject: BUG #17495: Regression in 15beta1 when filtering subquery including row_number window function
Date: Tue, 24 May 2022 16:17:35 +0000
Message-ID: <17495-7ffe2fa0b261b9fa@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17495
Logged by: Jeremy Evans
Email address: jeremyevans0@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: OpenBSD 7.1/amd64
Description:
From testing with 15beta1, I think there is a regression in filtering a
subquery using the result of the row_number window function. Here is the
code:
CREATE TEMPORARY TABLE artists (id integer PRIMARY KEY, name text);
CREATE TEMPORARY TABLE albums (id integer PRIMARY KEY, name text, artist_id
integer REFERENCES artists);
CREATE TEMPORARY TABLE tags (id integer PRIMARY KEY, name text);
CREATE TEMPORARY TABLE albums_tags (album_id integer REFERENCES albums,
tag_id integer REFERENCES tags);
INSERT INTO artists (id, name) VALUES (1, 'Ar'), (2, 'Ar2');
INSERT INTO albums (id, name, artist_id) VALUES (1, 'Al', 1), (2, 'Al2',
2);
INSERT INTO tags (id, name) VALUES (1, 'T'), (2, 'U'), (3, 'V'), (4,
'T2');
INSERT INTO albums_tags (album_id, tag_id) VALUES (1, 1), (1, 2), (1, 3),
(2, 4);
SELECT albums.artist_id AS b, tags.id AS c, row_number() OVER (PARTITION BY
albums.artist_id ORDER BY tags.name) AS x_sequel_row_number_x FROM tags
INNER JOIN albums_tags ON (albums_tags.tag_id = tags.id) INNER JOIN albums
ON (albums.id = albums_tags.album_id);
-- Regression in 15beta1, includes (1, 3) even though x_sequel_row_number_x
= 3 for that row
SELECT b, c FROM (SELECT albums.artist_id AS b, tags.id AS c, row_number()
OVER (PARTITION BY albums.artist_id ORDER BY tags.name) AS
x_sequel_row_number_x FROM tags INNER JOIN albums_tags ON
(albums_tags.tag_id = tags.id) INNER JOIN albums ON (albums.id =
albums_tags.album_id)) AS t1 WHERE (x_sequel_row_number_x <= 2);
Results From PostgreSQL 8.4-14:
b | c | x_sequel_row_number_x
---+---+-----------------------
1 | 1 | 1
1 | 2 | 2
1 | 3 | 3
2 | 4 | 1
(4 rows)
b | c
---+---
1 | 1
1 | 2
2 | 4
(3 rows)
Results on PostgreSQL 15beta1:
b | c | x_sequel_row_number_x
---+---+-----------------------
1 | 1 | 1
1 | 2 | 2
1 | 3 | 3
2 | 4 | 1
(4 rows)
b | c
---+---
1 | 1
1 | 2
1 | 3
2 | 4
(4 rows)
This was discovered by the tests for Sequel, a Ruby database access
library.
Thanks,
Jeremy
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, jeremyevans0@gmail.com
Subject: Re: BUG #17495: Regression in 15beta1 when filtering subquery including row_number window function
In-Reply-To: <17495-7ffe2fa0b261b9fa@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