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