ClickHouse · Ergus · Oct 15, 2025 · Aug 20, 2025 · Aug 20, 2025 · Aug 20, 2025
diff --git a/src/Analyzer/Passes/LikePerfectAffixRewritePass.cpp b/src/Analyzer/Passes/LikePerfectAffixRewritePass.cpp
@@ -0,0 +1,162 @@
+#include <Analyzer/Passes/LikePerfectAffixRewritePass.h>
+
+#include <Analyzer/ConstantNode.h>
+#include <Analyzer/FunctionNode.h>
+#include <Analyzer/IQueryTreeNode.h>
+#include <Analyzer/IdentifierNode.h>
+#include <Analyzer/InDepthQueryTreeVisitor.h>
+#include <Core/Settings.h>
+#include <Common/StringUtils.h>
+#include <DataTypes/DataTypeLowCardinality.h>
+#include <DataTypes/DataTypeNullable.h>
+#include <DataTypes/IDataType.h>
+#include <Functions/FunctionFactory.h>
+
+#include <boost/algorithm/string.hpp>
+
+namespace DB
+{
+namespace Setting
+{
+    extern const SettingsBool optimize_rewrite_like_perfect_affix;
+}
+
+namespace
+{
+
+/** Rewrite LIKE expressions with perfect affix (prefix or suffix) to startsWith or endsWith functions.
+  * The scope of the rewrite:
+  *     - `col LIKE 'Prefix%'`: as `startsWith(col, 'Prefix')`
+  *     - `col LIKE '%Suffix'`: as `endsWith(col, 'Suffix')`
+  *     - `NOT LIKE `: negating above functions with `NOT`
+  * Out of scope:
+  *     - `ILIKE` is not rewritten until starts/endsWithCaseInsensitive is implemented, because lower() or left() based rewrites are suboptimal
+  * Type requirement on the left operand, `col`:
+  *     - `startsWith`: Only resolved column of type `String`, `FixedString`, or nested types of these:
+  *         - `LowCardinality(S)`, `Nullable(S)`, `LowCardinality(Nullable(S))` where `S` is either `String` or `FixedString`.
+  *     - `endsWith`: same as `startsWith` except `FixedString` (nested or not), because `c LIKE '%suffix\0'` is not equal to `endsWith(c, 'suffix\0')`.
+  */
+class LikePerfectAffixRewriteVisitor : public InDepthQueryTreeVisitorWithContext<LikePerfectAffixRewriteVisitor>
+{
+public:
+    using Base = InDepthQueryTreeVisitorWithContext<LikePerfectAffixRewriteVisitor>;
+    using Base::Base;
+
+    void enterImpl(QueryTreeNodePtr & node)
+    {
+        if (!getSettings()[Setting::optimize_rewrite_like_perfect_affix])
+            return;
+
+        auto * function_node = node->as<FunctionNode>();
+        if (!function_node)
+            return;
+
+        const String & function_name = function_node->getFunctionName();
+        const bool is_like = (function_name == "like");
+        const bool is_not_like = (function_name == "notLike");
+        if (!(is_like || is_not_like))
+            return;
+
+        auto & args = function_node->getArguments().getNodes();
+        if (args.size() != 2)
+            return;
+
+        /// Do not rewrite for non-column left hand side
+        if (args[0]->getNodeType() != QueryTreeNodeType::COLUMN)
+            return;
+
+        /// Extract affix (prefix or suffix) and check if suitable for rewrite
+        auto * pattern_constant = args[1]->as<ConstantNode>();
+        if (!pattern_constant || !isString(pattern_constant->getResultType()))
+            return;
+
+        auto pattern = pattern_constant->getValue().safeGet<String>();
+        const bool is_suffix = pattern.starts_with("%");
+
+        /// Do not rewrite for
+        /// (1) non-string related type (String, FixedString, or nested within LowCardinality or Nullable or both)
+        /// (2) Suffix matching on FixedString, which requires trimming trailing null chars
+        if (!isResultTypeSupported(args[0]->getResultType(), is_suffix))
+            return;
+
+        /// Only rewrite for perfect prefix or suffix
+        /// Suffix is prefix in reverse
+        if (is_suffix)
+            std::reverse(pattern.begin(), pattern.end());
+
+        auto [affix, is_perfect] = extractFixedPrefixFromLikePattern(pattern, true);
+        if (!is_perfect || affix.empty())
+            return;
+
+        if (is_suffix)
+            std::reverse(affix.begin(), affix.end());
+
+        auto affix_constant = std::make_shared<ConstantNode>(std::move(affix));
+
+        /// Create startsWith/endsWith function
+        FunctionNodePtr new_node = operation(is_suffix ? "endsWith" : "startsWith", args[0], affix_constant);
+        if (is_not_like)
+            new_node = operation("not", new_node);
+
+        /// Replpace the original LIKE node
+        chassert(new_node != nullptr, "should have been created");
+        chassert(new_node->getResultType()->getTypeId() == node->getResultType()->getTypeId(), "Rewrite should preserve type");
+        node = std::move(new_node);
+    }
+private:
+    template<typename... Args>
+    requires (std::is_convertible_v<Args, QueryTreeNodePtr> && ...) && (sizeof...(Args) >= 1)
+    FunctionNodePtr operation(const String & op, Args&&... operands)
+    {
+        auto op_function = std::make_shared<FunctionNode>(op);
+        auto op_resolver = FunctionFactory::instance().get(op, getContext());
+        op_function->getArguments().getNodes() = {std::forward<Args>(operands)...};
+        op_function->resolveAsFunction(op_resolver);
+        return op_function;
+    }
+
+    bool isStr(const DataTypePtr & type, const bool for_suffix)
+    {
+        /// Do not rewrite for fixedstring with suffix, because `col LIKE '%suffix\0'` is not `endsWith(col, 'suffix\0')`
+        if (for_suffix)
+            return isString(type);
+        else
+            return isStringOrFixedString(type);
+    }
+
+    bool isResultTypeSupported(const DataTypePtr & type, const bool for_suffix)
+    {
+        return isStr(type, for_suffix) || isNullableStr(type, for_suffix) || isLowCardinalityMaybeNullableStr(type, for_suffix);
+    }
+
+    bool isLowCardinalityMaybeNullableStr(const DataTypePtr & type, const bool for_suffix)
+    {
+        if (const DataTypeLowCardinality * type_low_cardinality = typeid_cast<const DataTypeLowCardinality *>(type.get()))
+        {
+            DataTypePtr type_dictionary = type_low_cardinality->getDictionaryType();
+            return isStr(type_dictionary, for_suffix) || isNullableStr(type_dictionary, for_suffix);
+        }
+        return false;
+    }
+
+    bool isNullableStr(const DataTypePtr & type, const bool for_suffix)
+    {
+        if (const DataTypeNullable * type_nullable = typeid_cast<const DataTypeNullable *>(type.get()))
+        {
+            DataTypePtr type_nested = type_nullable->getNestedType();
+            return isStr(type_nested, for_suffix);
+        }
+        return false;
+    }
+};
+
+}
+
+void LikePerfectAffixRewritePass::run(QueryTreeNodePtr & query_tree_node, ContextPtr context)
+{
+    LikePerfectAffixRewriteVisitor visitor(context);
+    visitor.visit(query_tree_node);
+}
+
+}
+
diff --git a/src/Analyzer/Passes/LikePerfectAffixRewritePass.h b/src/Analyzer/Passes/LikePerfectAffixRewritePass.h
@@ -0,0 +1,17 @@
+#pragma once
+
+#include <Analyzer/IQueryTreePass.h>
+
+namespace DB
+{
+class LikePerfectAffixRewritePass final : public IQueryTreePass
+{
+public:
+    String getName() override { return "LikePerfectAffixRewritePass"; }
+
+    String getDescription() override { return "Rewrite LIKE expressions with perfect affix (prefix or suffix) into startsWith or endsWith functions."; }
+
+    void run(QueryTreeNodePtr & query_tree_node, ContextPtr context) override;
+};
+
+}
diff --git a/src/Analyzer/Passes/tests/gtest_analyzer_utils.cpp b/src/Analyzer/Passes/tests/gtest_analyzer_utils.cpp
@@ -0,0 +1,60 @@
+#include <Analyzer/Passes/tests/gtest_analyzer_utils.h>
+#include <gtest/gtest.h>
+
+#include <Analyzer/ColumnNode.h>
+#include <Analyzer/FunctionNode.h>
+#include <Analyzer/IdentifierNode.h>
+#include <Analyzer/IQueryTreePass.h>
+#include <Analyzer/QueryTreeBuilder.h>
+#include <Analyzer/Utils.h>
+#include <Common/tests/gtest_global_context.h>
+#include <Common/tests/gtest_global_register.h>
+#include <Parsers/parseQuery.h>
+#include <Parsers/ExpressionListParsers.h>
+
+using namespace DB;
+
+/// Resolve columns of a type within a query tree
+static QueryTreeNodePtr resolveColumn(DataTypePtr type, QueryTreeNodePtr node, std::map<String, QueryTreeNodePtr> & resolved_map, ContextPtr context)
+{
+    auto * function_node = node->as<FunctionNode>();
+    if (!function_node)
+    {
+        auto * identifier_node = node->as<IdentifierNode>();
+        /// it is a column
+        if (identifier_node)
+        {
+            String col_name = identifier_node->getIdentifier().getFullName();
+            auto it = resolved_map.find(col_name);
+            if (it != resolved_map.end())
+                return it->second;
+            auto column = std::make_shared<ColumnNode>(NameAndTypePair(col_name, type), node);
+            resolved_map[col_name] = column;
+            return column;
+        }
+        /// it is constant
+        return node;
+    }
+    QueryTreeNodes new_args;
+    for (const auto & argument : function_node->getArguments())
+    {
+        auto arg = resolveColumn(type, argument, resolved_map, context);
+        new_args.push_back(arg);
+    }
+    function_node->getArguments().getNodes() = std::move(new_args);
+    resolveOrdinaryFunctionNodeByName(*function_node, function_node->getFunctionName(), context);
+    return node;
+};
+
+/// Run an analyzer pass on a condition involving a column of a type, then compare against the expected expression.
+void testPassOnCondition(QueryTreePassPtr pass, DataTypePtr columnType, const String & cond, const String & expected)
+{
+    ContextPtr context = getContext().context; ParserExpressionWithOptionalAlias exp_elem(false);
+    ASTPtr query = parseQuery(exp_elem, cond, 10000, 10000, 10000);
+    QueryTreeNodePtr node = buildQueryTree(query, context);
+
+    std::map<String, QueryTreeNodePtr> resolved_map;
+    node = resolveColumn(columnType, node, resolved_map, context);
+    pass->run(node, context);
+    EXPECT_EQ(node->formatConvertedASTForErrorMessage(), expected);
+}
diff --git a/src/Analyzer/Passes/tests/gtest_analyzer_utils.h b/src/Analyzer/Passes/tests/gtest_analyzer_utils.h
@@ -0,0 +1,7 @@
+#pragma once
+
+#include <Analyzer/IQueryTreePass.h>
+#include <Columns/IColumn.h>
+#include <base/types.h>
+
+void testPassOnCondition(DB::QueryTreePassPtr pass, DB::DataTypePtr columnType, const String & cond, const String & expected);
diff --git a/src/Analyzer/Passes/tests/gtest_like_perfect_affix_rewrite.cpp b/src/Analyzer/Passes/tests/gtest_like_perfect_affix_rewrite.cpp
@@ -0,0 +1,62 @@
+#include <gtest/gtest.h>
+
+#include <Analyzer/ColumnNode.h>
+#include <Analyzer/FunctionNode.h>
+#include <Analyzer/IdentifierNode.h>
+#include <Analyzer/QueryTreeBuilder.h>
+#include <Analyzer/Passes/LikePerfectAffixRewritePass.h>
+#include <Analyzer/Passes/tests/gtest_analyzer_utils.h>
+#include <Analyzer/Utils.h>
+#include <Common/tests/gtest_global_context.h>
+#include <Common/tests/gtest_global_register.h>
+#include <DataTypes/DataTypeString.h>
+#include <DataTypes/DataTypesNumber.h>
+#include <Parsers/parseQuery.h>
+#include <Parsers/ExpressionListParsers.h>
+
+using namespace DB;
+
+TEST(LikePerfectAffixRewrite, rewrite)
+{
+    tryRegisterFunctions();
+    auto test_f = [&](const String & cond, const String & expected)
+    {
+        testPassOnCondition(
+            QueryTreePassPtr(new LikePerfectAffixRewritePass()), DataTypePtr(new DataTypeString()), 
+            cond, expected);
+    };
+
+    /// Perfect affix LIKE
+    test_f("col LIKE 'Test%'", "startsWith(col, 'Test')");
+    test_f("col LIKE 'a%'", "startsWith(col, 'a')");
+    test_f("col LIKE '%Test'", "endsWith(col, 'Test')");
+    test_f("col LIKE '%a'", "endsWith(col, 'a')");
+
+    /// Perfect affix ILIKE should NOT be rewritten
+    test_f("col ILIKE 'Test%'", "col ILIKE 'Test%'");
+
+    /// Perfect affix without upper bound
+    test_f("col LIKE '\xFF%'", "startsWith(col, '\xFF')");
+    test_f("col LIKE '%\xFF'", "endsWith(col, '\xFF')");
+
+    /// Perfect affix NOT LIKE
+    test_f("col NOT LIKE 'Test%'", "not(startsWith(col, 'Test'))");
+    test_f("col NOT LIKE '%Test'", "not(endsWith(col, 'Test'))");
+
+    /// Imperfect affix (I)LIKE should not be rewritten
+    test_f("col LIKE 'hello_world%'", "col LIKE 'hello_world%'");
+    test_f("col LIKE '%hello_world'", "col LIKE '%hello_world'");
+    test_f("col LIKE '%test%'", "col LIKE '%test%'");
+    test_f("col LIKE '%test_'", "col LIKE '%test_'");
+    test_f("col LIKE '_test%'", "col LIKE '_test%'");
+    test_f("col LIKE '%'", "col LIKE '%'");
+    test_f("col LIKE 'exactvalue'", "col LIKE 'exactvalue'");
+
+    test_f("col ILIKE 'hello_world%'", "col ILIKE 'hello_world%'");
+
+    /// Imperfect affix NOT (I)LIKE should not be rewritten
+    test_f("col NOT LIKE 'hello_world%'", "col NOT LIKE 'hello_world%'");
+    test_f("col NOT LIKE '%hello_world'", "col NOT LIKE '%hello_world'");
+    test_f("col NOT ILIKE 'hello_world%'", "col NOT ILIKE 'hello_world%'");
+    test_f("col NOT ILIKE '%hello_world'", "col NOT ILIKE '%hello_world'");
+}
diff --git a/src/Analyzer/Passes/tests/gtest_logical_expression_optimizer.cpp b/src/Analyzer/Passes/tests/gtest_logical_expression_optimizer.cpp
@@ -4,6 +4,7 @@
 #include <Analyzer/FunctionNode.h>
 #include <Analyzer/IdentifierNode.h>
 #include <Analyzer/Passes/LogicalExpressionOptimizerPass.h>
+#include <Analyzer/Passes/tests/gtest_analyzer_utils.h>
 #include <Analyzer/QueryTreeBuilder.h>
 #include <Analyzer/Utils.h>
 #include <Common/tests/gtest_global_context.h>
@@ -14,53 +15,14 @@
 
 using namespace DB;
 
-QueryTreeNodePtr resolve_everything(QueryTreeNodePtr node, std::map<String, QueryTreeNodePtr> & resolved_map, ContextPtr context)
-{
-    auto * function_node = node->as<FunctionNode>();
-    if (!function_node)
-    {
-        auto * identifier_node = node->as<IdentifierNode>();
-        /// it is a column
-        if (identifier_node)
-        {
-            String col_name = identifier_node->getIdentifier().getFullName();
-            auto it = resolved_map.find(col_name);
-            if (it != resolved_map.end())
-                return it->second;
-            DataTypePtr type = DataTypePtr(new DataTypeInt32());
-            auto column = std::make_shared<ColumnNode>(NameAndTypePair(col_name, type), node);
-            resolved_map[col_name] = column;
-            return column;
-        }
-        /// it is constant
-        return node;
-    }
-    QueryTreeNodes new_args;
-    for (const auto & argument : function_node->getArguments())
-    {
-        auto arg = resolve_everything(argument, resolved_map, context);
-        new_args.push_back(arg);
-    }
-    function_node->getArguments().getNodes() = std::move(new_args);
-    resolveOrdinaryFunctionNodeByName(*function_node, function_node->getFunctionName(), context);
-    return node;
-};
-
 TEST(OptimizeAndCompareChain, compare)
 {
     tryRegisterFunctions();
-    std::map<String, QueryTreeNodePtr> resolved_map;
     auto test_f = [&](const String & cond, const String & expected)
     {
-        ContextPtr context = getContext().context;
-        ParserExpressionWithOptionalAlias exp_elem(false);
-        ASTPtr query = parseQuery(exp_elem, cond, 10000, 10000, 10000);
-        QueryTreeNodePtr node = buildQueryTree(query, context);
-
-        node = resolve_everything(node, resolved_map, context);
-        LogicalExpressionOptimizerPass pass;
-        pass.run(node, context);
-        EXPECT_EQ(node->formatConvertedASTForErrorMessage(), expected);
+        testPassOnCondition(
+            QueryTreePassPtr(new LogicalExpressionOptimizerPass()), DataTypePtr(new DataTypeInt32()), 
+            cond, expected);
     };
 
     // constant is large

diff --git a/src/Analyzer/QueryTreePassManager.cpp b/src/Analyzer/QueryTreePassManager.cpp
@@ -33,6 +33,7 @@
 #include <Analyzer/Passes/IfTransformStringsToEnumPass.h>
 #include <Analyzer/Passes/InjectRandomOrderIfNoOrderByPass.h>
 #include <Analyzer/Passes/L2DistanceTransposedPartialReadsPass.h>
+#include <Analyzer/Passes/LikePerfectAffixRewritePass.h>
 #include <Analyzer/Passes/LogicalExpressionOptimizerPass.h>
 #include <Analyzer/Passes/MultiIfToIfPass.h>
 #include <Analyzer/Passes/NormalizeCountVariantsPass.h>
@@ -309,6 +310,7 @@ void addQueryTreePasses(QueryTreePassManager & manager, bool only_analyze)
 
     manager.addPass(std::make_unique<ConvertOrLikeChainPass>());
 
+    manager.addPass(std::make_unique<LikePerfectAffixRewritePass>());
     manager.addPass(std::make_unique<LogicalExpressionOptimizerPass>());
 
     manager.addPass(std::make_unique<CrossToInnerJoinPass>());