MySQL error 1235: 'doesn't yet support LIMIT & IN/ALL/ANY/SOME subquery'
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'Short answer
MySQL does not allow LIMIT inside a subquery that is used with IN, ALL, ANY or SOME. The query is not wrong in logic, the server just does not support that form. Move the ordered, limited query into the FROM clause as a derived table and join to it, or number the rows with ROW_NUMBER() when you need the top rows per group.
Fix it fast
1Find out which cause you have
Find the subquery that holds LIMIT and see which operator it sits under. The restriction applies to IN, ALL, ANY and SOME.
-- fails SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);2Match what you see, then jump to its fix
What does the error look like?
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'ER_NOT_SUPPORTED_YET
Cause 1 of 2
LIMIT sits inside an IN, ALL, ANY or SOME subquery
1Check
The manual lists LIMIT as unsupported in subqueries for certain operators, and shows an IN subquery with ORDER BY and LIMIT as the failing example.
SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);2Fix
Put the limited query in the FROM clause as a derived table, which needs an alias, and join to it instead of using IN.
SELECT t1.*
FROM t1
JOIN (SELECT s2 FROM t2 ORDER BY s2 LIMIT 1) AS top_rows
ON t1.s1 = top_rows.s2;3Verify
The query runs. Compare its row count with what you expected from the original IN.
SHOW WARNINGS;Confirmed by dev.mysql.com
Cause 2 of 2
You wanted the top N rows for each group, not N rows overall
1Check
LIMIT counts rows over the whole subquery result, so it cannot give N rows per group even where it is allowed. Number the rows inside each group instead.
SELECT subject, time, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY time DESC) AS rn FROM t;2Fix
Use ROW_NUMBER() with PARTITION BY and ORDER BY in a derived table, then keep the rows whose number is small enough.
SELECT subject, time
FROM (
SELECT subject, time,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY time DESC) AS rn
FROM t
) AS ranked
WHERE rn <= 3;3Verify
Each subject appears at most three times.
SELECT subject, COUNT(*) FROM (SELECT subject, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY time DESC) AS rn FROM t) AS r WHERE rn <= 3 GROUP BY subject;Confirmed by dev.mysql.com
How do I stop it coming back?
- Prefer joins to derived tables over IN with LIMIT.
- Run the query on a copy of the data when you change its shape, and compare row counts.
- Keep the manual page on subquery restrictions in your team notes.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix