Re: subselect removes rows

On Mon, Nov 29, 2021 at 10:09 AM Poot, Bas (B.J.) <bas(dot)poot(at)politie(dot)nl>
wrote:

> The problem is that I don't expect (and can't imagine it is correct) that
> a (any) sub-select removes rows from the resultset.
>
>
> Ignoring your specific example for the moment your basic complaint seems
to be that:

select 1, unnest(array[]::integer[]); --zero rows
select 1, (select unnest(array[]::integer[])); -- one row, second column is
null

This is actually the reverse of what you are saying though - the subselect
actually allows the row to be returned, not the opposite. In short, it
turns the implicit join between the input row and the set returning
function into a left join instead of an inner join.

This is how things work today - an empty SRF function implicitly inner
joins on the rows of the main query and so, like any inner join, will
remove rows from the output if there are no records on the SRF side of the
join.

Others, or the mailing list archives where this has come up many times, can
provide further insight into the why. I choose not to remember such
details here and just accept it as the behavior. With the addition of
lateral joins the cases where you have an SRF in the select-list should
tend toward zero anyway (and then you get to be explicit as to inner or
outer join).

David J.