Thanks for replying. Maybe I'm just wrong in my assumption. A user reports incorrect order in the following query:
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', ... , '987', '58468') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id )
WHERE ( ( main.Queue IN ('1', ... , '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
We have an option in our product that makes this query simpler, no joins in the subquery. The user reports that using this option helps with order.
This is a too complex query to build a test on. Tried simpler scenarios and failed.