pathwaycom/pathway · error · SyntaxError

Correlated subqueries not supported.

Error message

Correlated subqueries not supported.

What it means

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.

Source

Thrown at python/pathway/internals/sql/processing.py:506

    def prune_fn(_self, _parent, _key):
        return isinstance(_self, sql_expr.Subquery)

    return [
        _self
        for _self, _parent, _key in node.dfs(prune=prune_fn)
        if prune_fn(_self, _parent, _key)
    ]


# mutates `field`
def _process_field_for_subqueries(field, tab, context, orig_context, agg_fun):
    context_subqueries = {**context}
    tab_joined = tab
    for subquery in _all_nonnested_subqueries(field):
        try:
            subquery_tab, _ = _subquery(subquery, orig_context)
        except KeyError:
            raise SyntaxError("Correlated subqueries not supported.")
        tabname = f"__pathway__tmp__table__name__{next(_tmp_table_cnt)}"
        context_subqueries[tabname] = subquery_tab
        [colexpr] = subquery_tab
        subquery.replace(sqlglot.parse_one(f"{agg_fun}({tabname}.{colexpr.name})"))
        tab_joined = tab_joined.join(subquery_tab, id=tab_joined.id)

    return tab_joined, context_subqueries


@register(nodetype=sql_expr.Select)
def _select(
    node: sql_expr.Select, context: ContextType
) -> tuple[table.Table, ContextType]:
    orig_context = context

    # WITH block
    context = _with_block(node, context)

View on GitHub (pinned to fa2f74a464)

Solutions

1. Rewrite the correlated subquery as an explicit JOIN plus GROUP BY/aggregation, then select from the joined table
2. Or use Pathway native API: table.join / table.groupby(...).reduce(...) to compute the aggregate and join it back
3. Make the subquery self-contained (reference only its own FROM tables) if the correlation was unintentional

Example fix

-- before
SELECT t.k, (SELECT MAX(s.x) FROM s WHERE s.k = t.k) AS mx FROM t;

-- after
SELECT t.k, m.mx
FROM t LEFT JOIN (SELECT k, MAX(x) AS mx FROM s GROUP BY k) m ON m.k = t.k;

def mentions_outer_alias_inside_subquery(query: str, outer_alias: str) -> bool:
    # crude heuristic: outer alias used within parentheses after a WHERE inside a subquery
    import re
    return bool(re.search(rf"\(\s*SELECT.*WHERE.*\b{outer_alias}\.", query, re.S | re.I))

try:
    tab = pw.sql(query)
except SyntaxError as e:
    if "Correlated" in str(e):
        tab = pw.sql(rewrite_as_join(query))  # pre-authored rewrite
    else:
        raise

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• parsing {query} failed
• {node.sql()} not supported.
• PIVOTS not supported
• {key}: {repr} not supported.
• Failed to install dependencies

AI-assisted analysis of pathwaycom/pathway@fa2f74a464 (2026-08-15). Data as JSON: /api/errors/53f6e7ffee39baec. Report an issue: GitHub.