MDEV-23216: LONGTEXT column with collation doesn't sort

An overflow was happening with LONGTEXT columns, when the length was converted to the length
in the strxfrm form (mem-comparable keys).
Introduced a function to truncate the length to the max_sort_length before calculating
the length of the strxfrm form.

4 files changed, 40 insertions, 5 deletions

diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index dd04ae42e5a..f1a6cb086b8 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -4079,4 +4079,22 @@ COUNT(DISTINCT a)
 34
 SET @@tmp_memory_table_size= @save_tmp_memory_table_size;
 DROP TABLE t1;
+#
+# MDEV-23216: LONGTEXT column with collation doesn't sort
+#
+CREATE TABLE t1 (a LONGTEXT COLLATE utf8mb4_swedish_ci);
+INSERT INTO t1 VALUES ('A'),('Z'),('B'),('Y');
+SELECT * FROM t1 ORDER BY a;
+a
+A
+B
+Y
+Z
+SELECT * FROM t1 ORDER BY a DESC;
+a
+Z
+Y
+B
+A
+DROP TABLE t1;
 # End of 10.5 tests
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 8398246cae9..e27822006b5 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2522,5 +2522,15 @@ SELECT COUNT(DISTINCT a) FROM t1;
 SET @@tmp_memory_table_size= @save_tmp_memory_table_size;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-23216: LONGTEXT column with collation doesn't sort
+--echo #
+
+CREATE TABLE t1 (a LONGTEXT COLLATE utf8mb4_swedish_ci);
+INSERT INTO t1 VALUES ('A'),('Z'),('B'),('Y');
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t1 ORDER BY a DESC;
+
+DROP TABLE t1;
 
 --echo # End of 10.5 tests
diff --git a/sql/filesort.cc b/sql/filesort.cc
index 3f99bebd0cc..5a07b8596bd 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -2102,9 +2102,7 @@ Type_handler_string_result::sort_length(THD *thd,
                                        SORT_FIELD_ATTR *sortorder) const
 {
   CHARSET_INFO *cs;
-  sortorder->length= item->max_length;
-  set_if_smaller(sortorder->length, thd->variables.max_sort_length);
-  sortorder->original_length= item->max_length;
+  sortorder->set_length_and_original_length(thd, item->max_length);
 
   if (use_strnxfrm((cs= item->collation.collation)))
   {
@@ -2211,9 +2209,9 @@ sortlength(THD *thd, Sort_keys *sort_keys, bool *allow_packing_for_sortkeys)
     {
       Field *field= sortorder->field;
       CHARSET_INFO *cs= sortorder->field->sort_charset();
-      sortorder->length= sortorder->field->sort_length();
+      sortorder->set_length_and_original_length(thd, field->sort_length());
+
       sortorder->suffix_length= sortorder->field->sort_suffix_length();
-      sortorder->original_length= sortorder->length;
       sortorder->type= field->is_packable() ?
                        SORT_FIELD_ATTR::VARIABLE_SIZE :
                        SORT_FIELD_ATTR::FIXED_SIZE;
@@ -2748,6 +2746,14 @@ bool SORT_FIELD_ATTR::check_if_packing_possible(THD *thd) const
 }
 
 
+void SORT_FIELD_ATTR::set_length_and_original_length(THD *thd, uint length_arg)
+{
+  length= length_arg;
+  set_if_smaller(length, thd->variables.max_sort_length);
+  original_length= length_arg;
+}
+
+
 /*
   Compare function used for packing sort keys
 */
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 36f98ce1a66..08e5af289f3 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -6454,6 +6454,7 @@ struct SORT_FIELD_ATTR
                                 uchar *b, size_t *b_len);
   bool check_if_packing_possible(THD *thd) const;
   bool is_variable_sized() { return type == VARIABLE_SIZE; }
+  void set_length_and_original_length(THD *thd, uint length_arg);
 };