What causes "Given correlated subquery is not supported" in prestodb/presto?

Rewrite the correlated subquery as a JOIN (LEFT JOIN with aggregation or DISTINCT)

prestodb/presto · error · PrestoException

UNSUPPORTED_SUBQUERY

UNSUPPORTED_SUBQUERY

Error message

Given correlated subquery is not supported

What it means

After subquery-planning rewriting, this optimizer verifies that every subquery node no longer carries correlation variables. If a correlated subquery survives rewriting, the engine cannot decorrelate it and throws UNSUPPORTED_SUBQUERY.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/planner/optimizations/CheckSubqueryNodesAreRewritten.java:61

                .ifPresent(node -> {
                    ApplyNode applyNode = (ApplyNode) node;
                    error(applyNode.getCorrelation(), applyNode.getOriginSubqueryError());
                });

        searchFrom(plan).where(LateralJoinNode.class::isInstance)
                .findFirst()
                .ifPresent(node -> {
                    LateralJoinNode lateralJoinNode = (LateralJoinNode) node;
                    error(lateralJoinNode.getCorrelation(), lateralJoinNode.getOriginSubqueryError());
                });

        return PlanOptimizerResult.optimizerResult(plan, false);
    }

    private void error(List<VariableReferenceExpression> correlation, String originSubqueryError)
    {
        checkState(!correlation.isEmpty(), "All the non correlated subqueries should be rewritten at this point");
        throw new PrestoException(UNSUPPORTED_SUBQUERY, format(originSubqueryError, "Given correlated subquery is not supported"));
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

Rewrite the correlated subquery as a JOIN (LEFT JOIN with aggregation or DISTINCT)
Unnest via a CTE computing the correlated part per group, then join on the key
Test on a newer Presto version where decorrelation rules may cover the pattern; if still failing, file an issue with the query

Example fix

// before
SELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE region = o.region);
// after
WITH avg_by_region AS (
  SELECT region, AVG(total) AS avg_total FROM orders GROUP BY region
)
SELECT o.* FROM orders o JOIN avg_by_region a ON o.region = a.region
WHERE o.total > a.avg_total;

Defensive patterns

Strategy: try-catch

Validate before calling

// detect outer-column references inside subqueries before running
const hasCorrelation = /\b(SELECT[\s\S]*?\bFROM\b[\s\S]*?WHERE[\s\S]*?\b\w+\.)/i.test(sql);

Type guard

null

Try / catch

try {
    return query(sql);
} catch (PrestoException e) {
    if (e.getErrorCode() == UNSUPPORTED_SUBQUERY.toErrorCode()) {
        // fall back to a manually unnested JOIN version of the query
        return query(unnestedVariant);
    }
    throw e;
}

Prevention

Prefer explicit JOINs over correlated subqueries in Presto
Push correlated logic into CTEs with GROUP BY on the correlation key
Test complex subquery queries against the target Presto version before deploying

When it happens

Trigger: Running a query with a correlated subquery pattern that Presto's rewrite rules cannot decorrelate (e.g. correlation in an unsupported position like certain aggregates, ORDER BY/LIMIT inside subqueries, or non-equality correlations).

Common situations: Correlated EXISTS/IN/SCALAR subqueries with complex predicates; OR conditions linking outer and inner columns; subqueries in CASE expressions the rewriter doesn't handle.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/512cf73b48f5862d. Report an issue: GitHub.