Re: v17 Possible Union All Bug - Mailing list pgsql-bugs

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.