---
phase: 30-derived-metrics
plan: 02
type: execute
wave: 2
depends_on: [30-01]
files_modified:
  - src/expand.rs
  - test/sql/phase30_derived_metrics.test
  - test/sql/TEST_LIST
  - tests/parse_proptest.rs
autonomous: true
requirements: [DRV-02, DRV-03]

must_haves:
  truths:
    - "Derived metric expressions are resolved by inlining referenced metrics' aggregate expressions at expansion time"
    - "Stacked derived metrics (derived referencing derived) resolve correctly in topological order"
    - "Facts are inlined into base metric expressions BEFORE those expressions are used to resolve derived metrics"
    - "Join resolution includes tables needed by base metrics referenced through derived metrics"
    - "Parenthesization prevents operator precedence errors when metric expressions are inlined"
    - "DESCRIBE shows derived metrics alongside base metrics in the metrics column"
    - "just test-all passes"
  artifacts:
    - path: "src/expand.rs"
      provides: "inline_derived_metrics, toposort_derived functions; updated expand() and resolve_joins_pkfk"
      contains: "fn inline_derived_metrics"
    - path: "test/sql/phase30_derived_metrics.test"
      provides: "End-to-end sqllogictest for derived metrics DDL, query, stacking, errors"
    - path: "test/sql/TEST_LIST"
      provides: "Updated test list with phase30_derived_metrics.test"
      contains: "phase30_derived_metrics"
    - path: "tests/parse_proptest.rs"
      provides: "Proptest for derived metric expression substitution edge cases"
  key_links:
    - from: "src/expand.rs"
      to: "src/expand.rs"
      via: "inline_derived_metrics called from expand() after fact inlining setup"
      pattern: "inline_derived_metrics"
    - from: "src/expand.rs"
      to: "src/expand.rs"
      via: "resolve_joins_pkfk collects source_tables transitively through derived metric references"
      pattern: "source_table.*derived"
    - from: "test/sql/phase30_derived_metrics.test"
      to: "src/expand.rs"
      via: "sqllogictest verifies arithmetic correctness of derived metric expansion"
      pattern: "SELECT.*profit.*margin"
---

<objective>
Implement derived metric expression inlining in the expansion pipeline and add end-to-end tests.

Purpose: Complete the derived metrics feature by making derived metric expressions expand to correct SQL at query time. After Plan 01 established parsing and validation, this plan implements the runtime expansion logic: metric-to-metric inlining with topological ordering, join resolution through derived references, and comprehensive testing (sqllogictest + proptests).

Output: expand.rs with derived metric inlining pipeline, phase30_derived_metrics.test sqllogictest, parse_proptest.rs with derived metric proptests.
</objective>

<execution_context>
@/Users/paul/.claude/get-shit-done/workflows/execute-plan.md
@/Users/paul/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/30-derived-metrics/30-RESEARCH.md

# Phase 29 Plan 02 -- directly relevant: fact inlining pattern
@.planning/phases/29-facts-clause-hierarchies/29-02-SUMMARY.md

# Plan 01 SUMMARY for parsing/validation context
@.planning/phases/30-derived-metrics/30-01-SUMMARY.md

@src/expand.rs
@src/model.rs

<interfaces>
<!-- Key types and contracts the executor needs. Extracted from codebase. -->

From src/model.rs:
```rust
pub struct Metric {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,  // None = derived metric
    pub output_type: Option<String>,
}

pub struct Fact {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
}
```

From src/expand.rs (existing functions to reuse):
```rust
fn replace_word_boundary(haystack: &str, needle: &str, replacement: &str) -> String;
fn toposort_facts(facts: &[Fact]) -> Result<Vec<usize>, String>;
fn inline_facts(expr: &str, facts: &[Fact], topo_order: &[usize]) -> String;

fn resolve_joins_pkfk(
    def: &SemanticViewDefinition,
    resolved_dims: &[&Dimension],
    resolved_mets: &[&Metric],  // Derived metrics have source_table: None
) -> Vec<String>;

pub fn expand(
    view_name: &str,
    def: &SemanticViewDefinition,
    req: &QueryRequest,
) -> Result<String, ExpandError>;
```

From src/expand.rs -- current metric SELECT construction (lines ~587-601):
```rust
for met in &resolved_mets {
    let resolved_expr = if def.facts.is_empty() {
        met.expr.clone()
    } else {
        inline_facts(&met.expr, &def.facts, &topo_order)
    };
    let final_expr = if let Some(ref type_str) = met.output_type {
        format!("CAST({resolved_expr} AS {type_str})")
    } else {
        resolved_expr
    };
    select_items.push(format!("    {} AS {}", final_expr, quote_ident(&met.name)));
}
```

From src/graph.rs (created in Plan 01):
```rust
pub fn find_fact_references<'a>(expr: &str, fact_names: &[&'a str]) -> Vec<&'a str>;
// Reuse for finding metric name references in derived metric expressions
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Implement derived metric inlining in expand.rs</name>
  <files>src/expand.rs</files>
  <behavior>
    - Test: inline_derived_metrics with one base metric (revenue=SUM(x)) and one derived (profit=revenue-cost) produces profit -> "(SUM(x)) - (SUM(y))"
    - Test: inline_derived_metrics with stacked derived (profit=revenue-cost, margin=profit/revenue) resolves both correctly
    - Test: inline_derived_metrics with facts: base metric has fact reference, derived metric references the base. Chain: fact -> base metric -> derived metric
    - Test: inline_derived_metrics parenthesizes inlined expressions to prevent operator precedence errors (profit=a-b, margin=profit/a -> "(result_a - result_b) / (result_a)")
    - Test: inline_derived_metrics word-boundary safety: metric named "revenue" does not match "revenue_total"
    - Test: expand() with derived metric produces correct SQL with the derived expression in SELECT and no extra GROUP BY for derived metrics
    - Test: expand() with derived-only query (no base metrics requested) produces correct SQL including needed JOINs
    - Test: resolve_joins_pkfk includes source_tables of base metrics referenced by derived metrics (transitive join resolution)
    - Test: expand() with derived metric that references metrics from different tables includes all needed JOINs
  </behavior>
  <action>
    Modify expand.rs to support derived metric expression inlining. Three changes needed:

    **Change 1: Add `inline_derived_metrics` function**

    Create `inline_derived_metrics(metrics: &[Metric], facts: &[Fact], fact_topo_order: &[usize]) -> HashMap<String, String>` that:
    1. Builds a `resolved` HashMap mapping metric name (lowercased) to its fully-resolved aggregate expression
    2. For base metrics (source_table.is_some()): inline facts into their expression, store in `resolved`
    3. For derived metrics (source_table.is_none()): collect into a separate list, topologically sort them by inter-derived-metric dependencies, then inline in order. For each derived metric:
       - Start with its raw expression
       - Replace each known metric name reference (all entries in `resolved` so far) with the parenthesized resolved expression: `replace_word_boundary(&expr, name, &format!("({resolved_expr})"))`
       - Add the resolved expression to `resolved`
    4. Return the `resolved` HashMap

    Create `toposort_derived(derived: &[(usize, &Metric)], all_metric_names: &[&str]) -> Vec<usize>` using Kahn's algorithm (same pattern as `toposort_facts`):
    - Build adjacency list: for each derived metric, find references to OTHER derived metric names in its expression (using word-boundary scan)
    - References to base metrics are NOT edges in this DAG (base metrics are already resolved)
    - Topological sort returns indices into the `derived` slice in resolution order

    **Change 2: Update `expand()` to use resolved expressions**

    Replace the current per-metric `inline_facts` call in the SELECT construction loop with a lookup into the pre-computed `inline_derived_metrics` result:

    ```rust
    // Before the SELECT loop:
    let resolved_exprs = inline_derived_metrics(&def.metrics, &def.facts, &topo_order);

    // In the SELECT loop for metrics:
    for met in &resolved_mets {
        let resolved_expr = resolved_exprs
            .get(&met.name.to_ascii_lowercase())
            .cloned()
            .unwrap_or_else(|| met.expr.clone());
        // ... rest of output_type wrapping unchanged
    }
    ```

    This replaces the current inline_facts-per-metric approach. The `inline_derived_metrics` function handles both base metrics (inline facts) and derived metrics (inline facts then inline metric references). Base metrics still get fact inlining through this path.

    **Change 3: Update `resolve_joins_pkfk` for derived metric join resolution**

    Derived metrics have `source_table: None`, so the current loop that collects needed aliases from `met.source_table` misses them. Add logic to resolve derived metrics' transitive table dependencies:

    For each resolved metric that has `source_table: None` (derived):
    1. Find all metric names referenced in the derived metric's expression (using word-boundary scan against all metric names)
    2. For each referenced metric, if it has a `source_table`, add that to the needed aliases
    3. If the referenced metric is also derived, recurse (or just look up its resolved expression which already contains table-qualified column references -- the JOIN resolution will pick up table aliases from the inlined expression)

    The simplest correct approach: when a derived metric is in `resolved_mets`, find all base metrics it references (directly or transitively) and add their source_tables to the needed set. This can be done by scanning the derived metric's RESOLVED expression (after inlining) for table alias references, but that is fragile. Better: build a small helper that walks the metric dependency graph to collect base metric source_tables.

    IMPORTANT: Derived metric expressions are NOT aggregates themselves -- they are arithmetic combinations of aggregates. The SELECT item for a derived metric is its fully-resolved expression (which contains aggregate calls like SUM(...)), NOT wrapped in another aggregate. The current code already handles this correctly because it just uses the resolved expression directly.

    IMPORTANT: The `topo_order` for facts is computed ONCE and passed to `inline_derived_metrics`. Do not re-compute it.
  </action>
  <verify>
    <automated>cargo test expand::tests -- --nocapture 2>&1 | tail -30</automated>
  </verify>
  <done>
    - inline_derived_metrics resolves all metric expressions (base: facts inlined, derived: facts+metrics inlined)
    - Stacked derived metrics resolve in correct topological order
    - Parenthesization prevents operator precedence errors
    - Word-boundary replacement prevents substring collisions
    - expand() uses pre-computed resolved expressions instead of per-metric inline_facts
    - resolve_joins_pkfk includes transitive table dependencies from derived metrics
    - All existing expand tests pass unchanged
    - cargo test passes clean
  </done>
</task>

<task type="auto">
  <name>Task 2: End-to-end sqllogictest, proptests, and test infrastructure for derived metrics</name>
  <files>test/sql/phase30_derived_metrics.test, test/sql/TEST_LIST, tests/parse_proptest.rs</files>
  <action>
    Create comprehensive end-to-end tests for derived metrics.

    **1. Create `test/sql/phase30_derived_metrics.test`**

    Test cases (sqllogictest format):
    a. **Setup**: CREATE TABLE orders (id INT, region VARCHAR); CREATE TABLE line_items (id INT, order_id INT, extended_price DOUBLE, discount DOUBLE, unit_cost DOUBLE);
       INSERT known data rows (at least 3-5 rows with calculable results).

    b. **Basic derived metric**: CREATE SEMANTIC VIEW with base metrics (revenue=SUM(li.net_price), cost=SUM(li.unit_cost)) and derived metric (profit=revenue-cost). Query with dimensions=['region'] and metrics=['profit']. Verify arithmetic result matches expected.

    c. **Stacked derived metrics**: Add margin=profit/revenue*100. Query with metrics=['profit', 'margin']. Verify both results.

    d. **Derived metric alongside base metrics**: Query with metrics=['revenue', 'cost', 'profit']. Verify all three are correct.

    e. **Derived metrics with facts**: Add FACTS clause (li.net_price AS li.extended_price * (1 - li.discount)). Verify the fact -> base metric -> derived metric chain produces correct results.

    f. **Derived metric only query** (no base metrics requested): Query with metrics=['profit'] only. Verify JOINs are correctly resolved and result is correct.

    g. **DESCRIBE shows derived metrics**: DESCRIBE SEMANTIC VIEW shows both base and derived metrics in the metrics JSON column. Verify derived metrics appear with source_table null.

    h. **Error: cycle in derived metrics**: statement error with expected message pattern for cycle.

    i. **Error: unknown metric reference**: statement error with expected message pattern.

    j. **Error: aggregate in derived metric**: statement error with "must not contain aggregate function" pattern.

    k. **Error: single-word aggregate detection**: SUM(revenue), COUNT(revenue), AVG(revenue) all rejected.

    Use `query R` for DOUBLE results and `query IR` etc. for mixed-type results. Use precise expected values (calculate by hand from the inserted data).

    **2. Update `test/sql/TEST_LIST`**

    Add `test/sql/phase30_derived_metrics.test` to the list.

    **3. Add proptests to `tests/parse_proptest.rs`**

    Add proptest for derived metric parsing edge cases:
    - Generate random metric names (alphanumeric + underscore, 1-20 chars)
    - Generate random arithmetic expressions using those names with operators (+, -, *, /)
    - Parse through `parse_metrics_clause` and verify no panics
    - Test with mixed qualified and unqualified entries

    Add proptest for derived metric expression substitution:
    - Generate random metric names and expressions
    - Run through `replace_word_boundary` and verify no panics, output length is reasonable

    **4. Run full test suite**

    After all test files are created, run `just build && just test-all` to verify everything passes including the sqllogictest and DuckLake CI.
  </action>
  <verify>
    <automated>just test-all</automated>
  </verify>
  <done>
    - phase30_derived_metrics.test covers: basic derived, stacking, mixed base+derived queries, facts+derived chain, derived-only query, DESCRIBE, error cases (cycle, unknown ref, aggregate)
    - TEST_LIST updated with phase30_derived_metrics.test
    - Proptests cover adversarial derived metric parsing and expression substitution
    - `just test-all` passes (cargo test + sqllogictest + DuckLake CI)
    - All existing tests in other test files pass unchanged
  </done>
</task>

</tasks>

<verification>
```bash
# Unit tests for expand module (new + existing)
cargo test expand::tests

# Full Rust test suite including proptests
cargo test

# Build extension and run sqllogictest
just build && just test-sql

# Full quality gate
just test-all
```
</verification>

<success_criteria>
- Derived metrics expand to correct SQL with aggregate expressions inlined
- Stacked derived metrics resolve in topological order with correct parenthesization
- Facts are inlined into base metrics before derived metric resolution
- Join resolution includes tables needed by base metrics referenced through derived metrics
- End-to-end sqllogictest verifies arithmetic correctness against known data
- Proptests verify no panics on adversarial input
- `just test-all` passes (full quality gate per CLAUDE.md)
</success_criteria>

<output>
After completion, create `.planning/phases/30-derived-metrics/30-02-SUMMARY.md`
</output>
