Use case

1. Allow to plug in external catalogs with databases and tables into a database in ClickHouse. This adds a level of nesting, as a database in ClickHouse can contain other databases.

2. Compatibility with Postgres, which has a two-level hierarchy with databases and schemas.

Describe the solution you'd like

We already allow database and table names with arbitrary characters, including dots. For example, if you have a database named a.b and a table named c.d with a column e.f, you can query it with SELECT "a.b"."c.d"."e.f" FROM "a.b"."c.d" - if you properly quoted these names.

The idea is to allow writing these names without quotes and resolve the ambiguity.

When a user writes SELECT a.b.c.d.e.f, we will check all combinations:

• if database a.b.c.d exists with table e and column f;
• if database a.b.c exists with table d.e and column f;
• if database a.b.c exists with table d and column e.f;
• if database a.b exists with table c.d.e and column f;
• if database a.b exists with table c.d and column e.f;
• if database a.b exists with table c and column d.e.f;
• if database a exists with table b.c.d.e and column f;
• if database a exists with table b.c.d and column e.f;
• if database a exists with table b.c and column d.e.f;
• if database a exists with table b and column c.d.e.f;
• if the current database has table a.b.c.d.e and column f;
• if the current database has table a.b.c.d and column e.f;
• if the current database has table a.b.c and column d.e.f;
• if the current database has table a.b and column c.d.e.f;
• if the current database has table a and column b.c.d.e.f;
• if one of the tables in a query's FROM section has a column a.b.c.d.e.f, which name is not ambiguous;

When a user does USE x we will allow it even if x is a prefix component of any database name - for example, if database x does not exist, but there are x.y and/or x.z, it is allowed to USE x.

When a user does USE x.y we will allow it even if y is a prefix of any table name.
For example, if database x.y does not exist, but there are tables x."y.a" and/or x."y.b", it is allowed to USE x.y.

Additionally, we can allow a relative USE statement when, assuming you are inside database a, the statement USE b could dive into a.b if it exists.

When a user does SHOW TABLES, we list tables under a certain prefix, possibly from different databases.

GRANT and REVOKE queries for prefixes are translated to corresponding wildcard grants.

Describe the alternative

A viable alternative is: don't do that.