Column qualification fails on chained partitions due to unpropagated subquery column renamings

Executing a query with chained partitioning (.PARTITION()) and child collection unwinding (.a) raises a SQLGlot OptimizeError: Unknown column: customer_id_5 during the SQL query optimization pass (apply_sqlglot_optimizer).

PyDough's relational translation generates an invalid subquery alias reference in the SQL AST where a derived table (_s15) renames a column (customer_id_5 AS c_id), but the outer SELECT clause still attempts to access _s15.customer_id_5 instead of _s15.c_id.

Dataset

pear_music (Chinook schema)

Minimal Reproducible Example

lines = invoice_line.WHERE(
    HAS(invoice) & ISIN(track.genre.name, ("Rock",))
).CALCULATE(
    c_id=invoice.customer.customer_id,
    c_name=invoice.customer.first_name
)

# Chained partitioning followed by unwinding the child collection '.a'
result = lines.PARTITION(
    name="a", 
    by=(c_id, c_name)
).PARTITION(
    name="b", 
    by=c_id
).CALCULATE(
    n=COUNT(a)
).a

Error Traceback

Traceback (most recent call last):
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/sqlglot/execute_relational.py", line 117, in convert_relation_to_sql
    glot_expr = apply_sqlglot_optimizer(glot_expr, relational, sqlglot_dialect)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/sqlglot/execute_relational.py", line 164, in apply_sqlglot_optimizer
    glot_expr = qualify(glot_expr, **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/sqlglot/override_qualify.py", line 119, in qualify
    expression = qualify_columns_func(
                 ^^^^^^^^^^^^^^^^^^^^^
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/sqlglot/override_qualify.py", line 390, in qualify_columns_func
    _qualify_columns(
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/sqlglot/override_qualify.py", line 435, in _qualify_columns
    raise OptimizeError(f"Unknown column: {column_name}")
sqlglot.errors.OptimizeError: Unknown column: customer_id_5

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/juankx/svc/pydough_test/pydough_test.py", line 180, in <module>
    sql = pydough.to_sql(unqualified_node, session=tester.session)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/evaluation/evaluate_unqualified.py", line 169, in to_sql
    return convert_relation_to_sql(relational, session, max_rows)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/juankx/svc/pydough_test/.venv/lib/python3.12/site-packages/pydough/sqlglot/execute_relational.py", line 121, in convert_relation_to_sql
    raise pydough.active_session.error_builder.sql_runtime_failure(
pydough.errors.error_types.PyDoughSQLException: SQL query optimization failed. Please check the query syntax:
SELECT DISTINCT
  c_id,
  first_name AS c_name
FROM (
  SELECT
    _s15.customer_id_5 AS c_id,
    _s15.first_name
  FROM (
    SELECT DISTINCT
      customer_id
    FROM (
      SELECT
        _s4.customer_id,
        _s5.name
      FROM (
        SELECT
          _s1.customer_id,
          _s0.track_id
        FROM (
          SELECT
            invoice_id,
            track_id
          FROM chinook.public.invoice_line
        ) AS _s0
        INNER JOIN (
          SELECT
            customer_id,
            invoice_id
          FROM chinook.public.invoice
        ) AS _s1
          ON _s0.invoice_id = _s1.invoice_id
      ) AS _s4
      LEFT JOIN (
        SELECT
          _s3.name,
          _s2.track_id
        FROM (
          SELECT
            genre_id,
            track_id
          FROM chinook.public.track
        ) AS _s2
        INNER JOIN (
          SELECT
            genre_id,
            name
          FROM chinook.public.genre
        ) AS _s3
          ON _s2.genre_id = _s3.genre_id
      ) AS _s5
        ON _s4.track_id = _s5.track_id
    ) AS _t1
    WHERE
      name IN ('Rock')
  ) AS _s14
  INNER JOIN (
    SELECT
      customer_id_5 AS c_id,
      first_name
    FROM (
      SELECT
        _s12.customer_id_5,
        _s12.first_name,
        _s13.name
      FROM (
        SELECT
          _s9.customer_id_5,
          _s9.first_name,
          _s8.track_id
        FROM (
          SELECT
            invoice_id,
            track_id
          FROM chinook.public.invoice_line
        ) AS _s8
        INNER JOIN (
          SELECT
            _s7.customer_id AS customer_id_5,
            _s7.first_name,
            _s6.invoice_id
          FROM (
            SELECT
              customer_id,
              invoice_id
            FROM chinook.public.invoice
          ) AS _s6
          INNER JOIN (
            SELECT
              customer_id,
              first_name
            FROM chinook.public.customer
          ) AS _s7
            ON _s6.customer_id = _s7.customer_id
        ) AS _s9
          ON _s8.invoice_id = _s9.invoice_id
      ) AS _s12
      LEFT JOIN (
        SELECT
          _s11.name,
          _s10.track_id
        FROM (
          SELECT
            genre_id,
            track_id
          FROM chinook.public.track
        ) AS _s10
        INNER JOIN (
          SELECT
            genre_id,
            name
          FROM chinook.public.genre
        ) AS _s11
          ON _s10.genre_id = _s11.genre_id
      ) AS _s13
        ON _s12.track_id = _s13.track_id
    ) AS _t2
    WHERE
      name IN ('Rock')
  ) AS _s15
    ON _s14.customer_id = _s15.c_id
) AS _t0
Error: Unknown column: customer_id_5

Root Cause Analysis

Inside the generated SQLGlot AST before column qualification, subquery _s15 projects customer_id_5 with an alias c_id:

SELECT
  _s15.customer_id_5 AS c_id,  -- <--- Problem: '_s15' does not export 'customer_id_5', only 'c_id'
  _s15.first_name
FROM ...
INNER JOIN (
  SELECT
    customer_id_5 AS c_id,     -- <--- Column is aliased to 'c_id' here
    first_name
  FROM (...) AS _t2
  WHERE name IN ('Rock')
) AS _s15
  ON _s14.customer_id = _s15.c_id

Because _s15 renames customer_id_5 to c_id, customer_id_5 is no longer a valid column identifier on _s15. When SQLGlot's qualification pass (qualify_columns_func) validates column bindings across derived table boundaries, it fails with OptimizeError: Unknown column: customer_id_5.

Expected Behavior

PyDough should consistently track column renaming across chained partition operations so that outer SELECT clauses reference the exported subquery alias (_s15.c_id) rather than the internal upstream column identifier (_s15.customer_id_5).

Follow-Up: Additional Context & Working Counter-Example

1. Simplified Equivalent SQL

For reference, here is a simplified version of the Pydough generated SQL query without requiring double-nested subquery projections:

SELECT DISTINCT
  c.customer_id AS c_id,
  c.first_name AS c_name
FROM chinook.public.customer c
JOIN chinook.public.invoice i 
  ON c.customer_id = i.customer_id
JOIN chinook.public.invoice_line il 
  ON i.invoice_id = il.invoice_id
JOIN chinook.public.track t 
  ON il.track_id = t.track_id
JOIN chinook.public.genre g 
  ON t.genre_id = g.genre_id
WHERE g.name = 'Rock';

2. Working Counter-Example (TPCH)

Chained partitioning followed by child collection unwinding (.a) works as expected in TPCH when partitioning directly on root collection columns:

# Works OK without error
# Filter line items using 2-level parent relationships and calculate customer attributes
l_items = lines.WHERE(
    HAS(order) & ISIN(part.brand, ("Brand#11",))
).CALCULATE(
    c_id=order.customer.key,
    c_name=order.customer.name
)

# Chained partitioning on multi-level calculated terms followed by unwinding '.a'
result = l_items.PARTITION(
    name="a", 
    by=(c_id, c_name)
).PARTITION(
    name="b", 
    by=c_id
).CALCULATE(
    n=COUNT(a)
).a

Key Insight

The reason why this query fails in Chinook (pear_music) could be that the intermediate table and parent table share the exact same physical column name, whereas in TPCH they do not.

In Chinook:

invoice table has column customer_id.
customer table has column customer_id.

When PyDough generates the join between invoice (_s6) and customer (_s7), both tables contain a column named customer_id. To resolve the collision inside subquery _s9, PyDough automatically renames customer.customer_id to customer_id_5:

-- Inside _s9 in Chinook:
SELECT
  _s7.customer_id AS customer_id_5,  -- Renamed to avoid collision with _s6.customer_id
  _s7.first_name,
  _s6.invoice_id
FROM chinook.public.invoice AS _s6
INNER JOIN chinook.public.customer AS _s7
  ON _s6.customer_id = _s7.customer_id

Later, in subquery _s15, customer_id_5 is aliased to c_id (customer_id_5 AS c_id). When the outer query attempts to select from _s15, PyDough's generator incorrectly references _s15.customer_id_5 instead of _s15.c_id, triggering the Unknown column: customer_id_5 error.

In standard TPCH:

orders table has column o_custkey.
customer table has column c_custkey.

I was unable to reproduce on main and confirmed via bisecting that it's reproducible on release tag commit so it was fixed by that PR that was merged yesterday.

@hadia206

Thanks @hadia206
I can confirm this issue is fixed on master after Fix HAS(...) == 1 breaking multi-level aggregations (#575)- #589
#589
Tested with pear_music database in postgres where it was originally identified.

Navigation Menu

Repository navigation

Column qualification fails on chained partitions due to unpropagated subquery column renamings #591

Dataset

Minimal Reproducible Example

Root Cause Analysis

Expected Behavior

Follow-Up: Additional Context & Working Counter-Example

1. Simplified Equivalent SQL

2. Working Counter-Example (TPCH)

Key Insight

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Column qualification fails on chained partitions due to unpropagated subquery column renamings #591

Description

Dataset

Minimal Reproducible Example

Root Cause Analysis

Expected Behavior

Activity

juankx-bodo commented on Oct 8, 2026

Follow-Up: Additional Context & Working Counter-Example

1. Simplified Equivalent SQL

2. Working Counter-Example (TPCH)

Key Insight

hadia206 commented on Oct 9, 2026

juankx-bodo commented on Oct 9, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions