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

On Sat, Jul 4, 2026 at 6:57 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Fri, 3 Jul 2026 at 21:10, John Naylor <johncnaylorls(at)gmail(dot)com> wrote:
> > 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.
>
> Please share what you discovered. It would be good to understand if
> it's just a simple missing check that can be added to handle all
> three, or if there's just more complexity in this than making this
> work correctly is worth.

I was going to share the three examples, but then I had a better idea:
I had Claude generate a PL/pgSQL script to exhaustively (or something
close to it) iterate through the the whole space on a few different
data set shapes, look for plans with run conditions, and compare the
result to the "c+0" oracle. (attached as
count_runcondition_matrix.sql). On master, it found 295 (!) distinct
window specs that give wrong answers with run conditions on at least
one data set (attached as runcond-failures-master.txt). With v2, that
number falls to 162, which is still a surprisingly large number
(attached as runcond-failures-v2.txt). This script takes a while to
run, so it's probably not a good basis for a regression test, but it
seems useful to test against, and it can be modified to test
hypotheses.

I then had Claude generate two patches against master, attached as v3.
One is the minimal fix to get the above test matrix to pass. I didn't
try to determine how many currently working cases got lost that way.
The other is an attempt to provide complete coverage of all
monotonicity proofs. The latter is quite an indigestible mess, so we
probably cannot commit something like that, but I think a reasonable
takeaway is that EXCLUDE presents a really difficult set of cases.

I'll also note that two of v2's tests are valid on the data they work
on, but are not valid in general. I've attached v2-wrong-test-repo.sql
to demonstrate.

--
John Naylor
Amazon Web Services