Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
Oliver Ford <ojford@gmail.com>
From: Oliver Ford <ojford@gmail.com>
To: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Krasiyan Andreev <krasiyan@gmail.com>
Date: 2018-07-13T12:52:00Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Fix coding style with "else".
- 20628b62e46e 19 (unreleased) landed
-
Fix multi WinGetFuncArgInFrame/Partition calls with IGNORE NULLS.
- 2d7b247cb414 19 (unreleased) landed
-
Fix Coverity issue reported in commit 2273fa32bce.
- dd766a441d69 19 (unreleased) landed
-
Use ereport rather than elog in WinCheckAndInitializeNullTreatment.
- 5f3808646f67 19 (unreleased) landed
-
Avoid uninitialized-variable warnings from older compilers.
- 71540dcdcb22 19 (unreleased) cited
-
Fix Coverity issues reported in commit 25a30bbd423.
- 2273fa32bce7 19 (unreleased) landed
-
Improve EXPLAIN's display of window functions.
- 8b1b342544b6 18.0 cited
-
Automatically generate node support functions
- 964d01ae90c3 16.0 cited
Attachments
- 0001-respect.patch (application/octet-stream) patch 0001
Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM FIRST/LAST to the non-aggregate window functions. A previous patch (https://www.postgresql.org/message-id/CA+=vxNa5_N1q5q5OkxC0aQnNdbo2Ru6GVw+86wk+oNsUNJDLig@mail.gmail.com) partially implemented this feature. However, that patch worked by adding the null treatment clause to the window frame's frameOptions variable, and consequently had the limitation that it wasn't possible to reuse a window frame definition in a single query where two functions were called that had different null treatment options. This meant that the patch was never committed. The attached path takes a different approach which gets around this limitation. For example, the following query would not work correctly with the implementation in the old patch but does with the attached patch: WITH cte (x) AS ( select null union select 1 union select 2) SELECT x, first_value(x) over w as with_default, first_value(x) respect nulls over w as with_respect, first_value(x) ignore nulls over w as with_ignore from cte WINDOW w as (order by x nulls first rows between unbounded preceding and unbounded following); x | with_default | with_respect | with_ignore ---+--------------+--------------+------------- | | | 1 1 | | | 1 2 | | | 1 (3 rows) == Implementation == The patch adds two types to the pg_type catalog: "ignorenulls" and "fromlast". These types are of the Boolean category, and work as wrappers around the bool type. They are used as function arguments to extra versions of the window functions that take additional boolean arguments. RESPECT NULLS and FROM FIRST are ignored by the parser, but IGNORE NULLS and FROM LAST lead to the extra versions being called with arguments to ignore nulls and order from last. == Testing == Updated documentation and added regression tests. All existing tests pass. This change will need a catversion bump. Thanks to Krasiyan Andreev for initially testing this patch.