v17 Possible Union All Bug
| От | David G. Johnston |
|---|---|
| Тема | v17 Possible Union All Bug |
| Дата | |
| Msg-id | CAKFQuwY3Ek=cLThgd8FdaSc5JRDVt0FaV00gMcWra+TAR4gGUw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: v17 Possible Union All Bug
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | pgsql-bugs |
Hey,
The attached pg_dumpall file creates some test roles and some views, two of which show the expected and problem behaviors. There is a lot going on beneath these views but suffice to say I've granted admin of g6c_service_manager_su to u6_green_leader_su twice, once with the bootstrap superuser as the grantor and once with the cr_admin role as the grantor. The query is supposed to notice that the otherwise identical grants have two different grantors and combine them into a single newline separated presentation. Note that both v16 examples below show this expected output as does the "working" view in v17. The "broken" view in v17 decides not to place them on separate lines.
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.
The only difference from the broken view to the working view is the entire first union all subquery block beginning with the " 'mou' || " string be prepended is removed. I.e., inside of the ARRAY there is no "union all" in the working version, there is one (two subqueries) in the broken version. Note that with this test data the "mou" subquery does not return any rows, all output rows are coming from the "mog" one.
Results on a clean v17 head build from today:
psql (17devel)
Type "help" for help.
postgres=# select * from rolegraph.role_graph_broken;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser +
| | | | mog of g6c_service_manager_su from cr_admin
(1 row)
postgres=# select * from rolegraph.role_graph_working;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | cr_admin +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser
(1 row)
Type "help" for help.
postgres=# select * from rolegraph.role_graph_broken;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser +
| | | | mog of g6c_service_manager_su from cr_admin
(1 row)
postgres=# select * from rolegraph.role_graph_working;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | cr_admin +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser
(1 row)
Results on a clean v16 stable build from today:
postgres=# select * from rolegraph.role_graph_working;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | cr_admin +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser
(1 row)
postgres=# select * from rolegraph.role_graph_broken;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | cr_admin +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser
(1 row)
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | cr_admin +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser
(1 row)
postgres=# select * from rolegraph.role_graph_broken;
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+-----------------------------------------------
16390 | User | u6_green_leader_su | f | mog of g6a_fixedops_manager_su from superuser+
| | | | mog of g6c_service_manager_su from superuser +
| | | | cr_admin +
| | | | mog of g6d_service_advisor_su from superuser +
| | | | mog of g6e_service_tech_su from superuser
(1 row)
As an additional observation - I could swear I ran this last week on v17 without this particular error showing up so it seems like a recent thing. Might end up giving me a chance to do my first git bisect...
I'm also attaching the explain analyze plans for the collapse (broken) and no-collapse cases, from the v17 build.
David J.
Вложения
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: Misleading/inaccurate error message from pg_basebackup
Следующее
От: PG Bug reporting formДата:
Сообщение: BUG #18307: system columns does not support using join