MIME-Version: 1.0
References: <19533-413a1014e5d0e766@postgresql.org>
 <EC273387-5C73-4C9C-BF30-8BA260A90FFB@outlook.com>
 <CAMbWs4-6umOecRifmC88RW0SHWDAvb4c3-VD8XQ3NJufsUyUOQ@mail.gmail.com>
 <CAApHDvqHXZpF9pPStLqPp5O=G2gVpPD959bMTRRkLjdy4Tvx7g@mail.gmail.com>
 <CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@mail.gmail.com>
In-Reply-To: 
 <CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@mail.gmail.com>
From: John Naylor <johncnaylorls@gmail.com>
Date: Fri, 3 Jul 2026 16:09:52 +0700
Message-ID: 
 <CANWCAZbiYQ+=CHAaftwp2x83Gy6tJCB8GUe15yO5DW-cpp0Mxg@mail.gmail.com>
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown
 on count() with EXCLUDE CURRENT ROW
To: David Rowley <dgrowleyml@gmail.com>
Cc: Richard Guo <guofenglinux@gmail.com>,
 Chengpeng Yan <chengpeng_yan@outlook.com>,
	"imchifan@163.com" <imchifan@163.com>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CANWCAZbiYQ%2B%3DCHAaftwp2x83Gy6tJCB8GUe15yO5DW-cpp0Mxg%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 3, 2026 at 10:38=E2=80=AFAM David Rowley <dgrowleyml@gmail.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);

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

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

--
John Naylor
Amazon Web Services