Message-ID: <74be0e9f-f827-b201-00eb-8ae93e53617b@aol.com>
Date: Mon, 26 Jun 2023 17:14:15 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Subject: Re: Window functions: frame-adhering aggregate without ORDER BY
 clause
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@lists.postgresql.org
References: <4ecad084-cf68-641c-ce02-b07b0b4625c9.ref@aol.com>
 <4ecad084-cf68-641c-ce02-b07b0b4625c9@aol.com>
 <716589.1687787663@sss.pgh.pa.us>
From: Romain Carl <romaincarl@aol.com>
Autocrypt: addr=romaincarl@aol.com; keydata=
 xsDNBGNC9xEBDAC8Bw/iVZfRtFM2abeFKtpS3NWCJ6I7w21jaukWKaRLvvM2ea2SBxhKKo74
 BaDY4ekMor/8aEdAT18NDx2Hx2L7jJxvuAL2JrfKrrkBGI+8OfMXRUCbfYHqvxpWt1W1m7PL
 Q5hk7P5ADiTlfwHvXLJG0nIjYCHxEQ9Cbdj8UioTMEYJUqhQe94j/ml+5Y/cALopSqVpalhQ
 hoxeR+SERLOtpALGlmqnkQC3Fi+NJo3vJI4PsrPLsW5kF5O5rNM55/i06QjgqDkU81YLUf1f
 xB1T+DEqR8Gg7Ae5wekIoDYLMsUk/LLMb3U8UqHhAeDd0eteyFqXvrjahwTIR+oBC2u2wtM/
 x/BesL4NSBgl6ztpG+IDXx9JQO/ntDrDpdxTkvaeGFydU4cWd5lOzd0Ab+xNgSLblg30cycq
 JwZElDPaqBhis5SPsnrsIn6XdC7k/7P/RL2gLIJtvjVcXgBEsXsu+EZ5m/91iQmFhApxDGNg
 2gQ105MID/YJ2BzzXXdSJiUAEQEAAc0gUm9tYWluIENhcmwgPHJvbWFpbmNhcmxAYW9sLmNv
 bT7CwQcEEwEIADEWIQRvx1EgcnbJBTrsRargIMKBZfo7PwUCY0L3EwIbAwQLCQgHBRUICQoL
 BRYCAwEAAAoJEOAgwoFl+js/awsMAIpuoeA83rYH3ecvLw+BL86T1J84q+MdoE1wgF9we9rE
 xWEpScRNackEN9hdKJvarzcXq40lkdwJokfxU9a9S5BLOmdqWDp+Nt873lHuLBSd2txZXu7M
 ZaGkDJyQToBnZNinuEq2GcNVFqlEQd4U++NOHNt2VOF9DL7vDlDNCN7KWXtFZ2Y1MLwv3Jad
 cW7H/UxLoKssF7zU4qysIVl8xNKcos4finG3oVUNcjP/YFfvAzDu8Ek37otDldsEJhwzBpP7
 sp89hVEvI6zdfDxKl6BCxvAEOV5t+RvnNRrfNM1X1uWOwpbGQ5epzbAxs79xzvigHov5OSCt
 +tZRu1PuW2QRA058UgjnRuUc50FuNPMVaGdokhcuDGT5nb6s7CdVoYEiVR71v1Y4G7h4Sh8u
 O+hOEPsm8jH1dHnQqt0voJgN/KeVi9sNReeKTgyUqGLAhf503UXeQ5otnFKykP/fSWsVmi/R
 6NmVCFyST/HJ9ypYcbAaSA/rQVJqTlTV4VG/7s7AzQRjQvcTAQwAm/vk9+2svn4D9ukpXTik
 LFkjHkLc997A7ol6G9xFL+N2tn0LRJzsqqFvKIIwMJGCa8OCob0qM52SY/ycoCQi9+1GsKvO
 7DRR8GTPI8zNIdNBpeLZcZMB3iOBozpgcBbUv11Bz2JwQvh5N4UcL+ZLNwf9n7cyj2dEPmXV
 cNH/q6L7vA/2UHFEZ0Sghaa3AGZ+dyBMLRIdMP9AEG+8UKxMQvK4FdbBBL1dgX2zmaLvBEIq
 1BjqWAMBC+rdXsKXGIvvCZ+PlXNKTQtRGQjTISB+k3UzZ9hwhZQZtYFnoQDH6pwx+Rj2m813
 Jz22fe+A01bC5a71SAEJgAQNnSkzXL2y1BxuoZ4qLMARqp1/6hiBN34Zuc+dPA1hMfyFOyVp
 fzdt3wfQRmakwwotu/tmefUTYJPnFd0RTZd8wGJxnyrevwoJWGzyyzPJckIX41KOtAcF1xih
 yA+H4CqNSczE7W0m0NwPpAQS7j3LRZely3tkpdZYq3WJuBZp8gEmeK0FT+BDABEBAAHCwPYE
 GAEIACAWIQRvx1EgcnbJBTrsRargIMKBZfo7PwUCY0L3FAIbDAAKCRDgIMKBZfo7PzQwDACN
 ne20cuuMO13xj2C+WBAIiAWbRlFJIRYZoCqgAXpX+T5rpd4Mz+R5YmMTQxhVVW55RgHOE555
 uDD/NnNcRMrI357i6GVIxKH1IrLi/bGwMaqGndmbyOwVWJ296WZbxyS7kcwOvoOYsjwXf4V7
 KNlO2oZadmT3EdRa2wG5pP8Fx9yRUQ6O4VV9v1JrFt6opiK1Qk1hM8eTNHtRz6G0THW8dsew
 6y/ZzfSmR3Jc6A8tKmnWu9AaoR9b6Wz/3OQbLC1egZo/UdkUoPA1W72bHXVwZDfljJtUvj+m
 gy9xDZdufAHFyAPJCQZaRA/1rr8s4HuxYGOOB5NPLmu0qQm8KaeH402XdxS82NsZ5Fzp0uHC
 EgKoXawh8T4RT08de6nmXZRU3mVsTAhzrYC1tlGfvSHYGIuSul4X6n0hY6tiIIWRmVE4OfVp
 irCSq/v+Wgj3O7jqm6Lx8FtpCepWExDyXFC9LT7EKLcGKuwDa+SZb+xXCwSqo2vR+QnMqswL
 ihiTl2M=
In-Reply-To: <716589.1687787663@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Length: 1477
Archived-At: 
 <https://www.postgresql.org/message-id/74be0e9f-f827-b201-00eb-8ae93e53617b%40aol.com>
Precedence: bulk

Alright, this makes sense. Thank you for the quick response!

Best regards,
Romain Carl

On 26.06.23 15:54, Tom Lane wrote:
> Romain Carl <romaincarl@aol.com> writes:
>> among the window tests (src/test/regress/expected/window.out), I noticed
>> the presence of tests that rely upon the order of rows not determined by
>> any ORDER BY clause, such as:
> Yeah ...
>
>> The current row's frame and, consequently, the result of the sum
>> aggregate depend on the order produced by the sequential scan of table
>> tenk1. Since such order is, in general, not part of PG's defined
>> behavior, what purpose do the tests that rely upon it serve?
> The tests are perfectly entitled to test PG's actual behavior.
> I don't see much difference between this particular case and the
> fact that we have any tests at all that lack ORDER BY, because
> formally speaking the engine could choose to emit the rows in
> some other order.  In practice, if we ever did make the engine
> behave differently, it'd be on us to fix affected test cases.
>
>> Following up to that, how is an EXCLUDE GROUP defined to behave in
>> absence of any ORDER BY clause?
> I see in the docs
>
>      <literal>EXCLUDE GROUP</literal> excludes the current row and its
>      ordering peers from the frame.
>
> and a bit later
>
>      Without <literal>ORDER BY</literal>,
>      ... all rows become peers of the current row.
>
> so excluding the whole frame seems like the right behavior.
>
> 			regards, tom lane