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