Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sk0EQ-006aDH-Gb for pgsql-general@arkaria.postgresql.org; Fri, 30 Aug 2024 11:55:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sk0EN-0037UK-BI for pgsql-general@arkaria.postgresql.org; Fri, 30 Aug 2024 11:55:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sk0EM-0037UC-Vn for pgsql-general@lists.postgresql.org; Fri, 30 Aug 2024 11:55:55 +0000 Received: from mail-lj1-x22c.google.com ([2a00:1450:4864:20::22c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sk0EK-002FEk-4X for pgsql-general@lists.postgresql.org; Fri, 30 Aug 2024 11:55:54 +0000 Received: by mail-lj1-x22c.google.com with SMTP id 38308e7fff4ca-2ef27bfd15bso16340151fa.2 for ; Fri, 30 Aug 2024 04:55:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1725018952; x=1725623752; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=MpPn7fMIJT9bTpZjDI5F+JdW/BxXt22AWNb3qTLaZ6o=; b=FVNdU5Koo/+zvaCRQEn0eXVxQGQH8oY//4zWPbBrJRcFsh/i+V9oLCQbcuaFYiQWpv tywkeNof4vtNp6XQ/0+MuMTeILgew9Pq0U1PV+QnysterlcBjrVblTrcjWWa+aiZVQMH 7iUTWFnCM4eGqXu8eQayHcPlKNHXLuflLrjDm5AEtmgd7U5nkxyqMLMZaaKSWVHNudw2 fAAPVGzwL71xO23ggFoh5vAfJXELflGBEcio1zXrlcYeC7p03+LjG+RnULuUHkPprBfm hPY1rYMEhB862esGipbMrQi3AJg07S5L8D/4xVXbAK7NvuYSaBB8H1tvhU4ejyam/0zb mGqg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1725018952; x=1725623752; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=MpPn7fMIJT9bTpZjDI5F+JdW/BxXt22AWNb3qTLaZ6o=; b=WcUApKRe7UqSWzrMT0HO5wa9yZkxUaEX7imPn8mPP1hO3klNLJnWtpTg2+vERSuJdP 5dvgBg1PxgqK8jSftyFVEyQP/IdSEppDMVKubKZrSa+jIby7hM1VLEr/SnfYgmB+v2jS 1gnpKsyFQCn3ABAnNnKSeFGd1wdIb9upPXRxlSlVTL7/UBdbt7Yhl4hwlIUGAldxl57u cww2h5g7PnD5bEuEjvL8X45qRlul/rAKLjTeANTPQWSGejnkxZRdwqkiUBcj9QIpzoDH tFqqhWVkjfPQbAT3zHtK9dpCtuuY/6qiAwDBFMlVkH5G/4c50EGVz4sF2cVTO0j2wkX0 Y5MQ== X-Forwarded-Encrypted: i=1; AJvYcCUJD3Xm25FrsR4Nv3glZxZRchSPe8LpKnadG/w/Psy2GDDpgFtD5K0ZSontqursKe1x2pNKw6lr8cn/P9Fi@lists.postgresql.org X-Gm-Message-State: AOJu0Yx5C8WO21O7KpmzFOS6h71Nk+MmBPy7Zr0YmBrwAI3gW0w/dcu6 /zudD8vwQPKDCSb1Z+rXv9BsX55B1lA57lvkf4U4iOzSgy0P/mTZA4gsTMqalt1scw58LzGIapu DRq6U0Az5OIScdQ/GMsC/y1T41As= X-Google-Smtp-Source: AGHT+IEWqBvfkv6O07BMM4Rsl6qVcedyHgVO1A/ZEIOYqPwdJe9hyFNPd/nTmo/EGdmJJcvQknbilouJNE1Wy76cDMU= X-Received: by 2002:a2e:811:0:b0:2f3:e2fd:aed4 with SMTP id 38308e7fff4ca-2f6105cbb56mr37895361fa.13.1725018951487; Fri, 30 Aug 2024 04:55:51 -0700 (PDT) MIME-Version: 1.0 References: <317220.1724976433@sss.pgh.pa.us> In-Reply-To: From: Rumpi Gravenstein Date: Fri, 30 Aug 2024 07:55:40 -0400 Message-ID: Subject: Re: Analytic Function Bug To: Tom Lane , PostgreSQL Content-Type: multipart/alternative; boundary="000000000000d597f80620e54529" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d597f80620e54529 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Wait...I see my issue. Duh! The where clause is applied after the CTE is evaluated On Fri, Aug 30, 2024 at 7:37=E2=80=AFAM Rumpi Gravenstein wrote: > > that the lag() functions are seeing some rows that don't show up in > the final output. > > > 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 wrot= e: > >> Rumpi Gravenstein 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 >> > > > -- > Rumpi Gravenstein > --=20 Rumpi Gravenstein --000000000000d597f80620e54529 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Wait...I see my issue.=C2=A0 Duh!=C2=A0 The where clause i= s applied after the CTE is evaluated

On Fri, Aug 30, 2024 at 7:37=E2=80=AFAM= Rumpi Gravenstein <rgravens@gmail= .com> wrote:
<snip>
that the lag() functions a= re seeing some rows that don't show up in
the final output.=C2=A0=C2= =A0
</snip>

I'm under the impression that the predicate filter is applied bef= ore the analytic is evaluated.=C2=A0 Are you suggesting that I have this wr= ong -- the analytic is evaluated and then the filter is applied?

<= div class=3D"gmail_quote">
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

=C2=A0 =C2=A0 partition by p.logical_partition_key, p.txt order by indx

but then you filter by

=C2=A0 =C2=A0 where logical_partition_key=3D'TEST_DATA' and usage_t= ext=3D'F(T61)(EXPORT)';

that the lag() functions are seeing some rows that don't show up in
the final output.=C2=A0 (This'd require that some output rows from &quo= t;parse"
share txt values but not usage_text values, but that certainly looks
like it's possible.)=C2=A0 So IMO you have not actually demonstrated th= at
there is any bug.

=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


--
Ru= mpi Gravenstein


--
Ru= mpi Gravenstein
--000000000000d597f80620e54529--