RE: LIMIT in subqueries

[Paul Poulain]

Hi,

Sorry to drag up a discussion of an old topic, but I'm using MySQL 5.5 and I still have the same issue...

In a previous topic (http://forums.mysql.com/read.php?86,14788,239000#msg-239000), Stephen Booth had problems with the following query:

select sum(sorties) from missions where sorties in (select sorties from missions limit 0,2);

raising error:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

because LIMIT isn't allowed in a subquery.

Alan Rabello kindly posed the following SQL as a workaround:

select sum(sorties) from missions where sorties in (select * from (select sorties from missions limit 0,2) alias);

Now, I've run into the same problem and concede that the workaround works, but I'm struggling to understand _why_ it works! Isn't the workaround still a subquery, or rather a subquery of a subquery, albeit the inner subselect is given an alias (a "derived table"[?] pretending to be another table)?

Many thanks,

Paul.