Re: Reproducing incorrect order with order by in a subquery

On Wed, Jun 14, 2023 at 3:50 PM Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
wrote:

> IOW neither is help to be expected on this list nor can any
> testing (on PG) help with anything to be expected on MySQL ?
>

Don't expect any help on mysql part.

As to the question: since the outer query does not have an
> ORDER BY it can return results in any order INCLUDING the one
> produced by the subquery. Which renders impossible any

*proving* that it can return rows in orders different from
>

Well, in *theory* it can return rows in a different order that doesn't
match the order produced
by the subquery. As far as I know no RDBMS state in its documentation that
ordering between
subquery and its outer query is preserved. Some explicitly state the
opposite:

https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

MS SQL server:

"The ORDER BY clause is not valid in views, inline functions, derived
tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses
are also specified. When ORDER BY is used in these objects, the clause is
used only to determine the rows returned by the TOP clause or OFFSET and
FETCH clauses. The ORDER BY clause does not guarantee ordered results when
these constructs are queried, unless ORDER BY is also specified in the
query itself."

My goal was to find a small dataset that demonstrates this ordering
mismatch.

Failed to reach my goal. Ended up with a code change with a lot of
explanations,
comments and links to documentation. No prove in tests that the old code
was wrong
and a new one fixes it.

> the subquery *unless* one forces a different order on the
> outer query. Which in turn would defeat the purpose as then
> the outer query *does* have an explicit ordering...
>

--
Best regards, Ruslan.