Primitives for case insensitive simple pattern match

commit_hash:5f4bdb090c2f60459073e3e95ccd39ec58b95232

15 files changed, 266 insertions, 13 deletions

diff --git a/yql/essentials/core/expr_nodes/yql_expr_nodes.json b/yql/essentials/core/expr_nodes/yql_expr_nodes.json
index c051517eee9..e0b08030e94 100644
--- a/yql/essentials/core/expr_nodes/yql_expr_nodes.json
+++ b/yql/essentials/core/expr_nodes/yql_expr_nodes.json
@@ -1281,6 +1281,11 @@
             "Match": {"Type": "Callable", "Name": "=="}
         },
         {
+            "Name": "TCoCmpEqualsIgnoreCase",
+            "Base": "TCoCompare",
+            "Match": {"Type": "Callable", "Name": "EqualsIgnoreCase"}
+        },
+        {
             "Name": "TCoCmpNotEqual",
             "Base": "TCoCompare",
             "Match": {"Type": "Callable", "Name": "!="}
@@ -1301,16 +1306,32 @@
             "Match": {"Type": "Callable", "Name": "StartsWith"}
         },
         {
+            "Name": "TCoCmpStartsWithIgnoreCase",
+            "Base": "TCoCompare",
+            "Match": {"Type": "Callable", "Name": "StartsWithIgnoreCase"}
+        },
+        {
             "Name": "TCoCmpEndsWith",
             "Base": "TCoCompare",
             "Match": {"Type": "Callable", "Name": "EndsWith"}
         },
         {
+            "Name": "TCoCmpEndsWithIgnoreCase",
+            "Base": "TCoCompare",
+            "Match": {"Type": "Callable", "Name": "EndsWithIgnoreCase"}
+        },
+
+        {
             "Name": "TCoCmpStringContains",
             "Base": "TCoCompare",
             "Match": {"Type": "Callable", "Name": "StringContains"}
         },
         {
+            "Name": "TCoCmpStringContainsIgnoreCase",
+            "Base": "TCoCompare",
+            "Match": {"Type": "Callable", "Name": "StringContainsIgnoreCase"}
+        },
+        {
             "Name": "TCoInc",
             "Base": "TCallable",
             "Match": {"Type": "Callable", "Name": "Inc"},
diff --git a/yql/essentials/core/peephole_opt/yql_opt_peephole_physical.cpp b/yql/essentials/core/peephole_opt/yql_opt_peephole_physical.cpp
index ef78839b26d..2b64e4e0bd1 100644
--- a/yql/essentials/core/peephole_opt/yql_opt_peephole_physical.cpp
+++ b/yql/essentials/core/peephole_opt/yql_opt_peephole_physical.cpp
@@ -8620,6 +8620,69 @@ TExprNode::TPtr ExpandSqlCompare(const TExprNode::TPtr& node, TExprContext& ctx)
 
     return node;
 }
+TExprNode::TPtr ExpandContainsIgnoreCase(const TExprNode::TPtr& node, TExprContext& ctx) {
+    YQL_CLOG(DEBUG, CorePeepHole) << "Expand " << node->Content();
+    const TString part{node->Child(1)->Child(0)->Content()};
+    TString pattern;
+    if (node->Content() == "EqualsIgnoreCase") {
+        pattern = part;
+    } else if (node->Content() == "StartsWithIgnoreCase") {
+        pattern = part + "%";
+    } else if (node->Content() == "EndsWithIgnoreCase") {
+        pattern = "%" + part;
+    } else if (node->Content() == "StringContainsIgnoreCase") {
+        pattern = "%" + part + "%";
+    } else {
+        YQL_ENSURE(!"Unknown IngoreCase node");
+    }
+    const auto pos = node->Pos();
+    auto patternExpr = ctx.Builder(pos)
+        .Callable("Apply")
+            .Callable(0, "Udf")
+                .Atom(0, "Re2.PatternFromLike")
+            .Seal()
+            .Callable(1, node->Child(1)->Content())
+                .Atom(0, pattern)
+            .Seal()
+        .Seal()
+    .Build();
+
+
+    auto optionsExpr = ctx.Builder(pos)
+        .Callable("NamedApply")
+            .Callable(0, "Udf")
+                .Atom(0, "Re2.Options")
+            .Seal()
+            .List(1)
+            .Seal()
+            .Callable(2, "AsStruct")
+                .List(0)
+                    .Atom(0, "CaseSensitive")
+                    .Callable(1, "Bool")
+                        .Atom(0, "false", TNodeFlags::Default)
+                    .Seal()
+                .Seal()
+            .Seal()
+        .Seal()
+    .Build();
+
+    auto result = ctx.Builder(pos)
+        .Callable("Apply")
+            .Callable(0, "AssumeStrict")
+                .Callable(0, "Udf")
+                    .Atom(0, "Re2.Match")
+                    .List(1)
+                        .Add(0, patternExpr)
+                        .Add(1, optionsExpr)
+                    .Seal()
+                .Seal()
+            .Seal()
+            .Add(1, node->Child(0))
+        .Seal()
+    .Build();
+
+    return result;
+}
 
 template <bool Equals>
 TExprNode::TPtr ExpandAggrEqual(const TExprNode::TPtr& node, TExprContext& ctx) {
@@ -8944,6 +9007,10 @@ struct TPeepHoleRules {
         {"EmptyIterator", &DropDependsOnFromEmptyIterator},
         {"Version", &ExpandVersion},
         {RightName, &ExpandRightOverCons},
+        {"EqualsIgnoreCase", &ExpandContainsIgnoreCase},
+        {"StartsWithIgnoreCase", &ExpandContainsIgnoreCase},
+        {"EndsWithIgnoreCase", &ExpandContainsIgnoreCase},
+        {"StringContainsIgnoreCase", &ExpandContainsIgnoreCase},
     };
 
     const TExtPeepHoleOptimizerMap CommonStageExtRules = {
diff --git a/yql/essentials/core/type_ann/type_ann_core.cpp b/yql/essentials/core/type_ann/type_ann_core.cpp
index a4a780a71af..e97b1690c4a 100644
--- a/yql/essentials/core/type_ann/type_ann_core.cpp
+++ b/yql/essentials/core/type_ann/type_ann_core.cpp
@@ -3323,7 +3323,9 @@ namespace NTypeAnnImpl {
             return IGraphTransformer::TStatus::Error;
         }
 
-        if (IsNull(input->Head()) || IsNull(input->Tail())) {
+        const auto ignoreNulls = input->Content().ends_with("IgnoreCase");
+
+        if ((IsNull(input->Head()) || IsNull(input->Tail())) && !ignoreNulls) {
             output = MakeBoolNothing(input->Pos(), ctx.Expr);
             return IGraphTransformer::TStatus::Repeat;
         }
@@ -3343,8 +3345,11 @@ namespace NTypeAnnImpl {
             }
             bool isOptional = false;
             const TDataExprType* dataType = nullptr;
-            if (!IsDataOrOptionalOfData(type, isOptional, dataType) ||
-                !(dataType->GetSlot() == EDataSlot::String || dataType->GetSlot() == EDataSlot::Utf8))
+            if ((!IsDataOrOptionalOfData(type, isOptional, dataType) ||
+                !(dataType->GetSlot() == EDataSlot::String || dataType->GetSlot() == EDataSlot::Utf8) ||
+                dataType->IsOptionalOrNull()) &&
+                (!IsNull(*type) && ignoreNulls)
+            )
             {
                 ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(child->Pos()), TStringBuilder()
                     << "Expected (optional) string/utf8 or corresponding Pg type, but got: " << *child->GetTypeAnn()));
@@ -3352,8 +3357,7 @@ namespace NTypeAnnImpl {
             }
             hasOptionals = hasOptionals || isOptional;
         }
-
-        if (hasOptionals)
+        if (hasOptionals && !ignoreNulls)
             input->SetTypeAnn(ctx.Expr.MakeType<TOptionalExprType>(ctx.Expr.MakeType<TDataExprType>(EDataSlot::Bool)));
         else
             input->SetTypeAnn(ctx.Expr.MakeType<TDataExprType>(EDataSlot::Bool));
@@ -12490,6 +12494,7 @@ template <NKikimr::NUdf::EDataSlot DataSlot>
         Functions["GreaterOrEqual"] = &CompareWrapper<false>;
         Functions["=="] = &CompareWrapper<true>;
         Functions["Equal"] = &CompareWrapper<true>;
+        Functions["EqualsIgnoreCase"] = &WithWrapper;
         Functions["!="] = &CompareWrapper<true>;
         Functions["NotEqual"] = &CompareWrapper<true>;
         Functions["Inc"] = &IncDecWrapper<true>;
@@ -12553,8 +12558,11 @@ template <NKikimr::NUdf::EDataSlot DataSlot>
         Functions["Find"] = &FindWrapper;
         Functions["RFind"] = &FindWrapper;
         Functions["StartsWith"] = &WithWrapper;
+        Functions["StartsWithIgnoreCase"] = &WithWrapper;
         Functions["EndsWith"] = &WithWrapper;
+        Functions["EndsWithIgnoreCase"] = &WithWrapper;
         Functions["StringContains"] = &WithWrapper;
+        Functions["StringContainsIgnoreCase"] = &WithWrapper;
         Functions["ByteAt"] = &ByteAtWrapper;
         Functions["ListIf"] = &ListIfWrapper;
         Functions["AsList"] = &AsListWrapper<false>;
diff --git a/yql/essentials/sql/v1/context.cpp b/yql/essentials/sql/v1/context.cpp
index ae313a171e1..9decaefd4e9 100644
--- a/yql/essentials/sql/v1/context.cpp
+++ b/yql/essentials/sql/v1/context.cpp
@@ -71,6 +71,7 @@ THashMap<TStringBuf, TPragmaField> CTX_PRAGMA_FIELDS = {
     {"DistinctOverKeys", &TContext::DistinctOverKeys},
     {"GroupByExprAfterWhere", &TContext::GroupByExprAfterWhere},
     {"FailOnGroupByExprOverride", &TContext::FailOnGroupByExprOverride},
+    {"OptimizeSimpleILIKE", &TContext::OptimizeSimpleIlike}
 };
 
 typedef TMaybe<bool> TContext::*TPragmaMaybeField;
diff --git a/yql/essentials/sql/v1/context.h b/yql/essentials/sql/v1/context.h
index 14314c0a314..e45a0495a2c 100644
--- a/yql/essentials/sql/v1/context.h
+++ b/yql/essentials/sql/v1/context.h
@@ -376,6 +376,7 @@ namespace NSQLTranslationV1 {
         bool GroupByExprAfterWhere = false;
         bool FailOnGroupByExprOverride = false;
         bool EmitUnionMerge = false;
+        bool OptimizeSimpleIlike = false;
         TVector<size_t> ForAllStatementsParts;
 
         TMaybe<TString> Engine;
diff --git a/yql/essentials/sql/v1/node.cpp b/yql/essentials/sql/v1/node.cpp
index e5c7d353795..fe5d4dff7b0 100644
--- a/yql/essentials/sql/v1/node.cpp
+++ b/yql/essentials/sql/v1/node.cpp
@@ -3179,7 +3179,10 @@ TNodePtr BuildBinaryOp(TContext& ctx, TPosition pos, const TString& opName, TNod
         return nullptr;
     }
 
-    static const THashSet<TStringBuf> nullSafeOps = {"IsDistinctFrom", "IsNotDistinctFrom"};
+    static const THashSet<TStringBuf> nullSafeOps = {
+        "IsDistinctFrom", "IsNotDistinctFrom",
+        "EqualsIgnoreCase", "StartsWithIgnoreCase", "EndsWithIgnoreCase", "StringContainsIgnoreCase"
+    };
     if (!nullSafeOps.contains(opName)) {
         const bool bothArgNull = a->IsNull() && b->IsNull();
         const bool oneArgNull  = a->IsNull() || b->IsNull();
diff --git a/yql/essentials/sql/v1/sql_expression.cpp b/yql/essentials/sql/v1/sql_expression.cpp
index 3eb1904cc59..4b7e31fdaa3 100644
--- a/yql/essentials/sql/v1/sql_expression.cpp
+++ b/yql/essentials/sql/v1/sql_expression.cpp
@@ -1797,19 +1797,25 @@ TNodePtr TSqlExpression::SubExpr(const TRule_xor_subexpr& node, const TTrailingQ
                             return nullptr;
                         }
 
-                        if (opName == "like" || mayIgnoreCase) {
+                        if ((opName == "like") || mayIgnoreCase || Ctx.OptimizeSimpleIlike) {
                             // TODO: expand LIKE in optimizers - we can analyze argument types there
+                            const bool useIgnoreCaseOp = (opName == "ilike") && !mayIgnoreCase;
+                            const auto& equalOp = useIgnoreCaseOp ? "EqualsIgnoreCase" : "==";
+                            const auto& startsWithOp = useIgnoreCaseOp ? "StartsWithIgnoreCase" : "StartsWith";
+                            const auto& endsWithOp = useIgnoreCaseOp ? "EndsWithIgnoreCase" : "EndsWith";
+                            const auto& containsOp = useIgnoreCaseOp ? "StringContainsIgnoreCase" : "StringContains";
+
                             YQL_ENSURE(!components.empty());
                             const auto& first = components.front();
                             if (components.size() == 1 && first.IsSimple) {
                                 // no '%'s and '_'s  in pattern
                                 YQL_ENSURE(first.Prefix == first.Suffix);
-                                isMatch = BuildBinaryOp(Ctx, pos, "==", res, BuildLiteralRawString(pos, first.Suffix, isUtf8));
+                                isMatch = BuildBinaryOp(Ctx, pos, equalOp, res, BuildLiteralRawString(pos, first.Suffix, isUtf8));
                             } else if (!first.Prefix.empty()) {
                                 const TString& prefix = first.Prefix;
                                 TNodePtr prefixMatch;
                                 if (Ctx.EmitStartsWith) {
-                                    prefixMatch = BuildBinaryOp(Ctx, pos, "StartsWith", res, BuildLiteralRawString(pos, prefix, isUtf8));
+                                    prefixMatch = BuildBinaryOp(Ctx, pos, startsWithOp, res, BuildLiteralRawString(pos, prefix, isUtf8));
                                 } else {
                                     prefixMatch = BuildBinaryOp(Ctx, pos, ">=", res, BuildLiteralRawString(pos, prefix, isUtf8));
                                     auto upperBound = isUtf8 ? NextValidUtf8(prefix) : NextLexicographicString(prefix);
@@ -1834,7 +1840,7 @@ TNodePtr TSqlExpression::SubExpr(const TRule_xor_subexpr& node, const TTrailingQ
                                         TNodePtr sizePred = BuildBinaryOp(Ctx, pos, ">=",
                                             TNodePtr(new TCallNodeImpl(pos, "Size", { res })),
                                             TNodePtr(new TLiteralNumberNode<ui32>(pos, "Uint32", ToString(prefix.size() + suffix.size()))));
-                                        TNodePtr suffixMatch = BuildBinaryOp(Ctx, pos, "EndsWith", res, BuildLiteralRawString(pos, suffix, isUtf8));
+                                        TNodePtr suffixMatch = BuildBinaryOp(Ctx, pos, endsWithOp, res, BuildLiteralRawString(pos, suffix, isUtf8));
                                         isMatch = new TCallNodeImpl(pos, "And", {
                                             sizePred,
                                             prefixMatch,
@@ -1849,14 +1855,14 @@ TNodePtr TSqlExpression::SubExpr(const TRule_xor_subexpr& node, const TTrailingQ
                                 if (components.size() == 3 && components.back().Prefix.empty()) {
                                     // '%foo%'
                                     YQL_ENSURE(!components[1].Prefix.empty());
-                                    isMatch = BuildBinaryOp(Ctx, pos, "StringContains", res, BuildLiteralRawString(pos, components[1].Prefix, isUtf8));
+                                    isMatch = BuildBinaryOp(Ctx, pos, containsOp, res, BuildLiteralRawString(pos, components[1].Prefix, isUtf8));
                                 } else if (components.size() == 2) {
                                     // '%foo'
-                                    isMatch = BuildBinaryOp(Ctx, pos, "EndsWith", res, BuildLiteralRawString(pos, components[1].Prefix, isUtf8));
+                                    isMatch = BuildBinaryOp(Ctx, pos, endsWithOp, res, BuildLiteralRawString(pos, components[1].Prefix, isUtf8));
                                 }
                             } else if (Ctx.AnsiLike && !components.back().Suffix.empty()) {
                                 const TString& suffix = components.back().Suffix;
-                                TNodePtr suffixMatch = BuildBinaryOp(Ctx, pos, "EndsWith", res, BuildLiteralRawString(pos, suffix, isUtf8));
+                                TNodePtr suffixMatch = BuildBinaryOp(Ctx, pos, endsWithOp, res, BuildLiteralRawString(pos, suffix, isUtf8));
                                 isMatch = BuildBinaryOp(Ctx, pos, "And", suffixMatch, isMatch);
                             }
                             // TODO: more StringContains/StartsWith/EndsWith cases?
diff --git a/yql/essentials/sql/v1/sql_query.cpp b/yql/essentials/sql/v1/sql_query.cpp
index c9ea302c8c7..a750d941917 100644
--- a/yql/essentials/sql/v1/sql_query.cpp
+++ b/yql/essentials/sql/v1/sql_query.cpp
@@ -3434,6 +3434,12 @@ TNodePtr TSqlQuery::PragmaStatement(const TRule_pragma_stmt& stmt, bool& success
             }
 
             Ctx.Engine = *literal;
+        } else if (normalizedPragma == "optimizesimpleilike") {
+            Ctx.OptimizeSimpleIlike = true;
+            Ctx.IncrementMonCounter("sql_pragma", "OptimizeSimpleILIKE");
+        } else if (normalizedPragma == "disableoptimizesimpleilike") {
+            Ctx.OptimizeSimpleIlike = false;
+            Ctx.IncrementMonCounter("sql_pragma", "DisableOptimizeSimpleILIKE");
         } else {
             Error() << "Unknown pragma: " << pragma;
             Ctx.IncrementMonCounter("sql_errors", "UnknownPragma");
diff --git a/yql/essentials/tests/sql/minirun/part5/canondata/result.json b/yql/essentials/tests/sql/minirun/part5/canondata/result.json
index ec17a6eec0b..1eadc4bc263 100644
--- a/yql/essentials/tests/sql/minirun/part5/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part5/canondata/result.json
@@ -1145,6 +1145,20 @@
             "uri": "https://{canondata_backend}/1942100/1466d7e49a6dc5a8df761a5ac92539095e1a14a0/resource.tar.gz#test.test_library-library_udf--Results_/results.txt"
         }
     ],
+    "test.test[like-ilike--Debug]": [
+        {
+            "checksum": "e9edc47e022f94844d6755223a748bbe",
+            "size": 5224,
+            "uri": "https://{canondata_backend}/1899731/c18b136d437186ca17b897aa3cbd89e03d6ab70e/resource.tar.gz#test.test_like-ilike--Debug_/opt.yql"
+        }
+    ],
+    "test.test[like-ilike--Results]": [
+        {
+            "checksum": "4f140efd3491b22880e294dce1c735c2",
+            "size": 9773,
+            "uri": "https://{canondata_backend}/1899731/c18b136d437186ca17b897aa3cbd89e03d6ab70e/resource.tar.gz#test.test_like-ilike--Results_/results.txt"
+        }
+    ],
     "test.test[like-like_escape-default.txt-Debug]": [
         {
             "checksum": "9a241374a2f6995712f675e514d7e64a",
diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
index 60e9fee9459..74fb98a7cc1 100644
--- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
@@ -875,6 +875,20 @@
             "uri": "file://test.test_lambda-list_aggregate_flatmap-default.txt-Results_/extracted"
         }
     ],
+    "test.test[like-ilike-Ansi-Debug]": [
+        {
+            "checksum": "2bf3a064a80a62b0d1e57b4fc19f85aa",
+            "size": 4198,
+            "uri": "https://{canondata_backend}/1871102/cf0bf303bf8ddaa5f80dc41d0b1079fd931793f8/resource.tar.gz#test.test_like-ilike-Ansi-Debug_/opt.yql"
+        }
+    ],
+    "test.test[like-ilike-Ansi-Results]": [
+        {
+            "checksum": "4f140efd3491b22880e294dce1c735c2",
+            "size": 9773,
+            "uri": "https://{canondata_backend}/1871102/cf0bf303bf8ddaa5f80dc41d0b1079fd931793f8/resource.tar.gz#test.test_like-ilike-Ansi-Results_/results.txt"
+        }
+    ],
     "test.test[match_recognize-alerts_without_order-default.txt-Debug]": [
         {
             "checksum": "617ad997b55cab0792da8c20f2fdeb07",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index dd80cad3ba4..7bb69811dcd 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -4283,6 +4283,13 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_library-library_yqls_/sql.yql"
         }
     ],
+    "test_sql2yql.test[like-ilike]": [
+        {
+            "checksum": "82ed5458d953d02cb6b225495713cd99",
+            "size": 11180,
+            "uri": "https://{canondata_backend}/1946324/f3be5399e4ba264ff20e7048ebc764e7239129dc/resource.tar.gz#test_sql2yql.test_like-ilike_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[like-like_escape]": [
         {
             "checksum": "dad96e3616b966f1a15c37b4396213a1",
@@ -10521,6 +10528,11 @@
             "uri": "file://test_sql_format.test_library-library_yqls_/formatted.sql"
         }
     ],
+    "test_sql_format.test[like-ilike]": [
+        {
+            "uri": "file://test_sql_format.test_like-ilike_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[like-like_escape]": [
         {
             "uri": "file://test_sql_format.test_like-like_escape_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_like-ilike_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_like-ilike_/formatted.sql
new file mode 100644
index 00000000000..96e89141855
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_like-ilike_/formatted.sql
@@ -0,0 +1,51 @@
+PRAGMA OptimizeSimpleILIKE;
+
+SELECT
+    'test' ILIKE 'test',
+    'TEST' ILIKE 'test',
+    'тест' ILIKE 'ТЕСТ',
+    'Привет' ILIKE 'пРиВеТ',
+    'prefix' ILIKE 'Pre%',
+    'префикс' ILIKE 'Пре%',
+    'suFfix' ILIKE '%Fix',
+    'СУФФИКС' ILIKE '%Икс',
+    'инфикс' ILIKE '%Фи%',
+    'Комплекс' ILIKE '%О%П%С',
+
+    --negative
+    'тест' ILIKE 'Тесть',
+    'Тост' ILIKE '%мост',
+    'Лось' ILIKE 'Лом%'
+;
+
+SELECT
+    NULL ILIKE 'test',
+    NULL ILIKE 'te%',
+    NULL ILIKE '%st',
+    NULL ILIKE '%es%'
+;
+
+$table = [
+    <|
+        str: 'TeSt',
+        optStr: Just('TeSt'),
+        nullStr: Nothing(String?)
+    |>
+];
+
+SELECT
+    str ILIKE 'test',
+    str ILIKE 'te%',
+    str ILIKE '%st',
+    str ILIKE '%es%',
+    optStr ILIKE 'test',
+    optStr ILIKE 'te%',
+    optStr ILIKE '%st',
+    optStr ILIKE '%es%',
+    nullStr ILIKE 'test',
+    nullStr ILIKE 'te%',
+    nullStr ILIKE '%st',
+    nullStr ILIKE '%es%'
+FROM
+    AS_TABLE($table)
+;
diff --git a/yql/essentials/tests/sql/suites/like/ilike-Ansi.cfg b/yql/essentials/tests/sql/suites/like/ilike-Ansi.cfg
new file mode 100644
index 00000000000..7dc25ec8499
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/like/ilike-Ansi.cfg
@@ -0,0 +1 @@
+pragma AnsiLike
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/like/ilike.cfg b/yql/essentials/tests/sql/suites/like/ilike.cfg
new file mode 100644
index 00000000000..e69de29bb2d
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/like/ilike.cfg
diff --git a/yql/essentials/tests/sql/suites/like/ilike.sql b/yql/essentials/tests/sql/suites/like/ilike.sql
new file mode 100644
index 00000000000..b67825e2feb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/like/ilike.sql
@@ -0,0 +1,48 @@
+PRAGMA OptimizeSimpleILIKE;
+
+SELECT
+    'test' ILIKE 'test',
+    'TEST' ILIKE 'test',
+    'тест' ILIKE 'ТЕСТ',
+    'Привет' ILIKE 'пРиВеТ',
+    'prefix' ILIKE 'Pre%',
+    'префикс' ILIKE 'Пре%',
+    'suFfix' ILIKE '%Fix',
+    'СУФФИКС' ILIKE '%Икс',
+    'инфикс' ILIKE '%Фи%',
+    'Комплекс' ILIKE '%О%П%С',
+    --negative
+    'тест' ILIKE 'Тесть',
+    'Тост' ILIKE '%мост',
+    'Лось' ILIKE 'Лом%'
+;
+
+SELECT
+    Null ILIKE 'test',
+    Null ILIKE 'te%',
+    Null ILIKE '%st',
+    Null ILIKE '%es%'
+;
+
+$table = [<|
+  str: 'TeSt',
+  optStr: Just("TeSt"),
+  nullStr: Nothing(String?)
+|>];
+
+SELECT
+    str ILIKE 'test',
+    str ILIKE 'te%',
+    str ILIKE '%st',
+    str ILIKE '%es%',
+
+    optStr ILIKE 'test',
+    optStr ILIKE 'te%',
+    optStr ILIKE '%st',
+    optStr ILIKE '%es%',
+    
+    nullStr ILIKE 'test',
+    nullStr ILIKE 'te%',
+    nullStr ILIKE '%st',
+    nullStr ILIKE '%es%'
+FROM AS_TABLE($table);
\ No newline at end of file