{"record":{"id":"53f6e7ffee39baec","repo":"pathwaycom/pathway","slug":"correlated-subqueries-not-supported","errorCode":null,"errorMessage":"Correlated subqueries not supported.","messagePattern":"Correlated subqueries not supported\\.","errorType":"exception","errorClass":"SyntaxError","httpStatus":null,"severity":"error","filePath":"python/pathway/internals/sql/processing.py","lineNumber":506,"sourceCode":"    def prune_fn(_self, _parent, _key):\n        return isinstance(_self, sql_expr.Subquery)\n\n    return [\n        _self\n        for _self, _parent, _key in node.dfs(prune=prune_fn)\n        if prune_fn(_self, _parent, _key)\n    ]\n\n\n# mutates `field`\ndef _process_field_for_subqueries(field, tab, context, orig_context, agg_fun):\n    context_subqueries = {**context}\n    tab_joined = tab\n    for subquery in _all_nonnested_subqueries(field):\n        try:\n            subquery_tab, _ = _subquery(subquery, orig_context)\n        except KeyError:\n            raise SyntaxError(\"Correlated subqueries not supported.\")\n        tabname = f\"__pathway__tmp__table__name__{next(_tmp_table_cnt)}\"\n        context_subqueries[tabname] = subquery_tab\n        [colexpr] = subquery_tab\n        subquery.replace(sqlglot.parse_one(f\"{agg_fun}({tabname}.{colexpr.name})\"))\n        tab_joined = tab_joined.join(subquery_tab, id=tab_joined.id)\n\n    return tab_joined, context_subqueries\n\n\n@register(nodetype=sql_expr.Select)\ndef _select(\n    node: sql_expr.Select, context: ContextType\n) -> tuple[table.Table, ContextType]:\n    orig_context = context\n\n    # WITH block\n    context = _with_block(node, context)\n","sourceCodeStart":488,"sourceCodeEnd":524,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/internals/sql/processing.py#L488-L524","documentation":"When Pathway processes scalar subqueries inside a SELECT expression, it re-evaluates each subquery against the outer context. If resolving a name inside the subquery raises KeyError (the referenced table/column is not in the subquery's own scope), Pathway reports SyntaxError('Correlated subqueries not supported.'): the subquery depends on outer-query names, which Pathway's SQL engine cannot resolve.","triggerScenarios":"pw.sql with a subquery that references an outer table/column, e.g. SELECT ..., (SELECT MAX(x) FROM s WHERE s.k = t.k) FROM t; also EXISTS/IN subqueries correlated with the outer row.","commonSituations":"Porting standard SQL where correlated subqueries are idiomatic lookup patterns; forgetting to add the join condition inside the subquery so it accidentally references outer names.","solutions":["Rewrite the correlated subquery as an explicit JOIN plus GROUP BY/aggregation, then select from the joined table","Or use Pathway native API: table.join / table.groupby(...).reduce(...) to compute the aggregate and join it back","Make the subquery self-contained (reference only its own FROM tables) if the correlation was unintentional"],"exampleFix":"-- before\nSELECT t.k, (SELECT MAX(s.x) FROM s WHERE s.k = t.k) AS mx FROM t;\n\n-- after\nSELECT t.k, m.mx\nFROM t LEFT JOIN (SELECT k, MAX(x) AS mx FROM s GROUP BY k) m ON m.k = t.k;","handlingStrategy":"fallback","validationCode":"def mentions_outer_alias_inside_subquery(query: str, outer_alias: str) -> bool:\n    # crude heuristic: outer alias used within parentheses after a WHERE inside a subquery\n    import re\n    return bool(re.search(rf\"\\(\\s*SELECT.*WHERE.*\\b{outer_alias}\\.\", query, re.S | re.I))","typeGuard":null,"tryCatchPattern":"try:\n    tab = pw.sql(query)\nexcept SyntaxError as e:\n    if \"Correlated\" in str(e):\n        tab = pw.sql(rewrite_as_join(query))  # pre-authored rewrite\n    else:\n        raise","preventionTips":["Prefer JOIN + GROUP BY over correlated subqueries when writing for Pathway","Review subqueries for references to outer-table aliases before running"],"tags":["sql","subquery","pathway"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}