Re: Allowing join removals for more join types

On Tue, May 20, 2014 at 11:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > I'm also now wondering if I need to do some extra tests in the existing
> > code to ensure that the subquery would have had no side affects.
>
> You should probably at least refuse the optimization if the subquery's
> tlist contains volatile functions.
>
> Functions that return sets might be problematic too [ experiments... ]
> Yeah, they are. This behavior is actually a bit odd:
>
>
...

>
> regression=# select q1,unnest(array[1,2]) as u from int8_tbl group by 1;
> q1 | u
> ------------------+---
> 4567890123456789 | 1
> 4567890123456789 | 2
> 123 | 1
> 123 | 2
> (4 rows)
>
> EXPLAIN shows that the reason the last case behaves like that is that
> the SRF is expanded *after* the grouping step. I'm not entirely sure if
> that's a bug --- given the lack of complaints, perhaps not. But it shows
> you can't apply this optimization without changing the existing behavior.
>
> I doubt you should drop a subquery containing FOR UPDATE, either.
> That's a side effect, just as much as a volatile function would be.
>
> regards, tom lane
>

Yeah that is strange indeed.
I've made some updates to the patch to add some extra checks for any
volatile functions in the target list and set returning functions.
The FOR UPDATE currently does not really need an explicit check as I'm
currently only supporting removals of sub queries that have either GROUP BY
or DISTINCT clauses, none of which allow FOR UPDATE anyway.

Regards

David Rowley