Re: BUG #17502: View based on window functions returns wrong results when queried
| От | Daniel Farkaš |
|---|---|
| Тема | Re: BUG #17502: View based on window functions returns wrong results when queried |
| Дата | |
| Msg-id | CAGckUK2GLF=d9J5ErEWgK5x8ECqCw4equnq3jEzrqtfJw+iHYw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: BUG #17502: View based on window functions returns wrong results when queried (Magnus Hagander <magnus@hagander.net>) |
| Ответы |
Re: BUG #17502: View based on window functions returns wrong results when queried
(David Rowley <dgrowleyml@gmail.com>)
|
| Список | pgsql-bugs |
Hey,
SELECT metric_name FROM analytics_view;
Thanks for the response.
I was able to replicate in 10.21.
Here is how:
SELECT version();
PostgreSQL 10.21 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
CREATE TABLE analytics_table (dimension_1 VARCHAR, dimension_2 VARCHAR, metric_1 VARCHAR, metric_2 VARCHAR);
INSERT INTO analytics_table VALUES ('a1', 'b1', 'c1', 'd1'), ('a1', 'b2', 'c2', 'd2');
SELECT * FROM analytics_table;
dimension_1|dimension_2|metric_1|metric_2|
-----------+-----------+--------+--------+
a1 |b1 |c1 |d1 |
a1 |b2 |c2 |d2 |
INSERT INTO analytics_table VALUES ('a1', 'b1', 'c1', 'd1'), ('a1', 'b2', 'c2', 'd2');
SELECT * FROM analytics_table;
dimension_1|dimension_2|metric_1|metric_2|
-----------+-----------+--------+--------+
a1 |b1 |c1 |d1 |
a1 |b2 |c2 |d2 |
SELECT
dimension_1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) order by dimension_1, dimension_2) AS rownum,
row_number() OVER(partition by dimension_1, dimension_2) AS metricnum
FROM analytics_table ORDER BY dimension_1, dimension_2;
dimension_1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) order by dimension_1, dimension_2) AS rownum,
row_number() OVER(partition by dimension_1, dimension_2) AS metricnum
FROM analytics_table ORDER BY dimension_1, dimension_2;
dimension_1|dimension_2|rownum|metricnum|
-----------+-----------+------+------+
a1 |b1 | 1| 1|
a1 |b1 | 1| 2|
a1 |b2 | 2| 1|
a1 |b2 | 2| 2|
-----------+-----------+------+------+
a1 |b1 | 1| 1|
a1 |b1 | 1| 2|
a1 |b2 | 2| 1|
a1 |b2 | 2| 2|
CREATE VIEW analytics_view AS
SELECT
dimension_1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) order by dimension_1, dimension_2) AS rownum,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN 'metric_1' ELSE 'metric_2' END AS metric_name,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN metric_1 ELSE metric_2 END AS metric_value
FROM analytics_table ORDER BY dimension_1, dimension_2;
SELECT * FROM analytics_view;
SELECT
dimension_1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) order by dimension_1, dimension_2) AS rownum,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN 'metric_1' ELSE 'metric_2' END AS metric_name,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN metric_1 ELSE metric_2 END AS metric_value
FROM analytics_table ORDER BY dimension_1, dimension_2;
SELECT * FROM analytics_view;
dimension_1|dimension_2|rownum|metric_name|metric_value|
-----------+-----------+------+-----------+------------+
a1 |b1 | 1|metric_1 |c1 |
a1 |b1 | 1|metric_2 |d1 |
a1 |b2 | 2|metric_1 |c2 |
a1 |b2 | 2|metric_2 |d2 |
-----------+-----------+------+-----------+------------+
a1 |b1 | 1|metric_1 |c1 |
a1 |b1 | 1|metric_2 |d1 |
a1 |b2 | 2|metric_1 |c2 |
a1 |b2 | 2|metric_2 |d2 |
SELECT metric_name FROM analytics_view;
metric_name|
-----------+
metric_1 |
metric_1 |
metric_1 |
metric_1 |
CREATE MATERIALIZED VIEW analytics_materialized_view AS
SELECT
dimension_1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) order by dimension_1, dimension_2) AS rownum,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN 'metric_1' ELSE 'metric_2' END AS metric_name,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN metric_1 ELSE metric_2 END AS metric_value
FROM analytics_table ORDER BY dimension_1, dimension_2;
-----------+
metric_1 |
metric_1 |
metric_1 |
metric_1 |
CREATE MATERIALIZED VIEW analytics_materialized_view AS
SELECT
dimension_1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) order by dimension_1, dimension_2) AS rownum,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN 'metric_1' ELSE 'metric_2' END AS metric_name,
CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) = 1 THEN metric_1 ELSE metric_2 END AS metric_value
FROM analytics_table ORDER BY dimension_1, dimension_2;
SELECT * FROM analytics_materialized_view;
dimension_1|dimension_2|rownum|metric_name|metric_value|
-----------+-----------+------+-----------+------------+
a1 |b1 | 1|metric_1 |c1 |
a1 |b1 | 1|metric_2 |d1 |
a1 |b2 | 2|metric_1 |c2 |
a1 |b2 | 2|metric_2 |d2 |
-----------+-----------+------+-----------+------------+
a1 |b1 | 1|metric_1 |c1 |
a1 |b1 | 1|metric_2 |d1 |
a1 |b2 | 2|metric_1 |c2 |
a1 |b2 | 2|metric_2 |d2 |
SELECT metric_name FROM analytics_materialized_view;
metric_name|
-----------+
metric_1 |
metric_2 |
metric_1 |
metric_2 |
-----------+
metric_1 |
metric_2 |
metric_1 |
metric_2 |
For some analytics purposes I needed to transform one wide table with multiple metrics into a metric_name/metric_value pairs stored as separate rows. That's why I did all this. I guess the reason and the method are not important, the fact that the view gives different results does look like a bug.
Cheers,
Daniel Farkas
Datoris
On Sun, May 29, 2022 at 4:22 PM Magnus Hagander <magnus@hagander.net> wrote:
On Sun, May 29, 2022 at 4:20 PM PG Bug reporting form <noreply@postgresql.org> wrote:The following bug has been logged on the website:
Bug reference: 17502
Logged by: Daniel Farkaš
Email address: daniel.farkas@datoris.com
PostgreSQL version: 10.10
Operating system: Linux
Description:
Hey,
Please be gentle, I've never been in contact with Postgres developers.
In short, I've created a view, which has rather sketchy window functions,
but it gives me results I need.
When I do select * on it, it gives me what I expect. One of the columns has
five distinct values.
But when I do group by on that column, it gives me only one of the values.
When I drop the view and create materialized view, all is good, I get all
five values.
My guess is that some parts of the inner select are affecting outer, view's
select, which is not something I would expect.
My current Postgres is PostgreSQL 10.10 on x86_64-pc-linux-musl, compiled by
gcc (Alpine 8.3.0) 8.3.0, 64-bit.
If you think this is worth investigating further, I will try composing a
simpler example, and test it in a more recent Postgres version.
Maybe it's a known limitation I'm not aware of.
Let me know what you think.Please see if you can reproduce this on a current version of PostgreSQL 10, which is 10.21. Version 10.10 is lacking more than two and a half years worth of bugfixes.If you can then yes, try to put together a simpler example, because it certainly does not sound like correct behavior.//Magnus
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Magnus HaganderДата:
Сообщение: Re: BUG #17502: View based on window functions returns wrong results when queried
Следующее
От: Tom LaneДата:
Сообщение: Re: psql 15beta1 does not print notices on the console until transaction completes