prefer_* session settings

These settings are available in system.settings and are autogenerated from source.

prefer_column_name_to_alias

Enables or disables using the original column names instead of aliases in query expressions and clauses. It especially matters when alias is the same as the column name, see Expression Aliases. Enable this setting to make aliases syntax rules in ClickHouse more compatible with most other database engines. Possible values:

0 — The column name is substituted with the alias.
1 — The column name is not substituted with the alias.

Example The difference between enabled and disabled: Query:

SET prefer_column_name_to_alias = 0;
SELECT avg(number) AS number, max(number) FROM numbers(10);

Result:

Received exception from server (version 21.5.1):
Code: 184. DB::Exception: Received from localhost:9000. DB::Exception: Aggregate function avg(number) is found inside another aggregate function in query: While processing avg(number) AS number.

Query:

SET prefer_column_name_to_alias = 1;
SELECT avg(number) AS number, max(number) FROM numbers(10);

Result:

┌─number─┬─max(number)─┐
│    4.5 │           9 │
└────────┴─────────────┘

prefer_external_sort_block_bytes

Prefer maximum block bytes for external sort, reduce the memory usage during merging.

prefer_global_in_and_join

Enables the replacement of IN/JOIN operators with GLOBAL IN/GLOBAL JOIN. Possible values:

0 — Disabled. IN/JOIN operators are not replaced with GLOBAL IN/GLOBAL JOIN.
1 — Enabled. IN/JOIN operators are replaced with GLOBAL IN/GLOBAL JOIN.

Usage Although SET distributed_product_mode=global can change the queries behavior for the distributed tables, it’s not suitable for local tables or tables from external resources. Here is when the prefer_global_in_and_join setting comes into play. For example, we have query serving nodes that contain local tables, which are not suitable for distribution. We need to scatter their data on the fly during distributed processing with the GLOBAL keyword — GLOBAL IN/GLOBAL JOIN. Another use case of prefer_global_in_and_join is accessing tables created by external engines. This setting helps to reduce the number of calls to external sources while joining such tables: only one call per query. See also:

Distributed subqueries for more information on how to use GLOBAL IN/GLOBAL JOIN

prefer_localhost_replica

Enables/disables preferable using the localhost replica when processing distributed queries. Possible values:

1 — ClickHouse always sends a query to the localhost replica if it exists.
0 — ClickHouse uses the balancing strategy specified by the load_balancing setting.

Disable this setting if you use max_parallel_replicas without parallel_replicas_custom_key. If parallel_replicas_custom_key is set, disable this setting only if it’s used on a cluster with multiple shards containing multiple replicas. If it’s used on a cluster with a single shard and multiple replicas, disabling this setting will have negative effects.

prefer_warmed_unmerged_parts_seconds

Only has an effect in ClickHouse Cloud. If a merged part is less than this many seconds old and is not pre-warmed (see cache_populated_by_fetch), but all its source parts are available and pre-warmed, SELECT queries will read from those parts instead. Only for Replicated-/SharedMergeTree. Note that this only checks whether CacheWarmer processed the part; if the part was fetched into cache by something else, it’ll still be considered cold until CacheWarmer gets to it; if it was warmed, then evicted from cache, it’ll still be considered warm.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

prefer_column_name_to_alias

prefer_external_sort_block_bytes

prefer_global_in_and_join

prefer_localhost_replica

prefer_warmed_unmerged_parts_seconds

​prefer_column_name_to_alias

​prefer_external_sort_block_bytes

​prefer_global_in_and_join

​prefer_localhost_replica

​prefer_warmed_unmerged_parts_seconds

prefer_column_name_to_alias

prefer_external_sort_block_bytes

prefer_global_in_and_join

prefer_localhost_replica

prefer_warmed_unmerged_parts_seconds