why doesn't MySQL support 'LIMIT & IN/ALL/ANY/SOME subquery'

Question

Almost everyone have met this subquery ERROR:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

and every answer suggest us to replace subquery with left outer join in this situation.

Supporting limit subquery with in operation is a nice-to-have feature because lots of subqueries are used with in operator, but why MySQL development group decide not to bring it to MySQL, is there any reasons that makes limit with these operator in a subquery dangerous or bad performance?

On Nov 30th 2016, as Mysql 8.0 latest beta draft documentation, MySQL still not support this kind of subquery.

http://dev.mysql.com/doc/refman/8.0/en/subquery-errors.html

Answer 1

Here are my comments on each scenario listed in that link that you sent:

1. Using a "limit" in a subquery: this would make false/lying and inconsistent results (since the subquery would return different rows at different times due to current caches and optimizations, even if there were no data changes)
2. Multiple columns in a subquery inside the SELECT statements - I understand a few cases where this would be useful but it could be confusing to read in the SQL statement which is why I think it's not supported - I'm not aware of any db engine that would allow this and there are several ways to do it correctly.
3. Subquery returns more than one row - yeah if you want a value that's EQUAL, then it must be equal to another value, not to a list; if you want any value from a list then it's a different thing and therefore a different syntax is required
4. Updating the same table as in a subquery - technical limitation, some engines lock tables instead of rows. I'm sure I've done this a lot; gotta love Oracle DB :-)

The only possible scenario which I could possibly think of to limit the results of a subquery is to bring just a data sample, like if you want to bring a count of errors in a log or something and you just want a column for random sample (ex: [PROGRAM][LOG COUNT][ERR COUNT][ERR EXAMPLE])... in this very rare case, I would understand the usage of limiting the result of a subquery but for this you can use some group by function such as MIN() or MAX() and definitely not through LIMIT.

To more directly answer just the main question "why doesn't MySQL support 'LIMIT & IN/ALL/ANY/SOME subquery'" - because if you are trying to select data like data in (select data... LIMIT...) are you basically trying to do data in (some random data), since the LIMIT will prevent some rows from showing up in the subquery results and there's no way to determine which ones.

Anyway, if you find any scenario that you think you need to limit the results of a subquery and it's not to just bring a sample of data - think again, something is probably wrong with your logic; ask here if it's the case :-)