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