pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: Reproducing incorrect order with order by in a subquery
Date: Wed, 14 Jun 2023 14:50:11 +0200
Message-ID: <ZIm3g4yCK3nMCQYB@hermes.hilbert.loc> (raw)
In-Reply-To: <CAMOxC8tXRR+LRkxWiiiXve9gKD16MjaAGNJDy6uB5henA7Wx-A@mail.gmail.com>
References: <CAMOxC8tBBR8DK3c73zUWSWRzTPP2FHv5+sLFqL2ZWWwLqGmzhw@mail.gmail.com>
	<dd2f192c-2b90-2bb7-27b4-004af7e7e26a@gmx.net>
	<CAMOxC8tvSx1Dduu5CvBTZ3eZdNMyE9=S2PSiypWKafVaF=-9nQ@mail.gmail.com>
	<1635592.1686668813@sss.pgh.pa.us>
	<CAMOxC8s98-YJE=C2Rurvs_1Y_Uu8GQA+uAv02c+HvHXpv4WY6Q@mail.gmail.com>
	<CAMOxC8tXRR+LRkxWiiiXve9gKD16MjaAGNJDy6uB5henA7Wx-A@mail.gmail.com>

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





view thread (11+ messages)  latest in thread

Message-ID: <ZIm3g4yCK3nMCQYB@hermes.hilbert.loc>
Permalink:  ../ZIm3g4yCK3nMCQYB@hermes.hilbert.loc/
Also on:    postgresql.org/message-id/ZIm3g4yCK3nMCQYB@hermes.hilbert.loc

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: Karsten.Hilbert@gmx.net
  Subject: Re: Reproducing incorrect order with order by in a subquery
  In-Reply-To: <ZIm3g4yCK3nMCQYB@hermes.hilbert.loc>
  References: <CAMOxC8tBBR8DK3c73zUWSWRzTPP2FHv5+sLFqL2ZWWwLqGmzhw@mail.gmail.com> <dd2f192c-2b90-2bb7-27b4-004af7e7e26a@gmx.net> <CAMOxC8tvSx1Dduu5CvBTZ3eZdNMyE9=S2PSiypWKafVaF=-9nQ@mail.gmail.com> <1635592.1686668813@sss.pgh.pa.us> <CAMOxC8s98-YJE=C2Rurvs_1Y_Uu8GQA+uAv02c+HvHXpv4WY6Q@mail.gmail.com> <CAMOxC8tXRR+LRkxWiiiXve9gKD16MjaAGNJDy6uB5henA7Wx-A@mail.gmail.com> <ZIm3g4yCK3nMCQYB@hermes.hilbert.loc>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox