pathwaycom/pathway · error · NotImplementedError

PIVOTS not supported

Error message

PIVOTS not supported

What it means

After translating a subquery node (sql_expr.Subquery) in pw.sql, Pathway checks for leftover PIVOT/UNPIVOT clauses in node.args. A non-empty 'pivots' list raises NotImplementedError('PIVOTS not supported'): Pathway's SQL layer simply has no implementation of PIVOT attached to a subquery.

Source

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

    tabs = []
    for expression in node.expressions:
        tab, context = _run(expression, context)
        tabs.append(tab)
    ret = tabs[0]
    for tab in tabs[1:]:
        ret = ret.join(tab)
    return ret, context


@register(nodetype=sql_expr.Subquery)
def _subquery(
    node: sql_expr.Subquery, context: ContextType
) -> tuple[table.Table, ContextType]:
    context = _with_block(node, context)
    tab, _ = _run(node.args.pop("this"), context)
    tab, context = _alias_block(node, tab, context)
    if node.args.pop("pivots", []) != []:
        raise NotImplementedError("PIVOTS not supported")
    _check_work_done(node)
    return tab, context


@register(nodetype=sql_expr.Table)
def _table(
    node: sql_expr.Table, context: ContextType
) -> tuple[table.Joinable, ContextType]:
    name = _identifier(node.args.pop("this"), context)
    tab = context[name]
    tab, context = _alias_block(node, tab, context)
    joined_tab, context = _joins_block(node, tab, context)

    if node.args.pop("pivots", []) != []:
        raise NotImplementedError("PIVOTS not supported")
    _check_work_done(node)
    return joined_tab, context

View on GitHub (pinned to fa2f74a464)

Solutions

1. Rewrite PIVOT/UNPIVOT using explicit conditional aggregation (FILTER / CASE WHEN with the pathway-reducible form) inside the SELECT
2. Or perform the reshape with Pathway's native API (table.with_columns, groupby/reduce) after reading the base table via pw.sql
3. Remove the pivot and pivot manually in a downstream table operation

Example fix

-- before
SELECT * FROM (SELECT k, v FROM t) PIVOT (MAX(v) FOR k IN ('a','b'));

-- after
SELECT
  MAX(CASE WHEN k = 'a' THEN v END) AS a,
  MAX(CASE WHEN k = 'b' THEN v END) AS b
FROM t;

def has_pivot(query: str) -> bool:
    q = query.upper()
    return "PIVOT" in q or "UNPIVOT" in q

try:
    tab = pw.sql(query)
except NotImplementedError as e:
    if "PIVOT" in str(e):
        tab = pw.sql(to_conditional_aggregation(query))
    else:
        raise

When it happens

Trigger: Calling pw.sql() with a query whose FROM clause contains (subquery) PIVOT(...) or (subquery) UNPIVOT(...); dialect-specific pivot syntax that sqlglot normalizes into the subquery's pivots arg.

Common situations: Porting analytics/warehouse SQL that uses PIVOT for reshaping; queries written for SQL Server/BigQuery/Snowflake style PIVOT.

Related errors

• {node.sql()} not supported.
• {key}: {repr} not supported.
• parsing {query} failed
• Correlated subqueries not supported.
• Currently, tantivy bm25 index is supported only in the as-of

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