On Tue, Jun 13, 2023 at 8:55 PM Ruslan Zakirov <ruslan.zakirov@xxxxxxxxx> wrote:
On Tue, Jun 13, 2023 at 6:06 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Ruslan Zakirov <ruslan.zakirov@xxxxxxxxx> writes:
> I know how to fix the problem and I know that ORDER BY should be in the
> outermost select.
> However, I want to write a test case that shows that the old code is wrong,
> but can not create
> minimal set of tables to reproduce it. With this I'm looking for help.
The ORDER BY in the sub-select will be honored at the output of the
sub-select. To have a different ordering at the final output, you
need the upper query to do something that would re-order the rows.
Joining the sub-select to something else might make that happen,
or you could apply DISTINCT or some other non-trivial processing
in the upper query.
regards, tom lane
Hello Tom,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 mainLEFT 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 50We 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.
Hello,
First of all I want to apologize. We work with multiple RDBMS systems. This particular user is using mysql. So it's not clear if it works ok or not on Pg.
Anyway, yesterday I tried my simplified case on Pg latest, Pg 11 and on mysql latest. Had no luck. Either my test case is too simple or I can not find the correct distribution of data between two tables.
Spent too much time on this. Going to work on the query builder and move the "order by" clause out of the subquery. Just to be on the safe side. Most probably it will fix the issue for the user.
