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 does not estimate the benefit of a hypothetical projection yet — it reports each one with status: not_applicable. Defining them is useful today for validating a definition against the table without materializing it, and for tooling that reads system.hypothetical_projections.
CREATE HYPOTHETICAL PROJECTION
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
DROP HYPOTHETICAL PROJECTION
Removes a hypothetical projection from the current session.
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. It reads no table data, so SELECT on the projection’s columns is not required yet; when EXPLAIN WHATIF starts estimating projections it will read those columns and column-level SELECT will be required then, as it already is 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. DROP ALL HYPOTHETICAL PROJECTIONS names no table and requires no privilege.
See also
Last modified on August 28, 2026
