Subject: Re: BUG #18060: Left joining rows using random() function in join
 condition doesn't work as expected.
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
References: <18060-e59408b5655979ed@postgresql.org>
 <2548808.1692547941@sss.pgh.pa.us>
From: James Inform <james.inform@pharmapp.de>
Message-ID: <ec939c42-2f67-00ba-d1ff-4fcfe041c48b@pharmapp.de>
Date: Sun, 20 Aug 2023 20:26:34 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.16; rv:52.0)
 Gecko/20100101 PostboxApp/7.0.60
MIME-Version: 1.0
In-Reply-To: <2548808.1692547941@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
UI-OutboundReport: notjunk:1;M01:P0:Pf8dEYWQxMM=;Vj9+5iqiG2N1kV3hniiJcOO30S+
 N1ajxxtnBbPuTNhMQJyk1e/joXuGqS0TEgHYMHonFJ+yHEqB1t3f0cifF4m8LrYkVuJsEvqbq
 jE+K3M6wl69eQ1lSDgUs8izlnSgyJ0BrAkrY8x3mlkNuBFHO6gjMdQtUf2+Dcuk9HijT2XQVO
 eabk5ulTu5c5MfZ3bD2xClBUhqKO/rlzNqjfcwfvUOnWtU1vFw5vjc/c8eMS1TPbPNb/BVeDl
 BUX0iANPdUx7Lh6eMu/8aLM6ErlIJ9Zrhm/+aNTQ2FoyEw4+N/kt0LrJrBiaytNR03dYyVXC8
 HXyc0Mgioh5H7Uanf6Q3bv1UUbgREJr/kuS5Jnd1XxdF+cMvXS/STtGE2CmdCpY7mC7wPdiiE
 StO0nlKDv3SInvanAlQ5WN2DxrIXBMtw9vILdUxu/hfFfV0FQX+qsNCzchXdmcRtZD6CQ7Gqy
 r4w/N0lMqdU8Fx5O7Af2IsgdFnZ68MCIoO5Ctptp0SF8Y3Ob6TKriamN1vq+QSICIPZ//do9N
 aPWCHuppVdpy+WLOmNC8MVKLMK2GYePOpXksFKDpaV7VSiXWJK1wywJqTvBxNHI+lnCpWPIas
 d2rFFmI1dAs9H9RGwdMNp+tBm7BRGJkiuTkigSK9NDg+UE1/Cj0T1opq94xVkVQoxYHBLB6Yo
 C7wSAPQCuKC3fsGK/m+E81WbbBvKlzrcgK4yfZNZEXfrd9B74CLvLl7OQTLhLXuqlOIB/9GWk
 t+ZZ4Qg/iu7d2lsMzD6f6rcTSPzE7GRsrq1lDP4GVXudkfQUHpRuoqNE8OUXERcxl+PZC6aOa
 bCvwwJ9c+21DsaNZ7ZS9uRKjjtrk3KHoauH/VKCDobDbCBSWdjKg+KdrSKggNz2W8mkzuPsYf
 4WSs6oZnMDtaN5w==
Archived-At: 
 <https://www.postgresql.org/message-id/ec939c42-2f67-00ba-d1ff-4fcfe041c48b%40pharmapp.de>
Precedence: bulk

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@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