Re: BUG #17502: View based on window functions returns wrong results when queried

Hey,

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 |

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|rownum|metricnum|
-----------+-----------+------+------+
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;
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 |

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;

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 |

SELECT metric_name FROM analytics_materialized_view;
metric_name|
-----------+
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(at)hagander(dot)net> wrote:

>
> On Sun, May 29, 2022 at 4:20 PM PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 17502
>> Logged by: Daniel Farkaš
>> Email address: daniel(dot)farkas(at)datoris(dot)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
>
>