This feature adds RESPECT NULLS and IGNORE NULLS syntax to several
window functions, according to the SQL Standard.
Unlike the last time this was attempted[1], my version does not hardcode
the spec's list of functions that this applies to. Instead, it accepts
it for all true window functions (that is, it does not apply to
aggregates acting as window functions).
This patch also does not attempt to solve the FROM LAST problem. That
remains unimplemented.
For the CREATE FUNCTION syntax, I used TREAT NULLS so as to avoid
creating new keywords.
The second patch adds some new window functions in order to test that
the null treatment works correctly for cases that aren't covered by the
standard functions but that custom functions might want to use. It is
*not* intended to be committed; I am only submitting the first patch for
inclusion in core.
This is based off of 324435eb14.
[1]
https://www.postgresql.org/message-id/CAGMVOdsbtRwE_4%2Bv8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A%40mail.gmail.com
--
Vik Fearing
>From 9ea72a4fe144f8ffdccb4b53eb4ea97e24ce61f7 Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik.fear...@protonmail.com>
Date: Sun, 31 May 2020 07:29:35 +0200
Subject: [PATCH 1/2] implement <null treatment> for window functions
---
doc/src/sgml/func.sgml | 19 +-
doc/src/sgml/ref/create_function.sgml | 12 +
doc/src/sgml/syntax.sgml | 14 +
src/backend/catalog/pg_aggregate.c | 3 +-
src/backend/catalog/pg_proc.c | 8 +
src/backend/commands/functioncmds.c | 29 +-
src/backend/commands/typecmds.c | 1 +
src/backend/executor/nodeWindowAgg.c | 463 +++++++++++-------
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/makefuncs.c | 1 +
src/backend/nodes/outfuncs.c | 2 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/util/clauses.c | 1 +
src/backend/parser/gram.y | 24 +-
src/backend/parser/parse_func.c | 27 +-
src/backend/utils/adt/ruleutils.c | 12 +-
src/include/catalog/pg_proc.dat | 27 +-
src/include/catalog/pg_proc.h | 4 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/primnodes.h | 8 +
src/include/parser/kwlist.h | 2 +
src/include/parser/parse_func.h | 3 +-
.../regress/expected/create_function_3.out | 8 +
.../regress/expected/create_procedure.out | 4 +
src/test/regress/expected/misc_sanity.out | 10 +
src/test/regress/expected/window.out | 211 ++++++++
src/test/regress/sql/create_function_3.sql | 7 +
src/test/regress/sql/create_procedure.sql | 1 +
src/test/regress/sql/misc_sanity.sql | 9 +
src/test/regress/sql/window.sql | 101 ++++
31 files changed, 812 insertions(+), 205 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f065856535..388b942d57 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19722,6 +19722,14 @@ SELECT count(*) FROM sometable;
about frame specifications.
</para>
+ <para>
+ The functions <function>lead</function>, <function>lag</function>,
+ <function>first_value</function>, <function>last_value</function>, and
+ <function>nth_value</function> accept a null treatment option which is
+ <literal>RESPECT NULLS</literal> or <literal>IGNORE NULLS</literal>.
+ If this option is not specified, the default is <literal>RESPECT NULLS</literal>.
+ </para>
+
<para>
When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
@@ -19735,14 +19743,9 @@ SELECT count(*) FROM sometable;
<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
+ 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.)
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index f81cedc823..aae967eeb8 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION
{ LANGUAGE <replaceable class="parameter">lang_name</replaceable>
| TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ]
| WINDOW
+ | TREAT NULLS
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
@@ -293,6 +294,17 @@ CREATE [ OR REPLACE ] FUNCTION
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>TREAT NULLS</literal></term>
+
+ <listitem>
+ <para><literal>TREAT NULLS</literal> indicates that the function is able
+ to handle the <literal>RESPECT NULLS</literal> and <literal>IGNORE
+ NULLS</literal> options. Only window functions may specify this.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>IMMUTABLE</literal></term>
<term><literal>STABLE</literal></term>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 2f993ca2e0..5e3a4fc51a 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1790,6 +1790,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>) [ RESPECT NULLS | IGNORE NULLS ] OVER <replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ RESPECT NULLS | IGNORE NULLS ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
<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> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
@@ -1803,6 +1805,18 @@ FROM generate_series(1,10) AS s(i);
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <replaceable class="parameter">frame_clause</replaceable> ]
</synopsis>
+ </para>
+
+ <note>
+ <para>
+ The versions with <literal>RESPECT NULLS</literal> or <literal>IGNORE
+ NULLS</literal> only apply to true window functions, whereas the versions
+ with <literal>FILTER</literal> only apply to aggregate functions used as
+ window functions.
+ </para>
+ </note>
+
+ <para>
The optional <replaceable class="parameter">frame_clause</replaceable>
can be one of
<synopsis>
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 7d887ea24a..a8fcf5307f 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -626,6 +626,7 @@ AggregateCreate(const char *aggName,
* definable for agg) */
false, /* isLeakProof */
false, /* isStrict (not needed for agg) */
+ false, /* null_treatment (not needed for agg) */
PROVOLATILE_IMMUTABLE, /* volatility (not needed
* for agg) */
proparallel,
@@ -861,7 +862,7 @@ lookup_agg_function(List *fnName,
nargs, input_types, false, false,
&fnOid, rettype, &retset,
&nvargs, &vatype,
- &true_oid_array, NULL);
+ &true_oid_array, NULL, NULL);
/* only valid case is a normal function not returning a set */
if (fdresult != FUNCDETAIL_NORMAL || !OidIsValid(fnOid))
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 6cdda35d1c..ff2038be13 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -80,6 +80,7 @@ ProcedureCreate(const char *procedureName,
bool security_definer,
bool isLeakProof,
bool isStrict,
+ bool null_treatment,
char volatility,
char parallel,
oidvector *parameterTypes,
@@ -303,6 +304,7 @@ ProcedureCreate(const char *procedureName,
values[Anum_pg_proc_prosecdef - 1] = BoolGetDatum(security_definer);
values[Anum_pg_proc_proleakproof - 1] = BoolGetDatum(isLeakProof);
values[Anum_pg_proc_proisstrict - 1] = BoolGetDatum(isStrict);
+ values[Anum_pg_proc_pronulltreatment - 1] = BoolGetDatum(null_treatment);
values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet);
values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility);
values[Anum_pg_proc_proparallel - 1] = CharGetDatum(parallel);
@@ -382,6 +384,12 @@ ProcedureCreate(const char *procedureName,
errdetail("\"%s\" is a window function.", procedureName) :
0)));
+ /* Not okay to set null treatment if not a window function */
+ if (null_treatment && oldproc->prokind != PROKIND_WINDOW)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("cannot set null treatment on a non-window function")));
+
dropcmd = (prokind == PROKIND_PROCEDURE ? "DROP PROCEDURE" :
prokind == PROKIND_AGGREGATE ? "DROP AGGREGATE" :
"DROP FUNCTION");
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 1b5bdcec8b..48ff7cafe2 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -701,6 +701,7 @@ compute_function_attributes(ParseState *pstate,
bool *windowfunc_p,
char *volatility_p,
bool *strict_p,
+ bool *null_treatment_p,
bool *security_definer,
bool *leakproof_p,
ArrayType **proconfig,
@@ -714,6 +715,7 @@ compute_function_attributes(ParseState *pstate,
DefElem *language_item = NULL;
DefElem *transform_item = NULL;
DefElem *windowfunc_item = NULL;
+ DefElem *nulltreatment_item = NULL;
DefElem *volatility_item = NULL;
DefElem *strict_item = NULL;
DefElem *security_item = NULL;
@@ -769,6 +771,20 @@ compute_function_attributes(ParseState *pstate,
parser_errposition(pstate, defel->location)));
windowfunc_item = defel;
}
+ else if (strcmp(defel->defname, "null_treatment") == 0)
+ {
+ if (nulltreatment_item)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, defel->location)));
+ if (is_procedure)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("invalid attribute in procedure definition"),
+ parser_errposition(pstate, defel->location)));
+ nulltreatment_item = defel;
+ }
else if (compute_common_attribute(pstate,
is_procedure,
defel,
@@ -820,6 +836,14 @@ compute_function_attributes(ParseState *pstate,
*volatility_p = interpret_func_volatility(volatility_item);
if (strict_item)
*strict_p = intVal(strict_item->arg);
+ if (nulltreatment_item)
+ {
+ *null_treatment_p = intVal(nulltreatment_item->arg);
+ if (*null_treatment_p && !*windowfunc_p)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
+ errmsg("cannot set null treatment on a non-window function")));
+ }
if (security_item)
*security_definer = intVal(security_item->arg);
if (leakproof_item)
@@ -945,6 +969,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
isStrict,
security,
isLeakProof;
+ bool null_treatment;
char volatility;
ArrayType *proconfig;
float4 procost;
@@ -968,6 +993,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
/* Set default attributes */
isWindowFunc = false;
isStrict = false;
+ null_treatment = false;
security = false;
isLeakProof = false;
volatility = PROVOLATILE_VOLATILE;
@@ -983,7 +1009,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
stmt->options,
&as_clause, &language, &transformDefElem,
&isWindowFunc, &volatility,
- &isStrict, &security, &isLeakProof,
+ &isStrict, &null_treatment, &security, &isLeakProof,
&proconfig, &procost, &prorows,
&prosupport, ¶llel);
@@ -1163,6 +1189,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
security,
isLeakProof,
isStrict,
+ null_treatment,
volatility,
parallel,
parameterTypes,
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 9e5938b10e..a4b080c631 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1591,6 +1591,7 @@ makeRangeConstructors(const char *name, Oid namespace,
false, /* security_definer */
false, /* leakproof */
false, /* isStrict */
+ false, /* null_treatment */
PROVOLATILE_IMMUTABLE, /* volatility */
PROPARALLEL_SAFE, /* parallel safety */
constructorArgTypesVector, /* parameterTypes */
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 4cc7da268d..39e0f4aa16 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -68,6 +68,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 */
+ NullTreatment null_treatment; /* RESPECT/IGNORE NULLS? */
} WindowObjectData;
/*
@@ -2478,6 +2479,7 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winobj->winstate = winstate;
winobj->argstates = wfuncstate->args;
winobj->localmem = NULL;
+ winobj->null_treatment = wfunc->winnulltreatment;
perfuncstate->winobj = winobj;
}
}
@@ -3173,48 +3175,103 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
TupleTableSlot *slot;
bool gottuple;
int64 abs_pos;
+ bool ignore_nulls = false;
+ int target = relpos;
+ int step;
+ Datum result;
Assert(WindowObjectIsValid(winobj));
winstate = winobj->winstate;
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
- switch (seektype)
+ /*
+ * If we're not ignoring nulls, we'll just go straight to the desired row.
+ * But if we are ignoring nulls, we'll have to step towards the target row
+ * by row so we need to determine how we get there.
+ */
+ if (winobj->null_treatment == NULL_TREATMENT_IGNORE)
{
- case WINDOW_SEEK_CURRENT:
- abs_pos = winstate->currentpos + relpos;
- break;
- case WINDOW_SEEK_HEAD:
- abs_pos = relpos;
- 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;
- }
-
- gottuple = window_gettupleslot(winobj, abs_pos, slot);
+ ignore_nulls = true;
- if (!gottuple)
- {
- if (isout)
- *isout = true;
- *isnull = true;
- return (Datum) 0;
+ if (seektype == WINDOW_SEEK_HEAD)
+ {
+ step = 1;
+ relpos = 0;
+ }
+ else if (seektype == WINDOW_SEEK_TAIL)
+ {
+ step = -1;
+ relpos = 0;
+ }
+ else
+ {
+ if (target > 0)
+ step = 1;
+ else if (target < 0)
+ step = -1;
+ else
+ step = 0;
+ relpos = step;
+ }
}
- else
+
+ for (;;)
{
- if (isout)
- *isout = false;
- if (set_mark)
- WinSetMarkPosition(winobj, abs_pos);
+ switch (seektype)
+ {
+ case WINDOW_SEEK_CURRENT:
+ abs_pos = winstate->currentpos + relpos;
+ break;
+ case WINDOW_SEEK_HEAD:
+ abs_pos = relpos;
+ 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;
+ }
+
+ gottuple = window_gettupleslot(winobj, abs_pos, slot);
+
+ /* Did we fall off the end of the partition? */
+ if (!gottuple)
+ {
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+ }
+
+ /* Evaluate the expression at this row */
econtext->ecxt_outertuple = slot;
- return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
- econtext, isnull);
+ result = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+
+ /* If we got a null and we're ignoring them, move the goalposts */
+ if (ignore_nulls && *isnull)
+ target += step;
+
+ /* Once we've reached our target, we're done */
+ if (relpos == target)
+ {
+ if (isout)
+ *isout = false;
+ /*
+ * We can only mark the row if we're not ignoring nulls (because we
+ * jump straight there).
+ */
+ if (set_mark && !ignore_nulls)
+ WinSetMarkPosition(winobj, abs_pos);
+ return result;
+ }
+
+ /* Otherwise move closer and try again */
+ relpos += step;
}
}
@@ -3261,170 +3318,218 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
TupleTableSlot *slot;
int64 abs_pos;
int64 mark_pos;
+ bool ignore_nulls = false;
+ int target = relpos;
+ int step;
+ Datum result;
Assert(WindowObjectIsValid(winobj));
winstate = winobj->winstate;
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
- switch (seektype)
+ /*
+ * If we're not ignoring nulls, we'll just go straight to the desired row.
+ * But if we are ignoring nulls, we'll have to step towards the target row
+ * by row so we need to determine how we get there.
+ */
+ if (winobj->null_treatment == NULL_TREATMENT_IGNORE)
{
- 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 + relpos;
- mark_pos = abs_pos;
+ ignore_nulls = true;
- /*
- * Account for exclusion option if one is active, but advance only
- * abs_pos not mark_pos. This prevents changes of the current
- * row's peer group from resulting in trying to fetch a row before
- * some previous mark position.
- *
- * Note that in some corner cases such as current row being
- * outside frame, these calculations are theoretically too simple,
- * but it doesn't matter because we'll end up deciding the row is
- * out of frame. We do not attempt to avoid fetching rows past
- * end of frame; that would happen in some cases anyway.
- */
- switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
- {
- case 0:
- /* no adjustment needed */
- break;
- case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
- if (abs_pos >= winstate->currentpos &&
- winstate->currentpos >= winstate->frameheadpos)
- abs_pos++;
- break;
- case FRAMEOPTION_EXCLUDE_GROUP:
- update_grouptailpos(winstate);
- if (abs_pos >= winstate->groupheadpos &&
- winstate->grouptailpos > winstate->frameheadpos)
- {
- int64 overlapstart = Max(winstate->groupheadpos,
- winstate->frameheadpos);
+ if (target > 0)
+ step = 1;
+ else if (target < 0)
+ step = -1;
+ else if (seektype == WINDOW_SEEK_HEAD)
+ step = 1;
+ else if (seektype == WINDOW_SEEK_TAIL)
+ step = -1;
+ else
+ step = 0;
+
+ relpos = 0;
+ }
- abs_pos += winstate->grouptailpos - overlapstart;
- }
- break;
- case FRAMEOPTION_EXCLUDE_TIES:
- update_grouptailpos(winstate);
- if (abs_pos >= winstate->groupheadpos &&
- winstate->grouptailpos > winstate->frameheadpos)
- {
- int64 overlapstart = Max(winstate->groupheadpos,
- winstate->frameheadpos);
+ for (;;)
+ {
+ 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 + relpos;
+ mark_pos = abs_pos;
- if (abs_pos == overlapstart)
- abs_pos = winstate->currentpos;
- else
- abs_pos += winstate->grouptailpos - overlapstart - 1;
- }
- break;
- default:
- elog(ERROR, "unrecognized frame option state: 0x%x",
- winstate->frameOptions);
- break;
- }
- 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 + relpos;
+ /*
+ * Account for exclusion option if one is active, but advance only
+ * abs_pos not mark_pos. This prevents changes of the current
+ * row's peer group from resulting in trying to fetch a row before
+ * some previous mark position.
+ *
+ * Note that in some corner cases such as current row being
+ * outside frame, these calculations are theoretically too simple,
+ * but it doesn't matter because we'll end up deciding the row is
+ * out of frame. We do not attempt to avoid fetching rows past
+ * end of frame; that would happen in some cases anyway.
+ */
+ switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
+ {
+ case 0:
+ /* no adjustment needed */
+ break;
+ case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
+ if (abs_pos >= winstate->currentpos &&
+ winstate->currentpos >= winstate->frameheadpos)
+ abs_pos++;
+ break;
+ case FRAMEOPTION_EXCLUDE_GROUP:
+ update_grouptailpos(winstate);
+ if (abs_pos >= winstate->groupheadpos &&
+ winstate->grouptailpos > winstate->frameheadpos)
+ {
+ int64 overlapstart = Max(winstate->groupheadpos,
+ winstate->frameheadpos);
- /*
- * Account for exclusion option if one is active. If there is no
- * exclusion, we can safely set the mark at the accessed row. But
- * if there is, we can only mark the frame start, because we can't
- * be sure how far back in the frame the exclusion might cause us
- * to fetch in future. Furthermore, we have to actually check
- * against frameheadpos here, since it's unsafe to try to fetch a
- * row before frame start if the mark might be there already.
- */
- switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
- {
- case 0:
- /* no adjustment needed */
- mark_pos = abs_pos;
- break;
- case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
- if (abs_pos <= winstate->currentpos &&
- winstate->currentpos < winstate->frametailpos)
- abs_pos--;
- update_frameheadpos(winstate);
- if (abs_pos < winstate->frameheadpos)
- goto out_of_frame;
- mark_pos = winstate->frameheadpos;
- break;
- case FRAMEOPTION_EXCLUDE_GROUP:
- update_grouptailpos(winstate);
- if (abs_pos < winstate->grouptailpos &&
- winstate->groupheadpos < winstate->frametailpos)
- {
- int64 overlapend = Min(winstate->grouptailpos,
- winstate->frametailpos);
+ abs_pos += winstate->grouptailpos - overlapstart;
+ }
+ break;
+ case FRAMEOPTION_EXCLUDE_TIES:
+ update_grouptailpos(winstate);
+ if (abs_pos >= winstate->groupheadpos &&
+ winstate->grouptailpos > winstate->frameheadpos)
+ {
+ int64 overlapstart = Max(winstate->groupheadpos,
+ winstate->frameheadpos);
- abs_pos -= overlapend - winstate->groupheadpos;
- }
- update_frameheadpos(winstate);
- if (abs_pos < winstate->frameheadpos)
- goto out_of_frame;
- mark_pos = winstate->frameheadpos;
- break;
- case FRAMEOPTION_EXCLUDE_TIES:
- update_grouptailpos(winstate);
- if (abs_pos < winstate->grouptailpos &&
- winstate->groupheadpos < winstate->frametailpos)
- {
- int64 overlapend = Min(winstate->grouptailpos,
- winstate->frametailpos);
+ if (abs_pos == overlapstart)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos += winstate->grouptailpos - overlapstart - 1;
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized frame option state: 0x%x",
+ winstate->frameOptions);
+ break;
+ }
+ 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 + relpos;
- if (abs_pos == overlapend - 1)
- abs_pos = winstate->currentpos;
- else
- abs_pos -= overlapend - 1 - winstate->groupheadpos;
- }
- update_frameheadpos(winstate);
- if (abs_pos < winstate->frameheadpos)
- goto out_of_frame;
- mark_pos = winstate->frameheadpos;
- break;
- default:
- elog(ERROR, "unrecognized frame option state: 0x%x",
- winstate->frameOptions);
- mark_pos = 0; /* keep compiler quiet */
- break;
- }
- break;
- default:
- elog(ERROR, "unrecognized window seek type: %d", seektype);
- abs_pos = mark_pos = 0; /* keep compiler quiet */
- break;
- }
+ /*
+ * Account for exclusion option if one is active. If there is no
+ * exclusion, we can safely set the mark at the accessed row. But
+ * if there is, we can only mark the frame start, because we can't
+ * be sure how far back in the frame the exclusion might cause us
+ * to fetch in future. Furthermore, we have to actually check
+ * against frameheadpos here, since it's unsafe to try to fetch a
+ * row before frame start if the mark might be there already.
+ */
+ switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
+ {
+ case 0:
+ /* no adjustment needed */
+ mark_pos = abs_pos;
+ break;
+ case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
+ if (abs_pos <= winstate->currentpos &&
+ winstate->currentpos < winstate->frametailpos)
+ abs_pos--;
+ update_frameheadpos(winstate);
+ if (abs_pos < winstate->frameheadpos)
+ goto out_of_frame;
+ mark_pos = winstate->frameheadpos;
+ break;
+ case FRAMEOPTION_EXCLUDE_GROUP:
+ update_grouptailpos(winstate);
+ if (abs_pos < winstate->grouptailpos &&
+ winstate->groupheadpos < winstate->frametailpos)
+ {
+ int64 overlapend = Min(winstate->grouptailpos,
+ winstate->frametailpos);
- if (!window_gettupleslot(winobj, abs_pos, slot))
- goto out_of_frame;
+ abs_pos -= overlapend - winstate->groupheadpos;
+ }
+ update_frameheadpos(winstate);
+ if (abs_pos < winstate->frameheadpos)
+ goto out_of_frame;
+ mark_pos = winstate->frameheadpos;
+ break;
+ case FRAMEOPTION_EXCLUDE_TIES:
+ update_grouptailpos(winstate);
+ if (abs_pos < winstate->grouptailpos &&
+ winstate->groupheadpos < winstate->frametailpos)
+ {
+ int64 overlapend = Min(winstate->grouptailpos,
+ winstate->frametailpos);
- /* The code above does not detect all out-of-frame cases, so check */
- if (row_is_in_frame(winstate, abs_pos, slot) <= 0)
- goto out_of_frame;
+ if (abs_pos == overlapend - 1)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ }
+ update_frameheadpos(winstate);
+ if (abs_pos < winstate->frameheadpos)
+ goto out_of_frame;
+ mark_pos = winstate->frameheadpos;
+ break;
+ default:
+ elog(ERROR, "unrecognized frame option state: 0x%x",
+ winstate->frameOptions);
+ mark_pos = 0; /* keep compiler quiet */
+ break;
+ }
+ break;
+ default:
+ elog(ERROR, "unrecognized window seek type: %d", seektype);
+ abs_pos = mark_pos = 0; /* keep compiler quiet */
+ break;
+ }
- if (isout)
- *isout = false;
- if (set_mark)
- WinSetMarkPosition(winobj, mark_pos);
- econtext->ecxt_outertuple = slot;
- return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
- econtext, isnull);
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
+
+ /* The code above does not detect all out-of-frame cases, so check */
+ if (row_is_in_frame(winstate, abs_pos, slot) <= 0)
+ goto out_of_frame;
+
+ /* Evaluate the expression at this row */
+ econtext->ecxt_outertuple = slot;
+ result = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+
+ /* If we got a null and we're ignoring them, move the goalposts */
+ if (ignore_nulls && *isnull)
+ target += step;
+
+ /* Once we've reached our target, we're done */
+ if (relpos == target)
+ {
+ if (isout)
+ *isout = false;
+ /*
+ * We can only mark the row if we're not ignoring nulls (because we
+ * jump straight there).
+ */
+ if (set_mark && !ignore_nulls)
+ WinSetMarkPosition(winobj, mark_pos);
+ return result;
+ }
+
+ /* Otherwise move closer and try again */
+ relpos += step;
+ }
out_of_frame:
if (isout)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index d8cf87e6d0..102d1cbb6c 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1533,6 +1533,7 @@ _copyWindowFunc(const WindowFunc *from)
COPY_SCALAR_FIELD(winref);
COPY_SCALAR_FIELD(winstar);
COPY_SCALAR_FIELD(winagg);
+ COPY_SCALAR_FIELD(winnulltreatment);
COPY_LOCATION_FIELD(location);
return newnode;
@@ -2689,6 +2690,7 @@ _copyFuncCall(const FuncCall *from)
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
COPY_NODE_FIELD(over);
+ COPY_SCALAR_FIELD(win_null_treatment);
COPY_LOCATION_FIELD(location);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 627b026b19..e5c281f59e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -264,6 +264,7 @@ _equalWindowFunc(const WindowFunc *a, const WindowFunc *b)
COMPARE_SCALAR_FIELD(winref);
COMPARE_SCALAR_FIELD(winstar);
COMPARE_SCALAR_FIELD(winagg);
+ COMPARE_SCALAR_FIELD(winnulltreatment);
COMPARE_LOCATION_FIELD(location);
return true;
@@ -2383,6 +2384,7 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
COMPARE_NODE_FIELD(over);
+ COMPARE_SCALAR_FIELD(win_null_treatment);
COMPARE_LOCATION_FIELD(location);
return true;
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index b442b5a29e..2367f3666c 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -593,6 +593,7 @@ makeFuncCall(List *name, List *args, int location)
n->agg_distinct = false;
n->func_variadic = false;
n->over = NULL;
+ n->win_null_treatment = NULL_TREATMENT_NONE;
n->location = location;
return n;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e2f177515d..71ae6a8b89 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1184,6 +1184,7 @@ _outWindowFunc(StringInfo str, const WindowFunc *node)
WRITE_UINT_FIELD(winref);
WRITE_BOOL_FIELD(winstar);
WRITE_BOOL_FIELD(winagg);
+ WRITE_INT_FIELD(winnulltreatment);
WRITE_LOCATION_FIELD(location);
}
@@ -2772,6 +2773,7 @@ _outFuncCall(StringInfo str, const FuncCall *node)
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
WRITE_NODE_FIELD(over);
+ WRITE_BOOL_FIELD(win_null_treatment);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 42050ab719..0fca146299 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -654,6 +654,7 @@ _readWindowFunc(void)
READ_UINT_FIELD(winref);
READ_BOOL_FIELD(winstar);
READ_BOOL_FIELD(winagg);
+ READ_INT_FIELD(winnulltreatment);
READ_LOCATION_FIELD(location);
READ_DONE();
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 0c6fe0115a..1762208197 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2454,6 +2454,7 @@ eval_const_expressions_mutator(Node *node,
newexpr->winref = expr->winref;
newexpr->winstar = expr->winstar;
newexpr->winagg = expr->winagg;
+ newexpr->winnulltreatment = expr->winnulltreatment;
newexpr->location = expr->location;
return (Node *) newexpr;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4ff35095b8..6dd25b5c27 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -588,6 +588,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> null_treatment
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
@@ -656,7 +657,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 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
@@ -688,7 +689,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+ RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
@@ -7765,6 +7766,10 @@ createfunc_opt_item:
{
$$ = makeDefElem("window", (Node *)makeInteger(true), @1);
}
+ | TREAT NULLS_P
+ {
+ $$ = makeDefElem("null_treatment", (Node *)makeInteger(true), @1);
+ }
| common_func_opt_item
{
$$ = $1;
@@ -13693,6 +13698,14 @@ func_expr: func_application within_group_clause filter_clause over_clause
}
n->agg_filter = $3;
n->over = $4;
+ n->win_null_treatment = NULL_TREATMENT_NONE;
+ $$ = (Node *) n;
+ }
+ | func_application null_treatment over_clause
+ {
+ FuncCall *n = (FuncCall *) $1;
+ n->over = $3;
+ n->win_null_treatment = $2;
$$ = (Node *) n;
}
| func_expr_common_subexpr
@@ -14050,6 +14063,11 @@ window_definition:
}
;
+null_treatment:
+ IGNORE_P NULLS_P { $$ = NULL_TREATMENT_IGNORE; }
+ | RESPECT NULLS_P { $$ = NULL_TREATMENT_RESPECT; }
+ ;
+
over_clause: OVER window_specification
{ $$ = $2; }
| OVER ColId
@@ -15126,6 +15144,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -15232,6 +15251,7 @@ unreserved_keyword:
| REPLACE
| REPLICA
| RESET
+ | RESPECT
| RESTART
| RESTRICT
| RETURNS
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 9c3b6ad916..cd507720d5 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -96,6 +96,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
WindowDef *over = (fn ? fn->over : NULL);
+ NullTreatment win_null_treatment = (fn ? fn->win_null_treatment : NULL_TREATMENT_NONE);
bool could_be_projection;
Oid rettype;
Oid funcid;
@@ -107,6 +108,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
Oid *declared_arg_types;
List *argnames;
List *argdefaults;
+ bool null_treatment;
Node *retval;
bool retset;
int nvargs;
@@ -265,7 +267,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
!func_variadic, true,
&funcid, &rettype, &retset,
&nvargs, &vatype,
- &declared_arg_types, &argdefaults);
+ &declared_arg_types, &argdefaults,
+ &null_treatment);
cancel_parser_errposition_callback(&pcbstate);
@@ -505,6 +508,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 (over && win_null_treatment != NULL_TREATMENT_NONE)
+ 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)
@@ -525,6 +535,13 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("window function %s cannot have WITHIN GROUP",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+
+ if (!null_treatment && win_null_treatment != NULL_TREATMENT_NONE)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("window function %s does not accept RESPECT/IGNORE NULLS",
+ NameListToString(funcname)),
+ parser_errposition(pstate, location)));
}
else if (fdresult == FUNCDETAIL_COERCION)
{
@@ -822,6 +839,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
wfunc->winstar = agg_star;
wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
wfunc->aggfilter = agg_filter;
+ wfunc->winnulltreatment = win_null_treatment;
wfunc->location = location;
/*
@@ -1392,7 +1410,8 @@ func_get_detail(List *funcname,
int *nvargs, /* return value */
Oid *vatype, /* return value */
Oid **true_typeids, /* return value */
- List **argdefaults) /* optional return value */
+ List **argdefaults, /* optional return value */
+ bool *null_treatment) /* optional return value */
{
FuncCandidateList raw_candidates;
FuncCandidateList best_candidate;
@@ -1406,6 +1425,8 @@ func_get_detail(List *funcname,
*true_typeids = NULL;
if (argdefaults)
*argdefaults = NIL;
+ if (null_treatment)
+ *null_treatment = NULL_TREATMENT_NONE;
/* Get list of possible candidates from namespace search */
raw_candidates = FuncnameGetCandidates(funcname, nargs, fargnames,
@@ -1683,6 +1704,8 @@ func_get_detail(List *funcname,
*argdefaults = defaults;
}
}
+ if (null_treatment)
+ *null_treatment = pform->pronulltreatment;
switch (pform->prokind)
{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 2cbcb4b85e..c78c310d10 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2661,6 +2661,9 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
if (proc->prokind == PROKIND_WINDOW)
appendStringInfoString(&buf, " WINDOW");
+ if (proc->pronulltreatment)
+ appendStringInfoString(&buf, " TREAT NULLS");
+
switch (proc->provolatile)
{
case PROVOLATILE_IMMUTABLE:
@@ -9485,7 +9488,12 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
- appendStringInfoString(buf, ") OVER ");
+ if (wfunc->winnulltreatment == NULL_TREATMENT_IGNORE)
+ appendStringInfoString(buf, ") IGNORE NULLS OVER ");
+ else if (wfunc->winnulltreatment == NULL_TREATMENT_RESPECT)
+ appendStringInfoString(buf, ") RESPECT NULLS OVER ");
+ else
+ appendStringInfoString(buf, ") OVER ");
foreach(l, context->windowClause)
{
@@ -11021,7 +11029,7 @@ generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes,
!use_variadic, true,
&p_funcid, &p_rettype,
&p_retset, &p_nvargs, &p_vatype,
- &p_true_typeids, NULL);
+ &p_true_typeids, NULL, NULL);
else
{
p_result = FUNCDETAIL_NOTFOUND;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 61f2c2f5b4..ed0e44c1f2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9684,33 +9684,42 @@
proargtypes => 'int4', prosrc => 'window_ntile' },
{ oid => '3106', descr => 'fetch the preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_lag' },
+ proargtypes => 'anyelement', prosrc => 'window_lag',
+ pronulltreatment => 't' },
{ oid => '3107', descr => 'fetch the Nth preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset' },
+ proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset',
+ pronulltreatment => 't' },
{ oid => '3108', descr => 'fetch the Nth preceding row value with default',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4 anyelement',
- prosrc => 'window_lag_with_offset_and_default' },
+ prosrc => 'window_lag_with_offset_and_default',
+ pronulltreatment => 't' },
{ oid => '3109', descr => 'fetch the following row value',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_lead' },
+ proargtypes => 'anyelement', prosrc => 'window_lead',
+ pronulltreatment => 't' },
{ oid => '3110', descr => 'fetch the Nth following row value',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset' },
+ proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset',
+ pronulltreatment => 't' },
{ oid => '3111', descr => 'fetch the Nth following row value with default',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4 anyelement',
- prosrc => 'window_lead_with_offset_and_default' },
+ prosrc => 'window_lead_with_offset_and_default',
+ pronulltreatment => 't' },
{ oid => '3112', descr => 'fetch the first row value',
proname => 'first_value', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_first_value' },
+ proargtypes => 'anyelement', prosrc => 'window_first_value',
+ pronulltreatment => 't' },
{ oid => '3113', descr => 'fetch the last row value',
proname => 'last_value', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement', prosrc => 'window_last_value' },
+ proargtypes => 'anyelement', prosrc => 'window_last_value',
+ pronulltreatment => 't' },
{ oid => '3114', descr => 'fetch the Nth row value',
proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
- proargtypes => 'anyelement int4', prosrc => 'window_nth_value' },
+ proargtypes => 'anyelement int4', prosrc => 'window_nth_value',
+ pronulltreatment => 't' },
# functions for range types
{ oid => '3832', descr => 'I/O',
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ee3959da09..983af5905f 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -67,6 +67,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce
/* strict with respect to NULLs? */
bool proisstrict BKI_DEFAULT(t);
+ /* can handle IGNORE/RESPECT NULLS? (must be window function) */
+ bool pronulltreatment BKI_DEFAULT(f);
+
/* returns a set? */
bool proretset BKI_DEFAULT(f);
@@ -191,6 +194,7 @@ extern ObjectAddress ProcedureCreate(const char *procedureName,
bool security_definer,
bool isLeakProof,
bool isStrict,
+ bool null_treatment,
char volatility,
char parallel,
oidvector *parameterTypes,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5e1ffafb91..f05562f2c6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -357,6 +357,7 @@ typedef struct FuncCall
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
struct WindowDef *over; /* OVER clause, if any */
+ NullTreatment win_null_treatment; /* IGNORE NULLS or RESPECT NULLS? */
int location; /* token location, or -1 if unknown */
} FuncCall;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d73be2ad46..f536616106 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -52,6 +52,13 @@ typedef enum OnCommitAction
ONCOMMIT_DROP /* ON COMMIT DROP */
} OnCommitAction;
+typedef enum NullTreatment
+{
+ NULL_TREATMENT_NONE = 0,
+ NULL_TREATMENT_RESPECT,
+ NULL_TREATMENT_IGNORE
+} NullTreatment;
+
/*
* RangeVar - range variable, used in FROM clauses
*
@@ -379,6 +386,7 @@ typedef struct WindowFunc
Index winref; /* index of associated WindowClause */
bool winstar; /* true if argument list was really '*' */
bool winagg; /* is function a simple aggregate? */
+ NullTreatment winnulltreatment; /* can accept RESPECT/IGNORE NULLS? */
int location; /* token location, or -1 if unknown */
} WindowFunc;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 08f22ce211..a15c15eb3a 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -190,6 +190,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD)
PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD)
PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD)
PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
@@ -342,6 +343,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
+PG_KEYWORD("respect", RESPECT, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD)
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index dd189f5452..ead6241525 100644
--- a/src/include/parser/parse_func.h
+++ b/src/include/parser/parse_func.h
@@ -41,7 +41,8 @@ extern FuncDetailCode func_get_detail(List *funcname,
bool expand_variadic, bool expand_defaults,
Oid *funcid, Oid *rettype,
bool *retset, int *nvargs, Oid *vatype,
- Oid **true_typeids, List **argdefaults);
+ Oid **true_typeids, List **argdefaults,
+ bool *null_treatment);
extern int func_match_argtypes(int nargs,
Oid *input_typeids,
diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out
index ba260df996..a4468d7bec 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -104,6 +104,12 @@ SELECT proname, prosecdef FROM pg_proc
functest_c_3 | t
(3 rows)
+--
+-- TREAT NULLS
+--
+CREATE FUNCTION functest_D_1(integer) RETURNS integer LANGUAGE 'sql'
+ TREAT NULLS AS 'SELECT $1';
+ERROR: cannot set null treatment on a non-window function
--
-- LEAKPROOF
--
@@ -280,6 +286,8 @@ CREATE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL AS 'SELECT $1';
CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL WINDOW AS 'SELECT $1';
ERROR: cannot change routine kind
DETAIL: "functest1" is a function.
+CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL TREAT NULLS AS 'SELECT $1';
+ERROR: cannot set null treatment on a non-window function
CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1';
ERROR: cannot change routine kind
DETAIL: "functest1" is a function.
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index 211a42cefa..704052ec2c 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -161,6 +161,10 @@ CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (
ERROR: invalid attribute in procedure definition
LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT I...
^
+CREATE PROCEDURE ptestx() LANGUAGE SQL TREAT NULLS AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
+ERROR: invalid attribute in procedure definition
+LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL TREAT NULLS AS $$ INS...
+ ^
CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
ERROR: invalid attribute in procedure definition
LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT I...
diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index 8538173ff8..3b310cf362 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -109,3 +109,13 @@ ORDER BY 1, 2;
pg_largeobject_metadata | lomacl | aclitem[]
(11 rows)
+-- **************** pg_class ****************
+-- Look for non-window functions with null treatment (there should be none)
+SELECT proname, prokind, pronulltreatment
+FROM pg_proc
+WHERE pronulltreatment AND prokind <> 'w'
+ORDER BY oid;
+ proname | prokind | pronulltreatment
+---------+---------+------------------
+(0 rows)
+
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d5fd4045f9..29d0247a7a 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3863,3 +3863,214 @@ SELECT * FROM pg_temp.f(2);
{5}
(5 rows)
+-- IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+ name text,
+ orbit integer
+);
+INSERT INTO planets VALUES
+ ('mercury', 88),
+ ('venus', 224),
+ ('earth', NULL),
+ ('mars', NULL),
+ ('jupiter', 4332),
+ ('saturn', 24491),
+ ('uranus', NULL),
+ ('neptune', 60182),
+ ('pluto', 90560),
+ ('xyzzy', 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 planets.name, +
+ planets.orbit, +
+ lag(planets.orbit) OVER w AS lag, +
+ lag(planets.orbit) RESPECT NULLS OVER w AS lag_respect,+
+ lag(planets.orbit) IGNORE NULLS OVER w AS lag_ignore +
+ FROM planets +
+ WINDOW w AS (ORDER BY planets.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)
+
+-- window function without null treatment
+SELECT row_number() OVER (ORDER BY name) FROM planets;
+ row_number
+------------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT row_number() RESPECT NULLS OVER (ORDER BY name) FROM planets; -- fails
+ERROR: window function row_number does not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT row_number() RESPECT NULLS OVER (ORDER BY name) FROM ...
+ ^
+SELECT row_number() IGNORE NULLS OVER (ORDER BY name) FROM planets; -- fails
+ERROR: window function row_number does not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT row_number() IGNORE NULLS OVER (ORDER BY name) FROM p...
+ ^
+-- regular aggregate
+SELECT sum(orbit) OVER () FROM planets;
+ 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;
+ ^
+--cleanup
+DROP TABLE planets CASCADE;
+NOTICE: drop cascades to view planets_view
diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql
index 7a2df0ea8a..ee4fe64237 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -78,6 +78,12 @@ SELECT proname, prosecdef FROM pg_proc
'functest_C_2'::regproc,
'functest_C_3'::regproc) ORDER BY proname;
+--
+-- TREAT NULLS
+--
+CREATE FUNCTION functest_D_1(integer) RETURNS integer LANGUAGE 'sql'
+ TREAT NULLS AS 'SELECT $1';
+
--
-- LEAKPROOF
--
@@ -186,6 +192,7 @@ DROP FUNCTION functest_b_2; -- error, ambiguous
CREATE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL AS 'SELECT $1';
CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL WINDOW AS 'SELECT $1';
+CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL TREAT NULLS AS 'SELECT $1';
CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1';
DROP FUNCTION functest1(a int);
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index 89b96d580f..a3c253698a 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -118,6 +118,7 @@ CALL version(); -- error: not a procedure
CALL sum(1); -- error: not a procedure
CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
+CREATE PROCEDURE ptestx() LANGUAGE SQL TREAT NULLS AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index 3ce32e4725..ccb4f008d9 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -94,3 +94,12 @@ WHERE c.oid < 16384 AND
relkind = 'r' AND
attstorage != 'p'
ORDER BY 1, 2;
+
+-- **************** pg_class ****************
+
+-- Look for non-window functions with null treatment (there should be none)
+
+SELECT proname, prokind, pronulltreatment
+FROM pg_proc
+WHERE pronulltreatment AND prokind <> 'w'
+ORDER BY oid;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index fe273aa31e..35a7081a45 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1276,3 +1276,104 @@ $$ 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,
+ orbit integer
+);
+
+INSERT INTO planets VALUES
+ ('mercury', 88),
+ ('venus', 224),
+ ('earth', NULL),
+ ('mars', NULL),
+ ('jupiter', 4332),
+ ('saturn', 24491),
+ ('uranus', NULL),
+ ('neptune', 60182),
+ ('pluto', 90560),
+ ('xyzzy', 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)
+;
+
+-- window function without null treatment
+SELECT row_number() OVER (ORDER BY name) FROM planets;
+SELECT row_number() RESPECT NULLS OVER (ORDER BY name) FROM planets; -- fails
+SELECT row_number() IGNORE NULLS OVER (ORDER BY name) FROM planets; -- fails
+
+-- regular aggregate
+SELECT sum(orbit) OVER () FROM planets;
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+
+--cleanup
+DROP TABLE planets CASCADE;
--
2.17.1
>From b131e074b0399b1859712d5fda8e62754c3eddbd Mon Sep 17 00:00:00 2001
From: Vik Fearing <vik.fear...@protonmail.com>
Date: Thu, 4 Jun 2020 08:23:42 +0200
Subject: [PATCH 2/2] new window functions to aid testing
---
src/backend/utils/adt/windowfuncs.c | 88 ++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 12 ++++
src/test/regress/expected/window.out | 49 ++++++++++++++++
src/test/regress/sql/window.sql | 23 ++++++++
4 files changed, 172 insertions(+)
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index f0c8ae686d..616a8d84a1 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -472,3 +472,91 @@ window_nth_value(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+Datum
+window_nth_value_last(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ bool const_offset;
+ Datum result;
+ bool isnull;
+ int32 nth;
+
+ nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+ const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
+
+ if (nth <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+ errmsg("argument of nth_value_last must be greater than zero")));
+
+ result = WinGetFuncArgInFrame(winobj, 0,
+ -(nth - 1), WINDOW_SEEK_TAIL, const_offset,
+ &isnull, NULL);
+ if (isnull)
+ PG_RETURN_NULL();
+
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+window_abs_nth(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ bool const_offset;
+ Datum result;
+ bool isnull;
+ int32 nth;
+
+ nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+ const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
+
+ if (nth <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+ errmsg("argument of abs_nth must be greater than zero")));
+
+ result = WinGetFuncArgInPartition(winobj, 0,
+ nth - 1,
+ WINDOW_SEEK_HEAD,
+ const_offset,
+ &isnull, NULL);
+ if (isnull)
+ PG_RETURN_NULL();
+
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+window_abs_nth_last(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ bool const_offset;
+ Datum result;
+ bool isnull;
+ int32 nth;
+
+ nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+ const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
+
+ if (nth <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+ errmsg("argument of abs_nth_last must be greater than zero")));
+
+ result = WinGetFuncArgInPartition(winobj, 0,
+ -(nth - 1),
+ WINDOW_SEEK_TAIL,
+ const_offset,
+ &isnull, NULL);
+ if (isnull)
+ PG_RETURN_NULL();
+
+ PG_RETURN_DATUM(result);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ed0e44c1f2..94d78d3abc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9720,6 +9720,18 @@
proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_nth_value',
pronulltreatment => 't' },
+{ oid => '8114', descr => 'fetch the Nth row value',
+ proname => 'nth_value_last', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4', prosrc => 'window_nth_value_last',
+ pronulltreatment => 't' },
+{ oid => '8115', descr => 'fetch the Nth row value',
+ proname => 'abs_nth', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4', prosrc => 'window_abs_nth',
+ pronulltreatment => 't' },
+{ oid => '8116', descr => 'fetch the Nth row value',
+ proname => 'abs_nth_last', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4', prosrc => 'window_abs_nth_last',
+ pronulltreatment => 't' },
# functions for range types
{ oid => '3832', descr => 'I/O',
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 29d0247a7a..f91cb21893 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3998,6 +3998,55 @@ WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLO
xyzzy | | 4332 | 88 | 224 |
(10 rows)
+-- nth_value_last
+SELECT name,
+ orbit,
+ nth_value_last(orbit, 2) RESPECT NULLS OVER w1,
+ nth_value_last(orbit, 2) IGNORE NULLS OVER w1,
+ nth_value_last(orbit, 2) RESPECT NULLS OVER w2,
+ nth_value_last(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_last | nth_value_last | nth_value_last | nth_value_last
+---------+-------+----------------+----------------+----------------+----------------
+ earth | | 224 | 24491 | 4332 |
+ jupiter | 4332 | 224 | 24491 | | 4332
+ mars | | 224 | 24491 | 88 | 88
+ mercury | 88 | 224 | 24491 | 60182 | 60182
+ neptune | 60182 | 224 | 24491 | 90560 | 90560
+ pluto | 90560 | 224 | 24491 | 24491 | 90560
+ saturn | 24491 | 224 | 24491 | | 24491
+ uranus | | 224 | 24491 | 224 | 24491
+ venus | 224 | 224 | 24491 | 224 | 24491
+ xyzzy | | 224 | 24491 | 224 |
+(10 rows)
+
+-- abs_nth, abs_nth_last
+SELECT name,
+ orbit,
+ abs_nth(orbit, 3) RESPECT NULLS OVER w,
+ abs_nth(orbit, 3) IGNORE NULLS OVER w,
+ abs_nth_last(orbit, 3) RESPECT NULLS OVER w,
+ abs_nth_last(orbit, 3) IGNORE NULLS OVER w
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+ name | orbit | abs_nth | abs_nth | abs_nth_last | abs_nth_last
+---------+-------+---------+---------+--------------+--------------
+ earth | | | 60182 | | 90560
+ jupiter | 4332 | | 60182 | | 90560
+ mars | | | 60182 | | 90560
+ mercury | 88 | | 60182 | | 90560
+ neptune | 60182 | | 60182 | | 90560
+ pluto | 90560 | | 60182 | | 90560
+ saturn | 24491 | | 60182 | | 90560
+ uranus | | | 60182 | | 90560
+ venus | 224 | | 60182 | | 90560
+ xyzzy | | | 60182 | | 90560
+(10 rows)
+
-- last_value
SELECT name,
orbit,
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 35a7081a45..2b5048784c 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1353,6 +1353,29 @@ WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLO
w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
;
+-- nth_value_last
+SELECT name,
+ orbit,
+ nth_value_last(orbit, 2) RESPECT NULLS OVER w1,
+ nth_value_last(orbit, 2) IGNORE NULLS OVER w1,
+ nth_value_last(orbit, 2) RESPECT NULLS OVER w2,
+ nth_value_last(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)
+;
+
+-- abs_nth, abs_nth_last
+SELECT name,
+ orbit,
+ abs_nth(orbit, 3) RESPECT NULLS OVER w,
+ abs_nth(orbit, 3) IGNORE NULLS OVER w,
+ abs_nth_last(orbit, 3) RESPECT NULLS OVER w,
+ abs_nth_last(orbit, 3) IGNORE NULLS OVER w
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
-- last_value
SELECT name,
orbit,
--
2.17.1
