Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW

On Fri, Jul 3, 2026 at 10:38 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> It's a little hard for me to know what to do here, as I really don't
> know how commonly people use the EXCLUDE clause, and more so, how
> often the Run Condition optimisation will apply when those are used.
> It would be good to have an idea if the bug discovery was from a
> real-world case, or if it was discovered from tooling, or from looking
> at the code.

If it was from tooling, it must have stopped with the first case it
found, as is often the case. I just tried myself and found three more
apparent bugs with EXCLUDE both with and without the v1 patch. Do you
want to see the reproducers, or rethink the risk/reward ratio of
what's handled? I'm also not sure how common that clause is.

Separately, I also I believe I found a counterexample to the part
starting with /* No ORDER BY clause then all rows are peers */ :

CREATE TABLE t7 (x int);
INSERT INTO t7 VALUES (7), (8), (9);

=# SELECT * FROM
(SELECT x,
count(*) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c
FROM t7) s
WHERE c >= 2;
x | c
---+---
(0 rows)

=# SELECT * FROM
(SELECT x,
count(*) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c
FROM t7) s
WHERE c + 0 >= 2; -- baseline
x | c
---+---
2 | 2
2 | 3
(2 rows)

--
John Naylor
Amazon Web Services