On Wed, 2014-04-16 at 12:50 +0100, Nicholas White wrote:
> 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:
I took a good look at this today.
* It fails for offset of 0 with IGNORE NULLS. Fixed (trivial).
* The tests are locale-sensitive. Fixed (trivial).
* The leadlag_common function is just way too long. I refactored the
IGNORE NULLS code into it's own function (win_get_arg_ignore_nulls())
with the same API as WinGetFuncArgInPartition. This cleans things up
substantially, and makes it easier to add useful comments.
* "We're implementing the former semantics, so we'll need to correct
slightly" sounds arbitrary, but it's mandated by the standard. That
should be clarified.
* I did a lot of other refactoring within win_get_arg_ignore_nulls for
the constant case. I'm not done yet, and I'm not 100% sure it's a net
gain, because the code ended up a little longer. But the previous
version was quite hard to follow because of so many special cases around
positive versus negative offsets. For instance, having the negative
'next' value in your code actually means something quite different than
when it's positive, but it took me a while to figure that out, so I made
it into two variables. I hope my code is moving it in a direction that's
easier for others to understand.
Please let me know if you think I am making things worse with my
refactorings. Otherwise I'll keep working on this and hopefully get it
committable soon.
The attached patch is still a WIP; just posting it here in case you see
any red flags.
Regards,
Jeff Davis
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13164,13169 **** SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
--- 13164,13170 ----
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>
***************
*** 13178,13184 **** 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
</entry>
</row>
--- 13179,13187 ----
<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. If
! <literal>IGNORE NULLS</> is specified then the function will be evaluated
! as if the rows containing nulls didn't exist.
</entry>
</row>
***************
*** 13191,13196 **** SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
--- 13194,13200 ----
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>
***************
*** 13205,13211 **** 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
</entry>
</row>
--- 13209,13217 ----
<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. If
! <literal>IGNORE NULLS</> is specified then the function will be evaluated
! as if the rows containing nulls didn't exist.
</entry>
</row>
***************
*** 13299,13309 **** 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</>.
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
--- 13305,13314 ----
<note>
<para>
The SQL standard defines a <literal>RESPECT NULLS</> or
! <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
*** a/src/backend/executor/nodeWindowAgg.c
--- b/src/backend/executor/nodeWindowAgg.c
***************
*** 2458,2464 **** window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
* API exposed to window functions
***********************************************************************/
-
/*
* WinGetPartitionLocalMemory
* Get working memory that lives till end of partition processing
--- 2458,2463 ----
***************
*** 2494,2499 **** WinGetCurrentPosition(WindowObject winobj)
--- 2493,2509 ----
}
/*
+ * 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.
*
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 293,298 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
--- 293,299 ----
%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
***************
*** 556,562 **** 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
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
--- 557,563 ----
HANDLER HAVING HEADER_P HOLD HOUR_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
***************
*** 586,592 **** 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
ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
--- 587,593 ----
RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFRESH REINDEX
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
! RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK
ROW ROWS RULE
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
***************
*** 11900,11918 **** window_definition:
}
;
! over_clause: OVER window_specification
! { $$ = $2; }
! | OVER ColId
{
WindowDef *n = makeNode(WindowDef);
! n->name = $2;
n->refname = NULL;
n->partitionClause = NIL;
n->orderClause = NIL;
! n->frameOptions = FRAMEOPTION_DEFAULTS;
n->startOffset = NULL;
n->endOffset = NULL;
! n->location = @2;
$$ = n;
}
| /*EMPTY*/
--- 11901,11928 ----
}
;
! 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 = $3;
n->refname = NULL;
n->partitionClause = NIL;
n->orderClause = NIL;
! n->frameOptions = FRAMEOPTION_DEFAULTS | $1;
n->startOffset = NULL;
n->endOffset = NULL;
! n->location = @3;
$$ = n;
}
| /*EMPTY*/
***************
*** 12906,12911 **** unreserved_keyword:
--- 12916,12922 ----
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
***************
*** 12993,12998 **** unreserved_keyword:
--- 13004,13010 ----
| REPLACE
| REPLICA
| RESET
+ | RESPECT
| RESTART
| RESTRICT
| RETURNS
*** a/src/backend/parser/parse_agg.c
--- b/src/backend/parser/parse_agg.c
***************
*** 694,721 **** transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
{
Index winref = 0;
ListCell *lc;
Assert(windef->refname == NULL &&
windef->partitionClause == NIL &&
! windef->orderClause == NIL &&
! windef->frameOptions == FRAMEOPTION_DEFAULTS);
foreach(lc, pstate->p_windowdefs)
{
! WindowDef *refwin = (WindowDef *) lfirst(lc);
!
winref++;
! if (refwin->name && strcmp(refwin->name, windef->name) == 0)
{
wfunc->winref = winref;
break;
}
}
! if (lc == 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
{
--- 694,775 ----
{
Index winref = 0;
ListCell *lc;
+ WindowDef *refwin = NULL;
Assert(windef->refname == NULL &&
windef->partitionClause == NIL &&
! windef->orderClause == NIL);
foreach(lc, pstate->p_windowdefs)
{
! WindowDef *thiswin = (WindowDef *) lfirst(lc);
winref++;
!
! 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 (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
{
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
***************
*** 726,731 **** ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
--- 726,747 ----
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
*/
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 4982,4992 **** get_rule_windowspec(WindowClause *wc, List *targetList,
bool needspace = false;
const char *sep;
ListCell *l;
appendStringInfoChar(buf, '(');
if (wc->refname)
{
! appendStringInfoString(buf, quote_identifier(wc->refname));
needspace = true;
}
/* partition clauses are always inherited, so only print if no refname */
--- 4982,4997 ----
bool needspace = false;
const char *sep;
ListCell *l;
+ size_t refname_len = 0;
+ int initial_buf_len = buf->len;
appendStringInfoChar(buf, '(');
if (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 */
***************
*** 5068,5074 **** get_rule_windowspec(WindowClause *wc, List *targetList,
/* we will now have a trailing space; remove it */
buf->len--;
}
! appendStringInfoChar(buf, ')');
}
/* ----------
--- 5073,5092 ----
/* we will now have a trailing space; remove it */
buf->len--;
}
!
! /*
! * 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, ')');
}
/* ----------
***************
*** 7860,7866 **** get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
! appendStringInfoString(buf, ") OVER ");
foreach(l, context->windowClause)
{
--- 7878,7884 ----
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
! appendStringInfoString(buf, ") ");
foreach(l, context->windowClause)
{
***************
*** 7868,7873 **** get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
--- 7886,7895 ----
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
*** a/src/backend/utils/adt/windowfuncs.c
--- b/src/backend/utils/adt/windowfuncs.c
***************
*** 13,19 ****
--- 13,21 ----
*/
#include "postgres.h"
+ #include "nodes/bitmapset.h"
#include "utils/builtins.h"
+ #include "utils/memutils.h"
#include "windowapi.h"
/*
***************
*** 24,29 **** typedef struct rank_context
--- 26,47 ----
int64 rank; /* current rank */
} rank_context;
+
+ typedef struct leadlag_const_context
+ {
+ /* the index of the lead / lagged value */
+ int64 next;
+
+ /* how many non-NULL tuples before we can start emitting? */
+ int64 lag_wait;
+ } leadlag_const_context;
+
+ /*
+ * lead-lag process helpers
+ */
+ #define ISNULL_INDEX(i) (2 * (i))
+ #define HAVESCANNED_INDEX(i) ((2 * (i)) + 1)
+
/*
* ntile process information
*/
***************
*** 38,43 **** typedef struct
--- 56,65 ----
static bool rank_up(WindowObject winobj);
static Datum leadlag_common(FunctionCallInfo fcinfo,
bool forward, bool withoffset, bool withdefault);
+ static Datum win_get_arg_ignore_nulls(WindowObject winobj, int argno,
+ int offset, int seektype,
+ bool const_offset, bool *isnull,
+ bool *isout);
/*
***************
*** 280,286 **** 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.
*/
static Datum
leadlag_common(FunctionCallInfo fcinfo,
--- 302,309 ----
* 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. We'll only
! * return this default if the offset we want is outside of the partition.
*/
static Datum
leadlag_common(FunctionCallInfo fcinfo,
***************
*** 290,303 **** leadlag_common(FunctionCallInfo fcinfo,
int32 offset;
bool const_offset;
Datum result;
! bool isnull;
! bool isout;
if (withoffset)
{
offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
if (isnull)
PG_RETURN_NULL();
const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
}
else
--- 313,337 ----
int32 offset;
bool const_offset;
Datum result;
! bool isnull = false;
! bool isout = false;
! bool ignore_nulls;
!
! /* is IGNORE NULLS specified? */
! ignore_nulls = (WinGetFrameOptions(winobj) & FRAMEOPTION_IGNORE_NULLS) != 0;
if (withoffset)
{
offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
if (isnull)
PG_RETURN_NULL();
+
+ /*
+ * 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).
+ */
const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
}
else
***************
*** 305,325 **** leadlag_common(FunctionCallInfo fcinfo,
offset = 1;
const_offset = true;
}
! result = WinGetFuncArgInPartition(winobj, 0,
! (forward ? offset : -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
*/
if (withdefault)
result = WinGetFuncArgCurrent(winobj, 2, &isnull);
}
if (isnull)
--- 339,382 ----
offset = 1;
const_offset = true;
}
+ if (!forward)
+ {
+ offset = -offset;
+ }
! if (ignore_nulls)
! {
! result = win_get_arg_ignore_nulls(winobj, 0,
! offset,
! WINDOW_SEEK_CURRENT,
! const_offset,
! &isnull, &isout);
! }
! else
! {
! 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.
*/
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)
***************
*** 329,334 **** leadlag_common(FunctionCallInfo fcinfo,
--- 386,714 ----
}
/*
+ * win_get_arg_ignore_nulls
+ *
+ * Like WinGetFuncArgInPartition, but skips over any rows where the argument
+ * evaluates to NULL as though they did not exist. Offset is relative to the
+ * current row (positive or negative). If offset is zero, *isout is set false,
+ * and the argument at the current row is returned, even if it is NULL.
+ *
+ * Some argument names differ from WinGetFuncArgInPartition, but the meanings
+ * are the same.
+ */
+ static Datum
+ win_get_arg_ignore_nulls(WindowObject winobj, int argno,
+ int offset, int seektype, bool const_offset,
+ bool *result_isnull, bool *result_isout)
+ {
+ /*
+ * 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;
+ bool local_isout = false;
+ bool local_isnull = false;
+ Datum result;
+
+ /*
+ * Special case: when applying the offset, we're ignoring all rows where
+ * the argument evaluates to NULL. But if the offset is zero, the standard
+ * requires that we evaluate the argument at the current row even if it is
+ * NULL.
+ */
+ if (offset == 0)
+ {
+ result = WinGetFuncArgInPartition(winobj, 0,
+ offset,
+ WINDOW_SEEK_CURRENT,
+ const_offset,
+ result_isnull, result_isout);
+ Assert (*result_isout == false);
+ return result;
+ }
+
+ if (const_offset)
+ {
+ leadlag_const_context *context;
+
+ /*
+ * For positive offset, the mark will always be set to the current
+ * row. We initialize by scanning until we find 'offset' non-NULL
+ * values, and remember that index in a context variable, 'next'. Each
+ * time this function is called, if the current argument value is
+ * NULL, we just return the same value. If the current row is
+ * non-NULL, we scan forward to find one more non-NULL value, update
+ * 'next' to that index, and return the new value.
+ *
+ * For a negative offset, the mark will always be set to the index of
+ * the 'offset'th non-NULL value before the current row if it
+ * exists. In addition to 'next', we also need to track another
+ * context variable 'lag_wait'; otherwise we don't know if we've
+ * actually seen 'offset' non-NULL values or not. The 'lag_wait' is
+ * initialized to 'offset' and counts down with each non-NULL value
+ * encountered, always returning NULL until it reaches zero. When
+ * lag_count is zero, we save the value at 'next'. If the argument
+ * value at the current row is NULL, we then scan forward until we
+ * find the next non-NULL value, and mark it and update 'next'. The
+ * saved value is returned.
+ */
+
+ context = WinGetPartitionLocalMemory(winobj,
+ sizeof(leadlag_const_context));
+
+ /*
+ * First time through, initialize.
+ */
+ if (WinGetCurrentPosition(winobj) == 0)
+ {
+ /*
+ * If offset is positive, we need to scan through looking for
+ * 'offset' non-NULL values. Do not set the mark.
+ */
+ if (offset >= 0)
+ {
+ int i, j;
+
+ for (i = 0, j = -1; i < offset && !local_isout; i++)
+ {
+ do
+ {
+ j++;
+ result = WinGetFuncArgInPartition(winobj, 0,
+ j,
+ WINDOW_SEEK_HEAD,
+ false,
+ &local_isnull,
+ &local_isout);
+ }
+ while (local_isnull && !local_isout);
+ }
+
+ context->next = j;
+ context->lag_wait = 0;
+ }
+ else
+ {
+ /* 'next' will be incremented before being read */
+ context->next = -1;
+ context->lag_wait = -offset;
+ }
+ }
+
+ /*
+ * Look at the current row to check whether it's NULL or not. If the
+ * offset is positive, use this opportunity to mark the position;
+ * otherwise it will be done later.
+ */
+ WinGetFuncArgInPartition(winobj, 0,
+ 0,
+ WINDOW_SEEK_CURRENT,
+ (offset >= 0),
+ &local_isnull, &local_isout);
+
+ /* offset negative, and haven't seen enough non-NULL values yet */
+ if (context->lag_wait > 0)
+ {
+ if (!local_isnull)
+ context->lag_wait--;
+
+ /* track the current position */
+ context->next++;
+
+ *result_isnull = true;
+ *result_isout = false;
+ return (Datum) 0;
+ }
+
+ /*
+ * For negative offset, we fetch the value from before the scan.
+ */
+ if (offset < 0)
+ {
+ result = WinGetFuncArgInPartition(winobj, 0,
+ context->next,
+ WINDOW_SEEK_HEAD,
+ false,
+ result_isnull, result_isout);
+ }
+
+ if (!local_isnull)
+ {
+ int i = context->next;
+
+ do
+ {
+ i++;
+ WinGetFuncArgInPartition(winobj, 0,
+ i,
+ WINDOW_SEEK_HEAD,
+ (offset < 0),
+ &local_isnull, &local_isout);
+ }
+ while (local_isnull && !local_isout);
+
+ context->next = i;
+ }
+
+ /*
+ * For positive offset, we fetch the value from after the scan.
+ */
+ if (offset >= 0)
+ {
+ result = WinGetFuncArgInPartition(winobj, 0,
+ context->next,
+ WINDOW_SEEK_HEAD,
+ false,
+ result_isnull, result_isout);
+ }
+
+ return result;
+ }
+ else
+ {
+ int64 scanning,
+ current = WinGetCurrentPosition(winobj);
+
+ bool local_forward = (offset >= 0);
+
+ /*
+ * 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.
+ */
+ local_forward = offset == 0 ? local_forward : (offset > 0);
+ offset = abs(offset);
+
+ for (scanning = current;; local_forward ? ++scanning : --scanning)
+ {
+ if (scanning < 0 || scanning >= WinGetPartitionRowCount(winobj))
+ {
+ local_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"!
+ */
+ local_isnull = true;
+ result = (Datum) 0;
+
+ break;
+ }
+
+ if (bms_is_member(HAVESCANNED_INDEX(scanning), *null_values))
+ {
+ local_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,
+ &local_isnull, &local_isout);
+ if (local_isout)
+ break;
+
+ bms_add_member(*null_values, HAVESCANNED_INDEX(scanning));
+ if (local_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 (!local_isnull)
+ {
+ if (offset == 0)
+ {
+ result = WinGetFuncArgInPartition(winobj, 0,
+ scanning,
+ WINDOW_SEEK_HEAD,
+ false,
+ &local_isnull, &local_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;
+ }
+ }
+ }
+
+ *result_isnull = local_isnull;
+ *result_isout = local_isout;
+ return result;
+ }
+
+ /*
* lag
* returns the value of VE evaluated on a row that is 1
* row before the current row within a partition,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 420,438 **** 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.
*/
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 */
} WindowDef;
/*
--- 420,457 ----
* 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. The semantics of each override
! * depends on the field.
*/
typedef struct WindowDef
{
NodeTag type;
! /* 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;
/*
***************
*** 457,462 **** typedef struct WindowDef
--- 476,482 ----
#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)
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 180,185 **** PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD)
--- 180,186 ----
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)
***************
*** 312,317 **** PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
--- 313,319 ----
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)
*** a/src/include/windowapi.h
--- b/src/include/windowapi.h
***************
*** 46,51 **** extern void *WinGetPartitionLocalMemory(WindowObject winobj, Size sz);
--- 46,53 ----
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);
*** a/src/test/regress/expected/window.out
--- b/src/test/regress/expected/window.out
***************
*** 1822,1824 **** SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
--- 1822,2064 ----
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
+ ('ajax', 'mythological', 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
+ -----------+-----
+ ajax |
+ 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
+ -----------+-----
+ ajax |
+ 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
+ -----------+-----
+ ajax |
+ 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
+ -----------+------
+ ajax | 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
+ -----------+------
+ ajax | 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
+ -----------+------
+ ajax | 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
+ -----------+-----
+ ajax |
+ 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
+ -----------+-----
+ ajax |
+ 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
+ -----------+-----
+ ajax |
+ 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
+ -----------+------
+ ajax | 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
+ -----------+------
+ ajax | 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
+ -----------+------
+ ajax | 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...
+ ^
+ -- ensure that a zero offset still returns the current value, even if NULL
+ SELECT name, lead(age, 0) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+ -----------+------
+ ajax |
+ alfred | 8
+ bones |
+ churchill |
+ lassie | 4
+ mickey | 7
+ molly |
+ rover | 3
+ (8 rows)
+
+ SELECT name, lag(age, 0) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax |
+ alfred | 8
+ bones |
+ churchill |
+ lassie | 4
+ mickey | 7
+ molly |
+ rover | 3
+ (8 rows)
+
+ DROP TABLE dogs CASCADE;
+ NOTICE: drop cascades to view v_dogs
*** a/src/test/regress/sql/window.sql
--- b/src/test/regress/sql/window.sql
***************
*** 641,643 **** SELECT to_char(SUM(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FO
--- 641,710 ----
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
+ ('ajax', 'mythological', 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;
+
+ -- ensure that a zero offset still returns the current value, even if NULL
+ SELECT name, lead(age, 0) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, 0) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+ DROP TABLE dogs CASCADE;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
