Re: v17 Possible Union All Bug

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