Обсуждение: Reproducing incorrect order with order by in a subquery

Ruslan Zakirov schrieb am 13.06.2023 um 09:49:
> For example I have a query:
>
> SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X)
> main;
>
> So the `ORDER BY` clause ended up in a subquery. Most of the time
> ordering works until it doesn't.
>
> Can you help me create a set of test tables with some data to
> reproduce this problem more repeatedly? I just want to write a
> regression test to make sure it doesn't happen again.
Your final/overall query has no ORDER BY, so Postgres is free to return the result in any order it likes.

You will have to add an ORDER BY to the "main" part to get a guaranteed sort order

Ruslan Zakirov <ruslan.zakirov@gmail.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

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.

On Tue, Jun 13, 2023 at 6:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ruslan Zakirov <ruslan.zakirov@gmail.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.

IOW neither is help to be expected on this list nor can any
testing (on PG) help with anything to be expected on MySQL ?

As to the question: since the outer query does not have an
ORDER BY it can return results in any order INCLUDING the one
produced by the subquery. Which renders impossible any

*proving* that it can return rows in orders different from

the subquery *unless* one forces a different order on the
outer query. Which in turn would defeat the purpose as then
the outer query *does* have an explicit ordering...