Re: Allowing join removals for more join types

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 from int8_tbl;
q1
------------------
123
123
4567890123456789
4567890123456789
4567890123456789
(5 rows)

regression=# select q1 from int8_tbl group by 1;
q1
------------------
4567890123456789
123
(2 rows)

regression=# select q1,unnest(array[1,2]) as u from int8_tbl;
q1 | u
------------------+---
123 | 1
123 | 2
123 | 1
123 | 2
4567890123456789 | 1
4567890123456789 | 2
4567890123456789 | 1
4567890123456789 | 2
4567890123456789 | 1
4567890123456789 | 2
(10 rows)

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