The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
If such a subquery is an IN subquery
and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true:
Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery.
This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see
optimizer_switch flag name is condition_pushdown_for_subquery