为什么CTE不会仅被评估一次？不同数据库CTE实现差异

先看一段测试SQL：

WITH tbl AS (
    SELECT RAND() AS a
) SELECT * FROM tbl AS tbl1, tbl AS tbl2

在部分数据库中执行这段语句，会返回两个不同的随机值，和“CTE仅在查询启动时评估一次、后续引用直接复用结果”的直觉认知完全不符；但在MySQL中执行相同语句，会返回两个完全一致的数值。
这个差异本质是不同数据库对CTE的实现逻辑不统一导致的。

SQL标准没有对CTE的执行模型做强制约束，目前主流数据库的CTE实现分为两类，行为差异极大：

• 物化执行模型：CTE在首次被引用时完整执行内部逻辑，计算结果写入临时存储，后续所有对该CTE的引用都直接读取临时存储的结果，不会重复执行CTE内的语句。MySQL 8.0及以上版本的非递归CTE默认采用该模型，因此两次引用tbl时，RAND()只会被调用一次，返回两个相同的值。
• 内联展开模型：查询优化器会将CTE等价为宏定义，在执行计划生成阶段把CTE的逻辑直接替换到每一个引用位置，不会做结果缓存。PostgreSQL、默认配置下的SQL Server、Oracle等多数数据库默认采用该优化逻辑，上述测试SQL会被等价改写为：

  SELECT * 
  FROM (SELECT RAND() AS a) AS tbl1,
       (SELECT RAND() AS a) AS tbl2
  

  两个子查询中的RAND()是完全独立的两次调用，自然会返回不同的随机值。

• 不要依赖数据库默认的CTE物化行为编写业务逻辑。如果明确要求CTE只计算一次，优先使用对应数据库提供的物化提示（比如PostgreSQL 12+支持的MATERIALIZED关键字、SQL Server对应的CTE物化hint），或者主动将CTE结果写入临时表后再做关联查询，避免跨数据库、跨版本行为不一致引发逻辑错误。
• 如果业务场景不需要复用CTE结果（比如大结果集CTE仅被引用一次、CTE逻辑非常简单），可以使用数据库提供的非物化hint跳过临时表写入步骤，减少IO开销提升查询性能。

内容的提问来源于stack exchange，提问作者David542