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 1q91Fu-0000Yj-Nq for pgsql-general@arkaria.postgresql.org; Tue, 13 Jun 2023 10:28:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q91Ft-0000bc-Jx for pgsql-general@arkaria.postgresql.org; Tue, 13 Jun 2023 10:28:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q91Ec-00021I-8C for pgsql-general@lists.postgresql.org; Tue, 13 Jun 2023 10:26:46 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q91EZ-001xP1-IQ for pgsql-general@lists.postgresql.org; Tue, 13 Jun 2023 10:26:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1686652001; x=1687256801; i=shammat@gmx.net; bh=b8pKK9lz83vPpGTDLps2l2478Rx/MU7SNu9N+z8ou1k=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=n8bL47f1hqoF+6W2kj8P2kHSqC5Q7mY07gkrt/BO5C7xVpeyiB9GX5+cQ0vdcgzQEUxx0Gp 7tq8tgVGSQ4vYk9Zyrt1BLCMHmzCTtHCR4R1ik0YevfnjSC3kpAJ8wZcAZkoia0DPS+0bPm05 q/5YPCY0dR5cgYOR71K8zmTQ7rsS24/207imkXepx0VMnp2KRISljFvaFt6P2PbiiiJPPnW4o /TInUFculA12cXBQjMNsKW11pKeCuPbuVFSHgkSdi7Yw0v0kCBxpvfm2yiQpmnKkd9LFVt1Pg coJ5UzAKBU0Aj+VUnCzg0D6AsTHhUyqGefqrLIGDTCbiY5rsfi/w== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.8.0.19] ([185.40.248.10]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M2f5T-1q8kec0kDn-0049ca for ; Tue, 13 Jun 2023 12:26:41 +0200 Subject: Re: Reproducing incorrect order with order by in a subquery To: pgsql-general@lists.postgresql.org References: From: Thomas Kellerer Message-ID: Date: Tue, 13 Jun 2023 12:26:40 +0200 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8 Content-Language: de-DE Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:q+N5KNrF19eMu8jARf/XOYm4uNUfju9dNmQNRIV5DEvuxcwYtDN re/zGIzCpCAj6cpSnKkHE4/Wfovm4SeLHULU0N7GmmVj9xBia3zCBQos16lRldEbZLQsCxL 274v4wPgh9WJ+juuAgvzI4ZU99LlKW3b3uLfSjz/InznzGYUOpaM9TlYTv+tYtoZ1tGe+cW QgSebCiahBs+vSJ88U6aQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:lz/J0giRj50=;wLtdxPrURcQ8gIYHPYkstSFanMq LnKo7h8mBBcCcrOfw7jfihrG7Evn+m/dawrqLtKV2HMV3QAsgPYv4bqWkLPUWtYJNL1z9cEli 6YzwXtVDW652+mvygzVXWQMx1em9yBuvYGjYtyhdyf00Ea7g5yA3zLJkFRKxWNQiE3UTp+eKn 1PeQv3Mj/ZRYZhjMRP6rQq/awMxA2hGR5wzqmi28GKQ08YhVBbSALCBbotxtGKVbqynKIOk9+ bAokGa7TF3PG6iaiMHcxu1H8JyNiA84pGeEh56fpx8w32Pdx4AKFf0WQEc/rtMXMP0KUhqZmK ThZtdDfYeiZc32inDQtft7NbAJBPu2ALDyZMCfvvqKiYMUkmoOA8yOXouUwvpc8x8gE69Q6is kvKVmq5TSl70nZGHroNdbFo08ckTfxJ5r8ilnDuOpYK9KyniW626azQYH/5UEmIuPGQggvE8l U/0qPHH8g/t9KYfJkv7WNAFlxzIh9dpZ4e3XGNYPnyF+65PzaPrXB/ZoZ/Gc3ATs2BZkS9MgZ SK+FF4gAYl3MlS2B9XFbs1tCS14N6/cu7vce/E7sB7SewKiNnCmTsU7cpxz7adoDewPknu07P a10Eizoj6BmIodyAj5UKvze1ig800GGyTWmnpcTgIlRSmhSJbPpsFMdIsfkna16Wy1wRqzCqd Pe2wuWmzOuRFIj7jNc3zYFppqxAP5d+KAkEfJBYPQtRNG8ktQwvhSWji+VcYw6b++roY0fT7E DbSL1tdwmF+hmZy3NhDkW9Yy1WQ0z54NXbOUEb2+MA604AwHcG5ZXIU9i5H2ad7Zy9brL/xJ4 Ht4nnpOBGV8gBbkiWZeiBHfYD7fp0lYfe/vkTq4vnEw6iRWOQNi/8XaYP8ZogWrerteJxpLv6 7Z40d/5379duiS3985gaIXMJBldKgOGM4oBZlw9McoS8TAG62/ObBjB5GFkhPevMgBbhVZzbo EJgCkQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ruslan Zakirov schrieb am 13.06.2023 um 09:49: > For example I have a query: > > SELECT main.*, count(*) OVER () FROM (SELECT DISTINCT ... ORDER BY X) > main; > > So the `ORDER BY` clause ended up in a subquery. Most of the time > ordering works until it doesn't. > > Can you help me create a set of test tables with some data to > reproduce this problem more repeatedly? I just want to write a > regression test to make sure it doesn't happen again. Your final/overall query has no ORDER BY, so Postgres is free to return th= e result in any order it likes. You will have to add an ORDER BY to the "main" part to get a guaranteed so= rt order