| Bug #111987 | Error filesort result when max_length_for_sort_data is set to a big value | ||
|---|---|---|---|
| Submitted: | 7 Aug 2023 11:26 | Modified: | 8 Aug 2023 1:08 |
| Reporter: | Zeng Zihao | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.42 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Aug 2023 13:11]
MySQL Verification Team
Hi Mr. Zihao, Thank you for your bug report. However, we are unable to repeat your test case with 8.0. That variable is deprecated since 8.0.20 and is not used any more. Since 5.7 will be maintained only for the short time, it will get only the patches for the hard error. Since this variable is deprecated, this bug will not be fixed in 5.7. Please, start planning for the upgrade to 8.0. Not a bug.
[8 Aug 2023 1:08]
Zeng Zihao
This bug is related to the filesort algorithm. This SQL do not use the filesort in MySQL 8.0. Therefore, it does not have the bug. +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 6775 | 100.00 | NULL | | 2 | DERIVED | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6775 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ In MySQL 5.7 +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 6775 | 100.00 | NULL | | 2 | DERIVED | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6775 | 100.00 | Using temporary; Using filesort | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
[8 Aug 2023 12:25]
MySQL Verification Team
Hi Mr. Zihao, Sorting is also using in some operations with temporary tables. Anyway, that variable is now deprecated.
Description: When I execute an SQL statement and set max_length_for_sort_data variable to 16KB, I got an error result in MySQL 5.7.42 and the newest version. How to repeat: create database db01; use db01; CREATE TABLE t1 ( a varchar(50), b varchar(50), c decimal(16,0), primary key (a) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; drop procedure insertNumbers; set max_length_for_sort_data = 16 * 1024; -- insert test data DELIMITER // CREATE PROCEDURE insertNumbers() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 7133 DO INSERT INTO t1 VALUES (i,i,i); SET i = i + 1; END WHILE; END;// DELIMITER ; call insertNumbers; -- the correct result should be 7133 select count(*) from (SELECT a, b, c, SUM(c) AS '2017-03', SUM(c) AS '2017-04', SUM(c) AS '2017-05', SUM(c) AS '2017-06', SUM(c) AS '2017-07', SUM(c) AS '2017-08', SUM(c) AS '2017-09', SUM(c) AS '2017-10', SUM(c) AS '2017-11', SUM(c) AS '2017-12', SUM(c) AS '2018-01', SUM(c) AS '2018-02', SUM(c) AS '2018-03', SUM(c) AS '2018-04', SUM(c) AS '2018-05', SUM(c) AS '2018-06', SUM(c) AS '2018-07', SUM(c) AS '2018-08', SUM(c) AS '2018-09', SUM(c) AS '2018-10', SUM(c) AS '2018-11', SUM(c) AS '2018-12', SUM(c) AS '2019-01', SUM(c) AS '2019-02', SUM(c) AS '2019-03', SUM(c) AS '2019-04', SUM(c) AS '2019-05', SUM(c) AS '2019-06', SUM(c) AS '2019-07', SUM(c) AS '2019-08', SUM(c) AS '2019-09', SUM(c) AS '2019-10', SUM(c) AS '2019-11', SUM(c) AS '2019-12', SUM(c) AS '2020-01', SUM(c) AS '2020-02', SUM(c) AS '2020-03', SUM(c) AS '2020-04', SUM(c) AS '2020-05', SUM(c) AS '2020-06', SUM(c) AS '2020-07', SUM(c) AS '2020-08', SUM(c) AS '2020-09', SUM(c) AS '2020-10', SUM(c) AS '2020-11', SUM(c) AS '2020-12', SUM(c) AS '2021-01', SUM(c) AS '2021-02', SUM(c) AS '2021-03', SUM(c) AS '2021-04', SUM(c) AS '2021-05', SUM(c) AS '2021-06', SUM(c) AS '2021-07', SUM(c) AS '2021-08', SUM(c) AS '2021-09', SUM(c) AS '2021-10', SUM(c) AS '2021-11', SUM(c) AS '2021-12', SUM(c) AS '2022-01', SUM(c) AS '2022-02', SUM(c) AS '2022-03', SUM(c) AS '2022-04', SUM(c) AS '2022-05', SUM(c) AS '2022-06', SUM(c) AS '2022-07', SUM(c) AS '2022-08', SUM(c) AS '2022-09', SUM(c) AS '2022-10', SUM(c) AS '2022-11', SUM(c) AS '2022-12', SUM(c) AS '2023-01', SUM(c) AS '2023-02', SUM(c) AS '2023-03', SUM(c) AS '2023-04', SUM(c) AS '2023-05', SUM(c) AS '2023-06', SUM(c) AS '2023-07', SUM(c) AS '2023-08', SUM(c) AS '2023-09', SUM(c) AS '2023-10', SUM(c) AS '2023-11', SUM(c) AS '2023-12', SUM(c) AS '2024-01', SUM(c) AS '2024-02', SUM(c) AS '2024-03', SUM(c) AS '2024-04', SUM(c) AS '2024-05', SUM(c) AS '2024-06', SUM(c) AS '2024-07', SUM(c) AS '2024-08', SUM(c) AS '2024-09', SUM(c) AS '2024-10', SUM(c) AS '2024-11', SUM(c) AS '2024-12', SUM(c) AS '2025-01', SUM(c) AS '2025-02', SUM(c) AS '2025-03', SUM(c) AS '2025-04', SUM(c) AS '2025-05', SUM(c) AS '2025-06', SUM(c) AS '2025-07', SUM(c) AS '2025-08', SUM(c) AS '2025-09', SUM(c) AS '2025-10', SUM(c) AS '2025-11', SUM(c) AS '2025-12', SUM(c) AS '2026-01', SUM(c) AS '2026-02', SUM(c) AS '2026-03', SUM(c) AS '2026-04', SUM(c) AS '2026-05', SUM(c) AS '2026-06', SUM(c) AS '2026-07', SUM(c) AS '2026-08', SUM(c) AS '2026-09', SUM(c) AS '2026-10', SUM(c) AS '2026-11', SUM(c) AS '2026-12' -- if we have the next row, the error result will be 6301 which is error. -- ,SUM(c) AS '2027-01' FROM t1 GROUP BY a, b, c) a;