MIME-Version: 1.0
References: <CAEpg1wDJU6DzUJxJdFAUZFzmw6bY+p17bXvCLupwsxc0V6vqrg@mail.gmail.com>
 <317220.1724976433@sss.pgh.pa.us>
In-Reply-To: <317220.1724976433@sss.pgh.pa.us>
From: Rumpi Gravenstein <rgravens@gmail.com>
Date: Fri, 30 Aug 2024 07:37:15 -0400
Message-ID: <CAEpg1wAyaMmYYgCLm1Ohft50SCiuhgMRRr0u880wAkyFxpbs6w@mail.gmail.com>
Subject: Re: Analytic Function Bug
To: Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f9ff700620e5037f"
Archived-At: <https://www.postgresql.org/message-id/CAEpg1wAyaMmYYgCLm1Ohft50SCiuhgMRRr0u880wAkyFxpbs6w%40mail.gmail.com>
Precedence: bulk

--000000000000f9ff700620e5037f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<snip>
that the lag() functions are seeing some rows that don't show up in
the final output.
</snip>

I'm under the impression that the predicate filter is applied before the
analytic is evaluated.  Are you suggesting that I have this wrong -- the
analytic is evaluated and then the filter is applied?

On Thu, Aug 29, 2024 at 8:07=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Rumpi Gravenstein <rgravens@gmail.com> writes:
> > Is this a PostgreSQL bug?
>
> Maybe, but you haven't provided enough information to let anyone else
> reproduce the behavior.
>
> Personally I'm suspicious that because your lag() calls are over
>
>     partition by p.logical_partition_key, p.txt order by indx
>
> but then you filter by
>
>     where logical_partition_key=3D'TEST_DATA' and
> usage_text=3D'F(T61)(EXPORT)';
>
> that the lag() functions are seeing some rows that don't show up in
> the final output.  (This'd require that some output rows from "parse"
> share txt values but not usage_text values, but that certainly looks
> like it's possible.)  So IMO you have not actually demonstrated that
> there is any bug.
>
>                         regards, tom lane
>


--=20
Rumpi Gravenstein

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

<div dir=3D"ltr"><div>&lt;snip&gt;</div><div>that the lag() functions are s=
eeing some rows that don&#39;t show up in<br>the final output.=C2=A0=C2=A0<=
br></div><div>&lt;/snip&gt;</div><div dir=3D"ltr"><br></div><div dir=3D"ltr=
">I&#39;m under the impression that the predicate filter is applied before =
the analytic is evaluated.=C2=A0 Are you suggesting that I have this wrong =
-- the analytic is evaluated and then the filter is applied?</div><br><div =
class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Aug 29,=
 2024 at 8:07=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">=
tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex">Rumpi Gravenstein &lt;<a href=3D"mailto:rgravens@gmail.com=
" target=3D"_blank">rgravens@gmail.com</a>&gt; writes:<br>
&gt; Is this a PostgreSQL bug?<br>
<br>
Maybe, but you haven&#39;t provided enough information to let anyone else<b=
r>
reproduce the behavior.<br>
<br>
Personally I&#39;m suspicious that because your lag() calls are over<br>
<br>
=C2=A0 =C2=A0 partition by p.logical_partition_key, p.txt order by indx<br>
<br>
but then you filter by<br>
<br>
=C2=A0 =C2=A0 where logical_partition_key=3D&#39;TEST_DATA&#39; and usage_t=
ext=3D&#39;F(T61)(EXPORT)&#39;;<br>
<br>
that the lag() functions are seeing some rows that don&#39;t show up in<br>
the final output.=C2=A0 (This&#39;d require that some output rows from &quo=
t;parse&quot;<br>
share txt values but not usage_text values, but that certainly looks<br>
like it&#39;s possible.)=C2=A0 So IMO you have not actually demonstrated th=
at<br>
there is any bug.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div><br clear=3D"all"><div><br></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature">Ru=
mpi Gravenstein<input type=3D"hidden"><input type=3D"hidden"><div></div></d=
iv></div>

--000000000000f9ff700620e5037f--