To clarify the situation - this is only minimum fragment causing bug of bigger query we (Ondrej Prochazka is my colegue) use to select TOP elements of each group by technique "Row sampling" http://explainextended.com/2009/03/06/advanced-row-sampling/
Database has 100GB+ of data.
In real usecase subquery has LIMIT N,1 like this:
(SELECT position FROM bar AS b
WHERE b.foo_id = f.id
ORDER BY b.foo_id , b.position
) AS maxkey
FROM foo AS f
LIMIT 10, 1;
to select 10th element.
But this is not the question.. Question is, why mysql crashes and how we can workaround it?
I assumed this is because of indexes. Changing index `foo_id_position` to UNIQUE solves it. But i cant do it beacuse of data.
Removing duplicate index `foo_id` solves it too, but actually we use it with some other column.