ClickHouse DB::Exception: Query cache used with non-deterministic functions

The "DB::Exception: Query cache used with non-deterministic functions" error in ClickHouse occurs when you attempt to cache the results of a query that contains non-deterministic functions such as now(), rand(), currentUser(), or similar. The error code is QUERY_CACHE_USED_WITH_NONDETERMINISTIC_FUNCTIONS. ClickHouse raises this error because caching results from functions that return different values on each call would produce stale or incorrect data on subsequent cache hits.

Impact

This error prevents the query from being stored in or served from the query cache. The effects include:

The query itself may still execute successfully (depending on settings), but its results will not be cached
Repeated execution of the same query will hit the underlying tables every time, increasing resource usage
Applications relying on the query cache for performance may experience higher latency than expected

Common Causes

Using now() or time-related functions -- Queries that include now(), today(), yesterday(), or currentTimestamp() produce different results at different times, making cached results unreliable.
Random value functions -- Functions like rand(), randNormal(), randBernoulli(), or generateUUIDv4() are inherently non-deterministic.
Session or context functions -- Functions such as currentUser(), currentDatabase(), or queryID() return values that depend on the execution context.
Dictionary functions with TTL-based refresh -- In some configurations, dictionary lookups may be flagged as non-deterministic if the dictionary data can change between calls.
Explicit query cache settings -- Setting use_query_cache = 1 at the session or query level while the query contains any of the above functions.

Troubleshooting and Resolution Steps

Identify the non-deterministic functions in your query: Review your query text and look for any function that does not produce the same output given the same input. Common offenders:

-- These will trigger the error when used with query cache
SELECT now(), count(*) FROM events;
SELECT rand() as sample_id, * FROM users LIMIT 10;
SELECT *, currentUser() FROM logs;

Replace non-deterministic functions with literal values: Compute the value outside the query and pass it as a constant:

-- Instead of:
SELECT * FROM events WHERE timestamp > now() - INTERVAL 1 HOUR;

-- Use a fixed value:
SELECT * FROM events WHERE timestamp > '2024-01-15 10:00:00';

Or compute it in a parameterized query:

SET param_cutoff = '2024-01-15 10:00:00';
SELECT * FROM events WHERE timestamp > {cutoff:DateTime};

Move non-deterministic expressions outside the cached query: Use a two-step approach where the cacheable part is separated:

-- Step 1: cacheable aggregation
SELECT date, count(*) AS cnt FROM events GROUP BY date
SETTINGS use_query_cache = 1;

-- Step 2: filter by current time in application code

Allow non-deterministic caching explicitly (use with caution): If you understand the trade-offs and accept that cached results may be stale, you can override the safety check:
```
SELECT now(), count(*) FROM events
SETTINGS
    use_query_cache = 1,
    query_cache_nondeterministic_function_handling = 'save';
```
The query_cache_nondeterministic_function_handling setting accepts 'throw' (default, raises the error), 'save' (caches anyway), and 'ignore' (does not cache but does not throw).
Set the handling mode at session or profile level: If your workload tolerates slightly stale cached results across the board:
```
SET query_cache_nondeterministic_function_handling = 'ignore';
```
This silently skips caching for non-deterministic queries without raising an error.

Best Practices

Design cacheable queries to be fully deterministic -- pass time boundaries and other variable inputs as literal parameters rather than using functions like now().
Use query_cache_nondeterministic_function_handling = 'ignore' in interactive or exploratory environments where the error message is disruptive but caching is not critical.
Reserve 'save' mode for dashboards where slightly stale data (within the cache TTL) is acceptable and performance gains from caching outweigh freshness requirements.
Set appropriate query_cache_ttl values so that even if non-deterministic queries are cached, the staleness window is bounded.
Audit queries before enabling the query cache in production to identify all non-deterministic function usage.

Frequently Asked Questions

Q: Which functions does ClickHouse consider non-deterministic for query cache purposes?
A: ClickHouse flags functions that can return different results across calls with the same arguments. This includes time functions (now(), today(), currentTimestamp()), random functions (rand(), generateUUIDv4()), and context functions (currentUser(), currentDatabase(), queryID()). The full list is determined by each function's internal isDeterministic() property.

Q: Does the query still execute when this error is thrown?
A: By default (when query_cache_nondeterministic_function_handling is set to 'throw'), the query fails with an exception. If set to 'ignore', the query executes normally but results are not cached.

Q: Can I use toStartOfHour(now()) and cache the result?
A: Even though toStartOfHour(now()) changes less frequently than now(), ClickHouse still considers it non-deterministic because now() is non-deterministic. You would need to pass the rounded time as a literal value or use the 'save' handling mode.

Q: Is the query cache the same as the system uncompressed cache or mark cache?
A: No. The query cache stores final query results and serves them directly on cache hits. The uncompressed cache and mark cache operate at the storage layer and cache raw data blocks and mark file contents, respectively. The non-deterministic function restriction applies only to the query result cache.