Pushdown ilike (#18540)

9 files changed, 101 insertions, 34 deletions

diff --git a/ydb/core/kqp/expr_nodes/kqp_expr_nodes.json b/ydb/core/kqp/expr_nodes/kqp_expr_nodes.json
index 2f5f3586613..c5edab05fa8 100644
--- a/ydb/core/kqp/expr_nodes/kqp_expr_nodes.json
+++ b/ydb/core/kqp/expr_nodes/kqp_expr_nodes.json
@@ -670,7 +670,8 @@
             "Match": {"Type": "Callable", "Name": "KqpOlapApply"},
             "Children": [
                 {"Index": 0, "Name": "Lambda", "Type": "TCoLambda"},
-                {"Index": 1, "Name": "Args", "Type": "TExprList"}
+                {"Index": 1, "Name": "Args", "Type": "TExprList"},
+                {"Index": 2, "Name": "KernelName", "Type": "TCoAtom"}
             ]
         },
         {
diff --git a/ydb/core/kqp/host/kqp_type_ann.cpp b/ydb/core/kqp/host/kqp_type_ann.cpp
index 35dcaf1e3df..d3d8920615b 100644
--- a/ydb/core/kqp/host/kqp_type_ann.cpp
+++ b/ydb/core/kqp/host/kqp_type_ann.cpp
@@ -1153,7 +1153,7 @@ TStatus AnnotateOlapApplyColumnArg(const TExprNode::TPtr& node, TExprContext& ct
 }
 
 TStatus AnnotateOlapApply(const TExprNode::TPtr& node, TExprContext& ctx) {
-    if (!EnsureArgsCount(*node, 2U, ctx)) {
+    if (!EnsureArgsCount(*node, 3U, ctx)) {
         return TStatus::Error;
     }
 
@@ -1177,6 +1177,10 @@ TStatus AnnotateOlapApply(const TExprNode::TPtr& node, TExprContext& ctx) {
         return TStatus::Repeat;
     }
 
+    if (!EnsureAtom(*node->Child(TKqpOlapApply::idx_KernelName), ctx)) {
+        return TStatus::Error;
+    }
+
     node->SetTypeAnn(lambda->GetTypeAnn());
     return TStatus::Ok;
 }
diff --git a/ydb/core/kqp/opt/physical/kqp_opt_phy_olap_filter.cpp b/ydb/core/kqp/opt/physical/kqp_opt_phy_olap_filter.cpp
index 3f0f1abd6dd..aabb9e7e914 100644
--- a/ydb/core/kqp/opt/physical/kqp_opt_phy_olap_filter.cpp
+++ b/ydb/core/kqp/opt/physical/kqp_opt_phy_olap_filter.cpp
@@ -253,6 +253,7 @@ TMaybeNode<TExprBase> YqlApplyPushdown(const TExprBase& apply, const TExprNode&
     return Build<TKqpOlapApply>(ctx, apply.Pos())
         .Lambda(ctx.NewLambda(apply.Pos(), ctx.NewArguments(argument.Pos(), std::move(lambdaArgs)), ctx.ReplaceNodes(apply.Ptr(), replacements)))
         .Args().Add(std::move(realArgs)).Build()
+        .KernelName(ctx.NewAtom(apply.Pos(), ""))    
         .Done();
 }
 
@@ -466,6 +467,32 @@ TExprBase BuildOneElementComparison(const std::pair<TExprBase, TExprBase>& param
             .Done();
     }
 
+    if (const auto* stringUdfFunction = IgnoreCaseSubstringMatchFunctions.FindPtr(predicate.CallableName())) {
+        const auto& leftArg = ctx.NewArgument(pos, "left");
+        const auto& rightArg = ctx.NewArgument(pos, "right");
+
+        const auto& callUdfLambda = ctx.NewLambda(pos, ctx.NewArguments(pos, {leftArg, rightArg}),
+            ctx.Builder(pos)
+                .Callable("Apply")
+                    .Callable(0, "Udf")
+                        .Atom(0, *stringUdfFunction)
+                    .Seal()
+                    .Add(1, leftArg)
+                    .Add(2, rightArg)
+                .Seal()
+            .Build()
+        );
+
+        return Build<TKqpOlapApply>(ctx, pos)
+            .Lambda(callUdfLambda)
+            .Args()
+                .Add(parameter.first)
+                .Add(parameter.second)
+            .Build()
+            .KernelName(ctx.NewAtom(pos, *stringUdfFunction))
+        .Done();
+    }
+
     std::string compareOperator = "";
 
     if (predicate.Maybe<TCoCmpEqual>()) {
diff --git a/ydb/core/kqp/opt/physical/predicate_collector.cpp b/ydb/core/kqp/opt/physical/predicate_collector.cpp
index b9d3b562720..0479d5be8a6 100644
--- a/ydb/core/kqp/opt/physical/predicate_collector.cpp
+++ b/ydb/core/kqp/opt/physical/predicate_collector.cpp
@@ -9,6 +9,13 @@ namespace NKikimr::NKqp::NOpt {
 using namespace NYql;
 using namespace NYql::NNodes;
 
+THashMap<TString, TString> IgnoreCaseSubstringMatchFunctions = {
+    {"EqualsIgnoreCase", "String.AsciiEqualsIgnoreCase"},
+    {"StartsWithIgnoreCase", "String.AsciiStartsWithIgnoreCase"},
+    {"EndsWithIgnoreCase", "String.AsciiEndsWithIgnoreCase"},
+    {"StringContainsIgnoreCase", "String.AsciiContainsIgnoreCase"}
+};
+
 namespace {
 
 bool IsSupportedPredicate(const TCoCompare& predicate) {
@@ -298,6 +305,18 @@ bool CheckComparisonParametersForPushdown(const TCoCompare& compare, const TExpr
         }
     }
 
+    if (options.PushdownSubstring) { //EnableSimpleIlikePushdown FF
+        if (IgnoreCaseSubstringMatchFunctions.contains(compare.CallableName())) {
+            const auto& right = compare.Right().Ref();
+            YQL_ENSURE(right.IsCallable("String") || right.IsCallable("Utf8"));
+            const auto pattern = right.Child(0);
+            YQL_ENSURE(pattern->IsAtom());
+            if (UTF8Detect(pattern->Content()) != ASCII) {
+                return false;
+            }
+        }
+    }
+
     return true;
 }
 
diff --git a/ydb/core/kqp/opt/physical/predicate_collector.h b/ydb/core/kqp/opt/physical/predicate_collector.h
index f4d48de2492..a10bef041d9 100644
--- a/ydb/core/kqp/opt/physical/predicate_collector.h
+++ b/ydb/core/kqp/opt/physical/predicate_collector.h
@@ -21,6 +21,8 @@ struct TPushdownOptions {
     bool PushdownSubstring;
 };
 
+extern THashMap<TString, TString> IgnoreCaseSubstringMatchFunctions;
+
 void CollectPredicates(const NNodes::TExprBase& predicate, TOLAPPredicateNode& predicateTree, const TExprNode* lambdaArg, const NNodes::TExprBase& lambdaBody, const TPushdownOptions& options);
 
 }
diff --git a/ydb/core/kqp/query_compiler/kqp_olap_compiler.cpp b/ydb/core/kqp/query_compiler/kqp_olap_compiler.cpp
index bbd0fac714d..4aab71c32e0 100644
--- a/ydb/core/kqp/query_compiler/kqp_olap_compiler.cpp
+++ b/ydb/core/kqp/query_compiler/kqp_olap_compiler.cpp
@@ -608,6 +608,7 @@ TTypedColumn CompileYqlKernelScalarApply(const TKqpOlapApply& apply, TKqpOlapCom
     const auto idx = ctx.GetKernelRequestBuilder().AddScalarApply(apply.Lambda().Ref(), argTypes, ctx.ExprCtx());
     function->SetKernelIdx(idx);
     function->SetFunctionType(TProgram::YQL_KERNEL);
+    function->SetKernelName(apply.KernelName().StringValue());
     std::for_each(ids.cbegin(), ids.cend(), [function] (ui64 id) { function->AddArguments()->SetId(id); });
     return {command->GetColumn().GetId(), ctx.ExprCtx().MakeType<TBlockExprType>(apply.Lambda().Body().Ref().GetTypeAnn())};
 }
diff --git a/ydb/core/kqp/ut/olap/combinatory/select.cpp b/ydb/core/kqp/ut/olap/combinatory/select.cpp
index af25cfcf130..d018f501e55 100644
--- a/ydb/core/kqp/ut/olap/combinatory/select.cpp
+++ b/ydb/core/kqp/ut/olap/combinatory/select.cpp
@@ -16,9 +16,10 @@ TConclusionStatus TSelectCommand::DoExecute(TKikimrRunner& kikimr) {
     const i64 headerApproveStart = controller->GetHeadersApprovedOnSelect().Val();
     const i64 headerNoDataStart = controller->GetHeadersSkippedNoData().Val();
 
-    Cerr << "EXECUTE: " << Command << Endl;
+    const auto command = "PRAGMA OptimizeSimpleILIKE; PRAGMA AnsiLIke;" + Command;
+    Cerr << "EXECUTE: " << command << Endl;
     auto session = kikimr.GetTableClient().CreateSession().GetValueSync().GetSession();
-    auto it = kikimr.GetQueryClient().StreamExecuteQuery(Command, NYdb::NQuery::TTxControl::BeginTx().CommitTx()).ExtractValueSync();
+    auto it = kikimr.GetQueryClient().StreamExecuteQuery(command, NYdb::NQuery::TTxControl::BeginTx().CommitTx()).ExtractValueSync();
     UNIT_ASSERT_VALUES_EQUAL_C(it.GetStatus(), NYdb::EStatus::SUCCESS, it.GetIssues().ToString());
     TString output = StreamResultToYson(it);
     if (Compare) {
diff --git a/ydb/core/kqp/ut/olap/json_ut.cpp b/ydb/core/kqp/ut/olap/json_ut.cpp
index 25e14785299..3693771ea97 100644
--- a/ydb/core/kqp/ut/olap/json_ut.cpp
+++ b/ydb/core/kqp/ut/olap/json_ut.cpp
@@ -627,22 +627,22 @@ Y_UNIT_TEST_SUITE(KqpOlapJson) {
             READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") like "%1b4%" ORDER BY Col1;
             EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
             IDX_ND_SKIP_APPROVE: 0, 4, 1
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1b4%" ORDER BY Col1;
-#            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
-#            IDX_ND_SKIP_APPROVE: 0, 4, 1
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1B4" ORDER BY Col1;
-#            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
-#            IDX_ND_SKIP_APPROVE: 0, 4, 1
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "1b5" ORDER BY Col1;
-#            EXPECTED: []
-#            IDX_ND_SKIP_APPROVE: 0, 5, 0
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.a") = "1b5" ORDER BY Col1;
-#            EXPECTED: []
-#            IDX_ND_SKIP_APPROVE: 0, 5, 0
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1b4%" ORDER BY Col1;
+            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
+            IDX_ND_SKIP_APPROVE: 0, 4, 1
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1B4" ORDER BY Col1;
+            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
+            IDX_ND_SKIP_APPROVE: 0, 4, 1
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "1b5" ORDER BY Col1;
+            EXPECTED: []
+            IDX_ND_SKIP_APPROVE: 0, 5, 0
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.a") = "1b5" ORDER BY Col1;
+            EXPECTED: []
+            IDX_ND_SKIP_APPROVE: 0, 5, 0
             ------
             READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.a") = "a4" ORDER BY Col1;
             EXPECTED: [[4u;["{\"a\":\"a4\",\"b.c.d\":\"b4\"}"]];[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
@@ -794,18 +794,18 @@ Y_UNIT_TEST_SUITE(KqpOlapJson) {
             READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") like "%1b4%" ORDER BY Col1;
             EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
             IDX_ND_SKIP_APPROVE: 0, 4, 1
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1b4%" ORDER BY Col1;
-#            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
-#            IDX_ND_SKIP_APPROVE: 0, 4, 1
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1B4" ORDER BY Col1;
-#            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
-#            IDX_ND_SKIP_APPROVE: 0, 4, 1
-#            ------
-#            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "1b5" ORDER BY Col1;
-#            EXPECTED: []
-#            IDX_ND_SKIP_APPROVE: 0, 5, 0
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1b4%" ORDER BY Col1;
+            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
+            IDX_ND_SKIP_APPROVE: 0, 4, 1
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "%1B4" ORDER BY Col1;
+            EXPECTED: [[14u;["{\"a\":\"a4\",\"b.c.d\":\"1b4\"}"]]]
+            IDX_ND_SKIP_APPROVE: 0, 4, 1
+            ------
+            READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.\"b.c.d\"") ilike "1b5" ORDER BY Col1;
+            EXPECTED: []
+            IDX_ND_SKIP_APPROVE: 0, 5, 0
             ------
             READ: SELECT * FROM `/Root/ColumnTable` WHERE JSON_VALUE(Col2, "$.a") = "1b5" ORDER BY Col1;
             EXPECTED: []
diff --git a/ydb/core/tx/program/builder.cpp b/ydb/core/tx/program/builder.cpp
index 144a1b2bfe8..9c169da4dcc 100644
--- a/ydb/core/tx/program/builder.cpp
+++ b/ydb/core/tx/program/builder.cpp
@@ -20,8 +20,20 @@ namespace NKikimr::NArrow::NSSA {
 TConclusion<std::shared_ptr<IStepFunction>> TProgramBuilder::MakeFunction(const TColumnInfo& name,
     const NKikimrSSA::TProgram::TAssignment::TFunction& func, std::shared_ptr<NArrow::NSSA::IKernelLogic>& kernelLogic,
     std::vector<TColumnChainInfo>& arguments) const {
-    if (func.GetKernelName()) {
-        kernelLogic.reset(IKernelLogic::TFactory::Construct(func.GetKernelName()));
+    if (const auto& kernelName = func.GetKernelName(); !kernelName.empty()) {
+        if (kernelName == "String.AsciiEqualsIgnoreCase") {
+            kernelLogic = std::make_shared<TLogicMatchString>(TIndexCheckOperation::EOperation::Contains, false, false);
+        } else if (kernelName == "String.AsciiContainsIgnoreCase") {
+            kernelLogic = std::make_shared<TLogicMatchString>(TIndexCheckOperation::EOperation::Contains, false, false);
+        } else if (kernelName == "String.AsciiContainsIgnoreCase") {
+            kernelLogic = std::make_shared<TLogicMatchString>(TIndexCheckOperation::EOperation::Contains, false, false);
+        } else if (kernelName == "String.AsciiStartsWithIgnoreCase") {
+            kernelLogic = std::make_shared<TLogicMatchString>(TIndexCheckOperation::EOperation::StartsWith, false, false);
+        } else if (kernelName == "String.AsciiEndsWithIgnoreCase") {
+            kernelLogic = std::make_shared<TLogicMatchString>(TIndexCheckOperation::EOperation::EndsWith, false, false);
+        } else {
+            kernelLogic.reset(IKernelLogic::TFactory::Construct(kernelName));
+        }
     } else if (func.HasYqlOperationId()) {
         kernelLogic = std::make_shared<TSimpleKernelLogic>(func.GetYqlOperationId());
     } else {