---
phase: 57-introspection-diagnostics
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/parse.rs
  - src/ddl/mod.rs
  - src/ddl/describe.rs
  - src/ddl/show_materializations.rs
  - src/query/explain.rs
  - src/expand/materialization.rs
  - src/lib.rs
  - test/sql/phase57_introspection.test
  - test/sql/TEST_LIST
autonomous: true
requirements: [INTR-01, INTR-02, INTR-03]

must_haves:
  truths:
    - "explain_semantic_view() output includes a '-- Materialization: <name>' header line when a materialization covers the request"
    - "explain_semantic_view() output includes '-- Materialization: none' when no materialization matches"
    - "DESCRIBE SEMANTIC VIEW includes MATERIALIZATION rows with TABLE, DIMENSIONS, METRICS properties"
    - "DESCRIBE SEMANTIC VIEW without materializations produces no MATERIALIZATION rows (unchanged output)"
    - "SHOW SEMANTIC MATERIALIZATIONS IN view_name lists materialization rows with name, table, dimensions, metrics"
    - "SHOW SEMANTIC MATERIALIZATIONS (cross-view form) lists materializations across all views"
    - "SHOW SEMANTIC MATERIALIZATIONS supports LIKE/STARTS WITH/LIMIT filter clauses"
  artifacts:
    - path: "src/ddl/show_materializations.rs"
      provides: "ShowSemanticMaterializationsVTab and ShowSemanticMaterializationsAllVTab"
    - path: "src/expand/materialization.rs"
      provides: "find_routing_materialization_name() helper function"
      contains: "pub(crate) fn find_routing_materialization_name"
    - path: "test/sql/phase57_introspection.test"
      provides: "Integration tests for INTR-01, INTR-02, INTR-03"
  key_links:
    - from: "src/query/explain.rs"
      to: "src/expand/materialization.rs"
      via: "find_routing_materialization_name call in bind"
      pattern: "find_routing_materialization_name"
    - from: "src/parse.rs"
      to: "src/ddl/show_materializations.rs"
      via: "DdlKind::ShowMaterializations detection and rewrite"
      pattern: "ShowMaterializations"
    - from: "src/lib.rs"
      to: "src/ddl/show_materializations.rs"
      via: "VTab registration"
      pattern: "ShowSemanticMaterializationsVTab"
---

<objective>
Add materialization awareness to three introspection surfaces: explain_semantic_view() shows which materialization was routed to, DESCRIBE SEMANTIC VIEW includes MATERIALIZATION property rows, and a new SHOW SEMANTIC MATERIALIZATIONS command lists all declared materializations.

Purpose: Users need to diagnose materialization routing decisions and inspect declared materializations without reading raw DDL. This is the final phase of the v0.7.0 milestone, completing the introspection story for the materialization feature.

Output: Modified explain.rs, describe.rs, parse.rs, lib.rs; new show_materializations.rs; new find_routing_materialization_name helper; integration tests.
</objective>

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

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/57-introspection-diagnostics/57-RESEARCH.md
@.planning/phases/55-materialization-routing-engine/55-01-SUMMARY.md

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

From src/model.rs:
```rust
#[derive(Debug, Clone, Default, PartialEq, Serialize, Deserialize)]
pub struct Materialization {
    pub name: String,
    pub table: String,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub dimensions: Vec<String>,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub metrics: Vec<String>,
}
```

From src/expand/materialization.rs:
```rust
pub(crate) fn try_route_materialization(
    def: &SemanticViewDefinition,
    resolved_dims: &[&Dimension],
    resolved_mets: &[&Metric],
) -> Option<String>
```

From src/ddl/describe.rs:
```rust
struct DescribeRow {
    object_kind: String,
    object_name: String,
    parent_entity: String,
    property: String,
    property_value: String,
}
pub(crate) fn format_json_array(items: &[String]) -> String;
```

From src/ddl/show_dims.rs (canonical SHOW pattern):
```rust
struct ShowDimRow { database_name, schema_name, semantic_view_name, table_name, name, data_type, synonyms, comment }
pub struct ShowSemanticDimensionsVTab;      // single-view, takes 1 VARCHAR param
pub struct ShowSemanticDimensionsAllVTab;   // cross-view, takes no params
```

From src/parse.rs:
```rust
pub enum DdlKind { Create, CreateOrReplace, ..., ShowDimensions, ShowMetrics, ShowFacts }
fn detect_ddl_prefix(trimmed: &str) -> Option<(DdlKind, usize)>
fn function_name(kind: DdlKind) -> &'static str
fn rewrite_ddl(query: &str) -> Result<String, String>
pub fn extract_ddl_name(query: &str) -> Result<Option<String>, String>
const DDL_PREFIXES: &[&str]
pub fn validate_and_rewrite(query: &str) -> Result<Option<String>, ParseError>
```

From src/query/explain.rs (bind path, lines 190-202):
```rust
let expanded_sql = expand(&view_name, &def, &req)
    .map_err(|e| -> Box<dyn std::error::Error> { Box::new(QueryError::from(e)) })?;

let mut lines: Vec<String> = Vec::new();
lines.push(format!("-- Semantic View: {view_name}"));
lines.push(format!("-- Dimensions: {}", dimensions.join(", ")));
lines.push(format!("-- Metrics: {}", metrics.join(", ")));
if !facts.is_empty() {
    lines.push(format!("-- Facts: {}", facts.join(", ")));
}
lines.push(String::new());
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Add find_routing_materialization_name helper and EXPLAIN materialization header (INTR-01) + DESCRIBE materialization rows (INTR-02)</name>
  <files>src/expand/materialization.rs, src/query/explain.rs, src/ddl/describe.rs</files>

  <read_first>
    src/expand/materialization.rs
    src/query/explain.rs
    src/ddl/describe.rs
    src/model.rs (lines 205-227 for Materialization struct)
    src/expand/sql_gen.rs (lines 267-366 for expand() and resolution logic)
  </read_first>

  <action>
**1. Add `find_routing_materialization_name` to `src/expand/materialization.rs`:**

Add a new `pub(crate)` function after `try_route_materialization` (around line 78, before `build_materialized_sql`):

```rust
/// Find the name of the materialization that would be selected for routing.
///
/// Returns `Some(&str)` with the materialization name if an exact match exists,
/// `None` if no match or if routing would be excluded (semi-additive/window).
///
/// This is used by explain_semantic_view to report the routing decision
/// without duplicating the matching logic from `try_route_materialization`.
pub(crate) fn find_routing_materialization_name<'a>(
    def: &'a SemanticViewDefinition,
    resolved_dims: &[&Dimension],
    resolved_mets: &[&Metric],
) -> Option<&'a str> {
    if def.materializations.is_empty() {
        return None;
    }
    if resolved_mets.iter().any(|m| !m.non_additive_by.is_empty()) {
        return None;
    }
    if resolved_mets.iter().any(|m| m.is_window()) {
        return None;
    }

    let req_dims: HashSet<String> = resolved_dims
        .iter()
        .map(|d| d.name.to_ascii_lowercase())
        .collect();
    let req_mets: HashSet<String> = resolved_mets
        .iter()
        .map(|m| m.name.to_ascii_lowercase())
        .collect();

    for mat in &def.materializations {
        let mat_dims: HashSet<String> = mat.dimensions.iter().map(|d| d.to_ascii_lowercase()).collect();
        let mat_mets: HashSet<String> = mat.metrics.iter().map(|m| m.to_ascii_lowercase()).collect();
        if mat_dims == req_dims && mat_mets == req_mets {
            return Some(&mat.name);
        }
    }
    None
}
```

Add unit tests in the existing `#[cfg(test)] mod tests` block:
- `find_name_returns_none_for_empty_materializations` -- verify None when no mats declared
- `find_name_returns_matching_mat_name` -- verify returns "region_agg" for exact match
- `find_name_returns_none_for_no_match` -- verify None when dims/mets don't match
- `find_name_returns_none_for_semi_additive` -- verify None when semi-additive metric present
- `find_name_returns_none_for_window` -- verify None when window metric present

**2. Add materialization header to `src/query/explain.rs`:**

In the `bind` method of `ExplainSemanticViewVTab` (after line 170 where `def` is parsed and before line 179 where `req` is constructed), resolve dims/mets and call `find_routing_materialization_name`:

Add import at top of file:
```rust
use crate::expand::materialization::find_routing_materialization_name;
```

After line 177 (after `facts` wildcard expansion) and before line 179 (before `let req = QueryRequest`), add:

```rust
// Resolve dims/mets for materialization name lookup (INTR-01).
// This duplicates name resolution that expand() also does, but it's
// trivial (linear scan of definition arrays) and avoids changing
// expand()'s return type.
let mat_name = {
    let dim_refs: Vec<&crate::model::Dimension> = dimensions
        .iter()
        .filter_map(|name| def.dimensions.iter().find(|d| d.name.eq_ignore_ascii_case(name)))
        .collect();
    let met_refs: Vec<&crate::model::Metric> = metrics
        .iter()
        .filter_map(|name| def.metrics.iter().find(|m| m.name.eq_ignore_ascii_case(name)))
        .collect();
    find_routing_materialization_name(&def, &dim_refs, &met_refs)
        .map(String::from)
};
```

Then in the header section (after the Facts line at line 201, before `lines.push(String::new());` at line 202), add:

```rust
match mat_name {
    Some(ref name) => lines.push(format!("-- Materialization: {name}")),
    None => lines.push("-- Materialization: none".to_string()),
}
```

**3. Add `collect_materialization_rows` to `src/ddl/describe.rs`:**

Add a new function after `collect_metric_rows` (same pattern as existing collect_*_rows):

```rust
/// Collect MATERIALIZATION property rows from the definition.
///
/// Each materialization emits three rows: TABLE, DIMENSIONS, METRICS.
/// When `def.materializations` is empty, no rows are added.
fn collect_materialization_rows(def: &SemanticViewDefinition, rows: &mut Vec<DescribeRow>) {
    for mat in &def.materializations {
        rows.push(DescribeRow {
            object_kind: "MATERIALIZATION".to_string(),
            object_name: mat.name.clone(),
            parent_entity: String::new(),
            property: "TABLE".to_string(),
            property_value: mat.table.clone(),
        });
        rows.push(DescribeRow {
            object_kind: "MATERIALIZATION".to_string(),
            object_name: mat.name.clone(),
            parent_entity: String::new(),
            property: "DIMENSIONS".to_string(),
            property_value: format_json_array(&mat.dimensions),
        });
        rows.push(DescribeRow {
            object_kind: "MATERIALIZATION".to_string(),
            object_name: mat.name.clone(),
            parent_entity: String::new(),
            property: "METRICS".to_string(),
            property_value: format_json_array(&mat.metrics),
        });
    }
}
```

In the `DescribeSemanticViewVTab::bind()` method, add a call after `collect_metric_rows` (after line 527):
```rust
collect_materialization_rows(&def, &mut rows);
```
  </action>

  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test materialization -- --nocapture 2>&1 | tail -20 && cargo test describe -- --nocapture 2>&1 | tail -10</automated>
  </verify>

  <acceptance_criteria>
    - src/expand/materialization.rs contains `pub(crate) fn find_routing_materialization_name`
    - src/expand/materialization.rs contains `find_name_returns_none_for_empty_materializations` test
    - src/expand/materialization.rs contains `find_name_returns_matching_mat_name` test
    - src/query/explain.rs contains `use crate::expand::materialization::find_routing_materialization_name`
    - src/query/explain.rs contains `-- Materialization: {name}`
    - src/query/explain.rs contains `-- Materialization: none`
    - src/ddl/describe.rs contains `fn collect_materialization_rows`
    - src/ddl/describe.rs contains `"MATERIALIZATION".to_string()` in object_kind
    - src/ddl/describe.rs bind() calls `collect_materialization_rows(&def, &mut rows)` after `collect_metric_rows`
    - `cargo test materialization` exits 0
    - `cargo test describe` exits 0
  </acceptance_criteria>

  <done>
    find_routing_materialization_name() returns the correct materialization name or None. explain_semantic_view() output includes "-- Materialization: name" or "-- Materialization: none" in the header section. DESCRIBE SEMANTIC VIEW includes MATERIALIZATION rows with TABLE, DIMENSIONS, METRICS properties for each declared materialization. All cargo tests pass.
  </done>
</task>

<task type="auto">
  <name>Task 2: Add SHOW SEMANTIC MATERIALIZATIONS command with parser detection, VTab pair, and registration (INTR-03)</name>
  <files>src/parse.rs, src/ddl/show_materializations.rs, src/ddl/mod.rs, src/lib.rs</files>

  <read_first>
    src/parse.rs (lines 27-42 for DdlKind enum, lines 97-162 for detect_ddl_prefix, lines 220-236 for function_name, lines 580-650 for rewrite_ddl, lines 661-732 for extract_ddl_name, lines 739-755 for DDL_PREFIXES, lines 812-870 for validate_and_rewrite)
    src/ddl/show_dims.rs (entire file -- canonical SHOW VTab pattern)
    src/ddl/mod.rs
    src/lib.rs (lines 300-325 for imports, lines 525-563 for SHOW VTab registration)
    src/ddl/describe.rs (line 45-48 for format_json_array signature)
  </read_first>

  <action>
**1. Add `DdlKind::ShowMaterializations` to `src/parse.rs`:**

In the `DdlKind` enum (line ~42, after `ShowFacts`), add:
```rust
ShowMaterializations,
```

In `detect_ddl_prefix` (between `ShowFacts` and `Show` match arms -- after line 154, before the `show semantic views` match at line 157), add:
```rust
// SHOW SEMANTIC MATERIALIZATIONS (3 keywords) -- before SHOW SEMANTIC VIEWS
if let Some(n) = match_keyword_prefix(b, &[b"show", b"semantic", b"materializations"]) {
    return Some((DdlKind::ShowMaterializations, n));
}
```

In `function_name` (after `DdlKind::ShowFacts` at line 234), add:
```rust
DdlKind::ShowMaterializations => "show_semantic_materializations",
```

In `rewrite_ddl` match (the `DdlKind::Show | DdlKind::ShowTerse | DdlKind::ShowDimensions | ...` arm at line 601-605), add `DdlKind::ShowMaterializations` to the pattern:
```rust
DdlKind::Show
| DdlKind::ShowTerse
| DdlKind::ShowDimensions
| DdlKind::ShowMetrics
| DdlKind::ShowFacts
| DdlKind::ShowMaterializations => {
```

Inside the same match arm, in the `all_fn` sub-match (line 626-633), add a case for `ShowMaterializations`:
```rust
DdlKind::ShowMaterializations => "show_semantic_materializations_all",
```

In `extract_ddl_name` (the `ShowDimensions | ShowMetrics | ShowFacts` arm at line 695), add `ShowMaterializations`:
```rust
DdlKind::ShowDimensions | DdlKind::ShowMetrics | DdlKind::ShowFacts | DdlKind::ShowMaterializations => {
```

In `DDL_PREFIXES` const array (after `"show semantic facts"` at line 754), add:
```rust
"show semantic materializations",
```

In `validate_and_rewrite` (the `ShowDimensions | ShowMetrics | ShowFacts` arm at line 862), add `ShowMaterializations`:
```rust
DdlKind::ShowDimensions | DdlKind::ShowMetrics | DdlKind::ShowFacts | DdlKind::ShowMaterializations => {
```

Add unit tests in the existing `#[cfg(test)] mod tests` block in parse.rs:
- `detect_show_materializations` -- `detect_ddl_kind("SHOW SEMANTIC MATERIALIZATIONS")` == `Some(DdlKind::ShowMaterializations)`
- `detect_show_materializations_in_view` -- `detect_ddl_kind("SHOW SEMANTIC MATERIALIZATIONS IN my_view")` == `Some(DdlKind::ShowMaterializations)`
- `rewrite_show_materializations_all` -- `rewrite_ddl("SHOW SEMANTIC MATERIALIZATIONS")` == `Ok("SELECT * FROM show_semantic_materializations_all()")`
- `rewrite_show_materializations_in_view` -- `rewrite_ddl("SHOW SEMANTIC MATERIALIZATIONS IN my_view")` == `Ok("SELECT * FROM show_semantic_materializations('my_view')")`
- `near_miss_show_materialization` -- `detect_near_miss("SHOW SEMANTIC MATERIALIZATION")` returns Some with "Did you mean" suggestion
- `extract_ddl_name_show_materializations_in` -- `extract_ddl_name("SHOW SEMANTIC MATERIALIZATIONS IN my_view")` == `Ok(Some("my_view"))`
- `extract_ddl_name_show_materializations_all` -- `extract_ddl_name("SHOW SEMANTIC MATERIALIZATIONS")` == `Ok(None)`

**2. Create `src/ddl/show_materializations.rs`:**

Clone the structure from `src/ddl/show_dims.rs`, adapted for materializations:

```rust
use std::sync::atomic::{AtomicBool, Ordering};

use duckdb::{
    core::{DataChunkHandle, Inserter, LogicalTypeHandle, LogicalTypeId},
    vtab::{BindInfo, InitInfo, TableFunctionInfo, VTab},
};

use crate::catalog::CatalogState;
use crate::ddl::describe::format_json_array;
use crate::model::SemanticViewDefinition;

/// A single row in the SHOW SEMANTIC MATERIALIZATIONS output.
///
/// 7 columns: database_name, schema_name, semantic_view_name,
/// name, table, dimensions, metrics.
struct ShowMatRow {
    database_name: String,
    schema_name: String,
    semantic_view_name: String,
    name: String,
    table: String,
    dimensions: String,
    metrics: String,
}

pub struct ShowMatBindData {
    rows: Vec<ShowMatRow>,
}

unsafe impl Send for ShowMatBindData {}
unsafe impl Sync for ShowMatBindData {}

pub struct ShowMatInitData {
    done: AtomicBool,
}

unsafe impl Send for ShowMatInitData {}
unsafe impl Sync for ShowMatInitData {}

fn bind_output_columns(bind: &BindInfo) {
    bind.add_result_column("database_name", LogicalTypeHandle::from(LogicalTypeId::Varchar));
    bind.add_result_column("schema_name", LogicalTypeHandle::from(LogicalTypeId::Varchar));
    bind.add_result_column("semantic_view_name", LogicalTypeHandle::from(LogicalTypeId::Varchar));
    bind.add_result_column("name", LogicalTypeHandle::from(LogicalTypeId::Varchar));
    bind.add_result_column("table", LogicalTypeHandle::from(LogicalTypeId::Varchar));
    bind.add_result_column("dimensions", LogicalTypeHandle::from(LogicalTypeId::Varchar));
    bind.add_result_column("metrics", LogicalTypeHandle::from(LogicalTypeId::Varchar));
}

fn collect_mats(view_name: &str, json: &str) -> Vec<ShowMatRow> {
    let Ok(def) = SemanticViewDefinition::from_json(view_name, json) else {
        return Vec::new();
    };
    let db_name = def.database_name.clone().unwrap_or_default();
    let sch_name = def.schema_name.clone().unwrap_or_default();
    def.materializations
        .iter()
        .map(|m| ShowMatRow {
            database_name: db_name.clone(),
            schema_name: sch_name.clone(),
            semantic_view_name: view_name.to_string(),
            name: m.name.clone(),
            table: m.table.clone(),
            dimensions: format_json_array(&m.dimensions),
            metrics: format_json_array(&m.metrics),
        })
        .collect()
}

fn emit_rows(
    func: &TableFunctionInfo<impl VTab<BindData = ShowMatBindData, InitData = ShowMatInitData>>,
    output: &mut DataChunkHandle,
) -> Result<(), Box<dyn std::error::Error>> {
    let init_data = func.get_init_data();
    if init_data.done.swap(true, Ordering::Relaxed) {
        output.set_len(0);
        return Ok(());
    }
    let bind_data = func.get_bind_data();
    let n = bind_data.rows.len();
    let db_vec = output.flat_vector(0);
    let sch_vec = output.flat_vector(1);
    let sv_vec = output.flat_vector(2);
    let name_vec = output.flat_vector(3);
    let table_vec = output.flat_vector(4);
    let dims_vec = output.flat_vector(5);
    let mets_vec = output.flat_vector(6);
    for (i, row) in bind_data.rows.iter().enumerate() {
        db_vec.insert(i, row.database_name.as_str());
        sch_vec.insert(i, row.schema_name.as_str());
        sv_vec.insert(i, row.semantic_view_name.as_str());
        name_vec.insert(i, row.name.as_str());
        table_vec.insert(i, row.table.as_str());
        dims_vec.insert(i, row.dimensions.as_str());
        mets_vec.insert(i, row.metrics.as_str());
    }
    output.set_len(n);
    Ok(())
}

// Single-view form: show_semantic_materializations('view_name')
pub struct ShowSemanticMaterializationsVTab;

impl VTab for ShowSemanticMaterializationsVTab {
    type BindData = ShowMatBindData;
    type InitData = ShowMatInitData;

    fn bind(bind: &BindInfo) -> Result<Self::BindData, Box<dyn std::error::Error>> {
        crate::util::catch_unwind_to_result(std::panic::AssertUnwindSafe(|| {
            bind_output_columns(bind);
            let view_name = bind.get_parameter(0).to_string();
            let state_ptr = bind.get_extra_info::<CatalogState>();
            let guard = unsafe {
                (*state_ptr)
                    .read()
                    .map_err(|_| Box::<dyn std::error::Error>::from("catalog lock poisoned"))?
            };
            let json = guard
                .get(&view_name)
                .ok_or_else(|| format!("semantic view '{view_name}' does not exist"))?;
            let mut rows = collect_mats(&view_name, json);
            rows.sort_by(|a, b| a.name.cmp(&b.name));
            Ok(ShowMatBindData { rows })
        }))
    }

    fn init(_: &InitInfo) -> Result<Self::InitData, Box<dyn std::error::Error>> {
        Ok(ShowMatInitData { done: AtomicBool::new(false) })
    }

    fn func(func: &TableFunctionInfo<Self>, output: &mut DataChunkHandle) -> Result<(), Box<dyn std::error::Error>> {
        emit_rows(func, output)
    }

    fn parameters() -> Option<Vec<LogicalTypeHandle>> {
        Some(vec![LogicalTypeHandle::from(LogicalTypeId::Varchar)])
    }
}

// Cross-view form: show_semantic_materializations_all()
pub struct ShowSemanticMaterializationsAllVTab;

impl VTab for ShowSemanticMaterializationsAllVTab {
    type BindData = ShowMatBindData;
    type InitData = ShowMatInitData;

    fn bind(bind: &BindInfo) -> Result<Self::BindData, Box<dyn std::error::Error>> {
        crate::util::catch_unwind_to_result(std::panic::AssertUnwindSafe(|| {
            bind_output_columns(bind);
            let state_ptr = bind.get_extra_info::<CatalogState>();
            let guard = unsafe {
                (*state_ptr)
                    .read()
                    .map_err(|_| Box::<dyn std::error::Error>::from("catalog lock poisoned"))?
            };
            let mut rows = Vec::new();
            for (name, json) in guard.iter() {
                rows.extend(collect_mats(name, json));
            }
            rows.sort_by(|a, b| {
                a.semantic_view_name
                    .cmp(&b.semantic_view_name)
                    .then_with(|| a.name.cmp(&b.name))
            });
            Ok(ShowMatBindData { rows })
        }))
    }

    fn init(_: &InitInfo) -> Result<Self::InitData, Box<dyn std::error::Error>> {
        Ok(ShowMatInitData { done: AtomicBool::new(false) })
    }

    fn func(func: &TableFunctionInfo<Self>, output: &mut DataChunkHandle) -> Result<(), Box<dyn std::error::Error>> {
        emit_rows(func, output)
    }

    fn parameters() -> Option<Vec<LogicalTypeHandle>> {
        None
    }
}
```

**3. Update `src/ddl/mod.rs`:**

Add after `pub mod show_metrics;`:
```rust
pub mod show_materializations;
```

**4. Update `src/lib.rs`:**

In the imports section (around line 318-321), add:
```rust
show_materializations::{ShowSemanticMaterializationsAllVTab, ShowSemanticMaterializationsVTab},
```

After the SHOW SEMANTIC FACTS registration block (after line 563), add:
```rust
// SHOW SEMANTIC MATERIALIZATIONS
con.register_table_function_with_extra_info::<ShowSemanticMaterializationsVTab, _>(
    "show_semantic_materializations",
    &catalog_state,
)?;
con.register_table_function_with_extra_info::<ShowSemanticMaterializationsAllVTab, _>(
    "show_semantic_materializations_all",
    &catalog_state,
)?;
```
  </action>

  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test parse -- --nocapture 2>&1 | tail -20 && cargo test show_materializations -- --nocapture 2>&1 | tail -10</automated>
  </verify>

  <acceptance_criteria>
    - src/parse.rs DdlKind enum contains `ShowMaterializations`
    - src/parse.rs detect_ddl_prefix contains `match_keyword_prefix(b, &[b"show", b"semantic", b"materializations"])`
    - src/parse.rs function_name returns `"show_semantic_materializations"` for ShowMaterializations
    - src/parse.rs rewrite_ddl match arm includes `DdlKind::ShowMaterializations`
    - src/parse.rs DDL_PREFIXES contains `"show semantic materializations"`
    - src/parse.rs extract_ddl_name handles ShowMaterializations
    - src/parse.rs validate_and_rewrite handles ShowMaterializations
    - src/parse.rs tests contain `detect_show_materializations` and `near_miss_show_materialization`
    - src/ddl/show_materializations.rs exists with ShowSemanticMaterializationsVTab and ShowSemanticMaterializationsAllVTab
    - src/ddl/show_materializations.rs contains `fn collect_mats`
    - src/ddl/show_materializations.rs has 7 output columns: database_name, schema_name, semantic_view_name, name, table, dimensions, metrics
    - src/ddl/mod.rs contains `pub mod show_materializations`
    - src/lib.rs imports ShowSemanticMaterializationsVTab and ShowSemanticMaterializationsAllVTab
    - src/lib.rs registers both VTabs with function names "show_semantic_materializations" and "show_semantic_materializations_all"
    - `cargo test parse` exits 0
    - `cargo build` exits 0
  </acceptance_criteria>

  <done>
    SHOW SEMANTIC MATERIALIZATIONS IN view_name and SHOW SEMANTIC MATERIALIZATIONS (cross-view) are detected by the parser, rewritten to function calls, and executed via VTab pair. Parser unit tests cover detection, rewriting, near-miss, and name extraction. Compilation succeeds with new module and VTab registrations.
  </done>
</task>

<task type="auto">
  <name>Task 3: Integration tests and full suite verification (INTR-01, INTR-02, INTR-03)</name>
  <files>test/sql/phase57_introspection.test, test/sql/TEST_LIST</files>

  <read_first>
    test/sql/phase55_materialization_routing.test (existing materialization sqllogictest pattern -- DDL setup, query assertions)
    test/sql/phase41_describe.test (DESCRIBE output assertion pattern)
    test/sql/phase34_1_show_commands.test (SHOW command output assertion pattern)
    test/sql/phase34_1_1_show_filtering.test (LIKE/STARTS WITH/LIMIT filter assertions)
    test/sql/TEST_LIST
  </read_first>

  <action>
**1. Add `test/sql/phase57_introspection.test` to `test/sql/TEST_LIST`:**

Append:
```
test/sql/phase57_introspection.test
```

**2. Create `test/sql/phase57_introspection.test`:**

The test file needs:
- A shared setup that creates a semantic view WITH materializations and one WITHOUT
- Sections for each requirement

```sql
# name: test/sql/phase57_introspection.test
# description: Phase 57 - Introspection & Diagnostics for materializations
# group: [semantic_view]

require semantic_views

# ============================================================
# Setup: Create test views
# ============================================================

statement ok
CREATE TABLE orders (id INTEGER, region VARCHAR, status VARCHAR, amount DECIMAL(10,2));

statement ok
CREATE TABLE agg_region (region VARCHAR, total_revenue DECIMAL(10,2), order_count BIGINT);

statement ok
CREATE SEMANTIC VIEW mat_view (
    TABLES (
        orders PRIMARY KEY (id)
    )
    DIMENSIONS (
        region AS orders.region,
        status AS orders.status
    )
    METRICS (
        total_revenue AS SUM(orders.amount),
        order_count AS COUNT(orders.id)
    )
    MATERIALIZATIONS (
        region_agg AS (
            TABLE agg_region,
            DIMENSIONS (region),
            METRICS (total_revenue, order_count)
        )
    )
)

statement ok
CREATE SEMANTIC VIEW no_mat_view (
    TABLES (
        orders PRIMARY KEY (id)
    )
    DIMENSIONS (
        region AS orders.region
    )
    METRICS (
        total_revenue AS SUM(orders.amount)
    )
)

# ============================================================
# INTR-01: explain_semantic_view materialization routing info
# ============================================================

# Matching materialization shows name in header
query I
SELECT explain_output FROM explain_semantic_view('mat_view', dimensions := ['region'], metrics := ['total_revenue', 'order_count']) WHERE explain_output LIKE '-- Materialization:%'
----
-- Materialization: region_agg

# Non-matching request shows "none"
query I
SELECT explain_output FROM explain_semantic_view('mat_view', dimensions := ['region', 'status'], metrics := ['total_revenue']) WHERE explain_output LIKE '-- Materialization:%'
----
-- Materialization: none

# View without materializations shows "none"
query I
SELECT explain_output FROM explain_semantic_view('no_mat_view', dimensions := ['region'], metrics := ['total_revenue']) WHERE explain_output LIKE '-- Materialization:%'
----
-- Materialization: none

# Expanded SQL for matching materialization references agg table
query I
SELECT explain_output FROM explain_semantic_view('mat_view', dimensions := ['region'], metrics := ['total_revenue', 'order_count']) WHERE explain_output LIKE '%agg_region%'
----
FROM "agg_region"

# ============================================================
# INTR-02: DESCRIBE SEMANTIC VIEW with materializations
# ============================================================

# DESCRIBE includes MATERIALIZATION rows
query IIIII
SELECT * FROM describe_semantic_view('mat_view') WHERE object_kind = 'MATERIALIZATION'
----
MATERIALIZATION	region_agg	(empty)	TABLE	agg_region
MATERIALIZATION	region_agg	(empty)	DIMENSIONS	["region"]
MATERIALIZATION	region_agg	(empty)	METRICS	["total_revenue","order_count"]

# DESCRIBE without materializations has no MATERIALIZATION rows
query I
SELECT COUNT(*) FROM describe_semantic_view('no_mat_view') WHERE object_kind = 'MATERIALIZATION'
----
0

# ============================================================
# INTR-03: SHOW SEMANTIC MATERIALIZATIONS
# ============================================================

# Single-view form
query IIIIIII
SHOW SEMANTIC MATERIALIZATIONS IN mat_view
----
memory	main	mat_view	region_agg	agg_region	["region"]	["total_revenue","order_count"]

# Single-view form for view without materializations returns empty
query IIIIIII
SHOW SEMANTIC MATERIALIZATIONS IN no_mat_view
----

# Cross-view form lists all materializations
query IIIIIII
SHOW SEMANTIC MATERIALIZATIONS
----
memory	main	mat_view	region_agg	agg_region	["region"]	["total_revenue","order_count"]

# LIKE filtering
query IIIIIII
SHOW SEMANTIC MATERIALIZATIONS LIKE 'region%' IN mat_view
----
memory	main	mat_view	region_agg	agg_region	["region"]	["total_revenue","order_count"]

# LIKE with no match
query IIIIIII
SHOW SEMANTIC MATERIALIZATIONS LIKE 'xyz%' IN mat_view
----

# View does not exist
statement error
SHOW SEMANTIC MATERIALIZATIONS IN nonexistent_view

# ============================================================
# Cleanup
# ============================================================

statement ok
DROP SEMANTIC VIEW mat_view

statement ok
DROP SEMANTIC VIEW no_mat_view

statement ok
DROP TABLE agg_region

statement ok
DROP TABLE orders
```

Note: The exact query result format for DESCRIBE needs to match the property-per-row output with empty-string parent_entity. In sqllogictest, empty strings are shown as `(empty)`. Adjust if the test framework uses a different convention -- check the existing phase41_describe.test for the exact empty-string representation used.

**3. Build and run full test suite:**

```bash
just build && just test-all
```

If any sqllogictest assertions fail, adjust the expected output format to match actual output (e.g., empty string representation, column ordering, quoting). The key behaviors must be preserved.
  </action>

  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build && just test-all</automated>
  </verify>

  <acceptance_criteria>
    - test/sql/TEST_LIST contains `test/sql/phase57_introspection.test`
    - test/sql/phase57_introspection.test exists
    - test/sql/phase57_introspection.test contains `-- Materialization: region_agg` assertion
    - test/sql/phase57_introspection.test contains `-- Materialization: none` assertion
    - test/sql/phase57_introspection.test contains `SHOW SEMANTIC MATERIALIZATIONS IN mat_view` query
    - test/sql/phase57_introspection.test contains `SHOW SEMANTIC MATERIALIZATIONS` cross-view query
    - test/sql/phase57_introspection.test contains DESCRIBE with `object_kind = 'MATERIALIZATION'` filter
    - `just test-all` exits 0
  </acceptance_criteria>

  <done>
    All three requirements (INTR-01, INTR-02, INTR-03) verified via sqllogictest integration tests. The full test suite (`just test-all`) passes including Rust unit tests, property-based tests, sqllogictest, and DuckLake CI tests. No regressions in existing tests.
  </done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| DDL input -> parser | User-supplied SQL text (view names, LIKE patterns) crosses into parser detection and rewriting |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-57-01 | Tampering | parse.rs rewrite_ddl (SHOW MATERIALIZATIONS) | mitigate | View name sanitized via `.replace('\'', "''")` in rewrite_ddl -- same pattern as existing SHOW DIMENSIONS/METRICS/FACTS. No new escaping code needed. |
| T-57-02 | Tampering | parse.rs LIKE pattern (SHOW MATERIALIZATIONS LIKE) | accept | LIKE pattern handled by existing `parse_show_filter_clauses` + `build_filter_suffix` which already sanitize quoted strings via `extract_quoted_string`. No new code path. |
| T-57-03 | Information Disclosure | show_materializations.rs | accept | Materialization metadata (table names, covered dims/mets) is user-defined and stored in the catalog. Users who can query can already see this via DESCRIBE or GET_DDL. No new information surface. |
</threat_model>

<verification>
1. `cargo test` -- all Rust unit tests pass (materialization helper, parser detection, describe)
2. `just build && just test-sql` -- all sqllogictest integration tests pass including phase57
3. `just test-all` -- full suite (cargo test + sqllogictest + DuckLake CI) passes
4. Verify explain output: `FROM explain_semantic_view('view', ...) WHERE explain_output LIKE '-- Materialization:%'` returns correct line
5. Verify describe output: `FROM describe_semantic_view('view') WHERE object_kind = 'MATERIALIZATION'` returns TABLE/DIMENSIONS/METRICS rows
6. Verify show output: `SHOW SEMANTIC MATERIALIZATIONS IN view` returns correct columns
</verification>

<success_criteria>
- explain_semantic_view() includes "-- Materialization: <name>" header when a materialization matches, "-- Materialization: none" when it does not
- DESCRIBE SEMANTIC VIEW includes MATERIALIZATION property rows (TABLE, DIMENSIONS, METRICS) for views with materializations; unchanged output for views without
- SHOW SEMANTIC MATERIALIZATIONS IN view_name lists materializations with name, table, dimensions, metrics columns
- SHOW SEMANTIC MATERIALIZATIONS (cross-view) lists materializations across all views
- SHOW SEMANTIC MATERIALIZATIONS supports LIKE/STARTS WITH/LIMIT filter clauses via existing infrastructure
- Parser detects "SHOW SEMANTIC MATERIALIZATIONS" as DdlKind::ShowMaterializations
- Near-miss detection suggests "SHOW SEMANTIC MATERIALIZATIONS" for typos
- `just test-all` passes with zero regressions
</success_criteria>

<output>
After completion, create `.planning/phases/57-introspection-diagnostics/57-01-SUMMARY.md`
</output>
