Fix COUNT's logic for window run condition support 9d9c02ccd added code to allow the executor to stop early when processing WindowAgg nodes where a monotonic window function starts producing values that result in a pushed-down qual no longer matching, and will never match again due to the window function's monotonic properties.
That commit requires a SupportRequestWFuncMonotonic to exist on the window function and for it to detect when the function is monotonic. For COUNT(ANY) and COUNT(*), the support function failed to consider some cases where the WindowClause used EXCLUDE to exclude certain rows from being aggregated. Some WindowClause definitions mean we aggregate rows that come after the current row, and when processing those rows later, if we EXCLUDE certain rows, the monotonic property can be broken. Wrongly treating the COUNT(*) or COUNT(ANY) aggregate as monotonic could lead to rows being filtered that should not be filtered from the result set. Another issue was that the support function for the COUNT aggregate mistakenly thought that a WindowClause without an ORDER BY meant that the results would be both monotonically increasing and decreasing, but that's only true when in RANGE mode, where all rows are peers. It is possible to support various cases that do have an EXCLUDE clause, but getting the logic correct for the exact set of cases that are valid is quite complex and would likely better be left for a future project. Here, we mostly disable run condition pushdown when there is an EXCLUDE clause unless the clause is for EXCLUDE CURRENT ROW, uses COUNT(*) (rather than COUNT(ANY)), and the window aggregate has no FILTER clause. Bug: #19533 Reported-by: Qifan Liu <[email protected]> Author: Chengpeng Yan <[email protected]> Author: David Rowley <[email protected]> Reviewed-by: Richard Guo <[email protected]> Reviewed-by: John Naylor <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 15 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a857321625ec9ca9ef56a3c8f20a73a7c5dec17d Modified Files -------------- src/backend/utils/adt/int8.c | 34 +++++- src/test/regress/expected/window.out | 204 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++++- 3 files changed, 346 insertions(+), 7 deletions(-)
