Bug #105910 Change in behavior of max_sort_length between 5.7.36 and 8.0.27.
Submitted: 15 Dec 2021 20:42 Modified: 22 Dec 2021 19:11
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any

[15 Dec 2021 20:42] Jean-François Gagné 
Description:
Hi,

while understanding how max_sort_length [1] works, I observed that it is behaving differently in 5.7.36 and 8.0.27.  I do not know if this is a documentation bug in [1] or a regression in 8.0.  See How to repeat for details.

[1]: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_sort_lengt...

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
####################################################
# max_sort_length behavior is as expected in 5.7.36.

# Create a sandbox.
dbdeployer deploy single mysql_5.7.36

# max_sort_length is 1024 by default in 5.7.36.
./use -N <<< "show global variables like 'max_sort_length'"
max_sort_length 1024

# Initialize the sandbox.
./use <<< "
  CREATE DATABASE test_jfg;
  CREATE TABLE test_jfg.t (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    v VARCHAR(5000))"

# Add rows on both sides of the limit.
for i in 1020 1024; do
  for j in 2 4 6 1 3 5; do ./use test_jfg <<< "INSERT INTO t(v) VALUES('$(yes | head -n $i | paste -s -d "")$j')"; done
done

# In below, the values of v that are longer than 1024 characters are not sorted.
./use -N test_jfg <<< "SELECT SUBSTRING(v, 1020) FROM t ORDER BY v"
y1
y2
y3
y4
y5
y6
yyyyy2
yyyyy4
yyyyy6
yyyyy1
yyyyy3
yyyyy5

################################################################################################################
# But when running the same commands in 8.0.27, we get a sorted result while I was expecting an unsorted result.

dbdeployer deploy single mysql_8.0.27

./use -N <<< "show global variables like 'max_sort_length'"
max_sort_length 1024

./use <<< "
  CREATE DATABASE test_jfg;
  CREATE TABLE test_jfg.t (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    v VARCHAR(5000))"

for i in 1020 1024; do
  for j in 2 4 6 1 3 5; do ./use test_jfg <<< "INSERT INTO t(v) VALUES('$(yes | head -n $i | paste -s -d "")$j')"; done
done

./use -N test_jfg <<< "SELECT SUBSTRING(v, 1020) FROM t ORDER BY v"
y1
y2
y3
y4
y5
y6
yyyyy1
yyyyy2
yyyyy3
yyyyy4
yyyyy5
yyyyy6

Suggested fix:
Update the documentation if the behavior changed from 5.7 to 8.0, of fix 8.0 to match the documentation.
[15 Dec 2021 20:44] Jean-François Gagné 
Full context of this: I was trying to report Bug#105911 with 8.0.27, and this is how I saw this problem.
[16 Dec 2021 6:06] MySQL Verification Team 
Hello Jean-François,

Thank you for the report and feedback.

regards,
Umesh
[16 Dec 2021 9:33] Tor Didriksen 
Posted by developer:
 
Note the new documentation:
    "max_sort_length",
    "The number of bytes to use when sorting long values with PAD SPACE "
    "collations (only the first max_sort_length bytes of each value are "
    "used; the rest are ignored)",

So in 8.0 it depends on the character set / collation you are using.
[22 Dec 2021 19:11] Jon Stephens 
Fixed in mysqldoc rev 71512.

Closed.