Proposal: database_search_path setting for resolving unqualified table names #110980

Use case

In PostgreSQL, search_path lets you reference tables without qualifying them with a schema, resolving them against an ordered list of schemas. ClickHouse currently resolves unqualified table names only against the current database (currentDatabase()), which forces users to either fully qualify every cross-database table or restructure everything into a single database.

This is painful in common setups such as:

• Layered data architectures (raw → staging → analytics) where analysts mostly query one layer but occasionally reach into another.
• Multi-tenant or per-environment databases where the same query text should run against different databases depending on the user/profile.
• Migrations from PostgreSQL, where existing SQL relies on search_path semantics.

Proposed solution

Introduce a new setting, integrating with the existing settings hierarchy (server → profile → user → session → query) instead of adding new syntax:

sql-- Session level
SET database_search_path = 'analytics, staging, default';

-- Per query
SELECT * FROM events SETTINGS database_search_path = 'analytics, raw';

-- Per user (persistent)
ALTER USER analyst SETTINGS database_search_path = 'analytics, default';

And in profiles (users.xml / users.d):

xml<profiles>
    <analyst>
        <database_search_path>analytics,staging,default</database_search_path>
    </analyst>
</profiles>

This gives us profile support, per-query overrides, readonly/CONSTRAINT restrictions, and visibility in system.settings for free.