Re: Reproducing incorrect order with order by in a subquery

On Tue, Jun 13, 2023 at 8:55 PM Ruslan Zakirov <ruslan(dot)zakirov(at)gmail(dot)com>
wrote:

> On Tue, Jun 13, 2023 at 6:06 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Ruslan Zakirov <ruslan(dot)zakirov(at)gmail(dot)com> 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 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.
>

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.