diff options
author | Varun Gupta <varun.gupta@mariadb.com> | 2020-10-26 12:05:47 +0530 |
---|---|---|
committer | Varun Gupta <varun.gupta@mariadb.com> | 2020-10-28 01:07:33 +0530 |
commit | c07bceb6ed27b3e1c971a0282d3f0d429e4559c6 (patch) | |
tree | 5d0e179b080e0e48ac5164bf3b5cb43d348f8c9b | |
parent | 00ddea4f2f7d20e09a28655b2f30a4c014e9344a (diff) | |
download | mariadb-git-bb-10.5-varun.tar.gz |
MDEV-24015: SQL Error (1038): Out of sort memory when enough memory for the sort buffer is providedbb-10.5-varun
For a correlated subquery filesort is executed multiple times.
During each execution, sortlength() computed total sort key length in
Sort_keys::sort_length, without resetting it first.
Eventually Sort_keys::sort_length got larger than @@sort_buffer_size, which
caused filesort() to be aborted with error.
Fixed by making sortlength() to compute lengths only during the first
invocation. Subsequent invocations return pre-computed values.
-rw-r--r-- | mysql-test/main/order_by.result | 121 | ||||
-rw-r--r-- | mysql-test/main/order_by.test | 20 | ||||
-rw-r--r-- | sql/filesort.cc | 10 | ||||
-rw-r--r-- | sql/sql_sort.h | 50 |
4 files changed, 188 insertions, 13 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..b261f089313 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -2199,6 +2199,12 @@ sortlength(THD *thd, Sort_keys *sort_keys, bool *allow_packing_for_sortkeys) length=0; uint nullable_cols=0; + if (sort_keys->is_parameters_computed()) + { + *allow_packing_for_sortkeys= sort_keys->using_packed_sortkeys(); + return sort_keys->get_sort_length_with_memcmp_values(); + } + for (SORT_FIELD *sortorder= sort_keys->begin(); sortorder != sort_keys->end(); sortorder++) @@ -2260,6 +2266,8 @@ sortlength(THD *thd, Sort_keys *sort_keys, bool *allow_packing_for_sortkeys) // add bytes for nullable_cols sort_keys->increment_original_sort_length(nullable_cols); *allow_packing_for_sortkeys= allow_packing_for_keys; + sort_keys->set_sort_length_with_memcmp_values(length + nullable_cols); + sort_keys->set_parameters_computed(true); DBUG_PRINT("info",("sort_length: %d",length)); return length + nullable_cols; } @@ -2518,7 +2526,7 @@ void Sort_param::try_to_pack_sortkeys() return; const uint sz= Sort_keys::size_of_length_field; - uint sort_len= sort_keys->get_sort_length(); + uint sort_len= sort_keys->get_sort_length_with_original_values(); /* Heuristic introduced, skip packing sort keys if saving less than 128 bytes diff --git a/sql/sql_sort.h b/sql/sql_sort.h index 40f0c5ede5f..a474d7c25e9 100644 --- a/sql/sql_sort.h +++ b/sql/sql_sort.h @@ -255,10 +255,12 @@ class Sort_keys :public Sql_alloc, { public: Sort_keys(SORT_FIELD* arr, size_t count): - Sort_keys_array(arr, count), - m_using_packed_sortkeys(false), - size_of_packable_fields(0), - sort_length(0) + Sort_keys_array(arr, count), + m_using_packed_sortkeys(false), + size_of_packable_fields(0), + sort_length_with_original_values(0), + sort_length_with_memcmp_values(0), + parameters_computed(false) { DBUG_ASSERT(!is_null()); } @@ -280,14 +282,24 @@ public: return size_of_packable_fields; } - void set_sort_length(uint len) + void set_sort_length_with_original_values(uint len) { - sort_length= len; + sort_length_with_original_values= len; } - uint get_sort_length() + uint get_sort_length_with_original_values() { - return sort_length; + return sort_length_with_original_values; + } + + void set_sort_length_with_memcmp_values(uint len) + { + sort_length_with_memcmp_values= len; + } + + uint get_sort_length_with_memcmp_values() + { + return sort_length_with_memcmp_values; } static void store_sortkey_length(uchar *p, uint sz) @@ -307,9 +319,12 @@ public: void increment_original_sort_length(uint len) { - sort_length+= len; + sort_length_with_original_values+= len; } + bool is_parameters_computed() { return parameters_computed; } + void set_parameters_computed(bool val) { parameters_computed= val; } + static const uint size_of_length_field= 4; private: @@ -317,10 +332,21 @@ private: uint size_of_packable_fields; // Total length bytes for packable columns /* - The length that would be needed if we stored non-packed mem-comparable - images of fields? + The sort length for all the keyparts storing the original values + */ + uint sort_length_with_original_values; + + /* + The sort length for all the keyparts storing the mem-comparable images + */ + uint sort_length_with_memcmp_values; + + /* + TRUE parameters(like sort_length_* , size_of_packable_field) + are computed + FALSE otherwise. */ - uint sort_length; + bool parameters_computed; }; |