Hi, it looks like Vik Fearing's patch does not apply anymore, because there
are many conflicts with recent changes, fixed patch attached.
I am interested in reviewing and testing it for the next commitfest, if
it's design and implementation is found to be acceptable.
Additionally, if it is also acceptable, I can add support for handling
negative indexes for nth_value(), to be able to reverse order from
first/from last for the window frame.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2783985b55..44c8d006d0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19735,6 +19735,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.
@@ -19748,14 +19756,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/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 3031c52991..747e7e148f 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 9aa853748d..0451db0eab 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;
@@ -2375,6 +2376,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 4504b1503b..354451067a 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 051f1f1d49..52448c2466 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
@@ -7800,6 +7801,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;
@@ -13751,6 +13756,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
@@ -14171,6 +14184,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
@@ -15252,6 +15270,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -15358,6 +15377,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 c01da4bf01..4cb1a48777 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9757,33 +9757,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 7ef9b0eac0..e4927c1fb7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -359,6 +359,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;
