---
phase: 04-query-interface
plan: "03"
type: execute
wave: 2
depends_on:
  - "04-01"
files_modified:
  - test/sql/phase4_query.test
  - configure/setup_ducklake.py
  - test/sql/phase4_iceberg.test
  - Justfile
  - .gitignore
autonomous: true
requirements:
  - TEST-03
  - TEST-04

must_haves:
  truths:
    - "SQLLogicTest defines a semantic view, runs semantic_query with dimensions+metrics, and asserts correct aggregate values"
    - "SQLLogicTest verifies WHERE composition: view filters + user WHERE both apply"
    - "SQLLogicTest verifies multi-table join semantic view returns correct joined aggregates"
    - "SQLLogicTest compares EXPLAIN output of semantic_query vs hand-written SQL for plan equivalence"
    - "An integration test runs semantic_query against DuckLake/Iceberg tables and returns correct results"
  artifacts:
    - path: "test/sql/phase4_query.test"
      provides: "SQLLogicTest covering basic round-trip, WHERE composition, multi-join, and EXPLAIN equivalence"
      contains: "semantic_query"
    - path: "configure/setup_ducklake.py"
      provides: "Python script creating DuckLake catalog and loading jaffle-shop data"
      contains: "ducklake"
    - path: "test/sql/phase4_iceberg.test"
      provides: "SQLLogicTest for DuckLake/Iceberg integration test"
      contains: "ducklake"
    - path: "Justfile"
      provides: "Updated Just recipes for DuckLake setup"
      contains: "setup-ducklake"
  key_links:
    - from: "test/sql/phase4_query.test"
      to: "src/query/table_function.rs"
      via: "semantic_query() function calls in test SQL"
      pattern: "semantic_query"
    - from: "configure/setup_ducklake.py"
      to: "test/sql/phase4_iceberg.test"
      via: "Python creates DuckLake catalog used by test"
      pattern: "ducklake"
---

<objective>
Create comprehensive integration tests covering the full query round-trip: basic aggregation, WHERE composition, multi-table joins, EXPLAIN plan equivalence, and DuckLake/Iceberg source queries.

Purpose: TEST-03 and TEST-04 require end-to-end verification that semantic views produce correct results against real DuckDB databases, including Apache Iceberg tables via DuckLake. These tests catch integration bugs that unit tests cannot (type mapping, FFI correctness, query plan identity).

Output: Two SQLLogicTest files covering all five required test scenarios, plus a Python script for DuckLake setup and Just recipes for convenience.
</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/04-query-interface/04-RESEARCH.md
@.planning/phases/04-query-interface/04-CONTEXT.md
@.planning/phases/04-query-interface/04-01-SUMMARY.md
@test/sql/phase2_ddl.test
@Justfile
</context>

<tasks>

<task type="auto">
  <name>Task 1: Core query integration tests (SQLLogicTest)</name>
  <files>
    test/sql/phase4_query.test
  </files>
  <action>
Create `test/sql/phase4_query.test` covering four of the five required test scenarios (Iceberg is in Task 2).

**Test file structure:**

```
# name: test/sql/phase4_query.test
# group: [semantic_views]

require semantic_views
```

**Section 1: Setup -- Create test tables with known data**
```sql
CREATE TABLE test_orders (
    id INTEGER, region VARCHAR, category VARCHAR,
    amount DECIMAL(10,2), status VARCHAR, customer_id INTEGER
);
INSERT INTO test_orders VALUES
    (1, 'EMEA', 'electronics', 100.00, 'completed', 1),
    (2, 'APAC', 'electronics', 200.00, 'completed', 2),
    (3, 'EMEA', 'clothing', 50.00, 'pending', 1),
    (4, 'APAC', 'clothing', 150.00, 'completed', 3),
    (5, 'EMEA', 'electronics', 75.00, 'completed', 2);

CREATE TABLE test_customers (
    id INTEGER, name VARCHAR, tier VARCHAR
);
INSERT INTO test_customers VALUES
    (1, 'Alice', 'gold'),
    (2, 'Bob', 'silver'),
    (3, 'Charlie', 'gold');

CREATE TABLE test_products (
    category VARCHAR, supplier VARCHAR
);
INSERT INTO test_products VALUES
    ('electronics', 'TechCorp'),
    ('clothing', 'FashionInc');
```

**Section 2: Scenario 1 -- Basic round-trip (TEST-03, QUERY-01)**
- Define a semantic view over test_orders with dimensions [region, category] and metrics [total_revenue (sum(amount)), order_count (count(*))]
- Query with `dimensions := ['region'], metrics := ['total_revenue']`
- Assert: APAC=350.00, EMEA=225.00 (use `query RR rowsort` to handle ordering)
- Query with `dimensions := ['region', 'category'], metrics := ['total_revenue', 'order_count']`
- Assert correct 4-row result (APAC/clothing, APAC/electronics, EMEA/clothing, EMEA/electronics)

**Section 3: Scenario 2 -- WHERE composition (QUERY-02)**
- Using the same semantic view, add `filters: ["status = 'completed'"]` to the definition (redefine or define a new view with filters)
- Query with `dimensions := ['region'], metrics := ['total_revenue']` -- should only count completed orders
- Assert: APAC=350.00, EMEA=175.00 (excludes EMEA pending order of 50.00)
- Query with user WHERE: `SELECT * FROM semantic_query('filtered_orders', dimensions := ['region'], metrics := ['total_revenue']) WHERE region = 'EMEA'`
- Assert: single row EMEA=175.00 (both view filter AND user WHERE applied)

**Section 4: Scenario 4 -- Multi-table joins (QUERY-01 with joins)**
- Define a semantic view with:
  - base_table: test_orders
  - joins: [{table: "test_customers", on: "test_orders.customer_id = test_customers.id"}, {table: "test_products", on: "test_orders.category = test_products.category"}]
  - dimensions: [region, customer_tier (source_table: test_customers, expr: test_customers.tier), supplier (source_table: test_products, expr: test_products.supplier)]
  - metrics: [total_revenue (sum(test_orders.amount)), order_count (count(*))]
- Query with `dimensions := ['customer_tier'], metrics := ['total_revenue']`
- Assert: gold=300.00 (Alice 100+50, Charlie 150), silver=275.00 (Bob 200+75)
- Query with `dimensions := ['supplier'], metrics := ['order_count']`
- Assert: TechCorp=3, FashionInc=2

**Section 5: Scenario 5 -- EXPLAIN plan equivalence**
- Define a simple view and query both via semantic_query and via hand-written equivalent SQL
- For the semantic view query, run it through `explain_semantic_view` and verify the expanded SQL appears in output
- For plan equivalence: run `EXPLAIN SELECT region, sum(amount) AS total FROM test_orders GROUP BY region` and `EXPLAIN FROM semantic_query('simple_orders', dimensions := ['region'], metrics := ['total_revenue'])` -- both should produce the same physical plan
- Use `query T` to capture EXPLAIN output and compare key elements (the exact text may differ due to function wrapping, so check for structural equivalence rather than exact match -- look for the same scan and aggregation operators)
- If exact match is too fragile, verify that `explain_semantic_view` output contains the expected SQL string

**Section 6: Dimensions-only and metrics-only queries**
- Query with `dimensions := ['region']` only (no metrics) -- should return DISTINCT region values
- Assert: APAC, EMEA (2 rows)
- Query with `metrics := ['total_revenue']` only (no dimensions) -- should return global aggregate
- Assert: single row 575.00

**Section 7: Error cases**
- `FROM semantic_query('nonexistent', dimensions := ['x'])` -- error with "not found" and suggestion
- `FROM semantic_query('simple_orders', dimensions := ['nonexistent'])` -- error with "unknown dimension"
- `FROM semantic_query('simple_orders')` -- error with "specify at least dimensions or metrics"

**Section 8: Cleanup**
- Drop all semantic views and test tables for idempotency

**SQLLogicTest format notes:**
- Use `statement ok` for DDL/DML setup
- Use `query <types> rowsort` with expected output for SELECT results
- Use `statement error <substring>` for expected errors
- Types: R for decimal/float, I for integer, T for text
- Check existing `test/sql/phase2_ddl.test` for format patterns used in this project
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just test-sql 2>&1 | tail -20</automated>
    <manual>All sections in phase4_query.test pass. Verify result values match hand-calculated expected values.</manual>
  </verify>
  <done>SQLLogicTest covers basic round-trip, WHERE composition, multi-table joins, EXPLAIN equivalence, dimensions-only, metrics-only, and error cases. All tests pass with correct aggregate values.</done>
</task>

<task type="auto">
  <name>Task 2: DuckLake/Iceberg integration test and setup infrastructure</name>
  <files>
    configure/setup_ducklake.py
    test/sql/phase4_iceberg.test
    Justfile
    .gitignore
  </files>
  <action>
Create the DuckLake integration test infrastructure per CONTEXT.md decisions.

**`configure/setup_ducklake.py`** -- Python script that:
1. Downloads jaffle-shop sample data from dbt-labs S3 (raw_orders.csv, raw_customers.csv, raw_items.csv)
   - URL pattern: `https://raw.githubusercontent.com/dbt-labs/jaffle-shop/main/seeds/raw_orders.csv` (and similar for customers, items)
   - If download fails, skip gracefully with a message (CI may not have network access)
2. Creates a local DuckLake catalog:
   ```python
   import duckdb
   con = duckdb.connect('test/data/test_catalog.duckdb')
   con.execute("INSTALL ducklake; LOAD ducklake;")
   con.execute("ATTACH 'ducklake:test/data/jaffle.ducklake' AS jaffle (DATA_PATH 'test/data/jaffle_data/')")
   con.execute("CREATE OR REPLACE TABLE jaffle.raw_orders AS SELECT * FROM read_csv('test/data/seeds/raw_orders.csv')")
   con.execute("CREATE OR REPLACE TABLE jaffle.raw_customers AS SELECT * FROM read_csv('test/data/seeds/raw_customers.csv')")
   con.execute("CREATE OR REPLACE TABLE jaffle.raw_items AS SELECT * FROM read_csv('test/data/seeds/raw_items.csv')")
   ```
3. Creates `test/data/` directory structure
4. Is idempotent -- safe to run multiple times

**`test/sql/phase4_iceberg.test`** -- SQLLogicTest for Iceberg/DuckLake:

```
# name: test/sql/phase4_iceberg.test
# group: [semantic_views]

require semantic_views

# This test requires DuckLake setup. Run `just setup-ducklake` first.
# If DuckLake is not available, this test will be skipped.
```

**Test scenario 3 from CONTEXT.md: Iceberg source query**
- Install and load DuckLake: `INSTALL ducklake; LOAD ducklake;`
- Attach the DuckLake catalog: `ATTACH 'ducklake:test/data/jaffle.ducklake' AS jaffle (DATA_PATH 'test/data/jaffle_data/')`
- Define a semantic view over `jaffle.raw_orders` with:
  - dimensions: [status (expr: status)]
  - metrics: [order_count (expr: count(*)), total_orders_value (expr: count(id))]
- Query and assert correct results match the jaffle-shop dataset
- This proves the extension works with DuckLake/Iceberg tables

**NOTE:** The DuckLake test may need to be marked as optional or require a prerequisite (`require ducklake` or a setup check). If the SQLLogicTest runner cannot install extensions dynamically, fall back to a Python-based integration test script instead (e.g., `test/integration/test_ducklake.py`) that can be run separately via `just test-iceberg`.

**If SQLLogicTest runner cannot install DuckLake:**
Create `test/integration/test_ducklake.py` instead:
```python
import duckdb
# Load semantic_views extension
# Install and load DuckLake
# Create DuckLake catalog with test data inline (no external files needed)
# Define semantic view, run query, assert results
```
And add a Just recipe `test-iceberg` that runs this Python test.

**Justfile updates:**
- Add `setup-ducklake` recipe: `python3 configure/setup_ducklake.py`
- Add `test-iceberg` recipe: runs the DuckLake integration test (either via `just test-sql` if in SQLLogicTest format, or via `python3 test/integration/test_ducklake.py`)
- Update `test-all` to include the new test recipes

**.gitignore updates:**
- Add `test/data/seeds/` (downloaded CSV files)
- Add `test/data/*.ducklake` (DuckLake catalog files)
- Add `test/data/jaffle_data/` (DuckLake data directory)
- Add `test/data/*.duckdb` (test catalog databases)

**Important:** The jaffle-shop data files MUST be gitignored per CONTEXT.md. Only the setup script and test files are committed.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && just test-sql 2>&1 | tail -20</automated>
    <manual>Run `just setup-ducklake && just test-iceberg` to verify DuckLake integration test passes end-to-end</manual>
  </verify>
  <done>DuckLake setup script downloads jaffle-shop data and creates Iceberg catalog. Integration test verifies semantic_query works against DuckLake/Iceberg tables. Data files are gitignored. Just recipes provide convenient test execution.</done>
</task>

</tasks>

<verification>
1. `just test-sql` passes all SQLLogicTest files (existing + new phase4_query.test)
2. `just setup-ducklake` downloads data and creates DuckLake catalog without error
3. `just test-iceberg` passes the DuckLake/Iceberg integration test
4. `just test-all` runs all tests including the new integration tests
5. `git status` shows no data files tracked (all in .gitignore)
</verification>

<success_criteria>
- Basic round-trip test: define view, query with dims+metrics, assert correct aggregates
- WHERE composition test: view filters + user WHERE both applied correctly
- Multi-join test: semantic view with joins across 3 tables returns correct results
- EXPLAIN equivalence test: semantic view query plan matches hand-written SQL plan
- Iceberg/DuckLake test: semantic_query works against DuckLake/Iceberg tables
- All tests pass reliably and are idempotent
- Data files are gitignored, only scripts are committed
</success_criteria>

<output>
After completion, create `.planning/phases/04-query-interface/04-03-SUMMARY.md`
</output>
