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. 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
postgres_max_window_function_error.sql
Description: postgres_max_window_function_error.sql
