Message-ID: <a9440b72-91c3-4041-89ec-531de5c2a5bc@postgrespro.ru>
Date: Fri, 2 Feb 2024 23:24:29 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: v17 Possible Union All Bug
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>,
 Alexander Korotkov <akorotkov@postgresql.org>,
 PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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>
Content-Language: en-US
Organization: Postgres Professional
In-Reply-To: <c566aa61-b381-4420-ade9-24d0a01b71be@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/a9440b72-91c3-4041-89ec-531de5c2a5bc%40postgrespro.ru>
Precedence: bulk

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