MDEV-24015: SQL Error (1038): Out of sort memory when enough memory for the sort buffer is provided

The issue here was with the re-execution of a subquey, after few executions
the subquery execution returned error that not enough space was provided inside the sort
buffer.
This is happening because with the introduction of packed sort keys, during the phase
where sort length is calculated the parameters of the sort keys are not reset and
we keep up adding the sort length to the previous calculated sort length for the
earlier executions of the subquery. This happens because the sort keys structure is
allocated only once and so if their is re-execution of a subquery involving sorting
then we should be resetting the parameters of the sort keys.

4 files changed, 150 insertions, 0 deletions

diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index a3811ca61e2..806b7b9eea6 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -4123,4 +4123,125 @@ f
 baz
 foo
 DROP TABLE t1;
+#
+# MDEV-24015: SQL Error (1038): Out of sort memory when enough memory for the
+# sort buffer is provided
+#
+CREATE TABLE t1 (a VARCHAR(100), b INT);
+CREATE TABLE t2 (a VARCHAR(100), b INT);
+INSERT INTO t1 SELECT 'abc', seq FROM seq_1_to_50;
+INSERT INTO t2 SELECT seq, seq FROM seq_1_to_50;
+set @save_sort_buffer_size= @@sort_buffer_size;
+set sort_buffer_size=2000;
+ANALYZE FORMAT=JSON
+SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 50,
+      "r_rows": 50,
+      "r_table_time_ms": "REPLACED",
+      "r_other_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "subqueries": [
+      {
+        "expression_cache": {
+          "r_loops": 50,
+          "r_hit_ratio": 0,
+          "query_block": {
+            "select_id": 2,
+            "r_loops": 50,
+            "r_total_time_ms": "REPLACED",
+            "filesort": {
+              "sort_key": "t2.a",
+              "r_loops": 50,
+              "r_total_time_ms": "REPLACED",
+              "r_used_priority_queue": false,
+              "r_output_rows": 1,
+              "r_buffer_size": "REPLACED" across executions)",
+              "r_sort_mode": "sort_key,rowid",
+              "temporary_table": {
+                "table": {
+                  "table_name": "t2",
+                  "access_type": "ALL",
+                  "r_loops": 50,
+                  "rows": 50,
+                  "r_rows": 50,
+                  "r_table_time_ms": "REPLACED",
+                  "r_other_time_ms": "REPLACED",
+                  "filtered": 100,
+                  "r_filtered": 2,
+                  "attached_condition": "t1.b = t2.b"
+                }
+              }
+            }
+          }
+        }
+      }
+    ]
+  }
+}
+SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1;
+(SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a)
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+20
+21
+22
+23
+24
+25
+26
+27
+28
+29
+30
+31
+32
+33
+34
+35
+36
+37
+38
+39
+40
+41
+42
+43
+44
+45
+46
+47
+48
+49
+50
+set sort_buffer_size= @save_sort_buffer_size;
+DROP TABLE t1,t2;
 # End of 10.5 tests
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index 8e0b479e02d..88d5250bb4c 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2556,4 +2556,24 @@ INSERT t1 VALUES ('foo','bar'),('baz','qux');
 SELECT COALESCE(a, b) AS f FROM t1 ORDER BY f;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-24015: SQL Error (1038): Out of sort memory when enough memory for the
+--echo # sort buffer is provided
+--echo #
+
+CREATE TABLE t1 (a VARCHAR(100), b INT);
+CREATE TABLE t2 (a VARCHAR(100), b INT);
+
+INSERT INTO t1 SELECT 'abc', seq FROM seq_1_to_50;
+INSERT INTO t2 SELECT seq, seq FROM seq_1_to_50;
+
+set @save_sort_buffer_size= @@sort_buffer_size;
+set sort_buffer_size=2000;
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON
+SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1;
+SELECT (SELECT sum(t2.b) FROM t2 WHERE t1.b=t2.b GROUP BY t2.a) FROM t1;
+
+set sort_buffer_size= @save_sort_buffer_size;
+DROP TABLE t1,t2;
 --echo # End of 10.5 tests
diff --git a/sql/filesort.cc b/sql/filesort.cc
index 2ce532308a2..70926d7d449 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -2199,6 +2199,8 @@ sortlength(THD *thd, Sort_keys *sort_keys, bool *allow_packing_for_sortkeys)
   length=0;
   uint nullable_cols=0;
 
+  sort_keys->reset_parameters();
+
   for (SORT_FIELD *sortorder= sort_keys->begin();
        sortorder != sort_keys->end();
        sortorder++)
diff --git a/sql/sql_sort.h b/sql/sql_sort.h
index 40f0c5ede5f..9ebd2bcfa03 100644
--- a/sql/sql_sort.h
+++ b/sql/sql_sort.h
@@ -310,6 +310,13 @@ public:
     sort_length+= len;
   }
 
+  void reset_parameters()
+  {
+    m_using_packed_sortkeys= false;
+    size_of_packable_fields= 0;
+    sort_length= 0;
+  }
+
   static const uint size_of_length_field= 4;
 
 private: