为何看似相同的MySQL子查询在WHERE子句中表现不同？

我有两个逻辑看似一致的MySQL查询，但实际运行结果不同：

查询1（始终返回空结果） #

SELECT * FROM code
WHERE id = (
                SELECT id FROM code
                WHERE available = 1
                ORDER BY rand()
                LIMIT 1
    )
FOR UPDATE SKIP LOCKED;

查询2（可返回available = 1的随机行） #

SELECT * FROM code
WHERE id = (
        SELECT rc.id FROM
            (
                SELECT id FROM code
                WHERE available = 1
                ORDER BY rand()
                LIMIT 1
            ) as rc
    )
FOR UPDATE SKIP LOCKED;

子查询单独测试结果 #

单独运行两个子查询时，均能返回available = 1的随机行：

子查询1（查询2的内部子查询）

SELECT rc.id FROM
(
  SELECT id FROM code
  WHERE available = 1
  ORDER BY rand()
  LIMIT 1
) as rc;

-- 返回结果示例：
--       id
-- 1 <random_id>

子查询2（查询1的子查询）

SELECT id FROM code
WHERE available = 1
ORDER BY rand()
LIMIT 1;

-- 返回结果示例：
--       id
-- 1 <random_id>

表结构定义 #

CREATE TABLE `code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `available` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
);

我尝试运行这两个查询，查询1返回空结果，查询2则符合预期。请问两者结果不同的原因是什么？

两者结果不同的核心在于MySQL对子查询的处理机制差异：

• 查询1：子查询未被物化，执行逻辑被合并
  未用派生表包裹的子查询，会被MySQL优化器直接合并到主查询中执行。由于rand()函数每次调用都会生成新的随机值，这个子查询可能被多次执行：第一次生成一个随机id，主查询验证条件时又会再次执行子查询生成新的随机id，两次id不一致导致主查询找不到匹配行，最终返回空。
  再加上FOR UPDATE SKIP LOCKED的锁定逻辑，合并后的查询会让锁定操作与随机选id的逻辑相互干扰，进一步放大了结果不匹配的概率。

• 查询2：子查询被物化，结果固定
  用派生表rc包裹子查询后，MySQL会先执行内部子查询，将结果存入临时物化表（这个过程仅执行一次，rand()只生成一次随机值，得到固定的id）。之后主查询直接用物化表中的id匹配code表行，并应用FOR UPDATE SKIP LOCKED逻辑，因此能稳定返回符合条件的随机行。

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