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 --- doc/src/sgml/func/func-window.sgml | 38 +++++++++++++++++++++++--------------- 1 file changed, 23 insertions(+), 15 deletions(-) (limited to 'doc/src/sgml/func') diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml index cce0165b952..bcf755c9ebc 100644 --- a/doc/src/sgml/func/func-window.sgml +++ b/doc/src/sgml/func/func-window.sgml @@ -140,7 +140,7 @@ lag ( value anycompatible , offset integer - , default anycompatible ) + , default anycompatible ) null treatment anycompatible @@ -165,7 +165,7 @@ lead ( value anycompatible , offset integer - , default anycompatible ) + , default anycompatible ) null treatment anycompatible @@ -188,7 +188,7 @@ first_value - first_value ( value anyelement ) + first_value ( value anyelement ) null treatment anyelement @@ -202,7 +202,7 @@ last_value - last_value ( value anyelement ) + last_value ( value anyelement ) null treatment anyelement @@ -216,7 +216,7 @@ nth_value - nth_value ( value anyelement, n integer ) + nth_value ( value anyelement, n integer ) null treatment anyelement @@ -265,18 +265,26 @@ Other frame specifications can be used to obtain other effects. + + The null treatment option must be one of: + + RESPECT NULLS + IGNORE NULLS + + If unspecified, the default is RESPECT NULLS which includes NULL + values in any result calculation. IGNORE NULLS ignores NULL values. + This option is only allowed for the following functions: lag, + lead, first_value, last_value, + nth_value. + + - The SQL standard defines a RESPECT NULLS or - IGNORE NULLS option for lead, lag, - first_value, last_value, and - nth_value. This is not implemented in - PostgreSQL: the behavior is always the - same as the standard's default, namely RESPECT NULLS. - Likewise, the standard's FROM FIRST or FROM LAST - option for nth_value is not implemented: only the - default FROM FIRST behavior is supported. (You can achieve - the result of FROM LAST by reversing the ORDER BY + The SQL standard defines a FROM FIRST or FROM LAST + option for nth_value. This is not implemented in + PostgreSQL: only the default FROM FIRST + behavior is supported. (You can achieve the result of FROM LAST by + reversing the ORDER BY ordering.) -- cgit v1.2.3