MySQL行构造器查询含GROUP BY子查询时返回错误空结果问题

问题场景重现 #

使用MySQL行构造器语法时，当子查询包含GROUP BY子句，主查询会返回空结果；注释掉GROUP BY后查询正常，两者仅存在这一处差异。

返回正确结果的SQL #

SELECT a.ChannelId, a.GoodsId FROM t_test a
WHERE 1=1 
AND (a.ChannelId, a.GoodsId) NOT IN (
 SELECT ChannelId, GoodsId FROM (SELECT  ChannelId, GoodsId FROM t_test 
 WHERE (IFNULL(twoNotQuantity, 0) + IFNULL(twoCheckQuantity, 0)) > 0 
-- GROUP BY ChannelId, GoodsId
) b
);

返回空结果的SQL #

SELECT a.ChannelId, a.GoodsId FROM t_test a
WHERE 1=1 
AND (a.ChannelId, a.GoodsId) NOT IN (
 SELECT ChannelId, GoodsId FROM (SELECT  ChannelId, GoodsId FROM t_test 
 WHERE (IFNULL(twoNotQuantity, 0) + IFNULL(twoCheckQuantity, 0)) > 0 
GROUP BY ChannelId, GoodsId
) b
);

测试用表结构及数据：

CREATE TABLE `t_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `ChannelId` bigint DEFAULT NULL,
  `GoodsId` bigint DEFAULT NULL,
  `twoNotQuantity` decimal(20,4) DEFAULT NULL,
  `twoCheckQuantity` decimal(20,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1894 DEFAULT CHARSET=utf8mb3;

INSERT INTO demo.t_test (ChannelId,GoodsId,twoNotQuantity,twoCheckQuantity) VALUES
     (2129504377563648,2129456810682880,NULL,98.0000),
     (2129504377563648,2089744751847936,NULL,98.0000),
     (2129504377563648,2067916860058112,NULL,NULL);

原因分析 #

核心问题出在NOT IN与行构造器的组合逻辑，以及MySQL优化器对GROUP BY子查询的处理：

1. NOT IN的NULL敏感性：行构造器的NOT IN遵循SQL标准逻辑：只要子查询中有任意一行与主查询行的比较结果为UNKNOWN（通常因NULL参与比较导致），整个NOT IN条件会返回UNKNOWN，导致WHERE条件不成立，主查询无结果。
2. GROUP BY的优化器行为：即使子查询的GROUP BY结果表面无NULL，部分MySQL版本的优化器在处理GROUP BY子查询时，会将其转换为聚合临时表，过程中可能引入隐式NULL处理或行比较逻辑异常，导致原本应匹配的行被错误判定为不匹配，最终主查询返回空。
3. 不带GROUP BY的子查询返回重复行，行构造器NOT IN对重复行的处理逻辑与GROUP BY后的去重行不同，优化器执行路径的差异直接导致了结果差异。

行构造器语法的规避限制 #

1. 优先用NOT EXISTS替代NOT IN：NOT EXISTS是逐行匹配逻辑，不会因NULL或聚合操作导致整个条件失效，改写后的SQL如下：

SELECT a.ChannelId, a.GoodsId 
FROM t_test a
WHERE 1=1 
AND NOT EXISTS (
    SELECT 1 
    FROM t_test b
    WHERE b.ChannelId = a.ChannelId 
      AND b.GoodsId = a.GoodsId
      AND (IFNULL(b.twoNotQuantity, 0) + IFNULL(b.twoCheckQuantity, 0)) > 0
    GROUP BY b.ChannelId, b.GoodsId
);

2. 若必须用NOT IN，用DISTINCT替代GROUP BY去重：如果仅需去重，DISTINCT比GROUP BY更直接，可避免优化器的异常处理：

SELECT a.ChannelId, a.GoodsId 
FROM t_test a
WHERE 1=1 
AND (a.ChannelId, a.GoodsId) NOT IN (
    SELECT DISTINCT ChannelId, GoodsId 
    FROM t_test 
    WHERE (IFNULL(twoNotQuantity, 0) + IFNULL(twoCheckQuantity, 0)) > 0
);

3. 确保子查询结果无NULL：行构造器的每一列都不能为NULL，否则NOT IN会直接失效，必要时用IFNULL或COALESCE显式处理NULL值。
4. 注意版本差异：MySQL 5.7早期版本在行构造器与GROUP BY、NOT IN的组合上存在优化bug，建议升级到8.0及以上稳定版本。
5. 简化嵌套子查询：尽量将多层嵌套子查询改写为JOIN或CTE（MySQL 8.0+支持），提升可读性和优化器执行效率。

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