public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tatsuo Ishii <[email protected]>
To: [email protected]
To: [email protected]
To: [email protected]
To: [email protected]
To: [email protected]
To: [email protected]
Cc: [email protected]
Subject: Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
Date: Mon, 07 Jul 2025 14:37:37 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
Attached is the v16 patch.
In this patch I have changed row_is_in_frame() API from:
static int row_is_in_frame(WindowAggState *winstate, int64 pos,
TupleTableSlot *slot);
to:
static int row_is_in_frame(WindowObject winobj, int64 pos,
TupleTableSlot *slot, bool fetch_tuple);
The function is used to decide whether a row specified by pos is in a
frame or not. Previously we needed to always pass "slot" parameter
which is the row in question, fetched by window_gettupleslot. If
IGNORE NULLS option is not passed to window functions, this is fine
because they need to return the row anyway.
However if IGNORE NULLS specified, we need to throw away null rows
until we find the non null row requested by the caller. In reality,
not in all window frames it is required to pass a row to
row_is_in_frame: only when specific frame options are specified. For
example RANGE or GROUP options plus CURRENT ROW frame end option. In
previous patch, I explicitly checked these frame options before
calling row_is_in_frame. However I dislike the way because it's a
layer abstraction violation.
So in this patch I added "fetch_tuple" option to row_is_in_frame so
that it fetches row itself when necessary. A caller now don't need to
fetch the row to pass if fetch_tuple is false.
This way, not only we can avoid the layer violation problem, but
performance is enhanced because tuple is fetched only when it's
necessary.
Note that now the first argument of row_is_in_frame has been changed
from WindowAggState to WindowObject so that row_is_in_frame can call
window_gettupleslot inside.
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
Attachments:
[application/octet-stream] v16-0001-Add-IGNORE-NULLS-RESPECT-NULLS-option-to-Window-.patch (56.3K, ../[email protected]/2-v16-0001-Add-IGNORE-NULLS-RESPECT-NULLS-option-to-Window-.patch)
download | inline diff:
From 5825053d159e77fc475c0201eea27b23144fcd37 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Mon, 7 Jul 2025 14:12:15 +0900
Subject: [PATCH v16] Add IGNORE NULLS/RESPECT NULLS option to Window
functions.
Add IGNORE NULLS/RESPECT NULLS option to lead, lag, first_value,
last_value and nth_value.
---
doc/src/sgml/func.sgml | 38 ++-
doc/src/sgml/syntax.sgml | 10 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/nodeWindowAgg.c | 454 ++++++++++++++++++++++++---
src/backend/optimizer/util/clauses.c | 1 +
src/backend/parser/gram.y | 19 +-
src/backend/parser/parse_func.c | 9 +
src/backend/utils/adt/ruleutils.c | 7 +-
src/backend/utils/adt/windowfuncs.c | 10 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/primnodes.h | 13 +
src/include/parser/kwlist.h | 2 +
src/include/windowapi.h | 6 +
src/test/regress/expected/window.out | 311 ++++++++++++++++++
src/test/regress/sql/window.sql | 147 +++++++++
15 files changed, 969 insertions(+), 61 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 810b2b50f0d..5f3d9e85d4b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23543,7 +23543,7 @@ SELECT count(*) FROM sometable;
</indexterm>
<function>lag</function> ( <parameter>value</parameter> <type>anycompatible</type>
<optional>, <parameter>offset</parameter> <type>integer</type>
- <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+ <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anycompatible</returnvalue>
</para>
<para>
@@ -23568,7 +23568,7 @@ SELECT count(*) FROM sometable;
</indexterm>
<function>lead</function> ( <parameter>value</parameter> <type>anycompatible</type>
<optional>, <parameter>offset</parameter> <type>integer</type>
- <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+ <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anycompatible</returnvalue>
</para>
<para>
@@ -23591,7 +23591,7 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>first_value</primary>
</indexterm>
- <function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anyelement</returnvalue>
</para>
<para>
@@ -23605,7 +23605,7 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>last_value</primary>
</indexterm>
- <function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anyelement</returnvalue>
</para>
<para>
@@ -23619,7 +23619,7 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>nth_value</primary>
</indexterm>
- <function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> )
+ <function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anyelement</returnvalue>
</para>
<para>
@@ -23668,18 +23668,26 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ The <literal>null treatment</literal> option must be one of:
+<synopsis>
+ RESPECT NULLS
+ IGNORE NULLS
+</synopsis>
+ If unspecified, the default is <literal>RESPECT NULLS</literal> which includes NULL
+ values in any result calculation. <literal>IGNORE NULLS</literal> ignores NULL values.
+ This option is only allowed for the following functions: <function>lag</function>,
+ <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
+ <function>nth_value</function>.
+ </para>
+
<note>
<para>
- The SQL standard defines a <literal>RESPECT NULLS</literal> or
- <literal>IGNORE NULLS</literal> option for <function>lead</function>, <function>lag</function>,
- <function>first_value</function>, <function>last_value</function>, and
- <function>nth_value</function>. This is not implemented in
- <productname>PostgreSQL</productname>: the behavior is always the
- same as the standard's default, namely <literal>RESPECT NULLS</literal>.
- Likewise, the standard's <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
- option for <function>nth_value</function> is not implemented: only the
- default <literal>FROM FIRST</literal> behavior is supported. (You can achieve
- the result of <literal>FROM LAST</literal> by reversing the <literal>ORDER BY</literal>
+ The SQL standard defines a <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
+ option for <function>nth_value</function>. This is not implemented in
+ <productname>PostgreSQL</productname>: only the default <literal>FROM FIRST</literal>
+ behavior is supported. (You can achieve the result of <literal>FROM LAST</literal> by
+ reversing the <literal>ORDER BY</literal>
ordering.)
</para>
</note>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 916189a7d68..237d7306fe8 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1834,8 +1834,8 @@ FROM generate_series(1,10) AS s(i);
The syntax of a window function call is one of the following:
<synopsis>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) <optional>null treatment</optional> [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) <optional>null treatment</optional> [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
</synopsis>
@@ -1873,7 +1873,9 @@ EXCLUDE NO OTHERS
<para>
Here, <replaceable>expression</replaceable> represents any value
- expression that does not itself contain window function calls.
+ expression that does not itself contain window function calls. Some
+ non-aggregate functions allow a <literal>null treatment</literal> clause,
+ described in <xref linkend="functions-window"/>.
</para>
<para>
@@ -2048,7 +2050,7 @@ EXCLUDE NO OTHERS
<para>
The built-in window functions are described in <xref
- linkend="functions-window-table"/>. Other window functions can be added by
+ linkend="functions-window-table"/>. Other window functions can be added by
the user. Also, any built-in or user-defined general-purpose or
statistical aggregate can be used as a window function. (Ordered-set
and hypothetical-set aggregates cannot presently be used as window functions.)
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ebe85337c28..3a8ad201607 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -518,7 +518,7 @@ T612 Advanced OLAP operations YES
T613 Sampling YES
T614 NTILE function YES
T615 LEAD and LAG functions YES
-T616 Null treatment option for LEAD and LAG functions NO
+T616 Null treatment option for LEAD and LAG functions YES
T617 FIRST_VALUE and LAST_VALUE functions YES
T618 NTH_VALUE function NO function exists, but some options missing
T619 Nested window functions NO
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 9a1acce2b5d..8def00425f6 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -69,6 +69,9 @@ 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 */
+ uint8 *notnull_info; /* not null info */
+ int num_notnull_info; /* track size of the notnull_info array */
+ int ignore_nulls; /* ignore nulls */
} WindowObjectData;
/*
@@ -96,6 +99,7 @@ typedef struct WindowStatePerFuncData
bool plain_agg; /* is it just a plain aggregate function? */
int aggno; /* if so, index of its WindowStatePerAggData */
+ int ignore_nulls; /* ignore nulls */
WindowObject winobj; /* object used in window function API */
} WindowStatePerFuncData;
@@ -182,8 +186,8 @@ static void begin_partition(WindowAggState *winstate);
static void spool_tuples(WindowAggState *winstate, int64 pos);
static void release_partition(WindowAggState *winstate);
-static int row_is_in_frame(WindowAggState *winstate, int64 pos,
- TupleTableSlot *slot);
+static int row_is_in_frame(WindowObject winobj, int64 pos,
+ TupleTableSlot *slot, bool fetch_tuple);
static void update_frameheadpos(WindowAggState *winstate);
static void update_frametailpos(WindowAggState *winstate);
static void update_grouptailpos(WindowAggState *winstate);
@@ -198,6 +202,33 @@ static bool are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
static bool window_gettupleslot(WindowObject winobj, int64 pos,
TupleTableSlot *slot);
+static Datum ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
+ int relpos, int seektype,
+ bool set_mark, bool *isnull,
+ bool *isout);
+static Datum gettuple_eval_partition(WindowObject winobj, int argno,
+ int64 abs_pos, bool *isnull, bool *isout);
+static void init_notnull_info(WindowObject winobj);
+static void grow_notnull_info(WindowObject winobj, int64 pos);
+static uint8 get_notnull_info(WindowObject winobj, int64 pos);
+static void put_notnull_info(WindowObject winobj, int64 pos, bool isnull);
+
+/*
+ * Not null info bit array consists of 2-bit items
+ */
+#define NN_UNKNOWN 0x00 /* value not calculated yet */
+#define NN_NULL 0x01 /* NULL */
+#define NN_NOTNULL 0x02 /* NOT NULL */
+#define NN_MASK 0x03 /* mask for NOT NULL MAP */
+#define NN_BITS_PER_MEMBER 2 /* number of bit in not null map */
+/* number of items per variable */
+#define NN_ITEM_PER_VAR (BITS_PER_BYTE / NN_BITS_PER_MEMBER)
+/* convert map position to byte offset */
+#define NN_POS_TO_BYTES(pos) ((pos) / NN_ITEM_PER_VAR)
+/* bytes offset to map position */
+#define NN_BYTES_TO_POS(bytes) ((bytes) * NN_ITEM_PER_VAR)
+/* caculate shift bits */
+#define NN_SHIFT(pos) ((pos) % NN_ITEM_PER_VAR) * NN_BITS_PER_MEMBER
/*
* initialize_windowaggregate
@@ -942,7 +973,7 @@ eval_windowaggregates(WindowAggState *winstate)
* Exit loop if no more rows can be in frame. Skip aggregation if
* current row is not in frame but there might be more in the frame.
*/
- ret = row_is_in_frame(winstate, winstate->aggregatedupto, agg_row_slot);
+ ret = row_is_in_frame(agg_winobj, winstate->aggregatedupto, agg_row_slot, false);
if (ret < 0)
break;
if (ret == 0)
@@ -1263,6 +1294,11 @@ begin_partition(WindowAggState *winstate)
winobj->markpos = -1;
winobj->seekpos = -1;
+
+ /* reset null map */
+ if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
+ memset(perfuncstate->winobj->notnull_info, 0,
+ NN_POS_TO_BYTES(perfuncstate->winobj->num_notnull_info));
}
}
@@ -1412,8 +1448,8 @@ release_partition(WindowAggState *winstate)
* to our window framing rule
*
* The caller must have already determined that the row is in the partition
- * and fetched it into a slot. This function just encapsulates the framing
- * rules.
+ * and fetched it into a slot if fetch_tuple is false.
+.* This function just encapsulates the framing rules.
*
* Returns:
* -1, if the row is out of frame and no succeeding rows can be in frame
@@ -1423,8 +1459,9 @@ release_partition(WindowAggState *winstate)
* May clobber winstate->temp_slot_2.
*/
static int
-row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
+row_is_in_frame(WindowObject winobj, int64 pos, TupleTableSlot *slot, bool fetch_tuple)
{
+ WindowAggState *winstate = winobj->winstate;
int frameOptions = winstate->frameOptions;
Assert(pos >= 0); /* else caller error */
@@ -1453,9 +1490,13 @@ row_is_in_frame(WindowAggState *winstate, int64 pos, TupleTableSlot *slot)
else if (frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
{
/* following row that is not peer is out of frame */
- if (pos > winstate->currentpos &&
- !are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
- return -1;
+ if (pos > winstate->currentpos)
+ {
+ if (fetch_tuple)
+ window_gettupleslot(winobj, pos, slot);
+ if (!are_peers(winstate, slot, winstate->ss.ss_ScanTupleSlot))
+ return -1;
+ }
}
else
Assert(false);
@@ -2619,14 +2660,17 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winref %u",
wfunc->winref, node->winref);
- /* Look for a previous duplicate window function */
+ /*
+ * Look for a previous duplicate window function, which needs the same
+ * ignore_nulls value
+ */
for (i = 0; i <= wfuncno; i++)
{
if (equal(wfunc, perfunc[i].wfunc) &&
!contain_volatile_functions((Node *) wfunc))
break;
}
- if (i <= wfuncno)
+ if (i <= wfuncno && wfunc->ignore_nulls == perfunc[i].ignore_nulls)
{
/* Found a match to an existing entry, so just mark it */
wfuncstate->wfuncno = i;
@@ -2679,6 +2723,8 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winobj->argstates = wfuncstate->args;
winobj->localmem = NULL;
perfuncstate->winobj = winobj;
+ winobj->ignore_nulls = wfunc->ignore_nulls;
+ init_notnull_info(winobj);
/* It's a real window function, so set up to call it. */
fmgr_info_cxt(wfunc->winfnoid, &perfuncstate->flinfo,
@@ -3214,12 +3260,290 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
return true;
}
+/*
+ * get tupple and evaluate in a partition
+ */
+static Datum
+gettuple_eval_partition(WindowObject winobj, int argno,
+ int64 abs_pos, bool *isnull, bool *isout)
+{
+ WindowAggState *winstate;
+ ExprContext *econtext;
+ TupleTableSlot *slot;
+
+ winstate = winobj->winstate;
+ slot = winstate->temp_slot_1;
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ {
+ /* out of partition */
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+ }
+
+ if (isout)
+ *isout = false;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ econtext->ecxt_outertuple = slot;
+ return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+}
+
+/*
+ * ignorenulls_getfuncarginframe
+ * For IGNORE NULLS, get the next nonnull value in the frame, moving forward
+ * or backward until we find a value or reach the frame's end.
+ */
+static Datum
+ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
+ int relpos, int seektype, bool set_mark,
+ bool *isnull, bool *isout)
+{
+ WindowAggState *winstate;
+ ExprContext *econtext;
+ TupleTableSlot *slot;
+ Datum datum;
+ int64 abs_pos;
+ int64 mark_pos;
+ int notnull_offset;
+ int notnull_relpos;
+ int forward;
+
+ Assert(WindowObjectIsValid(winobj));
+ winstate = winobj->winstate;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ slot = winstate->temp_slot_1;
+ datum = (Datum) 0;
+ notnull_offset = 0;
+ notnull_relpos = abs(relpos);
+
+ switch (seektype)
+ {
+ case WINDOW_SEEK_CURRENT:
+ elog(ERROR, "WINDOW_SEEK_CURRENT is not supported for WinGetFuncArgInFrame");
+ abs_pos = mark_pos = 0; /* keep compiler quiet */
+ break;
+ case WINDOW_SEEK_HEAD:
+ /* rejecting relpos < 0 is easy and simplifies code below */
+ if (relpos < 0)
+ goto out_of_frame;
+ update_frameheadpos(winstate);
+ abs_pos = winstate->frameheadpos;
+ mark_pos = winstate->frameheadpos;
+ forward = 1;
+ break;
+ case WINDOW_SEEK_TAIL:
+ /* rejecting relpos > 0 is easy and simplifies code below */
+ if (relpos > 0)
+ goto out_of_frame;
+ update_frametailpos(winstate);
+ abs_pos = winstate->frametailpos - 1;
+ mark_pos = 0; /* keep compiler quiet */
+ forward = -1;
+ break;
+ default:
+ elog(ERROR, "unrecognized window seek type: %d", seektype);
+ abs_pos = mark_pos = 0; /* keep compiler quiet */
+ break;
+ }
+
+ /*
+ * Get the next nonnull value in the frame, moving forward or backward
+ * until we find a value or reach the frame's end.
+ */
+ do
+ {
+ int inframe;
+ int v;
+
+ /*
+ * Check apparent out of frame case. We need to do this because we
+ * may not call window_gettupleslot before row_is_in_frame, which
+ * supposes abs_pos is never negative.
+ */
+ if (abs_pos < 0)
+ goto out_of_frame;
+
+ /* check whether row is in frame */
+ inframe = row_is_in_frame(winobj, abs_pos, slot, true);
+ if (inframe == -1)
+ goto out_of_frame;
+ else if (inframe == 0)
+ goto advance;
+
+ if (isout)
+ *isout = false;
+
+ v = get_notnull_info(winobj, abs_pos);
+ if (v == NN_NULL) /* this row is known to be NULL */
+ goto advance;
+
+ else if (v == NN_UNKNOWN) /* need to check NULL or not */
+ {
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
+
+ econtext->ecxt_outertuple = slot;
+ datum = ExecEvalExpr(
+ (ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+ if (!*isnull)
+ notnull_offset++;
+
+ /* record the row status */
+ put_notnull_info(winobj, abs_pos, *isnull);
+ }
+ else /* this row is known to be NOT NULL */
+ {
+ notnull_offset++;
+ if (notnull_offset > notnull_relpos)
+ {
+ /* to prepare exiting this loop, datum needs to be set */
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
+
+ econtext->ecxt_outertuple = slot;
+ datum = ExecEvalExpr(
+ (ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+ }
+ }
+advance:
+ abs_pos += forward;
+ } while (notnull_offset <= notnull_relpos);
+
+ if (set_mark)
+ WinSetMarkPosition(winobj, mark_pos);
+
+ return datum;
+
+out_of_frame:
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+}
+
+
+/*
+ * init_notnull_info
+ * Initialize non null map.
+ */
+static void
+init_notnull_info(WindowObject winobj)
+{
+#define INIT_NOT_NULL_INFO_NUM 128 /* initial number of notnull info members */
+
+ if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
+ {
+ Size size = NN_POS_TO_BYTES(INIT_NOT_NULL_INFO_NUM);
+
+ winobj->notnull_info = palloc0(size);
+ winobj->num_notnull_info = INIT_NOT_NULL_INFO_NUM;
+ }
+}
+
+/*
+ * grow_notnull_info
+ * expand notnull_info if necessary.
+ * pos: not null info position
+*/
+static void
+grow_notnull_info(WindowObject winobj, int64 pos)
+{
+ if (pos >= winobj->num_notnull_info)
+ {
+ for (;;)
+ {
+ Size oldsize = NN_POS_TO_BYTES(winobj->num_notnull_info);
+ Size newsize = oldsize * 2;
+
+ winobj->notnull_info =
+ repalloc0(winobj->notnull_info, oldsize, newsize);
+ winobj->num_notnull_info = NN_BYTES_TO_POS(newsize);
+ if (winobj->num_notnull_info > pos)
+ break;
+ }
+ }
+}
+
+/*
+ * get_notnull_info
+ * retrieve a map
+ * pos: map position
+ */
+static uint8
+get_notnull_info(WindowObject winobj, int64 pos)
+{
+ uint8 mb;
+ int64 bpos;
+
+ grow_notnull_info(winobj, pos);
+ bpos = NN_POS_TO_BYTES(pos);
+ mb = winobj->notnull_info[bpos];
+ return (mb >> (NN_SHIFT(pos))) & NN_MASK;
+}
+
+/*
+ * put_notnull_info
+ * update map
+ * pos: map position
+ */
+static void
+put_notnull_info(WindowObject winobj, int64 pos, bool isnull)
+{
+ uint8 mb;
+ int64 bpos;
+ uint8 val = isnull ? NN_NULL : NN_NOTNULL;
+ int shift;
+
+ grow_notnull_info(winobj, pos);
+ bpos = NN_POS_TO_BYTES(pos);
+ mb = winobj->notnull_info[bpos];
+ shift = NN_SHIFT(pos);
+ mb &= ~(NN_MASK << shift); /* clear map */
+ mb |= (val << shift); /* update map */
+ winobj->notnull_info[bpos] = mb;
+}
/***********************************************************************
* API exposed to window functions
***********************************************************************/
+/*
+ * WinCheckAndInitializeNullTreatment
+ * Check null treatment clause and sets ignore_nulls
+ *
+ * Window functions should call this to check if they are being called with
+ * a null treatment clause when they don't allow it, or to set ignore_nulls.
+ */
+void
+WinCheckAndInitializeNullTreatment(WindowObject winobj,
+ bool allowNullTreatment,
+ FunctionCallInfo fcinfo)
+{
+ if (winobj->ignore_nulls != NO_NULLTREATMENT && !allowNullTreatment)
+ {
+ HeapTuple proctup;
+ Form_pg_proc procform;
+ Oid funcid;
+
+ funcid = fcinfo->flinfo->fn_oid;
+ proctup = SearchSysCache1(PROCOID,
+ ObjectIdGetDatum(funcid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+ elog(ERROR, "function %s does not allow RESPECT/IGNORE NULLS",
+ NameStr(procform->proname));
+ }
+ else if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
+ winobj->ignore_nulls = IGNORE_NULLS;
+
+}
+
/*
* WinGetPartitionLocalMemory
* Get working memory that lives till end of partition processing
@@ -3378,23 +3702,37 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
bool *isnull, bool *isout)
{
WindowAggState *winstate;
- ExprContext *econtext;
- TupleTableSlot *slot;
- bool gottuple;
int64 abs_pos;
+ Datum datum;
+ bool null_treatment = false;
+ int notnull_offset;
+ int notnull_relpos;
+ int forward;
Assert(WindowObjectIsValid(winobj));
winstate = winobj->winstate;
- econtext = winstate->ss.ps.ps_ExprContext;
- slot = winstate->temp_slot_1;
+
+ if (winobj->ignore_nulls == IGNORE_NULLS && relpos != 0)
+ {
+ null_treatment = true;
+ notnull_offset = 0;
+ notnull_relpos = abs(relpos);
+ forward = relpos > 0 ? 1 : -1;
+ }
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
- abs_pos = winstate->currentpos + relpos;
+ if (null_treatment)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos = winstate->currentpos + relpos;
break;
case WINDOW_SEEK_HEAD:
- abs_pos = relpos;
+ if (null_treatment)
+ abs_pos = 0;
+ else
+ abs_pos = relpos;
break;
case WINDOW_SEEK_TAIL:
spool_tuples(winstate, -1);
@@ -3406,25 +3744,67 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
break;
}
- gottuple = window_gettupleslot(winobj, abs_pos, slot);
-
- if (!gottuple)
+ if (!null_treatment) /* IGNORE NULLS is not specified */
{
- if (isout)
- *isout = true;
- *isnull = true;
- return (Datum) 0;
- }
- else
- {
- if (isout)
- *isout = false;
- if (set_mark)
+ datum = gettuple_eval_partition(winobj, argno,
+ abs_pos, isnull, isout);
+ if (!*isout && set_mark)
WinSetMarkPosition(winobj, abs_pos);
- econtext->ecxt_outertuple = slot;
- return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
- econtext, isnull);
+ return datum;
}
+
+ /*
+ * Get the next nonnull value in the partition, moving forward or backward
+ * until we find a value or reach the partition's end.
+ */
+ do
+ {
+ abs_pos += forward;
+ if (abs_pos < 0)
+ {
+ /* out of partition */
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ datum = 0;
+ break;
+ }
+
+ switch (get_notnull_info(winobj, abs_pos))
+ {
+ case NN_NOTNULL: /* this row is known to be NOT NULL */
+ notnull_offset++;
+ if (notnull_offset >= notnull_relpos)
+ {
+ /* prepare to exit this loop */
+ datum = gettuple_eval_partition(winobj, argno,
+ abs_pos, isnull, isout);
+ }
+ break;
+ case NN_NULL: /* this row is known to be NULL */
+ if (isout)
+ *isout = false;
+ *isnull = true;
+ datum = 0;
+ break;
+ default: /* need to check NULL or not */
+ datum = gettuple_eval_partition(winobj, argno,
+ abs_pos, isnull, isout);
+ if (*isout) /* out of partition? */
+ return datum;
+
+ if (!*isnull)
+ notnull_offset++;
+ /* record the row status */
+ put_notnull_info(winobj, abs_pos, *isnull);
+ break;
+ }
+ } while (notnull_offset < notnull_relpos);
+
+ if (!*isout && set_mark)
+ WinSetMarkPosition(winobj, abs_pos);
+
+ return datum;
}
/*
@@ -3476,6 +3856,10 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
+ if (winobj->ignore_nulls == IGNORE_NULLS)
+ return ignorenulls_getfuncarginframe(winobj, argno, relpos, seektype,
+ set_mark, isnull, isout);
+
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
@@ -3624,7 +4008,7 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
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)
+ if (row_is_in_frame(winobj, abs_pos, slot, false) <= 0)
goto out_of_frame;
if (isout)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index f45131c34c5..2765d794d20 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2572,6 +2572,7 @@ eval_const_expressions_mutator(Node *node,
newexpr->winref = expr->winref;
newexpr->winstar = expr->winstar;
newexpr->winagg = expr->winagg;
+ newexpr->ignore_nulls = expr->ignore_nulls;
newexpr->location = expr->location;
return (Node *) newexpr;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 70a0d832a11..27adff5f9d6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -631,7 +631,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> window_clause window_definition_list opt_partition_clause
%type <windef> window_definition over_clause window_specification
opt_frame_clause frame_extent frame_bound
-%type <ival> opt_window_exclusion_clause
+%type <ival> null_treatment opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
%type <boolean> opt_unique_null_treatment
@@ -729,7 +729,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
- IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
+ IDENTITY_P IF_P IGNORE_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -764,7 +764,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+ RESET RESPECT_P RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
@@ -15796,7 +15796,7 @@ func_application: func_name '(' ')'
* (Note that many of the special SQL functions wouldn't actually make any
* sense as functional index entries, but we ignore that consideration here.)
*/
-func_expr: func_application within_group_clause filter_clause over_clause
+func_expr: func_application within_group_clause filter_clause null_treatment over_clause
{
FuncCall *n = (FuncCall *) $1;
@@ -15829,7 +15829,8 @@ func_expr: func_application within_group_clause filter_clause over_clause
n->agg_within_group = true;
}
n->agg_filter = $3;
- n->over = $4;
+ n->ignore_nulls = $4;
+ n->over = $5;
$$ = (Node *) n;
}
| json_aggregate_func filter_clause over_clause
@@ -16425,6 +16426,12 @@ filter_clause:
/*
* Window Definitions
*/
+null_treatment:
+ IGNORE_P NULLS_P { $$ = PARSER_IGNORE_NULLS; }
+ | RESPECT_P NULLS_P { $$ = PARSER_RESPECT_NULLS; }
+ | /*EMPTY*/ { $$ = NO_NULLTREATMENT; }
+ ;
+
window_clause:
WINDOW window_definition_list { $$ = $2; }
| /*EMPTY*/ { $$ = NIL; }
@@ -17851,6 +17858,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -17969,6 +17977,7 @@ unreserved_keyword:
| REPLACE
| REPLICA
| RESET
+ | RESPECT_P
| RESTART
| RESTRICT
| RETURN
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..3772c514b1e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
+ int ignore_nulls = (fn ? fn->ignore_nulls : 0);
CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
Oid rettype;
@@ -514,6 +515,13 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("%s is not an ordered-set aggregate, so it cannot have WITHIN GROUP",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+
+ /* It also can't treat nulls as a window function */
+ if (ignore_nulls != NO_NULLTREATMENT)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("aggregate functions do not accept RESPECT/IGNORE NULLS"),
+ parser_errposition(pstate, location)));
}
}
else if (fdresult == FUNCDETAIL_WINDOWFUNC)
@@ -834,6 +842,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
wfunc->winstar = agg_star;
wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
wfunc->aggfilter = agg_filter;
+ wfunc->ignore_nulls = ignore_nulls;
wfunc->runCondition = NIL;
wfunc->location = location;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3d6e6bdbfd2..4e837d2afea 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11090,7 +11090,12 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
- appendStringInfoString(buf, ") OVER ");
+ appendStringInfoString(buf, ") ");
+
+ if (wfunc->ignore_nulls == PARSER_IGNORE_NULLS)
+ appendStringInfoString(buf, "IGNORE NULLS ");
+
+ appendStringInfoString(buf, "OVER ");
if (context->windowClause)
{
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index bb35f3bc4a9..969f02aa59b 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -86,6 +86,7 @@ window_row_number(PG_FUNCTION_ARGS)
WindowObject winobj = PG_WINDOW_OBJECT();
int64 curpos = WinGetCurrentPosition(winobj);
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
WinSetMarkPosition(winobj, curpos);
PG_RETURN_INT64(curpos + 1);
}
@@ -141,6 +142,7 @@ window_rank(PG_FUNCTION_ARGS)
rank_context *context;
bool up;
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
@@ -203,6 +205,7 @@ window_dense_rank(PG_FUNCTION_ARGS)
rank_context *context;
bool up;
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
@@ -266,6 +269,7 @@ window_percent_rank(PG_FUNCTION_ARGS)
int64 totalrows = WinGetPartitionRowCount(winobj);
Assert(totalrows > 0);
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
@@ -335,6 +339,7 @@ window_cume_dist(PG_FUNCTION_ARGS)
int64 totalrows = WinGetPartitionRowCount(winobj);
Assert(totalrows > 0);
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
@@ -413,6 +418,7 @@ window_ntile(PG_FUNCTION_ARGS)
WindowObject winobj = PG_WINDOW_OBJECT();
ntile_context *context;
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
context = (ntile_context *)
WinGetPartitionLocalMemory(winobj, sizeof(ntile_context));
@@ -535,6 +541,7 @@ leadlag_common(FunctionCallInfo fcinfo,
bool isnull;
bool isout;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
if (withoffset)
{
offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
@@ -652,6 +659,7 @@ window_first_value(PG_FUNCTION_ARGS)
Datum result;
bool isnull;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
result = WinGetFuncArgInFrame(winobj, 0,
0, WINDOW_SEEK_HEAD, true,
&isnull, NULL);
@@ -673,6 +681,7 @@ window_last_value(PG_FUNCTION_ARGS)
Datum result;
bool isnull;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
result = WinGetFuncArgInFrame(winobj, 0,
0, WINDOW_SEEK_TAIL, true,
&isnull, NULL);
@@ -696,6 +705,7 @@ window_nth_value(PG_FUNCTION_ARGS)
bool isnull;
int32 nth;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
if (isnull)
PG_RETURN_NULL();
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 28e2e8dc0fd..5933e64e9b3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -452,6 +452,7 @@ typedef struct FuncCall
List *agg_order; /* ORDER BY (list of SortBy) */
Node *agg_filter; /* FILTER clause, if any */
struct WindowDef *over; /* OVER clause, if any */
+ int ignore_nulls; /* ignore nulls for window function */
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6dfca3cb35b..e9d8bf74145 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -579,6 +579,17 @@ typedef struct GroupingFunc
* Collation information is irrelevant for the query jumbling, as is the
* internal state information of the node like "winstar" and "winagg".
*/
+
+/*
+ * Null Treatment options. If specified, initially set to PARSER_IGNORE_NULLS
+ * which is then converted to IGNORE_NULLS if the window function allows the
+ * null treatment clause.
+ */
+#define NO_NULLTREATMENT 0
+#define PARSER_IGNORE_NULLS 1
+#define PARSER_RESPECT_NULLS 2
+#define IGNORE_NULLS 3
+
typedef struct WindowFunc
{
Expr xpr;
@@ -602,6 +613,8 @@ typedef struct WindowFunc
bool winstar pg_node_attr(query_jumble_ignore);
/* is function a simple aggregate? */
bool winagg pg_node_attr(query_jumble_ignore);
+ /* ignore nulls. One of the Null Treatment options */
+ int ignore_nulls;
/* token location, or -1 if unknown */
ParseLoc location;
} WindowFunc;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..84182eaaae2 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -202,6 +202,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -378,6 +379,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("respect", RESPECT_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/windowapi.h b/src/include/windowapi.h
index cb2ece166b6..20cfd9e9dd9 100644
--- a/src/include/windowapi.h
+++ b/src/include/windowapi.h
@@ -28,6 +28,8 @@
#ifndef WINDOWAPI_H
#define WINDOWAPI_H
+#include "fmgr.h"
+
/* values of "seektype" */
#define WINDOW_SEEK_CURRENT 0
#define WINDOW_SEEK_HEAD 1
@@ -41,6 +43,10 @@ typedef struct WindowObjectData *WindowObject;
#define WindowObjectIsValid(winobj) \
((winobj) != NULL && IsA(winobj, WindowObjectData))
+extern void WinCheckAndInitializeNullTreatment(WindowObject winobj,
+ bool allowNullTreatment,
+ FunctionCallInfo fcinfo);
+
extern void *WinGetPartitionLocalMemory(WindowObject winobj, Size sz);
extern int64 WinGetCurrentPosition(WindowObject winobj);
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index b86b668f433..f929d81bc8a 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5453,3 +5453,314 @@ SELECT * FROM pg_temp.f(2);
{5}
(5 rows)
+-- IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+ name text,
+ distance text,
+ orbit integer
+);
+INSERT INTO planets VALUES
+ ('mercury', 'close', 88),
+ ('venus', 'close', 224),
+ ('earth', 'close', NULL),
+ ('mars', 'close', NULL),
+ ('jupiter', 'close', 4332),
+ ('saturn', 'far', 24491),
+ ('uranus', 'far', NULL),
+ ('neptune', 'far', 60182),
+ ('pluto', 'far', 90560),
+ ('xyzzy', 'far', NULL);
+-- test ruleutils
+CREATE VIEW planets_view AS
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+NOTICE: view "planets_view" will be a temporary view
+SELECT pg_get_viewdef('planets_view');
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT name, +
+ orbit, +
+ lag(orbit) OVER w AS lag, +
+ lag(orbit) OVER w AS lag_respect, +
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore+
+ FROM planets +
+ WINDOW w AS (ORDER BY name);
+(1 row)
+
+-- lag
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+ name | orbit | lag | lag_respect | lag_ignore
+---------+-------+-------+-------------+------------
+ earth | | | |
+ jupiter | 4332 | | |
+ mars | | 4332 | 4332 | 4332
+ mercury | 88 | | | 4332
+ neptune | 60182 | 88 | 88 | 88
+ pluto | 90560 | 60182 | 60182 | 60182
+ saturn | 24491 | 90560 | 90560 | 90560
+ uranus | | 24491 | 24491 | 24491
+ venus | 224 | | | 24491
+ xyzzy | | 224 | 224 | 224
+(10 rows)
+
+-- lead
+SELECT name,
+ orbit,
+ lead(orbit) OVER w AS lead,
+ lead(orbit) RESPECT NULLS OVER w AS lead_respect,
+ lead(orbit) IGNORE NULLS OVER w AS lead_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+ name | orbit | lead | lead_respect | lead_ignore
+---------+-------+-------+--------------+-------------
+ earth | | 4332 | 4332 | 4332
+ jupiter | 4332 | | | 88
+ mars | | 88 | 88 | 88
+ mercury | 88 | 60182 | 60182 | 60182
+ neptune | 60182 | 90560 | 90560 | 90560
+ pluto | 90560 | 24491 | 24491 | 24491
+ saturn | 24491 | | | 224
+ uranus | | 224 | 224 | 224
+ venus | 224 | | |
+ xyzzy | | | |
+(10 rows)
+
+-- first_value
+SELECT name,
+ orbit,
+ first_value(orbit) RESPECT NULLS OVER w1,
+ first_value(orbit) IGNORE NULLS OVER w1,
+ first_value(orbit) RESPECT NULLS OVER w2,
+ first_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | first_value | first_value | first_value | first_value
+---------+-------+-------------+-------------+-------------+-------------
+ earth | | | 4332 | | 4332
+ jupiter | 4332 | | 4332 | | 4332
+ mars | | | 4332 | | 4332
+ mercury | 88 | | 4332 | 4332 | 4332
+ neptune | 60182 | | 4332 | | 88
+ pluto | 90560 | | 4332 | 88 | 88
+ saturn | 24491 | | 4332 | 60182 | 60182
+ uranus | | | 4332 | 90560 | 90560
+ venus | 224 | | 4332 | 24491 | 24491
+ xyzzy | | | 4332 | | 224
+(10 rows)
+
+-- nth_value
+SELECT name,
+ orbit,
+ nth_value(orbit, 2) RESPECT NULLS OVER w1,
+ nth_value(orbit, 2) IGNORE NULLS OVER w1,
+ nth_value(orbit, 2) RESPECT NULLS OVER w2,
+ nth_value(orbit, 2) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | nth_value | nth_value | nth_value | nth_value
+---------+-------+-----------+-----------+-----------+-----------
+ earth | | 4332 | 88 | 4332 |
+ jupiter | 4332 | 4332 | 88 | 4332 | 88
+ mars | | 4332 | 88 | 4332 | 88
+ mercury | 88 | 4332 | 88 | | 88
+ neptune | 60182 | 4332 | 88 | 88 | 60182
+ pluto | 90560 | 4332 | 88 | 60182 | 60182
+ saturn | 24491 | 4332 | 88 | 90560 | 90560
+ uranus | | 4332 | 88 | 24491 | 24491
+ venus | 224 | 4332 | 88 | | 224
+ xyzzy | | 4332 | 88 | 224 |
+(10 rows)
+
+-- last_value
+SELECT name,
+ orbit,
+ last_value(orbit) RESPECT NULLS OVER w1,
+ last_value(orbit) IGNORE NULLS OVER w1,
+ last_value(orbit) RESPECT NULLS OVER w2,
+ last_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | last_value | last_value | last_value | last_value
+---------+-------+------------+------------+------------+------------
+ earth | | | 224 | | 4332
+ jupiter | 4332 | | 224 | 88 | 88
+ mars | | | 224 | 60182 | 60182
+ mercury | 88 | | 224 | 90560 | 90560
+ neptune | 60182 | | 224 | 24491 | 24491
+ pluto | 90560 | | 224 | | 24491
+ saturn | 24491 | | 224 | 224 | 224
+ uranus | | | 224 | | 224
+ venus | 224 | | 224 | | 224
+ xyzzy | | | 224 | | 224
+(10 rows)
+
+-- exclude current row
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
+;
+ name | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore
+---------+-------+-------------+------------+-----------+-------------+------------
+ earth | | 4332 | 4332 | | 4332 |
+ jupiter | 4332 | 88 | 88 | | 88 |
+ mars | | 4332 | 60182 | 88 | 88 | 4332
+ mercury | 88 | 4332 | 90560 | 60182 | 60182 | 4332
+ neptune | 60182 | 88 | 24491 | 90560 | 90560 | 88
+ pluto | 90560 | 88 | 24491 | 60182 | 24491 | 60182
+ saturn | 24491 | 60182 | 224 | 90560 | 224 | 90560
+ uranus | | 90560 | 224 | 24491 | 224 | 24491
+ venus | 224 | 24491 | 24491 | | | 24491
+ xyzzy | | 224 | 224 | | | 224
+(10 rows)
+
+-- valid and invalid functions
+SELECT sum(orbit) OVER () FROM planets; -- succeeds
+ sum
+--------
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+(10 rows)
+
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+ERROR: aggregate functions do not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT sum(orbit) RESPECT NULLS OVER () FROM planets;
+ ^
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+ERROR: aggregate functions do not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT sum(orbit) IGNORE NULLS OVER () FROM planets;
+ ^
+SELECT row_number() OVER () FROM planets; -- succeeds
+ row_number
+------------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
+ERROR: function row_number does not allow RESPECT/IGNORE NULLS
+SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails
+ERROR: function row_number does not allow RESPECT/IGNORE NULLS
+-- test two consecutive nulls
+update planets set orbit=null where name='jupiter';
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore
+---------+-------+-------------+------------+-----------+-------------+------------
+ earth | | | | | 88 |
+ jupiter | | 88 | 88 | | 88 |
+ mars | | 88 | 60182 | 60182 | 88 |
+ mercury | 88 | 88 | 90560 | 60182 | 60182 |
+ neptune | 60182 | 88 | 24491 | 60182 | 90560 | 88
+ pluto | 90560 | 88 | 24491 | 60182 | 24491 | 60182
+ saturn | 24491 | 60182 | 224 | 90560 | 224 | 90560
+ uranus | | 90560 | 224 | 24491 | 224 | 24491
+ venus | 224 | 24491 | 224 | 224 | | 24491
+ xyzzy | | 224 | 224 | | | 224
+(10 rows)
+
+-- test partitions
+SELECT name,
+ distance,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | distance | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore
+---------+----------+-------+-------------+------------+-----------+-------------+------------
+ earth | close | | | | | 88 |
+ jupiter | close | | 88 | 88 | | 88 |
+ mars | close | | 88 | 224 | 224 | 88 |
+ mercury | close | 88 | 88 | 224 | 224 | 224 |
+ venus | close | 224 | 88 | 224 | 224 | | 88
+ neptune | far | 60182 | 60182 | 24491 | 90560 | 90560 |
+ pluto | far | 90560 | 60182 | 24491 | 90560 | 24491 | 60182
+ saturn | far | 24491 | 60182 | 24491 | 90560 | | 90560
+ uranus | far | | 90560 | 24491 | 24491 | | 24491
+ xyzzy | far | | 24491 | 24491 | | | 24491
+(10 rows)
+
+-- nth_value without nulls
+SELECT x,
+ nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
+ x | nth_value
+---+-----------
+ 1 | 3
+ 2 | 3
+ 3 | 2
+ 4 | 3
+ 5 | 4
+(5 rows)
+
+SELECT x,
+ nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+ x | nth_value
+---+-----------
+ 1 | 2
+ 2 | 2
+ 3 | 2
+ 4 | 3
+ 5 | 4
+(5 rows)
+
+--cleanup
+DROP TABLE planets CASCADE;
+NOTICE: drop cascades to view planets_view
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 02f105f070e..1f8c8669436 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1958,3 +1958,150 @@ $$ LANGUAGE SQL STABLE;
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
SELECT * FROM pg_temp.f(2);
+
+-- IGNORE NULLS tests
+
+CREATE TEMPORARY TABLE planets (
+ name text,
+ distance text,
+ orbit integer
+);
+
+INSERT INTO planets VALUES
+ ('mercury', 'close', 88),
+ ('venus', 'close', 224),
+ ('earth', 'close', NULL),
+ ('mars', 'close', NULL),
+ ('jupiter', 'close', 4332),
+ ('saturn', 'far', 24491),
+ ('uranus', 'far', NULL),
+ ('neptune', 'far', 60182),
+ ('pluto', 'far', 90560),
+ ('xyzzy', 'far', NULL);
+
+-- test ruleutils
+CREATE VIEW planets_view AS
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+SELECT pg_get_viewdef('planets_view');
+
+-- lag
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
+-- lead
+SELECT name,
+ orbit,
+ lead(orbit) OVER w AS lead,
+ lead(orbit) RESPECT NULLS OVER w AS lead_respect,
+ lead(orbit) IGNORE NULLS OVER w AS lead_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
+-- first_value
+SELECT name,
+ orbit,
+ first_value(orbit) RESPECT NULLS OVER w1,
+ first_value(orbit) IGNORE NULLS OVER w1,
+ first_value(orbit) RESPECT NULLS OVER w2,
+ first_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- nth_value
+SELECT name,
+ orbit,
+ nth_value(orbit, 2) RESPECT NULLS OVER w1,
+ nth_value(orbit, 2) IGNORE NULLS OVER w1,
+ nth_value(orbit, 2) RESPECT NULLS OVER w2,
+ nth_value(orbit, 2) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- last_value
+SELECT name,
+ orbit,
+ last_value(orbit) RESPECT NULLS OVER w1,
+ last_value(orbit) IGNORE NULLS OVER w1,
+ last_value(orbit) RESPECT NULLS OVER w2,
+ last_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- exclude current row
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
+;
+
+-- valid and invalid functions
+SELECT sum(orbit) OVER () FROM planets; -- succeeds
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+SELECT row_number() OVER () FROM planets; -- succeeds
+SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
+SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails
+
+-- test two consecutive nulls
+update planets set orbit=null where name='jupiter';
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- test partitions
+SELECT name,
+ distance,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- nth_value without nulls
+SELECT x,
+ nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
+SELECT x,
+ nth_value(x,2) IGNORE NULLS OVER w
+FROM generate_series(1,5) g(x)
+WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+
+--cleanup
+DROP TABLE planets CASCADE;
--
2.25.1
view thread (91+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox