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:
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.