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