From: Tom Lane <tgl@sss.pgh.pa.us>
To: james.inform@pharmapp.de
cc: 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: <18060-e59408b5655979ed@postgresql.org>
References: <18060-e59408b5655979ed@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 18 Aug 2023 09:30:51 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2548807.1692547941.1@sss.pgh.pa.us>
Date: Sun, 20 Aug 2023 12:12:21 -0400
Message-ID: <2548808.1692547941@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2548808.1692547941%40sss.pgh.pa.us>
Precedence: bulk

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