Skip to content
ClickHouse Docs
49.6kGet Started

HYPOTHETICAL PROJECTION

View as MarkdownEdit this pageReport an issueSet up docs MCP

Autogenerated from ClickHouse system tables

Hypothetical projections are virtual, session-scoped projections that you can attach to a MergeTree family table without actually building or storing them. They exist only inside the current session and are listed by EXPLAIN WHATIF.

EXPLAIN WHATIF estimates normal (sorted) hypothetical projections: how many marks and rows the projection read would touch for a query, and whether the optimizer would choose it over the base table. Not every projection is estimated: aggregate projections and projections with a WHERE clause are two such cases, and a candidate that is not estimated is listed with the reason why. See EXPLAIN WHATIF for the full list. The session’s hypothetical projections are also visible in system.hypothetical_projections.

CREATE HYPOTHETICAL PROJECTION

CREATE HYPOTHETICAL PROJECTION [IF NOT EXISTS] name
    ON [db.]table_name (SELECT <columns> [WHERE ...] [GROUP BY ...] [ORDER BY ...]) [WITH SETTINGS (...)]

CREATE HYPOTHETICAL PROJECTION [IF NOT EXISTS] name
    ON [db.]table_name INDEX <expression> TYPE <projection_index_type> [WITH SETTINGS (...)]

The syntax mirrors ALTER TABLE ... ADD PROJECTION, and the definition is validated exactly the same way, so a projection rejected here could not have been materialized either. Nothing is built or written — only the description is stored, in the current session.

  • name — projection name; must be unique within (database, table) for this session, and must not collide with a real projection on the table.
  • The body accepts the same forms as a real projection: a reordering projection with ORDER BY, an aggregating one with GROUP BY, a filtered one with WHERE, or the projection-index form INDEX <expression> TYPE <projection_index_type>.
  • WITH SETTINGS (...) is accepted and preserved; the settings are visible in system.hypothetical_projections.

The target table must be a MergeTree family table in an Atomic database (it must have a UUID), because the session store keys entries by table UUID. The restrictions a real ADD PROJECTION enforces apply here too: tables with UNIQUE KEY, non-Ordinary merging modes under deduplicate_merge_projection_mode = throw, old-syntax MergeTree, and immutable disks are rejected.

Example

CREATE HYPOTHETICAL PROJECTION p_by_b ON t (SELECT a, b ORDER BY b);
CREATE HYPOTHETICAL PROJECTION p_idx ON t INDEX b TYPE basic;

DROP HYPOTHETICAL PROJECTION

DROP HYPOTHETICAL PROJECTION [IF EXISTS] name ON [db.]table_name

Removes a hypothetical projection from the current session.

DROP ALL HYPOTHETICAL PROJECTIONS

DROP ALL HYPOTHETICAL PROJECTIONS

Clears every hypothetical projection defined in the current session, regardless of table. It leaves hypothetical indexes untouched; DROP ALL HYPOTHETICAL INDEXES does the reverse.

Scope and lifetime

  • Hypothetical projections live only in the current session — they are invisible to other sessions and discarded when the session ends.
  • Defining or dropping one builds no projection and never affects ordinary queries against the table.
  • Inspect the current session’s hypothetical projections via system.hypothetical_projections.

Required privileges

CREATE HYPOTHETICAL PROJECTION requires ALTER ADD PROJECTION on the table — the same privilege the real ALTER TABLE ... ADD PROJECTION needs — because it validates the definition against the table’s columns. EXPLAIN WHATIF requires column-level SELECT on the projection’s columns to estimate it, as it already does for CREATE HYPOTHETICAL INDEX.

DROP HYPOTHETICAL PROJECTION requires the same privilege, so that naming a table in a drop cannot reveal whether it exists or is eligible, and EXPLAIN WHATIF re-checks it before validating a stored definition against the table. DROP ALL HYPOTHETICAL PROJECTIONS names no table and requires no privilege.

See also

Navigation