Re: Strange random() Correlation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Strange random() Correlation
Date: 2006-05-27 15:50:33
Message-ID: 23553.1148745033@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2006-05-27 16:16:26 Re: BUG #2424: initdb Did Not Escape the Password
Previous Message Euler Taveira de Oliveira 2006-05-27 12:28:16 Re: BUG #2456: How to write user defined functions in Postgress sql