From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Alexander Korotkov <akorotkov@postgresql.org>
Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
Subject: Re: v17 Possible Union All Bug
Date: Fri, 2 Feb 2024 23:24:29 +0700
Message-ID: <a9440b72-91c3-4041-89ec-531de5c2a5bc@postgrespro.ru> (raw)
In-Reply-To: <c566aa61-b381-4420-ade9-24d0a01b71be@postgrespro.ru>
References: <CAKFQuwY3Ek=cLThgd8FdaSc5JRDVt0FaV00gMcWra+TAR4gGUw@mail.gmail.com>
<CAKFQuwZKMbccEbjfLmpaBmD+heXRa-Cmf+XOKRe=mOxc9HYpqQ@mail.gmail.com>
<CAApHDvoJ21egQX=Mf015PBVY_9AoPhrdw-3Xpxr+Re_UjvnFTQ@mail.gmail.com>
<CAApHDvqGS=y2EgzLLJLBqPu0Cms9kecVs7H3ZG2pz9ofL9r3zg@mail.gmail.com>
<CAKFQuwaahSXUgT1Vpzm_L8YY3g=j8Hc0u0UTeXkNHHyff77Bcw@mail.gmail.com>
<CAKFQuwbp8cFu_w88cD8M9s6YppnBL1KkJOC9ht8E7MLscUTqvQ@mail.gmail.com>
<CAPpHfds_6Bk2H2FE_AxPTQ7A4cTWXCFVyXaX2TA4t5BUb-q46A@mail.gmail.com>
<41499cc3-31a1-4c9e-85d6-45e6d901ea6a@postgrespro.ru>
<c566aa61-b381-4420-ade9-24d0a01b71be@postgrespro.ru>
On 1/2/2024 16:53, Andrei Lepikhov wrote:
> On 1/2/2024 11:06, Andrei Lepikhov wrote:
>>> Thank you for noticing. I'm investigating this.
>> Very curious bug. I simplified the test a bit (see in attachment), but
>> still can't replace system tables, like pg_authid, with a plain table.
>> Will try further.
> Just for speedup the bug scrutiny - new replay script attached.
A bit closer to the end. The symptom of the problem in incorrect order
of the columns in IncrementalSort, look:
-> GroupAggregate (actual time=1.136..1.157 rows=5 loops=1)
Output: format('%I from %s'::text, other_role.rolname,...
Group Key: grant_instance.via, other_role.rolname
-> Incremental Sort (actual time=1.098..1.102 rows=5 loops=1)
Output: other_role.rolname, grant_instance.via,...
Sort Key: grant_instance.grantor, other_role.rolname,...
Presorted Key: grant_instance.grantor
-> Merge Join (rows=5 loops=1)
Output: other_role.rolname, grant_instance.via,...
Merge Cond: (grant_role.oid = grant_instance.grantor)
Correct variant (without changing grouping order):
-> GroupAggregate (actual time=0.638..0.655 rows=4 loops=1)
Output: format('%I from %s'::text, other_role.rolname, ...
Group Key: other_role.rolname, grant_instance.via
-> Sort (actual time=0.626..0.630 rows=5 loops=1)
Output: other_role.rolname, grant_instance.via, ...
Sort Key: other_role.rolname, grant_instance.via, ...
-> Merge Join (rows=5 loops=1)
Output: other_role.rolname, grant_instance.via, ...
Merge Cond: (grant_role.oid = grant_instance.grantor)
But it is only a symptom. I can fix it easily, but what is the source?
As I see, we have the same value of sortref for the grouping column
other_role.rolname and for EquivalenceClass "grant_role.oid =
grant_instance.grantor".
We create sortref for other_role.rolname and grant_instance.via in
adjust_group_pathkeys_for_groupagg, because aggregate string_agg() in
the aggref->aggorder list contains both these columns.
I don't see ORDER BY for these columns in the query.
So Why is it happened? May it be a core bug?
--
regards,
Andrei Lepikhov
Postgres Professional
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: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, aekorotkov@gmail.com, david.g.johnston@gmail.com, dgrowleyml@gmail.com, akorotkov@postgresql.org, pgsql-bugs@lists.postgresql.org
Subject: Re: v17 Possible Union All Bug
In-Reply-To: <a9440b72-91c3-4041-89ec-531de5c2a5bc@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox