Re: Invalid optimization of VOLATILE function in WHERE clause?

> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
> Sent: Wednesday, September 19, 2012 5:51 PM
> To: ktm(at)rice(dot)edu; David Johnston
> Cc: 'Florian Schoppmann'; 'Robert Haas'; pgsql-hackers(at)postgresql(dot)org;
'Tom
> Lane'
> Subject: RE: [HACKERS] Invalid optimization of VOLATILE function in WHERE
> clause?
>
> "David Johnston" <polobo(at)yahoo(dot)com> wrote:
>
> > VOLATILE: "A Volatile function used in an ORDER BY or WHERE clause
> > without referencing any columns from the query itself (i.e., no
> > parameters or all constants) will be evaluated a single time and the
> > result treated as a constant (i.e., all rows will have identical
> > values) for that part of the query."
>
> I hope you're wrong about the ORDER BY part of that. A quick test
confirms
> that it works in ORDER BY, at least for some cases. If there are any
> exceptions to that, I would sure like to know about it -- and really soon.
>
> select * from generate_series(1, 10000) s(n)
> order by random() limit 10;
>
> -Kevin

I'd rather have someone who knows the code assert one way or the other; I
tossed it in there because I thought I've seen people complain that random()
doesn't work as expected with ORDER BY but that may just be faulty memory.
It may or may not depend on whether LIMIT/OFFSET are involved...? Used in
the SELECT-list it gets evaluated for each row and I guess the ORDER BY
could have that behavior as well (I would expect it to anyway), so is it
strictly limited to WHERE clause evaluation that this discrepancy manifests?

David J.