On Tue, Jun 13, 2023 at 1:26 PM Thomas Kellerer <shammat@gmx.net> wrote:
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
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.