SQL Error (1038): Out of sort memory, consider increasing server sort buffer

Question

I have upgraded MariaDB 10.3.13 to the latest version 10.5.6. Once deployed, one of my queries started giving this error:

SQL Error (1038): Out of sort memory, consider increasing server sort buffer

I managed to simplify query while keeping the error on fresh 10.5.6 install (default settings). This works without issues on 10.3.13:

DROP TABLE IF EXISTS test_products;
CREATE TABLE test_products (
    product_name VARCHAR(7), id_country INT, PRIMARY KEY (product_name, id_country) 
) COLLATE='utf8_general_ci' ENGINE=INNODB;


DELIMITER //
FOR i IN 1..3700 DO INSERT INTO test_products (product_name, id_country) VALUES ('product', i); END FOR;
//
DELIMITER ;

SELECT p.product_name, p.id_country, hierarchy.hierarchy
FROM test_products p
LEFT JOIN (
    SELECT p2.product_name, p2.id_country, h.hierarchy, MAX(test) AS test
    FROM test_products p2
    LEFT JOIN (
        SELECT product_name, id_country, '_exactly_' AS hierarchy, 1 AS test -- removing one single character from string will make this query working again
        FROM test_products
    ) h ON h.product_name = p2.product_name AND h.id_country = p2.id_country
   GROUP BY product_name, id_country, h.hierarchy
) hierarchy ON hierarchy.product_name = p.product_name AND hierarchy.id_country = p.id_country

Increasing sort_buffer_size helps. However on original query I need to increase this buffer from default 2MB to around 80MB (no ORDER BY involved at all; products table filtered to around 15k-20k entries - not that big numbers at all). According to https://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/ I would rather keep original settings. Also I don't understand why 10.3.13 with default 2MB buffer works well while newer version does not.
Should I submit a bug on MariaDB? Or am I doing something wrong? Or should I investigate a week/two to simplify the query? Lost here.

tested on Windows Server 2012R2; Xeon [email protected]; 16GB ram

Thanks for help.

Answer 1

This bahaviour looked strange so I submitted a bug at MariaDB team.

Fix will be available since version 10.5.7.

For more details check https://jira.mariadb.org/browse/MDEV-24015