Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group