0001-initial-window-ignore.patch

From 81c48df9a08deb065379e8bccffb2f5592faa4d0 Mon Sep 17 00:00:00 2001
From: Oliver Ford <ojford@gmail.com>
Date: Wed, 19 Apr 2023 01:07:14 +0100
Subject: [PATCH] initial window ignore

---
 src/backend/executor/nodeWindowAgg.c | 263 ++++++++++++++++++++++++++-
 src/backend/optimizer/util/clauses.c |   1 +
 src/backend/parser/gram.y            |  20 +-
 src/backend/parser/parse_func.c      |   9 +
 src/backend/utils/adt/ruleutils.c    |   7 +-
 src/include/nodes/parsenodes.h       |   1 +
 src/include/nodes/primnodes.h        |   2 +
 src/include/parser/kwlist.h          |   2 +
 8 files changed, 297 insertions(+), 8 deletions(-)

diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 4f0618f27a..fac0e05dee 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -69,6 +69,11 @@ 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 */
+
+	bool		ignore_nulls;	/* ignore nulls */
+	int64		*win_nonnulls;	/* tracks non-nulls in ignore nulls mode */
+	int			nonnulls_size;	/* track size of the win_nonnulls array */
+	int			nonnulls_len;	/* track length of the win_nonnulls array */
 } WindowObjectData;
 
 /*
@@ -97,6 +102,7 @@ typedef struct WindowStatePerFuncData
 	bool		plain_agg;		/* is it just a plain aggregate function? */
 	int			aggno;			/* if so, index of its WindowStatePerAggData */
 
+	bool		ignore_nulls;	/* ignore nulls */
 	WindowObject winobj;		/* object used in window function API */
 }			WindowStatePerFuncData;
 
@@ -2560,14 +2566,14 @@ 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;
@@ -2620,6 +2626,13 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
 			winobj->argstates = wfuncstate->args;
 			winobj->localmem = NULL;
 			perfuncstate->winobj = winobj;
+			winobj->ignore_nulls = wfunc->ignore_nulls;
+			if (winobj->ignore_nulls)
+			{
+				winobj->win_nonnulls = palloc_array(int64, 16);
+				winobj->nonnulls_size = 16;
+				winobj->nonnulls_len = 0;
+			}
 
 			/* It's a real window function, so set up to call it. */
 			fmgr_info_cxt(wfunc->winfnoid, &perfuncstate->flinfo,
@@ -3306,6 +3319,244 @@ WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
 	return res;
 }
 
+static void increment_notnulls(WindowObject winobj, int64 pos)
+{
+	if (winobj->nonnulls_len == winobj->nonnulls_size)
+	{
+		winobj->nonnulls_size *= 2;
+		winobj->win_nonnulls =
+			repalloc_array(winobj->win_nonnulls,
+							int64,
+							winobj->nonnulls_size);
+	}
+	winobj->win_nonnulls[winobj->nonnulls_len] = pos;
+	winobj->nonnulls_len++;
+}
+
+static Datum ignorenulls_getfuncarginpartition(WindowObject winobj, int argno,
+						int relpos, int seektype, bool set_mark, 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;
+	int			i;
+
+	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;
+		break;
+	default:
+		elog(ERROR, "unrecognized window seek type: %d", seektype);
+		abs_pos = 0; /* keep compiler quiet */
+		break;
+	}
+
+	if (forward == -1)
+		goto check_partition;
+
+	/* if we're moving forward, store previous rows */
+	for (i=0; i < winobj->nonnulls_len; ++i)
+	{
+		if (winobj->win_nonnulls[i] > abs_pos)
+		{
+			abs_pos = winobj->win_nonnulls[i];
+			++notnull_offset;
+			if (notnull_offset == notnull_relpos)
+			{
+				if (isout)
+					*isout = false;
+				window_gettupleslot(winobj, abs_pos, slot);
+				econtext->ecxt_outertuple = slot;
+				return ExecEvalExpr((ExprState *)list_nth(winobj->argstates, argno),
+									econtext, isnull);
+			}
+		}
+	}
+
+check_partition:
+	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;
+			increment_notnulls(winobj, abs_pos);
+		}
+	} while (notnull_offset < notnull_relpos);
+
+	if (set_mark)
+		WinSetMarkPosition(winobj, abs_pos);
+	return datum;
+}
+
+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;
+	bool		gottuple;
+	int64		abs_pos;
+	int64		mark_pos;
+	int			notnull_offset;
+	int			notnull_relpos;
+	int			forward;
+	int			i;
+
+	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;
+			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;
+			forward = -1;
+			goto check_frame;
+			break;
+		default:
+			elog(ERROR, "unrecognized window seek type: %d", seektype);
+			abs_pos = mark_pos = 0; /* keep compiler quiet */
+			break;
+	}
+
+	/*
+	 * Store previous rows. Only possible in SEEK_HEAD mode
+	 */
+	for (i = 0; i < winobj->nonnulls_len; ++i)
+	{
+			int inframe;
+			if (winobj->win_nonnulls[i] < winobj->markpos)
+				continue;
+			if (!window_gettupleslot(winobj, winobj->win_nonnulls[i], slot))
+				continue;
+
+			inframe = row_is_in_frame(winstate, winobj->win_nonnulls[i], slot);
+			if (inframe <= 0)
+			{
+				if (inframe == -1 && set_mark)
+					WinSetMarkPosition(winobj, winobj->win_nonnulls[i]);
+				continue;
+			}
+
+			abs_pos = winobj->win_nonnulls[i] + 1;
+			++notnull_offset;
+
+			if (notnull_offset > notnull_relpos)
+			{
+				if (isout)
+				*isout = false;
+				econtext->ecxt_outertuple = slot;
+				return ExecEvalExpr((ExprState *)list_nth(winobj->argstates, argno),
+									econtext, isnull);
+			}
+	}
+
+check_frame:
+	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;
+
+			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;
+				increment_notnulls(winobj, abs_pos);
+			}
+
+advance:
+			abs_pos += forward;
+	} while (notnull_offset <= notnull_relpos);
+
+	return datum;
+
+out_of_frame:
+	if (isout)
+		*isout = true;
+	*isnull = true;
+	return (Datum) 0;
+}
+
 /*
  * WinGetFuncArgInPartition
  *		Evaluate a window function's argument expression on a specified
@@ -3340,6 +3591,10 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
 	econtext = winstate->ss.ps.ps_ExprContext;
 	slot = winstate->temp_slot_1;
 
+	if (winobj->ignore_nulls && relpos != 0)
+		return ignorenulls_getfuncarginpartition(winobj, argno, relpos, seektype,
+													set_mark, isnull, isout);
+
 	switch (seektype)
 	{
 		case WINDOW_SEEK_CURRENT:
@@ -3428,6 +3683,10 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
 	econtext = winstate->ss.ps.ps_ExprContext;
 	slot = winstate->temp_slot_1;
 
+	if (winobj->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 a9c7bc342e..01fd16acf9 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2474,6 +2474,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 acf6cf4866..3992839618 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -633,6 +633,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				opt_frame_clause frame_extent frame_bound
 %type <ival>	opt_window_exclusion_clause
 %type <str>		opt_existing_window_name
+%type <boolean> null_treatment
 %type <boolean> opt_if_not_exists
 %type <boolean> opt_unique_null_treatment
 %type <ival>	generated_when override_kind
@@ -718,7 +719,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
@@ -752,8 +753,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	RANGE READ REAL REASSIGN RECHECK 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
-	ROUTINE ROUTINES ROW ROWS RULE
+	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
 	SEQUENCE SEQUENCES
@@ -15213,7 +15214,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;
 
@@ -15246,7 +15247,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
@@ -15794,6 +15796,12 @@ filter_clause:
 /*
  * Window Definitions
  */
+null_treatment:
+			IGNORE_P NULLS_P						{ $$ = true; }
+			| RESPECT_P NULLS_P						{ $$ = false; }
+			| /*EMPTY*/								{ $$ = false; }
+		;
+
 window_clause:
 			WINDOW window_definition_list			{ $$ = $2; }
 			| /*EMPTY*/								{ $$ = NIL; }
@@ -17111,6 +17119,7 @@ unreserved_keyword:
 			| HOUR_P
 			| IDENTITY_P
 			| IF_P
+			| IGNORE_P
 			| IMMEDIATE
 			| IMMUTABLE
 			| IMPLICIT_P
@@ -17223,6 +17232,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 b3f0b6a137..afa4bcc8d1 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);
+	bool		ignore_nulls = (fn ? fn->ignore_nulls : false);
 	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)
+				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->location = location;
 
 		/*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 461735e84f..8c77b62263 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10060,7 +10060,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)
+		appendStringInfoString(buf, "IGNORE NULLS ");
+
+	appendStringInfoString(buf, "OVER ");
 
 	foreach(l, context->windowClause)
 	{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index cc7b32b279..073e2469ba 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -426,6 +426,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 */
+	bool		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 be9c29f0bf..221b5e6218 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -559,6 +559,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 */
+	bool		ignore_nulls;
 	/* token location, or -1 if unknown */
 	int			location;
 } WindowFunc;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f5b2e61ca5..ed652571e0 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -198,6 +198,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)
@@ -360,6 +361,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)
-- 
2.34.1