Re: Problem with volatile function

Craig Ringer <craig(at)postnewspapers(dot)com(dot)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.

We're not perfect about that, in a number of ways --- in particular,
we allow short-circuit evaluation of ANDed conditions, which means
that we might not bother to execute a volatile function for some
row of the Cartesian product if we're able to exclude that row from
the result based on other WHERE conditions.

The OP's complaint is a bit different from this, in that (if I
understand it correctly) he doesn't want the WHERE condition to
be dropped down to the individual relation scan, because that amounts
to improperly assuming that the volatile WHERE condition will produce
the same result at every row of the Cartesian product that included
that row from the base relation. This gripe isn't without foundation.
But I'm not seeing what properties you get from that that wouldn't
be broken by AND-shortcircuit evaluation.

Dropping the AND-shortcircuit optimization would mean that we revert
to the brain-dead "form the Cartesian product and then filter" approach
if even one volatile function appears anywhere in the WHERE conditions.
I doubt that either the OP or anyone else wants that.

regards, tom lane