I think I’m making some progress here, and it looks like a genuine bug to me.
Just to be sure, I cloned the VM that RT runs in and removed all plugins and all overrides from /opt/rt5/local/html. No change.
Brand new searches created with the query builder don’t sort properly, so it’s not something wrong with a widget or dashboard.
That leaves just that permission.
Next I turned on statement logging, and looked over the SQL logs for clicking on “10 newest unowned tickets” with and without the global ShowTicket permission. Those lead to very different queries.
With ShowTicket permission:
[3477] [Thu Jun 1 18:40:32 2023] [debug]: gemsley - SQL(0.000259s): SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM Tickets main JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue ) WHERE (main.IsMerged IS NULL) AND (main.Status != ?) AND (main.Type = ?) AND (main.Owner = ? AND ( ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) ) OR ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) ) ) ) ORDER BY main.Created DESC LIMIT ?; [ bound values: 'deleted' 'ticket' '6' 'approvals' 'new' 'open' 'stalled' 'default' 'new' 'open' 'stalled' '50' ] (/opt/rt5/sbin/../lib/RT/Interface/Web.pm:1423)
Without ShowTicket permission:
[3461] [Thu Jun 1 18:32:29 2023] [debug]: gemsley - SQL(0.000187s): SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM (SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = ? ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.Disabled = ? ) AND ( CachedGroupMembers_3.MemberId IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) WHERE ( ( main.Queue IN (?, ?, ?, ?, ?, ?) OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN (?, ?) ) OR ( main.Owner = ? ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != ?) AND (main.Type = ?) AND (main.Owner = ? AND ( ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) ) OR ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) ) ) ) ORDER BY main.Created DESC ) main LIMIT ?; [ bound values: 'RT::Ticket-Role' '0' '38' '837' '977' '950' '137720' '918' '48484' '903' '928' '976' '815' '952' '961' '962' '959' '963' '960' '958' '964' '965' '967' '966' '913' '927' '29' '1280' '802' '984' '128118' '796' '978' '981' '1016' '979' '51375' '1021' '16333' '1084' '83712' '45876' '126694' '83909' '26870' '18429' '137263' '992' '22500' '19948' '39078' '113663' '1415' '22466' '120659' '120715' '120658' '980' '122616' '985' '973' '949' '801' '1083' '987' '58468' '1' '15' '16' '18' '19' '20' 'requestor' 'cc' '38' 'deleted' 'ticket' '6' 'default' 'new' 'open' 'stalled' 'approvals' 'new' 'open' 'stalled' '10' ] (/opt/rt5/sbin/../lib/RT/Interface/Web.pm:1423)
That explains some of the older complaints I saw about not having ShowTicket global permission causing slowdowns before. It uses very different queries if it has to check permissions. Those were kind of hard to read though, so I enabled mysql’s statement logging to see what the SQL server was actually running.
With ShowTicket:
SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM Tickets main JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue ) WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '6' AND ( ( Queues_1.Lifecycle = 'default' AND main.Status IN ('new', 'open', 'stalled') ) OR ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') ) ) ) ORDER BY main.Created DESC LIMIT 50
Without ShowTicket:
SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM (SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_2 ON ( Groups_2.Domain = 'RT::Ticket-Role' ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1 ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3 ON ( CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.MemberId IN ('38', '837', '977', '950', '137720', '918', '48484', '903', '928', '976', '815', '952', '961', '962', '959', '963', '960', '958', '964', '965', '967', '966', '913', '927', '29', '1280', '802', '984', '128118', '796', '978', '981', '1016', '979', '51375', '1021', '16333', '1084', '83712', '45876', '126694', '83909', '26870', '18429', '137263', '992', '22500', '19948', '39078', '113663', '1415', '22466', '120659', '120715', '120658', '980', '122616', '985', '973', '949', '801', '1083', '987', '58468') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id ) WHERE ( ( main.Queue IN ('1', '15', '16', '18', '19', '20') OR ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN ('cc', 'requestor') ) OR ( main.Owner = '38' ) ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '6' AND ( ( Queues_1.Lifecycle = 'default' AND main.Status IN ('new', 'open', 'stalled') ) OR ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') ) ) ) ORDER BY main.Created DESC ) main LIMIT 50
Spot the error? I didn’t at first. But it’s putting the ORDER BY clause in the wrong spot. Instead of ) ORDER BY main.Created DESC ) main LIMIT 50
, it should read ) ) main ORDER BY main.Created DESC LIMIT 50
My conclusion so far is that there is a bug in how the query is generated. Now to dive into the code and figure out exactly where that statement gets generated.
EDIT: Setting Set($UseSQLForACLChecks, 0)
also fixes the sort issue, though the documentation says that will cause other bugs for wrong ticket counts. But seems to confirm that it is a bug with how the SQL statement is being structured.
EDIT2: Since it may be relevant for how the DBIx module handles things, I’m using MariaDB 10.3 on Ubuntu 20.04.6.