MIME-Version: 1.0
References: 
 <CAAAe_zBdtRuAfyEX6eqtprY8ejGXqo6ndrpy0LPEHSzGWDvpxw@mail.gmail.com>
 <20260302.094632.1950837561737687899.ishii@postgresql.org>
 <CAAAe_zCkpYn7qh=chxVLo4-HG8D57RwxhUq-c9zrYvZVtdDgeA@mail.gmail.com>
 <20260302.141823.129040280886273031.ishii@postgresql.org>
In-Reply-To: <20260302.141823.129040280886273031.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Tue, 3 Mar 2026 20:32:51 +0900
Message-ID: 
 <CAAAe_zCOCYF2_VdvsOn7KY5LwYJ2Tef5huHQV1hK7FJOyVd=3Q@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: vik@postgresfriends.org, er@xs4all.nl, jacob.champion@enterprisedb.com,
	david.g.johnston@gmail.com, peter@eisentraut.org,
	pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000001f9ee064c1d11b6"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zCOCYF2_VdvsOn7KY5LwYJ2Tef5huHQV1hK7FJOyVd%3D3Q%40mail.gmail.com>
Precedence: bulk

--00000000000001f9ee064c1d11b6
Content-Type: text/plain; charset="UTF-8"

Hi,Tatsuo

While reviewing the RPR test cases, I noticed that a subquery filter
on RPR window function results silently returns wrong results.

For example, given this query:

  SELECT * FROM (
      SELECT id, val, COUNT(*) OVER w as cnt
      FROM rpr_copy
      WINDOW w AS (
          ORDER BY id
          ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          PATTERN (A B?)
          DEFINE A AS val > 10, B AS val > 20
      )
  ) sub
  WHERE cnt > 0
  ORDER BY id;

This should return 2 rows, but returns 0 rows instead.

The EXPLAIN plan shows that "cnt > 0" is pushed down into the
WindowAgg node as a Run Condition:

  WindowAgg
    Run Condition: (count(*) OVER w > 0)    <-- pushed down
    ->  Sort
          ->  Seq Scan on rpr_copy

I will investigate the cause and work on a fix.

Best regards,
Henson

--00000000000001f9ee064c1d11b6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,Tatsuo<br><br>While reviewing the RPR test cases, I not=
iced that a subquery filter<br>on RPR window function results silently retu=
rns wrong results.<br><br>For example, given this query:<br><br><font face=
=3D"monospace">=C2=A0 SELECT * FROM (<br>=C2=A0 =C2=A0 =C2=A0 SELECT id, va=
l, COUNT(*) OVER w as cnt<br>=C2=A0 =C2=A0 =C2=A0 FROM rpr_copy<br>=C2=A0 =
=C2=A0 =C2=A0 WINDOW w AS (<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ORDER BY =
id<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ROWS BETWEEN CURRENT ROW AND UNBOU=
NDED FOLLOWING<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 PATTERN (A B?)<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 DEFINE A AS val &gt; 10, B AS val &gt; 20<b=
r>=C2=A0 =C2=A0 =C2=A0 )<br>=C2=A0 ) sub<br>=C2=A0 WHERE cnt &gt; 0<br>=C2=
=A0 ORDER BY id;</font><br><br>This should return 2 rows, but returns 0 row=
s instead.<br><br>The EXPLAIN plan shows that &quot;cnt &gt; 0&quot; is pus=
hed down into the<br>WindowAgg node as a Run Condition:<br><br><font face=
=3D"monospace">=C2=A0 WindowAgg<br>=C2=A0 =C2=A0 Run Condition: (count(*) O=
VER w &gt; 0) =C2=A0 =C2=A0&lt;-- pushed down<br>=C2=A0 =C2=A0 -&gt; =C2=A0=
Sort<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -&gt; =C2=A0Seq Scan on rpr_copy=
</font><br><br>I will investigate the cause and work on a fix.<br><br>Best =
regards,<br>Henson<br></div>

--00000000000001f9ee064c1d11b6--