Re: Querying a fast subquery never returns

[Eric Coll]

As I mentioned, the large query is not the problem. That query runs in a few seconds even with the nested subquery that runs for each row which is intended.

The problem is that we add a simple select from the query result, or just try to insert the data from the query result the query hangs forever in the sending data or sorting state.

This is just a bug in MySQL. I found a related bug reported in 2007 and it has never been fixed.

I created another view removing one join that wasn't necessary (a trivial join to a very small table) and now everything runs fine.

I am very surprised on how little interest the team has in fixing this pervasive bug. It comes down to the fact that you can't trust MySQL being able to handle fairly simple queries.

We just switch to MySQL 5.5 because MySQL 5.1 returned wrong results for queries on partitioned tables.