# Phase 17.1: Python vtab Crash Investigation - Research

**Researched:** 2026-03-08
**Domain:** DuckDB C API table function lifecycle, zero-copy vector operations, connection management, Python client interaction
**Confidence:** MEDIUM (crash not yet reproduced; analysis based on code inspection and known DuckDB pitfalls)

## Summary

This phase investigates crashes occurring when the semantic_views extension's table functions (vtabs) are used from the Python DuckDB client. The extension uses several patterns that interact with DuckDB's internal memory management: raw `duckdb_connection` pointers stored in `extra_info`, zero-copy vector referencing via `duckdb_vector_reference_vector`, and SQL execution via `duckdb_query` during bind/plan phases.

Code inspection reveals **five candidate crash vectors**, ranked by likelihood. The most probable cause is a **type mismatch in `duckdb_vector_reference_vector`** -- the zero-copy transfer in `SemanticViewVTab::func()` assumes the source query's column types exactly match the bind-time schema declaration. When DuckDB's optimizer changes runtime types (e.g., HUGEINT for `sum()` at planning time but BIGINT at execution time), this mismatch triggers the internal assertion `"Vector::Reference used on vector of different type"`. The `build_execution_sql` wrapper handles some known cases (HUGEINT, STRUCT, MAP) but may miss others under different query plans or attached database types (e.g., DuckLake/Iceberg).

A secondary concern is **connection lifetime management** -- raw `duckdb_connection` pointers (`query_conn`, `persist_conn`, `ddl_conn`) are created at extension init time but never explicitly destroyed. If the Python client's garbage collector collects the main connection object before these internal connections are closed, use-after-free could occur.

**Primary recommendation:** Build a systematic Python reproduction test script that exercises all table function paths (define, query, explain, list, describe, drop) with various column types, followed by targeted fixes for confirmed crash vectors.

## Standard Stack

### Core
| Library | Version | Purpose | Why Standard |
|---------|---------|---------|--------------|
| duckdb (Python) | 1.4.4 | Python client for crash reproduction | Same version as extension target |
| duckdb-rs | =1.4.4 | Rust bindings, VTab trait | Locked to extension version |
| libduckdb-sys | =1.4.4 | Raw C API FFI bindings | Locked to extension version |

### Supporting
| Library | Version | Purpose | When to Use |
|---------|---------|---------|-------------|
| lldb / gdb | system | Debugger for crash analysis | When reproducing SIGSEGV/SIGABRT |
| ASAN | gcc/clang built-in | Address sanitizer for memory issues | If crash involves use-after-free |

## Architecture Patterns

### Current Extension Architecture (Relevant to Crash)

```
Python DuckDB client
  |
  +-- LOAD semantic_views.duckdb_extension
  |     |
  |     +-- semantic_views_init_c_api()
  |           |
  |           +-- Connection::open_from_raw(db_handle) -- "main" connection
  |           +-- duckdb_connect(db_handle) -> persist_conn (file-backed only)
  |           +-- duckdb_connect(db_handle) -> query_conn (always)
  |           +-- duckdb_connect(db_handle) -> ddl_conn (always)
  |           |
  |           +-- register_table_function_with_extra_info:
  |           |     create_semantic_view  -- DefineState {catalog, persist_conn}
  |           |     semantic_view         -- QueryState {catalog, query_conn}
  |           |     explain_semantic_view -- QueryState {catalog, query_conn}
  |           |     (and others)
  |           |
  |           +-- sv_register_parser_hooks(db_handle, ddl_conn)
  |                 (C++ shim stores ddl_conn in file-scope static)
```

### Crash Vector Analysis

#### CV-1: `duckdb_vector_reference_vector` Type Mismatch (HIGH likelihood)
**What:** `SemanticViewVTab::func()` performs zero-copy vector transfer from source result chunks to output chunks. The source vectors come from executing the expanded SQL on `query_conn`; the output vectors were sized/typed at bind time.

**Why it crashes:** If the bind-time type declarations (from DDL-time inference or LIMIT 0) don't exactly match the runtime query result types, `duckdb_vector_reference_vector` triggers an internal DuckDB assertion: `"Vector::Reference used on vector of different type"`. This is a hard crash (SIGABRT), not an error.

**Known mitigations already in code:**
- `normalize_type_id()` converts HUGEINT->BIGINT, UHUGEINT->UBIGINT
- `build_execution_sql()` wraps with explicit casts for BIGINT, UBIGINT, STRUCT, MAP, INVALID(VARCHAR)

**What may still fail:**
- Column types not covered by the cast wrapper (ENUM, DECIMAL scale changes, new types from DuckLake/Iceberg)
- DDL-time inference stored types becoming stale if the underlying table schema changes
- In-memory databases where LIMIT 0 inference at query time produces different types than the actual query

**Code location:** `src/query/table_function.rs:587-598` (the `duckdb_vector_reference_vector` loop)

#### CV-2: Connection Lifetime / Use-After-Free (MEDIUM likelihood)
**What:** Three raw `duckdb_connection` handles are created at init time and stored in `extra_info` structs (which are cloned into DuckDB's catalog). These connections are never explicitly destroyed.

**Why it might crash:** The `Connection` created from `Connection::open_from_raw(db_handle)` in `semantic_views_init_c_api_internal` is a local variable. When the Rust function returns, this `Connection` is dropped. The `duckdb-rs` `Connection::drop()` calls `duckdb_disconnect`. However, the raw `query_conn`, `persist_conn`, and `ddl_conn` connections created via `duckdb_connect` are NOT wrapped in any RAII guard -- they leak (intentionally, since they must live for the database lifetime). If the Python client or DuckDB's internal teardown sequence closes the database before these connections are properly cleaned up, use-after-free could occur.

**Additional concern:** The static C++ variable `sv_ddl_conn` in `shim.cpp` holds a raw connection pointer with no lifetime management.

**Code location:** `src/lib.rs:336-456` (connection creation), `cpp/src/shim.cpp:51` (static `sv_ddl_conn`)

#### CV-3: `duckdb_query` During Bind/Plan Phase (MEDIUM likelihood)
**What:** Several vtab `bind()` implementations execute SQL via `duckdb_query` on a separate connection during the bind phase:
- `DefineSemanticViewVTab::bind()` runs LIMIT 0 for type inference on `persist_conn`
- `SemanticViewVTab::bind()` runs LIMIT 0 for type inference on `query_conn`
- The C++ `sv_ddl_bind()` runs the rewritten DDL on `ddl_conn`

**Why it might crash:** The Python DuckDB client may handle the bind phase differently from the CLI. If Python's execution context holds different locks or if the GIL interacts with DuckDB's internal threading model, executing `duckdb_query` on a separate connection during bind could deadlock or trigger assertion failures. The Phase 17-01 decision explicitly notes this was a known concern: "avoids ClientContext deadlock during parser hook plan/bind."

**Code location:** `src/ddl/define.rs:123-145`, `src/query/table_function.rs:443,484-485`, `cpp/src/shim.cpp:95-121`

#### CV-4: Materialized vs Streaming Result Confusion (LOW likelihood)
**What:** `SemanticViewVTab::func()` uses `duckdb_result_chunk_count` and `duckdb_result_get_chunk` to consume results. These functions assume the result is materialized (not streaming).

**Why it might crash:** If `duckdb_query` returns a streaming result in certain configurations, `duckdb_result_chunk_count` may return 0 or garbage, and `duckdb_result_get_chunk` may return null or invalid pointers.

**Mitigation already in code:** The code checks for null chunks and zero row counts.

**Code location:** `src/query/table_function.rs:551-558`

#### CV-5: `extra_info` Pointer Lifetime (LOW likelihood)
**What:** `register_table_function_with_extra_info` clones the `extra_info` value. The cloned `QueryState` contains a raw `duckdb_connection` pointer. The original `QueryState` goes out of scope at the end of `init_extension`, but the cloned copy persists in DuckDB's catalog.

**Why it might crash:** The clone copies the raw pointer value, not the connection itself. Both the original and clone point to the same connection. Since the original `QueryState` is dropped (but the connection handle is NOT wrapped in RAII), this should be safe -- the raw pointer remains valid. However, if `duckdb-rs` internally provides a delete callback that tries to drop the extra_info, it could call drop on the `QueryState` which would NOT close the connection (no Drop impl). This is likely safe but worth verifying.

**Code location:** `src/query/table_function.rs:31-41` (`QueryState` struct and safety impls)

### Anti-Patterns to Avoid
- **Raw pointer connections without RAII guards:** All three auxiliary connections (`persist_conn`, `query_conn`, `ddl_conn`) are raw pointers with no `Drop` implementation. While intentional (they must outlive the init function), this makes cleanup ordering fragile.
- **Type assumptions across the FFI boundary:** The `duckdb_vector_reference_vector` call assumes exact type match without any runtime check. DuckDB's internal assertion catches mismatches, but crashes instead of returning an error.
- **Static mutable state in C++:** `sv_ddl_conn` is a file-scope static with no synchronization. If parser hooks are called concurrently, this could cause data races (though DuckDB likely serializes parser calls).

## Don't Hand-Roll

| Problem | Don't Build | Use Instead | Why |
|---------|-------------|-------------|-----|
| Crash reproduction | Manual Python scripts only | Structured test matrix with known-crashy type combinations | Systematic coverage finds edge cases manual testing misses |
| Type mismatch debugging | Guessing which types fail | LIMIT 0 result type comparison against bind declarations | Definitively identifies the mismatch |
| Memory debugging | printf/eprintln | ASAN/UBSAN (Address/UndefinedBehavior Sanitizer) builds | Detects use-after-free, double-free that manual inspection misses |

**Key insight:** The crash may only manifest under specific conditions (certain column types, DuckLake-backed tables, in-memory vs file-backed) that the existing test suite doesn't cover from Python. A systematic reproduction matrix is essential.

## Common Pitfalls

### Pitfall 1: Vector Type Mismatch in Zero-Copy Transfer
**What goes wrong:** `duckdb_vector_reference_vector` crashes with SIGABRT when source and destination vectors have different DuckDB types.
**Why it happens:** The bind phase declares output column types based on DDL-time inference or LIMIT 0 results. The execution phase runs the actual query, whose optimizer may choose different internal types. The `build_execution_sql` wrapper covers known cases but may miss new ones.
**How to avoid:** Either (a) always cast all columns in the execution SQL to match bind declarations, or (b) validate types at runtime before calling `duckdb_vector_reference_vector` and fall back to per-value copy on mismatch.
**Warning signs:** Crash only with certain column types, works fine with VARCHAR/INTEGER but fails with DECIMAL, DATE, or types from external catalogs (DuckLake/Iceberg).

### Pitfall 2: Python GIL and DuckDB Connection Interactions
**What goes wrong:** Python's GIL (Global Interpreter Lock) and DuckDB's internal locking may interact in unexpected ways when the extension executes SQL on separate connections during bind.
**Why it happens:** The CLI has no GIL. Python holds the GIL during DuckDB calls, and DuckDB's bind phase may try to acquire internal locks that conflict with locks held by the separate connections.
**How to avoid:** Minimize work done during bind -- defer SQL execution to the func phase where possible. If bind-time SQL execution is necessary, ensure it uses a truly independent connection.
**Warning signs:** Deadlock (hang, not crash) when using `semantic_view()` from Python but not from CLI.

### Pitfall 3: Missing Drop for Auxiliary Connections
**What goes wrong:** When the database is closed (Python `con.close()` or garbage collection), the auxiliary connections (`query_conn`, `persist_conn`, `ddl_conn`) are not explicitly closed. This may cause use-after-free if DuckDB's shutdown sequence frees the `DatabaseInstance` before the connections finish their cleanup.
**Why it happens:** Raw `duckdb_connection` pointers have no RAII wrapper. The connections are intentionally leaked to outlive the init function, but they also outlive the database shutdown.
**How to avoid:** Store connections in a struct with a `Drop` implementation that calls `duckdb_disconnect`, or use DuckDB's `duckdb_table_function_set_extra_info` with a delete callback that performs cleanup.
**Warning signs:** Crash on Python process exit or when calling `con.close()` explicitly.

### Pitfall 4: Stale Type Inference from DDL-Time
**What goes wrong:** Types inferred at DDL time (when `create_semantic_view` is called) are stored in the JSON definition and reused at query time. If the underlying table schema changes, the stored types are stale.
**Why it happens:** DDL-time inference runs a LIMIT 0 query and stores the results. The semantic view definition doesn't update when the source table changes.
**How to avoid:** For the investigation phase, this is not the primary concern. Future mitigation: re-validate types at query time or invalidate cached types when source tables change.
**Warning signs:** Crash occurs only after ALTER TABLE or schema changes on source tables.

## Code Examples

### Reproduction Test Script Pattern (Python)
```python
#!/usr/bin/env python3
"""Python crash reproduction test for semantic_views vtab."""
import duckdb
import signal
import sys

def timeout_handler(signum, frame):
    print("TIMEOUT - possible deadlock")
    sys.exit(2)

signal.signal(signal.SIGALRM, timeout_handler)
signal.alarm(30)  # 30 second timeout for deadlock detection

def test_basic_query():
    """Minimal reproduction: define view, query it from Python."""
    con = duckdb.connect(":memory:", config={"allow_unsigned_extensions": "true"})
    con.execute("LOAD 'path/to/semantic_views.duckdb_extension'")

    con.execute("CREATE TABLE t (id INTEGER, amount DECIMAL(10,2), dt DATE)")
    con.execute("INSERT INTO t VALUES (1, 100.50, '2024-01-15')")

    con.execute("""SELECT * FROM create_semantic_view('v',
        tables := [{'alias': 't', 'table': 't'}],
        dimensions := [{'name': 'dt', 'expr': "date_trunc('month', dt)", 'source_table': 't'}],
        metrics := [{'name': 'total', 'expr': 'sum(amount)', 'source_table': 't'},
                    {'name': 'cnt', 'expr': 'count(*)', 'source_table': 't'}]
    )""")

    # This is the query that may crash
    result = con.execute("""SELECT * FROM semantic_view('v',
        dimensions := ['dt'],
        metrics := ['total', 'cnt']
    )""").fetchall()
    print(f"Result: {result}")
    con.close()
```
Source: Analysis of `test/integration/test_ducklake_ci.py` patterns

### Type Validation Before Vector Reference (Proposed Fix)
```rust
// In SemanticViewVTab::func(), before the zero-copy loop:
for col_idx in 0..n_cols {
    let src_vec = unsafe {
        ffi::duckdb_data_chunk_get_vector(src_chunk, col_idx as ffi::idx_t)
    };
    let dst_vec = unsafe {
        ffi::duckdb_data_chunk_get_vector(out_ptr, col_idx as ffi::idx_t)
    };

    // SAFETY CHECK: verify types match before zero-copy reference
    let src_type = unsafe { ffi::duckdb_vector_get_column_type(src_vec) };
    let dst_type = unsafe { ffi::duckdb_vector_get_column_type(dst_vec) };
    let src_type_id = unsafe { ffi::duckdb_get_type_id(src_type) };
    let dst_type_id = unsafe { ffi::duckdb_get_type_id(dst_type) };

    if src_type_id == dst_type_id {
        unsafe { ffi::duckdb_vector_reference_vector(dst_vec, src_vec) };
    } else {
        // Fall back to per-value copy or cast
        // Log the mismatch for debugging
        eprintln!(
            "Type mismatch col {}: src={:?} dst={:?}, falling back to copy",
            col_idx, src_type_id, dst_type_id
        );
        // TODO: implement per-value copy fallback
    }

    unsafe {
        ffi::duckdb_destroy_logical_type(&mut { src_type });
        ffi::duckdb_destroy_logical_type(&mut { dst_type });
    }
}
```
Source: Analysis of DuckDB C API vector docs and issue #15584, #19238

## State of the Art

| Old Approach | Current Approach | When Changed | Impact |
|--------------|------------------|--------------|--------|
| Per-value binary read/write dispatch | Zero-copy `duckdb_vector_reference_vector` | v0.3.0 (2026-03-03) | Eliminated ~600 LOC but introduced type-mismatch crash risk |
| No DDL-time type inference | LIMIT 0 at DDL time, stored in JSON | v0.2.0 (2026-03-03) | Enables typed output but creates stale-type risk |
| Single connection for everything | Separate connections for query/persist/ddl | v0.2.0+ | Avoids deadlocks but creates connection lifetime complexity |
| `duckdb_result_chunk_count` (materialized) | `duckdb_fetch_chunk` (streaming, recommended) | DuckDB deprecation notice | Current code uses the deprecated API |

## Investigation Approach

### Step 1: Reproduce the Crash
1. Build the extension: `just build`
2. Run the existing Python integration tests: `just test-ducklake-ci`
3. Create a minimal Python reproduction script with various column types:
   - VARCHAR (simple, should work)
   - BIGINT (count metrics)
   - DECIMAL (sum metrics)
   - DATE (date_trunc dimensions)
   - Mixed types in a single query
4. Test with in-memory database AND file-backed database
5. Test with and without DuckLake/Iceberg tables

### Step 2: Capture Crash Details
- Run Python under lldb: `lldb python3 -- script.py`
- Get the backtrace: `bt` at crash point
- Check if it's SIGABRT (assertion) or SIGSEGV (memory access)
- If SIGABRT: likely type mismatch in `duckdb_vector_reference_vector`
- If SIGSEGV: likely use-after-free on a connection pointer

### Step 3: Fix Based on Root Cause
- **If CV-1 (type mismatch):** Add runtime type check before `duckdb_vector_reference_vector`, expand the cast wrapper in `build_execution_sql` to cover all types, or fall back to per-value copy
- **If CV-2 (connection lifetime):** Add RAII wrappers for auxiliary connections, implement proper cleanup ordering
- **If CV-3 (bind-time SQL):** Defer LIMIT 0 inference to func phase, or add timeout/deadlock detection
- **If CV-4 (materialized result):** Switch from `duckdb_result_chunk_count`/`duckdb_result_get_chunk` to `duckdb_fetch_chunk` (streaming API)

## Open Questions

1. **What is the exact crash symptom?**
   - What we know: "Python vtab crash" was reported as urgent
   - What's unclear: Is it SIGABRT (assertion), SIGSEGV (memory), or a Python exception? What operation triggers it?
   - Recommendation: First step is reproducing the crash and capturing the exact error/signal

2. **Is the crash reproducible from the CLI?**
   - What we know: SQL logic tests pass (they use the CLI), suggesting this may be Python-specific
   - What's unclear: Whether the crash requires specific Python client behavior (GIL, garbage collection, connection lifecycle)
   - Recommendation: Try the exact same SQL sequence in the CLI to isolate Python-specific factors

3. **Does the crash involve DuckLake/Iceberg tables specifically?**
   - What we know: DuckLake tables use different type systems (Iceberg types mapped to DuckDB types), which could trigger type mismatches in the zero-copy path
   - What's unclear: Whether the crash only occurs with DuckLake or also with plain DuckDB tables
   - Recommendation: Test both paths in the reproduction script

4. **Is `duckdb_result_chunk_count` deprecated and problematic?**
   - What we know: DuckDB documentation mentions deprecation of the materialized result API
   - What's unclear: Whether the materialized API causes issues specifically under the Python client
   - Recommendation: LOW priority -- only investigate if other crash vectors are ruled out

## Validation Architecture

### Test Framework
| Property | Value |
|----------|-------|
| Framework | Python + just test-all |
| Config file | Justfile + Makefile |
| Quick run command | `just test-ducklake-ci` |
| Full suite command | `just test-all` |

### Phase Requirements -> Test Map
| Req ID | Behavior | Test Type | Automated Command | File Exists? |
|--------|----------|-----------|-------------------|-------------|
| CRASH-01 | Reproduce crash from Python | manual/script | `python3 test/integration/test_vtab_crash.py` | No - Wave 0 |
| CRASH-02 | Identify root cause | manual/debugger | `lldb python3 -- test/integration/test_vtab_crash.py` | No - Wave 0 |
| CRASH-03 | Fix prevents crash | integration | `just test-ducklake-ci` (post-fix) | Yes (existing) |
| CRASH-04 | No regression in CLI tests | integration | `just test-sql` | Yes (existing) |
| CRASH-05 | No regression in Rust tests | unit | `cargo test` | Yes (existing) |

### Sampling Rate
- **Per task commit:** `just test-ducklake-ci` (Python-specific)
- **Per wave merge:** `just test-all`
- **Phase gate:** Full suite green before /gsd:verify-work

### Wave 0 Gaps
- [ ] `test/integration/test_vtab_crash.py` -- minimal Python reproduction script
- [ ] Crash reproduction confirmed (or investigation pivots to different approach)

## Sources

### Primary (HIGH confidence)
- Project source code: `src/query/table_function.rs`, `src/lib.rs`, `cpp/src/shim.cpp` -- direct code inspection
- [DuckDB C API Vector docs](https://duckdb.org/docs/stable/clients/c/vector) -- `duckdb_vector_reference_vector` API
- [DuckDB C API Table Functions](https://duckdb.org/docs/stable/clients/c/table_functions) -- lifecycle and extra_info
- [duckdb-rs VTab DeepWiki](https://deepwiki.com/duckdb/duckdb-rs/5.1-virtual-tables-(vtab)) -- VTab trait patterns

### Secondary (MEDIUM confidence)
- [DuckDB Issue #19238](https://github.com/duckdb/duckdb/issues/19238) -- Vector::Reference type mismatch with DuckLake/Iceberg
- [DuckDB Issue #15584](https://github.com/duckdb/duckdb/issues/15584) -- Vector::Reference type mismatch in join decorrelation
- [duckdb-rs Issue #467](https://github.com/duckdb/duckdb-rs/issues/467) -- Segfault with remote table functions in 1.2.1
- [DuckDB Crash Troubleshooting](https://duckdb.org/docs/stable/guides/troubleshooting/crashes) -- general crash diagnosis
- [docs.rs duckdb Connection](https://docs.rs/duckdb/latest/duckdb/struct.Connection.html) -- register_table_function_with_extra_info semantics

### Tertiary (LOW confidence)
- [DuckDB Issue #3141](https://github.com/duckdb/duckdb/issues/3141) -- Python crash after connection close
- [DuckDB Python #127](https://github.com/duckdb/duckdb-python/issues/127) -- Segfault with multiple partitioned writes
- Web search for Python-specific vtab crashes -- no exact match found, needs validation

## Metadata

**Confidence breakdown:**
- Crash vector analysis: MEDIUM - based on code inspection and DuckDB issue patterns, not confirmed reproduction
- Standard stack: HIGH - versions are pinned in Cargo.toml
- Architecture: HIGH - direct code reading
- Pitfalls: MEDIUM - extrapolated from known DuckDB issues, not all confirmed for this project

**Research date:** 2026-03-08
**Valid until:** Investigation results (7 days -- crash investigation findings may change plan direction)
