Fixed patch attached, after new introduced conflicts.
Vik, can you add it to the next commitfest, to be able to test it.
Also, all tests from Oliver Ford's old patch also passed successfully.
На пт, 13.11.2020 г. в 0:44 ч. Vik Fearing <v...@postgresfriends.org> написа:
> On 11/12/20 11:35 PM, Krasiyan Andreev wrote:
> > Hi, it looks like Vik Fearing's patch does not apply anymore, because
> there
> > are many conflicts with recent changes, fixed patch attached.
>
> Thanks! I've been away from this list for a while and I have some
> catching up to do.
> --
> Vik Fearing
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7d06b979eb..e69977de76 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 3c1eaea651..31e08c26b4 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 3fdd87823e..685454c1ec 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1770,6 +1770,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>
@@ -1783,6 +1785,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 7664bb6285..fea8778ec8 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -627,6 +627,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,
@@ -848,7 +849,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 1dd9ecc063..2783b0d630 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,
@@ -307,6 +308,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);
@@ -386,6 +388,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/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index caa971c435..0615b08858 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -508,9 +508,9 @@ 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 function YES
-T618 NTH_VALUE function NO function exists, but some options missing
+T618 NTH_VALUE function NO supported except FROM LAST is not implemented
T619 Nested window functions NO
T620 WINDOW clause: GROUPS option YES
T621 Enhanced numeric functions YES
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index c3ce480c8f..245a768463 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -697,6 +697,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,
@@ -710,6 +711,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;
@@ -765,6 +767,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,
@@ -816,6 +832,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)
@@ -941,6 +965,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
isStrict,
security,
isLeakProof;
+ bool null_treatment;
char volatility;
ArrayType *proconfig;
float4 procost;
@@ -964,6 +989,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
/* Set default attributes */
isWindowFunc = false;
isStrict = false;
+ null_treatment = false;
security = false;
isLeakProof = false;
volatility = PROVOLATILE_VOLATILE;
@@ -979,7 +1005,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);
@@ -1159,6 +1185,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 483bb65ddc..003a3434e3 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1592,6 +1592,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 de58df3d3f..832b40b849 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;
/*
@@ -2473,6 +2474,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;
/* It's a real window function, so set up to call it. */
@@ -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;
- 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);
+ relpos = 0;
+ }
- 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;
+ 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;
- /*
- * 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);
+ /*
+ * 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);
+
+ 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);
+
+ 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;
- 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);
+ /*
+ * 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 -= 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 == 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 (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 (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
- 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;
- /* 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 (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 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 5a591d0a75..29bb505504 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1531,6 +1531,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;
@@ -2688,6 +2689,7 @@ _copyFuncCall(const FuncCall *from)
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
COPY_SCALAR_FIELD(funcformat);
+ 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 e2895a8985..b1267e8da5 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;
@@ -2376,6 +2377,7 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
COMPARE_SCALAR_FIELD(funcformat);
+ 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 ee033ae779..587cd43e6b 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -596,6 +596,7 @@ makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
n->agg_distinct = false;
n->func_variadic = false;
n->funcformat = funcformat;
+ 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 f26498cea2..0646356ccd 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1182,6 +1182,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);
}
@@ -2771,6 +2772,7 @@ _outFuncCall(StringInfo str, const FuncCall *node)
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
WRITE_ENUM_FIELD(funcformat, CoercionForm);
+ 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 ab7b535caa..95d7f04413 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 85ef873caa..ebcc3a1c13 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2486,6 +2486,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 efc9c99754..ffb758bb31 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -590,6 +590,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
@@ -658,7 +659,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
@@ -690,7 +691,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
@@ -7806,6 +7807,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;
@@ -13741,6 +13746,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
@@ -14161,6 +14174,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
@@ -15242,6 +15260,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -15348,6 +15367,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 8b4e3ca5e1..844e8fb79f 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -97,6 +97,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
+ NullTreatment win_null_treatment = (fn ? fn->win_null_treatment : NULL_TREATMENT_NONE);
bool could_be_projection;
Oid rettype;
Oid funcid;
@@ -108,6 +109,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;
@@ -267,7 +269,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);
@@ -507,6 +510,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)
@@ -527,6 +537,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)
{
@@ -824,6 +841,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;
/*
@@ -1394,7 +1412,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;
@@ -1408,6 +1427,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,
@@ -1685,6 +1706,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 c2c6df2a4f..1f44cb54b5 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:
@@ -9475,7 +9478,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)
{
@@ -11228,7 +11236,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 33dacfd340..5e7be2df29 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9764,33 +9764,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 => 'anycompatible',
proargtypes => 'anycompatible int4 anycompatible',
- 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 => 'anycompatible',
proargtypes => 'anycompatible int4 anycompatible',
- 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 f8e6dea22d..31d1dfd465 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);
@@ -198,6 +201,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 d1f9ef29ca..06fa070ff2 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -358,6 +358,7 @@ typedef struct FuncCall
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
CoercionForm funcformat; /* how to display this node */
+ 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 5b190bb99b..9a2a0b4377 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 71dcdf2889..b288422fda 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -190,6 +190,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)
@@ -342,6 +343,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, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL)
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 ce508ae1dc..0311f8ecc9 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -122,6 +122,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
--
@@ -298,6 +304,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 3838fa2324..f81f9b79cc 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -174,6 +174,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 d40afeef78..8615d78075 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 19e2ac518a..3bd9584c6c 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4063,3 +4063,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 bd108a918f..f7fe984e58 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -82,6 +82,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
--
@@ -190,6 +196,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 2ef1c82cea..188f5f85a9 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -130,6 +130,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') $$;
ALTER PROCEDURE ptest1(text) STRICT;
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 eae5fa6017..a021ed3544 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1328,3 +1328,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;
