
-- error-report of incorrect results of max window function with order by claus

                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.1.1 20250425 (Red Hat 15.1.1-1), 64-bit
(1 row)


-- QUERY with error result:

select *
,max_fac-min_fac as delta
from (
select
 of_season
,am_fac_code
,of_fac_order_n
,id_of
,max(of_fac_order_n) over wfac as max_fac
,min(of_fac_order_n) over wfac as min_fac
,row_number () over wfac as facor_num
,lead(of_fac_order_n,1,of_fac_order_n) over wfac as next_facor
from or_followup
left join models on id_am=of_id_am
window wfac as (partition by of_season,am_fac_code order by of_fac_order_n,id_of)
)
where of_season=23 order by am_fac_code,of_fac_order_n,id_of;


-- result: ERROR in column max_fac

cpsdb=#
select *
,max_fac-min_fac as delta
from (
select
 of_season
,am_fac_code
,of_fac_order_n
,id_of
,max(of_fac_order_n) over wfac as max_fac
,min(of_fac_order_n) over wfac as min_fac
,row_number () over wfac as facor_num
,lead(of_fac_order_n,1,of_fac_order_n) over wfac as next_facor
from or_followup
left join models on id_am=of_id_am
window wfac as (partition by of_season,am_fac_code order by of_fac_order_n,id_of)
)
where of_season=23 order by am_fac_code,of_fac_order_n,id_of;

 of_season | am_fac_code | of_fac_order_n | id_of | max_fac | min_fac | facor_num | next_facor | delta
-----------+-------------+----------------+-------+---------+---------+-----------+------------+-------
        23 |          13 |            845 | 48057 |     845 |     845 |         1 |        845 |     0
        23 |          13 |            845 | 48058 |     845 |     845 |         2 |        845 |     0
        23 |          13 |            845 | 48059 |     845 |     845 |         3 |        846 |     0
        23 |          13 |            846 | 48060 |     846 |     845 |         4 |        846 |     1
        23 |          13 |            846 | 48061 |     846 |     845 |         5 |        847 |     1
        23 |          13 |            847 | 48062 |     847 |     845 |         6 |        847 |     2
        23 |          13 |            847 | 48063 |     847 |     845 |         7 |        848 |     2
        23 |          13 |            848 | 48094 |     848 |     845 |         8 |        849 |     3
        23 |          13 |            849 | 48109 |     849 |     845 |         9 |        850 |     4
        23 |          13 |            850 | 48964 |     850 |     845 |        10 |        851 |     5
        23 |          13 |            851 | 49464 |     851 |     845 |        11 |        851 |     6
        23 |          24 |           2242 | 48028 |    2242 |    2242 |         1 |       2243 |     0
        23 |          24 |           2243 | 48044 |    2243 |    2242 |         2 |       2243 |     1
        23 |          24 |           2243 | 48045 |    2243 |    2242 |         3 |       2244 |     1
        23 |          24 |           2244 | 48047 |    2244 |    2242 |         4 |       2244 |     2
...


-- QUERY without order clause in window definition: CORRECT result:

select *
,max_fac-min_fac as delta
from (
select
 of_season
,am_fac_code
,of_fac_order_n
,id_of
,max(of_fac_order_n) over wfac as max_fac
,min(of_fac_order_n) over wfac as min_fac
,row_number () over wfac as facor_num
,lead(of_fac_order_n,1,of_fac_order_n) over wfac as next_facor
from or_followup
left join models on id_am=of_id_am
window wfac as (partition by of_season,am_fac_code ) --order by of_fac_order_n,id_of)
)
where of_season=23 order by am_fac_code,of_fac_order_n,id_of;



-- result: correct in column max_fac

cpsdb=#
select *
,max_fac-min_fac as delta
from (
select
 of_season
,am_fac_code
,of_fac_order_n
,id_of
,max(of_fac_order_n) over wfac as max_fac
,min(of_fac_order_n) over wfac as min_fac
,row_number () over wfac as facor_num
,lead(of_fac_order_n,1,of_fac_order_n) over wfac as next_facor
from or_followup
left join models on id_am=of_id_am
window wfac as (partition by of_season,am_fac_code ) --order by of_fac_order_n,id_of)
)
where of_season=23 order by am_fac_code,of_fac_order_n,id_of;

 of_season | am_fac_code | of_fac_order_n | id_of | max_fac | min_fac | facor_num | next_facor | delta
-----------+-------------+----------------+-------+---------+---------+-----------+------------+-------
        23 |          13 |            845 | 48057 |     851 |     845 |         3 |        850 |     6
        23 |          13 |            845 | 48058 |     851 |     845 |         2 |        845 |     6
        23 |          13 |            845 | 48059 |     851 |     845 |         1 |        845 |     6
        23 |          13 |            846 | 48060 |     851 |     845 |        11 |        846 |     6
        23 |          13 |            846 | 48061 |     851 |     845 |        10 |        846 |     6
        23 |          13 |            847 | 48062 |     851 |     845 |         9 |        846 |     6
        23 |          13 |            847 | 48063 |     851 |     845 |         8 |        847 |     6
        23 |          13 |            848 | 48094 |     851 |     845 |         7 |        847 |     6
        23 |          13 |            849 | 48109 |     851 |     845 |         6 |        848 |     6
        23 |          13 |            850 | 48964 |     851 |     845 |         4 |        851 |     6
        23 |          13 |            851 | 49464 |     851 |     845 |         5 |        849 |     6
        23 |          24 |           2242 | 48028 |    2254 |    2242 |         3 |       2254 |    12
        23 |          24 |           2243 | 48044 |    2254 |    2242 |        19 |       2247 |    12
        23 |          24 |           2243 | 48045 |    2254 |    2242 |        18 |       2243 |    12
        23 |          24 |           2244 | 48047 |    2254 |    2242 |        14 |       2244 |    12
...


Same in pg18_beta1

(only wrong result shown)

-- ERROR in column max_fac

cpsdb=# select version();
                              version
--------------------------------------------------------------------
 PostgreSQL 18beta1 on x86_64-linux, compiled by gcc-15.1.1, 64-bit
(1 row)

cpsdb=#
select *
,max_fac-min_fac as delta
from (
select
 of_season
,am_fac_code
,of_fac_order_n
,id_of
,max(of_fac_order_n) over wfac as max_fac
,min(of_fac_order_n) over wfac as min_fac
,row_number () over wfac as facor_num
,lead(of_fac_order_n,1,of_fac_order_n) over wfac as next_facor
from or_followup
left join models on id_am=of_id_am
window wfac as (partition by of_season,am_fac_code order by of_fac_order_n,id_of)
)
where of_season=23 order by am_fac_code,of_fac_order_n,id_of;
 of_season | am_fac_code | of_fac_order_n | id_of | max_fac | min_fac | facor_num | next_facor | delta
-----------+-------------+----------------+-------+---------+---------+-----------+------------+-------
        23 |          13 |            845 | 48057 |     845 |     845 |         1 |        845 |     0
        23 |          13 |            845 | 48058 |     845 |     845 |         2 |        845 |     0
        23 |          13 |            845 | 48059 |     845 |     845 |         3 |        846 |     0
        23 |          13 |            846 | 48060 |     846 |     845 |         4 |        846 |     1
        23 |          13 |            846 | 48061 |     846 |     845 |         5 |        847 |     1
        23 |          13 |            847 | 48062 |     847 |     845 |         6 |        847 |     2
        23 |          13 |            847 | 48063 |     847 |     845 |         7 |        848 |     2
        23 |          13 |            848 | 48094 |     848 |     845 |         8 |        849 |     3
        23 |          13 |            849 | 48109 |     849 |     845 |         9 |        850 |     4
        23 |          13 |            850 | 48964 |     850 |     845 |        10 |        851 |     5
        23 |          13 |            851 | 49464 |     851 |     845 |        11 |        851 |     6
        23 |          24 |           2242 | 48028 |    2242 |    2242 |         1 |       2243 |     0
        23 |          24 |           2243 | 48044 |    2243 |    2242 |         2 |       2243 |     1
        23 |          24 |           2243 | 48045 |    2243 |    2242 |         3 |       2244 |     1
        23 |          24 |           2244 | 48047 |    2244 |    2242 |         4 |       2244 |     2
...













