Re: Reproducing incorrect order with order by in a subquery

Am Wed, Jun 14, 2023 at 01:03:06PM +0300 schrieb Ruslan Zakirov:

> > This is a too complex query to build a test on. Tried simpler scenarios
> > and failed.
> >
>
> First of all I want to apologize. We work with multiple RDBMS systems. This
> particular user is using mysql.
...
> Anyway, yesterday I tried my simplified case on Pg latest, Pg 11 and on
> mysql latest. Had no luck. Either my test case is too simple or I can not
> find the correct distribution of data between two tables.

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

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

IMO you need to either rewrite the query or look to MySQL for
more detailed help. Probably the former, regardless of the
latter.

Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B