From 25a30bbd4235a49c854036c84fe90f2bc5a87652 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Fri, 3 Oct 2025 09:47:36 +0900 Subject: Add IGNORE NULLS/RESPECT NULLS option to Window functions. Add IGNORE NULLS/RESPECT NULLS option (null treatment clause) to lead, lag, first_value, last_value and nth_value window functions. If unspecified, the default is RESPECT NULLS which includes NULL values in any result calculation. IGNORE NULLS ignores NULL values. Built-in window functions are modified to call new API WinCheckAndInitializeNullTreatment() to indicate whether they accept IGNORE NULLS/RESPECT NULLS option or not (the API can be called by user defined window functions as well). If WinGetFuncArgInPartition's allowNullTreatment argument is true and IGNORE NULLS option is given, WinGetFuncArgInPartition() or WinGetFuncArgInFrame() will return evaluated function's argument expression on specified non NULL row (if it exists) in the partition or the frame. When IGNORE NULLS option is given, window functions need to visit and evaluate same rows over and over again to look for non null rows. To mitigate the issue, 2-bit not null information array is created while executing window functions to remember whether the row has been already evaluated to NULL or NOT NULL. If already evaluated, we could skip the evaluation work, thus we could get better performance. Author: Oliver Ford Co-authored-by: Tatsuo Ishii Reviewed-by: Krasiyan Andreev Reviewed-by: Andrew Gierth Reviewed-by: Tom Lane Reviewed-by: David Fetter Reviewed-by: Vik Fearing Reviewed-by: "David G. Johnston" Reviewed-by: Chao Li Discussion: https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com --- src/backend/utils/adt/windowfuncs.c | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'src/backend/utils/adt/windowfuncs.c') diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c index bb35f3bc4a9..969f02aa59b 100644 --- a/src/backend/utils/adt/windowfuncs.c +++ b/src/backend/utils/adt/windowfuncs.c @@ -86,6 +86,7 @@ window_row_number(PG_FUNCTION_ARGS) WindowObject winobj = PG_WINDOW_OBJECT(); int64 curpos = WinGetCurrentPosition(winobj); + WinCheckAndInitializeNullTreatment(winobj, false, fcinfo); WinSetMarkPosition(winobj, curpos); PG_RETURN_INT64(curpos + 1); } @@ -141,6 +142,7 @@ window_rank(PG_FUNCTION_ARGS) rank_context *context; bool up; + WinCheckAndInitializeNullTreatment(winobj, false, fcinfo); up = rank_up(winobj); context = (rank_context *) WinGetPartitionLocalMemory(winobj, sizeof(rank_context)); @@ -203,6 +205,7 @@ window_dense_rank(PG_FUNCTION_ARGS) rank_context *context; bool up; + WinCheckAndInitializeNullTreatment(winobj, false, fcinfo); up = rank_up(winobj); context = (rank_context *) WinGetPartitionLocalMemory(winobj, sizeof(rank_context)); @@ -266,6 +269,7 @@ window_percent_rank(PG_FUNCTION_ARGS) int64 totalrows = WinGetPartitionRowCount(winobj); Assert(totalrows > 0); + WinCheckAndInitializeNullTreatment(winobj, false, fcinfo); up = rank_up(winobj); context = (rank_context *) @@ -335,6 +339,7 @@ window_cume_dist(PG_FUNCTION_ARGS) int64 totalrows = WinGetPartitionRowCount(winobj); Assert(totalrows > 0); + WinCheckAndInitializeNullTreatment(winobj, false, fcinfo); up = rank_up(winobj); context = (rank_context *) @@ -413,6 +418,7 @@ window_ntile(PG_FUNCTION_ARGS) WindowObject winobj = PG_WINDOW_OBJECT(); ntile_context *context; + WinCheckAndInitializeNullTreatment(winobj, false, fcinfo); context = (ntile_context *) WinGetPartitionLocalMemory(winobj, sizeof(ntile_context)); @@ -535,6 +541,7 @@ leadlag_common(FunctionCallInfo fcinfo, bool isnull; bool isout; + WinCheckAndInitializeNullTreatment(winobj, true, fcinfo); if (withoffset) { offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); @@ -652,6 +659,7 @@ window_first_value(PG_FUNCTION_ARGS) Datum result; bool isnull; + WinCheckAndInitializeNullTreatment(winobj, true, fcinfo); result = WinGetFuncArgInFrame(winobj, 0, 0, WINDOW_SEEK_HEAD, true, &isnull, NULL); @@ -673,6 +681,7 @@ window_last_value(PG_FUNCTION_ARGS) Datum result; bool isnull; + WinCheckAndInitializeNullTreatment(winobj, true, fcinfo); result = WinGetFuncArgInFrame(winobj, 0, 0, WINDOW_SEEK_TAIL, true, &isnull, NULL); @@ -696,6 +705,7 @@ window_nth_value(PG_FUNCTION_ARGS) bool isnull; int32 nth; + WinCheckAndInitializeNullTreatment(winobj, true, fcinfo); nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull)); if (isnull) PG_RETURN_NULL(); -- cgit v1.2.3