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 1q9rVn-0000Wy-99 for pgsql-general@arkaria.postgresql.org; Thu, 15 Jun 2023 18:16:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9rVl-0003xT-6l for pgsql-general@arkaria.postgresql.org; Thu, 15 Jun 2023 18:15:57 +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 1q9rVk-0003s2-RC for pgsql-general@lists.postgresql.org; Thu, 15 Jun 2023 18:15:56 +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 1q9rVh-002ZzZ-St for pgsql-general@lists.postgresql.org; Thu, 15 Jun 2023 18:15:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1686852952; x=1687457752; i=karsten.hilbert@gmx.net; bh=DjjIKvY2xPCqHC4BxRBKuag2X+lw1cgyxNmIF/btE7M=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References:In-Reply-To; b=lYpFR3vTpO0tbovWuiQk4KYS3OrBBYr1MCgTZ6zpm+qTCkF4x5Juvhw+Cb2dfu5HutcNOmx mO7cXgVtEDRM4tbGYcoBaowBfFqvAFzgLSOmrD4xiz5KWyXs8E1Spu9sN9EtolQzPBaKSYlsN LG+2+UCff44HhM39Tf4ucAH7/B1VEnkPAPpyQg2ImAegc+5XTYeb+MqMOEPZJUR74bVBn0+Zb I6GTE9baX16rmmkx51figCrCCoTaYfMoTbAhoPsX6FMiNdZP1YVZIgFwC7YNuyu/LuM89T/h+ kFoCQveKb7FabQ4eRAyDsCTSadFbA2a92H5gnEJW6YBJ09sybHAg== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([193.77.111.174]) by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MI5UD-1qNLm40OFr-00FAKd; Thu, 15 Jun 2023 20:15:51 +0200 Received: from ncq by hermes with local (Exim 4.94.2) (envelope-from ) id 1q9rVa-0000vk-Mu; Thu, 15 Jun 2023 20:15:46 +0200 Date: Thu, 15 Jun 2023 20:15:46 +0200 From: Karsten Hilbert To: Ruslan Zakirov Cc: 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:AGgtCCdWdlHAtzEj8VzQ2VvO02HUHKGfT+78xwTP+P5I9OWPrvw HyvGfT7ZPeVXnI3qToJKX8b/UCavyXZFz1Wo3+25h2mYNHxvhZV5HNfGBj4yabgU/CLEXo1 PYUzE4lPcaxCHqBEUilSrVmPYRUI0GYfVtJ8BStjWfKlDbSUSgJieoD+fR5J+W6zUGXIWwR NFuJ+rH4pLgWPpTXn26ww== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:VBAOWmADa9w=;BRP5nzJ1nAQTPhEWCJ8Iv0BoQGa GGI8MDtSqkd/00PE73r7OFqTi/75LP7yxWxHjTSWfyfc8NSIfqAlFaFSaf0B4BI9aqq4HUJia /Dwrc6XoyYihQcvt+XfZLjs9l+rJbPspLQyEgeWgJBAVegckY54ZjHF3eiprLRmS3cMOgnJVz NAjqY2eZBhW6YO2vCzTiD05+kOoeE/+PopgJJ9LW7t5skEOcV0pOf04z0S1tfDkowSxkn6v+A JGb2JkUC4u1kAr+LwFjd402TJoq7hH0ALqbXutnIlGbLUKsn5ONXgsb3stD5qJTu23pj8qRVS 7RgW1rb8+Y3EduvQ0xjHUg/oXiPymaDE+jiXZsFTajSCotk+8UaJx7+2P4hPPiCb9DsMaOg0x Y+gvBF0L0fjqUfQOQ+bwK9rM21NAIbAhG/20oOKd0kPqV6wT23kAogWBojtn3/sVp0piyjEAB hg5tOr8RWJci7hcmtCo3XptqCGrKatxWMsf9Wtej8/tIXqkwCvYdKSzIZTYH1lDyqYjXU70wc 2ZO2K00izMRMKVSq4Y5bKT5qTidhB10cH+4ZlQVzWzUIMlHoE65H7CEped6MHBHa2IL8azy0D PwTFB65rCWGWVIm9ny+5wUimNayKRhq6HVMp70gNynumCz4/RkcBSeUIFnb1MPRsUqyDxgvlw W5CzoiJlwUakuixpwZxZrYMBgBa+Tl/IxfHaTFCR3pALFyTTm/EU99W40PxMcDEr5Bd06pnvW 8TmLpc9WW4Gebj9A6JC9lltdmbBNhdInnCBpTsK7CjGRT+3g6ALWiMonAWo2PxvugXgJSjCG5 Ci0sBsYKSYVjldLdkAjWeyrbPAUUBYj30Vk6cOQU1OSlMyERbgv+8pe5IFEdRTUYExbTzo7yH 5AG6His+fGBjNhHsDh2Ca/bxVzbC3/YLO6hvVwYmxejlifJZmaGvJJcRMIMtxhE9DTHZnEcP4 NpXbAvEi/VuoNXJzO+NTOXI+z+g= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Thu, Jun 15, 2023 at 12:58:55AM +0300 schrieb Ruslan Zakirov: e.g. > https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/ ... > My goal was to find a small dataset that demonstrates this ordering > mismatch. I attempted to think it through whether it is even *possible* to find a dataset (of any size) which *reliably* demonstrates the ordering mismatch. To my understanding -- since the outer query *can* (per happenstance) return rows in the order of the subquery's ORDER BY it is not possible to reliably demonstrate that it won't, is it ? Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B