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: Wed, 25 Jun 2025 16:19:03 +0900 (JST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
> BTW I am still not satisfied by the performance improvement for window
> functions for frames, that was only 40%. I will study the code to look
> for more optimization.
So I come up with more optimization for window functions working on
frames (i.e. first_value, last_value and nth_value). Attached v14
patch does it.
There are 3 major functions used here.
1) window_gettupleslot (get a row)
2) row_is_in_frame (check whether row is in frame or not)
3) ExecEvalExpr (evaluate arg on the row)
In v12 (and v13), we eliminate #3 in some cases but the saving was
only 40%. In v14, I found some cases where we don't need to call
#1. row_is_in_frame requires a row ("tuple" argument), which is
provided by #1. However row_is_in_frame actually uses the row argument
only when frame clause is "RANGE" or "GROUPS" and frame end is
"CURRENT ROW". In other cases it does not use "tuple" argument at
all. So I check the frame clause and the frame end, and if they are
not the case, I can omit #1. Plus if the not null cache for the row
has been already created, we can omit #3 as well. The optimization
contributes to the performance. I observe 2.7x (1k rows case) to 5.2x
(3k rows case) speed up when I compare the performance of v13 patch
and v14 patch using the same script (see attached).
v13:
rows duration (msec)
1000 34.740
2000 91.169
3000 205.847
4000 356.142
5000 557.063
v14:
rows duration (msec)
1000 12.807
2000 21.782
3000 39.248
4000 69.123
5000 101.220
I am not sure how the case where frame clause is "RANGE" or "GROUPS"
and frame end is "CURRENT ROW" is majority of window function use
cases. If it's majority, the optimization in v14 does not help much
because v14 does not optimize the case. However if it's not, the v14
patch is close to commitable form, I think. Comments are welcome.
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
for i in 1000 2000 3000 4000 5000
do
echo "$i rows: "
pos=`expr $i / 2`
psql -a test <<EOF
\timing
explain analyze
SELECT
x,
nth_value(x,$pos) IGNORE NULLS OVER w
FROM generate_series(1,$i) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
EOF
done | egrep "rows:|Time:" | egrep -v "Planning|Execution"|
sed -e 's/rows: *//' -e 's/Time: //' -e 's/ms//'
Attachments:
[application/octet-stream] v14-0001-Add-IGNORE-NULLS-RESPECT-NULLS-option-to-Window-.patch (53.8K, ../[email protected]/2-v14-0001-Add-IGNORE-NULLS-RESPECT-NULLS-option-to-Window-.patch)
download | inline diff:
From f47fee700e97f0f5f2537676671885f376619871 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <[email protected]>
Date: Wed, 25 Jun 2025 14:55:33 +0900
Subject: [PATCH v14] 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 | 437 +++++++++++++++++++++++++--
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, 962 insertions(+), 51 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 224d4fe5a9f..a95fdbab031 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..ba40cde550c 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;
@@ -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
@@ -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));
}
}
@@ -2619,14 +2655,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 +2718,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 +3255,308 @@ 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;
+ int frameOptions;
+
+ 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);
+ frameOptions = winstate->frameOptions;
+
+ 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;
+ bool gottuple = false;
+
+ /*
+ * 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;
+
+ /*
+ * row_is_in_frame requires slot if following frame options are set.
+ */
+ if (frameOptions & FRAMEOPTION_END_CURRENT_ROW &&
+ frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS))
+ {
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
+ gottuple = true;
+ }
+
+ /* check whether row is in frame */
+ inframe = row_is_in_frame(winstate, abs_pos, slot);
+ 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 (!gottuple)
+ {
+ 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 (!gottuple)
+ {
+ 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 +3715,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 +3757,57 @@ 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;
+ 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 +3859,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:
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 26a3e050086..9705c8ef2e8 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 50f53159d58..547b7f7e465 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
@@ -15738,7 +15738,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;
@@ -15771,7 +15771,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
@@ -16367,6 +16368,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; }
@@ -17793,6 +17800,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -17911,6 +17919,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 ba12678d1cb..8bedaf2f750 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
[text/plain] ignore_nulls_bench2.sh (407B, ../[email protected]/3-ignore_nulls_bench2.sh)
download | inline:
for i in 1000 2000 3000 4000 5000
do
echo "$i rows: "
pos=`expr $i / 2`
psql -a test <<EOF
\timing
explain analyze
SELECT
x,
nth_value(x,$pos) IGNORE NULLS OVER w
FROM generate_series(1,$i) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
EOF
done | egrep "rows:|Time:" | egrep -v "Planning|Execution"|
sed -e 's/rows: *//' -e 's/Time: //' -e 's/ms//'
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