On Mon, Jun 23, 2025 at 12:53 PM Hans Buschmann <buschmann@nidsa.net> wrote:
Hello hackers,
While exploring some data cleanup and transformation in old data I stumbeld over $topic:
In a set of data (selected by season=23) I want to determine the min and max value of an order number (of_fac_order_n) for all occuring factories (am_fac_code).
I defined an external window clause for use in min and max functions in the query:
window wfac as (partition by of_season,am_fac_code)
For the purpose of additionally using the row_number function with the same window clause I changed the window clause to
window wfac as (partition by of_season,am_fac_code order by of_fac_order_n,id_of)
When executing the same query with only this change the values of max(of_fac_order_n) are wrong and not identical for all rows of the selected partition.
It seems max(of_fac_order_n) is only evaluated til the current row and not for the whole partition.
Exactly. This is not a bug but expected behaviour, regarding the default window frame when ORDER BY is supplied or not.
> There is another important concept associated with window functions: for each row, there is a set of rows within its partition called itswindow frame. Some window functions act only on the rows of the window frame, rather than of the whole partition. By default, ifORDER BYis supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to theORDER BYclause. WhenORDER BYis omitted the default frame consists of all rows in the partition.
Best regards
Pantelis Theodosiou
The problem occurred under PG18 beta1, but the error is the same in PG17.5 (others not tested).
For a quick response I didn't construct a full reproducable demo case, but in the attached I include the whole queries and the corresponding results.
All olumns in this example are simple integers/small integers from normal btree tables, nothing special.
The clue is the addition of an order by clause in the query wide windows definition which gives the wrong results.
There certainly may be other combinations (min function with descending order, other window functions) which may be sensible to the same problem, but this was not tested here.
Please find the queries and the results in the attached error report (not directly callable).
