Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> 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