Request for Patch Feedback: Lag & Lead Window Functions Can Ignore Nulls
Started by Nicholas Whitealmost 13 years ago1 messages
The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead,
lag, [...]. This is not implemented in PostgreSQL
(http://www.postgresql.org/docs/devel/static/functions-window.html)
I've had a go at implementing this, and I've attached the resulting patch.
It's not finished yet (there's no documentation and no tests for a start),
but I was hoping to find out if my solution is along the right lines.
In particular, I'm storing the ignore-nulls flag in the frameOptions of a
window function definition, and am adding a function to the windowapi.h to
get at these options. I'm keeping the last non-null value in
WinGetPartitionLocalMemory (which I hope is the right place), but I'm not
using any of the *GetDatum macros to access it.
An example of my change's behaviour:
nwhite=# select *, lag(num,0) ignore nulls over (order by generate_series)
from
nwhite-# (select generate_series from generate_series(0,10)) s
nwhite-# left outer join
nwhite-# numbers n
nwhite-# on (s.generate_series = n.num);
generate_series | num | lag
-----------------+-----+-----
0 | |
1 | 1 | 1
2 | | 1
3 | | 1
4 | 4 | 4
5 | 5 | 5
6 | | 5
7 | | 5
8 | | 5
9 | 9 | 9
10 | | 9
(11 rows)
I'd find this feature really useful, so I hope you can help me get my patch
to a contributable state.
Thanks -
Nick
Attachments:
lead-lag-ignore-nulls.patchapplication/octet-stream; name=lead-lag-ignore-nulls.patchDownload
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 3bc42ba..548c506 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1996,6 +1996,16 @@ WinGetCurrentPosition(WindowObject winobj)
Assert(WindowObjectIsValid(winobj));
return winobj->winstate->currentpos;
}
+/*
+ * WinGetFrameOptions
+ * Returns the frame option flags
+ */
+int
+WinGetFrameOptions(WindowObject winobj)
+{
+ Assert(WindowObjectIsValid(winobj));
+ return winobj->winstate->frameOptions;
+}
/*
* WinGetPartitionRowCount
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9d07f30..c6c2584 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -496,6 +496,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> window_clause window_definition_list opt_partition_clause
%type <windef> window_definition over_clause window_specification
opt_frame_clause frame_extent frame_bound
+ over_specification
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
@@ -551,7 +552,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
@@ -581,7 +582,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
@@ -11785,7 +11786,8 @@ window_definition:
}
;
-over_clause: OVER window_specification
+over_specification:
+ OVER window_specification
{ $$ = $2; }
| OVER ColId
{
@@ -11800,9 +11802,18 @@ over_clause: OVER window_specification
n->location = @2;
$$ = n;
}
- | /*EMPTY*/
- { $$ = NULL; }
- ;
+ ;
+
+over_clause: RESPECT NULLS_P over_specification { $$ = $3; }
+ | IGNORE NULLS_P over_specification
+ {
+ if($3)
+ $3->frameOptions |= FRAMEOPTION_IGNORE_NULLS;
+ $$ = $3;
+ }
+ | over_specification
+ | /*EMPTY*/ { $$ = NULL; }
+ ;
window_specification: '(' opt_existing_window_name opt_partition_clause
opt_sort_clause opt_frame_clause ')'
@@ -13007,6 +13018,7 @@ type_func_name_keyword:
| CURRENT_SCHEMA
| FREEZE
| FULL
+ | IGNORE
| ILIKE
| INNER_P
| IS
@@ -13019,6 +13031,7 @@ type_func_name_keyword:
| OUTER_P
| OVER
| OVERLAPS
+ | RESPECT
| RIGHT
| SIMILAR
| VERBOSE
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 2f171ac..3144fd7 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -292,6 +292,7 @@ leadlag_common(FunctionCallInfo fcinfo,
Datum result;
bool isnull;
bool isout;
+ bool ignore_nulls;
if (withoffset)
{
@@ -322,8 +323,29 @@ leadlag_common(FunctionCallInfo fcinfo,
result = WinGetFuncArgCurrent(winobj, 2, &isnull);
}
+ ignore_nulls = (WinGetFrameOptions(winobj) & FRAMEOPTION_IGNORE_NULLS) != 0;
+ if(ignore_nulls)
+ {
+ /*
+ * We'll keep the last non-null value we've seen in our per-partition chunk
+ * of memory, so it gets cleaned up for us.
+ */
+ Datum* stash = (Datum*) WinGetPartitionLocalMemory(winobj, sizeof(Datum));
+ if(isnull)
+ {
+ result = *stash;
+ isnull = result == 0;
+ }
+ else
+ {
+ *stash = result;
+ }
+ }
+
if (isnull)
+ {
PG_RETURN_NULL();
+ }
PG_RETURN_DATUM(result);
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 2229ef0..a13c58b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -435,6 +435,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 /* lead/lag/nth */
#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 68a13b7..99f96a5 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -179,6 +179,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, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
@@ -312,6 +313,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, TYPE_FUNC_NAME_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 5bbf1fa..81f5ba0 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);