Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nvO7o-0005Vg-G9 for pgsql-bugs@arkaria.postgresql.org; Sun, 29 May 2022 18:58:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nvO7m-0004e1-ES for pgsql-bugs@arkaria.postgresql.org; Sun, 29 May 2022 18:58:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nvO7l-0004cm-Sh for pgsql-bugs@lists.postgresql.org; Sun, 29 May 2022 18:58:50 +0000 Received: from mail-pl1-x633.google.com ([2607:f8b0:4864:20::633]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nvO7e-0004bM-8X for pgsql-bugs@lists.postgresql.org; Sun, 29 May 2022 18:58:48 +0000 Received: by mail-pl1-x633.google.com with SMTP id n8so8525070plh.1 for ; Sun, 29 May 2022 11:58:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=datoris.com; s=google; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=XPOz5NO7iV0nWy/Qcs3ELf8JHVoZ5bwB4QQHfhjV0Zk=; b=gLp0hZcrwhn/crm+xgIlsHN0s5VTYG/O42hXcBWtoP4tGlG1MCvEUSrO/Mh8tLizM4 KY2ep2u5RQBWMd+h7A6IVDsg4aM6/wo66v7OWZgnyvB+2DLzJM4jekHVipJLl1IJIC8D e97LPrpgKPx5RdIzl8zK/F/ZubuzGGboT5dKZh2qh4OvSd2NW5EwKHPa6o+++AafMuQ6 TkGpgJ6Oo56ARn0lqeJ+dHNSIfVjC95QZHaNmHsXd8B04pRaQCTNHEJoTmcT1klSa1Jj 7R4JNgMXAw25cC+dMfud0x/d0Cnd91r/th9usljdTf0pwV8zvMDSdq1t6XXjNt8NH2oc nxeQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=XPOz5NO7iV0nWy/Qcs3ELf8JHVoZ5bwB4QQHfhjV0Zk=; b=GMJmExwhv/uc/kB4uBTLErhUppQCsor7/Q3Da+laPnoPxt5N8iBxmZVwgsu6RiiHqI CFQJfr+y/rd0ACwiJzvyAm5cLVQK3CMviSQX/VCQZ8iK8BvEfc6jqQEbGS6R/p4tcP6v 5+j1au4T7Ob+zhfhGsBtgF0DB+yJ3knUsgsBd38EZhPsfqzvJ+5wczNdUJqchWpnn38y NVogT9xLtHNabcH4DmGfJf+DcdQlysFk66ufva6eSXUTOR1D37DcJHVtj/RMTSdC2KzM MfxJZRfnkh3PVEfAVDFHMfb5xB42CQ42mcYLa8RMbG+W0kC9gYCh0RnuItoaSlVRnNX1 CTnA== X-Gm-Message-State: AOAM532RA2GxY4JuA8uQLu+U6cEBpSiuFWvlrWCHwEvByAJ7g+W/Mgr1 mC+gu/ZBejNIiQQbw50VYkc2dfWVLswbHiNFRs2r5A== X-Google-Smtp-Source: ABdhPJyPOoigf7Xibapw+4ung6Wio1wftKsGTcvn5yD2OeHRDLghgr/APjHtkHmjZt1hu1IjVjSbXN62xOiAl3tOcgg= X-Received: by 2002:a17:902:e803:b0:163:5074:c12b with SMTP id u3-20020a170902e80300b001635074c12bmr25128030plg.30.1653850720558; Sun, 29 May 2022 11:58:40 -0700 (PDT) MIME-Version: 1.0 References: <17502-281a7aaacfaa872a@postgresql.org> In-Reply-To: From: =?UTF-8?Q?Daniel_Farka=C5=A1?= Date: Sun, 29 May 2022 20:58:29 +0200 Message-ID: Subject: Re: BUG #17502: View based on window functions returns wrong results when queried To: Magnus Hagander Cc: pgsql-bugs@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000b5f50405e02b203c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b5f50405e02b203c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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) =3D 1 TH= EN 'metric_1' ELSE 'metric_2' END AS metric_name, CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) =3D 1 TH= EN 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) =3D 1 TH= EN 'metric_1' ELSE 'metric_2' END AS metric_name, CASE WHEN row_number() OVER(partition by dimension_1, dimension_2) =3D 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 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=C5=A1 >> 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 value= s. >> When I drop the view and create materialized view, all is good, I get al= l >> 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, compile= d >> 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 yea= rs > 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 > > --000000000000b5f50405e02b203c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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-mu= sl, 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 analyti= cs_table VALUES ('a1', 'b1', 'c1', 'd1'), (= 'a1', 'b2', 'c2', 'd2');

SELECT * FR= OM analytics_table;
dimension_1|dimension_2|metric_1|metric_2|
------= -----+-----------+--------+--------+
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b1 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 |c1 =C2=A0 =C2=A0 =C2=A0|d1 =C2=A0 =C2=A0 =C2= =A0|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |c2 = =C2=A0 =C2=A0 =C2=A0|d2 =C2=A0 =C2=A0 =C2=A0|

SELECT
dim= ension_1,
dimension_2,
row_number() OVER(partition by generate_series= (1,2) order by dimension_1, dimension_2) AS rownum,
row_number() OVER(pa= rtition by dimension_1, dimension_2) AS metricnum
FROM analytics_table O= RDER BY dimension_1, dimension_2;
dimension_1|dimension_2|rownum|metricnum|
-----------+-----------+-----= -+------+
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b1 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 1| =C2=A0 =C2=A0 1|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b1 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1| =C2=A0 =C2=A0 2|
a1 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |b2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 2|= =C2=A0 =C2=A0 1|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b2 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 2| =C2=A0 =C2=A0 2|

CREATE VIEW analytics_view AS
SELECT
dimension_= 1,
dimension_2,
row_number() OVER(partition by generate_series(1,2) o= rder by dimension_1, dimension_2) AS rownum,
CASE WHEN row_number() OVER= (partition by dimension_1, dimension_2) =3D 1=C2=A0THEN=C2=A0'metric_= 1'=C2=A0ELSE=C2=A0'metric_2' END AS metric_name,
CASE WHEN=C2=A0row_number() OVER(partition by dimension_1, dimension_2) =3D 1=C2= =A0THEN=C2=A0metric_1=C2=A0E= LSE=C2=A0metric_2=C2=A0END=C2=A0AS metr= ic_value
FROM analytics_table ORDER BY dimension_1, dimension_2;

= SELECT=C2=A0* FROM=C2=A0analytics_view;
dimension_1|dimension_2|rownum|metric_name|metric_value|
--------= ---+-----------+------+-----------+------------+
a1 =C2=A0 =C2=A0 =C2=A0= =C2=A0 |b1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1|metric_1 =C2=A0 |= c1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b= 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1|metric_2 =C2=A0 |d1 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b2 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 2|metric_1 =C2=A0 |c2 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b2 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 2|metric_2 =C2=A0 |d2 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|

SELECT=C2=A0met= ric_name FROM analytics_view;
metric_name|
-----------+
metric_1 =C2=A0 |
metric_1 =C2= =A0 |
metric_1 =C2=A0 |
metric_1 =C2=A0 |

CREATE= MATERIALIZED VIEW analytics_materialized_view AS
SELECT
dimension_1,=
dimension_2,
row_number() OVER(partition by generate_series(1,2) ord= er by dimension_1, dimension_2) AS rownum,
CASE WHEN=C2=A0row_number()= OVER(partition by dimension_1, dimension_2) =3D 1 THEN 'metric_1'= =C2=A0ELSE=C2=A0'metric_2'=C2=A0END=C2=A0AS metric_name,
CASE WHEN=C2=A0row_number() OVER(partition by dimension_1, dimension_2) =3D = 1=C2=A0THEN=C2=A0metric_1 ELSE metric_2=C2=A0END=C2=A0AS metric_value=
FROM analytics_table ORDER BY dimension_1, dimension_2;

SELECT * FROM=C2=A0analytics_materialized_view;
dimension_1|dimension_2|rownum|metric_name|metric_val= ue|
-----------+-----------+------+-----------+------------+
a1 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 |b1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1|= metric_1 =C2=A0 |c1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
a1 =C2=A0 =C2=A0= =C2=A0 =C2=A0 |b1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 1|metric_2 = =C2=A0 |d1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
a1 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 |b2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 2|metric_1 =C2=A0 |c= 2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
a1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |b2= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 2|metric_2 =C2=A0 |d2 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=
SELECT metric_name FROM=C2= =A0analytics_materialized_view;
metric_name|
-----------+
metric_1 =C2=A0 |
metric_2 =C2=A0 |metric_1 =C2=A0 |
metric_2 =C2=A0 |

For some analytics purposes I needed to transform one wide table with mul= tiple 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
Dat= oris

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&g= t; wrote:
The fo= llowing bug has been logged on the website:

Bug reference:=C2=A0 =C2=A0 =C2=A0 17502
Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Daniel Farka=C5=A1
Email address:=C2=A0 =C2=A0 =C2=A0 daniel.farkas@datoris.com
PostgreSQL version: 10.10
Operating system:=C2=A0 =C2=A0Linux
Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0

Hey,

Please be gentle, I've never been in contact with Postgres developers.<= br> In short, I've created a view, which has rather sketchy window function= s,
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.<= br> 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&#= 39;s
select, which is not something I would expect.
My current Postgres is PostgreSQL 10.10 on x86_64-pc-linux-musl, compiled b= y
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 behavi= or.

//Magnus

--000000000000b5f50405e02b203c--