---
phase: 34.1.1-close-gaps-with-snowflake-show-semantic-ddls-add-like-starts-with-limit
plan: 01
subsystem: ddl-parser
tags: [show, like, starts-with, limit, ilike, filter, snowflake-parity]

# Dependency graph
requires:
  - phase: 34.1-close-ddl-gaps-vs-snowflake-alter-semantic-view-show-semantic-dimensions-facts-metrics
    provides: SHOW SEMANTIC DIMENSIONS/METRICS/FACTS base commands and VTab implementations
provides:
  - LIKE clause (ILIKE case-insensitive) on all SHOW SEMANTIC commands
  - STARTS WITH clause (case-sensitive prefix) on all SHOW SEMANTIC commands
  - LIMIT clause on all SHOW SEMANTIC commands
  - Clause composition (LIKE + STARTS WITH + LIMIT in single statement)
  - FOR METRIC variant supports all filter clauses
  - extract_quoted_string helper for SQL-escaped single-quote string parsing
  - build_filter_suffix helper for WHERE/LIMIT SQL generation
  - ShowClauses struct + parse_show_filter_clauses ordered clause parser
affects: [documentation, registry-publishing]

# Tech tracking
tech-stack:
  added: []
  patterns:
    - "Parser-level clause injection: append WHERE/LIMIT to generated SELECT instead of modifying VTab"
    - "Ordered clause parser: parse clauses in Snowflake-mandated order (LIKE, IN, FOR METRIC, STARTS WITH, LIMIT)"

key-files:
  created:
    - test/sql/phase34_1_1_show_filtering.test
  modified:
    - src/parse.rs
    - test/sql/TEST_LIST

key-decisions:
  - "Parser-level filtering via WHERE/LIMIT injection (no VTab changes needed)"
  - "Strict clause order enforcement matching Snowflake syntax"
  - "Removed rewrite_show_dims_for_metric (absorbed into unified clause parser)"

patterns-established:
  - "ShowClauses struct: parsed filter clauses for SHOW commands, reusable if more clauses added"
  - "extract_quoted_string: SQL-escaped single-quote parser, reusable for any quoted string extraction"

requirements-completed: [DDL-08, DDL-09, DDL-10, DDL-11, DDL-12, DDL-13, DDL-14, DDL-15, DDL-16, DDL-17, DDL-18, DDL-19]

# Metrics
duration: 12min
completed: 2026-03-22
---

# Phase 34.1.1 Plan 01: SHOW Filter Clauses Summary

**LIKE/STARTS WITH/LIMIT clause parsing for all SHOW SEMANTIC commands via parser-level WHERE/LIMIT injection**

## Performance

- **Duration:** 12 min
- **Started:** 2026-03-22T18:02:54Z
- **Completed:** 2026-03-22T18:15:00Z
- **Tasks:** 2 (TDD + integration)
- **Files modified:** 3

## Accomplishments
- All four SHOW SEMANTIC command forms (DIMENSIONS, METRICS, FACTS, DIMENSIONS FOR METRIC) now support LIKE, STARTS WITH, and LIMIT clauses
- LIKE maps to DuckDB ILIKE (case-insensitive), STARTS WITH maps to case-sensitive LIKE with % suffix
- Clause order enforced matching Snowflake: LIKE before IN, STARTS WITH after IN/FOR METRIC, LIMIT last
- Single quotes in patterns are properly escaped (SQL injection safe)
- 22 new Rust unit tests + 18 sqllogictest integration test cases

## Task Commits

Each task was committed atomically:

1. **Task 1: Add helper functions and extend SHOW rewrite with LIKE/STARTS WITH/LIMIT (TDD)**
   - `ecfc415` (test: TDD RED - failing tests)
   - `de1728e` (feat: TDD GREEN - implementation passing all tests)
2. **Task 2: Integration tests via sqllogictest** - `f6cb3c9` (test)

## Files Created/Modified
- `src/parse.rs` - Added extract_quoted_string, build_filter_suffix, ShowClauses, parse_show_filter_clauses; rewrote SHOW arm in rewrite_ddl; updated extract_ddl_name for LIKE before IN; removed dead rewrite_show_dims_for_metric
- `test/sql/phase34_1_1_show_filtering.test` - 18 integration test cases covering all clause combinations
- `test/sql/TEST_LIST` - Added new test file

## Decisions Made
- Parser-level filtering via WHERE/LIMIT injection: avoids modifying 7 VTab structs, leverages DuckDB's SQL engine for filtering
- Strict clause order enforcement: matches Snowflake syntax exactly, simplifies parser logic
- Removed rewrite_show_dims_for_metric: absorbed into unified parse_show_filter_clauses, reducing code duplication

## Deviations from Plan

### Auto-fixed Issues

**1. [Rule 1 - Bug] Fixed FOR METRIC test expectations for same-table dimensions**
- **Found during:** Task 2 (sqllogictest)
- **Issue:** Test 13 and 15 assumed order_date (from table o) was excluded by fan-trap filter for metric total_amount (also from o), but same-table dimensions are safe
- **Fix:** Updated test expectations to include order_date in safe dimensions; changed LIKE pattern in Test 13 from '%r%' to '%gio%' to match only region
- **Files modified:** test/sql/phase34_1_1_show_filtering.test
- **Verification:** All 17 sqllogictest files pass

---

**Total deviations:** 1 auto-fixed (1 bug in test expectations)
**Impact on plan:** Test correction only, no code change needed. The parser implementation was correct.

## Issues Encountered
None - implementation followed the plan's recommended approach exactly.

## User Setup Required
None - no external service configuration required.

## Next Phase Readiness
- All SHOW SEMANTIC filtering clauses complete and tested
- Ready for documentation site and registry publishing phases
- Quality gate passes: 476 Rust tests + 17 sqllogictest files + 6 DuckLake CI tests

---
*Phase: 34.1.1-close-gaps-with-snowflake-show-semantic-ddls-add-like-starts-with-limit*
*Completed: 2026-03-22*

## Self-Check: PASSED

- FOUND: src/parse.rs
- FOUND: test/sql/phase34_1_1_show_filtering.test
- FOUND: .planning/phases/34.1.1-.../34.1.1-01-SUMMARY.md
- FOUND: ecfc415 (TDD RED)
- FOUND: de1728e (TDD GREEN)
- FOUND: f6cb3c9 (sqllogictest)
