Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL error 1235: 'doesn't yet support LIMIT & IN/ALL/ANY/SOME subquery'

ErrorERROR 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.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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);
  2. 2Match what you see, then jump to its fix

    1. 1ERROR 1235 (42000) on a query that has IN (SELECT ... LIMIT n)LIMIT sits inside an IN, ALL, ANY or SOME subqueryFix
    2. 2The LIMIT subquery was meant to run once per parent rowYou wanted the top N rows for each group, not N rows overallFix

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.