支持lateral join的下推

[jd-zhang]

Issue migrated from trac ticket # 743

component: DBA GUI | priority: major

2022-05-26 16:27:41: smith created the issue

---

例如:

explain (verbose, costs off)
select * from
  int8_tbl c left join (
    int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1
      on a.q2 = ss1.q1
    cross join
    lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
  ) on c.q2 = ss2.q1,
  lateral (select ss2.y offset 0) ss3;

下推之后的执行计划为：

Nested Loop
   Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1, (COALESCE(COALESCE(b.q2, '42'::bigint), d.q2)), ((COALESCE(COALESCE(b.q2, '42'::bigint), d.q2)))
   ->  RemotePlan
         Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, COALESCE(b.q2, '42'::bigint), COALESCE(COALESCE(b.q2, '42'::bigint), d.q2)
         Shard: 1        Remote SQL: SELECT c.q1,c.q2,a.q1,a.q2,b.q1,var$$0,phv$1,phv$0 FROM ( regression_$$_public.int8_tbl  as c left join (( regression_$$_public.int8_tbl  as a left join (SELECT *,  Coales
ce(b.q2,42) AS phv$1 FROM  regression_$$_public.int8_tbl  as b) as b on (a.q2 = b.q1)) join lateral (SELECT d.q1 as var$$0,phv$0 FROM (SELECT *,  Coalesce(phv$1,d.q2) AS phv$0 FROM  regression_$$_public.int8_
tbl  as d) as d) tmp$$1) on (c.q2 = var$$0))
   ->  Result
         Output: (COALESCE(COALESCE(b.q2, '42'::bigint), d.q2))
(7 rows)