Date: Mon, 4 Nov 2024 15:17:16 -0800
From: Guyren Howe <guyren@gmail.com>
To: PG-General Mailing List <pgsql-general@postgresql.org>
Message-ID: <3ed0759d-c332-4f96-a147-499a694e9204@Spark>
Subject: nth_value out of more than n values returns null
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="6729560c_2b509be4_11af"
Archived-At: <https://www.postgresql.org/message-id/3ed0759d-c332-4f96-a147-499a694e9204%40Spark>
Precedence: bulk

--6729560c_2b509be4_11af
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

This query:

SELECT NTH_VALUE(id, 5000000) OVER (ORDER BY created_at, id ASC) FROM table

in a table where SELECT COUNT(*) returns a value a few thousand over 5 million, where id is the primary key, returns null.

The inclusion of the primary key should make the order by a total order. So there should be a 5 millionth row.

How can this happen?

--6729560c_2b509be4_11af
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<html xmlns=3D=22http://www.w3.org/1999/xhtml=22>
<head>
<title></title>
</head>
<body>
<div name=3D=22messageBodySection=22>
<div dir=3D=22auto=22>This query:<br />
<br />
<span style=3D=22color:=231d1c1d;background-color:=23f8f8f8;font-family:S=
lack-Lato, Slack-=46ractions, appleLogo, sans-serif;font-size: 15px=22>SE=
LECT NTH=5FVALUE(id, 5000000) OVER (ORDER BY created=5Fat, id ASC) =46ROM=
 table</span><span style=3D=22font-size: 15px=22><br /></span><span style=
=3D=22font-size: 15px=22><br /></span><span style=3D=22color:=231d1c1d;ba=
ckground-color:=23f8f8f8;font-family:Slack-Lato, Slack-=46ractions, apple=
Logo, sans-serif;font-size: 15px=22>in a table where SELECT COUNT(*) retu=
rns a value a few thousand over 5 million, where id is the primary key, r=
eturns null.</span><span style=3D=22font-size: 15px=22><br /></span><span=
 style=3D=22font-size: 15px=22><br /></span><span style=3D=22color:=231d1=
c1d;background-color:=23f8f8f8;font-family:Slack-Lato, Slack-=46ractions,=
 appleLogo, sans-serif;font-size: 15px=22>The inclusion of the primary ke=
y should make the order by a total order. So there should be a 5 milliont=
h row.</span><span style=3D=22font-size: 15px=22><br /></span><span style=
=3D=22font-size: 15px=22><br /></span><span style=3D=22color:=231d1c1d;ba=
ckground-color:=23f8f8f8;font-family:Slack-Lato, Slack-=46ractions, apple=
Logo, sans-serif;font-size: 15px=22>How can this happen=3F</span></div>
</div>
</body>
</html>

--6729560c_2b509be4_11af--