Repository navigation
Database name is ignored when refreshing dictionaries with query settings #2155
Description
Describe the bug
When a dictionary depends on a table via the CLICKHOUSE source with a QUERY and DB clauses, running a SYSTEM RELOAD DICTIONARY query on that dictionary from the clickhouse client using CommandSettings produces an error whereby clickhouse is unable to find the table.
Steps to reproduce
var commandSettings = new CommandSettings();
commandSettings.setDatabase("my_database");
clickHouseClient.query("CREATE DATABASE IF NOT EXISTS my_database;");
clickHouseClient.query("CREATE TABLE IF NOT EXISTS my_table (id Int64) ENGINE = MergeTree() ORDER BY id;", commandSettings);
clickHouseClient.query(
"""
CREATE DICTIONARY IF NOT EXISTS my_dictionary
(id Int64)
PRIMARY KEY id
SOURCE(CLICKHOUSE(QUERY 'SELECT id FROM my_table WHERE id > 0' DATABASE my_database))
LIFETIME(MIN 1 MAX 10)
LAYOUT(FLAT());
""", commandSettings);
clickHouseClient.execute("SYSTEM RELOAD DICTIONARY my_dictionary;", commandSettings).get();Expected behaviour
The dictionary is reloaded correctly. The queries take the database name parameter into account correctly when using CommandSettings.setDatabase.
Unexpected behaviour
The dictionary cannot be reloaded because the database name is not included while resolving the source expression. The only way to get around this is to use the fully-qualified table name my_database.my_table when defining and reloading the dictionary.
Code example
This instead works:
clickHouseClient.query("CREATE DATABASE IF NOT EXISTS my_database;");
clickHouseClient.query("CREATE TABLE IF NOT EXISTS my_database.my_table (id Int64) ENGINE = MergeTree() ORDER BY id;");
clickHouseClient.query(
"""
CREATE DICTIONARY IF NOT EXISTS my_database.my_dictionary
(id Int64)
PRIMARY KEY id
SOURCE(CLICKHOUSE(QUERY 'SELECT id FROM my_database.my_table WHERE id > 0' DB my_database))
LIFETIME(MIN 1 MAX 10)
LAYOUT(FLAT());
""");
clickHouseClient.execute("SYSTEM RELOAD DICTIONARY my_database.my_dictionary;").get();Error log
Code: 60. DB::Exception: Unknown table expression identifier 'my_table' in scope SELECT id FROM my_table WHERE id > 0. (UNKNOWN_TABLE) (version 25.2.1.2018 (official build))
com.clickhouse.client.api.ServerException: Code: 60. DB::Exception: Unknown table expression identifier 'my_table' in scope SELECT id FROM my_table WHERE id > 0. (UNKNOWN_TABLE) (version 25.2.1.2018 (official build))
at com.clickhouse.client.api.internal.HttpAPIClientHelper.readError(HttpAPIClientHelper.java:384)
at com.clickhouse.client.api.internal.HttpAPIClientHelper.executeRequest(HttpAPIClientHelper.java:434)
at com.clickhouse.client.api.Client.lambda$query$10(Client.java:1623)
at com.clickhouse.client.api.Client.runAsyncOperation(Client.java:2007)
at com.clickhouse.client.api.Client.query(Client.java:1666)
at com.clickhouse.client.api.Client.query(Client.java:1564)
at com.clickhouse.client.api.Client.execute(Client.java:1909)
Configuration
Environment
- Client version: 0.8.1
- Language version: 17
- OS: mac os sequoia 15.2
ClickHouse server
- ClickHouse Server version: 25.2.1.2018
Activity
enqueue commentedon Jul 25, 2025
The behavior is not specific to the Java Connector. If you try to perform the steps in ClickHouse CLI, you will arrive at the same exception.
Please see the section in the manual on creating a dictionary with ClickHouse as a source.
You can include database name directly in query using dot-notation, if you have a complicated query, e.g.
SELECT id, value from my_database.my_table where id > 1.If your query is as simple as in your example, you might want to use
database,tableandwhereproperties in ClickHouse source definition and not use aqueryproperty, like so:chernser commentedon Nov 17, 2025
Should be fixed on the latest version 0.9.4.