Re: [Question] Window Function Results without ORDER BY Clause

Hi,

David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Friday, July 11, 2025, Zhang Mingli <zmlpostgres(at)gmail(dot)com> wrote:
>> So, are both result sets technically correct given the absence of an ORDER
>> BY clause?

> The system is behaving within the requirements of the specification. The
> query itself is bugged code that the query author should fix.

Well, it's our own regression-test query. I think the actual question
being asked here is "do our regression tests need to pass under random
non-default GUC settings?". I'd say no; it'd be next door to
impossible to guarantee that. If this query gave unstable results
in practice, we'd have noticed by now (it's been there since 2010).

regards, tom lane

Would like to know ..

1.
Any particular reason why ORDER BY clause was ignored/removed from windows function
2.
if by applying the ORDER BY clause on windows function, were the regression test results become deterministic.

Thanks in advance

Dinesh

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Friday, July 11, 2025 9:27 PM
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Zhang Mingli <zmlpostgres(at)gmail(dot)com>; PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: [Question] Window Function Results without ORDER BY Clause

Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Friday, July 11, 2025, Zhang Mingli <zmlpostgres(at)gmail(dot)com> wrote:
>> So, are both result sets technically correct given the absence of an ORDER
>> BY clause?

> The system is behaving within the requirements of the specification. The
> query itself is bugged code that the query author should fix.

Well, it's our own regression-test query. I think the actual question
being asked here is "do our regression tests need to pass under random
non-default GUC settings?". I'd say no; it'd be next door to
impossible to guarantee that. If this query gave unstable results
in practice, we'd have noticed by now (it's been there since 2010).

regards, tom lane