Metrics: an aggregate nested in another expression (e.g. APPROX_QUANTILES(x, 2)[OFFSET(1)]) loses its alias in the rewrite #6169

Version: sqlmesh 0.236.1 with sqlglot 30.8.0. The metric code in 0.236.4 is identical.

The metrics docs say the expression "can be any SQL statement that contains an aggregation function". When the aggregate is not the outermost node of the expression, the rewritten query references a column that doesn't exist.

Reproduction

-- models/orders.sql
MODEL (name sushi.orders, kind FULL);
SELECT 1 AS customer_id, 10 AS amount

-- metrics/m.sql
METRIC (name total_amount,  expression SUM(sushi.orders.amount));
METRIC (name median_amount, expression APPROX_QUANTILES(sushi.orders.amount, 2)[OFFSET(1)]);

ctx.rewrite("SELECT customer_id, METRIC(median_amount) FROM __semantic.__table GROUP BY customer_id").sql("bigquery")

total_amount is fine:

SELECT __table.customer_id AS customer_id, total_amount AS total_amount
FROM (SELECT customer_id, SUM(sushi__orders.amount) AS total_amount FROM sushi.orders AS sushi__orders GROUP BY customer_id) AS __table

median_amount loses its alias in the inner select, so the outer median_amount is unresolved:

SELECT __table.customer_id AS customer_id, median_amount AS median_amount
FROM (SELECT customer_id, APPROX_QUANTILES(sushi__orders.amount, 2)[OFFSET(1)] FROM sushi.orders AS sushi__orders GROUP BY customer_id) AS __table

Wrapping the aggregate (CAST(... AS FLOAT64), IFNULL(..., NULL), parentheses) gives the same output. The wrapper is dropped too.

Cause

Metric.aggs in sqlmesh/core/metric/definition.py keys each aggregate by agg.parent. That relies on the parent being the Alias that to_metric adds (exp.alias_(self.expression, self.name)). When the aggregate is nested (here under Bracket), the parent is the bracket, so the inner projection has no alias. formula then reduces to the bare metric name, so the wrapping expression is lost from the outer query as well.

Related

ARRAY_AGG(sushi.orders.amount ORDER BY sushi.orders.amount) fails with Could not infer a measures table. _get_measure_and_dim_tables.is_measure climbs If/Case/Binary/Paren/Distinct but not Order, so a column under an aggregate's ORDER BY is never taken as the measure.

Why it matters

On BigQuery there is no exact median aggregate (PERCENTILE_CONT is analytic only). So an exact median, or any order statistic, needs either an indexed APPROX_QUANTILES or a sorted ARRAY_AGG, and neither can be expressed as a metric today. A derived metric over a base aggregate (base[OFFSET(1)], where base is APPROX_QUANTILES(x, 2)) does rewrite correctly. So the expected behaviour would be for the nested case to be split the same way: alias the aggregate, and keep the wrapper in the formula.