All posts
ClickHouse: Fix Query Size and AST Limit Errors

ClickHouse: Fix Query Size and AST Limit Errors

November 18, 202611 min readSanjeev Kumar G
Share:

A generated query can fail before ClickHouse starts to read data. This can happen when a query contains a large IN list, many OR conditions, or a large number of nested expressions.

You may see an error related to the maximum query size, the Abstract Syntax Tree (AST), or AST depth. The query may work when you use a smaller list. It may fail when an application generates a larger query.

This problem often occurs in production when an application builds SQL from a list of IDs. For example, an application can generate an IN clause with tens of thousands of values. It can also generate a query with many filter conditions.

The error does not always mean that ClickHouse has a problem with your data or table. ClickHouse limits the size and complexity of queries to protect server resources during parsing and analysis.

This article explains how to confirm the limit, how each setting works, and how to fix the query. It also explains when you should increase a limit.

1. Why it happens

ClickHouse parses SQL before it executes the query. During parsing, it builds an Abstract Syntax Tree (AST). The AST represents the structure of the query.

A generated query can become large in two ways:

  • Query text size: The SQL contains many characters. A large list of literal values can increase the query size in bytes.
  • AST size and depth: The parser creates many AST nodes or a deeply nested tree. Many conditions, expressions, and nested queries can increase these values.

For example, an application might generate this query:

SELECT *
FROM events
WHERE user_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109, 110);

This query is small. However, a production application can generate an IN list with thousands or tens of thousands of IDs. Each value adds text and AST nodes.

A query with many conditions can also increase the AST depth. This can happen when the application generates nested expressions or repeatedly adds parentheses.

ClickHouse uses different settings to control these limits:

  • max_query_size controls the query text size that the parser reads into memory.
  • max_ast_elements limits the number of AST nodes.
  • max_ast_depth limits the AST nesting depth.
  • max_expanded_ast_elements limits the AST size after expansion, including aliases and asterisks.

These settings control different stages or aspects of query processing. Raising one setting does not automatically resolve a failure caused by another setting.

2. How to confirm

First, check the error message from your application or ClickHouse client. Look for errors related to query size, AST elements, AST depth, or expanded AST elements.

Then, check the settings on your server.

The following query reads the current values from system.settings.

SELECT
    name,
    value,
    changed
FROM system.settings
WHERE name IN
(
    'max_query_size',
    'max_ast_elements',
    'max_ast_depth',
    'max_expanded_ast_elements'
)
ORDER BY name;

This query shows the active setting values, their defaults, and whether the values have changed.

The changed column helps you identify settings that differ from their default values. Check the results before you apply another change.

You can also check server-level settings through system.server_settings. This table is useful when you need to inspect server configuration settings.

SELECT
    name,
    value,
    changed
FROM system.server_settings
WHERE name IN
(
    'max_query_size',
    'max_ast_elements',
    'max_ast_depth',
    'max_expanded_ast_elements'
)
ORDER BY name;

Compare the active values with the error message. If the query fails during parsing, the settings in the next section are the first ones to inspect.

3. The settings

The following table lists the defaults supplied for this article's reference commit. Verify these values on your target version.

SettingDefaultWhat it controlsRestart needed
max_query_size262144 bytesMaximum query text size that the parser reads into memory.No
max_ast_elements50000Maximum number of AST nodes.No
max_ast_depth1000Maximum nesting depth of the AST.No
max_expanded_ast_elements500000Maximum number of AST nodes after expansion.No

max_query_size

This setting limits the query text size that ClickHouse reads into memory for parsing. Its default value is 262144 bytes, or 256 KiB.

A large generated SQL statement can exceed this limit. This can happen when an application inserts a large number of literal values into the query.

max_ast_elements

This setting limits the number of nodes in the query AST. Its default value is 50000.

A query with many expressions, conditions, or literal values can create a large AST. ClickHouse checks this limit after parsing.

max_ast_depth

This setting limits the nesting depth of the query AST. Its default value is 1000.

Deeply nested expressions or conditions can exceed this limit. ClickHouse checks this limit after parsing.

max_expanded_ast_elements

This setting limits the AST size after expansion. Its default value is 500000.

Expansion can increase AST size when ClickHouse expands aliases or asterisks. A query can therefore exceed this limit even when its initial AST size is within the limit.

For the setting definitions, see the official ClickHouse query complexity documentation and settings documentation.

4. How to fix it

The first choice is to reduce the query size. Do not increase limits immediately.

Fix 1: Move the list into a temporary table

Instead of generating thousands of literal values, insert them into a temporary table. Then use the table in your IN condition.

Create a temporary table:

CREATE TEMPORARY TABLE filter_ids
(
    id UInt64
)
ENGINE = Memory;

Insert the IDs into the table. You can use batch inserts to avoid building a large SQL statement.

INSERT INTO filter_ids VALUES
    (101),
    (102),
    (103),
    (104),
    (105);

Use the table in your query:

SELECT *
FROM events
WHERE user_id IN (SELECT id FROM filter_ids);

This approach keeps the filter values separate from the SQL statement. It also makes the main query easier to read and maintain.

Temporary tables are session-scoped. Make sure your application uses the same session or connection when it creates the table and executes the query.

Fix 2: Use a table with the Set engine

Use the Set engine when you need a prepared set of values that you can reuse across queries. The set is stored in memory.

Create the table:

CREATE TABLE filter_ids
(
    id UInt64
)
ENGINE = Set;

Insert the values:

INSERT INTO filter_ids VALUES
    (101),
    (102),
    (103),
    (104),
    (105);

Use the table directly in the IN condition:

SELECT *
FROM events
WHERE user_id IN filter_ids;

The Set engine stores a prepared set in memory. ClickHouse does not need to rebuild the set for each query.

This can be useful when several queries use the same filter values. However, the set consumes memory, so size it carefully.

See the official IN operator documentation for details about table-based IN expressions.

Fix 3: Send external data with the query

External data lets your application send filter data together with a query. ClickHouse makes this data available through a temporary table during query processing.

For example, you can send a file containing IDs through the command-line client.

Create a file named ids.tsv with one ID per line:

101
102
103
104
105

Send the file with the query:

clickhouse-client \
    --query="SELECT * FROM events WHERE user_id IN _data" \
    --external \
    --file=ids.tsv \
    --name=_data \
    --types=UInt64

The client sends the file as external data. ClickHouse exposes the data through _data for the query.

This approach is useful when you need to filter by a large list for one query. It avoids embedding the list in the SQL text.

See the official External data for query processing documentation.

Fix 4: Increase the limits when the query must stay as it is

Sometimes you cannot change the query structure. For example, an application or reporting tool may generate SQL that you cannot modify.

In this case, increase the relevant limits. Start with the setting that matches the error. Do not increase all four settings without a reason.

Option A: Change a setting for one query

Use the SETTINGS clause to change the limit for one query.

For example, if the query exceeds max_query_size, set it to 1 MiB.

SELECT *
FROM events
WHERE user_id IN (101, 102, 103, 104, 105)
SETTINGS max_query_size = 1048576;

The value 1048576 is 1 MiB. Use it only if the query needs more than the default 256 KiB.

If the error relates to AST elements, you can increase that limit instead.

SELECT *
FROM events
WHERE user_id IN (101, 102, 103, 104, 105)
SETTINGS max_ast_elements = 100000;

The value 100000 is an example of a higher limit. It is not a recommended universal value.

Option B: Change the setting for the session

You can use SET to change a value for the current session.

SET max_query_size = 1048576;

For an AST element limit:

SET max_ast_elements = 100000;

The change applies to queries executed in that session. It does not require a server restart.

Option C: Change a settings profile in users.xml

If you need the change for a user or profile, configure it in the settings profile. The following example increases the query size limit.

<profiles>
    <default>
        <max_query_size>1048576</max_query_size>
    </default>
</profiles>

You can also add the AST limit to the profile:

<profiles>
    <default>
        <max_ast_elements>100000</max_ast_elements>
    </default>
</profiles>

These are example profile settings. Use the appropriate profile for your application rather than changing the default profile for all users. Configuration reload behavior can depend on the deployment and configuration setup.

For a settings profile, check your configuration management process and server configuration reload behavior. These query-level settings do not require a server restart when you change them through SQL.

5. How to verify the fix

After you apply the change, run the original query again. Confirm that it completes without the query size or AST error.

If you moved the list into a temporary table, confirm that the table contains the expected values.

SELECT count()
FROM filter_ids;

Check a few values to confirm that the data was inserted correctly.

SELECT id
FROM filter_ids
ORDER BY id
LIMIT 10;

Then run the original filtering query.

SELECT count()
FROM events
WHERE user_id IN (SELECT id FROM filter_ids);

Compare the results with the original application output. Confirm that the query returns the expected rows.

If you increased a setting, check the active value.

SELECT
    name,
    value,
    changed
FROM system.settings
WHERE name = 'max_query_size';

Replace max_query_size with the setting you changed.

If the query still fails, read the new error message. It may identify a different limit. Do not increase unrelated settings.

6. Risks and trade-offs

Increasing query limits can allow queries that previously failed. However, it can also increase resource use.

Memory consumption

Large queries require more memory during parsing and analysis. A large AST can consume significant memory, especially when the query contains many expressions or nested conditions.

Parsing time

ClickHouse must parse the query text and build its AST. A very large query can take more time before execution begins. This delay can affect application response time.

Concurrent workloads

If several clients send large queries at the same time, they can increase server resource use. This can affect other queries running on the same server.

Limits are not a query optimization

Increasing AST limits does not make a query more efficient. It only allows ClickHouse to process a larger query structure. If the query scans too much data or runs for hours, investigate the execution plan and data access instead.

8. Summary

  • Large generated SQL statements can exceed query size or AST limits.
  • Move large IN lists into temporary tables, Set tables, or external data.
  • Increase limits only when you cannot change the query structure.
  • Verify the active settings on your server before changing them.
  • Large queries can increase parsing time and memory consumption.

References

  1. ClickHouse documentation: Settings.
  2. ClickHouse documentation: Restrictions on query complexity.
  3. ClickHouse documentation: IN operators.
  4. ClickHouse documentation: External data for query processing.
  5. ClickHouse documentation: Temporary tables.
Share: