Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9Px5-0003Ta-VP for pgsql-general@arkaria.postgresql.org; Wed, 14 Jun 2023 12:50:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9Px4-0008De-GH for pgsql-general@arkaria.postgresql.org; Wed, 14 Jun 2023 12:50:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q9Px4-0008DU-4X for pgsql-general@lists.postgresql.org; Wed, 14 Jun 2023 12:50:18 +0000 Received: from mout.gmx.net ([212.227.15.15]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q9Px1-002KZs-5c for pgsql-general@lists.postgresql.org; Wed, 14 Jun 2023 12:50:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1686747013; x=1687351813; i=karsten.hilbert@gmx.net; bh=c/yicqtr7NCk4jzdhkbRtjMiLmfPeYm81HgsKACmiNQ=; h=X-UI-Sender-Class:Date:From:To:Subject:References:In-Reply-To; b=LZyIVMgDTKG2ucmMobKjIIwkJo9kuv74kinPdijveab6ZQYRYVEtJZ8xC/4Qc4cOo5VDcw3 61xu3+m52AKXDqZRTBZQDJ+zmRi84sCQQGYK9cYqdGpBjWiFeWDKVfv4VNf5WwNjFiQpCfIZz /zazNfeN33JyW43a0yd64pu3XC1nCQo47yoBkSODAMPXkMcgcZ3CDyOwN8UM6yjdYN5GP2YI+ psiPstlpywSU1wJhe5ZReZu4vnXomvkvdQZIws52CFldjUzaPGPtX6rap+ERp1QLw6A4C7hxL mH5IClLrNfP8+41yPtKVB1v6xk3XRzhq5jUTrKguRsD4FlZleCEQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([193.77.111.174]) by mail.gmx.net (mrgmx005 [212.227.17.190]) with ESMTPSA (Nemesis) id 1N6KUd-1q2QjU0iLp-016jHf for ; Wed, 14 Jun 2023 14:50:13 +0200 Received: from ncq by hermes with local (Exim 4.94.2) (envelope-from ) id 1q9Pwx-0001yt-Qk for pgsql-general@lists.postgresql.org; Wed, 14 Jun 2023 14:50:11 +0200 Date: Wed, 14 Jun 2023 14:50:11 +0200 From: Karsten Hilbert To: pgsql-general@lists.postgresql.org Subject: Re: Reproducing incorrect order with order by in a subquery Message-ID: References: <1635592.1686668813@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:XazhWeF1Z7L5HCOsuhSxXr5eohmGYm7+7xmLNQpW7vH8Dp2MODr 2ae5Y/GFdKwASiaDjs8XDLpFimcgzrqBG65KHL7TdNPVvhmBc+ZJHTY3SyjpwJM0eqViBHv V0r7+EGT2IrmL6NyFIjjhxjeQxNGl0M5HG8Ff+3AtXFCT5DVZH2rTbBBKIYRWWrW7nTsr23 en8DxIhuVVq4ibM1w4OXg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:8upvr/GH998=;mBlMB2ttecccxMpg8+E3G2YHFrt TqVN9Z4CRs7GQLyzBIBYps7DEUmw6Gv1LU+L+7okMAjCInI7RMTNAwKwK+oDcE5KvhrqR3b/q oUTydI4qdl3SDPSHrFfwyTkRTE02/hED1/71p3FO5U5oFeMx2JGd64/ruTLE6uxDfblY7o3KW SJr7zLW8vNyfDoB5jvKfdgqhp5lNhytWmuCpV35s9LzbL2+TiiCKjiAlFC4gZzE5FsUYx1MR+ VoCYxER5TTKa09XoayK8y5F+o95ySSt0DB97ryM/LJlqAD2zwVg/hdqZjVRN00u7iTeXjAu95 uWHUbl0rcPpPZI+o9geJHyO+xJv39RnAEkdxeOm5TFld5JS/EtmcB1IBdsaUL83Q+Oj2ROOh2 KNEx1WkqExANQRgbdZJ8OPXmDsH+ky29QElgmHqGqcvmmAm3z5zhI8kdsxdQeSWHlc3B8e+xo H59TCr2mX1onfJvIpnNyZm/uYRLoxyVZR3ApRE0Njvy4NoE6IVD4gTH7yvKp5j2Wj/XsOtZFj yNjPVq7yy2O88JlV0nd4QztQq9YdmvuJ1neTYw79rZD/ftG7CPEhIhEtC+fFysnzUm8GxoAyk Kz0hDDWnnOS3/M7nSQhxT7jjeSoXB56Zw9Lc+NDsM1tgMGACydrWfQ3sWzVhDn7hN36NwEcwL HVCQQKOuYA4Z+cELweVN54tCGStPi6e0tJT2d9YauPPeQLq7+R7mDL9sVa2BjbAcdDsGd8719 CI1JVCBsEcTXDbqRAojPJvZFrgB/mWfTD5yQUzowRC+0rg9aghnZl47iehChh7N/W4F63/THN rqLlbrNZajWCmTcMjkWEMLNh7bguNbejlETI3ClfgURmaFZdgDjwu4loBBcFKnX3+tnQLwOdm WPEX0knL9KYzrQH3iXo7TAoqhP9cneG+53W0jr5bF2l62I+af/Ik8AQu99lUdfMWmrahUUOYM iH8m8rByv0ptNb0h7GixNQdLT84= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 scenario= s > > and failed. > > > > First of all I want to apologize. We work with multiple RDBMS systems. T= his > 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 no= t > 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 =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B