MySQL中Having搭配OrderBy返回行数异常减少问题排查

问题描述 #

执行以下带ORDER BY的查询时，仅返回2行数据：

select 
    products.id,
    products.description,
    products.barcode,
    getStock(products.id, 'Production') AS total_stock   
from `products`
having total_stock > 0
order by products.description asc

但移除ORDER BY子句后，查询能正常返回4000行数据：

select 
    products.id,
    products.description,
    products.barcode,
    getStock(products.id, 'Production') AS total_stock   
from `products`
having total_stock > 0 

尝试添加GROUP BY子句后，问题依然存在：

select 
    products.id,
    products.description,
    products.barcode,
    getStock(products.id, 'Production') AS total_stock   
from `products`
group by products.id, products.description, products.barcode 
having total_stock > 0 
order by products.description asc

补充自定义函数getStock代码：

DELIMITER $$
CREATE FUNCTION getStock (productId INT, environment VARCHAR(255)) RETURNS INT(11)
BEGIN                  
    DECLARE qty INT(11) DEFAULT 0;
    
    SELECT SUM(pw.stock) INTO qty
        FROM products_wholesalers AS pw
        INNER JOIN subscriptions AS subscription ON subscription.id = pw.subscription_id
        WHERE pw.product_id = productId 
            AND pw.active = true
            AND subscription.type = 'Wholesaler'
            AND subscription.active = true
            AND subscription.environment = environment;
    
    RETURN qty;
END $$

问题根源 #

核心问题是自定义函数的参数名与表列名重名，导致函数逻辑完全偏离预期：

• 函数参数environment与subscriptions表的列名subscription.environment完全一致，MySQL解析WHERE subscription.environment = environment时，会优先将environment识别为表列名，而非传入的函数参数。
• 这使得该条件等价于subscription.environment = subscription.environment，即只要subscription.environment不为NULL就会成立，函数根本没有按照传入的'Production'环境参数过滤数据。
• 不同执行计划（是否带ORDER BY）下，MySQL对函数调用的处理存在差异，导致返回结果行数出现巨大波动：不带ORDER BY时，函数错误返回了符合其他条件的所有库存数据，得到4000行；带ORDER BY时，优化器执行计划变化导致函数返回结果大幅减少，仅得到2行。

解决方法 #

修改函数参数名，避免与列名冲突，同时调整WHERE条件中的参数引用：

DELIMITER $$
CREATE FUNCTION getStock (productId INT, p_environment VARCHAR(255)) RETURNS INT(11)
BEGIN                  
    DECLARE qty INT(11) DEFAULT 0;
    
    SELECT SUM(pw.stock) INTO qty
        FROM products_wholesalers AS pw
        INNER JOIN subscriptions AS subscription ON subscription.id = pw.subscription_id
        WHERE pw.product_id = productId 
            AND pw.active = true
            AND subscription.type = 'Wholesaler'
            AND subscription.active = true
            AND subscription.environment = p_environment;
    
    RETURN qty;
END $$

重新执行原查询后，即可得到符合预期的结果。

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