From: Tom Lane <tgl@sss.pgh.pa.us>
To: 김명준 <audwns525@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Fwd: Unexpected Multiple Records from Randomized Query
Date: Fri, 23 Feb 2024 10:09:25 -0500
Message-ID: <813880.1708700965@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOKhjox_dfmEi5PtxMbAM5sh5W+VA_RO6A8w_jJJx87Cz8g0wQ@mail.gmail.com>
References: <CAOKhjoz9_Fsf+NdbL08te_nWi_-hE2tycAmO7Yz4X0zZAtC2tA@mail.gmail.com>
<CAOKhjox_dfmEi5PtxMbAM5sh5W+VA_RO6A8w_jJJx87Cz8g0wQ@mail.gmail.com>
=?UTF-8?B?6rmA66qF7KSA?= <audwns525@gmail.com> writes:
> explain analyze
> SELECT * FROM users WHERE name = 'User '||trunc(random()*100) ;
> I expected the result to return one record. However, in some cases, the
> result comes back with 2 or 3 records. What am I doing wrong?
random() is re-evaluated at each row, so it's not that surprising
if you sometimes get multiple matches. This is the same behavior
that you relied on to fill the table with not-all-the-same names.
The preferred way to avoid that is to stuff the random() call into
a CTE:
WITH x AS (SELECT random() AS r)
SELECT * FROM users, x WHERE name = 'User '||trunc(r*100) ;
or in this case better to shove the whole constant computation
into the CTE.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, audwns525@gmail.com
Subject: Re: Fwd: Unexpected Multiple Records from Randomized Query
In-Reply-To: <813880.1708700965@sss.pgh.pa.us>
References: <CAOKhjoz9_Fsf+NdbL08te_nWi_-hE2tycAmO7Yz4X0zZAtC2tA@mail.gmail.com> <CAOKhjox_dfmEi5PtxMbAM5sh5W+VA_RO6A8w_jJJx87Cz8g0wQ@mail.gmail.com> <813880.1708700965@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox