Summary

When a schema migration adds columns to a table (e.g., PR #231 adding login_name, host_name, program_name, open_transaction_count, percent_complete to query_snapshots), the archive views silently break. The grid shows no data even though data exists in parquet archives.

Root Cause

DuckDbInitializer.CreateArchiveViewsAsync() creates views like:

CREATE OR REPLACE VIEW v_query_snapshots AS
SELECT * FROM query_snapshots
UNION ALL
SELECT * FROM read_parquet('...', union_by_name=true)

UNION ALL requires both sides to have the same number of columns. After a migration adds columns to the hot table, the table has more columns (29) than the old parquet archives (24). The UNION ALL fails, and the fallback creates a table-only view:

CREATE OR REPLACE VIEW v_query_snapshots AS SELECT * FROM query_snapshots

Since the hot table typically has 0 rows (data was archived), the view returns nothing.

Fix

Change all archive views to use UNION ALL BY NAME instead of UNION ALL. DuckDB supports this syntax and it matches columns by name, filling NULL for any columns missing from the parquet side:

CREATE OR REPLACE VIEW v_query_snapshots AS
SELECT * FROM query_snapshots
UNION ALL BY NAME
SELECT * FROM read_parquet('...', union_by_name=true)

One-line change in DuckDbInitializer.cs line 525. No other changes needed — all reader.IsDBNull() checks are already in place for the new columns, so NULL values from old parquet rows are handled correctly.

Validated

• UNION ALL with mismatched column counts: Binder Error: same number of result columns required
• UNION ALL BY NAME with the real parquet archives and current table schema: returns all 152 archived rows + hot table rows correctly
• New columns (login_name, host_name, program_name, open_transaction_count, percent_complete) return NULL for old parquet data, which the C# reader already handles via IsDBNull() guards

Affected Tables

All tables in ArchivableTables that use this view pattern. Currently query_snapshots is the one hit by PR #231, but any future schema addition to any archivable table would trigger the same bug.

🤖 Generated with Claude Code