On Mon, 2014-07-07 at 01:21 -0700, Jeff Davis wrote:
> On Sun, 2014-07-06 at 21:11 -0700, Jeff Davis wrote:
> > 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:
As innocent as this patch seemed at first, it actually opens up a lot of
questions.
Attached is the (incomplete) edit of the patch so far.
Changes from your patch:
* changed test to be locale-insensitive
* lots of refactoring in the execution itself
* fix offset 0 case
* many test improvements
* remove bitmapset and just use an array bitmap
* fix error message typo
Open Issues:
I don't think exposing the frame options is a good idea. That's an
internal concept now, but putting it in windowapi.h will mean that it
needs to live forever.
The struct is private, so there's no easy hack to access the frame
options directly. That means that we need to work with the existing API
functions, which is OK because I think that everything we want to do can
go into WinGetFuncArgInPartition(). If we do the same thing for
WinGetFuncArgInFrame(), then first/last/nth also work.
That leaves the questions:
* Do we want IGNORE NULLS to work for every window function, or only a
specified subset?
* If it only works for some window functions, is that hard-coded or
driven by the catalog?
* If it works for all window functions, could it cause some
pre-existing functions to behave strangely?
Also, I'm re-thinking Dean's comments here:
http://www.postgresql.org/message-id/CAEZATCWT3=P88nv2ThTjvRDLpOsVtAPxaVPe=MaWe-x=guh...@mail.gmail.com
He brings up a few good points. I will look into the frame vs. window
option, though it looks like you've already at least fixed the crash.
His other point about actually eliminating the NULLs from the window
itself is interesting, but I don't think it works. IGNORE NULLS ignores
*other* rows with NULL, but (per spec) does not ignore the current row.
That sounds awkward if you've already removed the NULL rows from the
window, but maybe there's something that could work.
And there are a few other things I'm still looking into, but hopefully
they don't raise new issues.
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
***************
*** 2494,2499 **** WinGetCurrentPosition(WindowObject winobj)
--- 2494,2510 ----
}
/*
+ * 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
***************
*** 301,306 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
--- 301,307 ----
%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
***************
*** 566,572 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
HANDLER HAVING HEADER_P HOLD HOUR_P
! IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P
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
--- 567,573 ----
HANDLER HAVING HEADER_P HOLD HOUR_P
! IDENTITY_P IF_P IGNORE ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_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
***************
*** 596,602 **** 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
--- 597,603 ----
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
***************
*** 11957,11975 **** 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*/
--- 11958,11985 ----
}
;
! 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*/
***************
*** 12963,12968 **** unreserved_keyword:
--- 12973,12979 ----
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
***************
*** 13051,13056 **** unreserved_keyword:
--- 13062,13068 ----
| 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("IGNORE 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,53 ----
int64 rank; /* current rank */
} rank_context;
+
+ typedef struct leadlag_const_context
+ {
+ /* the index of the lead / lagged value */
+ int64 current_nonnull;
+
+ /* how many non-NULL tuples before we can start emitting? */
+ int64 lag_wait;
+ } leadlag_const_context;
+
+ /*
+ * lead-lag process helpers
+ */
+ #define BITMAP_EXISTS(bitmap, index) \
+ (((bitmap)[(index) / 4] >> (((index) % 4)*2 + 0)) & 0x01)
+ #define BITMAP_NONNULL(bitmap, index) \
+ (((bitmap)[(index) / 4] >> (((index) % 4)*2 + 1)) & 0x01)
+ #define BITMAP_SET_EXISTS(bitmap, index) \
+ ((bitmap)[(index) / 4] |= (0x1 << (((index) % 4)*2 + 0)))
+ #define BITMAP_SET_NONNULL(bitmap, index) \
+ ((bitmap)[(index) / 4] |= (0x1 << (((index) % 4)*2 + 1)))
+
/*
* ntile process information
*/
***************
*** 38,43 **** typedef struct
--- 62,71 ----
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,
--- 308,315 ----
* 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
--- 319,343 ----
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)
--- 345,388 ----
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,
--- 392,674 ----
}
/*
+ * 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)
+ {
+ bool local_isout = false;
+ bool local_isnull = false;
+ Datum result;
+
+ Assert(seektype == WINDOW_SEEK_CURRENT);
+
+ /*
+ * 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;
+
+ /*
+ * When the offset is constant, we can mark the position of the first
+ * tuple we'll need. This optimization allows unneeded tuples to be
+ * freed, which also avoids searching through them unnecessarily.
+ *
+ * For a positive offset, the mark will be set to the current row,
+ * because we need to know whether it's NULL.
+ *
+ * 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.
+ */
+
+ context = WinGetPartitionLocalMemory(winobj,
+ sizeof(leadlag_const_context));
+
+ /*
+ * First time through, initialize.
+ */
+ if (WinGetCurrentPosition(winobj) == 0)
+ {
+ context->current_nonnull = -1;
+
+ /*
+ * If offset is positive, scan until 'offset' non-NULL values are
+ * seen, updating 'current_nonnull'. Do not set the mark.
+ */
+ if (offset >= 0)
+ {
+ int i;
+
+ for (i = 0; i < offset && !local_isout; i++)
+ {
+ do
+ {
+ WinGetFuncArgInPartition(winobj, 0,
+ ++context->current_nonnull,
+ WINDOW_SEEK_HEAD,
+ false,
+ &local_isnull,
+ &local_isout);
+ }
+ while (local_isnull && !local_isout);
+ }
+
+ context->lag_wait = 0;
+ }
+ else
+ 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--;
+ if (context->current_nonnull == -1)
+ context->current_nonnull = WinGetCurrentPosition(winobj);
+ }
+
+ *result_isnull = true;
+ *result_isout = true;
+ return (Datum) 0;
+ }
+
+ /* For negative offset, we fetch the value before scanning forward */
+ if (offset < 0)
+ result = WinGetFuncArgInPartition(winobj, 0,
+ context->current_nonnull,
+ WINDOW_SEEK_HEAD,
+ false,
+ result_isnull, result_isout);
+
+ /*
+ * If the current position's value is non-NULL, scan forward to find
+ * the next non-NULL value and update 'current_nonnull'. If offset is
+ * negative, then mark it.
+ *
+ * If the current position's value is NULL, then nothing changes.
+ */
+ if (!local_isnull)
+ {
+ do
+ {
+ WinGetFuncArgInPartition(winobj, 0,
+ ++context->current_nonnull,
+ WINDOW_SEEK_HEAD,
+ (offset < 0),
+ &local_isnull, &local_isout);
+ }
+ while (local_isnull && !local_isout);
+ }
+
+ /* For positive offset, we fetch the value after scanning forward */
+ if (offset >= 0)
+ result = WinGetFuncArgInPartition(winobj, 0,
+ context->current_nonnull,
+ WINDOW_SEEK_HEAD,
+ false,
+ result_isnull, result_isout);
+
+ return result;
+ }
+ else
+ {
+ char *bitmap;
+ int64 scanning,
+ current = WinGetCurrentPosition(winobj);
+
+ bool local_forward = (offset >= 0);
+
+ /*
+ * Create a bitmap with at least 2 * n positions. The first bit
+ * records whether we've examined that row before, and the second
+ * records whether it's non-NULL. No initialization is required,
+ * because it begins with all zeros, as it should.
+ *
+ * This bitmap allows us to randomly access the 'offset'th non-NULL
+ * value without scanning 'offset' (or more) tuple in the window each
+ * time. We still have to scan forward through the bitmap to skip over
+ * NULLs, but that's much cheaper.
+ */
+ bitmap = WinGetPartitionLocalMemory(
+ winobj, (WinGetPartitionRowCount(winobj) / 4) + 1);
+
+ /*
+ * 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 (BITMAP_EXISTS(bitmap, scanning))
+ {
+ local_isnull = !BITMAP_NONNULL(bitmap, scanning);
+ }
+ 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;
+
+ BITMAP_SET_EXISTS(bitmap, scanning);
+ if (!local_isnull)
+ BITMAP_SET_NONNULL(bitmap, 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)
***************
*** 313,318 **** PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
--- 314,320 ----
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,2251 ----
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 int
+ );
+ 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)
+
+ CREATE FUNCTION volatile_int(INT) RETURNS INT AS
+ $$ BEGIN RETURN $1; END; $$
+ LANGUAGE PLPGSQL VOLATILE;
+ -- (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, volatile_int(1)) 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, volatile_int(1)) 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, volatile_int(1)) 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, volatile_int(1)) 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, volatile_int(1)) 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, volatile_int(1)) 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)
+
+ -- (4) defaults
+ SELECT name, lead(age, 1, -1) 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 | -1
+ (8 rows)
+
+ SELECT name, lead(age, 2, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+ -----------+------
+ ajax | 4
+ alfred | 7
+ bones | 7
+ churchill | 7
+ lassie | 3
+ mickey | -1
+ molly | -1
+ rover | -1
+ (8 rows)
+
+ SELECT name, lead(age, 3, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+ -----------+------
+ ajax | 7
+ alfred | 3
+ bones | 3
+ churchill | 3
+ lassie | -1
+ mickey | -1
+ molly | -1
+ rover | -1
+ (8 rows)
+
+ SELECT name, lead(age, volatile_int(1), -1) 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 | -1
+ (8 rows)
+
+ SELECT name, lead(age, volatile_int(2), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+ -----------+------
+ ajax | 4
+ alfred | 7
+ bones | 7
+ churchill | 7
+ lassie | 3
+ mickey | -1
+ molly | -1
+ rover | -1
+ (8 rows)
+
+ SELECT name, lead(age, volatile_int(3), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lead
+ -----------+------
+ ajax | 7
+ alfred | 3
+ bones | 3
+ churchill | 3
+ lassie | -1
+ mickey | -1
+ molly | -1
+ rover | -1
+ (8 rows)
+
+ SELECT name, lag(age, 1, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax | -1
+ alfred | -1
+ bones | 8
+ churchill | 8
+ lassie | 8
+ mickey | 4
+ molly | 7
+ rover | 7
+ (8 rows)
+
+ SELECT name, lag(age, 2, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax | -1
+ alfred | -1
+ bones | -1
+ churchill | -1
+ lassie | -1
+ mickey | 8
+ molly | 4
+ rover | 4
+ (8 rows)
+
+ SELECT name, lag(age, 3, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax | -1
+ alfred | -1
+ bones | -1
+ churchill | -1
+ lassie | -1
+ mickey | -1
+ molly | 8
+ rover | 8
+ (8 rows)
+
+ SELECT name, lag(age, volatile_int(1), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax | -1
+ alfred | -1
+ bones | 8
+ churchill | 8
+ lassie | 8
+ mickey | 4
+ molly | 7
+ rover | 7
+ (8 rows)
+
+ SELECT name, lag(age, volatile_int(2), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax | -1
+ alfred | -1
+ bones | -1
+ churchill | -1
+ lassie | -1
+ mickey | 8
+ molly | 4
+ rover | 4
+ (8 rows)
+
+ SELECT name, lag(age, volatile_int(3), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ name | lag
+ -----------+-----
+ ajax | -1
+ alfred | -1
+ bones | -1
+ churchill | -1
+ lassie | -1
+ mickey | -1
+ molly | 8
+ rover | 8
+ (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: IGNORE 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: IGNORE 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)
+
+ SELECT name, lead(age, 0, -1) 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, -1) 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
+ DROP FUNCTION volatile_int(INT);
*** 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,732 ----
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 int
+ );
+
+ 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');
+
+ CREATE FUNCTION volatile_int(INT) RETURNS INT AS
+ $$ BEGIN RETURN $1; END; $$
+ LANGUAGE PLPGSQL VOLATILE;
+
+ -- (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, volatile_int(1)) OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age * 2, volatile_int(1)) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age * 2, volatile_int(1)) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+ -- (4) leads by expression
+ SELECT name, lead(age * 2, volatile_int(1)) OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age * 2, volatile_int(1)) RESPECT NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age * 2, volatile_int(1)) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+ -- (4) defaults
+ SELECT name, lead(age, 1, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age, 2, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age, 3, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age, volatile_int(1), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age, volatile_int(2), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lead(age, volatile_int(3), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+ SELECT name, lag(age, 1, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, 2, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, 3, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, volatile_int(1), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, volatile_int(2), -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, volatile_int(3), -1) 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;
+ SELECT name, lead(age, 0, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+ SELECT name, lag(age, 0, -1) IGNORE NULLS OVER (ORDER BY name) FROM dogs ORDER BY name;
+
+ DROP TABLE dogs CASCADE;
+ DROP FUNCTION volatile_int(INT);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
