Bug #114114 Filesort does not compare VARCHAR column past 341 matching characters

• Submitted: 26 Feb 5:56
• Modified: 27 Feb 22:29
• Reporter: Jacob Lucke
• Status: Verified
• Category: MySQL Server: Documentation
• Severity: S2 (Serious)
• Version: 8.0.36
• OS: Any
• CPU Architecture: Any

[26 Feb 5:56] Jacob Lucke

Description:
ORDER BY does not return correct results on a table with long VARCHAR column where the first 341 characters match for every row.

For the reproduction attached to this bug report, ORDER BY col2 ASC and ORDER BY col2 DESC return the same results:

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

mysql> select * from incorrectFilesort ORDER BY col2 ASC;
+------+---...----+
| sort | col2     |
+------+---...----+
|    1 | aa...aa7 |
|    2 | aa...aa6 |
|    3 | aa...aa5 |
|    4 | aa...aa4 |
|    5 | aa...aa3 |
|    6 | aa...aa2 |
|    7 | aa...aa1 |
+------+---...----+
7 rows in set (0.00 sec)

mysql> select * from incorrectFilesort ORDER BY col2 DESC;
+------+---...----+
| sort | col2     |
+------+---...----+
|    1 | aa...aa7 |
|    2 | aa...aa6 |
|    3 | aa...aa5 |
|    4 | aa...aa4 |
|    5 | aa...aa3 |
|    6 | aa...aa2 |
|    7 | aa...aa1 |
+------+---...----+
7 rows in set (0.00 sec)

How to repeat:
CREATE TABLE IF NOT EXISTS `incorrectFilesort` (
  `col1` SMALLINT NOT NULL,
  `col2` VARCHAR(1000) NOT NULL,
  PRIMARY KEY (`col1`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;

insert into incorrectFilesort
values (
  1,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa7'
), (
  2,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa6'
), (
  3,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa5'
), (
  4,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa4'
), (
  5,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa3'
), (
  6,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2'
), (
  7,
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'
);

[26 Feb 6:45] MySQL Verification Team

Hello Jacob,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[26 Feb 19:17] Knut Anders Hatlen

Hi Jacob,

Could it be that you are hitting the limitation documented here:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_sort_lengt...
?

Possible workarounds are increasing max_sort_length, or using a NO PAD collation such as utf8mb4_0900_bin instead of utf8mb4_bin.

[27 Feb 22:29] Jacob Lucke

Hi Knut,

Thanks for your prompt response. Increasing max_sort_length or using a NO PAD collation produces the expected results.

Can the documentation you linked be updated to mention window functions? Since this limitation exists in filesort, it is not clear from the documentation to change max_sort_length besides when using GROUP BY, ORDER BY, or DISTINCT.

E.g. if I insert 342 character long strings in a pattern ending with 1 2 1 2 1 2, then ROW_NUMBER will not properly count the rows since they do not get sorted due to the max_sort_length limitation:

mysql> select col1, right(col2,3), row_number() over(partition by col2) count from incorrectWindow;
+------+---------------+-------+
| col1 | right(col2,3) | count |
+------+---------------+-------+
|    1 | aa1           |     1 |
|    2 | aa2           |     1 |
|    3 | aa1           |     1 |
|    4 | aa2           |     1 |
|    5 | aa1           |     1 |
|    6 | aa2           |     1 |
+------+---------------+-------+
6 rows in set (0.00 sec)

mysql> set session max_sort_length=@@max_sort_length*2;
Query OK, 0 rows affected (0.00 sec)

mysql> select col1, right(col2,3), row_number() over(partition by col2) count from incorrectWindow;
+------+---------------+-------+
| col1 | right(col2,3) | count |
+------+---------------+-------+
|    1 | aa1           |     1 |
|    3 | aa1           |     2 |
|    5 | aa1           |     3 |
|    2 | aa2           |     1 |
|    4 | aa2           |     2 |
|    6 | aa2           |     3 |
+------+---------------+-------+
6 rows in set (0.00 sec)

[29 Feb 8:51] Knut Anders Hatlen

Changing bug category to documentation, so that the docs team can take a look at it.
I agree that it sounds reasonable to mention in the documentation of the max_sort_length system variable that it also affects WINDOW operations.