On Tue, Jun 13, 2023 at 1:26 PM Thomas Kellerer <sham...@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. -- Best regards, Ruslan.
