{"record":{"id":"512cf73b48f5862d","repo":"prestodb/presto","slug":"unsupported-subquery","errorCode":"UNSUPPORTED_SUBQUERY","errorMessage":"Given correlated subquery is not supported","messagePattern":"Given correlated subquery is not supported","errorType":"error_code","errorClass":"PrestoException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/planner/optimizations/CheckSubqueryNodesAreRewritten.java","lineNumber":61,"sourceCode":"                .ifPresent(node -> {\n                    ApplyNode applyNode = (ApplyNode) node;\n                    error(applyNode.getCorrelation(), applyNode.getOriginSubqueryError());\n                });\n\n        searchFrom(plan).where(LateralJoinNode.class::isInstance)\n                .findFirst()\n                .ifPresent(node -> {\n                    LateralJoinNode lateralJoinNode = (LateralJoinNode) node;\n                    error(lateralJoinNode.getCorrelation(), lateralJoinNode.getOriginSubqueryError());\n                });\n\n        return PlanOptimizerResult.optimizerResult(plan, false);\n    }\n\n    private void error(List<VariableReferenceExpression> correlation, String originSubqueryError)\n    {\n        checkState(!correlation.isEmpty(), \"All the non correlated subqueries should be rewritten at this point\");\n        throw new PrestoException(UNSUPPORTED_SUBQUERY, format(originSubqueryError, \"Given correlated subquery is not supported\"));\n    }\n}\n","sourceCodeStart":43,"sourceCodeEnd":64,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/planner/optimizations/CheckSubqueryNodesAreRewritten.java#L43-L64","documentation":"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.","triggerScenarios":"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).","commonSituations":"Correlated EXISTS/IN/SCALAR subqueries with complex predicates; OR conditions linking outer and inner columns; subqueries in CASE expressions the rewriter doesn't handle.","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"],"exampleFix":"// before\nSELECT * FROM orders o WHERE o.total > (SELECT AVG(total) FROM orders WHERE region = o.region);\n// after\nWITH avg_by_region AS (\n  SELECT region, AVG(total) AS avg_total FROM orders GROUP BY region\n)\nSELECT o.* FROM orders o JOIN avg_by_region a ON o.region = a.region\nWHERE o.total > a.avg_total;","handlingStrategy":"try-catch","validationCode":"// detect outer-column references inside subqueries before running\nconst hasCorrelation = /\\b(SELECT[\\s\\S]*?\\bFROM\\b[\\s\\S]*?WHERE[\\s\\S]*?\\b\\w+\\.)/i.test(sql);","typeGuard":"null","tryCatchPattern":"try {\n    return query(sql);\n} catch (PrestoException e) {\n    if (e.getErrorCode() == UNSUPPORTED_SUBQUERY.toErrorCode()) {\n        // fall back to a manually unnested JOIN version of the query\n        return query(unnestedVariant);\n    }\n    throw e;\n}","preventionTips":["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"],"tags":["subquery","correlation","sql-planning"],"backgroundTag":"unsupported-correlated-subquery","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}