Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo4oz-00050G-GW for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 16:34:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qo4nz-00198j-FY for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 16:33:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo4nz-00197I-4Y for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 16:33:00 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo4nx-0001ZP-8Y for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 16:32:59 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 394GWucQ2600125; Wed, 4 Oct 2023 12:32:56 -0400 From: Tom Lane To: jinser cc: pgsql-general@lists.postgresql.org Subject: Re: I'm using both window and agg, is this expected sorting behavior? In-reply-to: References: Comments: In-reply-to jinser message dated "Thu, 05 Oct 2023 00:05:19 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2600123.1696437176.1@sss.pgh.pa.us> Date: Wed, 04 Oct 2023 12:32:56 -0400 Message-ID: <2600124.1696437176@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk jinser writes: > This is what confuses me, I've tried many versions of pgsql, at least > including 11-16, and the behavior is consistent with the above; but I > feel that maybe the order by in the window function should not affect > the final result. Since you have not specified an ORDER BY for the overall query result, the implementation is entitled to return the rows in any order it pleases. Ours happens to sort by the window ordering before computing the window functions, so that's what you get. In this case anyway --- you shouldn't rely on that. regards, tom lane