Re: pull_up_simple_subquery

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> While working on KaiGai's "leaky views" patch, I found myself
> scrutinizing the behavior of the function named in the subject line;
> and specifically the retest of is_simple_subquery(). I've been unable
> to make that fail.

It might be that the is_simple_subquery conditions can't currently fail,
though that has been possible in the past and could be again someday.
The is_safe_append_member conditions can trivially fail after pullup,
however. An example in the regression database:

create or replace function foo1() returns setof int8 as
' select q2 from int8_tbl, tenk1 where q1 = unique1 '
language sql stable;

select * from foo1() union all select q1 from int8_tbl;

Like the comment says, I'd rather just retest the conditions than try to
work out exactly what might be possible or impossible to happen.

> However, despite my best efforts, I can't figure out what scenario
> it's protecting us against, at least not on current sources. The
> original bug report is here:

> http://archives.postgresql.org/pgsql-general/2004-01/msg00375.php

> Tom's reply indicates that the v4 example shouldn't get flattened, but
> it looks to me like current sources do flatten it and I really don't
> see why they shouldn't. Poking around with git bisect and the patch
> shown above, I see that the test case stopped tickling this code with
> commit e6ae3b5dbf2c07bceb737c5a0ff199b1156051d1, which introduced
> PlaceHolderVars, apparently for the precise purpose of allowing joins
> of this type to be flattened.

Yes, that was the point of PlaceHolderVars: we used to not be able to
flatten subqueries underneath outer joins, if they had any non-nullable
output expressions. Adding a PHV ensures that the expression will go to
null if it's supposed to.

regards, tom lane