Message-ID: <4ecad084-cf68-641c-ce02-b07b0b4625c9@aol.com>
Date: Mon, 26 Jun 2023 13:44:39 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.11.0
Content-Language: en-US
To: pgsql-sql@lists.postgresql.org
From: Romain Carl <romaincarl@aol.com>
Subject: Window functions: frame-adhering aggregate without ORDER BY clause
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=
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
References: <4ecad084-cf68-641c-ce02-b07b0b4625c9.ref@aol.com>
Content-Length: 2047
Archived-At: 
 <https://www.postgresql.org/message-id/4ecad084-cf68-641c-ce02-b07b0b4625c9%40aol.com>
Precedence: bulk

Hi Listers,

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:

SELECT sum(unique1) over (rows between 2 preceding and 2 following 
exclude no others),
     unique1, four
FROM tenk1 WHERE unique1 < 10;

Expected result:

  sum | unique1 | four
-------+-----------+------
       7 |            4 |    0
     13 |            2 |    2
     22 |            1 |    1
     26 |            6 |    2
     29 |            9 |    1
     31 |            8 |    0
     32 |            5 |    1
     23 |            3 |    3
     15 |            7 |    3
     10 |            0 |    0
(10 rows)

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?

Following up to that, how is an EXCLUDE GROUP defined to behave in 
absence of any ORDER BY clause? It seems to exclude the entire window 
frame according to this test:

SELECT sum(unique1) over (rows between 2 preceding and 2 following 
exclude group),
     unique1, four
FROM tenk1 WHERE unique1 < 10;

Expected result:

  sum | unique1 | four
-------+-----------+------
         |            4 |    0
         |            2 |    2
         |            1 |    1
         |            6 |    2
         |            9 |    1
         |            8 |    0
         |            5 |    1
         |            3 |    3
         |            7 |    3
         |            0 |    0
(10 rows)

Thanks in advance and best regards,
Romain