为何同一段SQL在MySQL 5.7.44与8.4.0中执行结果不同？

问题背景 #

在MySQL 5.7.44和8.4.0版本中执行以下SQL时，出现了结果不一致的情况：

测试SQL代码 #

CREATE TABLE subscriptions (
    id INT PRIMARY KEY
);
CREATE TABLE jobs (
    id INT PRIMARY KEY,
    subscription_id INT NULL,
    status VARCHAR(255),
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id)
);
INSERT INTO subscriptions VALUES (1),(2),(3);
INSERT INTO jobs (id,subscription_id,status) VALUES
(1,null,'dispatch_ok'),
(2,1,'dispatch_ok'),
(3,2,'created'),
(4,3,'created'),
(5,null,'created');
SELECT VERSION();
SELECT * FROM jobs
WHERE
    status = 'created'
    AND
    subscription_id NOT IN (
        SELECT
            subscription_id
        FROM
            jobs
        GROUP BY
            subscription_id
        HAVING
            COUNT(*) >= 1000000000
    )
ORDER BY id

执行结果对比 #

[mysql:5.7.44] [ { 'VERSION()': '5.7.44' } ]
[mysql:5.7.44] []

[mysql:8.4.0 ] [ { 'VERSION()': '8.4.0' } ]
[mysql:8.4.0 ] [
  { id: 3, subscription_id: 2, status: 'created' },
  { id: 4, subscription_id: 3, status: 'created' },
  { id: 5, subscription_id: null, status: 'created' }
]

预期子查询返回空集时，subscription_id NOT IN ()应判定为true（即使subscription_id为NULL），但MySQL 5.7.44返回空结果，添加WHERE subscription_id IS NOT NULL到子查询后问题解决，以下是背后的原因：

原因拆解 #

1. 子查询的实际返回差异 #

你的子查询逻辑是分组后筛选COUNT≥10亿的分组，显然没有任何分组满足这个条件，但两个版本的处理不同：

• MySQL 5.7.44：GROUP BY会将subscription_id为NULL的记录视为一个有效分组，即使该分组不满足HAVING条件，最终子查询返回的是包含NULL的单元素集合(NULL)，而非空集。
• MySQL 8.4.0：对分组查询做了优化，当没有任何分组满足HAVING条件时，直接返回空集，包括NULL分组也会被过滤掉。

2. NOT IN与NULL的三值逻辑冲突 #

SQL采用三值逻辑（TRUE/FALSE/UNKNOWN），NOT IN的逻辑本质是对集合中每个元素做不等于判断，再取逻辑与：

• 若子查询返回(NULL)，则subscription_id NOT IN (NULL)等价于subscription_id != NULL。
  • 无论subscription_id是NULL还是非NULL值，X != NULL的结果都是UNKNOWN（SQL中NULL与任何值比较结果都是UNKNOWN）。
  • WHERE条件仅会保留判定结果为TRUE的记录，UNKNOWN会被排除，所以5.7版本中返回空结果。

3. 添加WHERE subscription_id IS NOT NULL的作用 #

给子查询加上这个条件后，会过滤掉所有subscription_id为NULL的记录，此时没有任何分组能满足HAVING条件，子查询返回真正的空集：

• 逻辑上，"某值不在空集中"是恒为TRUE的（空集中没有任何元素，该值不可能属于它），所以无论是NULL还是非NULL的subscription_id，都会满足NOT IN ()的条件，返回预期的结果。

4. MySQL版本的逻辑变更 #

MySQL 8.0系列对分组查询的空结果处理逻辑做了调整，严格遵循"无满足条件分组则返回空集"的规则；而5.7版本中，NULL分组被特殊处理，即使不满足HAVING条件也会被保留，这是导致两个版本结果差异的核心原因。

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