---
phase: 40-show-command-alignment
plan: 02
type: execute
wave: 2
depends_on: ["40-01"]
files_modified:
  - src/ddl/show_dims_for_metric.rs
  - test/sql/phase34_1_show_dims_for_metric.test
  - test/sql/phase34_1_1_show_filtering.test
autonomous: true
requirements: [SHOW-05, SHOW-06, SHOW-07, SHOW-08]

must_haves:
  truths:
    - "SHOW SEMANTIC DIMENSIONS FOR METRIC returns 4 columns: table_name, name, data_type, required"
    - "The required column is BOOLEAN type with constant FALSE value"
    - "LIKE, STARTS WITH, LIMIT filtering works on all SHOW commands with new schemas"
    - "No SHOW command exposes an expr column"
    - "All SHOW commands use table_name not source_table"
  artifacts:
    - path: "src/ddl/show_dims_for_metric.rs"
      provides: "4-column SHOW DIMS FOR METRIC VTab with BOOLEAN required column"
      contains: "LogicalTypeId::Boolean"
    - path: "test/sql/phase34_1_show_dims_for_metric.test"
      provides: "Updated sqllogictest for 4-column DIMS FOR METRIC schema"
      contains: "TTTI"
    - path: "test/sql/phase34_1_1_show_filtering.test"
      provides: "Updated sqllogictest for all SHOW command filtering with new schemas"
      contains: "TTTTTT"
  key_links:
    - from: "src/ddl/show_dims_for_metric.rs"
      to: "duckdb::core::LogicalTypeId::Boolean"
      via: "as_mut_slice::<bool>() for required column emission"
      pattern: "as_mut_slice::<bool>"
    - from: "test/sql/phase34_1_1_show_filtering.test"
      to: "All 5 VTab files"
      via: "sqllogictest expected output matching new column schemas"
      pattern: "query TTTTTT"
---

<objective>
Update show_dims_for_metric.rs to a 4-column schema with BOOLEAN required column, and update all remaining sqllogictest files to match new SHOW command schemas.

Purpose: SHOW-05 requires DIMS FOR METRIC to output (table_name, name, data_type, required). SHOW-08 requires all filtering tests to pass with new schemas. This plan also completes SHOW-06 and SHOW-07 coverage in the remaining VTab and test files.
Output: 1 updated VTab file + 2 updated test files. `just test-all` passes (full quality gate).
</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/40-show-command-alignment/40-RESEARCH.md
@.planning/phases/40-show-command-alignment/40-01-SUMMARY.md

<interfaces>
<!-- Key types from Plan 01 output and existing code -->

From src/model.rs:
```rust
pub struct Dimension {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,
}

pub struct SemanticViewDefinition {
    // Phase 39 metadata:
    pub created_on: Option<String>,
    pub database_name: Option<String>,
    pub schema_name: Option<String>,
    // ... other fields
}
```

From src/ddl/show_dims.rs (after Plan 01 update):
```rust
// New 6-column schema: database_name, schema_name, semantic_view_name, table_name, name, data_type
// Pattern: collect_dims extracts db_name/sch_name from def, maps source_table -> table_name
```

Boolean column pattern (from DuckDB Rust crate excel.rs):
```rust
// In bind:
bind.add_result_column("required", LogicalTypeHandle::from(LogicalTypeId::Boolean));

// In func:
let mut required_vec = output.flat_vector(3);
let required_slice = required_vec.as_mut_slice::<bool>();
for i in 0..n {
    required_slice[i] = false;
}
```

sqllogictest column type for BOOLEAN: Use `I` (integer) -- DuckDB's sqllogictest runner renders booleans as 0/1. The constant FALSE will render as `0` in test output. If `I` causes issues, fall back to `T` and expect `false` as text.
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Update show_dims_for_metric.rs to 4-column schema with BOOLEAN</name>
  <read_first>
    - src/ddl/show_dims_for_metric.rs
    - src/ddl/show_dims.rs (after Plan 01 -- reference the new pattern)
    - src/model.rs (lines 24-39 for Dimension struct)
  </read_first>
  <files>src/ddl/show_dims_for_metric.rs</files>
  <action>
**show_dims_for_metric.rs -- 5 cols to 4 cols with BOOLEAN (SHOW-05, SHOW-06, SHOW-07):**

Target schema: `table_name VARCHAR, name VARCHAR, data_type VARCHAR, required BOOLEAN`

1. Update `ShowDimForMetricRow` struct -- remove `semantic_view_name` and `expr`, rename `source_table` to `table_name`:
```rust
struct ShowDimForMetricRow {
    table_name: String,
    name: String,
    data_type: String,
    // required is constant FALSE, no need to store in struct
}
```

2. In the `bind()` method, replace the 5 column declarations with 4:
```rust
bind.add_result_column("table_name", LogicalTypeHandle::from(LogicalTypeId::Varchar));
bind.add_result_column("name", LogicalTypeHandle::from(LogicalTypeId::Varchar));
bind.add_result_column("data_type", LogicalTypeHandle::from(LogicalTypeId::Varchar));
bind.add_result_column("required", LogicalTypeHandle::from(LogicalTypeId::Boolean));
```

3. Update BOTH row-construction sites in `bind()` (the single-table branch at ~line 233 and the multi-table branch at ~line 278):

For single-table branch (no joins):
```rust
def.dimensions
    .iter()
    .map(|d| ShowDimForMetricRow {
        table_name: d.source_table.clone().unwrap_or_default(),
        name: d.name.clone(),
        data_type: d.output_type.clone().unwrap_or_default(),
    })
    .collect()
```

For multi-table branch (fan-trap filtered):
```rust
.map(|d| ShowDimForMetricRow {
    table_name: d.source_table.clone().unwrap_or_default(),
    name: d.name.clone(),
    data_type: d.output_type.clone().unwrap_or_default(),
})
.collect()
```

4. Update `func()` to emit 4 columns. For VARCHAR columns use `Inserter::insert()`, for BOOLEAN use `as_mut_slice::<bool>()`:
```rust
let bind_data = func.get_bind_data();
let n = bind_data.rows.len();

let table_vec = output.flat_vector(0);   // table_name
let name_vec = output.flat_vector(1);    // name
let type_vec = output.flat_vector(2);    // data_type
let mut req_vec = output.flat_vector(3); // required (BOOLEAN)

for (i, row) in bind_data.rows.iter().enumerate() {
    table_vec.insert(i, row.table_name.as_str());
    name_vec.insert(i, row.name.as_str());
    type_vec.insert(i, row.data_type.as_str());
}

// Write boolean column: constant FALSE for all rows
let req_slice = req_vec.as_mut_slice::<bool>();
for i in 0..n {
    req_slice[i] = false;
}

output.set_len(n);
```

IMPORTANT: `as_mut_slice::<bool>()` requires `let mut req_vec` (mutable binding) since it borrows mutably. The VARCHAR vectors use `insert()` which takes `&self`, not `&mut self` -- that is the existing pattern.

5. Update SAFETY comments on `ShowDimsForMetricBindData` and `ShowDimsForMetricInitData`.
6. Update doc comments on the VTab struct to reflect 4-column schema.
7. The `is_dimension_reachable_for_metric` function and all fan-trap logic remain UNCHANGED -- only the row struct and emission logic change.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo check 2>&1 | tail -5</automated>
  </verify>
  <acceptance_criteria>
    - `show_dims_for_metric.rs` contains `LogicalTypeId::Boolean`
    - `show_dims_for_metric.rs` contains `as_mut_slice::<bool>()`
    - `show_dims_for_metric.rs` contains `"required"` column declaration
    - `show_dims_for_metric.rs` contains `"table_name"` column declaration
    - `show_dims_for_metric.rs` does NOT contain `"semantic_view_name"` in column declarations
    - `show_dims_for_metric.rs` does NOT contain `"expr"` in column declarations
    - `show_dims_for_metric.rs` does NOT contain `"source_table"` column name
    - `show_dims_for_metric.rs` has exactly 4 `add_result_column` calls
    - The `is_dimension_reachable_for_metric` function is unchanged
    - `cargo check` succeeds
  </acceptance_criteria>
  <done>show_dims_for_metric.rs emits 4-column schema (table_name, name, data_type, required BOOLEAN) with constant FALSE; fan-trap logic unchanged; compiles cleanly</done>
</task>

<task type="auto">
  <name>Task 2: Update all remaining sqllogictest files for new schemas</name>
  <read_first>
    - test/sql/phase34_1_show_dims_for_metric.test
    - test/sql/phase34_1_1_show_filtering.test
    - test/sql/phase34_1_show_commands.test (from Plan 01 -- reference the updated patterns)
  </read_first>
  <files>test/sql/phase34_1_show_dims_for_metric.test, test/sql/phase34_1_1_show_filtering.test</files>
  <action>
**phase34_1_show_dims_for_metric.test -- update for 4-column schema:**

1. Change ALL `query TTTTT` to `query TTTI` (3 VARCHAR + 1 BOOLEAN). There are 7 query directives (Tests 1-3, 6-7 and their expected output).

2. Update ALL expected output rows. The old format was:
   `{view_name}	{dim_name}	{expr}	{source_table}	(empty)`
   The new format is:
   `{source_table}	{dim_name}	(empty)	0`
   Where:
   - Column 1 (`table_name`): the source table alias (was column 4)
   - Column 2 (`name`): the dimension name (was column 2)
   - Column 3 (`data_type`): `(empty)` since no output_type is set (tests don't declare types)
   - Column 4 (`required`): `0` (boolean FALSE rendered as integer 0 in sqllogictest; if this fails, try `false`)

   CRITICAL: The `rowsort` will now sort by `table_name` first (not `semantic_view_name`). Verify sort order is correct for multi-row results.

3. Specific test updates:

   **Test 1** (simple_sv, all dims reachable):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS IN simple_sv FOR METRIC total
   ----
   s	product	(empty)	0
   s	region	(empty)	0
   ```

   **Test 2** (star_sv, order_total -- customer dims only):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS IN star_sv FOR METRIC order_total
   ----
   c	customer_country	(empty)	0
   c	customer_name	(empty)	0
   ```

   **Test 3** (star_sv, line_item_sum -- all dims reachable):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS IN star_sv FOR METRIC line_item_sum
   ----
   c	customer_country	(empty)	0
   c	customer_name	(empty)	0
   li	item_qty	(empty)	0
   ```

   **Test 6** (derived_sv, double_total):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS IN derived_sv FOR METRIC double_total
   ----
   c	customer_name	(empty)	0
   ```

   **Test 7** (case insensitivity):
   ```
   query TTTI rowsort
   show semantic dimensions in derived_sv for metric order_total
   ----
   c	customer_name	(empty)	0
   ```

   Note: If `0` for boolean doesn't work in sqllogictest, try `false` with `T` type letter instead of `I`. The DuckDB sqllogictest runner may vary in boolean rendering.

**phase34_1_1_show_filtering.test -- update ALL queries for new schemas:**

This file tests filtering on ALL SHOW commands. Every query directive needs updating.

1. **SHOW SEMANTIC DIMENSIONS queries (Tests 1-6, 12, 17-18):** Change `query TTTTT` to `query TTTTTT`. Update expected rows from:
   `{view_name}	{dim_name}	{expr}	{source_table}	(empty)`
   to:
   `memory	main	{view_name}	{source_table}	{dim_name}	(empty)`

   Note: `rowsort` order may change since `memory` and `main` are now the first two columns.

   Test 1 (LIKE '%name%' cross-view):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC DIMENSIONS LIKE '%name%'
   ----
   memory	main	filter_sv	c	customer_name	(empty)
   memory	main	products_sv	p	product_name	(empty)
   ```

   Test 2 (LIKE '%order%'):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC DIMENSIONS LIKE '%order%'
   ----
   memory	main	filter_sv	o	order_date	(empty)
   ```

   Test 3 (LIKE '%NAME%' IN filter_sv):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC DIMENSIONS LIKE '%NAME%' IN filter_sv
   ----
   memory	main	filter_sv	c	customer_name	(empty)
   ```

   Test 4 (STARTS WITH 'customer'):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC DIMENSIONS STARTS WITH 'customer'
   ----
   memory	main	filter_sv	c	customer_name	(empty)
   ```

   Test 5 (STARTS WITH 'Customer' -- no match): empty result, just update query type to `query TTTTTT rowsort`.

   Test 6 (LIKE '%name%' IN filter_sv LIMIT 1):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC DIMENSIONS LIKE '%name%' IN filter_sv LIMIT 1
   ----
   memory	main	filter_sv	c	customer_name	(empty)
   ```

   Test 12 (LIKE + IN + STARTS WITH + LIMIT):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC DIMENSIONS LIKE '%name%' IN filter_sv STARTS WITH 'cust' LIMIT 10
   ----
   memory	main	filter_sv	c	customer_name	(empty)
   ```

   Test 17 (case insensitivity):
   ```
   query TTTTTT rowsort
   show semantic dimensions like '%name%' in filter_sv
   ----
   memory	main	filter_sv	c	customer_name	(empty)
   ```

   Test 18 (empty LIKE): update to `query TTTTTT rowsort`, empty result.

2. **SHOW SEMANTIC METRICS queries (Tests 7-8):** Change `query TTTTT` to `query TTTTTT`. Update expected rows:

   Test 7 (LIKE '%amount%'):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC METRICS LIKE '%amount%'
   ----
   memory	main	filter_sv	o	total_amount	(empty)
   ```

   Test 8 (STARTS WITH 'order'):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC METRICS STARTS WITH 'order'
   ----
   memory	main	filter_sv	o	order_count	(empty)
   ```

3. **SHOW SEMANTIC FACTS queries (Tests 9-11):** Change `query TTTT` to `query TTTTTT`. Update expected rows. The `data_type` column will contain the inferred fact type (from Phase 39 `typeof()` inference):
   - `raw_amount AS o.amount` where `amount` is `DECIMAL(10,2)` -> `data_type` should be `DECIMAL(10,2)` if inference works, or `(empty)` if not.

   Use the same value as determined in Plan 01's test update for facts. If uncertain, use `(empty)` initially and fix after running tests.

   Test 9 (LIKE '%amount%'):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC FACTS LIKE '%amount%'
   ----
   memory	main	filter_sv	o	raw_amount	DECIMAL(10,2)
   ```

   Test 10 (STARTS WITH 'raw'):
   ```
   query TTTTTT rowsort
   SHOW SEMANTIC FACTS STARTS WITH 'raw'
   ----
   memory	main	filter_sv	o	raw_amount	DECIMAL(10,2)
   ```

   Test 11 (IN filter_sv LIMIT 1) -- note: this test uses `query TTTT` without rowsort currently; update to `query TTTTTT`:
   ```
   query TTTTTT
   SHOW SEMANTIC FACTS IN filter_sv LIMIT 1
   ----
   memory	main	filter_sv	o	raw_amount	DECIMAL(10,2)
   ```

4. **SHOW SEMANTIC VIEWS queries (Tests 19-25):** Change `query TT` to `query TTTTT`. The `created_on` column is non-deterministic. Use wrapping SELECT to exclude it:

   For each SHOW VIEWS test, wrap the SHOW command in a SELECT that skips `created_on`:
   ```sql
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS ...)
   ```
   This returns 4 deterministic columns. Use `query TTTT` for the wrapper.

   Test 19 (LIKE '%filter%'):
   ```
   query TTTT rowsort
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS LIKE '%filter%')
   ----
   filter_sv	SEMANTIC_VIEW	memory	main
   ```

   Test 20 (LIKE '%sv'):
   ```
   query TTTT rowsort
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS LIKE '%sv')
   ----
   filter_sv	SEMANTIC_VIEW	memory	main
   products_sv	SEMANTIC_VIEW	memory	main
   ```

   Test 21 (STARTS WITH 'filter'):
   ```
   query TTTT rowsort
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS STARTS WITH 'filter')
   ----
   filter_sv	SEMANTIC_VIEW	memory	main
   ```

   Test 22 (STARTS WITH 'Filter' -- no match):
   ```
   query TTTT rowsort
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS STARTS WITH 'Filter')
   ----
   ```

   Test 23 (LIMIT 1):
   ```
   query TTTT
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS LIMIT 1)
   ----
   filter_sv	SEMANTIC_VIEW	memory	main
   ```

   Test 24 (LIKE + LIMIT):
   ```
   query TTTT
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS LIKE '%sv' LIMIT 1)
   ----
   filter_sv	SEMANTIC_VIEW	memory	main
   ```

   Test 25 (LIKE + STARTS WITH + LIMIT):
   ```
   query TTTT rowsort
   SELECT name, kind, database_name, schema_name FROM (SHOW SEMANTIC VIEWS LIKE '%sv' STARTS WITH 'prod' LIMIT 10)
   ----
   products_sv	SEMANTIC_VIEW	memory	main
   ```

5. **FOR METRIC with filtering (Tests 13-15):** Change `query TTTTT` to `query TTTI` (4 columns: table_name, name, data_type, required). Update expected rows:

   Test 13 (LIKE '%gio%' IN filter_sv FOR METRIC total_amount):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS LIKE '%gio%' IN filter_sv FOR METRIC total_amount
   ----
   c	region	(empty)	0
   ```

   Test 14 (IN filter_sv FOR METRIC total_amount STARTS WITH 'customer'):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS IN filter_sv FOR METRIC total_amount STARTS WITH 'customer'
   ----
   c	customer_name	(empty)	0
   ```

   Test 15 (IN filter_sv FOR METRIC total_amount LIMIT 3):
   ```
   query TTTI rowsort
   SHOW SEMANTIC DIMENSIONS IN filter_sv FOR METRIC total_amount LIMIT 3
   ----
   c	customer_name	(empty)	0
   c	region	(empty)	0
   o	order_date	(empty)	0
   ```
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just build && just test-all 2>&1 | tail -20</automated>
  </verify>
  <acceptance_criteria>
    - `phase34_1_show_dims_for_metric.test` contains `query TTTI` (not `query TTTTT`)
    - `phase34_1_show_dims_for_metric.test` does NOT contain `query TTTTT`
    - `phase34_1_show_dims_for_metric.test` expected output rows have 4 tab-separated columns
    - `phase34_1_1_show_filtering.test` contains `query TTTTTT` for DIMS/METRICS/FACTS queries
    - `phase34_1_1_show_filtering.test` contains `query TTTI` for FOR METRIC queries
    - `phase34_1_1_show_filtering.test` contains `SEMANTIC_VIEW` in SHOW VIEWS expected output
    - `phase34_1_1_show_filtering.test` contains `SELECT name, kind` wrapper for SHOW VIEWS tests
    - `phase34_1_1_show_filtering.test` does NOT contain `query TT ` (old 2-column SHOW VIEWS)
    - `phase34_1_1_show_filtering.test` does NOT contain `query TTTT ` (old 4-column FACTS, unless preceded by SELECT wrapper)
    - `just test-all` passes (full quality gate)
  </acceptance_criteria>
  <done>All sqllogictest files updated to match new SHOW schemas; show_dims_for_metric.rs has 4-column schema with BOOLEAN required; `just test-all` passes green</done>
</task>

</tasks>

<verification>
After both tasks complete, the full quality gate:
1. `cargo test` passes -- all Rust unit tests, proptests, doc tests
2. `just build` succeeds -- extension binary compiles
3. `just test-sql` passes -- all 18+ sqllogictest files green
4. `just test-ducklake-ci` passes -- DuckLake integration tests
5. `just test-all` passes -- combined quality gate
</verification>

<success_criteria>
- show_dims_for_metric.rs outputs 4 columns: table_name, name, data_type, required (BOOLEAN, constant FALSE) (SHOW-05)
- No VTab file across the codebase contains "expr" column declaration (SHOW-06, combined with Plan 01)
- No VTab file across the codebase contains "source_table" column name (SHOW-07, combined with Plan 01)
- All LIKE, STARTS WITH, LIMIT filtering tests pass with new schemas (SHOW-08)
- `just test-all` passes (full quality gate per CLAUDE.md)
</success_criteria>

<output>
After completion, create `.planning/phases/40-show-command-alignment/40-02-SUMMARY.md`
</output>
