Re: v17 Possible Union All Bug

On Tue, Jan 23, 2024 at 4:51 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> I appreciate this is a bit of a messy test case.  I'm willing to work on
> simplifying it further but figured I'd at least get confirmation of
> reproducibility and maybe someone will have an ah-ha! moment.
>
>
Decided to focus on simplifying the query first.  I figured this out:

 WITH cte_role_graph AS (
         SELECT leaf_role.oid,
            leaf_role.role_type,
            leaf_role.rolname,
            leaf_role.rolsuper,
            array_to_string(ARRAY(

                 SELECT 'false' where false
                 UNION ALL

                 SELECT format('%I from %s'::text, 'test',
string_agg('test', '---'::text
                           ORDER BY grant_instance.level,
grant_instance.grantor, grant_instance.grantor_path
                    ))

                   FROM unnest(leaf_role.memberof_groups) other(other)
                     JOIN pg_roles other_role ON other_role.oid =
other.other
                     JOIN rolegraph.role_relationship grant_instance ON
grant_instance.leaf_node = leaf_role.oid AND grant_instance.group_node =
other.other
                     JOIN pg_roles grant_role ON grant_role.oid =
grant_instance.grantor
                  GROUP BY other_role.rolname, grant_instance.via
            ), E'\n'::text) AS administration
           FROM rolegraph.role_graph_detail leaf_role
           where rolname ~ 'u6_green'
        )
select * from cte_role_graph;

Running this query against the previously supplied dump file on HEAD should
produce the broken result.  Simply commenting out the ORDER BY clause in
the string_agg causes the correct result to appear, even with the UNION ALL
present.  Removing the union all and leaving the order by likewise still
produces the correct result.

psql (17devel)
Type "help" for help.

postgres=# \i tmp3.sql
  oid  | role_type |      rolname       | rolsuper | administration
-------+-----------+--------------------+----------+----------------
 16405 | User      | u6_green_leader_su | f        | test from test+
       |           |                    |          | test from test+
       |           |                    |          | test from test+
       |           |                    |          | test from test+
       |           |                    |          | test from test
(1 row)

postgres=# \i tmp3.sql
  oid  | role_type |      rolname       | rolsuper |    administration
-------+-----------+--------------------+----------+-----------------------
 16405 | User      | u6_green_leader_su | f        | test from test       +
       |           |                    |          | test from test---test+
       |           |                    |          | test from test       +
       |           |                    |          | test from test
(1 row)

David J.