MySQL中CTE的GROUP BY、CASE WHEN与WHERE冲突问题解析

简化后的CTE查询语句 #

WITH cte1 AS 
(
    SELECT 
    id,
    ( (price * quantity) + ((gst * quantity) + (gst2 * quantity))
    ) gross_revenue 
    FROM orders
    GROUP BY id
),

cte2 AS (
    SELECT 
    id,
    ROUND(gross_revenue, 2) gross_revenue,
    ROUND(gross_revenue, 2) gross_profit
    FROM cte1
),

cte3 AS (
    SELECT id, gross_revenue, 
        CASE 
           WHEN gross_profit < 0
            THEN 0
           ELSE 
            gross_profit
        END gross_profit
    FROM cte2
)
    
SELECT 
    id, gross_revenue, gross_profit
FROM cte3 

WHERE gross_profit >= 22.27 

LIMIT 25

报错情况 #

在MySQL 8.0.27版本中执行上述查询时，会触发以下错误：

Error Code: 1054
Unknown column 'cte1.gross_revenue' in 'where clause'

但执行以下任一操作后，查询可正常运行：

• 移除cte1中的GROUP BY
• 移除cte3中的CASE WHEN逻辑
• 将最终查询的WHERE替换为HAVING
• 移除WHERE中gross_profit >= 22.27的条件
• 将CASE WHEN从cte3移至最终的SELECT语句

同时，该查询在MySQL 8.0.31及以上版本中可正常执行。

问题解析 #

1. 为何使用HAVING可行而WHERE不行？ #

MySQL的查询执行逻辑中，WHERE是在行数据获取阶段过滤，优化器需要提前解析过滤条件中列的来源；而HAVING是在SELECT生成计算列之后对结果集过滤。
8.0.27版本的优化器存在bug，错误地将WHERE子句中的gross_profit关联到cte1的gross_revenue列。改用HAVING后，过滤直接作用于cte3已生成的gross_profit列，优化器无需溯源列的依赖关系，因此不会触发错误。

2. 为何将CASE WHEN移至最终SELECT语句可正常运行？ #

当CASE WHEN移到最终SELECT时，gross_profit是直接基于cte2的列计算生成的，列的依赖关系清晰明确。
而之前的链式CTE定义中，8.0.27的优化器存在列依赖解析bug，错误地将cte3中CASE WHEN里的gross_profit溯源到cte1的gross_revenue，导致解析错误。移到最终SELECT后，优化器能正确识别列的来源，避免了错误关联。

3. 为何cte1的GROUP BY会影响最终SELECT语句？ #

GROUP BY会让cte1的结果集变成聚合后的行，MySQL优化器在处理带聚合操作的CTE时，内部的列依赖跟踪逻辑出现异常，保留了错误的列关联标记。
移除GROUP BY后，cte1只是简单的行投影，优化器的列依赖跟踪逻辑恢复正常，不会触发这个bug。

4. 为何移除WHERE中gross_profit条件后查询正常？ #

这个bug的触发前提是优化器需要解析WHERE子句中gross_profit的过滤逻辑。移除该条件后，优化器无需处理这个列的依赖解析，自然不会触发错误的列关联逻辑，查询就能正常执行。

示例数据 #

id    price     quantity    gst     gst2 
1    90.0000     1.00        0.0000  0.0000
2    10.0000     1.00        0.0000  0.0000
3    30.0000     1.00        0.0000  0.0000
4    70.0000     100.00      0.0000  0.0000
5    500.0000    1.00        0.0000  0.0000
6    20.9100     20.00       2.0900  0.0000
7    100.0000    1.00        0.0000  0.0000
8    100.0000    -1.00       0.0000  0.0000
9    20.9100     -20.00      2.0000  0.0000
10   9.7100      1.00        0.0000  0.0000
11   80.0000     1.00        0.0000  0.0000
12   1100.0000   1.00        0.0000  0.0000
13   870.0000    1.00        0.0000  0.0000
14   947.0000    1.00        0.0000  0.0000
15   10.0000     1.00        1.0000  0.0000
16   200.0000    1.00        0.0000  0.0000
17   300.0000    1.00        0.0000  0.0000
18   600.0000    1.00        0.0000  0.0000
19   350.0000    1.00        0.0000  0.0000
20   10.0000     1.00        0.8000  0.0000
21   50.0000     1.00        0.0000  0.0000
22   60.0000     1.00        0.0000  0.0000
23   150.0000    1.00        0.0000  0.0000
24   20.0000     1.00        2.0000  0.0000
25   29.9500     1.00        3.0000  0.0000

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