clickhouse-materialized-column-view-filter

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.