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 +++++++++++++++++++++++--------------- doc/src/sgml/syntax.sgml | 10 ++++++---- 2 files changed, 29 insertions(+), 19 deletions(-) (limited to 'doc/src') 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.) diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 916189a7d68..237d7306fe8 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1834,8 +1834,8 @@ FROM generate_series(1,10) AS s(i); The syntax of a window function call is one of the following: -function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name -function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) +function_name (expression , expression ... ) null treatment [ FILTER ( WHERE filter_clause ) ] OVER window_name +function_name (expression , expression ... ) null treatment [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) @@ -1873,7 +1873,9 @@ EXCLUDE NO OTHERS Here, expression represents any value - expression that does not itself contain window function calls. + expression that does not itself contain window function calls. Some + non-aggregate functions allow a null treatment clause, + described in . @@ -2048,7 +2050,7 @@ EXCLUDE NO OTHERS The built-in window functions are described in . Other window functions can be added by + linkend="functions-window-table"/>. Other window functions can be added by the user. Also, any built-in or user-defined general-purpose or statistical aggregate can be used as a window function. (Ordered-set and hypothetical-set aggregates cannot presently be used as window functions.) -- cgit v1.2.3