DESCRIBE looks like a table, so why can't I SELECT from it?

DESCRIBE TABLE events prints names, types, defaults, codecs and TTL as rows and columns. It looks like a result set in every respect except the one that matters: you cannot do anything with it. I filed #8854 in January 2020 to change that, and the way the request was reshaped in its first day says more about ClickHouse’s design than the feature itself. The issue is still open.

A statement is not a relation

SELECT name FROM (DESCRIBE TABLE events) WHERE type = 'UInt64';

Code: 62. DB::Exception: Syntax error: failed at position 28 (TABLE): TABLE events) WHERE type = 'UInt64'.
Expected one of: token sequence, Dot, token, OpeningRoundBracket, UUID, alias, AS, FINAL, SAMPLE, table, ...

That is a parse error, not a planner or type error. FROM ( expects a SELECT (or a union of them); DESCRIBE is a different top-level statement with its own AST node and its own interpreter that writes a fixed-schema block straight to the client. It never goes through the query planner, so there is no plan node to put inside another query. Everything else in this page follows from that.

What I wanted was ordinary composition: filter columns by type, count them, compare two schemas, and above all ask a source that is not a table yet what shape it has, with the answer usable by the next statement.

The request, and how it widened in a day

My original proposal was a describe table function, so that SELECT name FROM describe(ABC) WHERE type = 'Int32' and SELECT count() FROM describe(jdbc('datasource://abc', 'schema', 'table')) would work.

The first reply came the same day from filimonov, who argued against the function: allowing SELECT name FROM (DESCRIBE TABLE ABC) WHERE type = 'Int32' would “most probably” be “simpler/more logical (comparing to introducing table function which is able to accept another tables / table functions)”. That is the real objection. describe(events) would have to accept a table identifier, or another table function call as its argument, so it would be a function with its own argument-resolution rules, rather than an ordinary one that takes values.

alexey-milovidov then widened the goal beyond DESCRIBE: “We should allow to select from anything returning a table like DESCRIBE, SHOW TABLES, …”. A month later amosbird floated the generic version: “An SQL table function then, though the result schema needs to be inferred.” The issue was labelled st-accepted later that day and renamed in March 2020 to “Allow to use arbitrary queries (e.g. DESCRIBE) in a subquery”, which is what it is still called. My small request had become a statement about the language.

How EXPLAIN and VALUES got through

The interesting part is that this has already happened for two statements. On 26.4:

SELECT count() FROM (EXPLAIN SELECT 1);
SELECT * FROM (VALUES (1, 'a'), (2, 'b'));
SELECT * FROM (EXPLAIN SYNTAX SELECT 1 + 1);

2
1	a
2	b
SELECT 1 + 1
FROM system.one

The current source of ParserSubquery (src/Parsers/ExpressionElementParsers.cpp) shows how. When the parser meets ( it tries SELECT, and failing that it tries EXPLAIN, and if that parses it rewrites the node:

/// Replace subquery `(EXPLAIN <kind> <explain_settings> SELECT ...)`
/// with `(SELECT * FROM viewExplain('<kind>', '<explain_settings>', (SELECT ...)))`

(VALUES ...) is rewritten the same way into a SQLStandardValues(...) table function. So the maintainers’ “select from anything” ended up implemented as syntax sugar over table functions: the statement keeps its friendly spelling, the planner only ever sees a table function. viewExplain is even visible, if you look for it:

SELECT name FROM system.table_functions WHERE name ILIKE '%view%' ORDER BY name;
-- view, viewExplain, viewIfPermitted

Note what that means for my original proposal. A describe table function is not wrong; it is the mechanism. The disagreement was only about the surface: should users call describe(x), or write (DESCRIBE TABLE x) and let the parser do the translation? EXPLAIN answers that: the second. DESCRIBE has not been wired up to it.

The 2025 attempt

In March 2025 an outside contributor, benwu25, opened PR #78378, “Add parser utilities for allowing DESCRIBE TABLE as a subquery where SELECT is allowed”. It made the examples from the issue parse, but the author wrote: “I am doubtful whether these changes produce an AST that can be processed correctly in the later stages”, and that the trouble was in the QueryTreeBuilder pass, the analyzer stage that turns the AST into a query tree. Parsing the syntax is the cheap half; the other half is producing something the analyzer can resolve, which is what the viewExplain rewrite does by routing around the question. The PR was closed unmerged on 2025-05-21.

I do not know why it was closed rather than redirected to the EXPLAIN-style rewrite, and I will not guess.

Workarounds, and what each costs

system.columns, for tables ClickHouse knows about. These are real tables, so everything composes:

SELECT name, type FROM system.columns
WHERE database = currentDatabase() AND table = 'events' AND type = 'UInt64';
-- user_id  UInt64

Comparing two schemas is a join over two such subqueries (with join_use_nulls = 1 so absent columns show as NULL):

SELECT name, a.type AS events, b.type AS events_v2
FROM (SELECT name, type FROM system.columns WHERE database = currentDatabase() AND table = 'events') AS a
FULL OUTER JOIN (SELECT name, type FROM system.columns WHERE database = currentDatabase() AND table = 'events_v2') AS b USING (name)
WHERE a.type IS DISTINCT FROM b.type
ORDER BY name
SETTINGS join_use_nulls = 1;

country      LowCardinality(String)  NULL
duration_ms  UInt32                  UInt64
event_type   LowCardinality(String)  String
region       NULL                    LowCardinality(String)

It is better than DESCRIBE in one way: it carries more columns (position, data_compressed_bytes, …). It is worse in another: it knows nothing about things that are not tables.

Probe table, for sources with inferred schemas. This is the case the request was really about. DESCRIBE works on a file(), url() or s3() source:

DESCRIBE file('data.jsonl', JSONEachRow);
-- user_id Nullable(Int64), country Nullable(String), score Nullable(Float64), ts Nullable(DateTime)

but its output cannot be filtered. A zero-row CREATE TABLE ... AS SELECT makes ClickHouse infer the schema and register it, after which system.columns is available:

CREATE TEMPORARY TABLE probe ENGINE = Memory
AS SELECT * FROM file('data.jsonl', JSONEachRow) LIMIT 0;

SELECT name, type FROM system.columns WHERE table = 'probe' AND type LIKE '%Float%';
-- score  Nullable(Float64)

I ran this on 26.4, and a temporary table is found by table = 'probe' alone. The costs are a write to the catalog (even a temporary one), a name to manage, and a second statement; it cannot be used in a view or a single SELECT.

Type only, in one query. SELECT * APPLY toTypeName FROM file(...) LIMIT 1 returns the types as a row, in column order, without names; and LIMIT 0 FORMAT JSON returns a meta block with names and types that a client can read. Both are one round trip but neither is SQL-composable: the result is a value, not a relation.

The client. DESCRIBE ... FORMAT JSONCompactEachRow gives one JSON array per column, which is what most tooling ends up consuming. For a script this is often the practical answer, and it is a reason the missing feature has not hurt more.

What it teaches about ClickHouse’s design

• Statements and queries are two grammars. Anything you can put in FROM (...) is a query that the planner can compose. SHOW, DESCRIBE and EXISTS are statements that return a block directly. The boundary is syntactic, and widening it is done one statement at a time.
• The table function is ClickHouse’s universal adapter. When a thing must become a relation, it becomes a table function (viewExplain, SQLStandardValues, numbers, file). The language’s surface can look like anything; the planner sees only that. See table functions as a first-class abstraction.
• Parsing is not the hard part. Both #78378 and the EXPLAIN code say so: a subquery that parses is not a subquery the analyzer can type. Anything that needs a result schema known before execution has to answer “what are the columns?” at analysis time, and DESCRIBE of an inferred source has to read data to do so.
• Introspection lives in system.*, by design. The catalog is already relational, so the missing syntax costs little for tables, and a lot only for what is not yet one.

State on 26.4

Nothing changed for DESCRIBE:

SELECT * FROM describe(events);
-- Code: 46. DB::Exception: Unknown table function describe. (UNKNOWN_FUNCTION)
SELECT * FROM (SHOW TABLES);
-- Code: 62. DB::Exception: Syntax error: failed at position 21 (TABLES): TABLES). ...

EXPLAIN and VALUES work in a subquery; DESCRIBE, SHOW, and EXISTS TABLE do not. If you need to introspect a schema inside SQL today, use system.columns for tables and the probe-table trick for inferred sources. If you need the answer in a script, take it from the client’s FORMAT. I would still use a (DESCRIBE ...) subquery the day it exists, and given that viewExplain already shows the path, a small viewDescribe-style rewrite looks like the shortest route to it.