MySQL Forums
Forum List  »  Quality Assurance

Re: SubSelect hit bug
Posted by: Lex Vjatkin
Date: January 20, 2012 02:53AM

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
(SELECT position FROM bar AS b
WHERE b.foo_id = f.id
ORDER BY b.foo_id , b.position
LIMIT 10,1
) 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.

Any ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
2117
January 19, 2012 05:11AM
Re: SubSelect hit bug
894
January 20, 2012 02:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.