`Context does not contain expression $0.<expr>` when accessing parent CALCULATE fields via relationship prefix

[juankx-bodo]

Summary

Referencing a previously calculated parent expression via a relationship prefix (e.g., customer.cust_name) fails during SQL lowering with a context error (Context does not contain expression $0.cust_name).

Additionally, there are scoping and error-handling inconsistencies:
(1) Term resolution suggests cust_name as a valid property on customer, but referencing it triggers a lower-level context lookup failure.
(2) pydough.explain_llm() completes successfully ("error": false) for code that fails during SQL translation.

Minimal Reproducible Example

# 'top' calculates 'cust_name' on customers
top = customers.CALCULATE(cust_name=name)

# Referencing 'customer.cust_name' from orders causes a lowering error
result = top.orders.CALCULATE(
    x=customer.cust_name
).PARTITION(name="g", by=x)

Error Output

Context does not contain expression $0.cust_name. Available expressions: [$0.account_balance, $0.address, $0.comment, $0.key, $0.market_segment, $0.name, $0.nation_key, $0.phone, BACK(1).account_balance, BACK(1).address, BACK(1).comment, BACK(1).cust_name, BACK(1).key, BACK(1).market_segment, BACK(1).name, BACK(1).nation_key, BACK(1).phone, clerk, comment, customer_key, key, order_date, order_priority, order_status, ship_priority, total_price]

Key Observations & Inconsistencies

• Scope Resolution Mismatch ($0 vs BACK(1)):
  When evaluating customer.cust_name, PyDough resolves customer to the raw relation context ($0), which only contains raw schema columns ($0.name, $0.key, etc.). However, the calculated property cust_name exists in the parent context (BACK(1).cust_name).

• Misleading Suggestion for Invalid Properties:
  If an invalid property name is used (e.g., x = customer.cust_name_1), PyDough raises an unrecognized_term error that explicitly lists cust_name as a valid candidate:
  Unrecognized term of customers.CALCULATE(cust_name=name).orders.customer: 'cust_name_1'. Did you mean: cust_name, name, comment?
  However, following the suggestion and using customer.cust_name crashes during SQL translation.

• explain_llm() Discrepancy:
  Running pydough.explain_llm() on x = customer.cust_name returns "error": false and marks x as a valid ChildReference, failing to detect the context error that occurs during SQL generation.

Expected Behavior

Either:
(1) PyDough should properly resolve calculated terms from parent contexts when qualified with a relationship name (customer.cust_name), OR
(2) Term resolution should consistently flag customer.cust_name as an invalid reference during parsing/validation before reaching SQL lowering.

Workarounds

• Direct Context Access: Access cust_name directly without the customer. prefix (x = cust_name), which correctly resolves to BACK(1).cust_name.
• Raw Field Navigation: Access underlying schema fields directly through the relationship (x = customer.name).

explain_llm() output

{
  "error": false,
  "query_summary": "Accesses 'customers', partitioned by x, selecting x (customer.cust_name).",
  "steps": [
    {
      "order": 1,
      "type": "GlobalContext",
      "description": "Entry point: the graph-level context.",
      "debug": {
        "available_terms": {
          "expressions": [],
          "collections": [
            "customers",
            "lines",
            "nations",
            "orders",
            "parts",
            "regions",
            "suppliers",
            "supply_records"
          ]
        }
      },
      "notes": []
    },
    {
      "order": 2,
      "type": "TableCollection",
      "description": "Accesses the 'customers' collection.",
      "collection": "customers",
      "debug": {
        "available_terms": {
          "expressions": [
            "account_balance",
            "address",
            "comment",
            "key",
            "market_segment",
            "name",
            "nation_key",
            "phone"
          ],
          "collections": [
            "nation",
            "orders"
          ]
        }
      },
      "notes": []
    },
    {
      "order": 3,
      "type": "Calculate",
      "description": "Adds computed expressions to the collection.",
      "terms": [
        "cust_name"
      ],
      "term_details": {
        "cust_name": {
          "kind": "Reference",
          "term_name": "name"
        }
      },
      "debug": {
        "available_terms": {
          "expressions": [
            "account_balance",
            "address",
            "comment",
            "cust_name",
            "key",
            "market_segment",
            "name",
            "nation_key",
            "phone"
          ],
          "collections": [
            "nation",
            "orders"
          ]
        }
      },
      "notes": []
    },
    {
      "order": 4,
      "type": "Calculate",
      "description": "Adds computed expressions to the collection.",
      "terms": [
        "x"
      ],
      "term_details": {
        "x": {
          "kind": "ChildReference",
          "text": "customer.cust_name",
          "term_name": "cust_name",
          "child_idx": 0
        }
      },
      "debug": {
        "available_terms": {
          "expressions": [
            "clerk",
            "comment",
            "cust_name",
            "customer_key",
            "key",
            "order_date",
            "order_priority",
            "order_status",
            "ship_priority",
            "total_price",
            "x"
          ],
          "collections": [
            "customer",
            "lines"
          ]
        }
      },
      "notes": [
        "Note: 'x' accesses 'customer.cust_name' via implicit singular relationship navigation \u2014 scoping to the parent row is enforced by the relationship, not by an explicit filter."
      ]
    },
    {
      "order": 5,
      "type": "PartitionBy",
      "description": "Partitions the collection by ['x'].",
      "keys": [
        "x"
      ],
      "name": "g",
      "child_name": "orders",
      "child_available_terms": {
        "expressions": [
          "clerk",
          "comment",
          "cust_name",
          "customer_key",
          "key",
          "order_date",
          "order_priority",
          "order_status",
          "ship_priority",
          "total_price",
          "x"
        ],
        "collections": [
          "customer",
          "lines"
        ]
      },
      "debug": {
        "available_terms": {
          "expressions": [
            "x"
          ],
          "collections": [
            "orders"
          ]
        }
      },
      "notes": [
        "The partition key(s) ['x'] identify each group and are accessible at the group level. Row-level data is accessible via the child collection 'orders'; aggregating over it (e.g. COUNT('orders')) operates on the rows within that group."
      ]
    }
  ],
  "schema": {
    "source_collection": "customers",
    "available_expressions": [
      "x"
    ],
    "output_columns": [],
    "column_types": {},
    "ordering": [],
    "limit": null
  }
}