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_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/be63b285e5006459c1c66d986dd3da27cbcce746 Modified Files -------------- src/backend/utils/adt/int8.c | 36 ++++++- src/test/regress/expected/window.out | 204 ++++++++++++++++++++++++++++++++++- src/test/regress/sql/window.sql | 115 +++++++++++++++++++- 3 files changed, 348 insertions(+), 7 deletions(-)
