pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedv17 Possible Union All Bug
13+ messages / 4 participants
[nested] [flat]
* v17 Possible Union All Bug
@ 2024-01-23 23:51 David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2024-01-23 23:51 UTC (permalink / raw)
To: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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)
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)
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.
Attachments:
no-collapse.txt (text/plain, 26.6K) download
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=217.06..334.09 rows=1 width=133) (actual time=0.972..0.990 rows=1 loops=1)
-> Nested Loop (cost=162.73..163.99 rows=1 width=134) (actual time=0.472..0.484 rows=1 loops=1)
-> Nested Loop (cost=108.55..109.77 rows=1 width=134) (actual time=0.345..0.355 rows=1 loops=1)
-> Nested Loop (cost=54.20..55.41 rows=1 width=102) (actual time=0.190..0.199 rows=1 loops=1)
-> Seq Scan on pg_authid (cost=0.00..1.19 rows=1 width=70) (actual time=0.020..0.027 rows=1 loops=1)
Filter: (rolname ~ 'u6_green'::text)
Rows Removed by Filter: 21
-> Aggregate (cost=54.20..54.21 rows=1 width=32) (actual time=0.168..0.170 rows=1 loops=1)
-> Sort (cost=54.20..54.20 rows=1 width=4) (actual time=0.167..0.169 rows=0 loops=1)
Sort Key: cte_role_relationship.group_node
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=51.88..54.19 rows=1 width=4) (actual time=0.165..0.166 rows=0 loops=1)
Join Filter: (pg_authid_1.oid = cte_role_relationship.group_node)
Rows Removed by Join Filter: 20
-> CTE Scan on cte_role_relationship (cost=51.88..53.00 rows=1 width=181) (actual time=0.067..0.152 rows=5 loops=1)
Filter: ((pg_authid.oid <> group_node) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.004..0.119 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_13 (cost=0.00..1.15 rows=15 width=181) (actual time=0.003..0.005 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.007..0.018 rows=7 loops=5)
Hash Cond: (a_2.group_node = m_2.member)
Join Filter: (((NOT m_2.set_option) AND (NOT m_2.inherit_option) AND (NOT m_2.admin_option) AND ((a_2.level + 1) <= 1)) OR (COALESCE(a_2.got_set, true) AND m_2.set_option) OR (COALESCE(a_2.got_inherit, true) AND m_2.inherit_option) OR (COALESCE(a_2.got_inherit_via_set, false) AND m_2.inherit_option) OR (COALESCE(a_2.got_set, false) AND (NOT COALESCE(a_2.got_inherit, false)) AND (NOT m_2.set_option) AND m_2.inherit_option) OR (COALESCE(m_2.admin_option, false) AND (((a_2.level + 1) = 1) OR (a_2.got_set AND m_2.set_option) OR (a_2.got_inherit AND m_2.inherit_option) OR (COALESCE(a_2.got_inherit_via_set, false) AND m_2.inherit_option) OR (COALESCE(a_2.got_set, false) AND (NOT COALESCE(a_2.got_inherit, false)) AND (NOT m_2.set_option) AND m_2.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_2 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.007..0.008 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_2 (cost=0.00..1.03 rows=3 width=15) (actual time=0.002..0.004 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_1 (cost=0.00..1.15 rows=2 width=4) (actual time=0.001..0.002 rows=4 loops=5)
Filter: rolcanlogin
Rows Removed by Filter: 18
-> Aggregate (cost=54.34..54.35 rows=1 width=32) (actual time=0.154..0.155 rows=1 loops=1)
-> Sort (cost=54.33..54.34 rows=1 width=4) (actual time=0.150..0.152 rows=5 loops=1)
Sort Key: cte_role_relationship_1.group_node
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=51.88..54.32 rows=1 width=4) (actual time=0.056..0.149 rows=5 loops=1)
Join Filter: (pg_authid_2.oid = cte_role_relationship_1.group_node)
Rows Removed by Join Filter: 77
-> CTE Scan on cte_role_relationship cte_role_relationship_1 (cost=51.88..53.00 rows=1 width=181) (actual time=0.051..0.132 rows=5 loops=1)
Filter: ((pg_authid.oid <> group_node) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.003..0.101 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_14 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.015 rows=7 loops=5)
Hash Cond: (a_3.group_node = m_3.member)
Join Filter: (((NOT m_3.set_option) AND (NOT m_3.inherit_option) AND (NOT m_3.admin_option) AND ((a_3.level + 1) <= 1)) OR (COALESCE(a_3.got_set, true) AND m_3.set_option) OR (COALESCE(a_3.got_inherit, true) AND m_3.inherit_option) OR (COALESCE(a_3.got_inherit_via_set, false) AND m_3.inherit_option) OR (COALESCE(a_3.got_set, false) AND (NOT COALESCE(a_3.got_inherit, false)) AND (NOT m_3.set_option) AND m_3.inherit_option) OR (COALESCE(m_3.admin_option, false) AND (((a_3.level + 1) = 1) OR (a_3.got_set AND m_3.set_option) OR (a_3.got_inherit AND m_3.inherit_option) OR (COALESCE(a_3.got_inherit_via_set, false) AND m_3.inherit_option) OR (COALESCE(a_3.got_set, false) AND (NOT COALESCE(a_3.got_inherit, false)) AND (NOT m_3.set_option) AND m_3.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_3 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.006..0.006 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_3 (cost=0.00..1.03 rows=3 width=15) (actual time=0.002..0.003 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_2 (cost=0.00..1.15 rows=13 width=4) (actual time=0.001..0.002 rows=16 loops=5)
Filter: (NOT rolcanlogin)
Rows Removed by Filter: 4
-> Aggregate (cost=54.19..54.20 rows=1 width=32) (actual time=0.126..0.128 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.19 rows=1 width=0) (actual time=0.126..0.127 rows=0 loops=1)
Join Filter: (pg_authid_3.oid = cte_role_relationship_2.leaf_node)
-> CTE Scan on cte_role_relationship cte_role_relationship_2 (cost=51.88..53.00 rows=1 width=181) (actual time=0.126..0.126 rows=0 loops=1)
Filter: ((pg_authid.oid <> leaf_node) AND (group_node = pg_authid.oid))
Rows Removed by Filter: 56
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.096 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_15 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.015 rows=7 loops=5)
Hash Cond: (a_4.group_node = m_4.member)
Join Filter: (((NOT m_4.set_option) AND (NOT m_4.inherit_option) AND (NOT m_4.admin_option) AND ((a_4.level + 1) <= 1)) OR (COALESCE(a_4.got_set, true) AND m_4.set_option) OR (COALESCE(a_4.got_inherit, true) AND m_4.inherit_option) OR (COALESCE(a_4.got_inherit_via_set, false) AND m_4.inherit_option) OR (COALESCE(a_4.got_set, false) AND (NOT COALESCE(a_4.got_inherit, false)) AND (NOT m_4.set_option) AND m_4.inherit_option) OR (COALESCE(m_4.admin_option, false) AND (((a_4.level + 1) = 1) OR (a_4.got_set AND m_4.set_option) OR (a_4.got_inherit AND m_4.inherit_option) OR (COALESCE(a_4.got_inherit_via_set, false) AND m_4.inherit_option) OR (COALESCE(a_4.got_set, false) AND (NOT COALESCE(a_4.got_inherit, false)) AND (NOT m_4.set_option) AND m_4.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_4 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_4 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_3 (cost=0.00..1.15 rows=2 width=4) (never executed)
Filter: rolcanlogin
-> Aggregate (cost=54.32..54.33 rows=1 width=32) (actual time=0.126..0.127 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.32 rows=1 width=0) (actual time=0.126..0.126 rows=0 loops=1)
Join Filter: (pg_authid_4.oid = cte_role_relationship_3.leaf_node)
-> CTE Scan on cte_role_relationship cte_role_relationship_3 (cost=51.88..53.00 rows=1 width=181) (actual time=0.125..0.126 rows=0 loops=1)
Filter: ((pg_authid.oid <> leaf_node) AND (group_node = pg_authid.oid))
Rows Removed by Filter: 56
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.004..0.096 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_16 (cost=0.00..1.15 rows=15 width=181) (actual time=0.004..0.006 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.014 rows=7 loops=5)
Hash Cond: (a_5.group_node = m_5.member)
Join Filter: (((NOT m_5.set_option) AND (NOT m_5.inherit_option) AND (NOT m_5.admin_option) AND ((a_5.level + 1) <= 1)) OR (COALESCE(a_5.got_set, true) AND m_5.set_option) OR (COALESCE(a_5.got_inherit, true) AND m_5.inherit_option) OR (COALESCE(a_5.got_inherit_via_set, false) AND m_5.inherit_option) OR (COALESCE(a_5.got_set, false) AND (NOT COALESCE(a_5.got_inherit, false)) AND (NOT m_5.set_option) AND m_5.inherit_option) OR (COALESCE(m_5.admin_option, false) AND (((a_5.level + 1) = 1) OR (a_5.got_set AND m_5.set_option) OR (a_5.got_inherit AND m_5.inherit_option) OR (COALESCE(a_5.got_inherit_via_set, false) AND m_5.inherit_option) OR (COALESCE(a_5.got_set, false) AND (NOT COALESCE(a_5.got_inherit, false)) AND (NOT m_5.set_option) AND m_5.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_5 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_5 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_4 (cost=0.00..1.15 rows=13 width=4) (never executed)
Filter: (NOT rolcanlogin)
SubPlan 3
-> Append (cost=56.01..115.74 rows=2 width=32) (actual time=0.358..0.373 rows=5 loops=1)
-> Subquery Scan on "*SELECT* 1_1" (cost=56.01..57.87 rows=1 width=32) (actual time=0.152..0.154 rows=0 loops=1)
-> GroupAggregate (cost=56.01..57.86 rows=1 width=128) (actual time=0.151..0.153 rows=0 loops=1)
Group Key: grant_instance.via, pg_authid_7.rolname
-> Incremental Sort (cost=56.01..57.62 rows=2 width=265) (actual time=0.151..0.153 rows=0 loops=1)
Sort Key: grant_instance.grantor, pg_authid_7.rolname, grant_instance.via, grant_instance.level, grant_instance.grantor_path
Presorted Key: grant_instance.grantor
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
-> Nested Loop Left Join (cost=54.47..57.53 rows=1 width=265) (actual time=0.147..0.150 rows=0 loops=1)
Join Filter: (pg_authid_9.oid = grant_instance.via[(cardinality(grant_instance.via) - 1)])
-> Nested Loop (cost=54.47..56.12 rows=1 width=201) (actual time=0.147..0.149 rows=0 loops=1)
Join Filter: (pg_authid_7.oid = other.other)
-> Nested Loop (cost=54.47..54.78 rows=1 width=145) (actual time=0.147..0.149 rows=0 loops=1)
Join Filter: (other.other = grant_instance.group_node)
-> Merge Join (cost=54.46..54.56 rows=1 width=141) (actual time=0.142..0.146 rows=5 loops=1)
Merge Cond: (grant_instance.grantor = pg_authid_8.oid)
-> Sort (cost=53.02..53.03 rows=1 width=77) (actual time=0.133..0.135 rows=5 loops=1)
Sort Key: grant_instance.grantor
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on grant_instance (cost=51.88..53.01 rows=1 width=77) (actual time=0.052..0.132 rows=5 loops=1)
-> CTE Scan on cte_role_relationship cte_role_relationship_4 (cost=51.88..53.00 rows=1 width=181) (actual time=0.052..0.131 rows=5 loops=1)
Filter: (((got_admin AND (leaf_node <> grantor)) OR grant_is_empty) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.097 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_5 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.015 rows=7 loops=5)
Hash Cond: (a.group_node = m.member)
Join Filter: (((NOT m.set_option) AND (NOT m.inherit_option) AND (NOT m.admin_option) AND ((a.level + 1) <= 1)) OR (COALESCE(a.got_set, true) AND m.set_option) OR (COALESCE(a.got_inherit, true) AND m.inherit_option) OR (COALESCE(a.got_inherit_via_set, false) AND m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND (NOT m.set_option) AND m.inherit_option) OR (COALESCE(m.admin_option, false) AND (((a.level + 1) = 1) OR (a.got_set AND m.set_option) OR (a.got_inherit AND m.inherit_option) OR (COALESCE(a.got_inherit_via_set, false) AND m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND (NOT m.set_option) AND m.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Sort (cost=1.44..1.48 rows=15 width=68) (actual time=0.007..0.007 rows=16 loops=1)
Sort Key: pg_authid_8.oid
Sort Method: quicksort Memory: 27kB
-> Seq Scan on pg_authid pg_authid_8 (cost=0.00..1.15 rows=15 width=68) (actual time=0.002..0.003 rows=22 loops=1)
-> Function Scan on unnest other (cost=0.00..0.10 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=5)
-> Seq Scan on pg_authid pg_authid_7 (cost=0.00..1.15 rows=15 width=68) (never executed)
-> Seq Scan on pg_authid pg_authid_9 (cost=0.00..1.15 rows=15 width=68) (never executed)
-> Subquery Scan on "*SELECT* 2" (cost=56.01..57.87 rows=1 width=32) (actual time=0.205..0.217 rows=5 loops=1)
-> GroupAggregate (cost=56.01..57.86 rows=1 width=128) (actual time=0.205..0.216 rows=5 loops=1)
Group Key: grant_instance_1.via, pg_authid_10.rolname
-> Incremental Sort (cost=56.01..57.62 rows=2 width=265) (actual time=0.193..0.195 rows=5 loops=1)
Sort Key: grant_instance_1.grantor, pg_authid_10.rolname, grant_instance_1.via, grant_instance_1.level, grant_instance_1.grantor_path
Presorted Key: grant_instance_1.grantor
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB
-> Nested Loop Left Join (cost=54.47..57.53 rows=1 width=265) (actual time=0.154..0.189 rows=5 loops=1)
Join Filter: (pg_authid_12.oid = grant_instance_1.via[(cardinality(grant_instance_1.via) - 1)])
Rows Removed by Join Filter: 110
-> Nested Loop (cost=54.47..56.12 rows=1 width=201) (actual time=0.148..0.165 rows=5 loops=1)
Join Filter: (pg_authid_10.oid = other_1.other)
Rows Removed by Join Filter: 97
-> Nested Loop (cost=54.47..54.78 rows=1 width=145) (actual time=0.144..0.151 rows=5 loops=1)
Join Filter: (other_1.other = grant_instance_1.group_node)
Rows Removed by Join Filter: 15
-> Merge Join (cost=54.46..54.56 rows=1 width=141) (actual time=0.140..0.144 rows=5 loops=1)
Merge Cond: (grant_instance_1.grantor = pg_authid_11.oid)
-> Sort (cost=53.02..53.03 rows=1 width=77) (actual time=0.133..0.134 rows=5 loops=1)
Sort Key: grant_instance_1.grantor
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on grant_instance_1 (cost=51.88..53.01 rows=1 width=77) (actual time=0.053..0.132 rows=5 loops=1)
-> CTE Scan on cte_role_relationship cte_role_relationship_5 (cost=51.88..53.00 rows=1 width=181) (actual time=0.052..0.131 rows=5 loops=1)
Filter: (((got_admin AND (leaf_node <> grantor)) OR grant_is_empty) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.003..0.097 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_6 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.015 rows=7 loops=5)
Hash Cond: (a_1.group_node = m_1.member)
Join Filter: (((NOT m_1.set_option) AND (NOT m_1.inherit_option) AND (NOT m_1.admin_option) AND ((a_1.level + 1) <= 1)) OR (COALESCE(a_1.got_set, true) AND m_1.set_option) OR (COALESCE(a_1.got_inherit, true) AND m_1.inherit_option) OR (COALESCE(a_1.got_inherit_via_set, false) AND m_1.inherit_option) OR (COALESCE(a_1.got_set, false) AND (NOT COALESCE(a_1.got_inherit, false)) AND (NOT m_1.set_option) AND m_1.inherit_option) OR (COALESCE(m_1.admin_option, false) AND (((a_1.level + 1) = 1) OR (a_1.got_set AND m_1.set_option) OR (a_1.got_inherit AND m_1.inherit_option) OR (COALESCE(a_1.got_inherit_via_set, false) AND m_1.inherit_option) OR (COALESCE(a_1.got_set, false) AND (NOT COALESCE(a_1.got_inherit, false)) AND (NOT m_1.set_option) AND m_1.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_1 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.006 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_1 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Sort (cost=1.44..1.48 rows=15 width=68) (actual time=0.006..0.007 rows=16 loops=1)
Sort Key: pg_authid_11.oid
Sort Method: quicksort Memory: 27kB
-> Seq Scan on pg_authid pg_authid_11 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.003 rows=22 loops=1)
-> Function Scan on unnest other_1 (cost=0.00..0.10 rows=10 width=4) (actual time=0.000..0.001 rows=4 loops=5)
-> Seq Scan on pg_authid pg_authid_10 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.001 rows=20 loops=5)
-> Seq Scan on pg_authid pg_authid_12 (cost=0.00..1.15 rows=15 width=68) (actual time=0.000..0.001 rows=22 loops=5)
Planning Time: 4.502 ms
Execution Time: 1.245 ms
(180 rows)
collapse.txt (text/plain, 18.6K) download
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=217.04..275.81 rows=1 width=133) (actual time=0.754..0.766 rows=1 loops=1)
-> Nested Loop (cost=162.71..163.98 rows=1 width=102) (actual time=0.438..0.447 rows=1 loops=1)
-> Nested Loop (cost=108.53..109.77 rows=1 width=102) (actual time=0.319..0.327 rows=1 loops=1)
-> Nested Loop (cost=54.19..55.40 rows=1 width=70) (actual time=0.174..0.181 rows=1 loops=1)
-> Seq Scan on pg_authid (cost=0.00..1.19 rows=1 width=70) (actual time=0.018..0.023 rows=1 loops=1)
Filter: (rolname ~ 'u6_green'::text)
Rows Removed by Filter: 21
-> Aggregate (cost=54.19..54.20 rows=1 width=32) (actual time=0.155..0.156 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.19 rows=1 width=0) (actual time=0.154..0.155 rows=0 loops=1)
Join Filter: (pg_authid_1.oid = cte_role_relationship.group_node)
Rows Removed by Join Filter: 20
-> CTE Scan on cte_role_relationship (cost=51.88..53.00 rows=1 width=181) (actual time=0.061..0.141 rows=5 loops=1)
Filter: ((pg_authid.oid <> group_node) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.003..0.110 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_9 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.006..0.017 rows=7 loops=5)
Hash Cond: (a_1.group_node = m_1.member)
Join Filter: (((NOT m_1.set_option) AND (NOT m_1.inherit_option) AND (NOT m_1.admin_option) AND ((a_1.level + 1) <= 1)) OR (COALESCE(a_1.got_set, true) AND m_1.set_option) OR (COALESCE(a_1.got_inherit, true) AND m_1.inherit_option) OR (COALESCE(a_1.got_inherit_via_set, false) AND m_1.inherit_option) OR (COALESCE(a_1.got_set, false) AND (NOT COALESCE(a_1.got_inherit, false)) AND (NOT m_1.set_option) AND m_1.inherit_option) OR (COALESCE(m_1.admin_option, false) AND (((a_1.level + 1) = 1) OR (a_1.got_set AND m_1.set_option) OR (a_1.got_inherit AND m_1.inherit_option) OR (COALESCE(a_1.got_inherit_via_set, false) AND m_1.inherit_option) OR (COALESCE(a_1.got_set, false) AND (NOT COALESCE(a_1.got_inherit, false)) AND (NOT m_1.set_option) AND m_1.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_1 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.001 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.007..0.007 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_1 (cost=0.00..1.03 rows=3 width=15) (actual time=0.002..0.004 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_1 (cost=0.00..1.15 rows=2 width=4) (actual time=0.001..0.002 rows=4 loops=5)
Filter: rolcanlogin
Rows Removed by Filter: 18
-> Aggregate (cost=54.34..54.35 rows=1 width=32) (actual time=0.145..0.146 rows=1 loops=1)
-> Sort (cost=54.33..54.34 rows=1 width=4) (actual time=0.141..0.143 rows=5 loops=1)
Sort Key: cte_role_relationship_1.group_node
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=51.88..54.32 rows=1 width=4) (actual time=0.051..0.139 rows=5 loops=1)
Join Filter: (pg_authid_2.oid = cte_role_relationship_1.group_node)
Rows Removed by Join Filter: 77
-> CTE Scan on cte_role_relationship cte_role_relationship_1 (cost=51.88..53.00 rows=1 width=181) (actual time=0.047..0.123 rows=5 loops=1)
Filter: ((pg_authid.oid <> group_node) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.093 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_10 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.005..0.014 rows=7 loops=5)
Hash Cond: (a_2.group_node = m_2.member)
Join Filter: (((NOT m_2.set_option) AND (NOT m_2.inherit_option) AND (NOT m_2.admin_option) AND ((a_2.level + 1) <= 1)) OR (COALESCE(a_2.got_set, true) AND m_2.set_option) OR (COALESCE(a_2.got_inherit, true) AND m_2.inherit_option) OR (COALESCE(a_2.got_inherit_via_set, false) AND m_2.inherit_option) OR (COALESCE(a_2.got_set, false) AND (NOT COALESCE(a_2.got_inherit, false)) AND (NOT m_2.set_option) AND m_2.inherit_option) OR (COALESCE(m_2.admin_option, false) AND (((a_2.level + 1) = 1) OR (a_2.got_set AND m_2.set_option) OR (a_2.got_inherit AND m_2.inherit_option) OR (COALESCE(a_2.got_inherit_via_set, false) AND m_2.inherit_option) OR (COALESCE(a_2.got_set, false) AND (NOT COALESCE(a_2.got_inherit, false)) AND (NOT m_2.set_option) AND m_2.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_2 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.006 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_2 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_2 (cost=0.00..1.15 rows=13 width=4) (actual time=0.001..0.002 rows=16 loops=5)
Filter: (NOT rolcanlogin)
Rows Removed by Filter: 4
-> Aggregate (cost=54.19..54.20 rows=1 width=32) (actual time=0.118..0.119 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.19 rows=1 width=0) (actual time=0.118..0.119 rows=0 loops=1)
Join Filter: (pg_authid_3.oid = cte_role_relationship_2.leaf_node)
-> CTE Scan on cte_role_relationship cte_role_relationship_2 (cost=51.88..53.00 rows=1 width=181) (actual time=0.118..0.118 rows=0 loops=1)
Filter: ((pg_authid.oid <> leaf_node) AND (group_node = pg_authid.oid))
Rows Removed by Filter: 56
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.090 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_11 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.004..0.014 rows=7 loops=5)
Hash Cond: (a_3.group_node = m_3.member)
Join Filter: (((NOT m_3.set_option) AND (NOT m_3.inherit_option) AND (NOT m_3.admin_option) AND ((a_3.level + 1) <= 1)) OR (COALESCE(a_3.got_set, true) AND m_3.set_option) OR (COALESCE(a_3.got_inherit, true) AND m_3.inherit_option) OR (COALESCE(a_3.got_inherit_via_set, false) AND m_3.inherit_option) OR (COALESCE(a_3.got_set, false) AND (NOT COALESCE(a_3.got_inherit, false)) AND (NOT m_3.set_option) AND m_3.inherit_option) OR (COALESCE(m_3.admin_option, false) AND (((a_3.level + 1) = 1) OR (a_3.got_set AND m_3.set_option) OR (a_3.got_inherit AND m_3.inherit_option) OR (COALESCE(a_3.got_inherit_via_set, false) AND m_3.inherit_option) OR (COALESCE(a_3.got_set, false) AND (NOT COALESCE(a_3.got_inherit, false)) AND (NOT m_3.set_option) AND m_3.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_3 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_3 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_3 (cost=0.00..1.15 rows=2 width=4) (never executed)
Filter: rolcanlogin
-> Aggregate (cost=54.32..54.33 rows=1 width=32) (actual time=0.116..0.117 rows=1 loops=1)
-> Nested Loop (cost=51.88..54.32 rows=1 width=0) (actual time=0.116..0.117 rows=0 loops=1)
Join Filter: (pg_authid_4.oid = cte_role_relationship_3.leaf_node)
-> CTE Scan on cte_role_relationship cte_role_relationship_3 (cost=51.88..53.00 rows=1 width=181) (actual time=0.116..0.116 rows=0 loops=1)
Filter: ((pg_authid.oid <> leaf_node) AND (group_node = pg_authid.oid))
Rows Removed by Filter: 56
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.088 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_12 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.004..0.013 rows=7 loops=5)
Hash Cond: (a_4.group_node = m_4.member)
Join Filter: (((NOT m_4.set_option) AND (NOT m_4.inherit_option) AND (NOT m_4.admin_option) AND ((a_4.level + 1) <= 1)) OR (COALESCE(a_4.got_set, true) AND m_4.set_option) OR (COALESCE(a_4.got_inherit, true) AND m_4.inherit_option) OR (COALESCE(a_4.got_inherit_via_set, false) AND m_4.inherit_option) OR (COALESCE(a_4.got_set, false) AND (NOT COALESCE(a_4.got_inherit, false)) AND (NOT m_4.set_option) AND m_4.inherit_option) OR (COALESCE(m_4.admin_option, false) AND (((a_4.level + 1) = 1) OR (a_4.got_set AND m_4.set_option) OR (a_4.got_inherit AND m_4.inherit_option) OR (COALESCE(a_4.got_inherit_via_set, false) AND m_4.inherit_option) OR (COALESCE(a_4.got_set, false) AND (NOT COALESCE(a_4.got_inherit, false)) AND (NOT m_4.set_option) AND m_4.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a_4 (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m_4 (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Seq Scan on pg_authid pg_authid_4 (cost=0.00..1.15 rows=13 width=4) (never executed)
Filter: (NOT rolcanlogin)
SubPlan 2
-> GroupAggregate (cost=57.34..57.47 rows=1 width=128) (actual time=0.186..0.197 rows=4 loops=1)
Group Key: pg_authid_6.rolname, cte_role_relationship_4.via
-> Sort (cost=57.34..57.34 rows=1 width=265) (actual time=0.176..0.177 rows=5 loops=1)
Sort Key: pg_authid_6.rolname, cte_role_relationship_4.via, cte_role_relationship_4.level, cte_role_relationship_4.grantor, cte_role_relationship_4.grantor_path
Sort Method: quicksort Memory: 26kB
-> Nested Loop Left Join (cost=51.88..57.33 rows=1 width=265) (actual time=0.062..0.171 rows=5 loops=1)
Join Filter: (pg_authid_8.oid = cte_role_relationship_4.via[(cardinality(cte_role_relationship_4.via) - 1)])
Rows Removed by Join Filter: 110
-> Nested Loop (cost=51.88..55.91 rows=1 width=201) (actual time=0.056..0.152 rows=5 loops=1)
Join Filter: (pg_authid_7.oid = cte_role_relationship_4.grantor)
Rows Removed by Join Filter: 15
-> Nested Loop (cost=51.88..54.58 rows=1 width=137) (actual time=0.054..0.145 rows=5 loops=1)
Join Filter: (pg_authid_6.oid = other.other)
Rows Removed by Join Filter: 97
-> Nested Loop (cost=51.88..53.24 rows=1 width=81) (actual time=0.050..0.131 rows=5 loops=1)
Join Filter: (other.other = cte_role_relationship_4.group_node)
Rows Removed by Join Filter: 15
-> CTE Scan on cte_role_relationship cte_role_relationship_4 (cost=51.88..53.00 rows=1 width=181) (actual time=0.047..0.124 rows=5 loops=1)
Filter: (((got_admin AND (leaf_node <> grantor)) OR grant_is_empty) AND (leaf_node = pg_authid.oid))
Rows Removed by Filter: 51
CTE cte_role_relationship
-> Recursive Union (cost=0.00..51.88 rows=45 width=181) (actual time=0.002..0.092 rows=56 loops=1)
-> Seq Scan on pg_authid pg_authid_5 (cost=0.00..1.15 rows=15 width=181) (actual time=0.002..0.004 rows=22 loops=1)
-> Hash Join (cost=1.07..5.03 rows=3 width=181) (actual time=0.004..0.014 rows=7 loops=5)
Hash Cond: (a.group_node = m.member)
Join Filter: (((NOT m.set_option) AND (NOT m.inherit_option) AND (NOT m.admin_option) AND ((a.level + 1) <= 1)) OR (COALESCE(a.got_set, true) AND m.set_option) OR (COALESCE(a.got_inherit, true) AND m.inherit_option) OR (COALESCE(a.got_inherit_via_set, false) AND m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND (NOT m.set_option) AND m.inherit_option) OR (COALESCE(m.admin_option, false) AND (((a.level + 1) = 1) OR (a.got_set AND m.set_option) OR (a.got_inherit AND m.inherit_option) OR (COALESCE(a.got_inherit_via_set, false) AND m.inherit_option) OR (COALESCE(a.got_set, false) AND (NOT COALESCE(a.got_inherit, false)) AND (NOT m.set_option) AND m.inherit_option))))
Rows Removed by Join Filter: 2
-> WorkTable Scan on cte_role_relationship a (cost=0.00..3.00 rows=150 width=143) (actual time=0.000..0.000 rows=11 loops=5)
-> Hash (cost=1.03..1.03 rows=3 width=15) (actual time=0.005..0.005 rows=17 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pg_auth_members m (cost=0.00..1.03 rows=3 width=15) (actual time=0.001..0.003 rows=17 loops=1)
-> Function Scan on unnest other (cost=0.00..0.10 rows=10 width=4) (actual time=0.001..0.001 rows=4 loops=5)
-> Seq Scan on pg_authid pg_authid_6 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.001 rows=20 loops=5)
-> Seq Scan on pg_authid pg_authid_7 (cost=0.00..1.15 rows=15 width=68) (actual time=0.000..0.001 rows=4 loops=5)
-> Seq Scan on pg_authid pg_authid_8 (cost=0.00..1.15 rows=15 width=68) (actual time=0.001..0.002 rows=22 loops=5)
Planning Time: 3.037 ms
Execution Time: 0.924 ms
(128 rows)
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-26 22:32 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2024-01-26 22:32 UTC (permalink / raw)
To: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-27 00:19 ` David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David Rowley @ 2024-01-27 00:19 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Sat, 27 Jan 2024 at 11:33, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> 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.
Are the results correct if you SET enable_presorted_aggregate=0;?
David
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
@ 2024-01-27 00:36 ` David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David Rowley @ 2024-01-27 00:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Sat, 27 Jan 2024 at 13:19, David Rowley <dgrowleyml@gmail.com> wrote:
> Are the results correct if you SET enable_presorted_aggregate=0;?
For the record, I don't get the same results as you. Perhaps you have
some other roles that I don't have.
I see:
oid | role_type | rolname | rolsuper | administration
-------+-----------+--------------------+----------+----------------
42077 | User | u6_green_leader_su | f |
(1 row)
It might be worth trying to make the repro more self-contained. Can
you swap out the auth table with a mockup of it?
David
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
@ 2024-01-29 16:19 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2024-01-29 16:19 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Fri, Jan 26, 2024 at 5:36 PM David Rowley <dgrowleyml@gmail.com> wrote:
> On Sat, 27 Jan 2024 at 13:19, David Rowley <dgrowleyml@gmail.com> wrote:
> > Are the results correct if you SET enable_presorted_aggregate=0;?
>
>
Apparently I didn't reply-all...
Yes, the problem goes away when I disabled presorted_aggregate
I'll see if that knowledge can help build a better reproducer.
I'm using a stock desktop install of Ubuntu 22.04 and compiling without icu
support.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-29 21:31 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2024-01-29 21:31 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Mon, Jan 29, 2024 at 9:19 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Fri, Jan 26, 2024 at 5:36 PM David Rowley <dgrowleyml@gmail.com> wrote:
>
>> On Sat, 27 Jan 2024 at 13:19, David Rowley <dgrowleyml@gmail.com> wrote:
>> > Are the results correct if you SET enable_presorted_aggregate=0;?
>>
>>
> Apparently I didn't reply-all...
>
> Yes, the problem goes away when I disabled presorted_aggregate
>
> I'll see if that knowledge can help build a better reproducer.
>
>
I've deferred doing a better reproducer for the moment, I reliably got:
initdb
psql --file ~/unionall-repro.sql
psql -c '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)
to be produced for the bad bisect result and the correct nested result for
*manager* to produce on the good result.
❯ git bisect bad
0452b461bc405e6d35d8a14c02813c15e28ae516 is the first bad commit
commit 0452b461bc405e6d35d8a14c02813c15e28ae516
Author: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sun Jan 21 22:21:36 2024 +0200
Explore alternative orderings of group-by pathkeys during optimization.
When evaluating a query with a multi-column GROUP BY clause, we can
minimize
sort operations or avoid them if we synchronize the order of GROUP BY
clauses
with the ORDER BY sort clause or sort order, which comes from the
underlying
query tree. Grouping does not imply any ordering, so we can compare
the keys in arbitrary order, and a Hash Agg leverages this. But for
Group Agg,
we simply compared keys in the order specified in the query. This commit
explores alternative ordering of the keys, trying to find a cheaper one.
The ordering of group keys may interact with other parts of the query,
some of
which may not be known while planning the grouping. For example, there
may be
an explicit ORDER BY clause or some other ordering-dependent operation
higher up
in the query, and using the same ordering may allow using either
incremental
sort or even eliminating the sort entirely.
The patch always keeps the ordering specified in the query, assuming
the user
might have additional insights.
This introduces a new GUC enable_group_by_reordering so that the
optimization
may be disabled if needed.
Discussion:
https://postgr.es/m/7c79e6a5-8597-74e8-0671-1c39d124c9d6%40sigaev.ru
Author: Andrei Lepikhov, Teodor Sigaev
Reviewed-by: Tomas Vondra, Claudio Freire, Gavin Flower, Dmitry Dolgov
Reviewed-by: Robert Haas, Pavel Borisov, David Rowley, Zhihong Yu
Reviewed-by: Tom Lane, Alexander Korotkov, Richard Guo, Alena Rybakina
src/backend/optimizer/path/equivclass.c | 13 +-
src/backend/optimizer/path/pathkeys.c | 252 +++++++++++++++
src/backend/optimizer/plan/planner.c | 424
++++++++++++--------------
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/nodes/pathnodes.h | 10 +
src/include/optimizer/paths.h | 2 +
src/test/regress/expected/aggregates.out | 202 ++++++++++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/sql/aggregates.sql | 75 +++++
src/tools/pgindent/typedefs.list | 1 +
11 files changed, 770 insertions(+), 223 deletions(-)
postgres-head (0452b46) (BISECTING)
❯ git bisect log
# bad: [97287bdfae41b8ea16b27dccb63771fcc196a55a] Move is_valid_ascii() to
ascii.h.
# good: [aa817c7496575b37fde6ea5e0cd65b26f29ea532] Avoid useless
ReplicationOriginExitCleanup locking
git bisect start '97287bdfae' 'aa817c7496'
# bad: [752533d40fd50de0b09d4b956cc32c38f5df2f05] Test EXPLAIN (FORMAT
JSON) ... XMLTABLE
git bisect bad 752533d40fd50de0b09d4b956cc32c38f5df2f05
# good: [7b1dbf0a8d1d4e1e6d01a76dc45a3216e8a16d94] More documentation
updates for incremental backup.
git bisect good 7b1dbf0a8d1d4e1e6d01a76dc45a3216e8a16d94
# good: [c64086b79dbad19e4ee0af8d19e835111aa87bd5] Reorder actions in
ProcArrayApplyRecoveryInfo()
git bisect good c64086b79dbad19e4ee0af8d19e835111aa87bd5
# good: [7ab80ac1caf9f48064190802e1068ef89e2883c4] Generalize the common
code of adding sort before processing of grouping
git bisect good 7ab80ac1caf9f48064190802e1068ef89e2883c4
# bad: [49cd2b93d7dbceefdf9a71cc301d284a2dd234c3] Add test module
injection_points
git bisect bad 49cd2b93d7dbceefdf9a71cc301d284a2dd234c3
# bad: [c03d91d9be378975bcdbfa3e5d40e17288e6f13f] Fix table name collision
in tests in 0452b461bc
git bisect bad c03d91d9be378975bcdbfa3e5d40e17288e6f13f
# bad: [0452b461bc405e6d35d8a14c02813c15e28ae516] Explore alternative
orderings of group-by pathkeys during optimization.
git bisect bad 0452b461bc405e6d35d8a14c02813c15e28ae516
# first bad commit: [0452b461bc405e6d35d8a14c02813c15e28ae516] Explore
alternative orderings of group-by pathkeys during optimization.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
@ 2024-01-29 21:45 ` Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2024-01-29 21:45 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Mon, Jan 29, 2024 at 11:32 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Mon, Jan 29, 2024 at 9:19 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
>>
>> On Fri, Jan 26, 2024 at 5:36 PM David Rowley <dgrowleyml@gmail.com> wrote:
>>>
>>> On Sat, 27 Jan 2024 at 13:19, David Rowley <dgrowleyml@gmail.com> wrote:
>>> > Are the results correct if you SET enable_presorted_aggregate=0;?
>>>
>>
>> Apparently I didn't reply-all...
>>
>> Yes, the problem goes away when I disabled presorted_aggregate
>>
>> I'll see if that knowledge can help build a better reproducer.
>>
>
> I've deferred doing a better reproducer for the moment, I reliably got:
> initdb
> psql --file ~/unionall-repro.sql
> psql -c '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)
>
> to be produced for the bad bisect result and the correct nested result for *manager* to produce on the good result.
Thank you for noticing. I'm investigating this.
------
Regards,
Alexander Korotkov
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
@ 2024-02-01 04:06 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-01 09:53 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 1 reply; 13+ messages in thread
From: Andrei Lepikhov @ 2024-02-01 04:06 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On 30/1/2024 04:45, Alexander Korotkov wrote:
> On Mon, Jan 29, 2024 at 11:32 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>>
>> On Mon, Jan 29, 2024 at 9:19 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
>>>
>>> On Fri, Jan 26, 2024 at 5:36 PM David Rowley <dgrowleyml@gmail.com> wrote:
>>>>
>>>> On Sat, 27 Jan 2024 at 13:19, David Rowley <dgrowleyml@gmail.com> wrote:
>>>>> Are the results correct if you SET enable_presorted_aggregate=0;?
>>>>
>>>
>>> Apparently I didn't reply-all...
>>>
>>> Yes, the problem goes away when I disabled presorted_aggregate
>>>
>>> I'll see if that knowledge can help build a better reproducer.
>>>
>>
>> I've deferred doing a better reproducer for the moment, I reliably got:
>> initdb
>> psql --file ~/unionall-repro.sql
>> psql -c '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)
>>
>> to be produced for the bad bisect result and the correct nested result for *manager* to produce on the good result.
>
>
> 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.
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:
unionall-repro-c.sql (application/sql, 10.1K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
@ 2024-02-01 09:53 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-02 16:24 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 1 reply; 13+ messages in thread
From: Andrei Lepikhov @ 2024-02-01 09:53 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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.
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:
dump.sql (application/sql, 10.6K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-01 09:53 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
@ 2024-02-02 16:24 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-04 04:57 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 1 reply; 13+ messages in thread
From: Andrei Lepikhov @ 2024-02-02 16:24 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-01 09:53 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-02 16:24 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
@ 2024-02-04 04:57 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-07 09:28 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Andrei Lepikhov @ 2024-02-04 04:57 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; Richard Guo <guofenglinux@gmail.com>
And finally, I've got the synthetic test:
CREATE TABLE mess_grouping (x integer, y integer, z integer, w integer,
f integer);
INSERT INTO mess_grouping (x,y,z,w,f) (SELECT x%10, x % 2, x%2, 2, x%10
FROM generate_series(1,100) AS x);
ANALYZE mess_grouping;
SET enable_nestloop = 'off';
SET enable_hashjoin = 'off';
SET enable_hashagg = 'off';
SET enable_group_by_reordering = 'on';
SELECT c1.z, c1.w, string_agg(''::text, repeat(''::text, c1.f) ORDER BY
c1.x,c1.y)
FROM mess_grouping c1 JOIN mess_grouping c2 ON (c1.x = c2.f)
GROUP BY c1.w, c1.z;
SET enable_group_by_reordering = 'off';
SELECT c1.z, c1.w, string_agg(''::text, repeat(''::text, c1.f) ORDER BY
c1.x,c1.y)
FROM mess_grouping c1 JOIN mess_grouping c2 ON (c1.x = c2.f)
GROUP BY c1.w, c1.z;
DROP TABLE IF EXISTS mess_grouping CASCADE;
You can see here, that first query execution produces:
z | w | string_agg
---+---+------------
0 | 2 |
1 | 2 |
0 | 2 |
1 | 2 |
0 | 2 |
1 | 2 |
0 | 2 |
1 | 2 |
0 | 2 |
1 | 2 |
(10 rows)
and second execution gives correct result:
z | w | string_agg
---+---+------------
0 | 2 |
1 | 2 |
(2 rows)
The simple fix is in the attachment. But I'm not sure we should fix
GROUP-BY optimization instead of the more general issue.
The source of the problem is root->group_pathkeys, which contains
grouping pathkeys and aggregate pathkeys. For now, their 'sortref'
values could intersect, and we can differ which one references the query
target list and which one the target list of the aggregate.
So, I would like to get advice here: should we make a quick fix here, or
is such a mess in the sortref values not a mess and designed for some
purposes?
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:
naive_fix.diff (text/plain, 1.1K) download
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 82ff31273b..f89745f063 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -369,6 +369,7 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
{
List *new_group_pathkeys = NIL,
*new_group_clauses = NIL;
+ List *grouping_pathkeys = list_copy_head(*group_pathkeys, num_groupby_pathkeys);
ListCell *lc;
int n;
@@ -395,7 +396,7 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
* there is no sortclause reference for some reason.
*/
if (foreach_current_index(lc) >= num_groupby_pathkeys ||
- !list_member_ptr(*group_pathkeys, pathkey) ||
+ !list_member_ptr(grouping_pathkeys, pathkey) ||
pathkey->pk_eclass->ec_sortref == 0)
break;
@@ -429,6 +430,7 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
*group_clauses = list_concat_unique_ptr(new_group_clauses,
*group_clauses);
+ pfree(grouping_pathkeys);
return n;
}
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-01 09:53 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-02 16:24 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-04 04:57 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
@ 2024-02-07 09:28 ` Alexander Korotkov <aekorotkov@gmail.com>
2024-02-08 05:52 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 1 reply; 13+ messages in thread
From: Alexander Korotkov @ 2024-02-07 09:28 UTC (permalink / raw)
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; Richard Guo <guofenglinux@gmail.com>
On Sun, Feb 4, 2024 at 6:57 AM Andrei Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> The simple fix is in the attachment. But I'm not sure we should fix
> GROUP-BY optimization instead of the more general issue.
> The source of the problem is root->group_pathkeys, which contains
> grouping pathkeys and aggregate pathkeys. For now, their 'sortref'
> values could intersect, and we can differ which one references the query
> target list and which one the target list of the aggregate.
> So, I would like to get advice here: should we make a quick fix here, or
> is such a mess in the sortref values not a mess and designed for some
> purposes?
Thank you, Andrei. I think we should apply this fix for now, while
better refactoring could be done in future. I've revised your fix
with more comments and a commit message. I'm going to push it if
there are no objections.
------
Regards,
Alexander Korotkov
Attachments:
0001-Fix-usage-of-aggregate-pathkeys-in-group_keys_reo-v1.patch (application/octet-stream, 5.8K) download
From 3f7373e5c9535058307c3298158e016e6592b53a Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Wed, 7 Feb 2024 10:56:13 +0200
Subject: [PATCH] Fix usage of aggregate pathkeys in
group_keys_reorder_by_pathkeys()
group_keys_reorder_by_pathkeys() function searched for matching pathkeys
within root->group_pathkeys. That could lead to picking an aggregate pathkey
and using its pathkey->pk_eclass->ec_sortref as an argument of
get_sortgroupref_clause_noerr(). Given that ec_sortref of an aggregate pathkey
references aggregate targetlist not query targetlist, this leads to incorrect
query optimization.
Fix this by looking for matching pathkeys only within the first
num_groupby_pathkeys pathkeys.
Reported-by: David G. Johnston
Discussion: https://postgr.es/m/CAKFQuwY3Ek%3DcLThgd8FdaSc5JRDVt0FaV00gMcWra%2BTAR4gGUw%40mail.gmail.com
Author: Andrei Lepikhov, Alexander Korotkov
---
src/backend/optimizer/path/pathkeys.c | 25 ++++++++++++++++++++----
src/test/regress/expected/aggregates.out | 24 +++++++++++++++++++++++
src/test/regress/sql/aggregates.sql | 19 ++++++++++++++++++
3 files changed, 64 insertions(+), 4 deletions(-)
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 82ff31273b9..315fa262487 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -355,10 +355,14 @@ pathkeys_contained_in(List *keys1, List *keys2)
/*
* group_keys_reorder_by_pathkeys
- * Reorder GROUP BY keys to match the input pathkeys.
+ * Reorder GROUP BY pahtkeys and clauses to match the input pathkeys.
*
- * Function returns new lists (pathkeys and clauses), original GROUP BY lists
- * stay untouched.
+ * 'pathkeys' is an input list of pathkeys
+ * '*group_pathkeys' and '*group_clauses' are pathkeys and clauses lists to
+ * reorder. The pointers are redirected to new lists, original lists
+ * stay untouched.
+ * 'num_groupby_pathkeys' is the number of first '*group_pathkeys' items to
+ * search matching pathkeys.
*
* Returns the number of GROUP BY keys with a matching pathkey.
*/
@@ -369,12 +373,24 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
{
List *new_group_pathkeys = NIL,
*new_group_clauses = NIL;
+ List *grouping_pathkeys;
ListCell *lc;
int n;
if (pathkeys == NIL || *group_pathkeys == NIL)
return 0;
+ /*
+ * We're going to search within just the first num_groupby_pathkeys of
+ * *group_pathkeys. The thing is that root->group_pathkeys is passed as
+ * *group_pathkeys containing grouping pathkeys altogether with aggregate
+ * pathkeys. If we process aggregate pathkeys we could get an invalid
+ * result of get_sortgroupref_clause_noerr(), because their
+ * pathkey->pk_eclass->ec_sortref doesn't referece query targetlist. So,
+ * we allocate a separate list of pathkeys for lookups.
+ */
+ grouping_pathkeys = list_copy_head(*group_pathkeys, num_groupby_pathkeys);
+
/*
* Walk the pathkeys (determining ordering of the input path) and see if
* there's a matching GROUP BY key. If we find one, we append it to the
@@ -395,7 +411,7 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
* there is no sortclause reference for some reason.
*/
if (foreach_current_index(lc) >= num_groupby_pathkeys ||
- !list_member_ptr(*group_pathkeys, pathkey) ||
+ !list_member_ptr(grouping_pathkeys, pathkey) ||
pathkey->pk_eclass->ec_sortref == 0)
break;
@@ -429,6 +445,7 @@ group_keys_reorder_by_pathkeys(List *pathkeys, List **group_pathkeys,
*group_clauses = list_concat_unique_ptr(new_group_clauses,
*group_clauses);
+ list_free(grouping_pathkeys);
return n;
}
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 7a73c19314b..f736bab67ef 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -2874,6 +2874,30 @@ SELECT y,x,array_agg(distinct w) FROM btg WHERE y < 0 GROUP BY x,y;
RESET enable_incremental_sort;
DROP TABLE btg;
+-- Check we don't pick aggregate path key instead of grouping path key
+CREATE TABLE group_agg_pk AS SELECT
+ i % 10 AS x,
+ i % 2 AS y,
+ i % 2 AS z,
+ 2 AS w,
+ i % 10 AS f
+FROM generate_series(1,100) AS i;
+ANALYZE group_agg_pk;
+SET enable_nestloop = off;
+SET enable_hashjoin = off;
+SELECT
+ c1.z, c1.w, string_agg(''::text, repeat(''::text, c1.f) ORDER BY c1.x,c1.y)
+FROM group_agg_pk c1 JOIN group_agg_pk c2 ON (c1.x = c2.f)
+GROUP BY c1.w, c1.z;
+ z | w | string_agg
+---+---+------------
+ 0 | 2 |
+ 1 | 2 |
+(2 rows)
+
+RESET enable_nestloop;
+RESET enable_hashjoin;
+DROP TABLE group_agg_pk;
-- The case, when scanning sort order correspond to aggregate sort order but
-- can not be found in the group-by list
CREATE TABLE agg_sort_order (c1 int PRIMARY KEY, c2 int);
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 916dbf908f7..a19ef713d30 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1231,6 +1231,25 @@ RESET enable_incremental_sort;
DROP TABLE btg;
+-- Check we don't pick aggregate path key instead of grouping path key
+CREATE TABLE group_agg_pk AS SELECT
+ i % 10 AS x,
+ i % 2 AS y,
+ i % 2 AS z,
+ 2 AS w,
+ i % 10 AS f
+FROM generate_series(1,100) AS i;
+ANALYZE group_agg_pk;
+SET enable_nestloop = off;
+SET enable_hashjoin = off;
+SELECT
+ c1.z, c1.w, string_agg(''::text, repeat(''::text, c1.f) ORDER BY c1.x,c1.y)
+FROM group_agg_pk c1 JOIN group_agg_pk c2 ON (c1.x = c2.f)
+GROUP BY c1.w, c1.z;
+RESET enable_nestloop;
+RESET enable_hashjoin;
+DROP TABLE group_agg_pk;
+
-- The case, when scanning sort order correspond to aggregate sort order but
-- can not be found in the group-by list
CREATE TABLE agg_sort_order (c1 int PRIMARY KEY, c2 int);
--
2.39.3 (Apple Git-145)
* Re: v17 Possible Union All Bug
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` Re: v17 Possible Union All Bug David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` Re: v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-01 09:53 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-02 16:24 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-04 04:57 ` Re: v17 Possible Union All Bug Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-07 09:28 ` Re: v17 Possible Union All Bug Alexander Korotkov <aekorotkov@gmail.com>
@ 2024-02-08 05:52 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 0 replies; 13+ messages in thread
From: Andrei Lepikhov @ 2024-02-08 05:52 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <dgrowleyml@gmail.com>; Alexander Korotkov <akorotkov@postgresql.org>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>; Richard Guo <guofenglinux@gmail.com>
On 7/2/2024 16:28, Alexander Korotkov wrote:
> On Sun, Feb 4, 2024 at 6:57 AM Andrei Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> The simple fix is in the attachment. But I'm not sure we should fix
>> GROUP-BY optimization instead of the more general issue.
>> The source of the problem is root->group_pathkeys, which contains
>> grouping pathkeys and aggregate pathkeys. For now, their 'sortref'
>> values could intersect, and we can differ which one references the query
>> target list and which one the target list of the aggregate.
>> So, I would like to get advice here: should we make a quick fix here, or
>> is such a mess in the sortref values not a mess and designed for some
>> purposes?
>
> Thank you, Andrei. I think we should apply this fix for now, while
> better refactoring could be done in future. I've revised your fix
> with more comments and a commit message. I'm going to push it if
> there are no objections.
I looked into the patch and found only one typo, 'pahtkeys'.
--
regards,
Andrei Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-02-08 05:52 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-23 23:51 v17 Possible Union All Bug David G. Johnston <david.g.johnston@gmail.com>
2024-01-26 22:32 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-27 00:19 ` David Rowley <dgrowleyml@gmail.com>
2024-01-27 00:36 ` David Rowley <dgrowleyml@gmail.com>
2024-01-29 16:19 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:31 ` David G. Johnston <david.g.johnston@gmail.com>
2024-01-29 21:45 ` Alexander Korotkov <aekorotkov@gmail.com>
2024-02-01 04:06 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-01 09:53 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-02 16:24 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-04 04:57 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-07 09:28 ` Alexander Korotkov <aekorotkov@gmail.com>
2024-02-08 05:52 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox