Skip to main content
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. For a query, it shows how many marks and rows a read of the projection would touch. It also shows whether the optimizer would choose the projection over the base table. It does not estimate aggregate projections and some other projections, and it lists each such candidate with the reason. See EXPLAIN WHATIF for the full list. The session’s hypothetical projections are also visible in 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. 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