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.
Date: Sun, 20 Aug 2023 12:12:21 -0400
Message-ID: <2548808.1692547941@sss.pgh.pa.us> (raw)
In-Reply-To: <18060-e59408b5655979ed@postgresql.org>
References: <18060-e59408b5655979ed@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, james.inform@pharmapp.de, 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: <2548808.1692547941@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox