Thanks for the detailed feedback, I'm sorry it took so long to
incorporate it. I've attached the latest version of the patch, fixing
in particular:
> We have this block:
I've re-written this so it only does a single pass through the window
definitions (my patch originally added a second pass), and only does
the clone if required.
> In gram.y there are some spurious whitespaces at end-of-line.
Fixed - I didn't know about diff --check, it's very useful!
> Also, in parsenodes.h, you had the [MANDATORY] and such tags.
I've re-written the comments (without tags) to make it much easier to
understand . I agree they were ugly!
>Exactly what case does the "in this case" phrase refer to?
Clarified in the comments
>A style issue. You have this:
Fixed
> And a final style comment.
Fixed
> Finally, I'm not really sure about the column you added to the regression
> tests table.
Indeed, it was a bit artificial. I've re-written the tests to use a
separate table as you suggest.
Thanks -
Nick
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0809a6d..5da852e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13185,6 +13185,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
lag(<replaceable class="parameter">value</replaceable> <type>any</>
[, <replaceable class="parameter">offset</replaceable> <type>integer</>
[, <replaceable class="parameter">default</replaceable> <type>any</> ]])
+ [ { RESPECT | IGNORE } NULLS ]
</function>
</entry>
<entry>
@@ -13199,7 +13200,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<replaceable class="parameter">default</replaceable> are evaluated
with respect to the current row. If omitted,
<replaceable class="parameter">offset</replaceable> defaults to 1 and
- <replaceable class="parameter">default</replaceable> to null
+ <replaceable class="parameter">default</replaceable> to null. If
+ <literal>IGNORE NULLS</> is specified then the function will be evaluated
+ as if the rows containing nulls didn't exist.
</entry>
</row>
@@ -13212,6 +13215,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
lead(<replaceable class="parameter">value</replaceable> <type>any</>
[, <replaceable class="parameter">offset</replaceable> <type>integer</>
[, <replaceable class="parameter">default</replaceable> <type>any</> ]])
+ [ { RESPECT | IGNORE } NULLS ]
</function>
</entry>
<entry>
@@ -13226,7 +13230,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<replaceable class="parameter">default</replaceable> are evaluated
with respect to the current row. If omitted,
<replaceable class="parameter">offset</replaceable> defaults to 1 and
- <replaceable class="parameter">default</replaceable> to null
+ <replaceable class="parameter">default</replaceable> to null. If
+ <literal>IGNORE NULLS</> is specified then the function will be evaluated
+ as if the rows containing nulls didn't exist.
</entry>
</row>
@@ -13320,11 +13326,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</> or
- <literal>IGNORE NULLS</> option for <function>lead</>, <function>lag</>,
- <function>first_value</>, <function>last_value</>, and
- <function>nth_value</>. This is not implemented in
- <productname>PostgreSQL</productname>: the behavior is always the
- same as the standard's default, namely <literal>RESPECT NULLS</>.
+ <literal>IGNORE NULLS</> option for <function>first_value</>,
+ <function>last_value</>, and <function>nth_value</>. This is not
+ implemented in <productname>PostgreSQL</productname>: the behavior is
+ always the same as the standard's default, namely <literal>RESPECT NULLS</>.
Likewise, the standard's <literal>FROM FIRST</> or <literal>FROM LAST</>
option for <function>nth_value</> is not implemented: only the
default <literal>FROM FIRST</> behavior is supported. (You can achieve
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 2fcc630..5cea825 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -2431,7 +2431,6 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
* API exposed to window functions
***********************************************************************/
-
/*
* WinGetPartitionLocalMemory
* Get working memory that lives till end of partition processing
@@ -2467,6 +2466,17 @@ WinGetCurrentPosition(WindowObject winobj)
}
/*
+ * WinGetFrameOptions
+ * Returns the frame option flags
+ */
+int
+WinGetFrameOptions(WindowObject winobj)
+{
+ Assert(WindowObjectIsValid(winobj));
+ return winobj->winstate->frameOptions;
+}
+
+/*
* WinGetPartitionRowCount
* Return total number of rows contained in the current partition.
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7b9895d..f11bc66 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -290,6 +290,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> TriggerEvents TriggerOneEvent
%type <value> TriggerFuncArg
%type <node> TriggerWhen
+%type <ival> opt_ignore_nulls
%type <list> event_trigger_when_list event_trigger_value_list
%type <defelt> event_trigger_when_item
@@ -552,7 +553,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 IN_P
+ IDENTITY_P IF_P IGNORE ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P
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
@@ -582,7 +583,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
+ RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
@@ -11885,19 +11886,28 @@ window_definition:
}
;
-over_clause: OVER window_specification
- { $$ = $2; }
- | OVER ColId
+opt_ignore_nulls:
+ IGNORE NULLS_P { $$ = FRAMEOPTION_IGNORE_NULLS; }
+ | RESPECT NULLS_P { $$ = 0; }
+ | /* EMPTY */ { $$ = 0; }
+ ;
+
+over_clause: opt_ignore_nulls OVER window_specification
+ {
+ $3->frameOptions |= $1;
+ $$ = $3;
+ }
+ | opt_ignore_nulls OVER ColId
{
WindowDef *n = makeNode(WindowDef);
- n->name = $2;
+ n->name = $3;
n->refname = NULL;
n->partitionClause = NIL;
n->orderClause = NIL;
- n->frameOptions = FRAMEOPTION_DEFAULTS;
+ n->frameOptions = FRAMEOPTION_DEFAULTS | $1;
n->startOffset = NULL;
n->endOffset = NULL;
- n->location = @2;
+ n->location = @3;
$$ = n;
}
| /*EMPTY*/
@@ -12891,6 +12901,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -12980,6 +12991,7 @@ unreserved_keyword:
| REPLACE
| REPLICA
| RESET
+ | RESPECT
| RESTART
| RESTRICT
| RETURNS
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 272d27f..6edfc4d 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -694,28 +694,82 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
{
Index winref = 0;
ListCell *lc;
+ WindowDef *refwin = NULL;
Assert(windef->refname == NULL &&
windef->partitionClause == NIL &&
- windef->orderClause == NIL &&
- windef->frameOptions == FRAMEOPTION_DEFAULTS);
+ windef->orderClause == NIL);
foreach(lc, pstate->p_windowdefs)
{
- WindowDef *refwin = (WindowDef *) lfirst(lc);
-
+ WindowDef *thiswin = (WindowDef *) lfirst(lc);
winref++;
- if (refwin->name && strcmp(refwin->name, windef->name) == 0)
+
+ if (thiswin->name && strcmp(thiswin->name, windef->name) == 0)
+ {
+ /*
+ * "thiswin" is the window we want - but we have to tweak the
+ * definition slightly as some window options (e.g. IGNORE
+ * NULLS) can't be specified a standalone window definition;
+ * they can only be specified when invoking a window function
+ * over a window definition. However, we don't want to modify
+ * the window def itself (as that'll affect other window
+ * functions that use it - so if we need to make changes to it
+ * we'll clone refwin, change the clone and add the clone to
+ * the list of window definitions in pstate.
+ *
+ * There's one catch: what if a statement has two (or more)
+ * window function calls that reference the same window
+ * definition, and both have IGNORE NULLs? We don't want to
+ * add two modified definitions to pstate, so we'll only break
+ * if thiswin is an exact match - if not we'll keep looking for
+ * a window definition with the same name *and* same frame
+ * options.
+ */
+ wfunc->winref = winref;
+ refwin = thiswin;
+ if(windef->frameOptions == FRAMEOPTION_DEFAULTS)
+ break; /* don't need to clone, so just use this one */
+ }
+
+ if (refwin && /* we need to have found the parent window */
+ thiswin->refname &&
+ strcmp(thiswin->refname, windef->name) == 0 && /* it reference the right parent */
+ thiswin->frameOptions == windef->frameOptions)
{
+ /* found a clone window specification that we can re-use */
wfunc->winref = winref;
+ refwin = thiswin;
break;
}
}
- if (lc == NULL) /* didn't find it? */
+
+ if (refwin == NULL) /* didn't find it? */
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("window \"%s\" does not exist", windef->name),
parser_errposition(pstate, windef->location)));
+ else if (windef->frameOptions != refwin->frameOptions /* we can't use the clone */
+ && windef->frameOptions != FRAMEOPTION_DEFAULTS) /* we can't use the parent */
+ {
+ /*
+ * This means we've found the parent, but no clones (if there were
+ * any) had the correct frame options. We'll clone the parent we
+ * found (refwin), set the frame options we want and add the new
+ * clone to pstate:
+ */
+ WindowDef *clone = makeNode(WindowDef);
+
+ clone->name = NULL;
+ clone->refname = pstrdup(refwin->name);
+ clone->frameOptions = windef->frameOptions; /* Note windef! */
+ clone->startOffset = copyObject(refwin->startOffset);
+ clone->endOffset = copyObject(refwin->endOffset);
+ clone->location = refwin->location;
+
+ pstate->p_windowdefs = lappend(pstate->p_windowdefs, clone);
+ wfunc->winref = list_length(pstate->p_windowdefs);
+ }
}
else
{
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index cc46084..ce39955 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -726,6 +726,22 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
NameListToString(funcname)),
parser_errposition(pstate, location)));
+ if (over->frameOptions & FRAMEOPTION_IGNORE_NULLS)
+ {
+ /*
+ * As this is only implemented for the lead & lag window functions
+ * we'll filter out all aggregate functions too.
+ */
+ if (fdresult != FUNCDETAIL_WINDOWFUNC
+ || (strcmp("lead", strVal(llast(funcname))) != 0 &&
+ strcmp("lag", strVal(llast(funcname))) != 0))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("RESPECT NULLS is only implemented for the lead and lag window functions"),
+ parser_errposition(pstate, location))); }
+ }
+
/*
* ordered aggs not allowed in windows yet
*/
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..add4048 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -4916,11 +4916,16 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
bool needspace = false;
const char *sep;
ListCell *l;
+ size_t refname_len = 0;
+ int initial_buf_len = buf->len;
appendStringInfoChar(buf, '(');
if (wc->refname)
{
- appendStringInfoString(buf, quote_identifier(wc->refname));
+ const char *quoted_refname = quote_identifier(wc->refname);
+
+ refname_len = strlen(quoted_refname);
+ appendStringInfoString(buf, quoted_refname);
needspace = true;
}
/* partition clauses are always inherited, so only print if no refname */
@@ -5002,7 +5007,20 @@ get_rule_windowspec(WindowClause *wc, List *targetList,
/* we will now have a trailing space; remove it */
buf->len--;
}
- appendStringInfoChar(buf, ')');
+
+ /*
+ * We'll tidy up the output slightly; if we've got a refname, but haven't
+ * overridden the partition-by, order-by or any of the frame flags
+ * relevant inside the window def's ()s, then we'll be left with
+ * "(<refname>)". We'll trim off the brackets in this case:
+ */
+ if (wc->refname && buf->len == initial_buf_len + refname_len + 1)
+ {
+ memcpy(buf->data + initial_buf_len, buf->data + initial_buf_len + 1, refname_len);
+ buf->len -= 1; /* the trailing ")" */
+ }
+ else
+ appendStringInfoChar(buf, ')');
}
/* ----------
@@ -7674,7 +7692,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
- appendStringInfoString(buf, ") OVER ");
+ appendStringInfoString(buf, ") ");
foreach(l, context->windowClause)
{
@@ -7682,6 +7700,10 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
if (wc->winref == wfunc->winref)
{
+ if (wc->frameOptions & FRAMEOPTION_IGNORE_NULLS)
+ appendStringInfoString(buf, "IGNORE NULLS ");
+ appendStringInfoString(buf, "OVER ");
+
if (wc->name)
appendStringInfoString(buf, quote_identifier(wc->name));
else
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 19f1fde..ccb53ce 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -13,7 +13,9 @@
*/
#include "postgres.h"
+#include "nodes/bitmapset.h"
#include "utils/builtins.h"
+#include "utils/memutils.h"
#include "windowapi.h"
/*
@@ -24,6 +26,18 @@ typedef struct rank_context
int64 rank; /* current rank */
} rank_context;
+
+typedef struct leadlag_const_context
+{
+ int64 next; /* the index of the lead / lagged value */
+} leadlag_const_context;
+
+/*
+ * lead-lag process helpers
+ */
+#define ISNULL_INDEX(i) (2 * (i))
+#define HAVESCANNED_INDEX(i) ((2 * (i)) + 1)
+
/*
* ntile process information
*/
@@ -280,7 +294,8 @@ window_ntile(PG_FUNCTION_ARGS)
* common operation of lead() and lag()
* For lead() forward is true, whereas for lag() it is false.
* withoffset indicates we have an offset second argument.
- * withdefault indicates we have a default third argument.
+ * withdefault indicates we have a default third argument. We'll only
+ * return this default if the offset we want is outside of the partition.
*/
static Datum
leadlag_common(FunctionCallInfo fcinfo,
@@ -290,8 +305,24 @@ leadlag_common(FunctionCallInfo fcinfo,
int32 offset;
bool const_offset;
Datum result;
- bool isnull;
- bool isout;
+ bool isnull = false;
+ bool isout = false;
+ bool ignore_nulls;
+
+ /**
+ * A ** pointer as we keep a Bitmapset * in the partition context, and
+ * WinGetPartitionLocalMemory returns a pointer to whatever's in the
+ * context.
+ */
+ Bitmapset **null_values;
+
+ /*
+ * We want to set the markpos (the earliest tuple we can access) as
+ * aggressively as possible to save memory, but if the offset isn't
+ * constant we really need random access on the partition (so can't mark
+ * at all).
+ */
+ ignore_nulls = (WinGetFrameOptions(winobj) & FRAMEOPTION_IGNORE_NULLS) != 0;
if (withoffset)
{
@@ -305,21 +336,247 @@ leadlag_common(FunctionCallInfo fcinfo,
offset = 1;
const_offset = true;
}
+ if (!forward)
+ {
+ offset = -offset;
+ }
- result = WinGetFuncArgInPartition(winobj, 0,
- (forward ? offset : -offset),
- WINDOW_SEEK_CURRENT,
- const_offset,
- &isnull, &isout);
+ if (ignore_nulls)
+ {
+ if(const_offset)
+ { int count_forward = 0;
+ leadlag_const_context *context;
+
+ /*
+ * We can process a constant offset much more efficiently; initially
+ * we'll scan through the first <offset> non-null rows, and store that
+ * index. On subsequent rows we'll decide whether to push that index
+ * forwards to the next non-null value, or just return it again.
+ */
+ context = WinGetPartitionLocalMemory(winobj, sizeof(leadlag_const_context));
+
+ /*
+ * Set the forward flag based on the direction of traversal - remember
+ * we can have a LEAD or LAG of -1, and that should be equivalent to a
+ * LAG or LEAD of 1 respectively.
+ */
+ forward = offset == 0 ? forward : (offset > 0);
+
+ if (WinGetCurrentPosition(winobj) == 0)
+ if (forward)
+ count_forward = offset;
+ else
+ context->next = offset; /* LAG, so offset is negative */
+ else
+ {
+ /*
+ * LEADs and LAGs are actually pretty similar - the decision of
+ * whether or not to push our offset value forwards depends on the
+ * current row (for LEADs) or the previous row (for LAGs) is NULL
+ * - hence the (forward ? 0 : -1) below.
+ */
+ result = WinGetFuncArgInPartition(winobj, 0,
+ forward ? 0 : -1,
+ WINDOW_SEEK_CURRENT,
+ forward,
+ &isnull, &isout);
+ if (!isnull)
+ count_forward = 1;
+ }
+
+ /*
+ * Count forward through the rows, skipping nulls and terminating if
+ * we run off the end of the window.
+ */
+ for (; count_forward > 0 && !isout; --count_forward)
+ {
+ do
+ {
+ /*
+ * Conveniently, calling WinGetFuncArgInPartition with an
+ * absolute index less than zero (correctly) sets isout and
+ * isnull to true
+ */
+ result = WinGetFuncArgInPartition(winobj, 0,
+ ++(context->next),
+ WINDOW_SEEK_HEAD,
+ !forward,
+ &isnull, &isout);
+ }
+ while (isnull && !isout);
+ }
+
+ result = WinGetFuncArgInPartition(winobj, 0,
+ context->next,
+ WINDOW_SEEK_HEAD,
+ !forward,
+ &isnull, &isout);
+ }
+ else
+ {
+ int64 scanning,
+ current = WinGetCurrentPosition(winobj);
+ bool scanForward;
+
+ /*
+ * This case is a little complicated; we're defining "IGNORE NULLS" as
+ * "run the query, and pretend the rows with nulls in them don't
+ * exist". This means that we'll scan from the current row an 'offset'
+ * number of non-null rows, and then return that one.
+ *
+ * As the offset isn't constant we need efficient random access to the
+ * partition, as we'll check upto O(partition size) tuples for each
+ * row we're calculating the window function value for.
+ */
+
+ null_values = (Bitmapset **) WinGetPartitionLocalMemory(winobj, sizeof(Bitmapset *));
+
+ if (*null_values == NULL)
+ {
+ MemoryContext oldcxt;
+
+ /*
+ * Accessing tuples is expensive, so we'll keep track of the ones
+ * we've accessed (more specifically, if they're null or not).
+ * We'll need one bit for whether the value is null and one bit
+ * for whether we've checked that tuple or not. We'll keep these
+ * two bits together (as opposed to having two separate bitmaps)
+ * to improve cache locality.
+ *
+ * However, we'd lose the efficient gains if we keep having to
+ * resize the Bitmapset (by setting higher and higher bits). We
+ * know the maximum number of bits we'll ever need, so we'll use
+ * bms_make_singleton to force our Bitmapset up to the required
+ * size.
+ */
+ int64 bits_needed = 2 * WinGetPartitionRowCount(winobj);
+
+ oldcxt = MemoryContextSwitchTo(GetMemoryChunkContext(null_values));
+ *null_values = bms_make_singleton(bits_needed + 1);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /*
+ * We use offset >= 0 instead of just forward as the offset might be
+ * in the opposite direction to the way we're scanning. We'll then
+ * force offset to be positive to make counting down the rows easier.
+ */
+ scanForward = offset == 0 ? forward : (offset > 0);
+ offset = abs(offset);
+
+ for (scanning = current;; scanForward ? ++scanning : --scanning)
+ {
+ if (scanning < 0 || scanning >= WinGetPartitionRowCount(winobj))
+ {
+ isout = true;
+
+ /*
+ * As we're out of the window we want to return NULL or the
+ * default value, but not whatever's left in result. We'll use
+ * the isnull flag to say "ignore it"!
+ */
+ isnull = true;
+ result = (Datum) 0;
+
+ break;
+ }
+
+ if (bms_is_member(HAVESCANNED_INDEX(scanning), *null_values))
+ {
+ isnull = bms_is_member(ISNULL_INDEX(scanning), *null_values);
+ }
+ else
+ {
+ /*
+ * first time we've accessed this index; let's see if it's
+ * null:
+ */
+ result = WinGetFuncArgInPartition(winobj, 0,
+ scanning,
+ WINDOW_SEEK_HEAD,
+ false,
+ &isnull, &isout);
+ if (isout)
+ break;
+
+ bms_add_member(*null_values, HAVESCANNED_INDEX(scanning));
+ if (isnull)
+ {
+ bms_add_member(*null_values, ISNULL_INDEX(scanning));
+ }
+ }
+
+ /*
+ * Now the isnull flag is set correctly. If !isnull there's a
+ * chance that we may stop iterating here:
+ */
+ if (!isnull)
+ {
+ if (offset == 0)
+ {
+ result = WinGetFuncArgInPartition(winobj, 0,
+ scanning,
+ WINDOW_SEEK_HEAD,
+ false,
+ &isnull, &isout);
+ break;
+ }
+ else
+ --offset; /* it's not null, so we're one step closer to
+ * the value we want */
+ }
+ else if (scanning == current)
+ {
+ /*--------
+ * A slight edge case. Consider:
+ *
+ * =================
+ * A | lag(A, 1)
+ * =================
+ * 1 | NULL
+ * 2 | 1
+ * NULL | ?
+ * =================
+ *
+ * Does a lag of one when the current value is null mean go back to the first
+ * non-null value (i.e. 2), or find the previous non-null value of the first
+ * non-null value (i.e. 1)? We're implementing the former semantics, so we'll
+ * need to correct slightly:
+ *--------
+ */
+ --offset;
+ }
+ }
+ }
+ }
+ else
+ {
+ /*
+ * We don't care about nulls; just get the row at the required offset.
+ */
+ result = WinGetFuncArgInPartition(winobj, 0,
+ offset,
+ WINDOW_SEEK_CURRENT,
+ const_offset,
+ &isnull, &isout);
+ }
if (isout)
{
/*
- * target row is out of the partition; supply default value if
- * provided. otherwise it'll stay NULL
+ * Target row is out of the partition; supply default value if
+ * provided.
*/
if (withdefault)
result = WinGetFuncArgCurrent(winobj, 2, &isnull);
+ else
+ {
+ /*
+ * Don't return whatever's lying around in result, force the
+ * output to null if there's no default.
+ */
+ Assert(isnull);
+ }
}
if (isnull)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 18d4991..8b18db4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -406,19 +406,38 @@ typedef struct SortBy
* For entries in a WINDOW list, "name" is the window name being defined.
* For OVER clauses, we use "name" for the "OVER window" syntax, or "refname"
* for the "OVER (window)" syntax, which is subtly different --- the latter
- * implies overriding the window frame clause.
+ * implies overriding the window frame clause. The semantics of each override
+ * depends on the field.
*/
typedef struct WindowDef
{
NodeTag type;
- char *name; /* window's own name */
- char *refname; /* referenced window name, if any */
- List *partitionClause; /* PARTITION BY expression list */
- List *orderClause; /* ORDER BY (list of SortBy) */
- int frameOptions; /* frame_clause options, see below */
- Node *startOffset; /* expression for starting bound, if any */
- Node *endOffset; /* expression for ending bound, if any */
- int location; /* parse location, or -1 if none/unknown */
+ /* Window's own name. This must be NULL for overrides. */
+ char *name;
+ /* Referenced window name, if any. This must be present on overrides. */
+ char *refname;
+ /*
+ * PARTITION BY expression list. If an override leaves this NULL, the
+ * parent's partitionClause will be used.
+ */
+ List *partitionClause;
+ /*
+ * ORDER BY (list of SortBy). This field is ignored in overrides - the
+ * parent's value will always be used.
+ */
+ List *orderClause;
+ /*
+ * The remaining fields in this struct must be specified on overrides,
+ * even if the override's value is the same as the parent's.
+ */
+ /* frame_clause options, see below */
+ int frameOptions;
+ /* Expression for starting bound, if any */
+ Node *startOffset;
+ /* expression for ending bound, if any */
+ Node *endOffset;
+ /* parse location, or -1 if none/unknown */
+ int location;
} WindowDef;
/*
@@ -443,6 +462,7 @@ typedef struct WindowDef
#define FRAMEOPTION_END_VALUE_PRECEDING 0x00800 /* end is V. P. */
#define FRAMEOPTION_START_VALUE_FOLLOWING 0x01000 /* start is V. F. */
#define FRAMEOPTION_END_VALUE_FOLLOWING 0x02000 /* end is V. F. */
+#define FRAMEOPTION_IGNORE_NULLS 0x04000
#define FRAMEOPTION_START_VALUE \
(FRAMEOPTION_START_VALUE_PRECEDING | FRAMEOPTION_START_VALUE_FOLLOWING)
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 61fae22..c11c65a 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -180,6 +180,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, UNRESERVED_KEYWORD)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
@@ -314,6 +315,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/include/windowapi.h b/src/include/windowapi.h
index 8557464..1d676e8 100644
--- a/src/include/windowapi.h
+++ b/src/include/windowapi.h
@@ -46,6 +46,8 @@ extern void *WinGetPartitionLocalMemory(WindowObject winobj, Size sz);
extern int64 WinGetCurrentPosition(WindowObject winobj);
extern int64 WinGetPartitionRowCount(WindowObject winobj);
+extern int WinGetFrameOptions(WindowObject winobj);
+
extern void WinSetMarkPosition(WindowObject winobj, int64 markpos);
extern bool WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2);
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c2cc742..6b84dc1 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1781,3 +1781,216 @@ SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
5 | t | t | t
(5 rows)
+-- check we haven't reserved words that might break backwards-compatibility:
+CREATE TABLE reserved (
+ ignore text,
+ respect text,
+ nulls text
+);
+DROP TABLE reserved;
+-- testing ignore nulls functionality
+CREATE TEMPORARY TABLE dogs (
+ name text,
+ breed text,
+ age smallint
+);
+INSERT INTO dogs VALUES
+('K-9', 'robot', NULL),
+('alfred', NULL, 8),
+('bones', 'shar pei', NULL),
+('churchill', 'bulldog', NULL),
+('lassie', NULL, 4),
+('mickey', 'poodle', 7),
+('molly', 'poodle', NULL),
+('rover', 'shar pei', 3);
+-- test view definitions are preserved
+CREATE TEMP VIEW v_dogs AS
+ SELECT
+ name,
+ sum(age) OVER (order by age rows between 1 preceding and 1 following) as sum_rows,
+ lag(age, 1) IGNORE NULLS OVER (ORDER BY name DESC) AS lagged_by_1,
+ lag(age, 2) IGNORE NULLS OVER w AS lagged_by_2
+ FROM dogs
+ WINDOW w as (ORDER BY name ASC);
+SELECT pg_get_viewdef('v_dogs');
+ pg_get_viewdef
+--------------------------------------------------------------------------------------------------
+ SELECT dogs.name, +
+ sum(dogs.age) OVER (ORDER BY dogs.age ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_rows,+
+ lag(dogs.age, 1) IGNORE NULLS OVER (ORDER BY dogs.name DESC) AS lagged_by_1, +
+ lag(dogs.age, 2) IGNORE NULLS OVER w AS lagged_by_2 +
+ FROM dogs +
+ WINDOW w AS (ORDER BY dogs.name);
+(1 row)
+
+-- (1) lags by constant
+SELECT name, lag(age) OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+-----------+-----
+ K-9 |
+ alfred |
+ bones | 8
+ churchill |
+ lassie |
+ mickey | 4
+ molly | 7
+ rover |
+(8 rows)
+
+SELECT name, lag(age) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+-----------+-----
+ K-9 |
+ alfred |
+ bones | 8
+ churchill |
+ lassie |
+ mickey | 4
+ molly | 7
+ rover |
+(8 rows)
+
+SELECT name, lag(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+-----------+-----
+ K-9 |
+ alfred |
+ bones | 8
+ churchill | 8
+ lassie | 8
+ mickey | 4
+ molly | 7
+ rover | 7
+(8 rows)
+
+-- (2) leads by constant
+SELECT name, lead(age) OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+-----------+------
+ K-9 | 8
+ alfred |
+ bones |
+ churchill | 4
+ lassie | 7
+ mickey |
+ molly | 3
+ rover |
+(8 rows)
+
+SELECT name, lead(age) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+-----------+------
+ K-9 | 8
+ alfred |
+ bones |
+ churchill | 4
+ lassie | 7
+ mickey |
+ molly | 3
+ rover |
+(8 rows)
+
+SELECT name, lead(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+-----------+------
+ K-9 | 8
+ alfred | 4
+ bones | 4
+ churchill | 4
+ lassie | 7
+ mickey | 3
+ molly | 3
+ rover |
+(8 rows)
+
+-- (3) lags by expression
+SELECT name, lag(age * 2) OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+-----------+-----
+ K-9 |
+ alfred |
+ bones | 16
+ churchill |
+ lassie |
+ mickey | 8
+ molly | 14
+ rover |
+(8 rows)
+
+SELECT name, lag(age * 2) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+-----------+-----
+ K-9 |
+ alfred |
+ bones | 16
+ churchill |
+ lassie |
+ mickey | 8
+ molly | 14
+ rover |
+(8 rows)
+
+SELECT name, lag(age * 2) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+-----------+-----
+ K-9 |
+ alfred |
+ bones | 16
+ churchill | 16
+ lassie | 16
+ mickey | 8
+ molly | 14
+ rover | 14
+(8 rows)
+
+-- (4) leads by expression
+SELECT name, lead(age * 2) OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+-----------+------
+ K-9 | 16
+ alfred |
+ bones |
+ churchill | 8
+ lassie | 14
+ mickey |
+ molly | 6
+ rover |
+(8 rows)
+
+SELECT name, lead(age * 2) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+-----------+------
+ K-9 | 16
+ alfred |
+ bones |
+ churchill | 8
+ lassie | 14
+ mickey |
+ molly | 6
+ rover |
+(8 rows)
+
+SELECT name, lead(age * 2) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+-----------+------
+ K-9 | 16
+ alfred | 8
+ bones | 8
+ churchill | 8
+ lassie | 14
+ mickey | 6
+ molly | 6
+ rover |
+(8 rows)
+
+-- these should be errors as the functionality isn't implemented yet:
+SELECT name, first_value(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ERROR: RESPECT NULLS is only implemented for the lead and lag window functions
+LINE 1: SELECT name, first_value(age) IGNORE NULLS OVER (ORDER BY na...
+ ^
+SELECT name, max(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ERROR: RESPECT NULLS is only implemented for the lead and lag window functions
+LINE 1: SELECT name, max(age) IGNORE NULLS OVER (ORDER BY name) FROM...
+ ^
+DROP TABLE dogs CASCADE;
+NOTICE: drop cascades to view v_dogs
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 31c98eb..13c0a7b 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -621,3 +621,66 @@ SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FO
SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
FROM (VALUES (1,true), (2,true), (3,false), (4,false), (5,true)) v(i,b)
WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);
+
+-- check we haven't reserved words that might break backwards-compatibility:
+CREATE TABLE reserved (
+ ignore text,
+ respect text,
+ nulls text
+);
+DROP TABLE reserved;
+
+-- testing ignore nulls functionality
+
+CREATE TEMPORARY TABLE dogs (
+ name text,
+ breed text,
+ age smallint
+);
+
+INSERT INTO dogs VALUES
+('K-9', 'robot', NULL),
+('alfred', NULL, 8),
+('bones', 'shar pei', NULL),
+('churchill', 'bulldog', NULL),
+('lassie', NULL, 4),
+('mickey', 'poodle', 7),
+('molly', 'poodle', NULL),
+('rover', 'shar pei', 3);
+
+-- test view definitions are preserved
+CREATE TEMP VIEW v_dogs AS
+ SELECT
+ name,
+ sum(age) OVER (order by age rows between 1 preceding and 1 following) as sum_rows,
+ lag(age, 1) IGNORE NULLS OVER (ORDER BY name DESC) AS lagged_by_1,
+ lag(age, 2) IGNORE NULLS OVER w AS lagged_by_2
+ FROM dogs
+ WINDOW w as (ORDER BY name ASC);
+SELECT pg_get_viewdef('v_dogs');
+
+-- (1) lags by constant
+SELECT name, lag(age) OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lag(age) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lag(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+-- (2) leads by constant
+SELECT name, lead(age) OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lead(age) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lead(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+-- (3) lags by expression
+SELECT name, lag(age * 2) OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lag(age * 2) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lag(age * 2) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+-- (4) leads by expression
+SELECT name, lead(age * 2) OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lead(age * 2) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, lead(age * 2) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+-- these should be errors as the functionality isn't implemented yet:
+SELECT name, first_value(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+SELECT name, max(age) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+DROP TABLE dogs CASCADE;
\ No newline at end of file
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
