问题现象

业务查询车辆申请单时遇到诡异问题：

• 子查询单独执行：正常返回多条数据
• 外层套一层 SELECT * FROM (子查询) c：结果直接为空
• 字段、条件、JSON 解析均无语法错误，加update_time到 SELECT 也无效

问题 SQL

-- 外层查询：结果为空
select * from (
    SELECT id, update_time
    FROM t_vehicle_apply_order
    WHERE delete_flag = 0
      AND apply_date BETWEEN '2026-03-27' AND '2026-03-30'
      AND (
            create_user_id = 940245941670387712
            OR EXISTS (
                SELECT 1 FROM JSON_TABLE(
                    approve_info,
                    '$[*]' COLUMNS (
                        roleName VARCHAR(50) PATH '$.roleName',
                        approveUserId VARCHAR(50) PATH '$.approveUserId'
                    )
                ) AS jt
                WHERE jt.roleName = 'confirmer'
                  AND jt.approveUserId = '1000000640'
            )
        )
    ORDER BY update_time DESC
) c;

根本原因

这是MySQL 5.7/8.0 官方优化规则，不是 Bug：

1. 标准 SQL 规定：子查询（派生表）的ORDER BY不保证生效，仅用于配合LIMIT筛选前 N 条
2. MySQL 优化器行为：派生表中ORDER BY无 LIMIT → 判定排序无意义 → 直接忽略排序逻辑，极端场景下会导致结果集被优化为空
3. 触发条件：子查询含ORDER BY、无LIMIT、作为FROM派生表嵌套

简单说：只排序不限条数，MySQL 认为没必要做，直接把结果 “优化没了”。

解决方案（二选一）

方案 1：子查询加超大 LIMIT（最快修复）

给子查询加一个足够大的LIMIT，让优化器保留排序与结果：

select * from (
    SELECT id
    FROM t_vehicle_apply_order
    WHERE delete_flag = 0
      AND apply_date BETWEEN '2026-03-27' AND '2026-03-30'
      AND (
            create_user_id = 940245941670387712
            OR EXISTS (
                SELECT 1 FROM JSON_TABLE(approve_info, '$[*]' COLUMNS (
                    roleName VARCHAR(50) PATH '$.roleName',
                    approveUserId VARCHAR(50) PATH '$.approveUserId'
                )) AS jt
                WHERE jt.roleName = 'confirmer' AND jt.approveUserId = '1000000640'
            )
        )
    ORDER BY update_time DESC
    LIMIT 99999999  -- 关键修复
) c;

方案 2：移到外层排序（规范写法，推荐）

删除子查询ORDER BY，在外层执行排序，语义更清晰：

SELECT id, update_time
FROM (
    SELECT id, update_time
    FROM t_vehicle_apply_order
    WHERE delete_flag = 0
      AND apply_date BETWEEN '2026-03-27' AND '2026-03-30'
      AND (
            create_user_id = 940245941670387712
            OR EXISTS (
                SELECT 1 FROM JSON_TABLE(approve_info, '$[*]' COLUMNS (
                    roleName VARCHAR(50) PATH '$.roleName',
                    approveUserId VARCHAR(50) PATH '$.approveUserId'
                )) AS jt
                WHERE jt.roleName = 'confirmer' AND jt.approveUserId = '1000000640'
            )
        )
) c
ORDER BY update_time DESC;

核心知识点总结

1. 子查询排序生效前提：必须配合LIMIT/OFFSET，否则 MySQL 会忽略
2. 派生表排序无效：FROM (SELECT ... ORDER BY)无 LIMIT，排序不生效且可能丢结果
3. 最佳实践：排序逻辑统一放在最外层查询，避免嵌套排序踩坑
4. 兼容提示：该问题仅 MySQL 特有，Oracle、PostgreSQL 无此优化行为

---

避坑指南

• 嵌套查询尽量不要在子查询写ORDER BY
• 必须排序时，子查询加LIMIT 99999999兜底
• 复杂 JSON 查询 + 排序场景，优先外层排序，减少优化器干扰