From: James Inform <james.inform@pharmapp.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18060: Left joining rows using random() function in join condition doesn't work as expected.
Date: Sun, 20 Aug 2023 19:58:29 +0200
Message-ID: <56f6ba9c-baf4-9e6a-78f4-1d22bab587de@pharmapp.de> (raw)
In-Reply-To: <2548808.1692547941@sss.pgh.pa.us>
References: <18060-e59408b5655979ed@postgresql.org>
<2548808.1692547941@sss.pgh.pa.us>
That explains the behavior.
I thought that the random() was "executed" one time per row, but that is
not the case.
So, thanks for your quick response.
PG Bug reporting form <noreply@postgresql.org> writes:
>> -- 2. Execute this query multiple time and you will see results where:
>> -- * no rows from link are joined
>> -- * extactly one row is joined
>> -- * multiple rows are joined
>> select * from source left join link on link_id = 1 + (random()*500)::int8
>> order by 1
>> /*
>> I would expect always exactly one row to be joined.
>> Instead I get 1, none or multiple.
> I see no bug here: instead, your expectation is faulty. The
> given join condition is unstable by definition, but it would
> have to be at least stable to produce the results you expect.
>
> Formally, the definition of a SQL join is "evaluate the join's ON
> condition for each pair of rows in the cross product of the two
> input relations, and return the row pair(s) that satisfy the ON".
> So with a random() join condition, anywhere from none to all of
> the join pairs involving a given LHS row might get returned,
> because the random() function will produce a different value
> for each join pair.
>
> You could shove the random() call into a materialized CTE if
> the semantics you want are that a single random() result is
> used across the entire query. If you want one random() result
> to be used for all join pairs involving a given LHS row, but
> different ones for different LHS rows, you could probably
> make that happen with some hack involving LATERAL. But I'm
> too lazy to work it out for you.
>
> 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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: james.inform@pharmapp.de, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18060: Left joining rows using random() function in join condition doesn't work as expected.
In-Reply-To: <56f6ba9c-baf4-9e6a-78f4-1d22bab587de@pharmapp.de>
* 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