Tom Lane wrote:
Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> writes:
Personally I'd expect that to only evaluate once. It's saying "where
f_name.counter in this row is equal to some single random value
generated at the start of the query". The parameters of the random()
function do not depend on the input, so Pg evaluates it once rather than
with each row.
No, that's not the model that we use. In my view, the semantics
specified by the SQL standard are that
SELECT ... FROM a, b, c WHERE ...
are that you notionally form the Cartesian product of a X b X c
and then evaluate the WHERE condition at each row of the product
to find out which rows make it into the SELECT result. Obviously,
the DBMS is going to try like heck to optimize the execution of that
model, but its optimizations should not change the results.
Thanks for explaining that; I'd completely misunderstood the way Pg
handles evaluation of expressions that don't explicitly reference fields
in the records being scanned.
I think the workarounds I suggested should still work, despite the
incorrect reasoning on which they were based.
--
Craig Ringer
