Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qXmiD-0001ih-Ct for pgsql-bugs@arkaria.postgresql.org; Sun, 20 Aug 2023 17:59:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qXmhC-0004RT-ID for pgsql-bugs@arkaria.postgresql.org; Sun, 20 Aug 2023 17:58:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qXmhC-0004RL-8K for pgsql-bugs@lists.postgresql.org; Sun, 20 Aug 2023 17:58:38 +0000 Received: from mout.kundenserver.de ([212.227.126.187]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qXmh9-0001Ac-1V for pgsql-bugs@lists.postgresql.org; Sun, 20 Aug 2023 17:58:37 +0000 Received: from vmpopsmtp.local ([185.212.53.230]) by mrelayeu.kundenserver.de (mreue012 [213.165.67.97]) with ESMTPSA (Nemesis) id 1Mm9NA-1ppxZB1sO0-00iBkO; Sun, 20 Aug 2023 19:58:33 +0200 Received: from mackerio.local (pakerio.local [192.168.2.101]) by vmpopsmtp.local (Postfix) with ESMTPS id 60A8214035C; Sun, 20 Aug 2023 19:58:32 +0200 (CEST) X-Footer: cGhhcm1hcHAuZGU= Received: from wsmpbm2.fritz.box ([10.0.8.53]) (authenticated user james.inform@pharmapp.de) by mackerio.local (Kerio Connect 9.2.7 patch 3) with ESMTPSA (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256 bits)); Sun, 20 Aug 2023 19:58:51 +0200 Subject: Re: BUG #18060: Left joining rows using random() function in join condition doesn't work as expected. To: Tom Lane Cc: pgsql-bugs@lists.postgresql.org References: <18060-e59408b5655979ed@postgresql.org> <2548808.1692547941@sss.pgh.pa.us> From: James Inform Message-ID: <56f6ba9c-baf4-9e6a-78f4-1d22bab587de@pharmapp.de> Date: Sun, 20 Aug 2023 19:58:29 +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 X-Provags-ID: V03:K1:b2S3WaN/Yd+zDeXSsxlJlSjpFp5ek/l3o7P3o+OaLeFBJ90B5iA z/HvmWpLVOSCUDoMoh5R1iHno4H80KT+EcvRD4vZs4nVo1AG90eBRuillEP2XRwzQziDWhw SZsjLCdXwzT/MGq+SAkHylgxv5C5XS7gKIWD2DIP/jvvJIcF5NLptp7ywti8YwZcz2z97Ny 2eAakOu3sotYBWL/FlLiw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:nFGzrYryzz4=;yE/Pt4UuPyvgRyCC6yODh2PzNz3 amMwwVuOp8aloc4Hi0LmksSpbXGscwfOXFF5uhTnG+g0S9N+SXt7FItKDVshLKPL0rmiyfj5e b5M7/SaStmCxaLFP+U1LMLtaGPhgM1FcW+FVnWSAcbABgj8Fo+LKKejHH++wKrVj4M0jwAhSe rd3dpeNuYH/oXl9hX1QZUA778A28x7PzwhRZaFikjP0SO7w0vUbnnPW1lpa3am1+lP8qNO917 w663tosfPqFkzMmLoxTg1MKZyiASU1vI39hSlq2DCod29pqmD2mWMKUy0IrYQk4a8nOZPuN/2 9eTVP5gLTwLjKPr6IrPu3CuoxgYoFmBgZCyL10bGP19mKJvgreYzQDdlOBKUYNrwMgLE8fpXe DswVBlkAIKznLeWECaLztiucUOXx062tmNdrypRWD/xnFZ9Dsugc3ZfN/7vvgxO7Ckyv1A/Q3 df0SOJXXlxyceE/Y57eCxkvPfbAmdRWwummVwHibqp4W0KR5ctdZi9CYa7LVZb4x1tN8Y/a0r p0+GIkjwdDMVehShU3TbGMEgDZFE4hqcmI8VxScZ1C7E7qs2YnljPtaL5SPYX1JlYYLRlvo7F okc+a7JGA/9lRJo0cqPLWxlQAm4xl/Q/8fGUGSybBUbE3TKRn6sogjty3MAVPJYYKgqQG40BA vNT+TSfVmUY5tonssq0V+snnC0JuuBJ52RT9jmamJ8pTUKeU2URafk9/un7AbeU+tJ75QixoT T5ZLtDYiVu2SDhsgowUXRooeInBucdkDkj5ElHiabqfrMJDQBuwDCRiohNCEOoQ9zCKQ12Ubx 4mAaDrL85udpfFa/JPkWLHsTxGrWvk0mb7Jxjvs+dvThQvTv8t+xBv5Hc1LQpk/x3K13cFxVG fXL9uCyQ/FRHj5w== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 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