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 <ojford@gmail.com>
Co-authored-by: Tatsuo Ishii <ishii@postgresql.org>
Reviewed-by: Krasiyan Andreev <krasiyan@gmail.com>
Reviewed-by: Andrew Gierth <andrew@tao11.riddles.org.uk>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: David Fetter <david@fetter.org>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com>
Reviewed-by: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com

2 files changed, 29 insertions, 19 deletions

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 @@
         </indexterm>
         <function>lag</function> ( <parameter>value</parameter> <type>anycompatible</type>
           <optional>, <parameter>offset</parameter> <type>integer</type>
-          <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+          <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
         <returnvalue>anycompatible</returnvalue>
        </para>
        <para>
@@ -165,7 +165,7 @@
         </indexterm>
         <function>lead</function> ( <parameter>value</parameter> <type>anycompatible</type>
           <optional>, <parameter>offset</parameter> <type>integer</type>
-          <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+          <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
         <returnvalue>anycompatible</returnvalue>
        </para>
        <para>
@@ -188,7 +188,7 @@
         <indexterm>
          <primary>first_value</primary>
         </indexterm>
-        <function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
+        <function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
         <returnvalue>anyelement</returnvalue>
        </para>
        <para>
@@ -202,7 +202,7 @@
         <indexterm>
          <primary>last_value</primary>
         </indexterm>
-        <function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
+        <function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
         <returnvalue>anyelement</returnvalue>
        </para>
        <para>
@@ -216,7 +216,7 @@
         <indexterm>
          <primary>nth_value</primary>
         </indexterm>
-        <function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> )
+        <function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> ) <optional> <parameter>null treatment</parameter> </optional>
         <returnvalue>anyelement</returnvalue>
        </para>
        <para>
@@ -265,18 +265,26 @@
    Other frame specifications can be used to obtain other effects.
   </para>
 
+  <para>
+   The <literal>null treatment</literal> option must be one of:
+<synopsis>
+  RESPECT NULLS
+  IGNORE NULLS
+</synopsis>
+   If unspecified, the default is <literal>RESPECT NULLS</literal> which includes NULL
+   values in any result calculation. <literal>IGNORE NULLS</literal> ignores NULL values.
+   This option is only allowed for the following functions: <function>lag</function>,
+   <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
+   <function>nth_value</function>.
+  </para>
+
   <note>
    <para>
-    The SQL standard defines a <literal>RESPECT NULLS</literal> or
-    <literal>IGNORE NULLS</literal> option for <function>lead</function>, <function>lag</function>,
-    <function>first_value</function>, <function>last_value</function>, and
-    <function>nth_value</function>.  This is not implemented in
-    <productname>PostgreSQL</productname>: the behavior is always the
-    same as the standard's default, namely <literal>RESPECT NULLS</literal>.
-    Likewise, the standard's <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
-    option for <function>nth_value</function> is not implemented: only the
-    default <literal>FROM FIRST</literal> behavior is supported.  (You can achieve
-    the result of <literal>FROM LAST</literal> by reversing the <literal>ORDER BY</literal>
+    The SQL standard defines a <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
+    option for <function>nth_value</function>. This is not implemented in
+    <productname>PostgreSQL</productname>: only the default <literal>FROM FIRST</literal>
+    behavior is supported. (You can achieve the result of <literal>FROM LAST</literal> by
+    reversing the <literal>ORDER BY</literal>
     ordering.)
    </para>
   </note>
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:
 
 <synopsis>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) <optional>null treatment</optional> [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) <optional>null treatment</optional> [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
 <replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
 <replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
 </synopsis>
@@ -1873,7 +1873,9 @@ EXCLUDE NO OTHERS
 
    <para>
     Here, <replaceable>expression</replaceable> 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 <literal>null treatment</literal> clause,
+    described in <xref linkend="functions-window"/>.
    </para>
 
    <para>
@@ -2048,7 +2050,7 @@ EXCLUDE NO OTHERS
 
    <para>
     The built-in window functions are described in <xref
-    linkend="functions-window-table"/>.  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.)