How to fix MySQL 4136 Subquery transformation was rejected by the optimizer?

Use a derived table to materialize the subquery: Wrapping the subquery in a derived table prevents semi-join transformation attempts.

4136

MySQLERRORNotableMEDIUM confidence

Subquery transformation was rejected by the optimizer

Rewrite using JOIN instead of subquery: An explicit JOIN avoids subquery transformation and gives the optimizer more flexibility.

Production Risk

Medium — The query fails or produces a plan that the optimizer cannot execute.

Why it happens

1A subquery uses features that are incompatible with semi-join transformation (e.g., LIMIT, GROUP BY, UNION).
2The outer query and subquery cannot be safely merged due to semantic constraints.

Fix 1

Rewrite using JOIN instead of subquery

SELECT a.* FROM a JOIN (SELECT id FROM b WHERE cond) sub ON a.id = sub.id;

Why this works

An explicit JOIN avoids subquery transformation and gives the optimizer more flexibility.

Fix 2

Use a derived table to materialize the subquery

Why this works

Wrapping the subquery in a derived table prevents semi-join transformation attempts.

What not to do

Sources

MySQL 8.0 — 4136 ER_SUBQUERY_TRANSFORM_REJECTED

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

At a glance

PlatformMySQL Error Numbers

Code4136

SeverityERROR

TierNotable

ConfidenceMEDIUM