`HAS(...) == 1` breaks multi-level aggregations when `always matches: false`

[juankx-bodo]

When a WHERE filter uses HAS(...) == 1 instead of HAS(...), PyDough fails to preserve the relationship context for downstream multi-level aggregations (e.g., SUM(child.grandchild.column)). During SQL translation, PyDough isolates the existence check into a CTE that strips the necessary joins, resulting in a invalid SQL error where the target column cannot be found (e.g., Error: column "<col>" does not exist) and producing malformed double aggregates like COALESCE(SUM(SUM(...)), 0).

This issue occurs when relationships have "always matches": false in their metadata definition.

PyDough should handle HAS(...) == 1 identically to HAS(...) regardless of whether "always matches" is true or false

Root Cause

• Expression Pattern-Matching: PyDough optimizes HAS(relationship) into a standard EXISTS subquery. However, HAS(relationship) == 1 is evaluated different.
• Context Stripping: To evaluate the scalar equality, PyDough builds an isolated existence CTE (e.g., _s1) that projects only the key required for the existence check.
• Lost Relational Context: When lowering subsequent multi-level aggregations (such as customer.invoice.total or customers.orders.total_price), PyDough assumes the join path was preserved. Because the CTE dropped the child table join, lowering fails with missing column errors and malformed nested aggregate expressions (SUM(SUM(...))).

Original Finding (pear-music database)

Metadata Configuration

In the pear-music dataset graph, the employee to customer relationship is defined with "always matches": false:

      {
        "name": "customer",
        "type": "simple join",
        "parent collection": "employee",
        "child collection": "customer",
        "keys": {
          "employee_id": [
            "support_rep_id"
          ]
        },
        "singular": false,
        "always matches": false,
        "description": "Customers assigned to the employee for support."
      },
      {
        "name": "employee",
        "type": "reverse",
        "original parent": "employee",
        "original property": "customer",
        "singular": true,
        "always matches": false,
        "description": "The support representative assigned to the customer."
      },

PyDough Code

# Reusable expressions
has_customers_expr = HAS(customers) == 1
nation_name_expr = name
total_orders_expr = SUM(customers.orders.total_price)

# Filter nations using HAS(...) == 1 and aggregate over 2-level relationship
nation_info = nations.WHERE(has_customers_expr).CALCULATE(
    nation_name=nation_name_expr,
    total_orders=total_orders_expr,
)

Execution Error & SQL Output

Executing pydough.to_sql() fails with:

Error: column "total" does not exist
LINE 10:   COALESCE(SUM(SUM(total)), 0) AS total_invoice

Generated malformed SQL:

WITH _s1 AS (
  SELECT
    MAX(support_rep_id) AS anything_support_rep_id
  FROM chinook.public.customer
  GROUP BY
    customer_id
)
SELECT
  CONCAT_WS(' ', MAX(employee.first_name), MAX(employee.last_name)) AS employee_name,
  COALESCE(SUM(SUM(total)), 0) AS total_invoice
FROM chinook.public.employee AS employee
JOIN _s1 AS _s1
  ON _s1.anything_support_rep_id = employee.employee_id

Workaround

Changing HAS(customer) == 1 to HAS(customer) correctly forces PyDough to optimize the join graph:

WITH _s3 AS (
  SELECT
    MAX(customer.support_rep_id) AS anything_support_rep_id,
    SUM(invoice.total) AS sum_total
  FROM chinook.public.customer AS customer
  LEFT JOIN chinook.public.invoice AS invoice
    ON customer.customer_id = invoice.customer_id
  GROUP BY
    customer.customer_id
)
SELECT
  CONCAT_WS(' ', MAX(employee.first_name), MAX(employee.last_name)) AS employee_name,
  COALESCE(SUM(_s3.sum_total), 0) AS total_invoice
FROM chinook.public.employee AS employee
JOIN _s3 AS _s3
  ON _s3.anything_support_rep_id = employee.employee_id
GROUP BY
  _s3.anything_support_rep_id

[juankx-bodo]

Reproducing on TPCH

Standard TPCH metadata (tpch_demo_graph.json) masks this edge case because nations -> customers and other relationships are hardcoded with "always matches": true. To reproduce this issue on TPCH, the metadata must be updated to reproduce relational constraints where foreign keys can be nullable.

Step 1: Update TPCH Metadata

Set "always matches": false on both directions of the nations -> customers relationship:

      {
        "type": "simple join",
        "name": "customers",
        "parent collection": "nations",
        "child collection": "customers",
        "singular": false,
        "always matches": false,
        "keys": {"key": ["nation_key"]},
        "description": "The customers belonging to a nation",
        "synonyms": ["citizens", "residents", "inhabitants", "consumers", "users", "buyers", "occupants"]
      },
      {
        "type": "reverse",
        "name": "nation",
        "original parent": "nations",
        "original property": "customers",
        "singular": true,
        "always matches": false,
        "description": "The nation that a customer belongs to",
        "synonyms": ["country", "state", "home"]
      },

Step 2: Minimal Reproducible Code

# Reusable expressions
has_customers_expr = HAS(customers) == 1
nation_name_expr = name
total_orders_expr = SUM(customers.orders.total_price)

# Filter nations using HAS(...) == 1 and aggregate over 2-level relationship
nation_info = nations.WHERE(has_customers_expr).CALCULATE(
    nation_name=nation_name_expr,
    total_orders=total_orders_expr,
)

Step 3: Resulting Error in TPCH

Postgres:

Error: column "o_totalprice" does not exist
LINE 10:   COALESCE(SUM(SUM(o_totalprice)), 0) AS total_orders

SQLite:

Error: no such column: o_totalprice

Generated SQL:

WITH _s1 AS (
  SELECT
    MAX(c_nationkey) AS anything_c_nationkey
  FROM main.customer
  GROUP BY
    c_custkey
)
SELECT
  MAX(nation.n_name) AS nation_name,
  COALESCE(SUM(SUM(o_totalprice)), 0) AS total_orders
FROM main.nation AS nation
JOIN _s1 AS _s1
  ON _s1.anything_c_nationkey = nation.n_nationkey

Context & Metadata Design Note

The current TPCH metadata implementation contain over-optimized graph metadata (always matches: true) tailored strictly to static benchmark data. In production databases:

• 1 -> N  relationships where the foreign key on the N-side is nullable requires "always matches": false on both sides.
• Data may change so metadata definitions must be based on database constraints and what could be possible according with those constraints rather than snapshot data distributions.