On Mon, Jun 23, 2025 at 12:53 PM Hans Buschmann <buschm...@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. As noted in Window Functions page of docs https://www.postgresql.org/docs/current/tutorial-window.html > There is another important concept associated with window functions: for each row, there is a set of rows within its partition called its *window frame*. Some window functions act only on the rows of the window frame, rather than of the whole partition. By default, if ORDER BY is 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 the ORDER BY clause. When ORDER BY is 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). > > > Thank you for looking > > > Hans Buschmann > > >
