嘿，这个问题问到点子上了——CTE和LATERAL JOIN确实是关系代数树表示里的两大“刺头”，尤其是涉及到查询优化的全局效率问题，我来给你拆解下：

关于CTE的处理：不用强行内联，用“引用+独立子计划”解决 #

你担心内联CTE会导致重复计算、优化器无法全局优化，这个顾虑完全合理。实际上，现代数据库的逻辑计划里，并不会简单把CTE直接内联成代数树的一部分，而是会用绑定节点（Binding Node）或者公共表达式引用节点来处理：

• 首先，单独维护一个CTE的子计划集合，每个CTE都对应一个独立的逻辑子树；
• 然后在主查询的逻辑树里，用专门的引用节点指向对应的CTE子计划。

这样做的好处是：

• 优化器可以灵活选择策略：如果CTE只被引用一次，内联可能更高效（可以把主计划的谓词下推到CTE子计划里）；如果被多次引用，就可以选择物化CTE的结果，避免重复计算；
• 全局优化不受影响：优化器可以把主计划和CTE子计划放在一起做等价变换，比如把主查询里的列裁剪、谓词下推等规则应用到CTE子计划中，只要符合语义约束。

关于LATERAL JOIN的突破：扩展代数节点语义，打破树形限制 #

LATERAL JOIN的核心是“后续子查询能引用前面FROM项的列”，这确实打破了传统关系代数里“子节点只能向上输出，父节点不能向下传递列”的树形结构限制。目前主流的解决思路有两种：

1. 给JOIN节点增加“外部上下文”属性 #

比如在LATERAL JOIN节点里，额外记录哪些来自左/上一级关系的列可以被右侧的子计划引用。拿你举的例子来说：

你的SQL：

SELECT *
FROM
(SELECT 1 id) A,
(
(SELECT 2) B
JOIN LATERAL (SELECT A.id) C ON TRUE
) D;

对应的逻辑计划（扩展节点版）大概是：

NATURAL JOIN
├── A (Subquery: SELECT 1 id)
└── LATERAL JOIN (D)
    ├── B (Subquery: SELECT 2)
    └── C (Subquery: SELECT A.id)
    └── [External Context: A.id]

这里的[External Context: A.id]就是额外的元数据，说明C的子计划可以访问A的列。优化器在处理时，会把A的列传递给C的执行上下文，同时可以正常做列裁剪、谓词下推等优化。

2. 用嵌套关系代数（NRA）表达依赖 #

把前面的关系作为参数传递给后面的子查询，把LATERAL子查询看成是一个“以前面关系的元组为输入、输出关系”的函数，然后再和前面的关系做连接。这种方式更贴近LATERAL JOIN的语义，不过理解起来稍微复杂一点。

更适合的中间表示选择：别局限于纯代数树 #

纯关系代数树确实是基础，但现代数据库的查询优化器已经很少只用它了，更实用的选择有两种：

• 表达式图（Expression Graph）：允许共享子表达式（完美解决CTE的复用问题），还能灵活表达循环依赖或者上下文依赖（比如LATERAL JOIN的列引用），优化器可以全局遍历整个图做等价变换；
• 带元数据的逻辑算子树：在传统代数树的基础上，给每个算子节点增加额外的元数据（比如外部列上下文、依赖关系、语义约束等），像PostgreSQL、MySQL的优化器都是用这种方式，专门的LateralJoin节点、CTERef节点就是典型例子。

这些表示方式既能保留关系代数的数学严谨性，又能兼容现代SQL的复杂语法，同时支持高效的全局查询优化。

备注：内容来源于stack exchange，提问作者Kathandrax