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 1qDoyQ-0004Ju-Q4 for pgsql-sql@arkaria.postgresql.org; Mon, 26 Jun 2023 16:21:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qDoyO-000552-9W for pgsql-sql@arkaria.postgresql.org; Mon, 26 Jun 2023 16:21:52 +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 1qDoyN-00054t-SF for pgsql-sql@lists.postgresql.org; Mon, 26 Jun 2023 16:21:51 +0000 Received: from mail-pj1-x1030.google.com ([2607:f8b0:4864:20::1030]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qDoyK-000c8T-Lp for pgsql-sql@lists.postgresql.org; Mon, 26 Jun 2023 16:21:51 +0000 Received: by mail-pj1-x1030.google.com with SMTP id 98e67ed59e1d1-262d33fa37cso960905a91.3 for ; Mon, 26 Jun 2023 09:21:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=heimdalldata.com; s=google; t=1687796506; x=1690388506; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=XSFk+XeLOo3vEFumve0SbBKqbAETYGn57wwUeIgBSlY=; b=DHiLMB5UPtbJVuD/rXcqCtWaRaQ+XRTLCJ5ZYZ3HCvaQvX4g6X0trJXZAxJBq/+zBQ TClK/QUL1kcX+c6lUgVQq3LfJ9+U5TuZLbSpgWqWdga3xw8cUP6/GzNaQ436vs9RvIRO 53rkZKAqCFZdAK8kR5kEeHVP7og9qzHqy34Lk= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687796506; x=1690388506; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=XSFk+XeLOo3vEFumve0SbBKqbAETYGn57wwUeIgBSlY=; b=b5BCszt1aMQ7lX7fvr5ilcED3+gTCnfh6Ms+QbqCAC2uc/G/yVuSv/KG8qYCi8UIGQ Qq8fLwP8CjxtztIsK5OJU+WVU03EH34gLA5AAKHHzHIgAvXIX39YJ0fduiVUOjEo0A42 Drr52aBZmqiJMcQglmFSxTAePhQUpIc11Wx6WLAKQFj+Mf8IUhyElQFpyo+41I8A1F+y 8bijlh41Y9cdsW2LPzbLHPn5k405BvzAZXrdzghMXORtnTKWR6L3MM8zEi0OWjcc3Q9e ei8hdbmAafHEJj3I5TNAqdXmaZFO9ZoGS2YLDB7ZO4J6xo9B+9WOgRHHbR60zdC1w5BG 8k/g== X-Gm-Message-State: AC+VfDwmHeKKiCj7hfr0mIRgtwfXHBCByTWAvuwtxfuY2htTMQG5aYq0 VGx4A20TTGVPsIy8sp5+SYHNsWtHUsfbi4qERVAKUw== X-Google-Smtp-Source: ACHHUZ7tiTNSiTxd6CXD+nJahcJvDE7RXfq8FpIufLR54uoYMO1vtnImTfShFpqLjxfi5bxpzQO05r18M6C7ekGC+GA= X-Received: by 2002:a17:90a:17ca:b0:262:ec74:bb33 with SMTP id q68-20020a17090a17ca00b00262ec74bb33mr2697472pja.46.1687796506222; Mon, 26 Jun 2023 09:21:46 -0700 (PDT) MIME-Version: 1.0 References: <4ecad084-cf68-641c-ce02-b07b0b4625c9.ref@aol.com> <4ecad084-cf68-641c-ce02-b07b0b4625c9@aol.com> <716589.1687787663@sss.pgh.pa.us> <74be0e9f-f827-b201-00eb-8ae93e53617b@aol.com> In-Reply-To: <74be0e9f-f827-b201-00eb-8ae93e53617b@aol.com> From: Erik Brandsberg Date: Mon, 26 Jun 2023 12:21:35 -0400 Message-ID: Subject: Re: Window functions: frame-adhering aggregate without ORDER BY clause To: Romain Carl Cc: Tom Lane , pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000034f93c05ff0abfb5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000034f93c05ff0abfb5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I would add to this that tests that ensure that undocumented behaviors are consistent are a good thing. I have seen many cases in my life where changing such behaviors will trigger breakage in applications that (unfortunately) depend on them. As such, by having the tests, it ensures that someone has to make a decision if they are broken, and decide if it is worth the risk. On Mon, Jun 26, 2023 at 11:14=E2=80=AFAM Romain Carl w= rote: > 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 writes: > >> among the window tests (src/test/regress/expected/window.out), I notic= ed > >> 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 > > > > EXCLUDE GROUP excludes the current row and its > > ordering peers from the frame. > > > > and a bit later > > > > Without ORDER BY, > > ... all rows become peers of the current row. > > > > so excluding the whole frame seems like the right behavior. > > > > regards, tom lane > > > --00000000000034f93c05ff0abfb5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I would add to this that tests that ensure that undocument= ed behaviors are consistent are a good thing.=C2=A0 I have seen many cases = in my life where changing such behaviors will trigger breakage in applicati= ons that (unfortunately) depend on them.=C2=A0 As such, by having the tests= , it ensures that someone has to make a decision if they are broken, and de= cide if it is worth the risk.=C2=A0=C2=A0

On Mon, Jun 26, 2023 at 11:14=E2= =80=AFAM Romain Carl <romaincarl@a= ol.com> wrote:
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 n= oticed
>> the presence of tests that rely upon the order of rows not determi= ned by
>> any ORDER BY clause, such as:
> Yeah ...
>
>> The current row's frame and, consequently, the result of the s= um
>> aggregate depend on the order produced by the sequential scan of t= able
>> tenk1. Since such order is, in general, not part of PG's defin= ed
>> 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.=C2=A0 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
>
>=C2=A0 =C2=A0 =C2=A0 <literal>EXCLUDE GROUP</literal> exclu= des the current row and its
>=C2=A0 =C2=A0 =C2=A0 ordering peers from the frame.
>
> and a bit later
>
>=C2=A0 =C2=A0 =C2=A0 Without <literal>ORDER BY</literal>, >=C2=A0 =C2=A0 =C2=A0 ... all rows become peers of the current row.
>
> so excluding the whole frame seems like the right behavior.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0regards, tom lane


--00000000000034f93c05ff0abfb5--