postgres_max_window_function_error.sql
application/octet-stream
Filename: postgres_max_window_function_error.sql
Type: application/octet-stream
Part: 0
-- 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
...