# Phase 34.1.1: Close Gaps with Snowflake SHOW SEMANTIC DDLs -- LIKE, STARTS WITH, LIMIT

**Researched:** 2026-03-22
**Domain:** DDL parser rewrite + VTab filtering for SHOW SEMANTIC commands
**Confidence:** HIGH

## Summary

Snowflake's SHOW SEMANTIC DIMENSIONS/METRICS/FACTS commands all support three optional filtering clauses that our implementation currently lacks: `LIKE '<pattern>'` (case-insensitive SQL wildcard matching on the `name` column), `STARTS WITH '<prefix>'` (case-sensitive prefix matching on the `name` column), and `LIMIT <rows>` (row count cap). The same three clauses also apply to the `SHOW SEMANTIC DIMENSIONS ... FOR METRIC` variant.

Our current architecture uses a two-layer approach: (1) the parser in `src/parse.rs` detects `SHOW SEMANTIC DIMENSIONS/METRICS/FACTS` and rewrites them to `SELECT * FROM show_semantic_*()` table function calls, and (2) the VTab implementations in `src/ddl/show_*.rs` collect all rows at bind time and emit them in a single chunk. The most natural place to implement LIKE/STARTS WITH/LIMIT is at the **parser rewrite layer** -- appending `WHERE` and `LIMIT` clauses to the generated `SELECT * FROM ...` statement. This avoids modifying VTab signatures, adding named parameters, or changing function registration. It leverages DuckDB's SQL engine to do the filtering, which is both simpler and more correct.

**Primary recommendation:** Handle LIKE, STARTS WITH, and LIMIT entirely in the parser rewrite layer (`src/parse.rs`). Generate SQL like `SELECT * FROM show_semantic_dimensions_all() WHERE name ILIKE '<pattern>' LIMIT <n>`. No VTab changes needed.

<phase_requirements>
## Phase Requirements

These are urgent-insertion requirements not yet formalized in REQUIREMENTS.md. Proposed IDs:

| ID | Description | Research Support |
|----|-------------|------------------|
| DDL-08 | `SHOW SEMANTIC DIMENSIONS LIKE '<pattern>'` filters dimensions by name using case-insensitive SQL wildcard matching (% and _) | Parser rewrite appends `WHERE name ILIKE '<pattern>'` |
| DDL-09 | `SHOW SEMANTIC METRICS LIKE '<pattern>'` filters metrics by name using case-insensitive SQL wildcard matching | Same pattern as DDL-08 |
| DDL-10 | `SHOW SEMANTIC FACTS LIKE '<pattern>'` filters facts by name using case-insensitive SQL wildcard matching | Same pattern as DDL-08 |
| DDL-11 | `SHOW SEMANTIC DIMENSIONS STARTS WITH '<prefix>'` filters by case-sensitive name prefix | Parser rewrite appends `WHERE name LIKE '<prefix>%'` (case-sensitive) |
| DDL-12 | `SHOW SEMANTIC METRICS STARTS WITH '<prefix>'` filters by case-sensitive name prefix | Same pattern as DDL-11 |
| DDL-13 | `SHOW SEMANTIC FACTS STARTS WITH '<prefix>'` filters by case-sensitive name prefix | Same pattern as DDL-11 |
| DDL-14 | `SHOW SEMANTIC DIMENSIONS LIMIT <n>` caps output rows | Parser rewrite appends `LIMIT <n>` |
| DDL-15 | `SHOW SEMANTIC METRICS LIMIT <n>` caps output rows | Same pattern as DDL-14 |
| DDL-16 | `SHOW SEMANTIC FACTS LIMIT <n>` caps output rows | Same pattern as DDL-14 |
| DDL-17 | `SHOW SEMANTIC DIMENSIONS IN view_name FOR METRIC metric_name` supports LIKE, STARTS WITH, LIMIT | Parser rewrite for the FOR METRIC variant includes same clause appending |
| DDL-18 | All LIKE/STARTS WITH/LIMIT clauses compose: `SHOW SEMANTIC DIMENSIONS LIKE '%rev%' STARTS WITH 'r' LIMIT 10` | WHERE conditions combined with AND |
| DDL-19 | Clauses work with both IN (single-view) and cross-view forms | Rewrite handles both code paths |
</phase_requirements>

## Snowflake Syntax Reference (Fetched from Official Docs)

### SHOW SEMANTIC DIMENSIONS
```sql
SHOW SEMANTIC DIMENSIONS [ LIKE '<pattern>' ]
                         [ IN <semantic_view_name> ]
                         [ STARTS WITH '<name_string>' ]
                         [ LIMIT <rows> ]
```

### SHOW SEMANTIC METRICS
```sql
SHOW SEMANTIC METRICS [ LIKE '<pattern>' ]
                      [ IN <semantic_view_name> ]
                      [ STARTS WITH '<name_string>' ]
                      [ LIMIT <rows> ]
```

### SHOW SEMANTIC FACTS
```sql
SHOW SEMANTIC FACTS [ LIKE '<pattern>' ]
                    [ IN <semantic_view_name> ]
                    [ STARTS WITH '<name_string>' ]
                    [ LIMIT <rows> ]
```

### SHOW SEMANTIC DIMENSIONS FOR METRIC
```sql
SHOW SEMANTIC DIMENSIONS [ LIKE '<pattern>' ]
                         IN <semantic_view_name>
                         FOR METRIC <metric_name>
                         [ STARTS WITH '<name_string>' ]
                         [ LIMIT <rows> ]
```

### Clause Semantics (from Snowflake docs)

| Clause | Matching | Target Column | Notes |
|--------|----------|---------------|-------|
| `LIKE '<pattern>'` | Case-insensitive | `name` | SQL wildcards: `%` (multi-char), `_` (single-char) |
| `STARTS WITH '<prefix>'` | **Case-sensitive** | `name` | Prefix match only |
| `LIMIT <rows>` | N/A | N/A | Max rows returned; Snowflake caps at 10,000 |

### Snowflake Clause Order

Snowflake requires this exact clause order:
1. `LIKE` (before IN)
2. `IN` (scope)
3. `FOR METRIC` (only for dimensions)
4. `STARTS WITH` (after IN/FOR METRIC)
5. `LIMIT` (always last)

### Snowflake Output Columns (for reference -- not changing ours)

Snowflake has more columns than we do (database_name, schema_name, synonyms, comment). We retain our existing column schema which is adequate for a single-database extension:

| Our columns (dims/metrics) | Snowflake equivalent |
|---------------------------|---------------------|
| semantic_view_name | semantic_view_name |
| name | name |
| expr | (no equivalent) |
| source_table | table_name |
| data_type | data_type |

## Architecture Patterns

### Current Rewrite Architecture

The SHOW commands flow through two layers:

```
User SQL                    Parser (parse.rs)              VTab (show_*.rs)
"SHOW SEMANTIC DIMS"  -->  rewrite_ddl()  -->  "SELECT * FROM show_semantic_dimensions_all()"
"SHOW SEMANTIC DIMS   -->  rewrite_ddl()  -->  "SELECT * FROM show_semantic_dimensions('v')"
 IN v"
"SHOW SEMANTIC DIMS   -->  rewrite_show_  -->  "SELECT * FROM show_semantic_dimensions_
 IN v FOR METRIC m"        dims_for_metric()     for_metric('v', 'm')"
```

### Recommended Implementation: Parser-Level Clause Injection

Instead of modifying VTab implementations, inject WHERE and LIMIT clauses into the generated SELECT:

```
SHOW SEMANTIC DIMENSIONS LIKE '%rev%' IN sales STARTS WITH 'r' LIMIT 5
                           |                      |               |
                           v                      v               v
SELECT * FROM show_semantic_dimensions('sales')
  WHERE name ILIKE '%rev%' AND name LIKE 'r%'
  LIMIT 5
```

**Why parser-level, not VTab-level:**

| Approach | Pros | Cons |
|----------|------|------|
| **Parser rewrite (recommended)** | No VTab changes; no new named params; DuckDB does filtering efficiently; fewer code changes; composable | Slightly longer generated SQL |
| VTab named parameters | Filtering happens in Rust | Requires 7 VTab structs to change; 3 new params each; named_parameters() changes; 7 registration changes; bind() changes |

The parser approach is strictly better here because the VTabs already emit all rows in a single chunk and DuckDB's optimizer handles WHERE/LIMIT efficiently on small result sets.

### Parser Rewrite Logic (Pseudocode)

The `rewrite_ddl` function for `ShowDimensions | ShowMetrics | ShowFacts` needs to be extended to:

1. After consuming the prefix (`SHOW SEMANTIC DIMENSIONS`), parse optional clauses in order:
   - `LIKE '<pattern>'` -- extract the quoted pattern string
   - `IN <view_name>` -- existing logic, extract view name
   - `FOR METRIC <metric_name>` -- existing logic (dimensions only)
   - `STARTS WITH '<prefix>'` -- extract the quoted prefix string
   - `LIMIT <n>` -- extract the integer
2. Build the base `SELECT * FROM <fn>(...)` call as before
3. Append `WHERE` conditions if LIKE and/or STARTS WITH are present:
   - LIKE: `WHERE name ILIKE '<escaped_pattern>'`
   - STARTS WITH: `WHERE name LIKE '<escaped_prefix>%'` (case-sensitive LIKE)
   - Both: `WHERE name ILIKE '<pattern>' AND name LIKE '<prefix>%'`
4. Append `LIMIT <n>` if present

### Recommended Project Structure Change

No new files needed. Changes concentrated in:

```
src/
  parse.rs           # Main changes: extend clause parsing for SHOW commands
  ddl/
    show_dims.rs     # NO CHANGES
    show_metrics.rs  # NO CHANGES
    show_facts.rs    # NO CHANGES
    show_dims_for_metric.rs  # NO CHANGES
  lib.rs             # NO CHANGES
test/
  sql/
    phase34_1_1_show_filtering.test  # NEW: sqllogictest for LIKE/STARTS WITH/LIMIT
```

### Anti-Patterns to Avoid

- **Modifying VTab signatures:** Adding named parameters to all 7 VTab structs is unnecessary complexity when the parser can handle it.
- **Custom LIKE implementation in Rust:** DuckDB's ILIKE is well-tested; do not reimplement pattern matching.
- **Forgetting to escape single quotes in LIKE patterns:** The pattern comes from user SQL and must be re-escaped when embedded in the generated SELECT.
- **Case sensitivity mismatch:** LIKE is case-**in**sensitive (use ILIKE), STARTS WITH is case-**sensitive** (use plain LIKE).

## Don't Hand-Roll

| Problem | Don't Build | Use Instead | Why |
|---------|-------------|-------------|-----|
| Case-insensitive wildcard matching | Custom Rust ILIKE | DuckDB `ILIKE` operator in generated SQL | DuckDB handles Unicode, edge cases |
| Case-sensitive prefix matching | Custom Rust starts_with | DuckDB `LIKE '<prefix>%'` (case-sensitive) | Consistent with DuckDB SQL semantics |
| Row limiting | Custom Rust truncation | DuckDB `LIMIT` clause | Zero overhead, correct semantics |

## Common Pitfalls

### Pitfall 1: Clause Order Sensitivity
**What goes wrong:** Snowflake mandates a specific clause order (LIKE, IN, FOR METRIC, STARTS WITH, LIMIT). If we parse too loosely, users might put clauses in wrong order and get confusing errors.
**Why it happens:** Our parser is hand-written byte-by-byte; order enforcement requires careful sequencing.
**How to avoid:** Parse clauses in the exact Snowflake order. After each clause consumed, only check for the next valid clause in sequence.
**Warning signs:** Tests pass with specific order but fail with reordered clauses.

### Pitfall 2: Single Quote Escaping in Patterns
**What goes wrong:** A LIKE pattern like `'O''Brien%'` contains escaped quotes. If we naively extract the pattern and re-embed it in SQL, we get broken SQL.
**Why it happens:** The parser extracts the string between quotes, but the string may contain `''` (SQL escaped quote).
**How to avoid:** Extract the raw content between outer quotes (handling `''` escape), then re-escape with `.replace('\'', "''")` when embedding in the generated SQL.
**Warning signs:** Patterns containing apostrophes cause SQL parse errors.

### Pitfall 3: ILIKE vs LIKE Semantics
**What goes wrong:** Using `LIKE` for the `LIKE` clause makes matching case-sensitive, violating Snowflake semantics. Using `ILIKE` for `STARTS WITH` makes it case-insensitive, also wrong.
**Why it happens:** Easy to mix up which clause uses which DuckDB operator.
**How to avoid:** LIKE clause -> DuckDB `ILIKE` (case-insensitive). STARTS WITH clause -> DuckDB `LIKE` (case-sensitive).
**Warning signs:** Test for `LIKE '%REV%'` not matching `revenue`, or `STARTS WITH 'R'` matching `revenue`.

### Pitfall 4: FOR METRIC Combined with LIKE/STARTS WITH/LIMIT
**What goes wrong:** The `rewrite_show_dims_for_metric` function is a separate code path. If LIKE/STARTS WITH/LIMIT support is added only to the main `rewrite_ddl` path but not to the FOR METRIC path, these clauses silently fail for that variant.
**Why it happens:** `rewrite_show_dims_for_metric` was extracted to a separate function for clippy line-limit compliance.
**How to avoid:** Ensure the FOR METRIC code path also collects and appends LIKE/STARTS WITH/LIMIT clauses.
**Warning signs:** `SHOW SEMANTIC DIMENSIONS LIKE '%x%' IN v FOR METRIC m STARTS WITH 'a' LIMIT 5` ignores filtering.

### Pitfall 5: Quoted String Parsing Edge Cases
**What goes wrong:** The pattern in `LIKE 'abc'` is delimited by single quotes. If the parser does not handle: (a) empty patterns `LIKE ''`, (b) patterns with spaces `LIKE 'has space%'`, (c) patterns with SQL injection `LIKE 'a''; DROP TABLE --'`, the system is incorrect or unsafe.
**Why it happens:** Hand-written string extraction is error-prone.
**How to avoid:** Parse the string between quotes properly, handling `''` escape sequences. Re-escape when re-embedding. Reject unterminated quotes.
**Warning signs:** SQL injection through LIKE patterns, or empty-pattern panics.

### Pitfall 6: Numeric Validation for LIMIT
**What goes wrong:** `LIMIT abc` or `LIMIT -5` should produce clear errors, not panics.
**Why it happens:** If `str::parse::<u64>()` fails, need a proper error message.
**How to avoid:** Parse LIMIT value, validate it is a positive integer, produce descriptive error on failure.
**Warning signs:** Non-numeric LIMIT causes panic or cryptic DuckDB error.

## Code Examples

### Pattern 1: Extracting a Quoted String from Input

```rust
/// Extract a single-quoted string from `input`, starting at position 0.
/// Returns (extracted_content, bytes_consumed) where bytes_consumed includes
/// the opening and closing quotes.
///
/// Handles SQL-style escaping: '' inside quotes represents a literal '.
fn extract_quoted_string(input: &str) -> Result<(String, usize), String> {
    let bytes = input.as_bytes();
    if bytes.is_empty() || bytes[0] != b'\'' {
        return Err("Expected single-quoted string".to_string());
    }
    let mut pos = 1;
    let mut result = String::new();
    while pos < bytes.len() {
        if bytes[pos] == b'\'' {
            if pos + 1 < bytes.len() && bytes[pos + 1] == b'\'' {
                // Escaped quote: '' -> '
                result.push('\'');
                pos += 2;
            } else {
                // End of string
                return Ok((result, pos + 1));
            }
        } else {
            result.push(bytes[pos] as char);
            pos += 1;
        }
    }
    Err("Unterminated single-quoted string".to_string())
}
```

### Pattern 2: Building WHERE/LIMIT Suffix

```rust
/// Build optional WHERE and LIMIT suffix for a SHOW rewrite.
fn build_filter_suffix(
    like_pattern: Option<&str>,
    starts_with: Option<&str>,
    limit: Option<u64>,
) -> String {
    let mut parts = Vec::new();

    if let Some(pattern) = like_pattern {
        let escaped = pattern.replace('\'', "''");
        parts.push(format!("name ILIKE '{escaped}'"));
    }
    if let Some(prefix) = starts_with {
        let escaped = prefix.replace('\'', "''");
        // Case-sensitive LIKE (not ILIKE) with appended %
        parts.push(format!("name LIKE '{escaped}%'"));
    }

    let mut suffix = String::new();
    if !parts.is_empty() {
        suffix.push_str(" WHERE ");
        suffix.push_str(&parts.join(" AND "));
    }
    if let Some(n) = limit {
        suffix.push_str(&format!(" LIMIT {n}"));
    }
    suffix
}
```

### Pattern 3: Extended Rewrite for SHOW Commands

```rust
// Current (simplified):
// "SHOW SEMANTIC DIMENSIONS IN v" -> "SELECT * FROM show_semantic_dimensions('v')"

// New (with filtering):
// "SHOW SEMANTIC DIMENSIONS LIKE '%rev%' IN v STARTS WITH 'r' LIMIT 5"
//   -> "SELECT * FROM show_semantic_dimensions('v') WHERE name ILIKE '%rev%' AND name LIKE 'r%' LIMIT 5"
```

### Pattern 4: Existing Rewrite Site to Modify

The key function to modify is `rewrite_ddl()` in `src/parse.rs`, specifically the `DdlKind::ShowDimensions | DdlKind::ShowMetrics | DdlKind::ShowFacts` match arm (lines 296-351). The clause parsing needs to happen after consuming the prefix but before building the final SQL string.

## Validation Architecture

### Test Framework
| Property | Value |
|----------|-------|
| Framework | sqllogictest-rs + cargo test (Rust unit) |
| Config file | `test/sql/*.test` files, `Cargo.toml` for unit tests |
| Quick run command | `cargo test -- parse` |
| Full suite command | `just test-all` |

### Phase Requirements to Test Map

| Req ID | Behavior | Test Type | Automated Command | File Exists? |
|--------|----------|-----------|-------------------|-------------|
| DDL-08 | LIKE on SHOW DIMS | unit + sqllogictest | `cargo test -- test_rewrite_show_dims_like` | Wave 0 |
| DDL-09 | LIKE on SHOW METRICS | unit + sqllogictest | `cargo test -- test_rewrite_show_metrics_like` | Wave 0 |
| DDL-10 | LIKE on SHOW FACTS | unit + sqllogictest | `cargo test -- test_rewrite_show_facts_like` | Wave 0 |
| DDL-11 | STARTS WITH on SHOW DIMS | unit + sqllogictest | `cargo test -- test_rewrite_show_dims_starts_with` | Wave 0 |
| DDL-12 | STARTS WITH on SHOW METRICS | unit + sqllogictest | (shared test) | Wave 0 |
| DDL-13 | STARTS WITH on SHOW FACTS | unit + sqllogictest | (shared test) | Wave 0 |
| DDL-14 | LIMIT on SHOW DIMS | unit + sqllogictest | `cargo test -- test_rewrite_show_dims_limit` | Wave 0 |
| DDL-15 | LIMIT on SHOW METRICS | unit + sqllogictest | (shared test) | Wave 0 |
| DDL-16 | LIMIT on SHOW FACTS | unit + sqllogictest | (shared test) | Wave 0 |
| DDL-17 | FOR METRIC with LIKE/STARTS WITH/LIMIT | unit + sqllogictest | `cargo test -- test_rewrite_show_dims_for_metric_like` | Wave 0 |
| DDL-18 | Clause composition (multiple clauses) | unit + sqllogictest | `cargo test -- test_rewrite_show_combined` | Wave 0 |
| DDL-19 | Both IN and cross-view forms | sqllogictest | `just test-sql` | Wave 0 |

### Sampling Rate
- **Per task commit:** `cargo test -- parse`
- **Per wave merge:** `just test-all`
- **Phase gate:** Full suite green before `/gsd:verify-work`

### Wave 0 Gaps
- [ ] `test/sql/phase34_1_1_show_filtering.test` -- sqllogictest for LIKE/STARTS WITH/LIMIT
- [ ] Unit tests in `src/parse.rs` for rewrite output with filter clauses

## State of the Art

| Old Approach | Current Approach | When Changed | Impact |
|--------------|------------------|--------------|--------|
| No LIKE/STARTS WITH/LIMIT | This phase adds them | Phase 34.1.1 | Feature parity with Snowflake |
| VTab-level filtering | Parser-rewrite filtering | Design decision | Simpler, fewer code changes, leverages DuckDB engine |

### Snowflake Scope Features We Skip

Snowflake's IN clause supports scoping to ACCOUNT, DATABASE, or SCHEMA:
```sql
SHOW SEMANTIC DIMENSIONS IN DATABASE my_db
SHOW SEMANTIC DIMENSIONS IN SCHEMA my_db.my_schema
```

We do **not** implement these scopes because our extension operates in a single-database, single-schema context (DuckDB loaded extension). Only `IN <semantic_view_name>` is supported.

## Open Questions

1. **Clause order strictness**
   - What we know: Snowflake mandates LIKE before IN, STARTS WITH after IN, LIMIT last
   - What's unclear: Whether we should enforce this strictly or accept any order
   - Recommendation: Enforce Snowflake order strictly. It simplifies parser logic and matches user expectations from Snowflake docs.

2. **LIMIT upper bound**
   - What we know: Snowflake caps at 10,000 rows. Our extension will rarely have that many.
   - What's unclear: Whether to enforce a 10,000 cap
   - Recommendation: Do not enforce a cap. Accept any positive integer. Our catalog is small; no need for artificial limits.

3. **Error messages for misplaced clauses**
   - What we know: Users may write `SHOW SEMANTIC DIMENSIONS IN v LIKE '%x%'` (LIKE after IN, wrong order)
   - What's unclear: Best error message
   - Recommendation: Produce a clear error: "LIKE must appear before IN. Usage: SHOW SEMANTIC DIMENSIONS [LIKE '<pattern>'] [IN view_name] [STARTS WITH '<prefix>'] [LIMIT <n>]"

## Sources

### Primary (HIGH confidence)
- [Snowflake SHOW SEMANTIC DIMENSIONS](https://docs.snowflake.com/en/sql-reference/sql/show-semantic-dimensions) -- full syntax, parameters, output columns, examples
- [Snowflake SHOW SEMANTIC METRICS](https://docs.snowflake.com/en/sql-reference/sql/show-semantic-metrics) -- full syntax, same pattern as dimensions
- [Snowflake SHOW SEMANTIC FACTS](https://docs.snowflake.com/en/sql-reference/sql/show-semantic-facts) -- full syntax, same pattern
- [Snowflake SHOW SEMANTIC DIMENSIONS FOR METRIC](https://docs.snowflake.com/en/sql-reference/sql/show-semantic-dimensions-for-metric) -- LIKE before IN, STARTS WITH after FOR METRIC
- Source code: `src/parse.rs`, `src/ddl/show_dims.rs`, `src/ddl/show_metrics.rs`, `src/ddl/show_facts.rs`, `src/ddl/show_dims_for_metric.rs`, `src/lib.rs`

### Secondary (MEDIUM confidence)
- DuckDB ILIKE operator behavior (from training data, verified via DuckDB docs search)

## Metadata

**Confidence breakdown:**
- Standard stack: HIGH -- no new libraries, pure Rust parser changes
- Architecture: HIGH -- parser rewrite pattern is well-established in this codebase (6+ DDL forms use it)
- Pitfalls: HIGH -- identified from direct code inspection and Snowflake doc analysis

**Research date:** 2026-03-22
**Valid until:** 2026-04-22 (stable domain, Snowflake syntax unlikely to change)
