MySQL8.0版本发现一个bug??

Question

我目前使用的是MySQL8.0.12。目前有一张表里有300多万的数据，然后我的查询需求是这样的，查询某个省份的数据，然后使用MySQL8.0提供的窗口函数对查询出来的数据通过某个字段分组。
发现的具体问题如下：
下面我是用一个简单的SQL查询，主要想证明目前的条件总共查询出来的数据只有26条

 SELECT
       p.*
    FROM colg_adn_school_admission_plan p
    WHERE p.school_code IN ('10005') AND p.province_id = 12 AND p.candidate_type = 0 AND p.batch_type = 4

结果如下

因为我的需求是先过滤出满足要求的数据，然后再使用窗口函数分组，因此使用MySQL8.0新特性，具体SQL语句如下：

WITH FilteredData AS (
    SELECT
       p.*
    FROM colg_adn_school_admission_plan p
    WHERE p.school_code IN ('10005') AND p.province_id = 12 AND p.candidate_type = 0 AND p.batch_type = 4
)
SELECT
   *
    ,ROW_NUMBER() OVER (PARTITION BY fd.major_inner_code ORDER BY fd.particular_year DESC) AS rn
FROM FilteredData fd;

奇怪的问题出现了，查询结果居然把表里300多万的数据全部查出来的？？？？？？？？？？？？？？

Answer 1

没有复现。

数据和建表语句来自 ChatGPT 创建。（另外，我本地的 8.0.30 版本也没有复现。）

drop table if exists colg_adn_school_admission_plan;

CREATE TABLE colg_adn_school_admission_plan (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,         -- 假设主键
    school_code VARCHAR(20) NOT NULL,             -- 学校代码
    province_id INT NOT NULL,                     -- 省份 ID
    candidate_type INT NOT NULL,                  -- 考生类型
    batch_type INT NOT NULL,                      -- 批次类型
    major_inner_code VARCHAR(50) NOT NULL,        -- 专业内部编码
    particular_year INT NOT NULL,                 -- 年份
    plan_count INT,                               -- 计划人数
    tuition_fee DECIMAL(10,2),                    -- 学费
    remark VARCHAR(255),                          -- 备注
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- 索引设计
    INDEX idx_school_province (school_code, province_id),
    INDEX idx_major_year (major_inner_code, particular_year),
    INDEX idx_candidate_batch (candidate_type, batch_type)
);
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10005', 12, 0, 4, 'CS2025', 2025, 100, 5000.00, '计算机科学'),
('10005', 12, 0, 4, 'ME2024', 2024, 80, 4800.00, '机械工程'),
('10005', 12, 0, 4, 'EE2023', 2023, 120, 5200.00, '电子工程'),
('10005', 12, 0, 4, 'CS2023', 2023, 90, 4900.00, '计算机科学'),
('10005', 12, 0, 4, 'ME2022', 2022, 70, 4600.00, '机械工程');
-- 符合查询条件的数据
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10005', 12, 0, 4, 'CS2025', 2025, 100, 5000.00, '计算机科学'),
('10005', 12, 0, 4, 'CS2024', 2024, 120, 5100.00, '计算机科学'),
('10005', 12, 0, 4, 'ME2023', 2023, 80, 4800.00, '机械工程'),
('10005', 12, 0, 4, 'EE2022', 2022, 90, 4600.00, '电子工程'),
('10005', 12, 0, 4, 'CS2022', 2022, 70, 4500.00, '计算机科学');

-- 不符合查询条件的数据
-- 不同的学校代码
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10006', 12, 0, 4, 'CS2025', 2025, 110, 5200.00, '计算机科学'),  -- school_code 不符合
('10007', 12, 0, 4, 'ME2024', 2024, 90, 4900.00, '机械工程'),      -- school_code 不符合
('10008', 12, 0, 4, 'EE2023', 2023, 75, 4700.00, '电子工程');      -- school_code 不符合

-- 不同的省份 ID
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10005', 13, 0, 4, 'CS2025', 2025, 105, 5300.00, '计算机科学'),   -- province_id 不符合
('10005', 11, 0, 4, 'ME2024', 2024, 95, 5000.00, '机械工程');      -- province_id 不符合

-- 不同的考生类型
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10005', 12, 1, 4, 'CS2025', 2025, 110, 5400.00, '计算机科学'),   -- candidate_type 不符合
('10005', 12, 2, 4, 'ME2024', 2024, 85, 5100.00, '机械工程');      -- candidate_type 不符合

-- 不同的批次类型
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10005', 12, 0, 3, 'CS2025', 2025, 120, 5500.00, '计算机科学'),   -- batch_type 不符合
('10005', 12, 0, 2, 'ME2024', 2024, 100, 5200.00, '机械工程');      -- batch_type 不符合

-- 不同的年份
INSERT INTO colg_adn_school_admission_plan (school_code, province_id, candidate_type, batch_type, major_inner_code, particular_year, plan_count, tuition_fee, remark)
VALUES
('10005', 12, 0, 4, 'CS2021', 2021, 60, 4000.00, '计算机科学'),     -- 年份不符合
('10005', 12, 0, 4, 'ME2020', 2020, 50, 3900.00, '机械工程');      -- 年份不符合

查询语句，只是包成了一个 count 查询。

select count(*)
from (WITH FilteredData AS (
    SELECT
       p.*
    FROM colg_adn_school_admission_plan p
    WHERE p.school_code IN ('10005') AND p.province_id = 12 AND p.candidate_type = 0 AND p.batch_type = 4
)
SELECT
   *
    ,ROW_NUMBER() OVER (PARTITION BY fd.major_inner_code ORDER BY fd.particular_year DESC) AS rn
FROM FilteredData fd) as t2;


select count(*)
from ( SELECT
       p.*
    FROM colg_adn_school_admission_plan p
    WHERE p.school_code IN ('10005') AND p.province_id = 12 AND p.candidate_type = 0 AND p.batch_type = 4) as t;


select version();

Answer 2

SELECT 
    p.*,
    ROW_NUMBER() OVER (PARTITION BY p.major_inner_code ORDER BY p.particular_year DESC) AS rn
FROM colg_adn_school_admission_plan p
WHERE p.school_code IN ('10005') AND p.province_id = 12 AND p.candidate_type = 0 AND p.batch_type = 4;

或者：

WITH FilteredData AS (
    SELECT
       p.id, p.school_code, p.province_id, p.candidate_type, p.batch_type, p.major_inner_code, p.particular_year
       -- 列出所有需要的列，而不是使用 p.*
    FROM colg_adn_school_admission_plan p
    WHERE p.school_code IN ('10005') AND p.province_id = 12 AND p.candidate_type = 0 AND p.batch_type = 4
)
SELECT
   *,
   ROW_NUMBER() OVER (PARTITION BY fd.major_inner_code ORDER BY fd.particular_year DESC) AS rn
FROM FilteredData fd;