Bug #89650 Incorrect ordering with memory tables and long keys
Submitted: 13 Feb 2018 13:55 Modified: 14 Feb 2018 6:41
Reporter: Zsolt Parragi (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7, 8.0, 5.6.39, 5.7.21 OS:Any
Assigned to: CPU Architecture:Any

[13 Feb 2018 13:55] Zsolt Parragi
Description:
See the repeat instructions.

Up to 1024 characters, the entries are displayed in the correct order.

Above that, the entries are displayed in their insertion order, which is the opposite.

The length of the field doesn't have to be 3072 - just long enough to hold the values will have the same results.

How to repeat:
Execute the following:

CREATE TABLE big_ok (id VARBINARY(3072) NOT NULL, PRIMARY KEY (id)) ENGINE=MEMORY;

INSERT INTO big_ok VALUES (CONCAT(REPEAT('a', 3070), 'c'));
INSERT INTO big_ok VALUES (CONCAT(REPEAT('a', 3070), 'b'));
INSERT INTO big_ok VALUES (CONCAT(REPEAT('a', 3070), 'a'));

SELECT RIGHT(id, 5) FROM big_ok FORCE INDEX (PRIMARY) ORDER BY id ASC LIMIT 10;
SELECT RIGHT(id, 5) FROM big_ok IGNORE INDEX (PRIMARY) ORDER BY id ASC LIMIT 10;
[13 Feb 2018 14:39] Zsolt Parragi
It's possible that this is intended behavior, but it is definitely misleading:

the problem is that the max_sort_length variable defaults to 1024, while the max key length is 3072.

This means that fields between these will be correctly sorted when the server is using an index, but won't be otherwise.
[14 Feb 2018 6:41] MySQL Verification Team
Hello Zsolt Parragi,

Thank you for the report.

Thanks,
Umesh