On Fri, Oct 2, 2020 at 4:56 PM Tomas Vondra
<tomas.vondra@2ndquadrant.com> wrote:
> >And I don't see any reason why the CASE statement couldn't in theory
> >(I don't know the internals enough to know when it actually happens)
> >be done as part of the base relation scan (in this case, the seq
> >scan). It's not dependent on any information from the join.
> >
>
> Imagine a query like this:
>
> select t1.id, volatile_func() from t1 join t2 using (id);
>
> and now assume t2 contains duplicate values. If the volatile_func gets
> evaluated as part of the t1 scan, then we'll get multiple occurrences
> in the results, due to the t2 duplicates. I belive volatile functions
> are not expected to behave like that - the docs say:
>
> A query using a volatile function will re-evaluate the function at
> every row where its value is needed..
>
> And I assume this references to rows at the level where the function is
> used, i.e. after the join.
Ah, this makes sense. I was thinking exactly the opposite: that you'd
want not to execute volatile functions multiple times for the same
base rel row, but now that you describe it it makes sense why they
shouldn't be.
James