Re: v17 Possible Union All Bug

On Tue, Jan 23, 2024 at 4:51 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)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.