Subject: Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 09 Mar 2025 15:39:57 +0900 (JST)

> Attached version removes the non-nulls array. That seems to speed
> everything up.  Running the above query with 1 million rows averages 450ms,
> similar when using lead/lag.

Great. However, CFbot complains about the patch:

https://cirrus-ci.com/task/6364194477441024

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Oliver Ford <[email-masked]>
Date: Sun, 9 Mar 2025 20:07:33 +0000

> https://cirrus-ci.com/task/6364194477441024
Attached fixes the headerscheck locally.

---

From: Oliver Ford <[email-masked]>
Date: Thu, 13 Mar 2025 07:49:03 +0000

> Attached fixes the headerscheck locally.

v11 attached because the previous version was broken by commit 8b1b342

From bf4ddaa83c1004a96471106babf6a06022c4228c Mon Sep 17 00:00:00 2001
From: Oliver Ford <[email-masked]>
Date: Wed, 12 Mar 2025 13:34:30 +0000
Subject: [PATCH] ignore nulls

---
 doc/src/sgml/func.sgml               |  38 ++--
 doc/src/sgml/syntax.sgml             |  10 +-
 src/backend/catalog/sql_features.txt |   2 +-
 src/backend/executor/nodeWindowAgg.c | 225 ++++++++++++++++++-
 src/backend/optimizer/util/clauses.c |   1 +
 src/backend/parser/gram.y            |  19 +-
 src/backend/parser/parse_func.c      |   9 +
 src/backend/utils/adt/ruleutils.c    |   7 +-
 src/backend/utils/adt/windowfuncs.c  |  10 +
 src/include/nodes/parsenodes.h       |   1 +
 src/include/nodes/primnodes.h        |  13 ++
 src/include/parser/kwlist.h          |   2 +
 src/include/windowapi.h              |   6 +
 src/test/regress/expected/window.out | 311 +++++++++++++++++++++++++++
 src/test/regress/sql/window.sql      | 147 +++++++++++++
 15 files changed, 773 insertions(+), 28 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 51dd8ad6571..d8cf8b039d5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23442,7 +23442,7 @@ SELECT count(*) FROM sometable;
         </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>
@@ -23467,7 +23467,7 @@ SELECT count(*) FROM sometable;
         </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>
@@ -23490,7 +23490,7 @@ SELECT count(*) FROM sometable;
         <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>
@@ -23504,7 +23504,7 @@ SELECT count(*) FROM sometable;
         <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>
@@ -23518,7 +23518,7 @@ SELECT count(*) FROM sometable;
         <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>
@@ -23567,18 +23567,26 @@ SELECT count(*) FROM sometable;
    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.)
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 2f250d2c57b..46a8959cb2f 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -518,7 +518,7 @@ T612	Advanced OLAP operations			YES
 T613	Sampling			YES	
 T614	NTILE function			YES	
 T615	LEAD and LAG functions			YES	
-T616	Null treatment option for LEAD and LAG functions			NO	
+T616	Null treatment option for LEAD and LAG functions			YES	
 T617	FIRST_VALUE and LAST_VALUE functions			YES	
 T618	NTH_VALUE function			NO	function exists, but some options missing
 T619	Nested window functions			NO	
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 9a1acce2b5d..149333bb687 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -69,6 +69,7 @@ typedef struct WindowObjectData
 	int			readptr;		/* tuplestore read pointer for this fn */
 	int64		markpos;		/* row that markptr is positioned on */
 	int64		seekpos;		/* row that readptr is positioned on */
+	int			ignore_nulls;	/* ignore nulls */
 } WindowObjectData;
 
 /*
@@ -96,6 +97,7 @@ typedef struct WindowStatePerFuncData
 
 	bool		plain_agg;		/* is it just a plain aggregate function? */
 	int			aggno;			/* if so, index of its WindowStatePerAggData */
+	int			ignore_nulls;	/* ignore nulls */
 
 	WindowObject winobj;		/* object used in window function API */
 }			WindowStatePerFuncData;
@@ -198,6 +200,13 @@ static bool are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
 static bool window_gettupleslot(WindowObject winobj, int64 pos,
 								TupleTableSlot *slot);
 
+static Datum ignorenulls_getfuncarginpartition(WindowObject winobj, int argno,
+											   int relpos, int seektype,
+											   bool *isnull, bool *isout);
+static Datum ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
+										   int relpos, int seektype,
+										   bool set_mark,  bool *isnull,
+										   bool *isout);
 
 /*
  * initialize_windowaggregate
@@ -2619,14 +2628,17 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 			elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winref %u",
 				 wfunc->winref, node->winref);
 
-		/* Look for a previous duplicate window function */
+		/*
+		 * Look for a previous duplicate window function, which needs the same
+		 * ignore_nulls value
+		 */
 		for (i = 0; i <= wfuncno; i++)
 		{
 			if (equal(wfunc, perfunc[i].wfunc) &&
 				!contain_volatile_functions((Node *) wfunc))
 				break;
 		}
-		if (i <= wfuncno)
+		if (i <= wfuncno && wfunc->ignore_nulls == perfunc[i].ignore_nulls)
 		{
 			/* Found a match to an existing entry, so just mark it */
 			wfuncstate->wfuncno = i;
@@ -2679,6 +2691,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 			winobj->argstates = wfuncstate->args;
 			winobj->localmem = NULL;
 			perfuncstate->winobj = winobj;
+			winobj->ignore_nulls = wfunc->ignore_nulls;
 
 			/* It's a real window function, so set up to call it. */
 			fmgr_info_cxt(wfunc->winfnoid, &perfuncstate->flinfo,
@@ -3214,12 +3227,212 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
 	return true;
 }
 
+/*
+ * ignorenulls_getfuncarginpartition
+ * For IGNORE NULLS, get the next nonnull value in the partition, moving forward or backward
+ * until we find a value or reach the partition's end.
+ */
+static Datum
+ignorenulls_getfuncarginpartition(WindowObject winobj, int argno,
+								  int relpos, int seektype, bool *isnull,
+								  bool *isout)
+{
+	WindowAggState *winstate;
+	ExprContext *econtext;
+	TupleTableSlot *slot;
+	Datum		datum;
+	bool		gottuple;
+	int64		abs_pos;
+	int			notnull_offset;
+	int			notnull_relpos;
+	int			forward;
+
+	Assert(WindowObjectIsValid(winobj));
+	winstate = winobj->winstate;
+	econtext = winstate->ss.ps.ps_ExprContext;
+	slot = winstate->temp_slot_1;
+	notnull_offset = 0;
+	notnull_relpos = abs(relpos);
+	forward = relpos > 0 ? 1 : -1;
+
+	switch (seektype)
+	{
+		case WINDOW_SEEK_CURRENT:
+			abs_pos = winstate->currentpos;
+			break;
+		case WINDOW_SEEK_HEAD:
+			abs_pos = 0;
+			break;
+		case WINDOW_SEEK_TAIL:
+			spool_tuples(winstate, -1);
+			abs_pos = winstate->spooled_rows - 1 + relpos;
+			break;
+		default:
+			elog(ERROR, "unrecognized window seek type: %d", seektype);
+			abs_pos = 0;		/* keep compiler quiet */
+			break;
+	}
+
+	do
+	{
+		abs_pos += forward;
+		gottuple = window_gettupleslot(winobj, abs_pos, slot);
+
+		if (!gottuple)
+		{
+			if (isout)
+				*isout = true;
+			*isnull = true;
+			return (Datum) 0;
+		}
+
+		if (isout)
+			*isout = false;
+		econtext->ecxt_outertuple = slot;
+		datum = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+							 econtext, isnull);
+
+		if (!*isnull)
+			++notnull_offset;
+	} while (notnull_offset < notnull_relpos);
+
+	return datum;
+}
+
+/*
+ * ignorenulls_getfuncarginframe
+ * For IGNORE NULLS, get the next nonnull value in the frame, moving forward or backward
+ * until we find a value or reach the frame's end.
+ */
+static Datum
+ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
+							  int relpos, int seektype, bool set_mark,
+							  bool *isnull, bool *isout)
+{
+	WindowAggState *winstate;
+	ExprContext *econtext;
+	TupleTableSlot *slot;
+	Datum		datum;
+	int64		abs_pos;
+	int64		mark_pos;
+	int			notnull_offset;
+	int			notnull_relpos;
+	int			forward;
+
+	Assert(WindowObjectIsValid(winobj));
+	winstate = winobj->winstate;
+	econtext = winstate->ss.ps.ps_ExprContext;
+	slot = winstate->temp_slot_1;
+	datum = (Datum) 0;
+	notnull_offset = 0;
+	notnull_relpos = abs(relpos);
+
+	switch (seektype)
+	{
+		case WINDOW_SEEK_CURRENT:
+			elog(ERROR, "WINDOW_SEEK_CURRENT is not supported for WinGetFuncArgInFrame");
+			abs_pos = mark_pos = 0; /* keep compiler quiet */
+			break;
+		case WINDOW_SEEK_HEAD:
+			/* rejecting relpos < 0 is easy and simplifies code below */
+			if (relpos < 0)
+				goto out_of_frame;
+			update_frameheadpos(winstate);
+			abs_pos = winstate->frameheadpos;
+			mark_pos = winstate->frameheadpos;
+			forward = 1;
+			break;
+		case WINDOW_SEEK_TAIL:
+			/* rejecting relpos > 0 is easy and simplifies code below */
+			if (relpos > 0)
+				goto out_of_frame;
+			update_frametailpos(winstate);
+			abs_pos = winstate->frametailpos - 1;
+			mark_pos = 0; /* keep compiler quiet */
+			forward = -1;
+			break;
+		default:
+			elog(ERROR, "unrecognized window seek type: %d", seektype);
+			abs_pos = mark_pos = 0; /* keep compiler quiet */
+			break;
+	}
+
+	do
+	{
+		int			inframe;
+
+		if (!window_gettupleslot(winobj, abs_pos, slot))
+			goto out_of_frame;
+
+		inframe = row_is_in_frame(winstate, abs_pos, slot);
+		if (inframe == -1)
+			goto out_of_frame;
+		else if (inframe == 0)
+			goto advance;
+
+		if (isout)
+			*isout = false;
+		econtext->ecxt_outertuple = slot;
+		datum = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+							 econtext, isnull);
+
+		if (!*isnull)
+			++notnull_offset;
+
+advance:
+		abs_pos += forward;
+	} while (notnull_offset <= notnull_relpos);
+
+	if (set_mark)
+		WinSetMarkPosition(winobj, mark_pos);
+
+	return datum;
+
+out_of_frame:
+	if (isout)
+		*isout = true;
+	*isnull = true;
+	return (Datum) 0;
+}
+
 
 /***********************************************************************
  * API exposed to window functions
  ***********************************************************************/

+/*
+ * WinCheckAndInitializeNullTreatment
+ *		Check null treatment clause and sets ignore_nulls
+ *
+ * Window functions should call this to check if they are being called with
+ * a null treatment clause when they don't allow it, or to set ignore_nulls.
+ */
+void
+WinCheckAndInitializeNullTreatment(WindowObject winobj,
+								   bool allowNullTreatment,
+								   FunctionCallInfo fcinfo)
+{
+	if (winobj->ignore_nulls != NO_NULLTREATMENT && !allowNullTreatment)
+	{
+		HeapTuple	proctup;
+		Form_pg_proc procform;
+		Oid			funcid;
+
+		funcid = fcinfo->flinfo->fn_oid;
+		proctup = SearchSysCache1(PROCOID,
+								  ObjectIdGetDatum(funcid));
+		if (!HeapTupleIsValid(proctup))
+			elog(ERROR, "cache lookup failed for function %u", funcid);
+		procform = (Form_pg_proc) GETSTRUCT(proctup);
+		elog(ERROR, "function %s does not allow RESPECT/IGNORE NULLS",
+			 NameStr(procform->proname));
+	}
+	else if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
+		winobj->ignore_nulls = IGNORE_NULLS;
+
+}
+
 /*
  * WinGetPartitionLocalMemory
  *		Get working memory that lives till end of partition processing
@@ -3388,6 +3601,10 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
 	econtext = winstate->ss.ps.ps_ExprContext;
 	slot = winstate->temp_slot_1;
 
+	if (winobj->ignore_nulls == IGNORE_NULLS && relpos != 0)
+		return ignorenulls_getfuncarginpartition(winobj, argno, relpos, seektype,
+												 isnull, isout);
+
 	switch (seektype)
 	{
 		case WINDOW_SEEK_CURRENT:
@@ -3476,6 +3693,10 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
 	econtext = winstate->ss.ps.ps_ExprContext;
 	slot = winstate->temp_slot_1;
 
+	if (winobj->ignore_nulls == IGNORE_NULLS)
+		return ignorenulls_getfuncarginframe(winobj, argno, relpos, seektype,
+											 set_mark, isnull, isout);
+
 	switch (seektype)
 	{
 		case WINDOW_SEEK_CURRENT:
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47f..e7091d7468c 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2570,6 +2570,7 @@ eval_const_expressions_mutator(Node *node,
 				newexpr->winref = expr->winref;
 				newexpr->winstar = expr->winstar;
 				newexpr->winagg = expr->winagg;
+				newexpr->ignore_nulls = expr->ignore_nulls;
 				newexpr->location = expr->location;
 
 				return (Node *) newexpr;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 271ae26cbaf..19b1e61d10b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -632,7 +632,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	window_clause window_definition_list opt_partition_clause
 %type <windef>	window_definition over_clause window_specification
 				opt_frame_clause frame_extent frame_bound
-%type <ival>	opt_window_exclusion_clause
+%type <ival>	null_treatment opt_window_exclusion_clause
 %type <str>		opt_existing_window_name
 %type <boolean> opt_if_not_exists
 %type <boolean> opt_unique_null_treatment
@@ -730,7 +730,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	HANDLER HAVING HEADER_P HOLD HOUR_P
 
-	IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
+	IDENTITY_P IF_P IGNORE_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
 	INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
 	INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
 	INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -765,7 +765,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
 	REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
-	RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+	RESET RESPECT_P RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
 	ROUTINE ROUTINES ROW ROWS RULE
 
 	SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
@@ -15758,7 +15758,7 @@ func_application: func_name '(' ')'
  * (Note that many of the special SQL functions wouldn't actually make any
  * sense as functional index entries, but we ignore that consideration here.)
  */
-func_expr: func_application within_group_clause filter_clause over_clause
+func_expr: func_application within_group_clause filter_clause null_treatment over_clause
 				{
 					FuncCall   *n = (FuncCall *) $1;
 
@@ -15791,7 +15791,8 @@ func_expr: func_application within_group_clause filter_clause over_clause
 						n->agg_within_group = true;
 					}
 					n->agg_filter = $3;
-					n->over = $4;
+					n->ignore_nulls = $4;
+					n->over = $5;
 					$$ = (Node *) n;
 				}
 			| json_aggregate_func filter_clause over_clause
@@ -16387,6 +16388,12 @@ filter_clause:
 /*
  * Window Definitions
  */
+null_treatment:
+			IGNORE_P NULLS_P						{ $$ = PARSER_IGNORE_NULLS; }
+			| RESPECT_P NULLS_P						{ $$ = PARSER_RESPECT_NULLS; }
+			| /*EMPTY*/								{ $$ = NO_NULLTREATMENT; }
+		;
+
 window_clause:
 			WINDOW window_definition_list			{ $$ = $2; }
 			| /*EMPTY*/								{ $$ = NIL; }
@@ -17824,6 +17831,7 @@ unreserved_keyword:
 			| HOUR_P
 			| IDENTITY_P
 			| IF_P
+			| IGNORE_P
 			| IMMEDIATE
 			| IMMUTABLE
 			| IMPLICIT_P
@@ -17941,6 +17949,7 @@ unreserved_keyword:
 			| REPLACE
 			| REPLICA
 			| RESET
+			| RESPECT_P
 			| RESTART
 			| RESTRICT
 			| RETURN
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..3772c514b1e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 	bool		agg_star = (fn ? fn->agg_star : false);
 	bool		agg_distinct = (fn ? fn->agg_distinct : false);
 	bool		func_variadic = (fn ? fn->func_variadic : false);
+	int			ignore_nulls = (fn ? fn->ignore_nulls : 0);
 	CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
 	bool		could_be_projection;
 	Oid			rettype;
@@ -514,6 +515,13 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 						 errmsg("%s is not an ordered-set aggregate, so it cannot have WITHIN GROUP",
 								NameListToString(funcname)),
 						 parser_errposition(pstate, location)));
+
+			/* It also can't treat nulls as a window function */
+			if (ignore_nulls != NO_NULLTREATMENT)
+				ereport(ERROR,
+						(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+						 errmsg("aggregate functions do not accept RESPECT/IGNORE NULLS"),
+						 parser_errposition(pstate, location)));
 		}
 	}
 	else if (fdresult == FUNCDETAIL_WINDOWFUNC)
@@ -834,6 +842,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
 		wfunc->winstar = agg_star;
 		wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
 		wfunc->aggfilter = agg_filter;
+		wfunc->ignore_nulls = ignore_nulls;
 		wfunc->runCondition = NIL;
 		wfunc->location = location;
 
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 9e90acedb91..ac97a35ad47 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11080,7 +11080,12 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
 		get_rule_expr((Node *) wfunc->aggfilter, context, false);
 	}
 
-	appendStringInfoString(buf, ") OVER ");
+	appendStringInfoString(buf, ") ");
+
+	if (wfunc->ignore_nulls == PARSER_IGNORE_NULLS)
+		appendStringInfoString(buf, "IGNORE NULLS ");
+
+	appendStringInfoString(buf, "OVER ");
 
 	if (context->windowClause)
 	{
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();
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 23c9e3c5abf..b13a7bfe674 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -439,6 +439,7 @@ typedef struct FuncCall
 	List	   *agg_order;		/* ORDER BY (list of SortBy) */
 	Node	   *agg_filter;		/* FILTER clause, if any */
 	struct WindowDef *over;		/* OVER clause, if any */
+	int			ignore_nulls;	/* ignore nulls for window function */
 	bool		agg_within_group;	/* ORDER BY appeared in WITHIN GROUP */
 	bool		agg_star;		/* argument was really '*' */
 	bool		agg_distinct;	/* arguments were labeled DISTINCT */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d0576da3e25..65081616402 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -577,6 +577,17 @@ typedef struct GroupingFunc
  * Collation information is irrelevant for the query jumbling, as is the
  * internal state information of the node like "winstar" and "winagg".
  */
+
+/*
+ * Null Treatment options. If specified, initially set to PARSER_IGNORE_NULLS
+ * which is then converted to IGNORE_NULLS if the window function allows the
+ * null treatment clause.
+ */
+#define NO_NULLTREATMENT 0
+#define PARSER_IGNORE_NULLS 1
+#define PARSER_RESPECT_NULLS 2
+#define IGNORE_NULLS 3
+
 typedef struct WindowFunc
 {
 	Expr		xpr;
@@ -600,6 +611,8 @@ typedef struct WindowFunc
 	bool		winstar pg_node_attr(query_jumble_ignore);
 	/* is function a simple aggregate? */
 	bool		winagg pg_node_attr(query_jumble_ignore);
+	/* ignore nulls. One of the Null Treatment options */
+	int			ignore_nulls;
 	/* token location, or -1 if unknown */
 	ParseLoc	location;
 } WindowFunc;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 40cf090ce61..3ba00a39e5d 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -202,6 +202,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -377,6 +378,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("respect", RESPECT_P, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/windowapi.h b/src/include/windowapi.h
index cb2ece166b6..20cfd9e9dd9 100644
--- a/src/include/windowapi.h
+++ b/src/include/windowapi.h
@@ -28,6 +28,8 @@
 #ifndef WINDOWAPI_H
 #define WINDOWAPI_H
 
+#include "fmgr.h"
+
 /* values of "seektype" */
 #define WINDOW_SEEK_CURRENT 0
 #define WINDOW_SEEK_HEAD 1
@@ -41,6 +43,10 @@ typedef struct WindowObjectData *WindowObject;
 #define WindowObjectIsValid(winobj) \
 	((winobj) != NULL && IsA(winobj, WindowObjectData))
 
+extern void WinCheckAndInitializeNullTreatment(WindowObject winobj,
+											   bool allowNullTreatment,
+											   FunctionCallInfo fcinfo);
+
 extern void *WinGetPartitionLocalMemory(WindowObject winobj, Size sz);
 
 extern int64 WinGetCurrentPosition(WindowObject winobj);
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index b86b668f433..f929d81bc8a 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5453,3 +5453,314 @@ SELECT * FROM pg_temp.f(2);
  {5}
 (5 rows)
 
+-- IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+    name text,
+    distance text,
+    orbit integer
+);
+INSERT INTO planets VALUES
+  ('mercury', 'close', 88),
+  ('venus', 'close', 224),
+  ('earth', 'close', NULL),
+  ('mars', 'close', NULL),
+  ('jupiter', 'close', 4332),
+  ('saturn', 'far', 24491),
+  ('uranus', 'far', NULL),
+  ('neptune', 'far', 60182),
+  ('pluto', 'far', 90560),
+  ('xyzzy', 'far', NULL);
+-- test ruleutils
+CREATE VIEW planets_view AS
+SELECT name,
+       orbit,
+       lag(orbit) OVER w AS lag,
+       lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+       lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+NOTICE:  view "planets_view" will be a temporary view
+SELECT pg_get_viewdef('planets_view');
+                  pg_get_viewdef                  
+--------------------------------------------------
+  SELECT name,                                   +
+     orbit,                                      +
+     lag(orbit) OVER w AS lag,                   +
+     lag(orbit) OVER w AS lag_respect,           +
+     lag(orbit) IGNORE NULLS OVER w AS lag_ignore+
+    FROM planets                                 +
+   WINDOW w AS (ORDER BY name);
+(1 row)
+
+-- lag
+SELECT name,
+       orbit,
+       lag(orbit) OVER w AS lag,
+       lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+       lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+  name   | orbit |  lag  | lag_respect | lag_ignore 
+---------+-------+-------+-------------+------------
+ earth   |       |       |             |           
+ jupiter |  4332 |       |             |           
+ mars    |       |  4332 |        4332 |       4332
+ mercury |    88 |       |             |       4332
+ neptune | 60182 |    88 |          88 |         88
+ pluto   | 90560 | 60182 |       60182 |      60182
+ saturn  | 24491 | 90560 |       90560 |      90560
+ uranus  |       | 24491 |       24491 |      24491
+ venus   |   224 |       |             |      24491
+ xyzzy   |       |   224 |         224 |        224
+(10 rows)
+
+-- lead
+SELECT name,
+       orbit,
+       lead(orbit) OVER w AS lead,
+       lead(orbit) RESPECT NULLS OVER w AS lead_respect,
+       lead(orbit) IGNORE NULLS OVER w AS lead_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+  name   | orbit | lead  | lead_respect | lead_ignore 
+---------+-------+-------+--------------+-------------
+ earth   |       |  4332 |         4332 |        4332
+ jupiter |  4332 |       |              |          88
+ mars    |       |    88 |           88 |          88
+ mercury |    88 | 60182 |        60182 |       60182
+ neptune | 60182 | 90560 |        90560 |       90560
+ pluto   | 90560 | 24491 |        24491 |       24491
+ saturn  | 24491 |       |              |         224
+ uranus  |       |   224 |          224 |         224
+ venus   |   224 |       |              |            
+ xyzzy   |       |       |              |            
+(10 rows)
+
+-- first_value
+SELECT name,
+       orbit,
+       first_value(orbit) RESPECT NULLS OVER w1,
+       first_value(orbit) IGNORE NULLS OVER w1,
+       first_value(orbit) RESPECT NULLS OVER w2,
+       first_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+  name   | orbit | first_value | first_value | first_value | first_value 
+---------+-------+-------------+-------------+-------------+-------------
+ earth   |       |             |        4332 |             |        4332
+ jupiter |  4332 |             |        4332 |             |        4332
+ mars    |       |             |        4332 |             |        4332
+ mercury |    88 |             |        4332 |        4332 |        4332
+ neptune | 60182 |             |        4332 |             |          88
+ pluto   | 90560 |             |        4332 |          88 |          88
+ saturn  | 24491 |             |        4332 |       60182 |       60182
+ uranus  |       |             |        4332 |       90560 |       90560
+ venus   |   224 |             |        4332 |       24491 |       24491
+ xyzzy   |       |             |        4332 |             |         224
+(10 rows)
+
+-- nth_value
+SELECT name,
+       orbit,
+       nth_value(orbit, 2) RESPECT NULLS OVER w1,
+       nth_value(orbit, 2) IGNORE NULLS OVER w1,
+       nth_value(orbit, 2) RESPECT NULLS OVER w2,
+       nth_value(orbit, 2) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+  name   | orbit | nth_value | nth_value | nth_value | nth_value 
+---------+-------+-----------+-----------+-----------+-----------
+ earth   |       |      4332 |        88 |      4332 |          
+ jupiter |  4332 |      4332 |        88 |      4332 |        88
+ mars    |       |      4332 |        88 |      4332 |        88
+ mercury |    88 |      4332 |        88 |           |        88
+ neptune | 60182 |      4332 |        88 |        88 |     60182
+ pluto   | 90560 |      4332 |        88 |     60182 |     60182
+ saturn  | 24491 |      4332 |        88 |     90560 |     90560
+ uranus  |       |      4332 |        88 |     24491 |     24491
+ venus   |   224 |      4332 |        88 |           |       224
+ xyzzy   |       |      4332 |        88 |       224 |          
+(10 rows)
+
+-- last_value
+SELECT name,
+       orbit,
+       last_value(orbit) RESPECT NULLS OVER w1,
+       last_value(orbit) IGNORE NULLS OVER w1,
+       last_value(orbit) RESPECT NULLS OVER w2,
+       last_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+  name   | orbit | last_value | last_value | last_value | last_value 
+---------+-------+------------+------------+------------+------------
+ earth   |       |            |        224 |            |       4332
+ jupiter |  4332 |            |        224 |         88 |         88
+ mars    |       |            |        224 |      60182 |      60182
+ mercury |    88 |            |        224 |      90560 |      90560
+ neptune | 60182 |            |        224 |      24491 |      24491
+ pluto   | 90560 |            |        224 |            |      24491
+ saturn  | 24491 |            |        224 |        224 |        224
+ uranus  |       |            |        224 |            |        224
+ venus   |   224 |            |        224 |            |        224
+ xyzzy   |       |            |        224 |            |        224
+(10 rows)
+
+-- exclude current row
+SELECT name,
+       orbit,
+       first_value(orbit) IGNORE NULLS OVER w,
+       last_value(orbit) IGNORE NULLS OVER w,
+       nth_value(orbit, 2) IGNORE NULLS OVER w,
+       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
+;
+  name   | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore 
+---------+-------+-------------+------------+-----------+-------------+------------
+ earth   |       |        4332 |       4332 |           |        4332 |           
+ jupiter |  4332 |          88 |         88 |           |          88 |           
+ mars    |       |        4332 |      60182 |        88 |          88 |       4332
+ mercury |    88 |        4332 |      90560 |     60182 |       60182 |       4332
+ neptune | 60182 |          88 |      24491 |     90560 |       90560 |         88
+ pluto   | 90560 |          88 |      24491 |     60182 |       24491 |      60182
+ saturn  | 24491 |       60182 |        224 |     90560 |         224 |      90560
+ uranus  |       |       90560 |        224 |     24491 |         224 |      24491
+ venus   |   224 |       24491 |      24491 |           |             |      24491
+ xyzzy   |       |         224 |        224 |           |             |        224
+(10 rows)
+
+-- valid and invalid functions
+SELECT sum(orbit) OVER () FROM planets; -- succeeds
+  sum   
+--------
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+(10 rows)
+
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+ERROR:  aggregate functions do not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT sum(orbit) RESPECT NULLS OVER () FROM planets;
+               ^
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+ERROR:  aggregate functions do not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT sum(orbit) IGNORE NULLS OVER () FROM planets;
+               ^
+SELECT row_number() OVER () FROM planets; -- succeeds
+ row_number 
+------------
+          1
+          2
+          3
+          4
+          5
+          6
+          7
+          8
+          9
+         10
+(10 rows)
+
+SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
+ERROR:  function row_number does not allow RESPECT/IGNORE NULLS
+SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails
+ERROR:  function row_number does not allow RESPECT/IGNORE NULLS
+-- test two consecutive nulls
+update planets set orbit=null where name='jupiter';
+SELECT name,
+       orbit,
+       first_value(orbit) IGNORE NULLS OVER w,
+       last_value(orbit) IGNORE NULLS OVER w,
+       nth_value(orbit, 2) IGNORE NULLS OVER w,
+       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+  name   | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore 
+---------+-------+-------------+------------+-----------+-------------+------------
+ earth   |       |             |            |           |          88 |           
+ jupiter |       |          88 |         88 |           |          88 |           
+ mars    |       |          88 |      60182 |     60182 |          88 |           
+ mercury |    88 |          88 |      90560 |     60182 |       60182 |           
+ neptune | 60182 |          88 |      24491 |     60182 |       90560 |         88
+ pluto   | 90560 |          88 |      24491 |     60182 |       24491 |      60182
+ saturn  | 24491 |       60182 |        224 |     90560 |         224 |      90560
+ uranus  |       |       90560 |        224 |     24491 |         224 |      24491
+ venus   |   224 |       24491 |        224 |       224 |             |      24491
+ xyzzy   |       |         224 |        224 |           |             |        224
+(10 rows)
+
+-- test partitions
+SELECT name,
+       distance,
+       orbit,
+       first_value(orbit) IGNORE NULLS OVER w,
+       last_value(orbit) IGNORE NULLS OVER w,
+       nth_value(orbit, 2) IGNORE NULLS OVER w,
+       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+  name   | distance | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore 
+---------+----------+-------+-------------+------------+-----------+-------------+------------
+ earth   | close    |       |             |            |           |          88 |           
+ jupiter | close    |       |          88 |         88 |           |          88 |           
+ mars    | close    |       |          88 |        224 |       224 |          88 |           
+ mercury | close    |    88 |          88 |        224 |       224 |         224 |           
+ venus   | close    |   224 |          88 |        224 |       224 |             |         88
+ neptune | far      | 60182 |       60182 |      24491 |     90560 |       90560 |           
+ pluto   | far      | 90560 |       60182 |      24491 |     90560 |       24491 |      60182
+ saturn  | far      | 24491 |       60182 |      24491 |     90560 |             |      90560
+ uranus  | far      |       |       90560 |      24491 |     24491 |             |      24491
+ xyzzy   | far      |       |       24491 |      24491 |           |             |      24491
+(10 rows)
+
+-- nth_value without nulls
+SELECT x,
+       nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
+ x | nth_value 
+---+-----------
+ 1 |         3
+ 2 |         3
+ 3 |         2
+ 4 |         3
+ 5 |         4
+(5 rows)
+
+SELECT x,
+       nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+ x | nth_value 
+---+-----------
+ 1 |         2
+ 2 |         2
+ 3 |         2
+ 4 |         3
+ 5 |         4
+(5 rows)
+
+--cleanup
+DROP TABLE planets CASCADE;
+NOTICE:  drop cascades to view planets_view
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 02f105f070e..1f8c8669436 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1958,3 +1958,150 @@ $$ LANGUAGE SQL STABLE;
 
 EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
 SELECT * FROM pg_temp.f(2);
+
+-- IGNORE NULLS tests
+
+CREATE TEMPORARY TABLE planets (
+    name text,
+    distance text,
+    orbit integer
+);
+
+INSERT INTO planets VALUES
+  ('mercury', 'close', 88),
+  ('venus', 'close', 224),
+  ('earth', 'close', NULL),
+  ('mars', 'close', NULL),
+  ('jupiter', 'close', 4332),
+  ('saturn', 'far', 24491),
+  ('uranus', 'far', NULL),
+  ('neptune', 'far', 60182),
+  ('pluto', 'far', 90560),
+  ('xyzzy', 'far', NULL);
+
+-- test ruleutils
+CREATE VIEW planets_view AS
+SELECT name,
+       orbit,
+       lag(orbit) OVER w AS lag,
+       lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+       lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+SELECT pg_get_viewdef('planets_view');
+
+-- lag
+SELECT name,
+       orbit,
+       lag(orbit) OVER w AS lag,
+       lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+       lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
+-- lead
+SELECT name,
+       orbit,
+       lead(orbit) OVER w AS lead,
+       lead(orbit) RESPECT NULLS OVER w AS lead_respect,
+       lead(orbit) IGNORE NULLS OVER w AS lead_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
+-- first_value
+SELECT name,
+       orbit,
+       first_value(orbit) RESPECT NULLS OVER w1,
+       first_value(orbit) IGNORE NULLS OVER w1,
+       first_value(orbit) RESPECT NULLS OVER w2,
+       first_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- nth_value
+SELECT name,
+       orbit,
+       nth_value(orbit, 2) RESPECT NULLS OVER w1,
+       nth_value(orbit, 2) IGNORE NULLS OVER w1,
+       nth_value(orbit, 2) RESPECT NULLS OVER w2,
+       nth_value(orbit, 2) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- last_value
+SELECT name,
+       orbit,
+       last_value(orbit) RESPECT NULLS OVER w1,
+       last_value(orbit) IGNORE NULLS OVER w1,
+       last_value(orbit) RESPECT NULLS OVER w2,
+       last_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- exclude current row
+SELECT name,
+       orbit,
+       first_value(orbit) IGNORE NULLS OVER w,
+       last_value(orbit) IGNORE NULLS OVER w,
+       nth_value(orbit, 2) IGNORE NULLS OVER w,
+       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
+;
+
+-- valid and invalid functions
+SELECT sum(orbit) OVER () FROM planets; -- succeeds
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+SELECT row_number() OVER () FROM planets; -- succeeds
+SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
+SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails
+
+-- test two consecutive nulls
+update planets set orbit=null where name='jupiter';
+SELECT name,
+       orbit,
+       first_value(orbit) IGNORE NULLS OVER w,
+       last_value(orbit) IGNORE NULLS OVER w,
+       nth_value(orbit, 2) IGNORE NULLS OVER w,
+       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- test partitions
+SELECT name,
+       distance,
+       orbit,
+       first_value(orbit) IGNORE NULLS OVER w,
+       last_value(orbit) IGNORE NULLS OVER w,
+       nth_value(orbit, 2) IGNORE NULLS OVER w,
+       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- nth_value without nulls
+SELECT x,
+       nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
+SELECT x,
+       nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+
+--cleanup
+DROP TABLE planets CASCADE;
-- 
2.43.0

---

From: Krasiyan Andreev <[email-masked]>
Date: Sat, 29 Mar 2025 09:51:55 +0200

Hi,
Patch applies and compiles, all included tests passed and after the latest
fixes for non-nulls array, performance is near to lead/lag without support
of "ignore nulls".
I have been using the last version for more than one month in a production
environment with real data and didn't find any bugs, so It is ready for
committer status.

---

From: Tatsuo Ishii <[email-masked]>
Date: Sat, 29 Mar 2025 20:18:09 +0900 (JST)

> environment with real data and didn't find any bugs, so It is ready for

One thing I worry about the patch is, now the non-nulls array
optimization was removed.  Since then I have been thinking about if
there could be other way to optimize searching for non null rows.

Best reagards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 12 Jun 2025 07:52:35 +0900 (JST)

> there could be other way to optimize searching for non null rows.

Here is the v12 patch to implement the optimization on top of Oliver's
v11 patch. Only src/backend/executor/nodeWindowAgg.c was modified
(especially ignorenulls_getfuncarginframe). In the patch I created
2-bit not null information array, representing following status for
each row:

UNKNOWN: the row is not determined whether it's NULL or NOT yet.
	 This is the initial value.
NULL: the row has been determined to be NULL.
NOT NULL: the row has been determined to be NOT NULL.

In ignorenulls_getfuncarginframe:

For the first time window function visits a row in a frame, the row is
fetched using window_gettupleslot() and it is checked whether it is in
the frame using row_is_in_frame(). If it's in the frame and the
information in the array is UNKNOWN, ExecEvalExpr() is executed to
find out if the expression on the function argument is NULL or
not. And the result (NULL or NOT NULL) is stored in the array.

If the information in the array is not UNKNOWN, we can skip calling
ExecEvalExpr() because the information is already in the array.

Note that I do not skip calling window_gettupleslot() and
row_is_in_frame(), skip only calling ExecEvalExpr(), because whether a
row is in a frame or not could be changing as the current row position
moves while processing window functions.

With this technique I observed around 40% speed up in my environment
using the script attached, comparing with Oliver's v11 patch.

v11:
rows	duration (msec)
1000	41.019 
2000	148.957 
3000	248.291 
4000	442.478 
5000	687.395

v12:
rows	duration (msec)
1000	27.515 
2000	78.913 
3000	174.737 
4000	311.412 
5000	482.156 

The patch is now generated using the standard git format-patch.  Also
I have slightly adjusted the coding style so that it aligns with the
one used in nodeWindowAgg.c, and ran pgindent.

Note that I have not modified ignorenulls_getfuncarginpartition yet. I
think we could optimize it using the not null info infrastructure as
well. Will come up with it.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
for i in 1000 2000 3000 4000 5000
do
    echo "$i rows: "
    pos=`expr $i / 2`
    psql -a test <<EOF
\timing
explain analyze
SELECT
  x,
nth_value(x,$pos) IGNORE NULLS OVER w
FROM generate_series(1,$i) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
EOF

done | egrep "rows:|Time:" | egrep -v "Planning|Execution"|
    sed -e 's/rows: *//' -e 's/Time: //' -e 's/ms//'

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 19 Jun 2025 15:21:11 +0900 (JST)

> using the script attached, comparing with Oliver's v11 patch.

Attached is the v13 patch to address this: i.e. optimize window
functions (lead/lag) working in a partition. In summary I get 40x
speed up comparing with v12 patch. Here is the test script.

EXPLAIN ANALYZE
SELECT lead(x, 5000) IGNORE NULLS OVER ()
              FROM generate_series(1,10000) g(x);

This looks for the 5k th row in a partition including 10k rows.
The average duration of 3 trials are:

v12: 2563.665 ms
v13: 126.259 ms

So I got 40x speed up with the v13 patch. In v12, we needed to scan
10k row partition over and over again. In v13 I used the same NULL/NOT
NULL cache infrastructure created in v12, and we need to scan the
partition only once. This is the reason for the speed up.

Also I removed ignorenulls_getfuncarginpartition(), which was the work
horse for null treatment for window functions working for partitions
in v12 patch. Basically it was a copy and modified version of
WinGetFuncArgInPartition(), thus had quite a few code duplication. In
v13, instead I modified WinGetFuncArgInPartition() so that it can
handle directly null treatment procedures.

BTW I am still not satisfied by the performance improvement for window
functions for frames, that was only 40%. I will study the code to look
for more optimization.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 25 Jun 2025 16:19:03 +0900 (JST)

> functions for frames, that was only 40%. I will study the code to look

So I come up with more optimization for window functions working on
frames (i.e. first_value, last_value and nth_value). Attached v14
patch does it.

There are 3 major functions used here.

1) window_gettupleslot (get a row)
2) row_is_in_frame (check whether row is in frame or not)
3) ExecEvalExpr (evaluate arg on the row)

In v12 (and v13), we eliminate #3 in some cases but the saving was
only 40%. In v14, I found some cases where we don't need to call
#1. row_is_in_frame requires a row ("tuple" argument), which is
provided by #1. However row_is_in_frame actually uses the row argument
only when frame clause is "RANGE" or "GROUPS" and frame end is
"CURRENT ROW". In other cases it does not use "tuple" argument at
all. So I check the frame clause and the frame end, and if they are
not the case, I can omit #1. Plus if the not null cache for the row
has been already created, we can omit #3 as well. The optimization
contributes to the performance. I observe 2.7x (1k rows case) to 5.2x
(3k rows case) speed up when I compare the performance of v13 patch
and v14 patch using the same script (see attached).

v13:
rows	duration (msec)
1000	34.740
2000	91.169
3000	205.847
4000	356.142
5000	557.063 

v14:
rows	duration (msec)
1000	12.807
2000	21.782
3000	39.248
4000 	69.123
5000 	101.220

I am not sure how the case where frame clause is "RANGE" or "GROUPS"
and frame end is "CURRENT ROW" is majority of window function use
cases. If it's majority, the optimization in v14 does not help much
because v14 does not optimize the case.  However if it's not, the v14
patch is close to commitable form, I think. Comments are welcome.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 30 Jun 2025 14:25:56 +0900 (JST)

Attached is the v15 patch to fix CFbot complains.
Other than that, nothing has been changed since v14.

Best regards,

---

From: Krasiyan Andreev <[email-masked]>
Date: Mon, 30 Jun 2025 11:08:26 +0300

Hi,
Patch applies and compiles, all included tests passed and performance gain
is really impressive. I have been using the latest versions for months with
real data and didn't find any bugs, so It is definitely ready for committer
status.

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 01 Jul 2025 09:19:06 +0900 (JST)

Krasiyan,

> real data and didn't find any bugs, so It is definitely ready for committer

Thanks for testing the patch. The CF status has been already set to
"ready for committer". I just changed the target version from 18 to
19.

> patch is close to commitable form, I think. Comments are welcome.

Have you tested cases where the frame option is "RANGE" or "GROUPS"
and the frame end is "CURRENT ROW"? I am asking because in these cases
the optimization in the v14 (and v15) patches do not apply and you may
not be satisfied by the performance.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 07 Jul 2025 14:37:37 +0900 (JST)

Attached is the v16 patch.

In this patch I have changed row_is_in_frame() API from:

static int	row_is_in_frame(WindowAggState *winstate, int64 pos,
							TupleTableSlot *slot);

to:

static int	row_is_in_frame(WindowObject winobj, int64 pos,
							TupleTableSlot *slot, bool fetch_tuple);

The function is used to decide whether a row specified by pos is in a
frame or not. Previously we needed to always pass "slot" parameter
which is the row in question, fetched by window_gettupleslot. If
IGNORE NULLS option is not passed to window functions, this is fine
because they need to return the row anyway.

However if IGNORE NULLS specified, we need to throw away null rows
until we find the non null row requested by the caller. In reality,
not in all window frames it is required to pass a row to
row_is_in_frame: only when specific frame options are specified. For
example RANGE or GROUP options plus CURRENT ROW frame end option. In
previous patch, I explicitly checked these frame options before
calling row_is_in_frame. However I dislike the way because it's a
layer abstraction violation.

So in this patch I added "fetch_tuple" option to row_is_in_frame so
that it fetches row itself when necessary. A caller now don't need to
fetch the row to pass if fetch_tuple is false.

This way, not only we can avoid the layer violation problem, but
performance is enhanced because tuple is fetched only when it's
necessary.

Note that now the first argument of row_is_in_frame has been changed
from WindowAggState to WindowObject so that row_is_in_frame can call
window_gettupleslot inside.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 16 Jul 2025 13:44:07 +0900 (JST)

Currently the patch set include some regression test additions. I
wanted to expand the test coverage and ended up an idea: generate new
test cases from the existing window function regression test
(window.sql).

Attached "insert_ignore_nulls.sh" script reads window.sql and inserts
"ignore nulls" before "over" clause of each window functions that
accept IGNORE NULLS option. This way, although the generated test
cases do not cover the case where NULL is included, at least covers
all non NULL cases, which is better than nothing, I think.

I replaced the existing window.sql with the modified one, and ran the
regression test. Indeed the test failed because expected file is for
non IGNORE NULLS options. However, the differences should be just for
SQL statements, not the output of the SQL statements since the data
set used does not include NULLs. I did an eyeball check the diff and
the result was what I expected.

For those who are interested this test, I attached some files.

insert_ignore_nulls.sh: shell script to insert "ignore nulls"
window.sql: modified regression script by insert_ignore_nulls.sh
window.diff: diff of original window.out and modified window.out

Question is, how could we put this kind of test into core if it worth
the effort? The simplest idea is just adding the modified window.sql
to the end of existing window.sql and update window.out. Thoughts?

Best regards,
#! /bin/sh

# Script to generate test cases for IGNORE NULLS option of some window
# functions (lead, lag, first_value, last_value, and nth_value) in the
# regression test SQL (window.sql).  This script supposed to read
# window.sql from stdin and inserts "ignore null" before "over" on all
# occurrences of the window functions that accept IGNORE NULLS, then
# print it to stdout.  Since currently the data set used for the
# existing regression test (window.sql) does not include NULLs, the
# test result using the results of this script is expected be
# identical to the existing results of existing window.sql (of course
# except the SQL statements).
sed -r \
    -e "s/lead\(([^)]*)\) over/lead(\1) ignore nulls over/g" \
    -e "s/lag\(([^)]*)\) over/lag(\1) ignore nulls over/g" \
    -e "s/first_value\(([^)]*)\) over/first_value(\1) ignore nulls over/g" \
    -e "s/last_value\(([^)]*)\) over/last_value(\1) ignore nulls over/g" \
    -e "s/nth_value\(([^)]*)\) over/nth_value(\1) ignore nulls over/g"
--
-- WINDOW FUNCTIONS
--

CREATE TEMPORARY TABLE empsalary (
    depname varchar,
    empno bigint,
    salary int,
    enroll_date date
);

INSERT INTO empsalary VALUES
('develop', 10, 5200, '2007-08-01'),
('sales', 1, 5000, '2006-10-01'),
('personnel', 5, 3500, '2007-12-10'),
('sales', 4, 4800, '2007-08-08'),
('personnel', 2, 3900, '2006-12-23'),
('develop', 7, 4200, '2008-01-01'),
('develop', 9, 4500, '2008-01-01'),
('sales', 3, 4800, '2007-08-01'),
('develop', 8, 6000, '2006-10-01'),
('develop', 11, 5200, '2007-08-15');

SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary;

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;

-- with GROUP BY
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
GROUP BY four, ten ORDER BY four, ten;

SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);

SELECT depname, empno, salary, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary) ORDER BY rank() OVER w;

-- empty window specification
SELECT COUNT(*) OVER () FROM tenk1 WHERE unique2 < 10;

SELECT COUNT(*) OVER w FROM tenk1 WHERE unique2 < 10 WINDOW w AS ();

-- no window operation
SELECT four FROM tenk1 WHERE FALSE WINDOW w AS (PARTITION BY ten);

-- cumulative aggregate
SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE unique2 < 10;

SELECT row_number() OVER (ORDER BY unique2) FROM tenk1 WHERE unique2 < 10;

SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE unique2 < 10;

SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT percent_rank() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT cume_dist() OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT ntile(3) OVER (ORDER BY ten, four), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;

SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;

SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;
SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten;

SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

-- last_value returns the last row of the frame, which is CURRENT ROW in ORDER BY window.
SELECT last_value(four) OVER (ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10;

SELECT last_value(ten) OVER (PARTITION BY four), ten, four FROM
	(SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s
	ORDER BY four, ten;

SELECT nth_value(ten, four + 1) OVER (PARTITION BY four), ten, four
	FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;

SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
FROM tenk1 GROUP BY ten, two;

SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;

SELECT (count(*) OVER (PARTITION BY four ORDER BY ten) +
  sum(hundred) OVER (PARTITION BY four ORDER BY ten))::varchar AS cntsum
  FROM tenk1 WHERE unique2 < 10;

-- opexpr with different windows evaluation.
SELECT * FROM(
  SELECT count(*) OVER (PARTITION BY four ORDER BY ten) +
    sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS total,
    count(*) OVER (PARTITION BY four ORDER BY ten) AS fourcount,
    sum(hundred) OVER (PARTITION BY two ORDER BY ten) AS twosum
    FROM tenk1
)sub
WHERE total <> fourcount + twosum;

SELECT avg(four) OVER (PARTITION BY four ORDER BY thousand / 100) FROM tenk1 WHERE unique2 < 10;

SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);

-- more than one window with GROUP BY
SELECT sum(salary),
	row_number() OVER (ORDER BY depname),
	sum(sum(salary)) OVER (ORDER BY depname DESC)
FROM empsalary GROUP BY depname;

-- identical windows with different names
SELECT sum(salary) OVER w1, count(*) OVER w2
FROM empsalary WINDOW w1 AS (ORDER BY salary), w2 AS (ORDER BY salary);

-- subplan
SELECT lead(ten, (SELECT two FROM tenk1 WHERE s.unique2 = unique2)) OVER (PARTITION BY four ORDER BY ten)
FROM tenk1 s WHERE unique2 < 10;

-- empty table
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 WHERE FALSE)s;

-- mixture of agg/wfunc in the same window
SELECT sum(salary) OVER w, rank() OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

-- strict aggs
SELECT empno, depname, salary, bonus, depadj, MIN(bonus) OVER (ORDER BY empno), MAX(depadj) OVER () FROM(
	SELECT *,
		CASE WHEN enroll_date < '2008-01-01' THEN 2008 - extract(YEAR FROM enroll_date) END * 500 AS bonus,
		CASE WHEN
			AVG(salary) OVER (PARTITION BY depname) < salary
		THEN 200 END AS depadj FROM empsalary
)s;

-- window function over ungrouped agg over empty row set (bug before 9.1)
SELECT SUM(COUNT(f1)) OVER () FROM int4_tbl WHERE f1=42;

-- window function with ORDER BY an expression involving aggregates (9.1 bug)
select ten,
  sum(unique1) + sum(unique2) as res,
  rank() over (order by sum(unique1) + sum(unique2)) as rank
from tenk1
group by ten order by ten;

-- window and aggregate with GROUP BY expression (9.2 bug)
explain (costs off)
select first_value(max(x)) over (), y
  from (select unique1 as x, ten+four as y from tenk1) ss
  group by y;

-- window functions returning pass-by-ref values from different rows
select x, lag(x, 1) ignore nulls over (order by x), lead(x, 3) ignore nulls over (order by x)
from (select x::numeric as x from generate_series(1,10) x);

-- test non-default frame specifications
SELECT four, ten,
	sum(ten) over (partition by four order by ten),
	last_value(ten) ignore nulls over (partition by four order by ten)
FROM (select distinct ten, four from tenk1) ss;

SELECT four, ten,
	sum(ten) over (partition by four order by ten range between unbounded preceding and current row),
	last_value(ten) ignore nulls over (partition by four order by ten range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;

SELECT four, ten,
	sum(ten) over (partition by four order by ten range between unbounded preceding and unbounded following),
	last_value(ten) ignore nulls over (partition by four order by ten range between unbounded preceding and unbounded following)
FROM (select distinct ten, four from tenk1) ss;

SELECT four, ten/4 as two,
	sum(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row),
	last_value(ten/4) ignore nulls over (partition by four order by ten/4 range between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;

SELECT four, ten/4 as two,
	sum(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row),
	last_value(ten/4) ignore nulls over (partition by four order by ten/4 rows between unbounded preceding and current row)
FROM (select distinct ten, four from tenk1) ss;

SELECT sum(unique1) over (order by four range between current row and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between current row and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 2 preceding and 2 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude no others),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude current row),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude group),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 2 preceding and 2 following exclude ties),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT first_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude current row),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT first_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude group),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT first_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude ties),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT last_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude current row),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT last_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude group),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT last_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude ties),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between 1 following and 3 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between unbounded preceding and 1 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (w range between current row and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);

SELECT sum(unique1) over (w range between unbounded preceding and current row exclude current row),
	unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);

SELECT sum(unique1) over (w range between unbounded preceding and current row exclude group),
	unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);

SELECT sum(unique1) over (w range between unbounded preceding and current row exclude ties),
	unique1, four
FROM tenk1 WHERE unique1 < 10 WINDOW w AS (order by four);

SELECT first_value(unique1) ignore nulls over w,
	nth_value(unique1, 2) ignore nulls over w AS nth_2,
	last_value(unique1) ignore nulls over w, unique1, four
FROM tenk1 WHERE unique1 < 10
WINDOW w AS (order by four range between current row and unbounded following);

SELECT sum(unique1) over
	(order by unique1
	 rows (SELECT unique1 FROM tenk1 ORDER BY unique1 LIMIT 1) + 1 PRECEDING),
	unique1
FROM tenk1 WHERE unique1 < 10;

CREATE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following) as sum_rows
	FROM generate_series(1, 10) i;

SELECT * FROM v_window;

SELECT pg_get_viewdef('v_window');

CREATE OR REPLACE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
	exclude current row) as sum_rows FROM generate_series(1, 10) i;

SELECT * FROM v_window;

SELECT pg_get_viewdef('v_window');

CREATE OR REPLACE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
	exclude group) as sum_rows FROM generate_series(1, 10) i;

SELECT * FROM v_window;

SELECT pg_get_viewdef('v_window');

CREATE OR REPLACE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
	exclude ties) as sum_rows FROM generate_series(1, 10) i;

SELECT * FROM v_window;

SELECT pg_get_viewdef('v_window');

CREATE OR REPLACE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i rows between 1 preceding and 1 following
	exclude no others) as sum_rows FROM generate_series(1, 10) i;

SELECT * FROM v_window;

SELECT pg_get_viewdef('v_window');

CREATE OR REPLACE TEMP VIEW v_window AS
	SELECT i, sum(i) over (order by i groups between 1 preceding and 1 following) as sum_rows FROM generate_series(1, 10) i;

SELECT * FROM v_window;

SELECT pg_get_viewdef('v_window');

DROP VIEW v_window;

CREATE TEMP VIEW v_window AS
	SELECT i, min(i) over (order by i range between '1 day' preceding and '10 days' following) as min_i
  FROM generate_series(now(), now()+'100 days'::interval, '1 hour') i;

SELECT pg_get_viewdef('v_window');

-- RANGE offset PRECEDING/FOLLOWING tests

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four desc range between 2::int8 preceding and 1::int2 preceding),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude no others),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude current row),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude group),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 1::int2 preceding exclude ties),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude ties),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four range between 2::int8 preceding and 6::int2 following exclude group),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (partition by four order by unique1 range between 5::int8 preceding and 6::int2 following
	exclude current row),unique1, four
FROM tenk1 WHERE unique1 < 10;

select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following),
	salary, enroll_date from empsalary;

select sum(salary) over (order by enroll_date desc range between '1 year'::interval preceding and '1 year'::interval following),
	salary, enroll_date from empsalary;

select sum(salary) over (order by enroll_date desc range between '1 year'::interval following and '1 year'::interval following),
	salary, enroll_date from empsalary;

select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
	exclude current row), salary, enroll_date from empsalary;

select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
	exclude group), salary, enroll_date from empsalary;

select sum(salary) over (order by enroll_date range between '1 year'::interval preceding and '1 year'::interval following
	exclude ties), salary, enroll_date from empsalary;

select first_value(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
	lead(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
	nth_value(salary, 1) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
	salary from empsalary;

select last_value(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
	lag(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
	salary from empsalary;

select first_value(salary) ignore nulls over(order by salary range between 1000 following and 3000 following
	exclude current row),
	lead(salary) ignore nulls over(order by salary range between 1000 following and 3000 following exclude ties),
	nth_value(salary, 1) ignore nulls over(order by salary range between 1000 following and 3000 following
	exclude ties),
	salary from empsalary;

select last_value(salary) ignore nulls over(order by salary range between 1000 following and 3000 following
	exclude group),
	lag(salary) ignore nulls over(order by salary range between 1000 following and 3000 following exclude group),
	salary from empsalary;

select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude ties),
	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following),
	salary, enroll_date from empsalary;

select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude ties),
	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude ties),
	salary, enroll_date from empsalary;

select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude group),
	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude group),
	salary, enroll_date from empsalary;

select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude current row),
	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
	exclude current row),
	salary, enroll_date from empsalary;

-- RANGE offset PRECEDING/FOLLOWING with null values
select x, y,
       first_value(y) ignore nulls over w,
       last_value(y) ignore nulls over w
from
  (select x, x as y from generate_series(1,5) as x
   union all select null, 42
   union all select null, 43) ss
window w as
  (order by x asc nulls first range between 2 preceding and 2 following);

select x, y,
       first_value(y) ignore nulls over w,
       last_value(y) ignore nulls over w
from
  (select x, x as y from generate_series(1,5) as x
   union all select null, 42
   union all select null, 43) ss
window w as
  (order by x asc nulls last range between 2 preceding and 2 following);

select x, y,
       first_value(y) ignore nulls over w,
       last_value(y) ignore nulls over w
from
  (select x, x as y from generate_series(1,5) as x
   union all select null, 42
   union all select null, 43) ss
window w as
  (order by x desc nulls first range between 2 preceding and 2 following);

select x, y,
       first_value(y) ignore nulls over w,
       last_value(y) ignore nulls over w
from
  (select x, x as y from generate_series(1,5) as x
   union all select null, 42
   union all select null, 43) ss
window w as
  (order by x desc nulls last range between 2 preceding and 2 following);

-- There is a syntactic ambiguity in the SQL standard.  Since
-- UNBOUNDED is a non-reserved word, it could be the name of a
-- function parameter and be used as an expression.  There is a
-- grammar hack to resolve such cases as the keyword.  The following
-- tests record this behavior.

CREATE FUNCTION unbounded_syntax_test1a(x int) RETURNS TABLE (a int, b int, c int)
LANGUAGE SQL
BEGIN ATOMIC
  SELECT sum(unique1) over (rows between x preceding and x following),
         unique1, four
  FROM tenk1 WHERE unique1 < 10;
END;

CREATE FUNCTION unbounded_syntax_test1b(x int) RETURNS TABLE (a int, b int, c int)
LANGUAGE SQL
AS $$
  SELECT sum(unique1) over (rows between x preceding and x following),
         unique1, four
  FROM tenk1 WHERE unique1 < 10;
$$;

-- These will apply the argument to the window specification inside the function.
SELECT * FROM unbounded_syntax_test1a(2);
SELECT * FROM unbounded_syntax_test1b(2);

CREATE FUNCTION unbounded_syntax_test2a(unbounded int) RETURNS TABLE (a int, b int, c int)
LANGUAGE SQL
BEGIN ATOMIC
  SELECT sum(unique1) over (rows between unbounded preceding and unbounded following),
         unique1, four
  FROM tenk1 WHERE unique1 < 10;
END;

CREATE FUNCTION unbounded_syntax_test2b(unbounded int) RETURNS TABLE (a int, b int, c int)
LANGUAGE SQL
AS $$
  SELECT sum(unique1) over (rows between unbounded preceding and unbounded following),
         unique1, four
  FROM tenk1 WHERE unique1 < 10;
$$;

-- These will not apply the argument but instead treat UNBOUNDED as a keyword.
SELECT * FROM unbounded_syntax_test2a(2);
SELECT * FROM unbounded_syntax_test2b(2);

DROP FUNCTION unbounded_syntax_test1a, unbounded_syntax_test1b,
              unbounded_syntax_test2a, unbounded_syntax_test2b;

-- Other tests with token UNBOUNDED in potentially problematic position
CREATE FUNCTION unbounded(x int) RETURNS int LANGUAGE SQL IMMUTABLE RETURN x;

SELECT sum(unique1) over (rows between 1 preceding and 1 following),
       unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between unbounded(1) preceding and unbounded(1) following),
       unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (rows between unbounded.x preceding and unbounded.x following),
       unique1, four
FROM tenk1, (values (1)) as unbounded(x) WHERE unique1 < 10;

DROP FUNCTION unbounded;

-- Check overflow behavior for various integer sizes

select x, last_value(x) ignore nulls over (order by x::smallint range between current row and 2147450884 following)
from generate_series(32764, 32766) x;

select x, last_value(x) ignore nulls over (order by x::smallint desc range between current row and 2147450885 following)
from generate_series(-32766, -32764) x;

select x, last_value(x) ignore nulls over (order by x range between current row and 4 following)
from generate_series(2147483644, 2147483646) x;

select x, last_value(x) ignore nulls over (order by x desc range between current row and 5 following)
from generate_series(-2147483646, -2147483644) x;

select x, last_value(x) ignore nulls over (order by x range between current row and 4 following)
from generate_series(9223372036854775804, 9223372036854775806) x;

select x, last_value(x) ignore nulls over (order by x desc range between current row and 5 following)
from generate_series(-9223372036854775806, -9223372036854775804) x;

-- Test in_range for other numeric datatypes

create temp table numerics(
    id int,
    f_float4 float4,
    f_float8 float8,
    f_numeric numeric
);

insert into numerics values
(0, '-infinity', '-infinity', '-infinity'),
(1, -3, -3, -3),
(2, -1, -1, -1),
(3, 0, 0, 0),
(4, 1.1, 1.1, 1.1),
(5, 1.12, 1.12, 1.12),
(6, 2, 2, 2),
(7, 100, 100, 100),
(8, 'infinity', 'infinity', 'infinity'),
(9, 'NaN', 'NaN', 'NaN');

select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float4 range between
             1 preceding and 1 following);
select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float4 range between
             1 preceding and 1.1::float4 following);
select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float4 range between
             'inf' preceding and 'inf' following);
select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float4 range between
             'inf' preceding and 'inf' preceding);
select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float4 range between
             'inf' following and 'inf' following);
select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float4 range between
             1.1 preceding and 'NaN' following);  -- error, NaN disallowed

select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float8 range between
             1 preceding and 1 following);
select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float8 range between
             1 preceding and 1.1::float8 following);
select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float8 range between
             'inf' preceding and 'inf' following);
select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float8 range between
             'inf' preceding and 'inf' preceding);
select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float8 range between
             'inf' following and 'inf' following);
select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_float8 range between
             1.1 preceding and 'NaN' following);  -- error, NaN disallowed

select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             1 preceding and 1 following);
select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             1 preceding and 1.1::numeric following);
select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             1 preceding and 1.1::float8 following);  -- currently unsupported
select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             'inf' preceding and 'inf' following);
select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             'inf' preceding and 'inf' preceding);
select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             'inf' following and 'inf' following);
select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from numerics
window w as (order by f_numeric range between
             1.1 preceding and 'NaN' following);  -- error, NaN disallowed

-- Test in_range for other datetime datatypes

create temp table datetimes(
    id int,
    f_time time,
    f_timetz timetz,
    f_interval interval,
    f_timestamptz timestamptz,
    f_timestamp timestamp
);

insert into datetimes values
(0, '10:00', '10:00 BST', '-infinity', '-infinity', '-infinity'),
(1, '11:00', '11:00 BST', '1 year', '2000-10-19 10:23:54+01', '2000-10-19 10:23:54'),
(2, '12:00', '12:00 BST', '2 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(3, '13:00', '13:00 BST', '3 years', '2001-10-19 10:23:54+01', '2001-10-19 10:23:54'),
(4, '14:00', '14:00 BST', '4 years', '2002-10-19 10:23:54+01', '2002-10-19 10:23:54'),
(5, '15:00', '15:00 BST', '5 years', '2003-10-19 10:23:54+01', '2003-10-19 10:23:54'),
(6, '15:00', '15:00 BST', '5 years', '2004-10-19 10:23:54+01', '2004-10-19 10:23:54'),
(7, '17:00', '17:00 BST', '7 years', '2005-10-19 10:23:54+01', '2005-10-19 10:23:54'),
(8, '18:00', '18:00 BST', '8 years', '2006-10-19 10:23:54+01', '2006-10-19 10:23:54'),
(9, '19:00', '19:00 BST', '9 years', '2007-10-19 10:23:54+01', '2007-10-19 10:23:54'),
(10, '20:00', '20:00 BST', '10 years', '2008-10-19 10:23:54+01', '2008-10-19 10:23:54'),
(11, '21:00', '21:00 BST', 'infinity', 'infinity', 'infinity');

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time range between
             '70 min'::interval preceding and '2 hours'::interval following);

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time desc range between
             '70 min' preceding and '2 hours' following);

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time desc range between
             '-70 min' preceding and '2 hours' following);  -- error, negative offset disallowed

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time range between
             'infinity'::interval preceding and 'infinity'::interval following);

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time range between
             'infinity'::interval preceding and 'infinity'::interval preceding);

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time range between
             'infinity'::interval following and 'infinity'::interval following);

select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_time range between
             '-infinity'::interval following and
             'infinity'::interval following);  -- error, negative offset disallowed

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz range between
             '70 min'::interval preceding and '2 hours'::interval following);

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz desc range between
             '70 min' preceding and '2 hours' following);

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz desc range between
             '70 min' preceding and '-2 hours' following);  -- error, negative offset disallowed

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz range between
             'infinity'::interval preceding and 'infinity'::interval following);

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz range between
             'infinity'::interval preceding and 'infinity'::interval preceding);

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz range between
             'infinity'::interval following and 'infinity'::interval following);

select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timetz range between
             'infinity'::interval following and
             '-infinity'::interval following);  -- error, negative offset disallowed

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval range between
             '1 year'::interval preceding and '1 year'::interval following);

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval desc range between
             '1 year' preceding and '1 year' following);

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval desc range between
             '-1 year' preceding and '1 year' following);  -- error, negative offset disallowed

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval range between
             'infinity'::interval preceding and 'infinity'::interval following);

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval range between
             'infinity'::interval preceding and 'infinity'::interval preceding);

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval range between
             'infinity'::interval following and 'infinity'::interval following);

select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_interval range between
             '-infinity'::interval following and
             'infinity'::interval following);  -- error, negative offset disallowed

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz range between
             '1 year'::interval preceding and '1 year'::interval following);

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz desc range between
             '1 year' preceding and '1 year' following);

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz desc range between
             '1 year' preceding and '-1 year' following);  -- error, negative offset disallowed

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz range between
             'infinity'::interval preceding and 'infinity'::interval following);

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz range between
             'infinity'::interval preceding and 'infinity'::interval preceding);

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz range between
             'infinity'::interval following and 'infinity'::interval following);

select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamptz range between
             '-infinity'::interval following and
             'infinity'::interval following);  -- error, negative offset disallowed

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp range between
             '1 year'::interval preceding and '1 year'::interval following);

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp desc range between
             '1 year' preceding and '1 year' following);

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp desc range between
             '-1 year' preceding and '1 year' following);  -- error, negative offset disallowed

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp range between
             'infinity'::interval preceding and 'infinity'::interval following);

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp range between
             'infinity'::interval preceding and 'infinity'::interval preceding);

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp range between
             'infinity'::interval following and 'infinity'::interval following);

select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
from datetimes
window w as (order by f_timestamp range between
             '-infinity'::interval following and
             'infinity'::interval following);  -- error, negative offset disallowed

-- RANGE offset PRECEDING/FOLLOWING error cases
select sum(salary) over (order by enroll_date, salary range between '1 year'::interval preceding and '2 years'::interval following
	exclude ties), salary, enroll_date from empsalary;

select sum(salary) over (range between '1 year'::interval preceding and '2 years'::interval following
	exclude ties), salary, enroll_date from empsalary;

select sum(salary) over (order by depname range between '1 year'::interval preceding and '2 years'::interval following
	exclude ties), salary, enroll_date from empsalary;

select max(enroll_date) over (order by enroll_date range between 1 preceding and 2 following
	exclude ties), salary, enroll_date from empsalary;

select max(enroll_date) over (order by salary range between -1 preceding and 2 following
	exclude ties), salary, enroll_date from empsalary;

select max(enroll_date) over (order by salary range between 1 preceding and -2 following
	exclude ties), salary, enroll_date from empsalary;

select max(enroll_date) over (order by salary range between '1 year'::interval preceding and '2 years'::interval following
	exclude ties), salary, enroll_date from empsalary;

select max(enroll_date) over (order by enroll_date range between '1 year'::interval preceding and '-2 years'::interval following
	exclude ties), salary, enroll_date from empsalary;

-- GROUPS tests

SELECT sum(unique1) over (order by four groups between unbounded preceding and current row),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between unbounded preceding and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between current row and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 1 preceding and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 1 following and unbounded following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between unbounded preceding and 2 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 2 preceding and 1 preceding),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 0 preceding and 0 following),
	unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following
	exclude current row), unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following
	exclude group), unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (order by four groups between 2 preceding and 1 following
	exclude ties), unique1, four
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (partition by ten
	order by four groups between 0 preceding and 0 following),unique1, four, ten
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (partition by ten
	order by four groups between 0 preceding and 0 following exclude current row), unique1, four, ten
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (partition by ten
	order by four groups between 0 preceding and 0 following exclude group), unique1, four, ten
FROM tenk1 WHERE unique1 < 10;

SELECT sum(unique1) over (partition by ten
	order by four groups between 0 preceding and 0 following exclude ties), unique1, four, ten
FROM tenk1 WHERE unique1 < 10;

select first_value(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
	lead(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
	nth_value(salary, 1) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
	salary, enroll_date from empsalary;

select last_value(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
	lag(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
	salary, enroll_date from empsalary;

select first_value(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following
	exclude current row),
	lead(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following exclude ties),
	nth_value(salary, 1) ignore nulls over(order by enroll_date groups between 1 following and 3 following
	exclude ties),
	salary, enroll_date from empsalary;

select last_value(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following
	exclude group),
	lag(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following exclude group),
	salary, enroll_date from empsalary;

-- Show differences in offset interpretation between ROWS, RANGE, and GROUPS
WITH cte (x) AS (
        SELECT * FROM generate_series(1, 35, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);

WITH cte (x) AS (
        SELECT * FROM generate_series(1, 35, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x range between 1 preceding and 1 following);

WITH cte (x) AS (
        SELECT * FROM generate_series(1, 35, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following);

WITH cte (x) AS (
        select 1 union all select 1 union all select 1 union all
        SELECT * FROM generate_series(5, 49, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x rows between 1 preceding and 1 following);

WITH cte (x) AS (
        select 1 union all select 1 union all select 1 union all
        SELECT * FROM generate_series(5, 49, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x range between 1 preceding and 1 following);

WITH cte (x) AS (
        select 1 union all select 1 union all select 1 union all
        SELECT * FROM generate_series(5, 49, 2)
)
SELECT x, (sum(x) over w)
FROM cte
WINDOW w AS (ORDER BY x groups between 1 preceding and 1 following);

-- with UNION
SELECT count(*) OVER (PARTITION BY four) FROM (SELECT * FROM tenk1 UNION ALL SELECT * FROM tenk2)s LIMIT 0;

-- check some degenerate cases
create temp table t1 (f1 int, f2 int8);
insert into t1 values (1,1),(1,2),(2,2);

select f1, sum(f1) over (partition by f1
                         range between 1 preceding and 1 following)
from t1 where f1 = f2;  -- error, must have order by
explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
                         range between 1 preceding and 1 following)
from t1 where f1 = f2;
select f1, sum(f1) over (partition by f1 order by f2
                         range between 1 preceding and 1 following)
from t1 where f1 = f2;
select f1, sum(f1) over (partition by f1, f1 order by f2
                         range between 2 preceding and 1 preceding)
from t1 where f1 = f2;
select f1, sum(f1) over (partition by f1, f2 order by f2
                         range between 1 following and 2 following)
from t1 where f1 = f2;

select f1, sum(f1) over (partition by f1
                         groups between 1 preceding and 1 following)
from t1 where f1 = f2;  -- error, must have order by
explain (costs off)
select f1, sum(f1) over (partition by f1 order by f2
                         groups between 1 preceding and 1 following)
from t1 where f1 = f2;
select f1, sum(f1) over (partition by f1 order by f2
                         groups between 1 preceding and 1 following)
from t1 where f1 = f2;
select f1, sum(f1) over (partition by f1, f1 order by f2
                         groups between 2 preceding and 1 preceding)
from t1 where f1 = f2;
select f1, sum(f1) over (partition by f1, f2 order by f2
                         groups between 1 following and 2 following)
from t1 where f1 = f2;

-- ordering by a non-integer constant is allowed
SELECT rank() OVER (ORDER BY length('abc'));

-- can't order by another window function
SELECT rank() OVER (ORDER BY rank() OVER (ORDER BY random()));

-- some other errors
SELECT * FROM empsalary WHERE row_number() OVER (ORDER BY salary) < 10;

SELECT * FROM empsalary INNER JOIN tenk1 ON row_number() OVER (ORDER BY salary) < 10;

SELECT rank() OVER (ORDER BY 1), count(*) FROM empsalary GROUP BY 1;

SELECT * FROM rank() OVER (ORDER BY random());

DELETE FROM empsalary WHERE (rank() OVER (ORDER BY random())) > 10;

DELETE FROM empsalary RETURNING rank() OVER (ORDER BY random());

SELECT count(*) OVER w FROM tenk1 WINDOW w AS (ORDER BY unique1), w AS (ORDER BY unique1);

SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM tenk1;

SELECT count() OVER () FROM tenk1;

SELECT generate_series(1, 100) OVER () FROM empsalary;

SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;

SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;

-- filter

SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
    sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum",
    depname
FROM empsalary GROUP BY depname;

--
-- Test SupportRequestOptimizeWindowClause's ability to de-duplicate
-- WindowClauses
--

-- Ensure WindowClause frameOptions are changed so that only a single
-- WindowAgg exists in the plan.
EXPLAIN (COSTS OFF)
SELECT
    empno,
    depname,
    row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn,
    rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
                 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk,
    dense_rank() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
                       CURRENT ROW AND CURRENT ROW) drnk,
    ntile(10) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
                    CURRENT ROW AND UNBOUNDED FOLLOWING) nt,
    percent_rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
                         CURRENT ROW AND UNBOUNDED FOLLOWING) pr,
    cume_dist() OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
                      CURRENT ROW AND UNBOUNDED FOLLOWING) cd
FROM empsalary;

-- Ensure WindowFuncs which cannot support their WindowClause's frameOptions
-- being changed are untouched
EXPLAIN (COSTS OFF, VERBOSE)
SELECT
    empno,
    depname,
    row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn,
    rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
                 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk,
    count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
                   CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;

-- Ensure the above query gives us the expected results
SELECT
    empno,
    depname,
    row_number() OVER (PARTITION BY depname ORDER BY enroll_date) rn,
    rank() OVER (PARTITION BY depname ORDER BY enroll_date ROWS BETWEEN
                 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rnk,
    count(*) OVER (PARTITION BY depname ORDER BY enroll_date RANGE BETWEEN
                   CURRENT ROW AND CURRENT ROW) cnt
FROM empsalary;

-- Test pushdown of quals into a subquery containing window functions

-- pushdown is safe because all PARTITION BY clauses include depname:
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT depname,
          sum(salary) OVER (PARTITION BY depname) depsalary,
          min(salary) OVER (PARTITION BY depname || 'A', depname) depminsalary
   FROM empsalary) emp
WHERE depname = 'sales';

-- pushdown is unsafe because there's a PARTITION BY clause without depname:
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT depname,
          sum(salary) OVER (PARTITION BY enroll_date) enroll_salary,
          min(salary) OVER (PARTITION BY depname) depminsalary
   FROM empsalary) emp
WHERE depname = 'sales';

-- Test window function run conditions are properly pushed down into the
-- WindowAgg
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          row_number() OVER (ORDER BY empno) rn
   FROM empsalary) emp
WHERE rn < 3;

-- The following 3 statements should result the same result.
SELECT * FROM
  (SELECT empno,
          row_number() OVER (ORDER BY empno) rn
   FROM empsalary) emp
WHERE rn < 3;

SELECT * FROM
  (SELECT empno,
          row_number() OVER (ORDER BY empno) rn
   FROM empsalary) emp
WHERE 3 > rn;

SELECT * FROM
  (SELECT empno,
          row_number() OVER (ORDER BY empno) rn
   FROM empsalary) emp
WHERE 2 >= rn;

-- Ensure r <= 3 is pushed down into the run condition of the window agg
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          rank() OVER (ORDER BY salary DESC) r
   FROM empsalary) emp
WHERE r <= 3;

SELECT * FROM
  (SELECT empno,
          salary,
          rank() OVER (ORDER BY salary DESC) r
   FROM empsalary) emp
WHERE r <= 3;

-- Ensure dr = 1 is converted to dr <= 1 to get all rows leading up to dr = 1
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          dense_rank() OVER (ORDER BY salary DESC) dr
   FROM empsalary) emp
WHERE dr = 1;

SELECT * FROM
  (SELECT empno,
          salary,
          dense_rank() OVER (ORDER BY salary DESC) dr
   FROM empsalary) emp
WHERE dr = 1;

-- Check COUNT() and COUNT(*)
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER (ORDER BY salary DESC) c
   FROM empsalary) emp
WHERE c <= 3;

SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER (ORDER BY salary DESC) c
   FROM empsalary) emp
WHERE c <= 3;

EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(empno) OVER (ORDER BY salary DESC) c
   FROM empsalary) emp
WHERE c <= 3;

SELECT * FROM
  (SELECT empno,
          salary,
          count(empno) OVER (ORDER BY salary DESC) c
   FROM empsalary) emp
WHERE c <= 3;

EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
   FROM empsalary) emp
WHERE c >= 3;

EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER () c
   FROM empsalary) emp
WHERE 11 <= c;

EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER (ORDER BY salary DESC) c,
          dense_rank() OVER (ORDER BY salary DESC) dr
   FROM empsalary) emp
WHERE dr = 1;

-- Ensure we get a run condition when there's a PARTITION BY clause
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          depname,
          row_number() OVER (PARTITION BY depname ORDER BY empno) rn
   FROM empsalary) emp
WHERE rn < 3;

-- and ensure we get the correct results from the above plan
SELECT * FROM
  (SELECT empno,
          depname,
          row_number() OVER (PARTITION BY depname ORDER BY empno) rn
   FROM empsalary) emp
WHERE rn < 3;

-- ensure that "unused" subquery columns are not removed when the column only
-- exists in the run condition
EXPLAIN (COSTS OFF)
SELECT empno, depname FROM
  (SELECT empno,
          depname,
          row_number() OVER (PARTITION BY depname ORDER BY empno) rn
   FROM empsalary) emp
WHERE rn < 3;

-- likewise with count(empno) instead of row_number()
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          depname,
          salary,
          count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
   FROM empsalary) emp
WHERE c <= 3;

-- and again, check the results are what we expect.
SELECT * FROM
  (SELECT empno,
          depname,
          salary,
          count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c
   FROM empsalary) emp
WHERE c <= 3;

-- Ensure we get the correct run condition when the window function is both
-- monotonically increasing and decreasing.
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          depname,
          salary,
          count(empno) OVER () c
   FROM empsalary) emp
WHERE c = 1;

-- Try another case with a WindowFunc with a byref return type
SELECT * FROM
  (SELECT row_number() OVER (PARTITION BY salary) AS rn,
          lead(depname) OVER (PARTITION BY salary) || ' Department' AS n_dep
   FROM empsalary) emp
WHERE rn < 1;

-- Some more complex cases with multiple window clauses
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT *,
          count(salary) OVER (PARTITION BY depname || '') c1, -- w1
          row_number() OVER (PARTITION BY depname) rn, -- w2
          count(*) OVER (PARTITION BY depname) c2, -- w2
          count(*) OVER (PARTITION BY '' || depname) c3, -- w3
          ntile(2) OVER (PARTITION BY depname) nt -- w2
   FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;

-- Ensure we correctly filter out all of the run conditions from each window
SELECT * FROM
  (SELECT *,
          count(salary) OVER (PARTITION BY depname || '') c1, -- w1
          row_number() OVER (PARTITION BY depname) rn, -- w2
          count(*) OVER (PARTITION BY depname) c2, -- w2
          count(*) OVER (PARTITION BY '' || depname) c3, -- w3
          ntile(2) OVER (PARTITION BY depname) nt -- w2
   FROM empsalary
) e WHERE rn <= 1 AND c1 <= 3 AND nt < 2;

-- Ensure we remove references to reduced outer joins as nulling rels in run
-- conditions
EXPLAIN (COSTS OFF)
SELECT 1 FROM
  (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c
   FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE
   WHERE e1.empno = e2.empno) s
WHERE s.c = 1;

-- Ensure the run condition optimization is used in cases where the WindowFunc
-- has a Var from another query level
EXPLAIN (COSTS OFF)
SELECT 1 FROM
  (SELECT ntile(s1.x) OVER () AS c
   FROM (SELECT (SELECT 1) AS x) AS s1) s
WHERE s.c = 1;

-- Tests to ensure we don't push down the run condition when it's not valid to
-- do so.

-- Ensure we don't push down when the frame options show that the window
-- function is not monotonically increasing
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER (ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) c
   FROM empsalary) emp
WHERE c <= 3;

-- Ensure we don't push down when the window function's monotonic properties
-- don't match that of the clauses.
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(*) OVER (ORDER BY salary) c
   FROM empsalary) emp
WHERE 3 <= c;

-- Ensure we don't use a run condition when there's a volatile function in the
-- WindowFunc
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count(random()) OVER (ORDER BY empno DESC) c
   FROM empsalary) emp
WHERE c = 1;

-- Ensure we don't use a run condition when the WindowFunc contains subplans
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno,
          salary,
          count((SELECT 1)) OVER (ORDER BY empno DESC) c
   FROM empsalary) emp
WHERE c = 1;

-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT depname,
          sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
          min(salary) OVER (PARTITION BY depname, empno order by enroll_date) depminsalary
   FROM empsalary) emp
WHERE depname = 'sales';

-- Ensure that the evaluation order of the WindowAggs results in the WindowAgg
-- with the same sort order that's required by the ORDER BY is evaluated last.
EXPLAIN (COSTS OFF)
SELECT empno,
       enroll_date,
       depname,
       sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
       min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;

-- As above, but with an adjusted ORDER BY to ensure the above plan didn't
-- perform only 2 sorts by accident.
EXPLAIN (COSTS OFF)
SELECT empno,
       enroll_date,
       depname,
       sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
       min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;

SET enable_hashagg TO off;

-- Ensure we don't get a sort for both DISTINCT and ORDER BY.  We expect the
-- sort for the DISTINCT to provide presorted input for the ORDER BY.
EXPLAIN (COSTS OFF)
SELECT DISTINCT
       empno,
       enroll_date,
       depname,
       sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
       min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, enroll_date;

-- As above but adjust the ORDER BY clause to help ensure the plan with the
-- minimum amount of sorting wasn't a fluke.
EXPLAIN (COSTS OFF)
SELECT DISTINCT
       empno,
       enroll_date,
       depname,
       sum(salary) OVER (PARTITION BY depname order by empno) depsalary,
       min(salary) OVER (PARTITION BY depname order by enroll_date) depminsalary
FROM empsalary
ORDER BY depname, empno;

RESET enable_hashagg;

-- Test Sort node reordering
EXPLAIN (COSTS OFF)
SELECT
  lead(1) OVER (PARTITION BY depname ORDER BY salary, enroll_date),
  lag(1) OVER (PARTITION BY depname ORDER BY salary,enroll_date,empno)
FROM empsalary;

-- Test incremental sorting
EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT depname,
          empno,
          salary,
          enroll_date,
          row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp,
          row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
   FROM empsalary) emp
WHERE first_emp = 1 OR last_emp = 1;

SELECT * FROM
  (SELECT depname,
          empno,
          salary,
          enroll_date,
          row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp,
          row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
   FROM empsalary) emp
WHERE first_emp = 1 OR last_emp = 1;

-- cleanup
DROP TABLE empsalary;

-- test user-defined window function with named args and default args
CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
  LANGUAGE internal WINDOW IMMUTABLE STRICT AS 'window_nth_value';

SELECT nth_value_def(n := 2, val := ten) OVER (PARTITION BY four), ten, four
  FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;

SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
  FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten) s;

--
-- Test the basic moving-aggregate machinery
--

-- create aggregates that record the series of transform calls (these are
-- intentionally not true inverses)

CREATE FUNCTION logging_sfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT COALESCE($1, '') || '*' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION logging_msfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT COALESCE($1, '') || '+' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION logging_minvfunc_nonstrict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '-' || quote_nullable($2) $$
LANGUAGE SQL IMMUTABLE;

CREATE AGGREGATE logging_agg_nonstrict (anyelement)
(
	stype = text,
	sfunc = logging_sfunc_nonstrict,
	mstype = text,
	msfunc = logging_msfunc_nonstrict,
	minvfunc = logging_minvfunc_nonstrict
);

CREATE AGGREGATE logging_agg_nonstrict_initcond (anyelement)
(
	stype = text,
	sfunc = logging_sfunc_nonstrict,
	mstype = text,
	msfunc = logging_msfunc_nonstrict,
	minvfunc = logging_minvfunc_nonstrict,
	initcond = 'I',
	minitcond = 'MI'
);

CREATE FUNCTION logging_sfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '*' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;

CREATE FUNCTION logging_msfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '+' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;

CREATE FUNCTION logging_minvfunc_strict(text, anyelement) RETURNS text AS
$$ SELECT $1 || '-' || quote_nullable($2) $$
LANGUAGE SQL STRICT IMMUTABLE;

CREATE AGGREGATE logging_agg_strict (text)
(
	stype = text,
	sfunc = logging_sfunc_strict,
	mstype = text,
	msfunc = logging_msfunc_strict,
	minvfunc = logging_minvfunc_strict
);

CREATE AGGREGATE logging_agg_strict_initcond (anyelement)
(
	stype = text,
	sfunc = logging_sfunc_strict,
	mstype = text,
	msfunc = logging_msfunc_strict,
	minvfunc = logging_minvfunc_strict,
	initcond = 'I',
	minitcond = 'MI'
);

-- test strict and non-strict cases
SELECT
	p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS row,
	logging_agg_nonstrict(v) over wnd as nstrict,
	logging_agg_nonstrict_initcond(v) over wnd as nstrict_init,
	logging_agg_strict(v::text) over wnd as strict,
	logging_agg_strict_initcond(v) over wnd as strict_init
FROM (VALUES
	(1, 1, NULL),
	(1, 2, 'a'),
	(1, 3, 'b'),
	(1, 4, NULL),
	(1, 5, NULL),
	(1, 6, 'c'),
	(2, 1, NULL),
	(2, 2, 'x'),
	(3, 1, 'z')
) AS t(p, i, v)
WINDOW wnd AS (PARTITION BY P ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY p, i;

-- and again, but with filter
SELECT
	p::text || ',' || i::text || ':' ||
		CASE WHEN f THEN COALESCE(v::text, 'NULL') ELSE '-' END as row,
	logging_agg_nonstrict(v) filter(where f) over wnd as nstrict_filt,
	logging_agg_nonstrict_initcond(v) filter(where f) over wnd as nstrict_init_filt,
	logging_agg_strict(v::text) filter(where f) over wnd as strict_filt,
	logging_agg_strict_initcond(v) filter(where f) over wnd as strict_init_filt
FROM (VALUES
	(1, 1, true,  NULL),
	(1, 2, false, 'a'),
	(1, 3, true,  'b'),
	(1, 4, false, NULL),
	(1, 5, false, NULL),
	(1, 6, false, 'c'),
	(2, 1, false, NULL),
	(2, 2, true,  'x'),
	(3, 1, true,  'z')
) AS t(p, i, f, v)
WINDOW wnd AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY p, i;

-- test that volatile arguments disable moving-aggregate mode
SELECT
	i::text || ':' || COALESCE(v::text, 'NULL') as row,
	logging_agg_strict(v::text)
		over wnd as inverse,
	logging_agg_strict(v::text || CASE WHEN random() < 0 then '?' ELSE '' END)
		over wnd as noinverse
FROM (VALUES
	(1, 'a'),
	(2, 'b'),
	(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY i;

SELECT
	i::text || ':' || COALESCE(v::text, 'NULL') as row,
	logging_agg_strict(v::text) filter(where true)
		over wnd as inverse,
	logging_agg_strict(v::text) filter(where random() >= 0)
		over wnd as noinverse
FROM (VALUES
	(1, 'a'),
	(2, 'b'),
	(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY i;

-- test that non-overlapping windows don't use inverse transitions
SELECT
	logging_agg_strict(v::text) OVER wnd
FROM (VALUES
	(1, 'a'),
	(2, 'b'),
	(3, 'c')
) AS t(i, v)
WINDOW wnd AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
ORDER BY i;

-- test that returning NULL from the inverse transition functions
-- restarts the aggregation from scratch. The second aggregate is supposed
-- to test cases where only some aggregates restart, the third one checks
-- that one aggregate restarting doesn't cause others to restart.

CREATE FUNCTION sum_int_randrestart_minvfunc(int4, int4) RETURNS int4 AS
$$ SELECT CASE WHEN random() < 0.2 THEN NULL ELSE $1 - $2 END $$
LANGUAGE SQL STRICT;

CREATE AGGREGATE sum_int_randomrestart (int4)
(
	stype = int4,
	sfunc = int4pl,
	mstype = int4,
	msfunc = int4pl,
	minvfunc = sum_int_randrestart_minvfunc
);

WITH
vs AS (
	SELECT i, (random() * 100)::int4 AS v
	FROM generate_series(1, 100) AS i
),
sum_following AS (
	SELECT i, SUM(v) OVER
		(ORDER BY i DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s
	FROM vs
)
SELECT DISTINCT
	sum_following.s = sum_int_randomrestart(v) OVER fwd AS eq1,
	-sum_following.s = sum_int_randomrestart(-v) OVER fwd AS eq2,
	100*3+(vs.i-1)*3 = length(logging_agg_nonstrict(''::text) OVER fwd) AS eq3
FROM vs
JOIN sum_following ON sum_following.i = vs.i
WINDOW fwd AS (
	ORDER BY vs.i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);

--
-- Test various built-in aggregates that have moving-aggregate support
--

-- test inverse transition functions handle NULLs properly
SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,AVG(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,AVG(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1.5),(2,2.5),(3,NULL),(4,NULL)) t(i,v);

SELECT i,AVG(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);

-- moving aggregates over infinite intervals
SELECT  x
        ,avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as curr_next_avg
        ,avg(x) OVER(ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as prev_curr_avg
        ,sum(x) OVER(ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) as curr_next_sum
        ,sum(x) OVER(ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as prev_curr_sum
FROM (VALUES (NULL::interval),
               ('infinity'::interval),
               ('-2147483648 days -2147483648 months -9223372036854775807 usecs'), -- extreme interval value
               ('-infinity'::interval),
               ('2147483647 days 2147483647 months 9223372036854775806 usecs'), -- extreme interval value
               ('infinity'::interval),
               ('6 days'::interval),
               ('7 days'::interval),
               (NULL::interval),
               ('-infinity'::interval)) v(x);

--should fail.
SELECT x, avg(x) OVER(ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
FROM (VALUES (NULL::interval),
               ('3 days'::interval),
               ('infinity'::timestamptz - now()),
               ('6 days'::interval),
               ('-infinity'::interval)) v(x);

--should fail.
SELECT x, sum(x) OVER(ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
FROM (VALUES (NULL::interval),
               ('3 days'::interval),
               ('infinity'::timestamptz - now()),
               ('6 days'::interval),
               ('-infinity'::interval)) v(x);

SELECT i,SUM(v::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,'1.10'),(2,'2.20'),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::interval) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,'1 sec'),(2,'2 sec'),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1.1),(2,2.2),(3,NULL),(4,NULL)) t(i,v);

SELECT SUM(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1.01),(2,2),(3,3)) v(i,n);

SELECT i,COUNT(v) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,COUNT(*) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT VAR_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VARIANCE(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT STDDEV_POP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_SAMP(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);

SELECT STDDEV(n::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
  FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);

-- test that inverse transition functions work with various frame options
SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);

SELECT i,SUM(v::int) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM (VALUES(1,1),(2,2),(3,3),(4,4)) t(i,v);

-- ensure aggregate over numeric properly recovers from NaN values
SELECT a, b,
       SUM(b) OVER(ORDER BY A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM (VALUES(1,1::numeric),(2,2),(3,'NaN'),(4,3),(5,4)) t(a,b);

-- It might be tempting for someone to add an inverse trans function for
-- float and double precision. This should not be done as it can give incorrect
-- results. This test should fail if anyone ever does this without thinking too
-- hard about it.
SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),'999999999999999999999D9')
  FROM (VALUES(1,1e20),(2,1)) n(i,n);

SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
  FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
  WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);

--
-- Test WindowAgg costing takes into account the number of rows that need to
-- be fetched before the first row can be output.
--

-- Ensure we get a cheap start up plan as the WindowAgg can output the first
-- row after reading 1 row from the join.
EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;

-- Ensure we get a cheap total plan.  Lack of ORDER BY in the WindowClause
-- means that all rows must be read from the join, so a cheap startup plan
-- isn't a good choice.
EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER ()
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
WHERE t2.two = 1
LIMIT 1;

-- Ensure we get a cheap total plan.  This time use UNBOUNDED FOLLOWING, which
-- needs to read all join rows to output the first WindowAgg row.
EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;

-- Ensure we get a cheap total plan.  This time use 10000 FOLLOWING so we need
-- to read all join rows.
EXPLAIN (COSTS OFF)
SELECT COUNT(*) OVER (ORDER BY t1.unique1 ROWS BETWEEN UNBOUNDED PRECEDING AND 10000 FOLLOWING)
FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.tenthous
LIMIT 1;

-- Tests for problems with failure to walk or mutate expressions
-- within window frame clauses.

-- test walker (fails with collation error if expressions are not walked)
SELECT array_agg(i) OVER w
  FROM generate_series(1,5) i
WINDOW w AS (ORDER BY i ROWS BETWEEN (('foo' < 'foobar')::integer) PRECEDING AND CURRENT ROW);

-- test mutator (fails when inlined if expressions are not mutated)
CREATE FUNCTION pg_temp.f(group_size BIGINT) RETURNS SETOF integer[]
AS $$
    SELECT array_agg(s) OVER w
      FROM generate_series(1,5) s
    WINDOW w AS (ORDER BY s ROWS BETWEEN CURRENT ROW AND GROUP_SIZE FOLLOWING)
$$ LANGUAGE SQL STABLE;

-- IGNORE NULLS tests

CREATE TEMPORARY TABLE planets (
    name text,
    distance text,
    orbit integer
);

INSERT INTO planets VALUES
  ('mercury', 'close', 88),
  ('venus', 'close', 224),
  ('earth', 'close', NULL),
  ('mars', 'close', NULL),
  ('jupiter', 'close', 4332),
  ('saturn', 'far', 24491),
  ('uranus', 'far', NULL),
  ('neptune', 'far', 60182),
  ('pluto', 'far', 90560),
  ('xyzzy', 'far', NULL);

-- test ruleutils
CREATE VIEW planets_view AS
SELECT name,
       orbit,
       lag(orbit) OVER w AS lag,
       lag(orbit) RESPECT NULLS OVER w AS lag_respect,
       lag(orbit) IGNORE NULLS OVER w AS lag_ignore
FROM planets
WINDOW w AS (ORDER BY name)
;
SELECT pg_get_viewdef('planets_view');

-- lag
SELECT name,
       orbit,
       lag(orbit) OVER w AS lag,
       lag(orbit) RESPECT NULLS OVER w AS lag_respect,
       lag(orbit) IGNORE NULLS OVER w AS lag_ignore
FROM planets
WINDOW w AS (ORDER BY name)
;

-- lead
SELECT name,
       orbit,
       lead(orbit) OVER w AS lead,
       lead(orbit) RESPECT NULLS OVER w AS lead_respect,
       lead(orbit) IGNORE NULLS OVER w AS lead_ignore
FROM planets
WINDOW w AS (ORDER BY name)
;

-- first_value
SELECT name,
       orbit,
       first_value(orbit) RESPECT NULLS OVER w1,
       first_value(orbit) IGNORE NULLS OVER w1,
       first_value(orbit) RESPECT NULLS OVER w2,
       first_value(orbit) IGNORE NULLS OVER w2
FROM planets
WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
;

-- nth_value
SELECT name,
       orbit,
       nth_value(orbit, 2) RESPECT NULLS OVER w1,
       nth_value(orbit, 2) IGNORE NULLS OVER w1,
       nth_value(orbit, 2) RESPECT NULLS OVER w2,
       nth_value(orbit, 2) IGNORE NULLS OVER w2
FROM planets
WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
;

-- last_value
SELECT name,
       orbit,
       last_value(orbit) RESPECT NULLS OVER w1,
       last_value(orbit) IGNORE NULLS OVER w1,
       last_value(orbit) RESPECT NULLS OVER w2,
       last_value(orbit) IGNORE NULLS OVER w2
FROM planets
WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
       w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
;

-- exclude current row
SELECT name,
       orbit,
       first_value(orbit) IGNORE NULLS OVER w,
       last_value(orbit) IGNORE NULLS OVER w,
       nth_value(orbit, 2) IGNORE NULLS OVER w,
       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
FROM planets
WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
;

-- valid and invalid functions
SELECT sum(orbit) OVER () FROM planets; -- succeeds
SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
SELECT row_number() OVER () FROM planets; -- succeeds
SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails

-- test two consecutive nulls
update planets set orbit=null where name='jupiter';
SELECT name,
       orbit,
       first_value(orbit) IGNORE NULLS OVER w,
       last_value(orbit) IGNORE NULLS OVER w,
       nth_value(orbit, 2) IGNORE NULLS OVER w,
       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
FROM planets
WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
;

-- test partitions
SELECT name,
       distance,
       orbit,
       first_value(orbit) IGNORE NULLS OVER w,
       last_value(orbit) IGNORE NULLS OVER w,
       nth_value(orbit, 2) IGNORE NULLS OVER w,
       lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
       lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
FROM planets
WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
;

-- nth_value without nulls
SELECT x,
       nth_value(x,2) IGNORE NULLS OVER w
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
SELECT x,
       nth_value(x,2) IGNORE NULLS OVER w
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);

--cleanup
DROP TABLE planets CASCADE;
662c662
< select x, lag(x, 1) over (order by x), lead(x, 3) over (order by x)
---
> select x, lag(x, 1) ignore nulls over (order by x), lead(x, 3) ignore nulls over (order by x)
681c681
< 	last_value(ten) over (partition by four order by ten)
---
> 	last_value(ten) ignore nulls over (partition by four order by ten)
709c709
< 	last_value(ten) over (partition by four order by ten range between unbounded preceding and current row)
---
> 	last_value(ten) ignore nulls over (partition by four order by ten range between unbounded preceding and current row)
737c737
< 	last_value(ten) over (partition by four order by ten range between unbounded preceding and unbounded following)
---
> 	last_value(ten) ignore nulls over (partition by four order by ten range between unbounded preceding and unbounded following)
765c765
< 	last_value(ten/4) over (partition by four order by ten/4 range between unbounded preceding and current row)
---
> 	last_value(ten/4) ignore nulls over (partition by four order by ten/4 range between unbounded preceding and current row)
793c793
< 	last_value(ten/4) over (partition by four order by ten/4 rows between unbounded preceding and current row)
---
> 	last_value(ten/4) ignore nulls over (partition by four order by ten/4 rows between unbounded preceding and current row)
938c938
< SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row),
---
> SELECT first_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude current row),
955c955
< SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group),
---
> SELECT first_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude group),
972c972
< SELECT first_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties),
---
> SELECT first_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude ties),
989c989
< SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude current row),
---
> SELECT last_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude current row),
1006c1006
< SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude group),
---
> SELECT last_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude group),
1023c1023
< SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 following exclude ties),
---
> SELECT last_value(unique1) ignore nulls over (ORDER BY four rows between current row and 2 following exclude ties),
1159,1161c1159,1161
< SELECT first_value(unique1) over w,
< 	nth_value(unique1, 2) over w AS nth_2,
< 	last_value(unique1) over w, unique1, four
---
> SELECT first_value(unique1) ignore nulls over w,
> 	nth_value(unique1, 2) ignore nulls over w AS nth_2,
> 	last_value(unique1) ignore nulls over w, unique1, four
1631,1633c1631,1633
< select first_value(salary) over(order by salary range between 1000 preceding and 1000 following),
< 	lead(salary) over(order by salary range between 1000 preceding and 1000 following),
< 	nth_value(salary, 1) over(order by salary range between 1000 preceding and 1000 following),
---
> select first_value(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
> 	lead(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
> 	nth_value(salary, 1) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
1649,1650c1649,1650
< select last_value(salary) over(order by salary range between 1000 preceding and 1000 following),
< 	lag(salary) over(order by salary range between 1000 preceding and 1000 following),
---
> select last_value(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
> 	lag(salary) ignore nulls over(order by salary range between 1000 preceding and 1000 following),
1666c1666
< select first_value(salary) over(order by salary range between 1000 following and 3000 following
---
> select first_value(salary) ignore nulls over(order by salary range between 1000 following and 3000 following
1668,1669c1668,1669
< 	lead(salary) over(order by salary range between 1000 following and 3000 following exclude ties),
< 	nth_value(salary, 1) over(order by salary range between 1000 following and 3000 following
---
> 	lead(salary) ignore nulls over(order by salary range between 1000 following and 3000 following exclude ties),
> 	nth_value(salary, 1) ignore nulls over(order by salary range between 1000 following and 3000 following
1686c1686
< select last_value(salary) over(order by salary range between 1000 following and 3000 following
---
> select last_value(salary) ignore nulls over(order by salary range between 1000 following and 3000 following
1688c1688
< 	lag(salary) over(order by salary range between 1000 following and 3000 following exclude group),
---
> 	lag(salary) ignore nulls over(order by salary range between 1000 following and 3000 following exclude group),
1704c1704
< select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1706c1706
< 	last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following),
---
> 	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following),
1722c1722
< select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1724c1724
< 	last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> 	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1741c1741
< select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1743c1743
< 	last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> 	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1760c1760
< select first_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> select first_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1762c1762
< 	last_value(salary) over(order by enroll_date range between unbounded preceding and '1 year'::interval following
---
> 	last_value(salary) ignore nulls over(order by enroll_date range between unbounded preceding and '1 year'::interval following
1781,1782c1781,1782
<        first_value(y) over w,
<        last_value(y) over w
---
>        first_value(y) ignore nulls over w,
>        last_value(y) ignore nulls over w
1801,1802c1801,1802
<        first_value(y) over w,
<        last_value(y) over w
---
>        first_value(y) ignore nulls over w,
>        last_value(y) ignore nulls over w
1821,1822c1821,1822
<        first_value(y) over w,
<        last_value(y) over w
---
>        first_value(y) ignore nulls over w,
>        last_value(y) ignore nulls over w
1841,1842c1841,1842
<        first_value(y) over w,
<        last_value(y) over w
---
>        first_value(y) ignore nulls over w,
>        last_value(y) ignore nulls over w
2001c2001
< select x, last_value(x) over (order by x::smallint range between current row and 2147450884 following)
---
> select x, last_value(x) ignore nulls over (order by x::smallint range between current row and 2147450884 following)
2010c2010
< select x, last_value(x) over (order by x::smallint desc range between current row and 2147450885 following)
---
> select x, last_value(x) ignore nulls over (order by x::smallint desc range between current row and 2147450885 following)
2019c2019
< select x, last_value(x) over (order by x range between current row and 4 following)
---
> select x, last_value(x) ignore nulls over (order by x range between current row and 4 following)
2028c2028
< select x, last_value(x) over (order by x desc range between current row and 5 following)
---
> select x, last_value(x) ignore nulls over (order by x desc range between current row and 5 following)
2037c2037
< select x, last_value(x) over (order by x range between current row and 4 following)
---
> select x, last_value(x) ignore nulls over (order by x range between current row and 4 following)
2046c2046
< select x, last_value(x) over (order by x desc range between current row and 5 following)
---
> select x, last_value(x) ignore nulls over (order by x desc range between current row and 5 following)
2073c2073
< select id, f_float4, first_value(id) over w, last_value(id) over w
---
> select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2091c2091
< select id, f_float4, first_value(id) over w, last_value(id) over w
---
> select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2109c2109
< select id, f_float4, first_value(id) over w, last_value(id) over w
---
> select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2127c2127
< select id, f_float4, first_value(id) over w, last_value(id) over w
---
> select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2145c2145
< select id, f_float4, first_value(id) over w, last_value(id) over w
---
> select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2163c2163
< select id, f_float4, first_value(id) over w, last_value(id) over w
---
> select id, f_float4, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2168c2168
< select id, f_float8, first_value(id) over w, last_value(id) over w
---
> select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2186c2186
< select id, f_float8, first_value(id) over w, last_value(id) over w
---
> select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2204c2204
< select id, f_float8, first_value(id) over w, last_value(id) over w
---
> select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2222c2222
< select id, f_float8, first_value(id) over w, last_value(id) over w
---
> select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2240c2240
< select id, f_float8, first_value(id) over w, last_value(id) over w
---
> select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2258c2258
< select id, f_float8, first_value(id) over w, last_value(id) over w
---
> select id, f_float8, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2263c2263
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2281c2281
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2299c2299
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2307c2307
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2325c2325
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2343c2343
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2361c2361
< select id, f_numeric, first_value(id) over w, last_value(id) over w
---
> select id, f_numeric, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2388c2388
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2408c2408
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2428c2428
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2433c2433
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2453c2453
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2473c2473
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2493c2493
< select id, f_time, first_value(id) over w, last_value(id) over w
---
> select id, f_time, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2499c2499
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2519c2519
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2539c2539
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2544c2544
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2564c2564
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2584c2584
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2604c2604
< select id, f_timetz, first_value(id) over w, last_value(id) over w
---
> select id, f_timetz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2610c2610
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2630c2630
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2650c2650
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2655c2655
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2675c2675
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2695c2695
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2715c2715
< select id, f_interval, first_value(id) over w, last_value(id) over w
---
> select id, f_interval, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2721c2721
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2741c2741
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2761c2761
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2766c2766
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2786c2786
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2806c2806
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2826c2826
< select id, f_timestamptz, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamptz, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2832c2832
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2852c2852
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2872c2872
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2877c2877
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2897c2897
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2917c2917
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
2937c2937
< select id, f_timestamp, first_value(id) over w, last_value(id) over w
---
> select id, f_timestamp, first_value(id) ignore nulls over w, last_value(id) ignore nulls over w
3253,3255c3253,3255
< select first_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
< 	lead(salary) over(order by enroll_date groups between 1 preceding and 1 following),
< 	nth_value(salary, 1) over(order by enroll_date groups between 1 preceding and 1 following),
---
> select first_value(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
> 	lead(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
> 	nth_value(salary, 1) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
3271,3272c3271,3272
< select last_value(salary) over(order by enroll_date groups between 1 preceding and 1 following),
< 	lag(salary) over(order by enroll_date groups between 1 preceding and 1 following),
---
> select last_value(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
> 	lag(salary) ignore nulls over(order by enroll_date groups between 1 preceding and 1 following),
3288c3288
< select first_value(salary) over(order by enroll_date groups between 1 following and 3 following
---
> select first_value(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following
3290,3291c3290,3291
< 	lead(salary) over(order by enroll_date groups between 1 following and 3 following exclude ties),
< 	nth_value(salary, 1) over(order by enroll_date groups between 1 following and 3 following
---
> 	lead(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following exclude ties),
> 	nth_value(salary, 1) ignore nulls over(order by enroll_date groups between 1 following and 3 following
3308c3308
< select last_value(salary) over(order by enroll_date groups between 1 following and 3 following
---
> select last_value(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following
3310c3310
< 	lag(salary) over(order by enroll_date groups between 1 following and 3 following exclude group),
---
> 	lag(salary) ignore nulls over(order by enroll_date groups between 1 following and 3 following exclude group),

---

From: Tatsuo Ishii <[email-masked]>
Date: Fri, 25 Jul 2025 16:49:14 +0900 (JST)

Attached are the v17 patches for adding RESPECT/IGNORE NULLS options
defined in the standard to some window functions. FROM FIRST/LAST
options are not considered in the patch (yet).

This time I split the patch into 6
patches for reviewer's convenience. Also each patch has a short commit
message to explain the patch.

0001: parse and analysis
0002: rewriter
0003: planner
0004: executor
0005: documents
0006: tests

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sat, 16 Aug 2025 18:33:29 +0900 (JST)

Attached are the v18 patches for adding RESPECT/IGNORE NULLS options
to some window functions. Recent changes to doc/src/sgml/func.sgml
required v17 to be rebased. Other than that, nothing has been changed.

Oliver, do you have any comments on the patches?

Best regards,

---

From: Oliver Ford <[email-masked]>
Date: Sat, 16 Aug 2025 20:19:54 +0100

> Oliver, do you have any comments on the patches?

Looks good, tried it on the nth_value test script from a bit ago - I added
a 1 million rows test and it takes an average of 12 seconds on my i7.

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 17 Aug 2025 07:34:19 +0900 (JST)

> a 1 million rows test and it takes an average of 12 seconds on my i7.

Thanks.

I have moved the CF entry from PG19-1 to PG19-2 as PG19-1 has been
already closed on July 31. Hope this help CF bot to catch the v18
patches.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Fri, 12 Sep 2025 18:53:16 +0900 (JST)

> a 1 million rows test and it takes an average of 12 seconds on my i7.

I would like to push the patch by the end of this month or early in
October if there's no objection.

Comments/suggestions are welcome.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Chao Li <[email-masked]>
Date: Mon, 15 Sep 2025 16:08:43 +0800

Overall LGTM. Just a few small comments:

> On Sep 12, 2025, at 17:53, Tatsuo Ishii <[email-masked]> wrote:
> 
> 

1 - 0001
```
```

Should we use the constant NO_NULLTREATMENT here for 0?

2 - 0001
```
@@ -579,6 +579,17 @@ typedef struct GroupingFunc
@@ -602,6 +613,8 @@ typedef struct WindowFunc
```

Maybe we can use “uint8” type for “ignore_nulls”. Because the previous two are both of type “bool”, an uint8 will just fit to the padding bytes, so that new field won’t add extra memory to the structure.

3 - 0004
```
 			winobj->markpos = -1;
 			winobj->seekpos = -1;
+
+			/* reset null map */
+			if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
+				memset(perfuncstate->winobj->notnull_info, 0,
+					   NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info));
 		}
```
Where in “if” and “memset()”, we can just use “winobj”.

4 - 0004
```
```

“Procform” is assigned but not used.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 17 Sep 2025 18:18:51 +0900 (JST)

> Should we use the constant NO_NULLTREATMENT here for 0?

Good suggestion. Will fix.

> Maybe we can use “uint8” type for “ignore_nulls”. Because the previous two are both of type “bool”, an uint8 will just fit to the padding bytes, so that new field won’t add extra memory to the stru…

If we change the data type for ignore_nulls in WindowFunc, we may also
want to change it elsewhere (FuncCall, WindowObjectData,
WindowStatePerFuncData) for consistency?

> Where in “if” and “memset()”, we can just use “winobj”.

Good catch. Will fix.

> “Procform” is assigned but not used.

I think procform is used in the following elog(ERROR, ...).

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 24 Sep 2025 14:39:00 +0900 (JST)

Attached is the updated v19 patches. Mostly applied changes suggested
by Chao.

> Good suggestion. Will fix.

Done.

> WindowStatePerFuncData) for consistency?

I tried to change all "int ignore_nulls;" to "uint8 ignore_nulls;" but
gen_node_support.pl dislikes it and complains like:

  could not handle type "uint8" in struct "FuncCall" field "ignore_nulls"

>> Where in “if” and “memset()”, we can just use “winobj”.

Done.

> I think procform is used in the following elog(ERROR, ...).

I added more tests for functions (rank(), dense_rank(),
percent_rank(), cume_dist() and ntile()) that do not support
RESPECT/IGNORE NULLS options to confirm that they throw errors if the
options are given. Previously there was only test cases for
row_number().

Also I have made small cosmetic changes to executor/nodeWindowAgg.c to
make too long lines shorter.

Best regards,

---

From: Oliver Ford <[email-masked]>
Date: Wed, 24 Sep 2025 13:24:02 +0100

>   could not handle type "uint8" in struct "FuncCall" field "ignore_nulls"
uint8 was missing in one place in that perl script. The attached patch
silences it for uint8/uint16.

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 24 Sep 2025 22:18:42 +0900 (JST)

Hi Oliver,
> silences it for uint8/uint16.

Thank you for the patch. (I noticed int8 is also missing).

I have looked into the commit 964d01ae90c3 which was made by Peter.  I
have quick read through the discussion to know why uint8/uint16 (and
int8) are missing in gen_node_support.pl. Unfortunately I have no
clear idea why these data types are missing in the script.

Peter,
Maybe you wanted to limit the data types that are actually used at
that point? If so, probably we should only add uint8 support this time
(uint8 is only needed to implement $Subject for now).  What do you
think?

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 02 Oct 2025 21:15:50 +0900 (JST)

> (uint8 is only needed to implement $Subject for now).  What do you

I decided not to include the fix to gen_node_support.pl for now and
commit the patch without it. We could revisit it later on.

So here is the commit message I would like to propose.

For the technical part please look at the message.

Non technical part:
First of all the author is Oliver (no doubt).  I would like to be
listed as a co-author since I wrote the not null cache part.

Next is reviewers. Actually the first effor to implement null
treatment clause was back to 9.3 era (2013) at least. After that
multiple trials to implemnt the feature happend but they had faded
away. I think we don't need to include all of those who joined the old
discussions as reviewers. So I started to check from the discussion:
https://postgr.es/m/flat/[email-masked]
because it's refered to by the commit fest entry.

Oliver and others, I love to hear your comment!

BTW, Oliver's last patch made the CF bot to misunderstand the patch
contents, which was not actually the main patch. So I attach the same
patch as v20.

----------------------------------------------------------------------
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 some the evaluation work, thus we could get better performance.

Author: Oliver Ford <[email-masked]>
Co-authored-by: Tatsuo Ishii <[email-masked]>
Reviewed-by: Andrew Gierth <[email-masked]>
Reviewed-by: Tom Lane <[email-masked]>
Reviewed-by: David Fetter <[email-masked]>
Reviewed-by: Vik Fearing <[email-masked]>
Reviewed-by: "David G. Johnston" <[email-masked]>
Reviewed-by: Krasiyan Andreev <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Discussion: https://postgr.es/m/flat/[email-masked]
----------------------------------------------------------------------

Best regards,

---

From: Oliver Ford <[email-masked]>
Date: Thu, 2 Oct 2025 16:36:26 +0100

> Oliver and others, I love to hear your comment!

Looks great, so glad this is finally going in.

---

From: Tatsuo Ishii <[email-masked]>
Date: Fri, 03 Oct 2025 10:06:06 +0900 (JST)

> Looks great, so glad this is finally going in.

I have just pushed the patch (plus patches for syntax.sgml and
sql_features.txt. They were missued after I splitted the patch).
Thank you for your effort!
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Tom Lane <[email-masked]>
Date: Sun, 05 Oct 2025 11:59:17 -0400

> sql_features.txt. They were missued after I splitted the patch).

Coverity is not very happy with this patch.
It's complaining that the result of window_gettupleslot
is not checked, which seems valid:

1503     			{
1504     				if (fetch_tuple)
>>>     CID 1666587:         Error handling issues  (CHECKED_RETURN)
>>>     Calling "window_gettupleslot" without checking return value (as is done elsewhere 8 out of 9 times).
1505     					window_gettupleslot(winobj, pos, slot);
1506     				if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
1507     					return -1;

and also that WinGetFuncArgInPartition is dereferencing
a possibly-null "isout" pointer at several places, including

>>>     Dereferencing null pointer "isout".
3806     				if (*isout)		/* out of partition? */

>>>     Dereferencing null pointer "isout".
3817     	if (!*isout && set_mark)
3818     		WinSetMarkPosition(winobj, abs_pos);

>>>     Dereferencing null pointer "isout".
3817     	if (!*isout && set_mark)
3818     		WinSetMarkPosition(winobj, abs_pos);

The latter complaints seem to be because some places in
WinGetFuncArgInPartition check for nullness of that pointer
and some do not.  That looks like at least a latent bug
to me.  If it isn't, the function's comment needs to be
expanded to say when it's legal to pass isout == NULL.

			regards, tom lane

---

From: Álvaro Herrera <[email-masked]>
Date: Sun, 5 Oct 2025 18:20:30 +0200

> Thank you for your effort!

I just noticed this compiler warning in a CI run,

[16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3820:16: warning: ‘datum’ may be used uninitialized [-Wmaybe-uninitialized]
[16:06:29.920]  3820 |         return datum;
[16:06:29.920]       |                ^~~~~
[16:06:29.920] ../src/backend/executor/nodeWindowAgg.c:3719:25: note: ‘datum’ was declared here
[16:06:29.920]  3719 |         Datum           datum;
[16:06:29.920]       |                         ^~~~~

The logic in this function looks somewhat wicked.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

---

From: Tom Lane <[email-masked]>
Date: Sun, 05 Oct 2025 12:35:46 -0400

I wrote:
> ... also that WinGetFuncArgInPartition is dereferencing
> a possibly-null "isout" pointer at several places

Looking around, there is only one in-core caller of
WinGetFuncArgInPartition, and it does pass a valid "isout" pointer,
explaining why this inconsistency wasn't obvious in testing.
There are outside callers though according to Debian Code Search,
and at least PostGIS is one that passes a null pointer.

As Alvaro notes nearby, this function is ridiculously complicated
already.  I'm tempted to remove the API allowance for isout == NULL,
and thereby simplify the code slightly, rather than complicate it more
by continuing to allow that.  We'd have to warn the PostGIS people
about the API change though.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Sun, 05 Oct 2025 12:51:45 -0400

> [16:06:29.920]  3820 |         return datum;

Yeah, I can easily believe that a compiler running at relatively low
optimization level wouldn't make the connection that the NN_NOTNULL
case must perform the "prepare to exit this loop" bit if the loop
will be exited this time.  But there's another thing that is
confusing: the NN_NULL case certainly looks like it's expecting
to exit the loop, but that "break" will only get out of the switch
not the loop.  Moreover, the NN_NULL case looks like it'd fail
to notice end-of-frame.  And it's not entirely clear what the
default case thinks it's doing either.

In short, this loop is impossible to understand, and the lack of
comments doesn't help.  Even if it's not actually buggy, it
needs to be rewritten in a way that helps readers and compilers
see that it's not buggy.  I think it might help to separate the
detection of null-ness and fetching of the datum value (if required)
from the loop control logic.

			regards, tom lane

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 06 Oct 2025 08:51:33 +0900 (JST)

Thank you for the report!

> 1506     				if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))

Yes, I forgot to check the return value of window_gettupleslot.

> to me.

Agreed.

Attached is a patch to fix the issue.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 06 Oct 2025 09:09:01 +0900 (JST)

> not the loop.

You mean "NN_NOTNULL" case?  if (notnull_offset >= notnull_relpos),
then following "while (notnull_offset < notnull_relpos)" does not
satisfy the continuous condition of the while loop and exits the loop.
I can add "goto" to explicitly exit the loop if we want.

> Moreover, the NN_NULL case looks like it'd fail

WinGetFuncArgInPartition() does not care about frame, no?

> from the loop control logic.

Thanks for the idea.  Let me think if I could change the loop to be
easier to understand.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 06 Oct 2025 09:28:13 +0900 (JST)

> about the API change though.

It think it's a good idea.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 06 Oct 2025 18:34:39 +0900 (JST)

> Attached is a patch to fix the issue.

Please disregard the v1 patch. It includes a bug: If
WinGetFuncArgInPartition() is called with set_mark == true and isout
== NULL, WinSetMarkPosition() is not called by
WinGetFuncArgInPartition().

I will post v2 patch.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 07 Oct 2025 11:28:32 +0900 (JST)

> WinGetFuncArgInPartition().

Attached is the v2 patch.

Best regards,

---

From: Paul Ramsey <[email-masked]>
Date: Tue, 7 Oct 2025 13:14:27 -0700

> Attached is the v2 patch.

Thanks! This passes regression, and reads right to my eye and (most
important to me) allows PostGIS to run under Pg19 again.

Thanks,
P

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 08 Oct 2025 08:50:16 +0900 (JST)

> [16:06:29.920]  3820 |         return datum;

Thanks for the report. I believe the warning is eliminated in the v2
patch[1].

Best regards,

[1] https://www.postgresql.org/message-id/20251007.112832.740065769089328041.ishii%40postgresql.org
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 08 Oct 2025 09:31:17 +0900 (JST)

> important to me) allows PostGIS to run under Pg19 again.

Thank you for the review! I have just pushed the v2 patch.

Best regards,

---

From: Tom Lane <[email-masked]>
Date: Thu, 09 Oct 2025 11:19:14 -0400

> Thank you for the review! I have just pushed the v2 patch.

While I'd paid basically zero attention to this patch (the claim
in the commit message that I reviewed it is a flight of fancy),
I've been forced to look through it as a consequence of the mop-up
that's been happening to silence compiler warnings.  There are a
couple of points that I think were not well done:

1. WinCheckAndInitializeNullTreatment really needs a rethink.
You cannot realistically assume that existing user-defined window
functions will be fixed to call that.  I think it should be set up
so that if the window function fails to call that, then something in
mainline execution of nodeWindowAgg.c throws an error when there had
been a RESPECT/IGNORE NULLS option.  With that idea, you could drop
the allowNullTreatment argument and just have the window functions
that support this syntax call something named along the lines of
WinAllowNullTreatmentOption.  Also the error is certainly user-facing,
so using elog() was quite inappropriate.  It should be ereport with an
errcode of (probably) ERRCODE_FEATURE_NOT_SUPPORTED.  Rolling your
own implementation of get_func_name() wasn't great either.

Alternatively, you could just drop the entire concept of throwing an
error for that.  What's the point?  The implementation is entirely
within nodeWindowAgg.c and does not depend in any way on the
cooperation of the window function.  I do not in any case like the
documentation's wording

as this fails to account for the possibility of user-defined window
functions.  IMO we could drop the error check altogether and rewrite
the docs along the lines of "Not all window functions pay attention
to this option.  Of the built-in window functions, only blah blah
and blah do."

2. AFAICS there is only one notnull_info array, which amounts to
assuming that the window function will have only one argument position
that it calls WinGetFuncArgInFrame or WinGetFuncArgInPartition for.
That may be true for the built-in functions but it seems mighty
restrictive for extensions.  Worse yet, there's no check, so that
you'd just get silently wrong answers if two or more arguments are
evaluated.  I think there ought to be a separate array for each argno;
of course only created if the window function actually asks for
evaluations of a particular argno.

			regards, tom lane

---

From: Tatsuo Ishii <[email-masked]>
Date: Sat, 11 Oct 2025 09:07:31 +0900 (JST)

> in the commit message that I reviewed it is a flight of fancy),

Sorry, I added you as a reviewer because you had joined past
discussions regarding this feature. Next time I will add reviewers
only those who actually looked into a patch.

> functions will be fixed to call that.

Currently if WinCheckAndInitializeNullTreatment is not called,
RESPECT/IGNORE NULLS option is disregarded and WinGetFuncArgInFrame or
WinGetFuncArgInPartition works as if RESPECT/IGNORE NULLS option is
not given. So I thought it's safe even if existing user-defined window
functions are not fixed.

> I think it should be set up
> WinAllowNullTreatmentOption.

Does that mean all user defined window functions start to fail after
upgrading to PostgreSQL 19?  I am not sure if it's acceptable for
extension developers and their users.

> Also the error is certainly user-facing,

I overlooked the elog() call and "own implementation of
get_func_name()". Will fix.

> error for that.  What's the point?

If we do that, extensions would need to be re-tested against IGNORE
NULLS option case. I might be wrong but I guess some of (or many of)
extension developers do not plan (or have no time to work on it for
now) to utilize IGNORE NULLS option for their extensions.

For buil-in window functions. I don't want to create test cases how
built-in window functions, that are not allowed IGNORE NULLS option,
behave against IGNORE NULLS option. Instead I prefer to throw an error
as it is done today.

> The implementation is entirely

> functions.

The page explains only built-in window functions. Thus for me it's not
that strange that it does not say anything about user defined window
functions.

> IMO we could drop the error check altogether and rewrite

I missed that. Thank you for pointed it out. I agree it would be
better allow to use multiple argument positions that calls
WinGetFuncArgInFrame or WinGetFuncArgInPartition in
extensions. Attached is a PoC patch for that.

Currently there's an issue with the patch, however.

SELECT x, y, mywindowfunc2(x, y, 2) IGNORE NULLS OVER w FROM g
WINDOW w AS (ORDER BY y);
psql:test2.sql:9: ERROR:  cannot fetch row before WindowObject's mark position

mywindowfunc2 is a user defined window function, taking 3 arguments. x
and y are expected to be evaluated to integer. The third argument is
relative offset to current row. In the query above x and y are
retrieved using two WinGetFuncArgInPartition() calls. The data set
(table "g") looks like below.

 x  | y 
----+---
    | 1
    | 2
 10 | 3
 20 | 4
(4 rows)

I think the cause of the error is:

(1) WinGetFuncArgInPartition keep on fetching column x until it's
evalued to not null and placed in the second row (in this case that's
x==20). In WinGetFuncArgInPartition WinSetMarkPosition is called at
abs_pos==3.

(2) WinGetFuncArgInPartition tries to fetch column y at row 0. Since
the mark was set to at row 3, the error occurred.

To avoid the error, we could call WinGetFuncArgInPartition with
set_mark = false (and call WinSetMarkPosition separately) but I am not
sure if it's an acceptable solution.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sat, 11 Oct 2025 14:42:18 +0900 (JST)

> get_func_name()". Will fix.

Attached is a trivial patch to fix that. I am going to push it if
there's no objection.

Best regards,

---

From: Chao Li <[email-masked]>
Date: Sat, 11 Oct 2025 13:57:58 +0800

> On Oct 11, 2025, at 13:42, Tatsuo Ishii <[email-masked]> wrote:
> 
> <v1-0001-Use-ereport-rather-than-elog-in-WinCheckAndInitia.patch>

I just take a quick look at the patch, a tiny comment is:

```
+		char	   *funcname = get_func_name(fcinfo->flinfo->fn_oid);
```

This can be a “const char *”.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sat, 11 Oct 2025 18:11:03 +0900 (JST)

> extension developers and their users.

Probably I misunderstood what you said. Now I realize what you are
suggesting was, throwing an error *only* when a RESPECT/IGNORE NULLS
option is given and the function did not call
WinAllowNullTreatmentOption. If the option is not given, no error is
thrown even if WinAllowNullTreatmentOption is not called. I am okay
with this direction. I will post a patch for this.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 12 Oct 2025 17:39:59 +0900 (JST)

> with this direction. I will post a patch for this.

While I was implementing this, I realized that in order to check if
WinAllowNullTreatmentOption had been called or not, it's necessary to
call the window function at least once in mainline execution of
nodeWindowAgg.c (I supposed in eval_windowfunction). I don't like this
because I expected to throw an error *before* calling the window
function.

So I studied your idea:
> error for that.

Attached is a patch to implement this. Previously window functions
should call WinCheckAndInitializeNullTreatment with
allowNullTreatment==true if they accept a null treatment
clause. Otherwise, they are called as if null treatment clause is not
specified. With the patch, window functions accept a null treatment
clause as specified without calling
WinCheckAndInitializeNullTreatment.

There's one thing which might be different from what you suggested
is, I want to give window functions a method to stat that they do not
want accept a null treatment clause. For this purpose
WinCheckAndInitializeNullTreatment (with allowNullTreatment==false)
can be called.(Alternatively we could eliminate allowNullTreatment
argument and rename it something like WinDisallowNullTreatmentOption).

Some of built-in window functions that do not accept a null treatment
clause call this in the patch. This way, we do not need to test the
case when the functions are given a null treatment option except just
they throw an error. User defined functions would call the function
for the same purpose as built-in window functions.

> cooperation of the window function.

I am not sure. For example built-in lead function's behavior (with
IGNORE NULLS option) is defined by the standard. Unlike RESPECT NULLS
case, the expected behavior may not be obvious. According the
standard:

1. If lead's "offset" option is 0, the argument evaluated on current
   row is returned regardless the value is NULL or NOT.

2. Otherwise, returns the value evaluated on a row which is nth NOT NULL.

For me, 2 is obvious but 1 was not so obvious because I thought that
lead() returns only non NULL value (except there's no non null values
or specified offset is out of partition).

Thus lead() calls WinGetFuncArgInPartition with
seektype==WINDOW_SEEK_CURRENT. Thus WinGetFuncArgInPartition with
WINDOW_SEEK_CURRENT is implemented in a way to satisfy the lead()
semantics above. This means if someone tries to implement a new window
function calling WinGetFuncArgInPartition with WINDOW_SEEK_CURRENT,
the function must has the same semantics as lead(). I think there's a
cooperation between nodeWindowAgg.c and window functions.

> to this option.  Of the built-in window functions, only blah blah

Fixing docs are not included in the patch (yet).

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 12 Oct 2025 21:05:30 +0900 (JST)

Hi Tom,

> that's been happening to silence compiler warnings.

Sorry for taking up your time to fix the compiler warnings. I haven't
noticed your commit 71540dcdcb2 until today. Next time I will try to
fix warnings found by buildfarm.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 13 Oct 2025 13:43:32 +0900 (JST)

> sure if it's an acceptable solution.

Attached is a v2 patch to fix the "cannot fetch row before
WindowObject's mark position" error, by tweaking the logic to
calculate the set mark position in WinGetFuncArgInPartition.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 13 Oct 2025 13:49:48 +0900 (JST)

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 13 Oct 2025 14:39:41 +0900 (JST)

> This can be a “const char *”.

Thanks for the review. In addition to the point, I added an assertion
which is called by all other window function API. Also added check to
the return value of get_func_name() because it could return NULL. V2
patch attached.

Best regards,

---

From: Álvaro Herrera <[email-masked]>
Date: Mon, 13 Oct 2025 13:22:18 +0200

> the return value of get_func_name() because it could return NULL. V2

Hmm, this change made me realize that all or almost all the calls to
get_func_name() would crash if it were to return a NULL value.  I found
no caller that checks the return value for nullness.  I wonder why do we
allow it to return NULL at all ... it might be better to just
elog(ERROR) if the cache entry is not found.  

I think it was already wrong as introduced by 31c775adeb22.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 14 Oct 2025 09:01:00 +0900 (JST)

> elog(ERROR) if the cache entry is not found.  

I agree it's better but what about user defined functions? Some of
them might already check the return value to emit their own error
messages, I don't know. If so, modifying get_func_name() could break
them. Maybe invent something like get_func_name_with_error(calling
elog(ERROR)) and gradually update our code?

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Tue, 14 Oct 2025 19:21:13 +0900 (JST)

> the return value of get_func_name() because it could return NULL. V2

V2 patch pushed. Thanks.

Best regards,

---

From: Michael Paquier <[email-masked]>
Date: Thu, 16 Oct 2025 15:19:21 +0900

> V2 patch pushed. Thanks.

Coverity thinks that this code has still some incorrect bits, and I
think that it is right to think so even on today's HEAD at
02c171f63fca.

In WinGetFuncArgInPartition()@nodeWindowAgg.c, we have the following
loop (keeping only the relevant parts:
    do
    {
        [...]
        else                    /* need to check NULL or not */
        {
            /* get tuple and evaluate in partition */
            datum = gettuple_eval_partition(winobj, argno,
                                            abs_pos, isnull, &myisout);
            if (myisout)        /* out of partition? */
                break;
            if (!*isnull)
                notnull_offset++;
            /* record the row status */
            put_notnull_info(winobj, abs_pos, *isnull);
        }
    } while (notnull_offset < notnull_relpos);

    /* get tuple and evaluate in partition */
    datum = gettuple_eval_partition(winobj, argno,
                                    abs_pos, isnull, &myisout);

And Coverity is telling that there is no point in setting a datum in
this else condition to just override its value when we exit the while
loop.  To me, it's a sigh that this code's logic could be simplified.

In passing, gettuple_eval_partition() is under-documented for me.  Its
name refers to the fact that it gets a tuple and evaluates a
partition.  Its top comment tells the same thing as the name of the
function, so it's a bit hard to say why it is useful with the code
written this way, and how others many benefit when attempting to reuse
it, or if it even makes sense to reuse it for other purposes.
--
Michael

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 16 Oct 2025 19:17:06 +0900 (JST)

Thanks for the report.

to:

     (void) gettuple_eval_partition(winobj, argno,
                                           abs_pos, isnull, &myisout);

This explicitely stats that we ignore the return value from
gettuple_eval_partition. I hope coverity understands this.

> it, or if it even makes sense to reuse it for other purposes.

What about changing the comment this way?

/* gettuple_eval_partition
 * get tuple in a patition and evaluate the window function's argument
 * expression on it.
 */

Attached is the patch for above.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Chao Li <[email-masked]>
Date: Thu, 16 Oct 2025 19:50:06 +0800

> On Oct 16, 2025, at 18:17, Tatsuo Ishii <[email-masked]> wrote:
> 
> Thanks for the report.
> 

I think Coverity is complaining about the redundant call to gettuple_eval_partition().

In the “else” clause, the function is called, then when “if (myisout)” is satisfied, it will break out the while loop. After that, the function is immediately called again, so “datum” is overwritten. But I haven’t spent time thinking about how to fix.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 19 Oct 2025 09:38:31 +0900 (JST)

> Attached is the patch for above.

Patch pushed with minor comment tweaks.
Thanks.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 19 Oct 2025 10:02:58 +0900 (JST)

> In the “else” clause, the function is called, then when “if (myisout)” is satisfied, it will break out the while loop. After that, the function is immediately called again, so “datum” is overwritte…

Yes, the function is called again. But I think the cost is cheap in
this case.  Inside the function window_gettupleslot() is called. It
could be costly if it spools tuples. But as tuple is already spooled
by the former call of gettuple_eval_partition(), almost no cost is
needed. We could avoid the redundant call by putting more code after
the former function call to return immediately, or introduce a goto
statement or a flag. But I think they will make the code harder to
read and do not worth the trouble. Others may think differently
though.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Sun, 19 Oct 2025 18:53:23 +0900 (JST)

> calculate the set mark position in WinGetFuncArgInPartition.

Attached is a v3 patch which is ready for commit IMO.  Major
difference from v2 patch is, now the patch satisfies the request
below.

>>> evaluations of a particular argno.

The NOT NULL information array is allocated only when the window
function actually asks for evaluations of a particular argno using
WinGetFuncArgInFrame or WinGetFuncArgInPartition.

If there's no objection, I am going to commit in a few days.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Chao Li <[email-masked]>
Date: Mon, 20 Oct 2025 11:22:04 +0800

> On Oct 19, 2025, at 17:53, Tatsuo Ishii <[email-masked]> wrote:
> 
> 
> <v3-0001-Fix-multi-WinGetFuncArgInFrame-Partition-calls-wi.patch>

A very trivial commit:

```
+	else
+
+		/*
+		 * For other cases we have no idea what position of row callers would
+		 * fetch next time. Also for relpos < 0 case (we go backward), we
+		 * cannot set mark either. For those cases we always set mark at 0.
+		 */
+		mark_pos = 0;
```

The empty line after “else” is not needed.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Mon, 20 Oct 2025 12:58:47 +0900 (JST)

> The empty line after “else” is not needed.

That was added by pgindent.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 22 Oct 2025 12:14:11 +0900 (JST)

> If there's no objection, I am going to commit in a few days.

Patch pushed. Thanks.

Best regards,

---

From: David Rowley <[email-masked]>
Date: Wed, 22 Oct 2025 17:18:31 +1300

> That was added by pgindent.

If it's written down somewhere, I can't find it, but the rule we
normally follow here is; don't use braces if the code block has a
single statement without any comments that appear on a separate line.
Otherwise, use braces.

Since your comments are not on the same line as the statement, it
should have braces. I imagine that's why pgindent is "acting weird".

David

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 22 Oct 2025 14:49:11 +0900 (JST)

> single statement without any comments that appear on a separate line.

Oh ok, I didn't know that.

> should have braces. I imagine that's why pgindent is "acting weird".

Attached is a trivial patch to follow the rule.

Best regards,

---

From: Tatsuo Ishii <[email-masked]>
Date: Thu, 23 Oct 2025 11:06:59 +0900 (JST)

Patch pushed. Thanks.