MySQL Bugs: #105911: Please add a warning when max_sort

Bug #105911	Please add a warning when max_sort_length is reached.
Submitted:	15 Dec 2021 20:43	Modified:	16 Dec 2021 5:56
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.7.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

in [1], we can read that:

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

> The server uses only the first max_sort_length bytes of each value [for sorting] and ignores the rest.

I understand it makes sense to have such limit, but it would be good to make it visible when the limit is reached.  Right now, when it is, there are no warning.  Please consider generating a warning when the limit is reached.

I am not able to reproduce with 8.0 because the behavior of max_sort_length is not as documented there, see Bug#105910 for details.

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
# 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

# No warnings even if the ORDER BY is not fully honored.
mysql [localhost:5736] {msandbox} (test_jfg) > SELECT SUBSTRING(v, 1020) FROM t ORDER BY v;
+--------------------+
| SUBSTRING(v, 1020) |
+--------------------+
| y1                 |
| y2                 |
| y3                 |
| y4                 |
| y5                 |
| y6                 |
| yyyyy2             |
| yyyyy4             |
| yyyyy6             |
| yyyyy1             |
| yyyyy3             |
| yyyyy5             |
+--------------------+
12 rows in set (0.00 sec)

# I would expect a warning similar to this.
mysql [localhost:5736] {msandbox} (test_jfg) > SELECT "1a" + 2;
+----------+
| "1a" + 2 |
+----------+
|        3 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:5736] {msandbox} (test_jfg) > show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1a' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Add a warning when max_sort_length is reached.

Hello Jean-François,

Thank you for the feature request!

regards,
Umesh

Posted by developer:
 
In 8.0, this is relevant only for the old PAD SPACE collations, which have all been deprecated.

Try this in 8.0:

select * from information_schema.collations;     

+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME             | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
| armscii8_general_ci        | armscii8           |  32 | Yes        | Yes         |       1 | PAD SPACE     |
| armscii8_bin               | armscii8           |  64 |            | Yes         |       1 | PAD SPACE     |
| ascii_general_ci           | ascii              |  11 | Yes        | Yes         |       1 | PAD SPACE     |
| ascii_bin                  | ascii              |  65 |            | Yes         |       1 | PAD SPACE     |
| big5_chinese_ci            | big5               |   1 | Yes        | Yes         |       1 | PAD SPACE     |
| big5_bin                   | big5               |  84 |            | Yes         |       1 | PAD SPACE     |
| binary                     | binary             |  63 | Yes        | Yes         |       1 | NO PAD        |

...

| latin1_swedish_ci          | latin1             |   8 | Yes        | Yes         |       1 | PAD SPACE     |

...

| utf8mb4_0900_ai_ci         | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |

...

Posted by developer:
 
In MySQL 8.0:

mysql> show variables like "character%";
+--------------------------+---------------------------------------------+
| Variable_name            | Value                                       |
+--------------------------+---------------------------------------------+
| character_set_client     | utf8mb4                                     |
| character_set_connection | utf8mb4                                     |
| character_set_database   | utf8mb4                                     |
| character_set_filesystem | binary                                      |
| character_set_results    | utf8mb4                                     |
| character_set_server     | utf8mb4                                     |
| character_set_system     | utf8mb3                                     |

In 5.7:
mysql> show variables like "character%";
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | utf8                                           |
| character_set_connection | utf8                                           |
| character_set_database   | latin1                                         |
| character_set_filesystem | binary                                         |
| character_set_results    | utf8                                           |
| character_set_server     | latin1                                         |
| character_set_system     | utf8                                           |