> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# Other session settings
> ClickHouse session settings in the Other generated group.
export const SettingsInfoBlock = ({type, default_value, changeable_without_restart}) => {
return
;
};
These settings are available in [system.settings](/docs/reference/system-tables/settings) and are autogenerated from [source](https://github.com/ClickHouse/ClickHouse/blob/master/src/Core/Settings.cpp).
add\_http\_cors\_header
Write add http CORS header.
analyze\_index\_with\_space\_filling\_curves
If a table has a space-filling curve in its index, e.g. `ORDER BY mortonEncode(x, y)` or `ORDER BY hilbertEncode(x, y)`, and the query has conditions on its arguments, e.g. `x >= 10 AND x <= 20 AND y >= 20 AND y <= 30`, use the space-filling curve for index analysis.
analyzer\_inline\_views
When enabled, the analyzer substitutes ordinary (non-materialized, non-parameterized) views with their defining subqueries, enabling cross-boundary optimizations such as predicate pushdown and column pruning.
any\_join\_distinct\_right\_table\_keys
Enables legacy ClickHouse server behaviour in `ANY INNER|LEFT JOIN` operations.
Use this setting only for backward compatibility if your use cases depend on legacy `JOIN` behaviour.
When the legacy behaviour is enabled:
* Results of `t1 ANY LEFT JOIN t2` and `t2 ANY RIGHT JOIN t1` operations are not equal because ClickHouse uses the logic with many-to-one left-to-right table keys mapping.
* Results of `ANY INNER JOIN` operations contain all rows from the left table like the `SEMI LEFT JOIN` operations do.
When the legacy behaviour is disabled:
* Results of `t1 ANY LEFT JOIN t2` and `t2 ANY RIGHT JOIN t1` operations are equal because ClickHouse uses the logic which provides one-to-many keys mapping in `ANY RIGHT JOIN` operations.
* Results of `ANY INNER JOIN` operations contain one row per key from both the left and right tables.
Possible values:
* 0 — Legacy behaviour is disabled.
* 1 — Legacy behaviour is enabled.
See also:
* [JOIN strictness](/docs/reference/statements/select/join#settings)
archive\_adaptive\_buffer\_max\_size\_bytes
Limits the maximum size of the adaptive buffer used when writing to archive files (for example, tar archives
arrow\_flight\_request\_descriptor\_type
Type of descriptor to use for Arrow Flight requests. 'path' sends the dataset name as a path descriptor. 'command' sends a SQL query as a command descriptor (required for Dremio).
Possible values:
* 'path' — Use FlightDescriptor::Path (default, works with most Arrow Flight servers)
* 'command' — Use FlightDescriptor::Command with a SELECT query (required for Dremio)
When set to `true`, all threads executing S3 requests to the same backup endpoint are slowed down
after any single S3 request encounters a retryable S3 error, such as 'Slow Down'.
When set to `false`, each thread handles s3 request backoff independently of the others.
cache\_warmer\_threads
Only has an effect in ClickHouse Cloud. Number of background threads for speculatively downloading new data parts into the filesystem cache, when [cache\_populated\_by\_fetch](/docs/reference/settings/merge-tree-settings#cache_populated_by_fetch) is enabled. Zero to disable.
calculate\_text\_stack\_trace
Calculate text stack trace in case of exceptions during query execution. This is the default. It requires symbol lookups that may slow down fuzzing tests when a huge amount of wrong queries are executed. In normal cases, you should not disable this option.
Cancels HTTP read-only queries (e.g. SELECT) when a client closes the connection without waiting for the response.
Cloud default value: `1`.
checksum\_on\_read
Validate checksums on reading. It is enabled by default and should be always enabled in production. Please do not expect any benefits in disabling this setting. It may only be used for experiments and benchmarks. The setting is only applicable for tables of MergeTree family. Checksums are always validated for other table engines and when receiving data over the network.
connection\_pool\_max\_wait\_ms
The wait time in milliseconds for a connection when the connection pool is full.
Possible values:
* Positive integer.
* 0 — Infinite timeout.
connections\_with\_failover\_max\_tries
The maximum number of connection attempts with each replica for the Distributed table engine.
convert\_query\_to\_cnf
When set to `true`, a `SELECT` query will be converted to conjuctive normal form (CNF). There are scenarios where rewriting a query in CNF may execute faster (view this [Github issue](https://github.com/ClickHouse/ClickHouse/issues/11749) for an explanation).
For example, notice how the following `SELECT` query is not modified (the default behavior):
```sql theme={null}
EXPLAIN SYNTAX
SELECT *
FROM
(
SELECT number AS x
FROM numbers(20)
) AS a
WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15))
SETTINGS convert_query_to_cnf = false;
```
The result is:
```response theme={null}
┌─explain────────────────────────────────────────────────────────┐
│ SELECT x │
│ FROM │
│ ( │
│ SELECT number AS x │
│ FROM numbers(20) │
│ WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15)) │
│ ) AS a │
│ WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15)) │
│ SETTINGS convert_query_to_cnf = 0 │
└────────────────────────────────────────────────────────────────┘
```
Let's set `convert_query_to_cnf` to `true` and see what changes:
```sql theme={null}
EXPLAIN SYNTAX
SELECT *
FROM
(
SELECT number AS x
FROM numbers(20)
) AS a
WHERE ((x >= 1) AND (x <= 5)) OR ((x >= 10) AND (x <= 15))
SETTINGS convert_query_to_cnf = true;
```
Notice the `WHERE` clause is rewritten in CNF, but the result set is the identical - the Boolean logic is unchanged:
```response theme={null}
┌─explain───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ SELECT x │
│ FROM │
│ ( │
│ SELECT number AS x │
│ FROM numbers(20) │
│ WHERE ((x <= 15) OR (x <= 5)) AND ((x <= 15) OR (x >= 1)) AND ((x >= 10) OR (x <= 5)) AND ((x >= 10) OR (x >= 1)) │
│ ) AS a │
│ WHERE ((x >= 10) OR (x >= 1)) AND ((x >= 10) OR (x <= 5)) AND ((x <= 15) OR (x >= 1)) AND ((x <= 15) OR (x <= 5)) │
│ SETTINGS convert_query_to_cnf = 1 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
Possible values: true, false
count\_matches\_stop\_at\_empty\_match
Stop counting once a pattern matches zero-length in the `countMatches` function.
cross\_to\_inner\_join\_rewrite
Use inner join instead of comma/cross join if there are joining expressions in the WHERE section. Values: 0 - no rewrite, 1 - apply if possible for comma/cross, 2 - force rewrite all comma joins, cross - if possible
data\_type\_default\_nullable
Allows data types without explicit modifiers [NULL or NOT NULL](/docs/reference/statements/create/table#null-or-not-null-modifiers) in column definition will be [Nullable](/docs/reference/data-types/nullable).
Possible values:
* 1 — The data types in column definitions are set to `Nullable` by default.
* 0 — The data types in column definitions are set to not `Nullable` by default.
decimal\_check\_overflow
Check overflow of decimal arithmetic/comparison operations
Enables or disables the deduplication check for materialized views that receive data from Replicated\* tables.
Possible values:
* 0 — Disabled.
* 1 — Enabled.
When enabled, ClickHouse performs deduplication of blocks in materialized views that depend on Replicated\* tables.
This setting is useful for ensuring that materialized views do not contain duplicate data when the insertion operation is being retried due to a failure.
**See Also**
* [NULL Processing in IN Operators](/docs/concepts/features/operations/insert/deduplicating-inserts-on-retries#insert-deduplication-with-materialized-views)
defer\_partition\_pruning\_after\_final
When enabled (default), partition pruning is skipped for `FINAL` queries on tables whose
partition-key columns are not part of the sorting key. This is the correctness-safe behavior
introduced in 26.3: `FINAL` may need to deduplicate rows that share a primary key but live
in different partitions, and partition pruning would silently exclude such rows from the
deduplication input.
When disabled, partition pruning is applied even with `FINAL`, restoring the pre-26.3
behavior. This can be substantially faster for queries with `WHERE` predicates on the
partition column, but is only correct when rows with the same primary key cannot exist
in different partitions — e.g. event-log tables whose partition column is set at insert
time and never changes.
This setting only affects partitioned tables whose partition-key columns are not contained
in the sorting key; for other tables partition pruning is always applied.
Possible values:
* 0 — Apply partition pruning before `FINAL` (pre-26.3 behavior, faster but unsafe in the general case).
* 1 — Defer partition pruning to after `FINAL` (default, correctness-safe).
describe\_compact\_output
If true, include only column names and types into result of DESCRIBE query
dialect
Which dialect will be used to parse query
discard\_query\_data
If enabled, the server skips sending query result rows to the client. The query is still executed and logged fully on the server, and the client still receives the remaining packets.
Used for shadow traffic, benchmarks, and fuzzing.
Has no effect for secondary queries.
Affects only the native TCP protocol.
distinct\_overflow\_mode
Sets what happens when the amount of data exceeds one of the limits.
Possible values:
* `throw`: throw an exception (default).
* `break`: stop executing the query and return the partial result, as if the
source data ran out.
do\_not\_merge\_across\_partitions\_select\_final
Improve FINAL queries by avoiding merges across different partitions.
When enabled, during SELECT FINAL queries, parts from different partitions will not be merged together. Instead, merging will only occur within each partition separately. This can significantly improve query performance when working with partitioned tables.
dynamic\_throw\_on\_type\_mismatch
When applying a function to a [Dynamic](/docs/reference/data-types/dynamic) column using the default implementation,
controls what happens for rows whose actual type is incompatible with the function:
* `true` (default) — throw an exception.
* `false` — return `NULL` for those rows instead.
enforce\_strict\_identifier\_format
If enabled, only allow identifiers containing alphanumeric characters and underscores.
engine\_url\_skip\_empty\_files
Enables or disables skipping empty files in [URL](/docs/reference/engines/table-engines/special/url) engine tables.
Possible values:
* 0 — `SELECT` throws an exception if empty file is not compatible with requested format.
* 1 — `SELECT` returns empty result for empty file.
exact\_rows\_before\_limit
When enabled, ClickHouse will provide exact value for rows\_before\_limit\_at\_least statistic, but with the cost that the data before limit will have to be read completely
except\_default\_mode
Set default mode in EXCEPT query. Possible values: empty string, 'ALL', 'DISTINCT'. If empty, query without mode will throw exception.
exclude\_materialize\_skip\_indexes\_on\_insert
Excludes specified skip indexes from being built and stored during INSERTs. The excluded skip indexes will still be built and stored [during merges](/docs/reference/settings/merge-tree-settings#materialize_skip_indexes_on_merge) or by an explicit
[MATERIALIZE INDEX](/docs/reference/statements/alter/skipping-index#materialize-index) query.
Has no effect if [materialize\_skip\_indexes\_on\_insert](/docs/reference/settings/session-settings/materialize#materialize_skip_indexes_on_insert) is false.
Example:
```sql theme={null}
CREATE TABLE tab
(
a UInt64,
b UInt64,
INDEX idx_a a TYPE minmax,
INDEX idx_b b TYPE set(3)
)
ENGINE = MergeTree ORDER BY tuple();
SET exclude_materialize_skip_indexes_on_insert='idx_a'; -- idx_a will be not be updated upon insert
--SET exclude_materialize_skip_indexes_on_insert='idx_a, idx_b'; -- neither index would be updated on insert
INSERT INTO tab SELECT number, number / 50 FROM numbers(100); -- only idx_b is updated
-- since it is a session setting it can be set on a per-query level
INSERT INTO tab SELECT number, number / 50 FROM numbers(100, 100) SETTINGS exclude_materialize_skip_indexes_on_insert='idx_b';
ALTER TABLE tab MATERIALIZE INDEX idx_a; -- this query can be used to explicitly materialize the index
SET exclude_materialize_skip_indexes_on_insert = DEFAULT; -- reset setting to default
```
execute\_exists\_as\_scalar\_subquery
Execute non-correlated EXISTS subqueries as scalar subqueries. As for scalar subqueries, the cache is used, and the constant folding applies to the result.
Cloud default value: `0`.
explain\_query\_plan\_default
Default format used by `EXPLAIN PLAN`.
Possible values:
* `pretty` (default since 26.7) — `actions`, `compact`, and `pretty` default to `true`, producing a compact, pretty, action-annotated plan.
* `legacy` — pre-26.7 output.
Specifying the `actions`, `compact`, or `pretty` options explicitly in the `EXPLAIN` statement (for example, `EXPLAIN actions = 0, compact = 0, pretty = 0 SELECT ...`) always overrides this setting.
`EXPLAIN PLAN` with `json = 1` or `distributed = 1` keeps the legacy (pre-26.7) defaults regardless of this setting, unless `actions`, `compact`, or `pretty` are set explicitly. The pretty output cannot represent JSON results or per-shard distributed plans, so those modes are only rendered correctly in legacy form.
extract\_key\_value\_pairs\_max\_pairs\_per\_row
**Aliases**: `extract_kvp_max_pairs_per_row`
Max number of pairs that can be produced by the `extractKeyValuePairs` function. Used as a safeguard against consuming too much memory.
extremes
Whether to count extreme values (the minimums and maximums in columns of a query result). Accepts 0 or 1. By default, 0 (disabled).
For more information, see the section "Extreme values".
Forces a query to an out-of-date replica if updated data is not available. See [Replication](/docs/reference/engines/table-engines/mergetree-family/replication).
ClickHouse selects the most relevant from the outdated replicas of the table.
Used when performing `SELECT` from a distributed table that points to replicated tables.
By default, 1 (enabled).
file\_like\_engine\_default\_partition\_strategy
Default partition strategy for file like engines.
filesystem\_prefetches\_limit
Maximum number of prefetches. Zero means unlimited. A setting `filesystem_prefetches_max_memory_usage` is more recommended if you want to limit the number of prefetches
final
Automatically applies [FINAL](/docs/reference/statements/select/from#final-modifier) modifier to all tables in a query, to tables where [FINAL](/docs/reference/statements/select/from#final-modifier) is applicable, including joined tables and tables in sub-queries, and
distributed tables.
Possible values:
* 0 - disabled
* 1 - enabled
Example:
```sql theme={null}
CREATE TABLE test
(
key Int64,
some String
)
ENGINE = ReplacingMergeTree
ORDER BY key;
INSERT INTO test FORMAT Values (1, 'first');
INSERT INTO test FORMAT Values (1, 'second');
SELECT * FROM test;
┌─key─┬─some───┐
│ 1 │ second │
└─────┴────────┘
┌─key─┬─some──┐
│ 1 │ first │
└─────┴───────┘
SELECT * FROM test SETTINGS final = 1;
┌─key─┬─some───┐
│ 1 │ second │
└─────┴────────┘
SET final = 1;
SELECT * FROM test;
┌─key─┬─some───┐
│ 1 │ second │
└─────┴────────┘
```
finalize\_projection\_parts\_synchronously
When enabled, projection parts are finalized synchronously during INSERT, reducing peak memory usage at the cost of reduced S3 upload parallelism. By default, each projection's output stream is kept alive until the entire part (including all projections) is finalized, which allows overlapping S3 uploads but increases peak memory proportional to the number of projections. This setting only affects the INSERT path; merge and mutation already finalize projections synchronously.
flatten\_nested
Sets the data format of a [nested](/docs/reference/data-types/nested-data-structures/index) columns.
Possible values:
* 1 — Nested column is flattened to separate arrays.
* 0 — Nested column stays a single array of tuples.
**Usage**
If the setting is set to `0`, it is possible to use an arbitrary level of nesting.
**Examples**
Query:
```sql theme={null}
SET flatten_nested = 1;
CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
SHOW CREATE TABLE t_nest;
```
Result:
```text theme={null}
┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
`n.a` Array(UInt32),
`n.b` Array(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
Query:
```sql theme={null}
SET flatten_nested = 0;
CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
SHOW CREATE TABLE t_nest;
```
Result:
```text theme={null}
┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
`n` Nested(a UInt32, b UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
fsync\_metadata
Enables or disables [fsync](http://pubs.opengroup.org/onlinepubs/9699919799/functions/fsync.html) when writing `.sql` files. Enabled by default.
It makes sense to disable it if the server has millions of tiny tables that are constantly being created and destroyed.
functions\_h3\_default\_if\_invalid
If false, h3 functions, e.g. h3CellAreaM2, throw an exception if input is invalid. If true, they return 0 or default value.
If all four arguments to `geoDistance`, `greatCircleDistance`, `greatCircleAngle` functions are Float64, return Float64 and use double precision for internal calculations. In previous ClickHouse versions, the functions always returned Float32.
geotoh3\_argument\_order
Function 'geoToH3' accepts (lon, lat) if set to 'lon\_lat' and (lat, lon) if set to 'lat\_lon'.
glob\_expansion\_max\_elements
Maximum number of allowed addresses (For external storages, table functions, etc).
h3togeo\_lon\_lat\_result\_order
Function 'h3ToGeo' returns (lon, lat) if true, otherwise (lat, lon).
handshake\_timeout\_ms
Timeout in milliseconds for receiving Hello packet from replicas during handshake.
hedged\_connection\_timeout\_ms
Connection timeout for establishing connection with replica for Hedged requests
highlight\_max\_matches\_per\_row
Sets the maximum number of highlight matches per row in the [highlight](/docs/reference/functions/regular-functions/string-search-functions#highlight) function. Use it to protect against excessive memory usage when highlighting highly repetitive patterns in large texts.
Possible values:
* Positive integer.
hnsw\_candidate\_list\_size\_for\_search
The size of the dynamic candidate list when searching the vector similarity index, also known as 'ef\_search'.
hsts\_max\_age
Expired time for HSTS. 0 means disable HSTS.
idle\_connection\_timeout
Timeout to close idle TCP connections after specified number of seconds.
Possible values:
* Positive integer (0 - close immediately, after 0 seconds).
If enabled, injects 'ORDER BY rand()' into SELECT queries without ORDER BY clause.
Applied only for subquery depth = 0. Subqueries and INSERT INTO ... SELECT are not affected.
If the top-level construct is UNION, 'ORDER BY rand()' is injected into all children independently.
Only useful for testing and development (missing ORDER BY is a source of non-deterministic query results).
interactive\_delay
The interval in microseconds for checking whether request execution has been canceled and sending the progress.
intersect\_default\_mode
Set default mode in INTERSECT query. Possible values: empty string, 'ALL', 'DISTINCT'. If empty, query without mode will throw exception.
least\_greatest\_legacy\_null\_behavior
If enabled, functions 'least' and 'greatest' return NULL if one of their arguments is NULL.
legacy\_column\_name\_of\_tuple\_literal
List all names of element of large tuple literals in their column names instead of hash. This settings exists only for compatibility reasons. It makes sense to set to 'true', while doing rolling update of cluster from version lower than 21.7 to higher.
limit
Sets the maximum number of rows to get from the query result. It adjusts the value set by the [LIMIT](/docs/reference/statements/select/limit) clause, so that the limit, specified in the query, cannot exceed the limit, set by this setting.
Possible values:
* 0 — The number of rows is not limited.
* Positive integer.
load\_marks\_asynchronously
Load MergeTree marks asynchronously
Cloud default value: `1`.
lock\_acquire\_timeout
Defines how many seconds a locking request waits before failing.
Locking timeout is used to protect from deadlocks while executing read/write operations with tables. When the timeout expires and the locking request fails, the ClickHouse server throws an exception "Locking attempt timed out! Possible deadlock avoided. Client should retry." with error code `DEADLOCK_AVOIDED`.
Possible values:
* Positive integer (in seconds).
* 0 — No locking timeout.
low\_priority\_query\_wait\_time\_ms
When the query prioritization mechanism is employed (see setting `priority`), low-priority queries wait for higher-priority queries to finish. This setting specifies the duration of waiting.
When creating a `Merge` table without an explicit schema or when using the `merge` table function, infer schema as a union of not more than the specified number of matching tables.
If there is a larger number of tables, the schema will be inferred from the first specified number of tables.
mongodb\_throw\_on\_unsupported\_query
If enabled, MongoDB tables will return an error when a MongoDB query cannot be built. Otherwise, ClickHouse reads the full table and processes it locally. This option does not apply when 'allow\_experimental\_analyzer=0'.
multiple\_joins\_try\_to\_keep\_original\_names
Do not add aliases to top level expression list on multiple joins rewrite
normalize\_function\_names
Normalize function names to their canonical names
offset
Sets the number of rows to skip before starting to return rows from the query. It adjusts the offset set by the [OFFSET](/docs/reference/statements/select/offset) clause, so that these two values are summarized.
Possible values:
* 0 — No rows are skipped .
* Positive integer.
**Example**
Input table:
```sql theme={null}
CREATE TABLE test (i UInt64) ENGINE = MergeTree() ORDER BY i;
INSERT INTO test SELECT number FROM numbers(500);
```
Query:
```sql theme={null}
SET limit = 5;
SET offset = 7;
SELECT * FROM test LIMIT 10 OFFSET 100;
```
Result:
```text theme={null}
┌───i─┐
│ 107 │
│ 108 │
│ 109 │
└─────┘
```
paimon\_target\_snapshot\_id
Query-level targeted snapshot read for Paimon incremental mode. When >0, the reader will only fetch the delta
for the specified snapshot\_id without advancing the committed watermark.
Default: -1 (disabled)
parallelize\_output\_from\_storages
Parallelize output for reading step from storage. It allows parallelization of query processing right after reading from storage if possible
partial\_result\_on\_first\_cancel
Allows query to return a partial result after cancel.
per\_part\_index\_stats
Logs index statistics per part
poll\_interval
Block at the query wait loop on the server for the specified number of seconds.
polyglot\_dialect
Source SQL dialect for the polyglot transpiler (e.g. 'sqlite', 'mysql', 'postgresql', 'snowflake', 'duckdb').
postgresql\_fault\_injection\_probability
Approximate probability of failing internal (for replication) PostgreSQL queries. Valid value is in interval \[0.0f, 1.0f]
predicate\_statistics\_sample\_rate
Collect predicate selectivity statistics into `system.predicate_statistics_log`. When set to N > 0, approximately 1/N of queries are sampled (by the query ID). 0 means disabled.
prefetch\_buffer\_size
The maximum size of the prefetch buffer to read from the filesystem. Values above 256 MiB are clamped to 256 MiB, as a read buffer never needs to be larger.
print\_pretty\_type\_names
Allows to print deep-nested type names in a pretty way with indents in `DESCRIBE` query and in `toTypeName()` function.
Example:
```sql theme={null}
CREATE TABLE test (a Tuple(b String, c Tuple(d Nullable(UInt64), e Array(UInt32), f Array(Tuple(g String, h Map(String, Array(Tuple(i String, j UInt64))))), k Date), l Nullable(String))) ENGINE=Memory;
DESCRIBE TABLE test FORMAT TSVRaw SETTINGS print_pretty_type_names=1;
```
```
a Tuple(
b String,
c Tuple(
d Nullable(UInt64),
e Array(UInt32),
f Array(Tuple(
g String,
h Map(
String,
Array(Tuple(
i String,
j UInt64
))
)
)),
k Date
),
l Nullable(String)
)
```
priority
Priority of the query. 1 - the highest, higher value - lower priority; 0 - do not use priorities.
push\_external\_roles\_in\_interserver\_queries
Enable pushing user roles from originator to other nodes while performing a query.
query\_metric\_log\_interval
The interval in milliseconds at which the [query\_metric\_log](/docs/reference/system-tables/query_metric_log) for individual queries is collected.
If set to any negative value, it will take the value `collect_interval_milliseconds` from the [query\_metric\_log setting](/docs/reference/settings/server-settings/settings#query_metric_log) or default to 1000 if not present.
To disable the collection of a single query, set `query_metric_log_interval` to 0.
Default value: -1
queue\_max\_wait\_ms
The wait time in the request queue, if the number of concurrent requests exceeds the maximum.
rabbitmq\_max\_wait\_ms
The wait time for reading from RabbitMQ before retry.
readonly
0 - no read-only restrictions. 1 - only read requests, as well as changing explicitly allowed settings. 2 - only read requests, as well as changing settings, except for the 'readonly' setting.
recursive\_cte\_max\_steps\_in\_type\_inference
Maximum number of iterations for inferring column types in recursive CTEs. Column types are determined by iteratively applying `getLeastSupertype` across the non-recursive and recursive sides of the UNION ALL until convergence. Set to 0 to disable type widening and use the types from the non-recursive part only.
regexp\_max\_matches\_per\_row
Sets the maximum number of matches for a single regular expression per row. Use it to protect against memory overload when using greedy regular expression in the [extractAllGroupsHorizontal](/docs/reference/functions/regular-functions/string-search-functions#extractAllGroupsHorizontal) function.
Possible values:
* Positive integer.
reject\_expensive\_hyperscan\_regexps
Reject patterns which will likely be expensive to evaluate with hyperscan (due to NFA state explosion)
remerge\_sort\_lowered\_memory\_bytes\_ratio
If memory usage after remerge does not reduced by this ratio, remerge will be disabled.
remote\_read\_min\_bytes\_for\_seek
Min bytes required for remote read (url, s3) to do seek, instead of read with ignore.
rename\_files\_after\_processing
* **Type:** String
* **Default value:** Empty string
This setting allows to specify renaming pattern for files processed by `file` table function. When option is set, all files read by `file` table function will be renamed according to specified pattern with placeholders, only if files processing was successful.
### Placeholders
* `%a` — Full original filename (e.g., "sample.csv").
* `%f` — Original filename without extension (e.g., "sample").
* `%e` — Original file extension with dot (e.g., ".csv").
* `%t` — Timestamp (in microseconds).
* `%%` — Percentage sign ("%").
### Example
* Option: `--rename_files_after_processing="processed_%f_%t%e"`
* Query: `SELECT * FROM file('sample.csv')`
If reading `sample.csv` is successful, file will be renamed to `processed_sample_1683473210851438.csv`
Specifies how long (in seconds) to wait for inactive replicas to execute [`ALTER`](/docs/reference/statements/alter/index), [`OPTIMIZE`](/docs/reference/statements/optimize) or [`TRUNCATE`](/docs/reference/statements/truncate) queries.
Possible values:
* `0` — Do not wait.
* Negative integer — Wait for unlimited time.
* Positive integer — The number of seconds to wait.
reserve\_memory
Used in workload scheduling. The minimum amount of RAM reserved to be used for running a query on a single server. Reservation is made through the WORKLOAD hierarchy using the value of a `workload` query setting.
If not enough memory is available to the workload, a query is prevented from starting and waits in pending state until the reservation can be fulfilled.
A value of `0` means no reservation.
This setting takes effect only if MEMORY RESERVATION resource is created.
Replace table engine from Replicated*MergeTree -> Shared*MergeTree during RESTORE.
Cloud default value: `1`.
result\_overflow\_mode
Sets what to do if the volume of the result exceeds one of the limits.
Possible values:
* `throw`: throw an exception (default).
* `break`: stop executing the query and return the partial result, as if the
source data ran out.
Using 'break' is similar to using LIMIT. `Break` interrupts execution only at the
block level. This means that amount of returned rows is greater than
[`max_result_rows`](/docs/reference/settings/session-settings/max-result#max_result_rows), multiple of [`max_block_size`](/docs/reference/settings/session-settings/max#max_block_size)
and depends on [`max_threads`](/docs/reference/settings/session-settings/max-threads#max_threads).
**Example**
```sql title="Query" theme={null}
SET max_threads = 3, max_block_size = 3333;
SET max_result_rows = 3334, result_overflow_mode = 'break';
SELECT *
FROM numbers_mt(100000)
FORMAT Null;
```
```text title="Result" theme={null}
6666 rows in set. ...
```
rows\_before\_aggregation
When enabled, ClickHouse will provide exact value for rows\_before\_aggregation statistic, represents the number of rows read before aggregatio
secondary\_indices\_enable\_bulk\_filtering
Enable the bulk filtering algorithm for indices. It is expected to be always better, but we have this setting for compatibility and control.
select\_sequential\_consistency
This setting differ in behavior between SharedMergeTree and ReplicatedMergeTree, see [SharedMergeTree consistency](/docs/products/cloud/features/infrastructure/shared-merge-tree#consistency) for more information about the behavior of `select_sequential_consistency` in SharedMergeTree.
Enables or disables sequential consistency for `SELECT` queries. Requires `insert_quorum_parallel` to be disabled (enabled by default).
Possible values:
* 0 — Disabled.
* 1 — Enabled.
Usage
When sequential consistency is enabled, ClickHouse allows the client to execute the `SELECT` query only for those replicas that contain data from all previous `INSERT` queries executed with `insert_quorum`. If the client refers to a partial replica, ClickHouse will generate an exception. The SELECT query will not include data that has not yet been written to the quorum of replicas.
When `insert_quorum_parallel` is enabled (the default), then `select_sequential_consistency` does not work. This is because parallel `INSERT` queries can be written to different sets of quorum replicas so there is no guarantee a single replica will have received all writes.
See also:
* [insert\_quorum](/docs/reference/settings/session-settings/insert-quorum#insert_quorum)
* [insert\_quorum\_timeout](/docs/reference/settings/session-settings/insert-quorum#insert_quorum_timeout)
* [insert\_quorum\_parallel](/docs/reference/settings/session-settings/insert-quorum#insert_quorum_parallel)
session\_timezone
Sets the implicit time zone of the current session or query.
The implicit time zone is the time zone applied to values of type DateTime/DateTime64 which have no explicitly specified time zone.
The setting takes precedence over the globally configured (server-level) implicit time zone.
A value of '' (empty string) means that the implicit time zone of the current session or query is equal to the [server time zone](/docs/reference/settings/server-settings/settings#timezone).
You can use functions `timeZone()` and `serverTimeZone()` to get the session time zone and server time zone.
Possible values:
* Any time zone name from `system.time_zones`, e.g. `Europe/Berlin`, `UTC` or `Zulu`
Examples:
```sql theme={null}
SELECT timeZone(), serverTimeZone() FORMAT CSV
"Europe/Berlin","Europe/Berlin"
```
```sql theme={null}
SELECT timeZone(), serverTimeZone() SETTINGS session_timezone = 'Asia/Novosibirsk' FORMAT CSV
"Asia/Novosibirsk","Europe/Berlin"
```
Assign session time zone 'America/Denver' to the inner DateTime without explicitly specified time zone:
```sql theme={null}
SELECT toDateTime64(toDateTime64('1999-12-12 23:23:23.123', 3), 3, 'Europe/Zurich') SETTINGS session_timezone = 'America/Denver' FORMAT TSV
1999-12-13 07:23:23.123
```
Not all functions that parse DateTime/DateTime64 respect `session_timezone`. This can lead to subtle errors.
See the following example and explanation.
```sql theme={null}
CREATE TABLE test_tz (`d` DateTime('UTC')) ENGINE = Memory AS SELECT toDateTime('2000-01-01 00:00:00', 'UTC');
SELECT *, timeZone() FROM test_tz WHERE d = toDateTime('2000-01-01 00:00:00') SETTINGS session_timezone = 'Asia/Novosibirsk'
0 rows in set.
SELECT *, timeZone() FROM test_tz WHERE d = '2000-01-01 00:00:00' SETTINGS session_timezone = 'Asia/Novosibirsk'
┌───────────────────d─┬─timeZone()───────┐
│ 2000-01-01 00:00:00 │ Asia/Novosibirsk │
└─────────────────────┴──────────────────┘
```
This happens due to different parsing pipelines:
* `toDateTime()` without explicitly given time zone used in the first `SELECT` query honors setting `session_timezone` and the global time zone.
* In the second query, a DateTime is parsed from a String, and inherits the type and time zone of the existing column`d`. Thus, setting `session_timezone` and the global time zone are not honored.
**See also**
* [timezone](/docs/reference/settings/server-settings/settings#timezone)
set\_overflow\_mode
Sets what happens when the amount of data exceeds one of the limits.
Possible values:
* `throw`: throw an exception (default).
* `break`: stop executing the query and return the partial result, as if the
source data ran out.
single\_join\_prefer\_left\_table
For single JOIN in case of identifier ambiguity prefer left table
skip\_redundant\_aliases\_in\_udf
Redundant aliases are not used (substituted) in user-defined functions in order to simplify it's usage.
Possible values:
* 1 — The aliases are skipped (substituted) in UDFs.
* 0 — The aliases are not skipped (substituted) in UDFs.
**Example**
The difference between enabled and disabled:
Query:
```sql theme={null}
SET skip_redundant_aliases_in_udf = 0;
CREATE FUNCTION IF NOT EXISTS test_03274 AS ( x ) -> ((x + 1 as y, y + 2));
EXPLAIN SYNTAX SELECT test_03274(4 + 2);
```
Result:
```text theme={null}
SELECT ((4 + 2) + 1 AS y, y + 2)
```
Query:
```sql theme={null}
SET skip_redundant_aliases_in_udf = 1;
CREATE FUNCTION IF NOT EXISTS test_03274 AS ( x ) -> ((x + 1 as y, y + 2));
EXPLAIN SYNTAX SELECT test_03274(4 + 2);
```
Result:
```text theme={null}
SELECT ((4 + 2) + 1, ((4 + 2) + 1) + 2)
```
sleep\_after\_receiving\_query\_ms
Time to sleep after receiving query in TCPHandler
snappy\_mode
Controls the wire format used for snappy compression for generic file I/O paths such as `file` and `url`. HTTP `Content-Encoding: snappy` always uses the framing format and ignores this setting.
Note that the raw snappy block format produced by a single `snappy::Compress` call (for example, the Prometheus remote protocol payloads handled by `SnappyBasicReadBuffer`) is a separate, protocol-specific wire format and is not controlled by this setting.
Possible values:
* `basic` — Hadoop snappy block format. Compatible with files read and written by Hadoop. Supports both reading and writing.
* `framed` — Snappy framing format, the standard streaming format defined by Google. Supports both reading and writing.
sort\_overflow\_mode
Sets what happens if the number of rows received before sorting exceeds one of the limits.
Possible values:
* `throw`: throw an exception.
* `break`: stop executing the query and return the partial result.
Controls whether function [splitBy\*()](/docs/reference/functions/regular-functions/splitting-merging-functions) with argument `max_substrings` > 0 will include the remaining string in the last element of the result array.
Possible values:
* `0` - The remaining string will not be included in the last element of the result array.
* `1` - The remaining string will be included in the last element of the result array. This is the behavior of Spark's [`split()`](https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html) function and Python's ['string.split()'](https://docs.python.org/3/library/stdtypes.html#str.split) method.
On server startup, prevent scheduling of refreshable materialized views, as if with SYSTEM STOP VIEWS. You can manually start them with `SYSTEM START VIEWS` or `SYSTEM START VIEW ` afterwards. Also applies to newly created views. Has no effect on non-refreshable materialized views.
tcp\_keep\_alive\_timeout
The time in seconds the connection needs to remain idle before TCP starts sending keepalive probes
Wait time to lock cache for space reservation for temporary data in filesystem cache
throw\_if\_no\_data\_to\_insert
Allows or forbids empty INSERTs, enabled by default (throws an error on an empty insert). Only applies to INSERTs using [`clickhouse-client`](/docs/concepts/features/interfaces/cli) or using the [gRPC interface](/docs/concepts/features/interfaces/grpc).
timeout\_before\_checking\_execution\_speed
Checks that execution speed is not too slow (no less than `min_execution_speed`),
after the specified time in seconds has expired.
transfer\_overflow\_mode
Sets what happens when the amount of data exceeds one of the limits.
Possible values:
* `throw`: throw an exception (default).
* `break`: stop executing the query and return the partial result, as if the
source data ran out.
transform\_null\_in
Enables equality of [NULL](/docs/reference/syntax#null) values for [IN](/docs/reference/statements/in) operator.
By default, `NULL` values can't be compared because `NULL` means undefined value. Thus, comparison `expr = NULL` must always return `false`. With this setting `NULL = NULL` returns `true` for `IN` operator.
Possible values:
* 0 — Comparison of `NULL` values in `IN` operator returns `false`.
* 1 — Comparison of `NULL` values in `IN` operator returns `true`.
**Example**
Consider the `null_in` table:
```text theme={null}
┌──idx─┬─────i─┐
│ 1 │ 1 │
│ 2 │ NULL │
│ 3 │ 3 │
└──────┴───────┘
```
Query:
```sql theme={null}
SELECT idx, i FROM null_in WHERE i IN (1, NULL) SETTINGS transform_null_in = 0;
```
Result:
```text theme={null}
┌──idx─┬────i─┐
│ 1 │ 1 │
└──────┴──────┘
```
Query:
```sql theme={null}
SELECT idx, i FROM null_in WHERE i IN (1, NULL) SETTINGS transform_null_in = 1;
```
Result:
```text theme={null}
┌──idx─┬─────i─┐
│ 1 │ 1 │
│ 2 │ NULL │
└──────┴───────┘
```
**See Also**
* [NULL Processing in IN Operators](/docs/reference/statements/in#null-processing)
traverse\_shadow\_remote\_data\_paths
Traverse frozen data (shadow directory) in addition to actual table data when query system.remote\_data\_paths
union\_default\_mode
Sets a mode for combining `SELECT` query results. The setting is only used when shared with [UNION](/docs/reference/statements/select/union) without explicitly specifying the `UNION ALL` or `UNION DISTINCT`.
Possible values:
* `'DISTINCT'` — ClickHouse outputs rows as a result of combining queries removing duplicate rows.
* `'ALL'` — ClickHouse outputs all rows as a result of combining queries including duplicate rows.
* `''` — ClickHouse generates an exception when used with `UNION`.
See examples in [UNION](/docs/reference/statements/select/union).
unique\_key\_max\_encoded\_size
Maximum size (in bytes) of the order-preserving binary encoding of a single `UNIQUE KEY` row.
unknown\_packet\_in\_send\_data
Send unknown packet instead of data Nth data packet
variant\_throw\_on\_type\_mismatch
When applying a function to a [Variant](/docs/reference/data-types/variant) column using the default implementation,
controls what happens for rows whose actual type is incompatible with the function:
* `true` (default) — throw an exception.
* `false` — return `NULL` for those rows instead.