ClickHouse: MATERIALIZED Column Not Accessible Through VIEW

Problem

When a ClickHouse VIEW selects specific columns from a table (not SELECT *), any MATERIALIZED or ALIAS columns not explicitly included in the view's SELECT list are invisible to queries through the view. Attempting WHERE v.materialized_col = ? on such a view produces a "column not found" error, which surfaces as an HTTP 500 in API layers.

Context / Trigger Conditions

• You add a new query filter (e.g., ?platform=vine) that references a column via a view alias
• The column is defined as String MATERIALIZED ... on the underlying table
• The VIEW was created with an explicit column list (not SELECT *)
• The column works fine when querying the base table directly
• The API returns HTTP 500 with no useful error message to the client
• Server logs show a ClickHouse "column not found" or similar schema error

Solution

Option A: Subquery (No migration required)

Replace direct column reference with a subquery against the base table:

-- BROKEN: view doesn't expose 'platform'
WHERE v.platform = ?

-- FIXED: subquery against the base table where MATERIALIZED column exists
WHERE v.id IN (
  SELECT id FROM events_deduped
  WHERE platform = ? AND kind IN (34235, 34236)
)

Option B: Migration (Cleaner long-term)

Create a new migration that drops and recreates the view to include the column:

DROP VIEW IF EXISTS nostr.videos;
CREATE VIEW nostr.videos AS
SELECT
    id, pubkey, created_at, kind, content, tags, sig, indexed_at,
    d_tag, title, thumbnail, video_url, author_name, loops,
    platform,  -- ADD THE MATERIALIZED COLUMN
    if(published_at > 0, published_at, toUnixTimestamp(created_at)) AS published_at,
    expiration_at
FROM nostr.events_deduped FINAL
WHERE kind IN (34235, 34236);

Warning: Dropping a view cascades — any dependent views (video_stats, trending_videos, videos_with_loops, etc.) must also be dropped and recreated in the correct dependency order.

Verification

1. Query the view directly: SELECT platform FROM videos LIMIT 1 — should return data (or empty string for non-vine)
2. API call with the filter param returns 200 instead of 500
3. Run full smoke test suite to confirm no regressions

Example (Funnelcake)

The nostr.videos view (migration 000060) selects a fixed column list from events_deduped. The platform column is String MATERIALIZED on events_deduped but not in the view.

PR #85 added v.platform = ? to get_recent_videos_with_events() and get_trending_videos_with_events(), both of which query FROM videos v. This caused HTTP 500 for any request with ?platform=vine.

Fix (PR #86): Changed to subquery approach. Note that videos_with_loops (a different view) DOES include platform — queries through that view (like get_videos_filtered) work fine.

Notes

• MATERIALIZED columns are physically stored but only accessible if explicitly selected
• ALIAS columns are computed on read and have the same visibility constraint in views
• Always check the view definition before adding WHERE conditions on columns
• The videos_with_loops view includes more columns than videos — consider which view your query is actually using
• In Funnelcake: videos view = minimal columns; videos_with_loops = full columns including platform