diff options
author | Tatsuo Ishii <ishii@postgresql.org> | 2025-10-03 09:47:36 +0900 |
---|---|---|
committer | Tatsuo Ishii <ishii@postgresql.org> | 2025-10-03 09:47:36 +0900 |
commit | 25a30bbd4235a49c854036c84fe90f2bc5a87652 (patch) | |
tree | 263bb113d48edb9858b6ad140590e9cec17a81d2 /src/backend/parser/parse_func.c | |
parent | 381f5cffae0040a402e082adc5d5e7636035d2a7 (diff) |
Add IGNORE NULLS/RESPECT NULLS option to Window functions.
Add IGNORE NULLS/RESPECT NULLS option (null treatment clause) to lead,
lag, first_value, last_value and nth_value window functions. If
unspecified, the default is RESPECT NULLS which includes NULL values
in any result calculation. IGNORE NULLS ignores NULL values.
Built-in window functions are modified to call new API
WinCheckAndInitializeNullTreatment() to indicate whether they accept
IGNORE NULLS/RESPECT NULLS option or not (the API can be called by
user defined window functions as well). If WinGetFuncArgInPartition's
allowNullTreatment argument is true and IGNORE NULLS option is given,
WinGetFuncArgInPartition() or WinGetFuncArgInFrame() will return
evaluated function's argument expression on specified non NULL row (if
it exists) in the partition or the frame.
When IGNORE NULLS option is given, window functions need to visit and
evaluate same rows over and over again to look for non null rows. To
mitigate the issue, 2-bit not null information array is created while
executing window functions to remember whether the row has been
already evaluated to NULL or NOT NULL. If already evaluated, we could
skip the evaluation work, thus we could get better performance.
Author: Oliver Ford <ojford@gmail.com>
Co-authored-by: Tatsuo Ishii <ishii@postgresql.org>
Reviewed-by: Krasiyan Andreev <krasiyan@gmail.com>
Reviewed-by: Andrew Gierth <andrew@tao11.riddles.org.uk>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: David Fetter <david@fetter.org>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com>
Reviewed-by: Chao Li <lic@highgo.com>
Discussion: https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
Diffstat (limited to 'src/backend/parser/parse_func.c')
-rw-r--r-- | src/backend/parser/parse_func.c | 9 |
1 files changed, 9 insertions, 0 deletions
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index c43020a769d..778d69c6f3c 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -100,6 +100,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, bool agg_star = (fn ? fn->agg_star : false); bool agg_distinct = (fn ? fn->agg_distinct : false); bool func_variadic = (fn ? fn->func_variadic : false); + int ignore_nulls = (fn ? fn->ignore_nulls : NO_NULLTREATMENT); CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL); bool could_be_projection; Oid rettype; @@ -518,6 +519,13 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, errmsg("%s is not an ordered-set aggregate, so it cannot have WITHIN GROUP", NameListToString(funcname)), parser_errposition(pstate, location))); + + /* It also can't treat nulls as a window function */ + if (ignore_nulls != NO_NULLTREATMENT) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("aggregate functions do not accept RESPECT/IGNORE NULLS"), + parser_errposition(pstate, location))); } } else if (fdresult == FUNCDETAIL_WINDOWFUNC) @@ -840,6 +848,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, wfunc->winstar = agg_star; wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE); wfunc->aggfilter = agg_filter; + wfunc->ignore_nulls = ignore_nulls; wfunc->runCondition = NIL; wfunc->location = location; |