Pulling up sublink may break join-removal logic

[Richard Guo]

Hi hackers,

I happened to notice $subject and not sure if it's an issue or not. When
we're trying to remove a LEFT JOIN, one of the requirements is the inner
side needs to be a single baserel. If there is a join qual that is a
sublink and can be converted to a semi join with the inner side rel, the
inner side would no longer be a single baserel and as a result the LEFT
JOIN can no longer be removed.

Here is an example to illustrate this behavior:

create table a(i int, j int);
create table b(i int UNIQUE, j int);
create table c(i int, j int);

# explain (costs off) select a.i from a left join b on a.i = b.i and
    b.j in (select j from c where b.i = c.i);
  QUERY PLAN
---------------
 Seq Scan on a
(1 row)

For the query above, we do not pull up the sublink and the LEFT JOIN is
removed.


# explain (costs off) select a.i from a left join b on a.i = b.i and
    b.j in (select j from c);
              QUERY PLAN
---------------------------------------
 Hash Left Join
   Hash Cond: (a.i = b.i)
   ->  Seq Scan on a
   ->  Hash
         ->  Hash Semi Join
               Hash Cond: (b.j = c.j)
               ->  Seq Scan on b
               ->  Hash
                     ->  Seq Scan on c
(9 rows)

Now for this above query, the sublink is pulled up to be a semi-join
with inner side rel 'b', which makes the inner side no longer a single
baserel. That causes the LEFT JOIN failing to be removed.

That is to say, pulling up sublink sometimes breaks join-removal logic.
Is this an issue that bothers you too?

Thanks
Richard