Re: Strange random() Correlation

Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)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() ...

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. The cases
where this actually matters are rare and the programmer knows darn well
what he's doing, so the workaround of inserting an OFFSET 0 to prevent
the flattening seems acceptable to me.

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

I didn't see any...

regards, tom lane