On 3/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > select f(x) from t where id = 1 order by n;
> > can cause f to execute for the entire table even if id is unique.
>
> Really? I'd consider it a bug if so. Compare
>
> select 1/x from t where x > 0
>
> If the presence of zeroes in t can make this throw a zero-divide error,
> the database is broken. In my mind the SQL spec is perfectly clear that
> WHERE filtering occurs before evaluation of the SELECT targetlist.
> (Sorting, however, occurs afterward --- so there are certainly potential
> gotchas of this ilk. But the specific example you give is bogus.)
You are quite right..I didn't state the problem properly. The
particular one that burned me was actually:
select f(x) from t where k order by y limit 1;
...which may or may not execute f(x) more than once depending on how
the planner implements order by y...the limit clause does not
necessarily guard against this, but a where clause does provide a
guarantee.
for posterity, the fix was:
select f(q.x) from (select x from t where k order by y limit 1) q;
if you will recall the f(x) in my case was a user_lock function and
the results were not pleasant :-) So out of habit I tend to separate
the extration from the function execution via subquery.
Merlin