Re: BUG #18060: Left joining rows using random() function in join condition doesn't work as expected.

Just looked at the results again.

They look strange. When I execute the query multiple times I get three
kinds of results:
- NO match on all columns
- THE SAME match on all columns
- THREE DIFFERENT columns that are repeated for all the 1000 rows.

With your explanation there should be randomly assigned rows appearing,
but the seems to be a pattern. At least more than those three different
ones.

Have you executed my example and looked at the results?
If not, please give it a try. Nothing looks random there.

> PG Bug reporting form <noreply(at)postgresql(dot)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