ClickHouse · yakov-olkhovskiy · Sep 28, 2025 · Jul 29, 2025 · Aug 1, 2025 · Aug 11, 2025
diff --git a/src/Client/LocalConnection.cpp b/src/Client/LocalConnection.cpp
@@ -16,6 +16,7 @@
 #include <QueryPipeline/Pipe.h>
 #include <Parsers/ASTInsertQuery.h>
 #include <Storages/IStorage.h>
+#include <Common/config_version.h>
 #include <Common/ConcurrentBoundedQueue.h>
 #include <Common/CurrentThread.h>
 #include <Interpreters/InternalTextLogsQueue.h>
@@ -732,11 +733,15 @@ Packet LocalConnection::receivePacket()
 }
 
 void LocalConnection::getServerVersion(
-    const ConnectionTimeouts & /* timeouts */, String & /* name */,
-    UInt64 & /* version_major */, UInt64 & /* version_minor */,
-    UInt64 & /* version_patch */, UInt64 & /* revision */)
-{
-    throw Exception(ErrorCodes::NOT_IMPLEMENTED, "Not implemented");
+    const ConnectionTimeouts & /* timeouts */, String & name,
+    UInt64 & version_major, UInt64 & version_minor,
+    UInt64 & version_patch, UInt64 & revision)
+{
+    name = std::string(VERSION_NAME);
+    version_major = VERSION_MAJOR;
+    version_minor = VERSION_MINOR;
+    version_patch = VERSION_PATCH;
+    revision = DBMS_TCP_PROTOCOL_VERSION;
 }
 
 void LocalConnection::setDefaultDatabase(const String & database)

diff --git a/src/Client/Suggest.cpp b/src/Client/Suggest.cpp
@@ -28,7 +28,10 @@ namespace ErrorCodes
     extern const int USER_SESSION_LIMIT_EXCEEDED;
 }
 
-static String getLoadSuggestionQuery(Int32 suggestion_limit, bool basic_suggestion, UInt64 server_revision)
+static constexpr const UInt64 CLICKHOUSE_SERVER_MIN_MAJOR_VERSION_WITH_SYSTEM_COMPLETIONS = 25;
+static constexpr const UInt64 CLICKHOUSE_SERVER_MIN_MINOR_VERSION_WITH_SYSTEM_COMPLETIONS = 8;
+
+static String getLoadSuggestionQueryUsingSystemTables(Int32 suggestion_limit, bool basic_suggestion, UInt64 server_revision)
 {
     /// NOTE: Once you will update the completion list,
     /// do not forget to update 01676_clickhouse_client_autocomplete.sh
@@ -89,6 +92,57 @@ static String getLoadSuggestionQuery(Int32 suggestion_limit, bool basic_suggesti
     return query;
 }
 
+static String getLoadSuggestionQueryUsingSystemCompletionsTable(Int32 suggestion_limit, bool basic_suggestion, UInt64 server_revision)
+{
+    /// NOTE: Once you will update the completion list,
+    /// do not forget to update 01676_clickhouse_client_autocomplete.sh
+    /// TODO: Use belongs column for better contextual suggestions
+    String unlimited_contexts = fmt::format(
+        "('function', 'table engine', 'format', 'table function', 'data type', 'merge tree setting', 'setting', 'aggregate function combinator pair'{}{})",
+        (server_revision >= DBMS_MIN_REVISION_WITH_SYSTEM_KEYWORDS_TABLE ? ", 'keyword'" : ""),
+        (basic_suggestion ? "" : ", 'cluster', 'macro', 'policy'")
+    );
+    String query = fmt::format(
+        "SELECT word FROM system.completions WHERE context IN {}",
+        unlimited_contexts
+    );
+
+    /// The user may disable loading of databases, tables, columns by setting suggestion_limit to zero.
+    if (suggestion_limit > 0)
+    {
+        String limited_contexts = fmt::format(
+            "('database', 'table', 'column'{})",
+            (basic_suggestion ? "" : ", 'dictionary'")
+        );
+        query += fmt::format(
+            " UNION ALL SELECT word FROM ("
+            " SELECT word, context, ROW_NUMBER() OVER (PARTITION BY context ORDER BY word) AS rn FROM "
+            " (SELECT DISTINCT word, context FROM system.completions WHERE context IN {})"
+            ") WHERE rn <= {}",
+            limited_contexts,
+            suggestion_limit
+        );
+    }
+
+    query = "SELECT DISTINCT arrayJoin(extractAll(word, '[\\\\w_]{2,}')) AS res FROM (" + query + ") WHERE notEmpty(res)";
+    return query;
+}
+
+static String getLoadSuggestionQuery(IServerConnection & connection, Int32 suggestion_limit, bool basic_suggestion, const ConnectionTimeouts & timeouts)
+{
+
+    String server_name;
+    UInt64 server_major_version = 0;
+    UInt64 server_minor_version = 0;
+    UInt64 server_patch_version = 0;
+    UInt64 server_revision = 0;
+    connection.getServerVersion(timeouts, server_name, server_major_version, server_minor_version, server_patch_version, server_revision);
+    if (server_major_version > CLICKHOUSE_SERVER_MIN_MAJOR_VERSION_WITH_SYSTEM_COMPLETIONS || (server_major_version == CLICKHOUSE_SERVER_MIN_MAJOR_VERSION_WITH_SYSTEM_COMPLETIONS && server_minor_version >= CLICKHOUSE_SERVER_MIN_MINOR_VERSION_WITH_SYSTEM_COMPLETIONS))
+        return getLoadSuggestionQueryUsingSystemCompletionsTable(suggestion_limit, basic_suggestion, server_revision);
+
+    return getLoadSuggestionQueryUsingSystemTables(suggestion_limit, basic_suggestion, server_revision);
+}
+
 template <typename ConnectionType>
 void Suggest::load(ContextPtr context, const ConnectionParameters & connection_parameters, Int32 suggestion_limit, bool wait_for_load)
 {
@@ -108,13 +162,11 @@ void Suggest::load(ContextPtr context, const ConnectionParameters & connection_p
             try
             {
                 auto connection = ConnectionType::createConnection(connection_parameters, my_context);
+                const auto basic_suggestion = std::is_same_v<ConnectionType, LocalConnection>;
+                auto suggestion_query = getLoadSuggestionQuery(*connection, suggestion_limit, basic_suggestion, connection_parameters.timeouts);
                 fetch(*connection,
                     connection_parameters.timeouts,
-                    getLoadSuggestionQuery(
-                        suggestion_limit,
-                        std::is_same_v<ConnectionType, LocalConnection>,
-                        connection->getServerRevision(connection_parameters.timeouts)
-                    ),
+                    suggestion_query,
                     my_context->getClientInfo());
             }
             catch (const Exception & e)
@@ -159,7 +211,8 @@ void Suggest::load(IServerConnection & connection,
 {
     try
     {
-        fetch(connection, timeouts, getLoadSuggestionQuery(suggestion_limit, true, connection.getServerRevision(timeouts)), client_info);
+        auto suggestion_query = getLoadSuggestionQuery(connection, suggestion_limit, true, timeouts);
+        fetch(connection, timeouts, suggestion_query, client_info);
     }
     catch (...)
     {

diff --git a/src/Storages/System/StorageSystemCompletions.cpp b/src/Storages/System/StorageSystemCompletions.cpp
@@ -1,43 +1,45 @@
-#include <AggregateFunctions/AggregateFunctionFactory.h>
 #include <Access/ContextAccess.h>
+#include <AggregateFunctions/AggregateFunctionFactory.h>
+#include <AggregateFunctions/Combinators/AggregateFunctionCombinatorFactory.h>
 #include <Columns/ColumnString.h>
-#include <Common/Macros.h>
 #include <Core/Settings.h>
 #include <DataTypes/DataTypeFactory.h>
 #include <DataTypes/DataTypeNullable.h>
 #include <DataTypes/DataTypeString.h>
 #include <Databases/IDatabase.h>
+#include <Dictionaries/DictionaryStructure.h>
 #include <Dictionaries/IDictionary.h>
 #include <Dictionaries/IDictionarySource.h>
-#include <Dictionaries/DictionaryStructure.h>
 #include <Formats/FormatFactory.h>
-#include <Functions/IFunction.h>
 #include <Functions/FunctionFactory.h>
-#include <Functions/UserDefined/UserDefinedSQLFunctionFactory.h>
+#include <Functions/IFunction.h>
 #include <Functions/UserDefined/UserDefinedExecutableFunctionFactory.h>
+#include <Functions/UserDefined/UserDefinedSQLFunctionFactory.h>
 #include <Interpreters/Context.h>
 #include <Interpreters/DatabaseCatalog.h>
 #include <Interpreters/ExternalDictionariesLoader.h>
 #include <Parsers/CommonParsers.h>
-#include <Storages/System/StorageSystemCompletions.h>
-#include <Storages/StorageFactory.h>
 #include <Storages/MergeTree/MergeTreeSettings.h>
+#include <Storages/StorageFactory.h>
+#include <Storages/System/StorageSystemCompletions.h>
 #include <TableFunctions/TableFunctionFactory.h>
+#include <Common/Macros.h>
 
 
 namespace DB
 {
 
 namespace Setting
 {
-    extern const SettingsUInt64 readonly;
-    extern const SettingsSeconds lock_acquire_timeout;
+extern const SettingsUInt64 readonly;
+extern const SettingsSeconds lock_acquire_timeout;
 }
 
 static constexpr const char * DATABASE_CONTEXT = "database";
 static constexpr const char * TABLE_CONTEXT = "table";
 static constexpr const char * COLUMN_CONTEXT = "column";
 static constexpr const char * FUNCTION_CONTEXT = "function";
+static constexpr const char * AGGREGATE_FUNCTION_COMBINATOR_PAIR_CONTEXT = "aggregate function combinator pair";
 static constexpr const char * TABLE_ENGINE_CONTEXT = "table engine";
 static constexpr const char * FORMAT_CONTEXT = "format";
 static constexpr const char * TABLE_FUNCTION_CONTEXT = "table function";
@@ -51,19 +53,25 @@ static constexpr const char * DICTIONARY_CONTEXT = "dictionary";
 
 ColumnsDescription StorageSystemCompletions::getColumnsDescription()
 {
-    auto description = ColumnsDescription
-    {
+    auto description = ColumnsDescription{
         {"word", std::make_shared<DataTypeString>(), "Completion token."},
         {"context", std::make_shared<DataTypeString>(), "Token entity kind (e.g. table)."},
-        {"belongs", std::make_shared<DataTypeNullable>(std::make_shared<DataTypeString>()), "Token for entity, this token belongs to (e.g. name of owning database)."}
-    };
+        {"belongs",
+         std::make_shared<DataTypeNullable>(std::make_shared<DataTypeString>()),
+         "Token for entity, this token belongs to (e.g. name of owning database)."}};
     return description;
 }
 
-void fillDataWithTableColumns(const String & database_name, const String & table_name, const StoragePtr & table, MutableColumns & res_columns, const ContextPtr & context)
+void fillDataWithTableColumns(
+    const String & database_name,
+    const String & table_name,
+    const StoragePtr & table,
+    MutableColumns & res_columns,
+    const ContextPtr & context)
 {
     const auto & access = context->getAccess();
-    if (!access->isGranted(AccessType::SHOW_TABLES) || !access->isGranted(AccessType::SHOW_TABLES, database_name) || !access->isGranted(AccessType::SHOW_TABLES, database_name, table_name))
+    if (!access->isGranted(AccessType::SHOW_TABLES) || !access->isGranted(AccessType::SHOW_TABLES, database_name)
+        || !access->isGranted(AccessType::SHOW_TABLES, database_name, table_name))
         return;
 
     if (!table)
@@ -119,7 +127,6 @@ void fillDataWithDatabasesTablesColumns(MutableColumns & res_columns, const Cont
             const auto & table = iterator->table();
             fillDataWithTableColumns(database_name, table_name, table, res_columns, context);
         }
-
     }
 
     if (context->hasSessionContext())
@@ -154,6 +161,23 @@ void fillDataWithFunctions(MutableColumns & res_columns, const ContextPtr & cont
         insert_function(function_name);
 }
 
+void fillDataWithAggregateFunctionCombinatorPair(MutableColumns & res_columns)
+{
+    const auto & aggregate_functions = AggregateFunctionFactory::instance().getAllRegisteredNames();
+    const auto & aggregate_function_combinators = AggregateFunctionCombinatorFactory::instance().getAllAggregateFunctionCombinators();
+    for (const auto & function_name : aggregate_functions)
+    {
+        for (const auto & [combinator_name, combinator] : aggregate_function_combinators)
+        {
+            if (combinator->isForInternalUsageOnly())
+                continue;
+            res_columns[0]->insert(function_name + combinator_name);
+            res_columns[1]->insert(AGGREGATE_FUNCTION_COMBINATOR_PAIR_CONTEXT);
+            res_columns[2]->insertDefault();
+        }
+    }
+}
+
 void fillDataWithTableEngines(MutableColumns & res_columns)
 {
     const auto & storage_factory = StorageFactory::instance();
@@ -170,9 +194,9 @@ void fillDataWithFormats(MutableColumns & res_columns)
 {
     const auto & format_factory = FormatFactory::instance();
     const auto & formats = format_factory.getAllFormats();
-    for (const auto & [format_name, _] : formats)
+    for (const auto & [_, creators] : formats)
     {
-        res_columns[0]->insert(format_name);
+        res_columns[0]->insert(creators.name);
         res_columns[1]->insert(FORMAT_CONTEXT);
         res_columns[2]->insertDefault();
     }
@@ -272,7 +296,7 @@ void fillDataWithPolicies(MutableColumns & res_columns, const ContextPtr & conte
 void fillDataWithDictionaries(MutableColumns & res_columns, const ContextPtr & context)
 {
     const auto & access = context->getAccess();
-    if (access->isGranted(AccessType::SHOW_DICTIONARIES))
+    if (!access->isGranted(AccessType::SHOW_DICTIONARIES))
         return;
 
     const auto & external_dictionaries = context->getExternalDictionariesLoader();
@@ -297,7 +321,8 @@ void fillDataWithDictionaries(MutableColumns & res_columns, const ContextPtr & c
     }
 }
 
-void StorageSystemCompletions::fillData(MutableColumns & res_columns, ContextPtr context, const ActionsDAG::Node *, std::vector<UInt8>) const
+void StorageSystemCompletions::fillData(
+    MutableColumns & res_columns, ContextPtr context, const ActionsDAG::Node *, std::vector<UInt8>) const
 {
     fillDataWithDatabasesTablesColumns(res_columns, context);
     fillDataWithFunctions(res_columns, context);
@@ -312,6 +337,7 @@ void StorageSystemCompletions::fillData(MutableColumns & res_columns, ContextPtr
     fillDataWithMacros(res_columns, context);
     fillDataWithPolicies(res_columns, context);
     fillDataWithDictionaries(res_columns, context);
+    fillDataWithAggregateFunctionCombinatorPair(res_columns);
 }
 
 }
diff --git a/tests/integration/test_suggestions/__init__.py b/tests/integration/test_suggestions/__init__.py
diff --git a/tests/integration/test_suggestions/configs/dictionary.xml b/tests/integration/test_suggestions/configs/dictionary.xml
@@ -0,0 +1,41 @@
+<clickhouse>
+<dictionary>
+   <name>default_dictionary</name>
+   <source>
+        <clickhouse>
+            <host>localhost</host>
+            <port>9000</port>
+            <user>default</user>
+            <password></password>
+            <db>test</db>
+            <table>default_dictionary_table</table>
+        </clickhouse>
+    </source>
+
+       <lifetime>
+           <min>0</min>
+           <max>0</max>
+       </lifetime>
+
+       <layout>
+           <cache><size_in_cells>128</size_in_cells></cache>
+       </layout>
+
+    <structure>
+        <id>
+            <name>id</name>
+        </id>
+        <attribute>
+            <name>SomeValue1</name>
+            <type>UInt8</type>
+            <null_value>1</null_value>
+        </attribute>
+
+        <attribute>
+            <name>SomeValue2</name>
+            <type>String</type>
+            <null_value>''</null_value>
+        </attribute>
+   </structure>
+</dictionary>
+</clickhouse>
diff --git a/tests/integration/test_suggestions/configs/macros.xml b/tests/integration/test_suggestions/configs/macros.xml
@@ -0,0 +1,5 @@
+<clickhouse>
+    <macros>
+        <defaultMac>ro</defaultMac>
+    </macros>
+</clickhouse>
diff --git a/tests/integration/test_suggestions/configs/users.xml b/tests/integration/test_suggestions/configs/users.xml
@@ -0,0 +1,9 @@
+<clickhouse>
+    <users>
+        <default>
+        </default>      
+        <test_user>
+            <password>123</password>
+        </test_user>
+    </users>
+</clickhouse>