Thank you very much for feedback and yes, that is very useful SQL syntax.
Maybe you miss my previous answer, but you are right, that patch is
currently dead,
because some important design questions must be discussed here, before
patch rewriting.
I have dropped support of from first/last for nth_value(),
but also I reimplemented it in a different way,
by using negative number for the position argument, to be able to get the
same frame in exact reverse order.
After that patch becomes much more simple and some concerns about
precedence hack has gone.
I have not renamed special bool type "ignorenulls"
(I know that it is not acceptable way for calling extra version
of window functions, but also it makes things very easy and it can reuse
frames),
but I removed the other special bool type "fromlast".
Attached file was for PostgreSQL 13 (master git branch, last commit fest),
everything was working and patch was at the time in very good shape, all
tests was passed.
I read previous review and suggestions from Tom about special bool type and
unreserved keywords and also,
that IGNORE NULLS could be implemented inside the WinGetFuncArgXXX
functions,
but I am not sure how exactly to proceed (some example will be very
helpful).
На чт, 30.04.2020 г. в 21:58 Stephen Frost <sfr...@snowman.net> написа:
> Greetings,
>
> This seems to have died out, and that's pretty unfortunate because this
> is awfully useful SQL standard syntax that people look for and wish we
> had.
>
> * Andrew Gierth (and...@tao11.riddles.org.uk) wrote:
> > So I've tried to rough out a decision tree for the various options on
> > how this might be implemented (discarding the "use precedence hacks"
> > option). Opinions? Additions?
> >
> > (formatted for emacs outline-mode)
> >
> > * 1. use lexical lookahead
> >
> > +: relatively straightforward parser changes
> > +: no new reserved words
> > +: has the option of working extensibly with all functions
> >
> > -: base_yylex needs extending to 3 lookahead tokens
>
> This sounds awful grotty and challenging to do and get right, and the
> alternative (just reserving these, as the spec does) doesn't seem so
> draconian as to be that much of an issue.
>
> > * 2. reserve nth_value etc. as functions
> >
> > +: follows the spec reasonably well
> > +: less of a hack than extending base_yylex
> >
> > -: new reserved words
> > -: more parser rules
> > -: not extensible
> >
>
> For my 2c, at least, reserving these strikes me as entirely reasonable.
> Yes, it sucks that we have to partially-reserve some additional
> keywords, but such is life. I get that we'll throw syntax errors
> sometimes when we might have given a better error, but I think we can
> accept that.
>
> > (now goto 1.2.1)
>
> Hmm, not sure this was right? but sure, I'll try...
>
> > *** 1.2.1. Check the function name in parse analysis against a fixed
> list.
> >
> > +: simple
> > -: not extensible
>
> Seems like this is more-or-less required since we'd be reserving them..?
>
> > *** 1.2.2. Provide some option in CREATE FUNCTION
> >
> > +: extensible
> > -: fairly intrusive, adding stuff to create function and pg_proc
>
> How would this work though, if we reserve the functions as keywords..?
> Maybe I'm not entirely following, but wouldn't attempts to use other
> functions end up with syntax errors in at least some of the cases,
> meaning that having other functions support this wouldn't really work?
> I don't particularly like the idea that some built-in functions would
> always work but others would work but only some of the time.
>
> > *** 1.2.3. Do something magical with function argument types
> >
> > +: doesn't need changes in create function / pg_proc
> > -: it's an ugly hack
>
> Not really a fan of 'ugly hack'.
>
> > * 3. "just say no" to the spec
> >
> > e.g. add new functions like lead_ignore_nulls(), or add extra boolean
> > args to lead() etc. telling them to skip nulls
> >
> > +: simple
> > -: doesn't conform to spec
> > -: using extra args isn't quite the right semantics
>
> Ugh, no thank you.
>
> Thanks!
>
> Stephen
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28035f1635..3d73c96891 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15702,7 +15702,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<function>
lag(<replaceable class="parameter">value</replaceable> <type>anyelement</type>
[, <replaceable class="parameter">offset</replaceable> <type>integer</type>
- [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]])
+ [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]]) [null_treatment]
</function>
</entry>
<entry>
@@ -15731,7 +15731,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<function>
lead(<replaceable class="parameter">value</replaceable> <type>anyelement</type>
[, <replaceable class="parameter">offset</replaceable> <type>integer</type>
- [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]])
+ [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]]) [null_treatment]
</function>
</entry>
<entry>
@@ -15757,7 +15757,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<indexterm>
<primary>first_value</primary>
</indexterm>
- <function>first_value(<replaceable class="parameter">value</replaceable> <type>any</type>)</function>
+ <function>first_value(<replaceable class="parameter">value</replaceable> <type>any</type>) [null_treatment]</function>
</entry>
<entry>
<type>same type as <replaceable class="parameter">value</replaceable></type>
@@ -15773,7 +15773,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<indexterm>
<primary>last_value</primary>
</indexterm>
- <function>last_value(<replaceable class="parameter">value</replaceable> <type>any</type>)</function>
+ <function>last_value(<replaceable class="parameter">value</replaceable> <type>any</type>) [null_treatment]</function>
</entry>
<entry>
<type>same type as <replaceable class="parameter">value</replaceable></type>
@@ -15790,7 +15790,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<primary>nth_value</primary>
</indexterm>
<function>
- nth_value(<replaceable class="parameter">value</replaceable> <type>any</type>, <replaceable class="parameter">nth</replaceable> <type>integer</type>)
+ nth_value(<replaceable class="parameter">value</replaceable> <type>any</type>, <replaceable class="parameter">nth</replaceable> <type>integer</type>) [null_treatment]
</function>
</entry>
<entry>
@@ -15806,6 +15806,16 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
</tgroup>
</table>
+ <para>
+ In <xref linkend="functions-window-table"/>, <replaceable>null_treatment</replaceable> is one of:
+ <synopsis>
+ RESPECT NULLS
+ IGNORE NULLS
+ </synopsis>
+ <literal>RESPECT NULLS</literal> specifies the default behavior to include nulls in the result.
+ <literal>IGNORE NULLS</literal> ignores any null values when determining a result.
+ </para>
+
<para>
All of the functions listed in
<xref linkend="functions-window-table"/> depend on the sort ordering
@@ -15843,17 +15853,11 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<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>
- ordering.)
+ 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 using negative number for the position argument,
+ as is done in many languages to indicate a <literal>FROM END</literal> index.)
</para>
</note>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 9f840ddfd2..a355e379e7 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -508,9 +508,9 @@ T612 Advanced OLAP operations YES
T613 Sampling YES
T614 NTILE function YES
T615 LEAD and LAG functions YES
-T616 Null treatment option for LEAD and LAG functions NO
+T616 Null treatment option for LEAD and LAG functions YES
T617 FIRST_VALUE and LAST_VALUE function YES
-T618 NTH_VALUE function NO function exists, but some options missing
+T618 NTH_VALUE function YES FROM LAST is supported by using negative number for the position argument
T619 Nested window functions NO
T620 WINDOW clause: GROUPS option YES
T621 Enhanced numeric functions YES
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 96e7fdbcfe..db369b1f9a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -154,6 +154,7 @@ static Node *makeBitStringConst(char *str, int location);
static Node *makeNullAConst(int location);
static Node *makeAConst(Value *v, int location);
static Node *makeBoolAConst(bool state, int location);
+static Node *makeTypedBoolAConst(bool state, char *type, int location);
static RoleSpec *makeRoleSpec(RoleSpecType type, int location);
static void check_qualified_name(List *names, core_yyscan_t yyscanner);
static List *check_func_name(List *names, core_yyscan_t yyscanner);
@@ -569,7 +570,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> xml_namespace_list
%type <target> xml_namespace_el
-%type <node> func_application func_expr_common_subexpr
+%type <node> func_application func_expr_common_subexpr func_expr_respect_ignore
%type <node> func_expr func_expr_windowless
%type <node> common_table_expr
%type <with> with_clause opt_with_clause
@@ -577,6 +578,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> within_group_clause
%type <node> filter_clause
+%type <ival> null_treatment_clause
%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
@@ -642,14 +644,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
EXTENSION EXTERNAL EXTRACT
- FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR
+ FALSE_P FAMILY FETCH FILTER FIRST_P FIRST_VALUE FLOAT_P FOLLOWING FOR
FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS
GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING GROUPS
HANDLER HAVING HEADER_P HOLD HOUR_P
- IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
+ IDENTITY_P IF_P IGNORE_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -658,14 +660,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
KEY
- LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
- LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
+ LABEL LAG LANGUAGE LARGE_P LAST_P LAST_VALUE LATERAL_P
+ LEAD LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NONE
- NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
+ NOT NOTHING NOTIFY NOTNULL NOWAIT NTH_VALUE NULL_P NULLIF
NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
@@ -680,7 +682,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+ RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
@@ -724,6 +726,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
/* Precedence: lowest to highest */
%nonassoc SET /* see relation_expr_opt_alias */
+%nonassoc FIRST_VALUE LAG LAST_VALUE LEAD NTH_VALUE
%left UNION EXCEPT
%left INTERSECT
%left OR
@@ -13769,6 +13772,10 @@ func_application: func_name '(' ')'
}
;
+null_treatment_clause:
+ RESPECT NULLS_P { $$ = 0; }
+ | IGNORE_P NULLS_P { $$ = WINFUNC_OPT_IGNORE_NULLS; }
+ ;
/*
* func_expr and its cousin func_expr_windowless are split out from c_expr just
@@ -13816,8 +13823,133 @@ func_expr: func_application within_group_clause filter_clause over_clause
}
| func_expr_common_subexpr
{ $$ = $1; }
+ | func_expr_respect_ignore over_clause
+ {
+ FuncCall *n = (FuncCall *) $1;
+ n->over = $2;
+ $$ = (Node *) n;
+ }
+ ;
+
+func_expr_respect_ignore:
+ FIRST_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+ {
+ FuncCall *n;
+ List *l = $3;
+ int winFuncArgs = $6;
+
+ /* Convert Ignore Nulls option to bool */
+ if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+ l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+ n = makeFuncCall(list_make1(makeString("first_value")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | FIRST_VALUE '(' func_arg_list opt_sort_clause ')'
+ {
+ FuncCall *n;
+ List *l = $3;
+
+ n = makeFuncCall(list_make1(makeString("first_value")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | LAG '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+ {
+ FuncCall *n;
+ List *l = $3;
+ int winFuncArgs = $6;
+
+ /* Convert Ignore Nulls option to bool */
+ if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+ l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+ n = makeFuncCall(list_make1(makeString("lag")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | LAG '(' func_arg_list opt_sort_clause ')'
+ {
+ FuncCall *n;
+ List *l = $3;
+
+ n = makeFuncCall(list_make1(makeString("lag")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | LAST_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+ {
+ FuncCall *n;
+ List *l = $3;
+ int winFuncArgs = $6;
+
+ /* Convert Ignore Nulls option to bool */
+ if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+ l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+ n = makeFuncCall(list_make1(makeString("last_value")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | LAST_VALUE '(' func_arg_list opt_sort_clause ')'
+ {
+ FuncCall *n;
+ List *l = $3;
+
+ n = makeFuncCall(list_make1(makeString("last_value")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | LEAD '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+ {
+ FuncCall *n;
+ List *l = $3;
+ int winFuncArgs = $6;
+
+ /* Convert Ignore Nulls option to bool */
+ if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+ l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+ n = makeFuncCall(list_make1(makeString("lead")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | LEAD '(' func_arg_list opt_sort_clause ')'
+ {
+ FuncCall *n;
+ List *l = $3;
+
+ n = makeFuncCall(list_make1(makeString("lead")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | NTH_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+ {
+ FuncCall *n;
+ List *l = $3;
+ int winFuncArgs = $6;
+
+ /* Convert Nulls option to bool */
+ if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+ l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+ n = makeFuncCall(list_make1(makeString("nth_value")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
+ | NTH_VALUE '(' func_arg_list opt_sort_clause ')'
+ {
+ FuncCall *n;
+ List *l = $3;
+
+ n = makeFuncCall(list_make1(makeString("nth_value")), l, @1);
+ n->agg_order = $4;
+ $$ = (Node *) n;
+ }
;
+
/*
* As func_expr but does not accept WINDOW functions directly
* (but they can still be contained in arguments for functions etc).
@@ -15225,6 +15357,7 @@ unreserved_keyword:
| FAMILY
| FILTER
| FIRST_P
+ | FIRST_VALUE
| FOLLOWING
| FORCE
| FORWARD
@@ -15240,6 +15373,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -15260,9 +15394,12 @@ unreserved_keyword:
| ISOLATION
| KEY
| LABEL
+ | LAG
| LANGUAGE
| LARGE_P
| LAST_P
+ | LAST_VALUE
+ | LEAD
| LEAKPROOF
| LEVEL
| LISTEN
@@ -15290,6 +15427,7 @@ unreserved_keyword:
| NOTHING
| NOTIFY
| NOWAIT
+ | NTH_VALUE
| NULLS_P
| OBJECT_P
| OF
@@ -15341,6 +15479,7 @@ unreserved_keyword:
| REPLACE
| REPLICA
| RESET
+ | RESPECT
| RESTART
| RESTRICT
| RETURNS
@@ -15815,6 +15954,15 @@ makeAConst(Value *v, int location)
*/
static Node *
makeBoolAConst(bool state, int location)
+{
+ return makeTypedBoolAConst(state, "bool", location);
+}
+
+/* makeTypedBoolAConst()
+ * Create an A_Const string node from a boolean and store inside the specified type.
+ */
+static Node *
+makeTypedBoolAConst(bool state, char *type, int location)
{
A_Const *n = makeNode(A_Const);
@@ -15822,7 +15970,7 @@ makeBoolAConst(bool state, int location)
n->val.val.str = (state ? "t" : "f");
n->location = location;
- return makeTypeCast((Node *)n, SystemTypeName("bool"), -1);
+ return makeTypeCast((Node *)n, SystemTypeName(type), -1);
}
/* makeRoleSpec
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 158784474d..ae7e821ab2 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9437,6 +9437,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
int nargs;
List *argnames;
ListCell *l;
+ bool ignorenulls = false;
if (list_length(wfunc->args) > FUNC_MAX_ARGS)
ereport(ERROR,
@@ -9463,7 +9464,32 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
if (wfunc->winstar)
appendStringInfoChar(buf, '*');
else
- get_rule_expr((Node *) wfunc->args, context, true);
+ {
+ ListCell *arglist;
+ Node *argnode = (Node *) wfunc->args;
+
+ get_rule_expr(argnode, context, true);
+
+ /* Determine if IGNORE NULLS should be appended */
+ foreach(arglist, (List *) argnode)
+ {
+ Node *arg = (Node *) lfirst(arglist);
+ if (nodeTag(arg) == T_Const)
+ {
+ Const *constnode = (Const *) arg;
+ if (constnode->consttype == IGNORENULLSOID)
+ {
+ /* parser does not save RESPECT NULLS arguments */
+ ignorenulls = true;
+ buf->len -= 2;
+ }
+ }
+ }
+ }
+
+ appendStringInfoChar(buf, ')');
+ if (ignorenulls)
+ appendStringInfoString(buf, " IGNORE NULLS");
if (wfunc->aggfilter != NULL)
{
@@ -9471,7 +9497,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
- appendStringInfoString(buf, ") OVER ");
+ appendStringInfoString(buf, " OVER ");
foreach(l, context->windowClause)
{
@@ -9649,6 +9675,10 @@ get_const_expr(Const *constval, deparse_context *context, int showtype)
appendStringInfoString(buf, "false");
break;
+ case IGNORENULLSOID:
+ showtype = -1;
+ break;
+
default:
simple_quote_literal(buf, extval);
break;
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index f0c8ae686d..bc639b1883 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -38,7 +38,12 @@ typedef struct
static bool rank_up(WindowObject winobj);
static Datum leadlag_common(FunctionCallInfo fcinfo,
bool forward, bool withoffset, bool withdefault);
-
+static Datum leadlag_common_ignore_nulls(FunctionCallInfo fcinfo,
+ bool forward, bool withoffset, bool withdefault);
+static Datum
+window_nth_value_ignorenulls_common(FunctionCallInfo fcinfo, int32 nth);
+static Datum
+window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth);
/*
* utility routine for *_rank functions.
@@ -328,6 +333,79 @@ leadlag_common(FunctionCallInfo fcinfo,
PG_RETURN_DATUM(result);
}
+static Datum
+leadlag_common_ignore_nulls(FunctionCallInfo fcinfo,
+ bool forward, bool withoffset, bool withdefault)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ int32 offset;
+ Datum result;
+ bool isnull;
+ bool isout = false;
+ int32 notnull_offset = 0, tmp_offset = 0;
+
+ if (withoffset)
+ {
+ offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+ if (offset < 0)
+ {
+ offset = abs(offset);
+ forward = !forward;
+ } else if (offset == 0)
+ {
+ result = WinGetFuncArgInPartition(winobj, 0, 0,
+ WINDOW_SEEK_CURRENT,
+ false,
+ &isnull, &isout);
+ if (isnull || isout)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_DATUM(result);
+ }
+ }
+ else
+ offset = 1;
+
+ while (notnull_offset < offset)
+ {
+ tmp_offset++;
+ result = WinGetFuncArgInPartition(winobj, 0,
+ (forward ? tmp_offset : -tmp_offset),
+ WINDOW_SEEK_CURRENT,
+ false,
+ &isnull, &isout);
+ if (isout)
+ goto out_of_frame;
+ else if (!isnull)
+ notnull_offset++;
+ }
+
+ result = WinGetFuncArgInPartition(winobj, 0,
+ (forward ? tmp_offset : -tmp_offset),
+ WINDOW_SEEK_CURRENT,
+ false,
+ &isnull, &isout);
+ if (isout)
+ goto out_of_frame;
+ else
+ PG_RETURN_DATUM(result);
+
+ out_of_frame:
+ /*
+ * target row is out of the partition; supply default value if
+ * provided. Otherwise return NULL.
+ */
+ if (withdefault)
+ {
+ result = WinGetFuncArgCurrent(winobj, 2, &isnull);
+ PG_RETURN_DATUM(result);
+ }
+ else
+ PG_RETURN_NULL();
+}
+
/*
* lag
* returns the value of VE evaluated on a row that is 1
@@ -363,6 +441,24 @@ window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
return leadlag_common(fcinfo, false, true, true);
}
+Datum
+window_lag_nulls_opt(PG_FUNCTION_ARGS)
+{
+ return leadlag_common_ignore_nulls(fcinfo, false, false, false);
+}
+
+Datum
+window_lag_with_offset_nulls_opt(PG_FUNCTION_ARGS)
+{
+ return leadlag_common_ignore_nulls(fcinfo, false, true, false);
+}
+
+Datum
+window_lag_with_offset_and_default_nulls_opt(PG_FUNCTION_ARGS)
+{
+ return leadlag_common_ignore_nulls(fcinfo, false, true, true);
+}
+
/*
* lead
* returns the value of VE evaluated on a row that is 1
@@ -398,6 +494,24 @@ window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
return leadlag_common(fcinfo, true, true, true);
}
+Datum
+window_lead_nulls_opt(PG_FUNCTION_ARGS)
+{
+ return leadlag_common_ignore_nulls(fcinfo, true, false, false);
+}
+
+Datum
+window_lead_with_offset_nulls_opt(PG_FUNCTION_ARGS)
+{
+ return leadlag_common_ignore_nulls(fcinfo, true, true, false);
+}
+
+Datum
+window_lead_with_offset_and_default_nulls_opt(PG_FUNCTION_ARGS)
+{
+ return leadlag_common_ignore_nulls(fcinfo, true, true, true);
+}
+
/*
* first_value
* return the value of VE evaluated on the first row of the
@@ -419,6 +533,31 @@ window_first_value(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+Datum
+window_first_value_nulls_opt(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ Datum result;
+ bool isnull,
+ isout;
+ int64 pos;
+
+ isout = false;
+ pos = 0;
+
+ while (!isout)
+ {
+ result = WinGetFuncArgInFrame(winobj, 0,
+ pos, WINDOW_SEEK_HEAD, false,
+ &isnull, &isout);
+ if (!isnull)
+ PG_RETURN_DATUM(result);
+ pos++;
+ }
+
+ PG_RETURN_NULL();
+}
+
/*
* last_value
* return the value of VE evaluated on the last row of the
@@ -440,35 +579,149 @@ window_last_value(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+Datum
+window_last_value_nulls_opt(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ Datum result;
+ bool isnull,
+ isout;
+ int64 pos;
+
+ isout = false;
+ pos = 0;
+
+ while (!isout)
+ {
+ result = WinGetFuncArgInFrame(winobj, 0,
+ pos, WINDOW_SEEK_TAIL, false,
+ &isnull, &isout);
+ if (!isnull)
+ PG_RETURN_DATUM(result);
+ pos--;
+ }
+
+ PG_RETURN_NULL();
+}
+
/*
* nth_value
* return the value of VE evaluated on the n-th row from the first
* row of the window frame, per spec.
*/
-Datum
-window_nth_value(PG_FUNCTION_ARGS)
+static Datum
+window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth)
{
WindowObject winobj = PG_WINDOW_OBJECT();
bool const_offset;
Datum result;
bool isnull;
- int32 nth;
+ bool fromlast;
nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
if (isnull)
PG_RETURN_NULL();
const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
- if (nth <= 0)
+ if (nth == 0)
ereport(ERROR,
- (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
- errmsg("argument of nth_value must be greater than zero")));
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+ errmsg("argument of nth_value must be greater or less than zero")));
+ else if (nth < 0)
+ {
+ nth = abs(nth);
+ fromlast = true;
+ }
+ else
+ fromlast = false;
+
+ result = WinGetFuncArgInFrame(winobj,
+ 0,
+ nth - 1,
+ fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD, const_offset,
+ &isnull,
+ NULL);
- result = WinGetFuncArgInFrame(winobj, 0,
- nth - 1, WINDOW_SEEK_HEAD, const_offset,
- &isnull, NULL);
if (isnull)
PG_RETURN_NULL();
PG_RETURN_DATUM(result);
}
+
+static Datum
+window_nth_value_ignorenulls_common(FunctionCallInfo fcinfo, int32 nth)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ Datum result;
+ bool isnull,
+ isout;
+ bool fromlast;
+ int32 tmp_offset, notnull_offset = 0;
+
+ nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+
+ if (nth == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+ errmsg("argument of nth_value must be greater or less than zero")));
+ else if (nth < 0)
+ {
+ nth = abs(nth);
+ fromlast = true;
+ tmp_offset = 1;
+ }
+ else
+ {
+ fromlast = false;
+ tmp_offset = -1;
+ }
+
+ while (notnull_offset < nth)
+ {
+ fromlast ? tmp_offset-- : tmp_offset++;
+ result = WinGetFuncArgInFrame(winobj, 0,
+ tmp_offset, fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD,
+ false, &isnull, &isout);
+ if (isout)
+ PG_RETURN_NULL();
+ if (!isnull)
+ notnull_offset++;
+ }
+
+ result = WinGetFuncArgInFrame(winobj, 0,
+ tmp_offset, fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD,
+ false, &isnull, &isout);
+
+ if (isout || isnull)
+ PG_RETURN_NULL();
+
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+window_nth_value(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ bool isnull;
+ int32 nth;
+
+ nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(window_nth_value_respectnulls_common(fcinfo, nth));
+}
+
+Datum
+window_nth_value_with_nulls_opt(PG_FUNCTION_ARGS)
+{
+ WindowObject winobj = PG_WINDOW_OBJECT();
+ bool isnull;
+ int32 nth;
+
+ nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+ if (isnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(window_nth_value_ignorenulls_common(fcinfo, nth));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..f07a9e442d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9539,32 +9539,64 @@
{ oid => '3106', descr => 'fetch the preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_lag' },
+{ oid => '4191', descr => 'fetch the preceding row value with nulls option',
+ proname => 'lag', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement ignorenulls', prosrc => 'window_lag_nulls_opt' },
{ oid => '3107', descr => 'fetch the Nth preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset' },
+{ oid => '4192', descr => 'fetch the Nth preceding row value with nulls option',
+ proname => 'lag', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4 ignorenulls',
+ prosrc => 'window_lag_with_offset_nulls_opt' },
{ oid => '3108', descr => 'fetch the Nth preceding row value with default',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4 anyelement',
prosrc => 'window_lag_with_offset_and_default' },
+{ oid => '4193', descr => 'fetch the Nth preceding row value with default and nulls option',
+ proname => 'lag', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4 anyelement ignorenulls',
+ prosrc => 'window_lag_with_offset_and_default_nulls_opt' },
{ oid => '3109', descr => 'fetch the following row value',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_lead' },
+{ oid => '4194', descr => 'fetch the following row value with nulls option',
+ proname => 'lead', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement ignorenulls', prosrc => 'window_lead_nulls_opt' },
{ oid => '3110', descr => 'fetch the Nth following row value',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset' },
+{ oid => '4195', descr => 'fetch the Nth following row value with nulls option',
+ proname => 'lead', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4 ignorenulls',
+ prosrc => 'window_lead_with_offset_nulls_opt' },
{ oid => '3111', descr => 'fetch the Nth following row value with default',
proname => 'lead', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4 anyelement',
prosrc => 'window_lead_with_offset_and_default' },
+{ oid => '4196', descr => 'fetch the Nth following row value with default and nulls option',
+ proname => 'lead', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4 anyelement ignorenulls',
+ prosrc => 'window_lead_with_offset_and_default_nulls_opt' },
{ oid => '3112', descr => 'fetch the first row value',
proname => 'first_value', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_first_value' },
+{ oid => '4197', descr => 'fetch the first row value with nulls option',
+ proname => 'first_value', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement ignorenulls', prosrc => 'window_first_value_nulls_opt' },
{ oid => '3113', descr => 'fetch the last row value',
proname => 'last_value', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_last_value' },
+{ oid => '4198', descr => 'fetch the last row value with nulls option',
+ proname => 'last_value', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement ignorenulls', prosrc => 'window_last_value_nulls_opt' },
{ oid => '3114', descr => 'fetch the Nth row value',
proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement int4', prosrc => 'window_nth_value' },
+{ oid => '4199', descr => 'fetch the Nth row value with nulls option',
+ proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement int4 ignorenulls',
+ prosrc => 'window_nth_value_with_nulls_opt' },
# functions for range types
{ oid => '3832', descr => 'I/O',
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 4cf2b9df7b..3c847e6db5 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -594,5 +594,10 @@
typname => 'anyrange', typlen => '-1', typbyval => 'f', typtype => 'p',
typcategory => 'P', typinput => 'anyrange_in', typoutput => 'anyrange_out',
typreceive => '-', typsend => '-', typalign => 'd', typstorage => 'x' },
+{ oid => '4142',
+ descr => 'boolean wrapper, \'true\'/\'false\'',
+ typname => 'ignorenulls', typlen => '1', typbyval => 't', typtype => 'b',
+ typcategory => 'B', typinput => 'boolin', typoutput => 'boolout',
+ typreceive => 'boolrecv', typsend => 'boolsend', typalign => 'c' },
]
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index da0706add5..0899bddd6e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -532,6 +532,11 @@ typedef struct WindowDef
(FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \
FRAMEOPTION_END_CURRENT_ROW)
+/*
+ * Null Treatment option
+ */
+#define WINFUNC_OPT_IGNORE_NULLS 0x00001 /* IGNORE NULLS */
+
/*
* RangeSubselect - subquery appearing in a FROM clause
*/
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index b1184c2d15..a33ce1d0fd 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -164,6 +164,7 @@ PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD)
PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD)
PG_KEYWORD("filter", FILTER, UNRESERVED_KEYWORD)
PG_KEYWORD("first", FIRST_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("first_value", FIRST_VALUE, UNRESERVED_KEYWORD)
PG_KEYWORD("float", FLOAT_P, COL_NAME_KEYWORD)
PG_KEYWORD("following", FOLLOWING, UNRESERVED_KEYWORD)
PG_KEYWORD("for", FOR, RESERVED_KEYWORD)
@@ -190,6 +191,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD)
PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD)
PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD)
PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
@@ -223,10 +225,13 @@ PG_KEYWORD("isolation", ISOLATION, UNRESERVED_KEYWORD)
PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD)
PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD)
+PG_KEYWORD("lag", LAG, UNRESERVED_KEYWORD)
PG_KEYWORD("language", LANGUAGE, UNRESERVED_KEYWORD)
PG_KEYWORD("large", LARGE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("last", LAST_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("last_value", LAST_VALUE, UNRESERVED_KEYWORD)
PG_KEYWORD("lateral", LATERAL_P, RESERVED_KEYWORD)
+PG_KEYWORD("lead", LEAD, UNRESERVED_KEYWORD)
PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD)
PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD)
PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD)
@@ -267,6 +272,7 @@ PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD)
PG_KEYWORD("notify", NOTIFY, UNRESERVED_KEYWORD)
PG_KEYWORD("notnull", NOTNULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("nowait", NOWAIT, UNRESERVED_KEYWORD)
+PG_KEYWORD("nth_value", NTH_VALUE, UNRESERVED_KEYWORD)
PG_KEYWORD("null", NULL_P, RESERVED_KEYWORD)
PG_KEYWORD("nullif", NULLIF, COL_NAME_KEYWORD)
PG_KEYWORD("nulls", NULLS_P, UNRESERVED_KEYWORD)
@@ -336,6 +342,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
+PG_KEYWORD("respect", RESPECT, UNRESERVED_KEYWORD)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out
index cd7fc03b04..0ac49263ec 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -73,7 +73,8 @@ WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%'
3361 | pg_ndistinct
3402 | pg_dependencies
5017 | pg_mcv_list
-(4 rows)
+ 4142 | ignorenulls
+(5 rows)
-- Make sure typarray points to a varlena array type of our own base
SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
@@ -166,10 +167,11 @@ WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
(p1.typelem != 0 AND p1.typlen < 0) AND NOT
(p2.prorettype = p1.oid AND NOT p2.proretset)
ORDER BY 1;
- oid | typname | oid | proname
-------+-----------+-----+---------
- 1790 | refcursor | 46 | textin
-(1 row)
+ oid | typname | oid | proname
+------+-------------+------+---------
+ 1790 | refcursor | 46 | textin
+ 4142 | ignorenulls | 1242 | boolin
+(2 rows)
-- Varlena array types will point to array_in
-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
@@ -217,10 +219,11 @@ WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
(p2.oid = 'array_out'::regproc AND
p1.typelem != 0 AND p1.typlen = -1)))
ORDER BY 1;
- oid | typname | oid | proname
-------+-----------+-----+---------
- 1790 | refcursor | 47 | textout
-(1 row)
+ oid | typname | oid | proname
+------+-------------+------+---------
+ 1790 | refcursor | 47 | textout
+ 4142 | ignorenulls | 1243 | boolout
+(2 rows)
SELECT p1.oid, p1.typname, p2.oid, p2.proname
FROM pg_type AS p1, pg_proc AS p2
@@ -280,10 +283,11 @@ WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
(p1.typelem != 0 AND p1.typlen < 0) AND NOT
(p2.prorettype = p1.oid AND NOT p2.proretset)
ORDER BY 1;
- oid | typname | oid | proname
-------+-----------+------+----------
- 1790 | refcursor | 2414 | textrecv
-(1 row)
+ oid | typname | oid | proname
+------+-------------+------+----------
+ 1790 | refcursor | 2414 | textrecv
+ 4142 | ignorenulls | 2436 | boolrecv
+(2 rows)
-- Varlena array types will point to array_recv
-- Exception as of 8.1: int2vector and oidvector have their own I/O routines
@@ -340,10 +344,11 @@ WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
(p2.oid = 'array_send'::regproc AND
p1.typelem != 0 AND p1.typlen = -1)))
ORDER BY 1;
- oid | typname | oid | proname
-------+-----------+------+----------
- 1790 | refcursor | 2415 | textsend
-(1 row)
+ oid | typname | oid | proname
+------+-------------+------+----------
+ 1790 | refcursor | 2415 | textsend
+ 4142 | ignorenulls | 2437 | boolsend
+(2 rows)
SELECT p1.oid, p1.typname, p2.oid, p2.proname
FROM pg_type AS p1, pg_proc AS p2
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d5fd4045f9..54ded65906 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2985,7 +2985,7 @@ LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
ERROR: argument of ntile must be greater than zero
SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
-ERROR: argument of nth_value must be greater than zero
+ERROR: argument of nth_value must be greater or less than zero
-- filter
SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
@@ -3863,3 +3863,369 @@ SELECT * FROM pg_temp.f(2);
{5}
(5 rows)
+-- RESPECT NULLS and IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+ name text,
+ orbit int
+);
+INSERT INTO planets VALUES
+ ('mercury', 88),
+ ('venus', 224),
+ ('earth', NULL),
+ ('mars', NULL),
+ ('jupiter', 4332),
+ ('saturn', 24491),
+ ('uranus', NULL),
+ ('neptune', 60182),
+ ('pluto', 90560);
+ -- test view definitions are preserved
+CREATE TEMP VIEW v_planets AS
+ SELECT
+ name,
+ sum(orbit) OVER (order by orbit) as sum_rows,
+ lag(orbit, 1) RESPECT NULLS OVER (ORDER BY name DESC) AS lagged_by_1,
+ lag(orbit, 2) IGNORE NULLS OVER w AS lagged_by_2,
+ first_value(orbit) IGNORE NULLS OVER w AS first_value_ignore,
+ nth_value(orbit,2) IGNORE NULLS OVER w AS nth_first_ignore,
+ nth_value(orbit,-2) IGNORE NULLS OVER w AS nth_last_ignore
+ FROM planets
+ WINDOW w as (ORDER BY name ASC);
+SELECT pg_get_viewdef('v_planets');
+ pg_get_viewdef
+------------------------------------------------------------------------------------
+ SELECT planets.name, +
+ sum(planets.orbit) OVER (ORDER BY planets.orbit) AS sum_rows, +
+ lag(planets.orbit, 1) OVER (ORDER BY planets.name DESC) AS lagged_by_1, +
+ lag(planets.orbit, 2) IGNORE NULLS OVER w AS lagged_by_2, +
+ first_value(planets.orbit) IGNORE NULLS OVER w AS first_value_ignore, +
+ nth_value(planets.orbit, 2) IGNORE NULLS OVER w AS nth_first_ignore, +
+ nth_value(planets.orbit, '-2'::integer) IGNORE NULLS OVER w AS nth_last_ignore+
+ FROM planets +
+ WINDOW w AS (ORDER BY planets.name);
+(1 row)
+
+SELECT name, lag(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lag
+---------+-------
+ earth |
+ jupiter |
+ mars | 4332
+ mercury |
+ neptune | 88
+ pluto | 60182
+ saturn | 90560
+ uranus | 24491
+ venus |
+(9 rows)
+
+SELECT name, lag(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lag
+---------+-------
+ earth |
+ jupiter |
+ mars | 4332
+ mercury |
+ neptune | 88
+ pluto | 60182
+ saturn | 90560
+ uranus | 24491
+ venus |
+(9 rows)
+
+SELECT name, lag(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lag
+---------+-------
+ earth |
+ jupiter |
+ mars | 4332
+ mercury | 4332
+ neptune | 88
+ pluto | 60182
+ saturn | 90560
+ uranus | 24491
+ venus | 24491
+(9 rows)
+
+SELECT name, lead(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lead
+---------+-------
+ earth | 4332
+ jupiter |
+ mars | 88
+ mercury | 60182
+ neptune | 90560
+ pluto | 24491
+ saturn |
+ uranus | 224
+ venus |
+(9 rows)
+
+SELECT name, lead(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lead
+---------+-------
+ earth | 4332
+ jupiter |
+ mars | 88
+ mercury | 60182
+ neptune | 90560
+ pluto | 24491
+ saturn |
+ uranus | 224
+ venus |
+(9 rows)
+
+SELECT name, lead(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lead
+---------+-------
+ earth | 4332
+ jupiter | 88
+ mars | 88
+ mercury | 60182
+ neptune | 90560
+ pluto | 24491
+ saturn | 224
+ uranus | 224
+ venus |
+(9 rows)
+
+SELECT name, lag(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lag
+---------+-------
+ earth | 4332
+ jupiter | 88
+ mars | 88
+ mercury | 60182
+ neptune | 90560
+ pluto | 24491
+ saturn | 224
+ uranus | 224
+ venus |
+(9 rows)
+
+SELECT name, lead(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | lead
+---------+-------
+ earth |
+ jupiter |
+ mars | 4332
+ mercury | 4332
+ neptune | 88
+ pluto | 60182
+ saturn | 90560
+ uranus | 24491
+ venus | 24491
+(9 rows)
+
+SELECT name, first_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | first_value
+---------+-------------
+ earth |
+ jupiter |
+ mars |
+ mercury |
+ neptune |
+ pluto |
+ saturn |
+ uranus |
+ venus |
+(9 rows)
+
+SELECT name, first_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | first_value
+---------+-------------
+ earth |
+ jupiter |
+ mars |
+ mercury |
+ neptune |
+ pluto |
+ saturn |
+ uranus |
+ venus |
+(9 rows)
+
+SELECT name, first_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | first_value
+---------+-------------
+ earth | 4332
+ jupiter | 4332
+ mars | 4332
+ mercury | 4332
+ neptune | 4332
+ pluto | 4332
+ saturn | 4332
+ uranus | 4332
+ venus | 4332
+(9 rows)
+
+SELECT name, last_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | last_value
+---------+------------
+ earth | 224
+ jupiter | 224
+ mars | 224
+ mercury | 224
+ neptune | 224
+ pluto | 224
+ saturn | 224
+ uranus | 224
+ venus | 224
+(9 rows)
+
+SELECT name, last_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | last_value
+---------+------------
+ earth | 224
+ jupiter | 224
+ mars | 224
+ mercury | 224
+ neptune | 224
+ pluto | 224
+ saturn | 224
+ uranus | 224
+ venus | 224
+(9 rows)
+
+SELECT name, last_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | last_value
+---------+------------
+ earth | 224
+ jupiter | 224
+ mars | 224
+ mercury | 224
+ neptune | 224
+ pluto | 224
+ saturn | 224
+ uranus | 224
+ venus | 224
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 4332
+ jupiter | 4332
+ mars | 4332
+ mercury | 4332
+ neptune | 4332
+ pluto | 4332
+ saturn | 4332
+ uranus | 4332
+ venus | 4332
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 88
+ jupiter | 88
+ mars | 88
+ mercury | 88
+ neptune | 88
+ pluto | 88
+ saturn | 88
+ uranus | 88
+ venus | 88
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 4332
+ jupiter | 4332
+ mars | 4332
+ mercury | 4332
+ neptune | 4332
+ pluto | 4332
+ saturn | 4332
+ uranus | 4332
+ venus | 4332
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 4332
+ jupiter | 4332
+ mars | 4332
+ mercury | 4332
+ neptune | 4332
+ pluto | 4332
+ saturn | 4332
+ uranus | 4332
+ venus | 4332
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 88
+ jupiter | 88
+ mars | 88
+ mercury | 88
+ neptune | 88
+ pluto | 88
+ saturn | 88
+ uranus | 88
+ venus | 88
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 4332
+ jupiter | 4332
+ mars | 4332
+ mercury | 4332
+ neptune | 4332
+ pluto | 4332
+ saturn | 4332
+ uranus | 4332
+ venus | 4332
+(9 rows)
+
+SELECT name, nth_value(orbit, -2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth |
+ jupiter |
+ mars |
+ mercury |
+ neptune |
+ pluto |
+ saturn |
+ uranus |
+ venus |
+(9 rows)
+
+SELECT name, nth_value(orbit, -2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth | 24491
+ jupiter | 24491
+ mars | 24491
+ mercury | 24491
+ neptune | 24491
+ pluto | 24491
+ saturn | 24491
+ uranus | 24491
+ venus | 24491
+(9 rows)
+
+SELECT name, nth_value(orbit, -2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+ name | nth_value
+---------+-----------
+ earth |
+ jupiter |
+ mars |
+ mercury |
+ neptune |
+ pluto |
+ saturn |
+ uranus |
+ venus |
+(9 rows)
+
+--cleanup
+DROP TABLE planets CASCADE;
+NOTICE: drop cascades to view v_planets
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index fe273aa31e..548902d482 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1276,3 +1276,67 @@ $$ LANGUAGE SQL STABLE;
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
SELECT * FROM pg_temp.f(2);
+
+-- RESPECT NULLS and IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+ name text,
+ orbit int
+);
+
+INSERT INTO planets VALUES
+ ('mercury', 88),
+ ('venus', 224),
+ ('earth', NULL),
+ ('mars', NULL),
+ ('jupiter', 4332),
+ ('saturn', 24491),
+ ('uranus', NULL),
+ ('neptune', 60182),
+ ('pluto', 90560);
+
+ -- test view definitions are preserved
+CREATE TEMP VIEW v_planets AS
+ SELECT
+ name,
+ sum(orbit) OVER (order by orbit) as sum_rows,
+ lag(orbit, 1) RESPECT NULLS OVER (ORDER BY name DESC) AS lagged_by_1,
+ lag(orbit, 2) IGNORE NULLS OVER w AS lagged_by_2,
+ first_value(orbit) IGNORE NULLS OVER w AS first_value_ignore,
+ nth_value(orbit,2) IGNORE NULLS OVER w AS nth_first_ignore,
+ nth_value(orbit,-2) IGNORE NULLS OVER w AS nth_last_ignore
+ FROM planets
+ WINDOW w as (ORDER BY name ASC);
+SELECT pg_get_viewdef('v_planets');
+
+SELECT name, lag(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lag(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lag(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, lead(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lead(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lead(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, lag(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lead(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, first_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, first_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, first_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, last_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, last_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, last_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, -2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, -2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, -2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+--cleanup
+DROP TABLE planets CASCADE;
