Re: Strange random() Correlation

On May 27 11:50, Tom Lane wrote:
> Volkan YAZICI <yazicivo@ttnet.net.tr> writes:
> > ISTM, there's a problem in the correlation of random() to outer JOINs.
>
> The random() functions are being evaluated more than once because the
> subselect gets "flattened" into the outer query, so that you have
> the equivalent of
>
>     select random(), ...  where t2.id = random() ...

Oops, sorry. I've just remembered this.

> We've previously discussed preventing the planner from flattening if
> there are any volatile functions in the sub-select's output list, but
> I think that would probably do about as much harm as good.

It can be quite informative to learn the pros and cons of this issue,
but I couldn't find related discussion in archives. I'd be so
appreciated if you can remember its subject or anything specifier for
the thread.

> > Furthermore, if you'd append an "OFFSET 0" to subselect, output becomes
> > more stable but still has some inconsistencies.
>
> I didn't see any...

That's all caused by a mis-interpretation of the output by me. Replacing
"(random() * 100)::int % 17" with "1 + ((random() * 100)::int % 16)"
solved my above question.


Regards.