public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniel Farkaš <[email protected]>
To: Magnus Hagander <[email protected]>
Cc: [email protected]
Subject: Re: BUG #17502: View based on window functions returns wrong results when queried
Date: Sun, 29 May 2022 20:58:29 +0200
Message-ID: <CAGckUK2GLF=d9J5ErEWgK5x8ECqCw4equnq3jEzrqtfJw+iHYw@mail.gmail.com> (raw)
In-Reply-To: <CABUevEz7t3OWxaSSVSHOxa5KcYioOk=yRwoL3iMViyZQ8m2wAw@mail.gmail.com>
References: <[email protected]>
<CABUevEz7t3OWxaSSVSHOxa5KcYioOk=yRwoL3iMViyZQ8m2wAw@mail.gmail.com>
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 <[email protected]> wrote:
>
> On Sun, May 29, 2022 at 4:20 PM PG Bug reporting form <
> [email protected]> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 17502
>> Logged by: Daniel Farkaš
>> Email address: [email protected]
>> 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
>
>
view thread (18+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #17502: View based on window functions returns wrong results when queried
In-Reply-To: <CAGckUK2GLF=d9J5ErEWgK5x8ECqCw4equnq3jEzrqtfJw+iHYw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox