From 25a30bbd4235a49c854036c84fe90f2bc5a87652 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Fri, 3 Oct 2025 09:47:36 +0900 Subject: [PATCH] Add IGNORE NULLS/RESPECT NULLS option to Window functions. Add IGNORE NULLS/RESPECT NULLS option (null treatment clause) to lead, lag, first_value, last_value and nth_value window functions. If unspecified, the default is RESPECT NULLS which includes NULL values in any result calculation. IGNORE NULLS ignores NULL values. Built-in window functions are modified to call new API WinCheckAndInitializeNullTreatment() to indicate whether they accept IGNORE NULLS/RESPECT NULLS option or not (the API can be called by user defined window functions as well). If WinGetFuncArgInPartition's allowNullTreatment argument is true and IGNORE NULLS option is given, WinGetFuncArgInPartition() or WinGetFuncArgInFrame() will return evaluated function's argument expression on specified non NULL row (if it exists) in the partition or the frame. When IGNORE NULLS option is given, window functions need to visit and evaluate same rows over and over again to look for non null rows. To mitigate the issue, 2-bit not null information array is created while executing window functions to remember whether the row has been already evaluated to NULL or NOT NULL. If already evaluated, we could skip the evaluation work, thus we could get better performance. Author: Oliver Ford Co-authored-by: Tatsuo Ishii Reviewed-by: Krasiyan Andreev Reviewed-by: Andrew Gierth Reviewed-by: Tom Lane Reviewed-by: David Fetter Reviewed-by: Vik Fearing Reviewed-by: "David G. Johnston" Reviewed-by: Chao Li Discussion: https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com --- doc/src/sgml/func/func-window.sgml | 38 ++- doc/src/sgml/syntax.sgml | 10 +- src/backend/catalog/sql_features.txt | 2 +- src/backend/executor/nodeWindowAgg.c | 467 +++++++++++++++++++++++++-- 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 | 406 +++++++++++++++++++++++ src/test/regress/sql/window.sql | 162 ++++++++++ 15 files changed, 1092 insertions(+), 61 deletions(-) diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml index cce0165b952..bcf755c9ebc 100644 --- a/doc/src/sgml/func/func-window.sgml +++ b/doc/src/sgml/func/func-window.sgml @@ -140,7 +140,7 @@ lag ( value anycompatible , offset integer - , default anycompatible ) + , default anycompatible ) null treatment anycompatible @@ -165,7 +165,7 @@ lead ( value anycompatible , offset integer - , default anycompatible ) + , default anycompatible ) null treatment anycompatible @@ -188,7 +188,7 @@ first_value - first_value ( value anyelement ) + first_value ( value anyelement ) null treatment anyelement @@ -202,7 +202,7 @@ last_value - last_value ( value anyelement ) + last_value ( value anyelement ) null treatment anyelement @@ -216,7 +216,7 @@ nth_value - nth_value ( value anyelement, n integer ) + nth_value ( value anyelement, n integer ) null treatment anyelement @@ -265,18 +265,26 @@ Other frame specifications can be used to obtain other effects. + + The null treatment option must be one of: + + RESPECT NULLS + IGNORE NULLS + + If unspecified, the default is RESPECT NULLS which includes NULL + values in any result calculation. IGNORE NULLS ignores NULL values. + This option is only allowed for the following functions: lag, + lead, first_value, last_value, + nth_value. + + - The SQL standard defines a RESPECT NULLS or - IGNORE NULLS option for lead, lag, - first_value, last_value, and - nth_value. This is not implemented in - PostgreSQL: the behavior is always the - same as the standard's default, namely RESPECT NULLS. - Likewise, the standard's FROM FIRST or FROM LAST - option for nth_value is not implemented: only the - default FROM FIRST behavior is supported. (You can achieve - the result of FROM LAST by reversing the ORDER BY + The SQL standard defines a FROM FIRST or FROM LAST + option for nth_value. This is not implemented in + PostgreSQL: only the default FROM FIRST + behavior is supported. (You can achieve the result of FROM LAST by + reversing the ORDER BY ordering.) 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: -function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name -function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) +function_name (expression , expression ... ) null treatment [ FILTER ( WHERE filter_clause ) ] OVER window_name +function_name (expression , expression ... ) null treatment [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) @@ -1873,7 +1873,9 @@ EXCLUDE NO OTHERS Here, expression 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 null treatment clause, + described in . @@ -2048,7 +2050,7 @@ EXCLUDE NO OTHERS The built-in window functions are described in . 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..cf667c81211 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -69,6 +69,14 @@ 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 */ + + /* + * Null treatment options. One of: NO_NULLTREATMENT, PARSER_IGNORE_NULLS, + * PARSER_RESPECT_NULLS or IGNORE_NULLS. + */ + int ignore_nulls; } WindowObjectData; /* @@ -96,6 +104,7 @@ typedef struct WindowStatePerFuncData bool plain_agg; /* is it just a plain aggregate function? */ int aggno; /* if so, index of its WindowStatePerAggData */ + uint8 ignore_nulls; /* ignore nulls */ WindowObject winobj; /* object used in window function API */ } WindowStatePerFuncData; @@ -182,8 +191,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 +207,34 @@ 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 bits 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 +979,8 @@ 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 +1301,12 @@ begin_partition(WindowAggState *winstate) winobj->markpos = -1; winobj->seekpos = -1; + + /* reset null map */ + if (winobj->ignore_nulls == IGNORE_NULLS) + memset(winobj->notnull_info, 0, + NN_POS_TO_BYTES( + perfuncstate->winobj->num_notnull_info)); } } @@ -1412,8 +1456,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 +1467,10 @@ 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 +1499,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 +2669,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 +2732,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 +3269,294 @@ 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) +{ +/* initial number of notnull info members */ +#define INIT_NOT_NULL_INFO_NUM 128 + + 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,67 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno, break; } - gottuple = window_gettupleslot(winobj, abs_pos, slot); - - if (!gottuple) - { - if (isout) - *isout = true; - *isnull = true; - return (Datum) 0; - } - else + if (!null_treatment) /* IGNORE NULLS is not specified */ { - 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 +3869,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 +4021,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 f49bde7595b..81d768ff2a2 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -2578,6 +2578,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 f1def67ac7c..57bf7a7c7f2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -632,7 +632,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type window_clause window_definition_list opt_partition_clause %type window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound -%type opt_window_exclusion_clause +%type null_treatment opt_window_exclusion_clause %type opt_existing_window_name %type opt_if_not_exists %type opt_unique_null_treatment @@ -730,7 +730,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 @@ -765,7 +765,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 @@ -15805,7 +15805,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; @@ -15838,7 +15838,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 @@ -16434,6 +16435,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; } @@ -17861,6 +17868,7 @@ unreserved_keyword: | HOUR_P | IDENTITY_P | IF_P + | IGNORE_P | IMMEDIATE | IMMUTABLE | IMPLICIT_P @@ -17979,6 +17987,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 c43020a769d..778d69c6f3c 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -100,6 +100,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 : NO_NULLTREATMENT); CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL); bool could_be_projection; Oid rettype; @@ -518,6 +519,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) @@ -840,6 +848,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 c6d83d67b87..21663af6979 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11091,7 +11091,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 ac0e02a1db7..87c1086ec99 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -453,6 +453,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..a595fa28ce1 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -5453,3 +5453,409 @@ 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 +SELECT rank() OVER () FROM planets; -- succeeds + rank +------ + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +SELECT rank() RESPECT NULLS OVER () FROM planets; -- fails +ERROR: function rank does not allow RESPECT/IGNORE NULLS +SELECT rank() IGNORE NULLS OVER () FROM planets; -- fails +ERROR: function rank does not allow RESPECT/IGNORE NULLS +SELECT dense_rank() OVER () FROM planets; -- succeeds + dense_rank +------------ + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +SELECT dense_rank() RESPECT NULLS OVER () FROM planets; -- fails +ERROR: function dense_rank does not allow RESPECT/IGNORE NULLS +SELECT dense_rank() IGNORE NULLS OVER () FROM planets; -- fails +ERROR: function dense_rank does not allow RESPECT/IGNORE NULLS +SELECT percent_rank() OVER () FROM planets; -- succeeds + percent_rank +-------------- + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 +(10 rows) + +SELECT percent_rank() RESPECT NULLS OVER () FROM planets; -- fails +ERROR: function percent_rank does not allow RESPECT/IGNORE NULLS +SELECT percent_rank() IGNORE NULLS OVER () FROM planets; -- fails +ERROR: function percent_rank does not allow RESPECT/IGNORE NULLS +SELECT cume_dist() OVER () FROM planets; -- succeeds + cume_dist +----------- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +SELECT cume_dist() RESPECT NULLS OVER () FROM planets; -- fails +ERROR: function cume_dist does not allow RESPECT/IGNORE NULLS +SELECT cume_dist() IGNORE NULLS OVER () FROM planets; -- fails +ERROR: function cume_dist does not allow RESPECT/IGNORE NULLS +SELECT ntile(1) OVER () FROM planets; -- succeeds + ntile +------- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +SELECT ntile(1) RESPECT NULLS OVER () FROM planets; -- fails +ERROR: function ntile does not allow RESPECT/IGNORE NULLS +SELECT ntile(1) IGNORE NULLS OVER () FROM planets; -- fails +ERROR: function ntile 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..85fc621c8db 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1958,3 +1958,165 @@ $$ 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 +SELECT rank() OVER () FROM planets; -- succeeds +SELECT rank() RESPECT NULLS OVER () FROM planets; -- fails +SELECT rank() IGNORE NULLS OVER () FROM planets; -- fails +SELECT dense_rank() OVER () FROM planets; -- succeeds +SELECT dense_rank() RESPECT NULLS OVER () FROM planets; -- fails +SELECT dense_rank() IGNORE NULLS OVER () FROM planets; -- fails +SELECT percent_rank() OVER () FROM planets; -- succeeds +SELECT percent_rank() RESPECT NULLS OVER () FROM planets; -- fails +SELECT percent_rank() IGNORE NULLS OVER () FROM planets; -- fails +SELECT cume_dist() OVER () FROM planets; -- succeeds +SELECT cume_dist() RESPECT NULLS OVER () FROM planets; -- fails +SELECT cume_dist() IGNORE NULLS OVER () FROM planets; -- fails +SELECT ntile(1) OVER () FROM planets; -- succeeds +SELECT ntile(1) RESPECT NULLS OVER () FROM planets; -- fails +SELECT ntile(1) 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.47.3