Re: Reproducing incorrect order with order by in a subquery - Mailing list pgsql-general

From David G. Johnston
Subject Re: Reproducing incorrect order with order by in a subquery
Date
Msg-id CAKFQuwb6+2KsQsva90d2oG7HzbVbeBMeCqM88LH0zO+THZLS-A@mail.gmail.com
Whole thread Raw
In response to Re: Reproducing incorrect order with order by in a subquery  (Ruslan Zakirov <ruslan.zakirov@gmail.com>)
List pgsql-general
On Tue, Jun 13, 2023 at 10:55 AM Ruslan Zakirov <ruslan.zakirov@gmail.com> wrote:
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.


If you want guaranteed ordered output you must place the order by in the outermost query level (i.e., before your limit 50).  Trying to do that for dynamic SQL where you don't actually know what query you are working with is going to be a challenge - maybe force the user to have the order by column first in their query then just say "ORDER BY 1" in the wrapper query you are adding?  Basically have them write "row_number() over (order by)" for their query and you then order by row number.

David J.



pgsql-general by date:

Previous
From: Ruslan Zakirov
Date:
Subject: Re: Reproducing incorrect order with order by in a subquery
Next
From: Torsten Förtsch
Date:
Subject: Exclusion constraint with negated operator?