SELECT distance FROM table WHERE distance < ( SELECT MAX(distance)/2 FROM table)
The key part was the subquery. They came back and said it returned an error:
"Can't reopen table"
The problem is this was on a TEMPORARY table. The manual clearly states this as a restriction:
You cannot refer to a
TEMPORARY table more than once in the same query. (http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html)However I decided to do a bit of digging to find out why that's the case. So I found a nicely commented answer, straight from the source's mouth:
/*
We're trying to use the same temporary table twice in a query.
Right now we don't support this because a temporary table
is always represented by only one TABLE object in THD, and
it can not be cloned. Emit an error for an unsupported behavior
*/ from sql/sql_base.cc open_table
5 comments:
I've always wondered about this, but I'm not familiar enough with the internal structure of MySQL for this to be very informative. Is this a minor implementation detail, or some kind of major conceptual issue? It sure would be nice to get rid of this limitation; it severely limits the utility of temporary tables.
I often hit this "bug/feature" and wish too that it would be corrected. :)
Here's another bug when you try to run that query. If the other table had not been a temporary table and you had any significant amount of data in either table you'd find that the performance will go to heck. The problem with a sub-query in the where clause is that the sub-query will be run for each row of the outer query.
Sub-queries in the FROM clause do not have this problem and of course on a query like this you can rewrite it so it doesn't need a sub-query at all.
Personally I love sub-queries because they can make the whole query more readable - but be careful, they;ll work fine until you get a medium amount of data in these tables (never mind large).
frankf471:
Not true, what you described is a DEPENDENT SUBQUERY, where it depends on the outer query.
In this case, the value is the same for each row of the outer query and mysql knows it.
You can see the difference in EXPLAIN output, and actual testing bears it out.
Hi,
This is a little more than a minor implementation detail. I discussed this with Monty at a MySQL University session some time ago, check the IRC logs documented here:
http://forge.mysql.com/wiki/Overview_of_query_execution_and_use_of_temp_tables
Monty discusses the cause in a little more depth, and some possible solutions.
Post a Comment