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