diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 896c08c..75cb36e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -12003,6 +12003,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lag(value any [, offset integer [, default any ]]) + [respect nulls]|[ignore nulls] @@ -12017,7 +12018,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; default are evaluated with respect to the current row. If omitted, offset defaults to 1 and - default to null + default to null. If + IGNORE NULLS is specified and a previous evalution in the + current window has returned a non-null value then that value will be + returned instead. @@ -12030,6 +12034,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lead(value any [, offset integer [, default any ]]) + [respect nulls]|[ignore nulls] @@ -12044,7 +12049,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; default are evaluated with respect to the current row. If omitted, offset defaults to 1 and - default to null + IGNORE NULLS is specified and a previous evalution in the + current window has returned a non-null value then that value will be + returned instead. @@ -12138,11 +12145,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; The SQL standard defines a RESPECT NULLS or - IGNORE NULLS option for lead, lag, - first_value, last_value, and - nth_value. This is not implemented in - PostgreSQL: the behavior is always the - same as the standard's default, namely RESPECT NULLS. + IGNORE NULLS option for first_value, + last_value, and nth_value. This is not + implemented in PostgreSQL: the behavior is + always the same as the standard's default, namely RESPECT NULLS. Likewise, the standard's FROM FIRST or FROM LAST option for nth_value is not implemented: only the default FROM FIRST behavior is supported. (You can achieve 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 window_clause window_definition_list opt_partition_clause %type window_definition over_clause window_specification opt_frame_clause frame_extent frame_bound + over_specification %type opt_existing_window_name %type 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); diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 752c7b4..bcc9140 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -5,19 +5,20 @@ CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, salary int, - enroll_date date + enroll_date date, + term_date date ); INSERT INTO empsalary VALUES -('develop', 10, 5200, '2007-08-01'), -('sales', 1, 5000, '2006-10-01'), -('personnel', 5, 3500, '2007-12-10'), -('sales', 4, 4800, '2007-08-08'), -('personnel', 2, 3900, '2006-12-23'), -('develop', 7, 4200, '2008-01-01'), -('develop', 9, 4500, '2008-01-01'), -('sales', 3, 4800, '2007-08-01'), -('develop', 8, 6000, '2006-10-01'), -('develop', 11, 5200, '2007-08-15'); +('develop', 10, 5200, '2007-08-01', null), +('sales', 1, 5000, '2006-10-01', null), +('personnel', 5, 3500, '2007-12-10', null), +('sales', 4, 4800, '2007-08-08', '2010-09-22'), +('personnel', 2, 3900, '2006-12-23', null), +('develop', 7, 4200, '2008-01-01', null), +('develop', 9, 4500, '2008-01-01', null), +('sales', 3, 4800, '2007-08-01', '2009-03-05'), +('develop', 8, 6000, '2006-10-01', '2009-11-17'), +('develop', 11, 5200, '2007-08-15', null); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; depname | empno | salary | sum -----------+-------+--------+------- @@ -1020,5 +1021,96 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; ERROR: argument of ntile must be greater than zero SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; ERROR: argument of nth_value must be greater than zero +-- test null behaviour +SELECT lag(term_date) OVER (ORDER BY empno) FROM empsalary; + lag +------------ + + + + 03-05-2009 + 09-22-2010 + + + 11-17-2009 + + +(10 rows) + +SELECT lag(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + lag +------------ + + + + 03-05-2009 + 09-22-2010 + + + 11-17-2009 + + +(10 rows) + +SELECT lag(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + lag +------------ + + + + 03-05-2009 + 09-22-2010 + 09-22-2010 + 09-22-2010 + 11-17-2009 + 11-17-2009 + 11-17-2009 +(10 rows) + +SELECT lead(term_date) OVER (ORDER BY empno) FROM empsalary; + lead +------------ + + 03-05-2009 + 09-22-2010 + + + 11-17-2009 + + + + +(10 rows) + +SELECT lead(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + lead +------------ + + 03-05-2009 + 09-22-2010 + + + 11-17-2009 + + + + +(10 rows) + +SELECT lead(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + lead +------------ + + 03-05-2009 + 09-22-2010 + 09-22-2010 + 09-22-2010 + 11-17-2009 + 11-17-2009 + 11-17-2009 + 11-17-2009 + 11-17-2009 +(10 rows) + -- cleanup DROP TABLE empsalary; diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 769be0f..cc9b583 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -6,20 +6,21 @@ CREATE TEMPORARY TABLE empsalary ( depname varchar, empno bigint, salary int, - enroll_date date + enroll_date date, + term_date date ); INSERT INTO empsalary VALUES -('develop', 10, 5200, '2007-08-01'), -('sales', 1, 5000, '2006-10-01'), -('personnel', 5, 3500, '2007-12-10'), -('sales', 4, 4800, '2007-08-08'), -('personnel', 2, 3900, '2006-12-23'), -('develop', 7, 4200, '2008-01-01'), -('develop', 9, 4500, '2008-01-01'), -('sales', 3, 4800, '2007-08-01'), -('develop', 8, 6000, '2006-10-01'), -('develop', 11, 5200, '2007-08-15'); +('develop', 10, 5200, '2007-08-01', null), +('sales', 1, 5000, '2006-10-01', null), +('personnel', 5, 3500, '2007-12-10', null), +('sales', 4, 4800, '2007-08-08', '2010-09-22'), +('personnel', 2, 3900, '2006-12-23', null), +('develop', 7, 4200, '2008-01-01', null), +('develop', 9, 4500, '2008-01-01', null), +('sales', 3, 4800, '2007-08-01', '2009-03-05'), +('develop', 8, 6000, '2006-10-01', '2009-11-17'), +('develop', 11, 5200, '2007-08-15', null); SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname, salary; @@ -264,5 +265,18 @@ SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1; SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1; +-- test null behaviour +SELECT lag(term_date) OVER (ORDER BY empno) FROM empsalary; + +SELECT lag(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + +SELECT lag(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + +SELECT lead(term_date) OVER (ORDER BY empno) FROM empsalary; + +SELECT lead(term_date) RESPECT NULLS OVER (ORDER BY empno) FROM empsalary; + +SELECT lead(term_date) IGNORE NULLS OVER (ORDER BY empno) FROM empsalary; + -- cleanup DROP TABLE empsalary;