Bug #100637 Regression on memory allocation regarding sort_buffer_size
Submitted: 26 Aug 2020 0:17 Modified: 17 Feb 2021 11:13
Reporter: Gonzalo Arruti Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.12 OS:Linux
Assigned to: CPU Architecture:Any

[26 Aug 2020 0:17] Gonzalo Arruti
Description:
From MySQL 8.0.12 and later versions, I can see a regression on memory allocation regarding sort_buffer_size. The attached repeatable test case shows how the same operation with default parameter values generates an error on MySQL 8.0.21 and works on MySQL 8.0.11. Also, the procedure was tested on MySQL 5.7.30 and works without errors. 

How to repeat:
See attached repeatable test case showing how works on both versions.
[26 Aug 2020 0:19] Gonzalo Arruti
Test Case

Attachment: test_case_out_of_memory.txt (text/plain), 5.46 KiB.

[26 Aug 2020 1:11] MySQL Verification Team
Thank you for the bug report.
[17 Feb 2021 11:13] Erlend Dahl
Posted by developer:

[3 Sep 2020 5:34] Steinar Gunderson

This is not a bug. 8.0.11 and earlier would truncate long sort keys, leading
to silently wrong results (obviously breaking the standard). 8.0.12 and later
no longer does this, which means that your sort buffer will need to be large
enough to hold at least a few rows for the merge phase.

If you wish the 8.0.11 behavior of not taking the entire sort key into
account, you can do ORDER BY LEFT(str, 256) or similar.
[17 Feb 2021 17:46] Trey Raymond
Sensible, but to avoid impacting your customers negatively, the default value should be changed to support that feature.  The current default assumes truncation is taking place.  Doubling it should avoid most complaints.