I have huge query with many joins and almost each has group_concat. To simplify the problem - it is this:
SELECT editions_data.editionNames,
editions_data.editionIds,
p.id
FROM product p
LEFT JOIN (SELECT GROUP_CONCAT( `e`.name SEPARATOR ",") AS `editionNames`,
GROUP_CONCAT( `e`.id SEPARATOR ",") AS `editionIds`,
pe.product_id as productId
FROM products_editions pe
LEFT JOIN editions e ON e.id = pe.edition_id
GROUP BY pe.product_id
) as editions_data ON p.id = editions_data.productId
WHERE p.id IN (...)
Locally it works fine. On server with 10.3 it worked fine. After upgrade on 10.5 it started to do this:
When I send 70 ids it returns correct data for each row. When I send 71 or more ids it returns first row with correct data and other rows have editions_data as null.
I tried to swap 70th and 71st id so there is not any problem with data. But for 70 ids it works and 71 ids it doesnt.
70 ids
editionNames | editionsIds | p.id
rock oldies | 1 | 1
rock oldies | 1 | 2
indies | 2 | 3
71+ ids
editionNames | editionsIds | p.id
rock oldies | 1 | 1
null | null | 2
null | null | 3
group_concat_max_length = 1048576 on both servers.
Edit: add db-fiddle as requested in comment https://www.db-fiddle.com/f/8H8oiFAnRKAAB4esUCbRCH/0
It looks like there is a bug. Not because of group_concats but because of group by in left joins. We found that the problem is caused in optimizer_switch by split_materialized=on.
When I set split_materialized=off it starts to work properly. But there is huge duration difference then so there is need to optimize the request (instead of 700ms, it takes 96 s).
So the solution is:
SET [SESSION|GLOBAL] optimizer_switch = 'split_materialized=off';
Better to use only session where the problem is because of the huge performance drop.