Re: Reproducing incorrect order with order by in a subquery

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