SQL parser + type annotation for Materialize statement

#### SQL Parser and Type Annotation for Materialize Statement ✎

- Added support for the `MATERIALIZE` SQL statement including parser rules and syntax highlighting updates
- Implemented type annotation and validation for the `MATERIALIZE` statement with proper error handling
- Enhanced SQL query processing to handle `MATERIALIZE` statements in various contexts including subqueries and limited views
- Added runtime support for `MATERIALIZE` operations with sort preservation and proper data sink handling
- Integrated `MATERIALIZE` into the SQL grammar and updated completion suggestions
- Added comprehensive unit tests covering various `MATERIALIZE` scenarios and edge cases

<a href="https://nda.ya.ru/t/qa0kX64r7DqvtN"><font size="2">Autodescription by Yandex Code Assistant</font></a>
commit_hash:32b04f93ceadf624b4d39fd6512798f9354bfd80

13 files changed, 483 insertions, 6 deletions

diff --git a/yql/essentials/sql/v1/SQLv1Antlr4.g.in b/yql/essentials/sql/v1/SQLv1Antlr4.g.in
index 173d36af13a..5ed65798cb6 100644
--- a/yql/essentials/sql/v1/SQLv1Antlr4.g.in
+++ b/yql/essentials/sql/v1/SQLv1Antlr4.g.in
@@ -87,6 +87,7 @@ sql_stmt_core:
   | alter_secret_stmt
   | drop_secret_stmt
   | truncate_table_stmt
+  | materialize_stmt
 ;
 
 expr:
@@ -449,6 +450,8 @@ combine_core:
   ON expr USING using_call_expr
 ;
 
+materialize_stmt: MATERIALIZE named_single_source (ON cluster_expr)? table_hints? INTO bind_parameter;
+
 // ISO/IEC 9075-2:2016(E) 7.7 <row pattern recognition clause>
 row_pattern_recognition_clause: MATCH_RECOGNIZE LPAREN
     window_partition_clause?
@@ -1575,6 +1578,7 @@ keyword_as_compat:
   | MATCH
   | MATCHES
   | MATCH_RECOGNIZE
+  | MATERIALIZE
   | MEASURES
   | MICROSECONDS
   | MILLISECONDS
@@ -1814,6 +1818,7 @@ keyword_compat: (
   | MATCH
   | MATCHES
   | MATCH_RECOGNIZE
+  | MATERIALIZE
   | MEASURES
   | MICROSECONDS
   | MILLISECONDS
@@ -2188,6 +2193,7 @@ MANAGE: M A N A G E;
 MATCH: M A T C H;
 MATCHES: M A T C H E S;
 MATCH_RECOGNIZE: M A T C H '_' R E C O G N I Z E;
+MATERIALIZE: M A T E R I A L I Z E;
 MEASURES: M E A S U R E S;
 MICROSECONDS: M I C R O S E C O N D S;
 MILLISECONDS: M I L L I S E C O N D S;
diff --git a/yql/essentials/sql/v1/format/sql_format.cpp b/yql/essentials/sql/v1/format/sql_format.cpp
index d2f5a488849..3d6ff416ddd 100644
--- a/yql/essentials/sql/v1/format/sql_format.cpp
+++ b/yql/essentials/sql/v1/format/sql_format.cpp
@@ -1127,6 +1127,11 @@ private:
         VisitAllFields(TRule_truncate_table_stmt::GetDescriptor(), msg);
     }
 
+    void VisitMaterialize(const TRule_materialize_stmt& msg) {
+        NewLine();
+        VisitAllFields(TRule_materialize_stmt::GetDescriptor(), msg);
+    }
+
     void VisitCreateTable(const TRule_create_table_stmt& msg) {
         NewLine();
         Visit(msg.GetToken1());
@@ -3406,6 +3411,7 @@ TStaticData::TStaticData()
           {TRule_alter_sequence_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitAlterSequence)},
           {TRule_alter_database_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitAlterDatabase)},
           {TRule_truncate_table_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitTruncateTable)},
+          {TRule_materialize_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitMaterialize)},
           {TRule_show_create_table_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitShowCreateTable)},
           {TRule_streaming_query_settings::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitStreamingQuerySettings)},
           {TRule_create_streaming_query_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitCreateStreamingQuery)},
diff --git a/yql/essentials/sql/v1/format/sql_format_ut.h b/yql/essentials/sql/v1/format/sql_format_ut.h
index 285106275c4..72d8e223847 100644
--- a/yql/essentials/sql/v1/format/sql_format_ut.h
+++ b/yql/essentials/sql/v1/format/sql_format_ut.h
@@ -56,6 +56,25 @@ Y_UNIT_TEST(TruncateTable) {
     setup.Run(cases);
 }
 
+Y_UNIT_TEST(Materialize) {
+    TCases cases{
+        {"use plato;materialize Input into $result;",
+         "USE plato;\n\nMATERIALIZE Input INTO $result;\n"},
+
+        {"materialize plato.Input on plato into $result;",
+         "MATERIALIZE plato.Input ON plato INTO $result;\n"},
+
+        {"use plato;materialize Input into $result;select * from $result;",
+         "USE plato;\n\nMATERIALIZE Input INTO $result;\n\nSELECT\n\t*\nFROM\n\t$result\n;\n"},
+
+        {"materialize (select * from plato.Input) on plato into $result;",
+         "MATERIALIZE (\n\tSELECT\n\t\t*\n\tFROM\n\t\tplato.Input\n) ON plato INTO $result;\n"},
+    };
+
+    TSetup setup;
+    setup.Run(cases);
+}
+
 Y_UNIT_TEST(GrantPermissions) {
     TCases cases{
         {"use plato;grant connect, modify tables, list on `/Root` to user;", "USE plato;\n\nGRANT CONNECT, MODIFY TABLES, LIST ON `/Root` TO user;\n"},
diff --git a/yql/essentials/sql/v1/ide/completion/sql_complete_ut.cpp b/yql/essentials/sql/v1/ide/completion/sql_complete_ut.cpp
index 6cb455ace11..092f7803a5e 100644
--- a/yql/essentials/sql/v1/ide/completion/sql_complete_ut.cpp
+++ b/yql/essentials/sql/v1/ide/completion/sql_complete_ut.cpp
@@ -206,6 +206,7 @@ Y_UNIT_TEST(Beginning) {
         {.Kind = Keyword, .Content = "IF"},
         {.Kind = Keyword, .Content = "IMPORT"},
         {.Kind = Keyword, .Content = "INSERT"},
+        {.Kind = Keyword, .Content = "MATERIALIZE"},
         {.Kind = Keyword, .Content = "PARALLEL"},
         {.Kind = Keyword, .Content = "PRAGMA"},
         {.Kind = Keyword, .Content = "PROCESS"},
@@ -430,6 +431,7 @@ Y_UNIT_TEST(Explain) {
         {.Kind = Keyword, .Content = "IF"},
         {.Kind = Keyword, .Content = "IMPORT"},
         {.Kind = Keyword, .Content = "INSERT"},
+        {.Kind = Keyword, .Content = "MATERIALIZE"},
         {.Kind = Keyword, .Content = "PARALLEL"},
         {.Kind = Keyword, .Content = "PRAGMA"},
         {.Kind = Keyword, .Content = "PROCESS"},
diff --git a/yql/essentials/sql/v1/node.cpp b/yql/essentials/sql/v1/node.cpp
index b8a892f9d11..ce843800e57 100644
--- a/yql/essentials/sql/v1/node.cpp
+++ b/yql/essentials/sql/v1/node.cpp
@@ -312,6 +312,10 @@ void INode::DisableSort() {
     DisableSort_ = true;
 }
 
+void INode::PreserveSort() {
+    PreserveSort_ = true;
+}
+
 bool INode::UsedSubquery() const {
     return false;
 }
diff --git a/yql/essentials/sql/v1/node.h b/yql/essentials/sql/v1/node.h
index 59abd7763f9..46a330b7f4b 100644
--- a/yql/essentials/sql/v1/node.h
+++ b/yql/essentials/sql/v1/node.h
@@ -204,6 +204,7 @@ public:
     virtual bool SetYqlSelectWindowName(TContext& ctx, TString name);
     void UseAsInner();
     void DisableSort();
+    void PreserveSort();
     virtual bool UsedSubquery() const;
     virtual bool IsSelect() const;
     virtual bool HasSelectResult() const;
@@ -301,6 +302,7 @@ protected:
     mutable TNodeState State_;
     bool AsInner_ = false;
     bool DisableSort_ = false;
+    bool PreserveSort_ = false;
 };
 using TNodePtr = INode::TPtr;
 
@@ -1750,6 +1752,7 @@ TNodePtr BuildAlterTransfer(TPosition pos, const TString& id, std::optional<TStr
                             const TObjectOperatorContext& context);
 TNodePtr BuildDropTransfer(TPosition pos, const TString& id, bool cascade, const TObjectOperatorContext& context);
 TNodePtr BuildWriteResult(TPosition pos, const TString& label, TNodePtr settings);
+TNodePtr BuildMaterialize(TPosition pos, TSourcePtr source, const TString& serviceId, TNodePtr cluster, TTableHints hints, TString alias, TScopedStatePtr scoped);
 TNodePtr BuildCommitClusters(TPosition pos);
 TNodePtr BuildRollbackClusters(TPosition pos);
 TNodePtr BuildQuery(TPosition pos, const TVector<TNodePtr>& blocks, bool topLevel, TScopedStatePtr scoped, bool useSeq);
diff --git a/yql/essentials/sql/v1/select.cpp b/yql/essentials/sql/v1/select.cpp
index 8219a8d8fa4..e1fa0fbf3d7 100644
--- a/yql/essentials/sql/v1/select.cpp
+++ b/yql/essentials/sql/v1/select.cpp
@@ -38,6 +38,9 @@ public:
     bool DoInit(TContext& ctx, ISource* src) override {
         YQL_ENSURE(!src, "Source not expected for subquery node");
         Source_->UseAsInner();
+        if (PreserveSort_) {
+            Source_->PreserveSort();
+        }
         if (!Source_->Init(ctx, nullptr)) {
             return false;
         }
@@ -189,6 +192,9 @@ public:
         if (AsInner_) {
             Source_->UseAsInner();
         }
+        if (PreserveSort_) {
+            Source_->PreserveSort();
+        }
 
         if (!Source_->Init(ctx, src)) {
             return false;
@@ -702,6 +708,9 @@ public:
     bool DoInit(TContext& ctx, ISource* src) override {
         // independent subquery should not connect source
         Subquery_->UseAsInner();
+        if (PreserveSort_) {
+            Subquery_->PreserveSort();
+        }
         if (!Subquery_->Init(ctx, nullptr)) {
             return false;
         }
@@ -845,6 +854,96 @@ bool IsYqlSubqueryRef(const TNodePtr& source) {
     return dynamic_cast<const TYqlSubqueryRefNode*>(source.Get()) != nullptr;
 }
 
+class TMaterializeNode: public INode {
+public:
+    TMaterializeNode(TPosition pos, TSourcePtr source, TString service, TNodePtr cluster, TTableHints hints, TString alias, TScopedStatePtr scoped)
+        : INode(pos)
+        , Source_(std::move(source))
+        , Service_(std::move(service))
+        , ClusterNode_(std::move(cluster))
+        , Hints_(std::move(hints))
+        , Alias_(std::move(alias))
+        , Scoped_(std::move(scoped))
+    {
+    }
+
+    bool DoInit(TContext& ctx, ISource* src) override {
+        Y_UNUSED(src);
+
+        if (!ctx.EnsureAvailable(GetPos(), NYql::NFeature::Materialize)) {
+            return false;
+        }
+
+        Source_->UseAsInner();
+        Source_->PreserveSort();
+
+        if (!Source_->Init(ctx, nullptr)) {
+            return false;
+        }
+
+        TTableList tableList;
+        Source_->GetInputTables(tableList);
+
+        auto tables = BuildInputTables(Pos_, tableList, false, Scoped_);
+        if (!tables->Init(ctx, Source_.Get())) {
+            return false;
+        }
+
+        auto sourceData = Source_->Build(ctx);
+        if (!sourceData) {
+            return false;
+        }
+
+        if (!ClusterNode_->Init(ctx, nullptr)) {
+            return false;
+        }
+
+        auto datasink = Y("DataSink", BuildQuotedAtom(Pos_, Service_), ClusterNode_);
+
+        TNodePtr options = BuildInputOptions(Pos_, Hints_);
+        if (!options) {
+            options = Q(Y());
+        }
+
+        Node_ = Y("let", Alias_, Y("block", Q(L(tables, Y("return", Y("Materialize!", "world", datasink, sourceData, options))))));
+        IsUsed_ = true;
+
+        return true;
+    }
+
+    TAstNode* Translate(TContext& ctx) const final {
+        return Node_->Translate(ctx);
+    }
+
+    TNodePtr DoClone() const final {
+        return new TMaterializeNode(GetPos(), Source_->CloneSource(), Service_, ClusterNode_->Clone(), Hints_, Alias_, Scoped_);
+    }
+
+    // Is used at the TYqlProgramNode
+    const TString* SubqueryAlias() const final {
+        return &Alias_;
+    }
+
+    // Is used at the TYqlProgramNode
+    bool UsedSubquery() const final {
+        return IsUsed_;
+    }
+
+private:
+    TSourcePtr Source_;
+    TString Service_;
+    TNodePtr ClusterNode_;
+    TTableHints Hints_;
+    TString Alias_;
+    TScopedStatePtr Scoped_;
+    bool IsUsed_ = false;
+    TNodePtr Node_;
+};
+
+TNodePtr BuildMaterialize(TPosition pos, TSourcePtr source, const TString& serviceId, TNodePtr cluster, TTableHints hints, TString alias, TScopedStatePtr scoped) {
+    return new TMaterializeNode(pos, std::move(source), serviceId, std::move(cluster), std::move(hints), std::move(alias), std::move(scoped));
+}
+
 class TInvalidSubqueryRefNode: public ISource {
 public:
     explicit TInvalidSubqueryRefNode(TPosition pos)
@@ -1072,6 +1171,9 @@ public:
         source->SetLabel(Label_);
         if (!NewSource_) {
             Node_->UseAsInner();
+            if (PreserveSort_) {
+                Node_->PreserveSort();
+            }
             if (!Node_->Init(ctx, nullptr)) {
                 return false;
             }
@@ -3344,7 +3446,7 @@ public:
 
 protected:
     bool IgnoreSort() const {
-        return AsInner_ && !SkipTake_ && EOrderKind::Sort == Source_->GetOrderKind();
+        return AsInner_ && !PreserveSort_ && !SkipTake_ && EOrderKind::Sort == Source_->GetOrderKind();
     }
 
     TSourcePtr Source_;
diff --git a/yql/essentials/sql/v1/sql.cpp b/yql/essentials/sql/v1/sql.cpp
index 75d930eea23..a3a5f714cac 100644
--- a/yql/essentials/sql/v1/sql.cpp
+++ b/yql/essentials/sql/v1/sql.cpp
@@ -192,6 +192,7 @@ bool NeedUseForAllStatements(const TRule_sql_stmt_core::AltCase& subquery) {
         case TRule_sql_stmt_core::kAltSqlStmtCore67: // alter secret
         case TRule_sql_stmt_core::kAltSqlStmtCore68: // drop secret
         case TRule_sql_stmt_core::kAltSqlStmtCore69: // truncate table
+        case TRule_sql_stmt_core::kAltSqlStmtCore70: // materialize
             return false;
         case TRule_sql_stmt_core::ALT_NOT_SET:
             YQL_ENSURE(false, "Unreachable");
diff --git a/yql/essentials/sql/v1/sql_query.cpp b/yql/essentials/sql/v1/sql_query.cpp
index d6bbfc83d45..109e9dc35c3 100644
--- a/yql/essentials/sql/v1/sql_query.cpp
+++ b/yql/essentials/sql/v1/sql_query.cpp
@@ -294,14 +294,18 @@ bool TSqlQuery::Statement(TVector<TNodePtr>& blocks, const TRule_sql_stmt_core&
 
     const auto& altCase = core.Alt_case();
     if (Mode_ == NSQLTranslation::ESqlMode::LIMITED_VIEW && (altCase >= TRule_sql_stmt_core::kAltSqlStmtCore4 &&
-                                                             altCase != TRule_sql_stmt_core::kAltSqlStmtCore13 && altCase != TRule_sql_stmt_core::kAltSqlStmtCore18)) {
+                                                             altCase != TRule_sql_stmt_core::kAltSqlStmtCore13 &&  // import
+                                                             altCase != TRule_sql_stmt_core::kAltSqlStmtCore18 &&  // define_action_or_subquery
+                                                             altCase != TRule_sql_stmt_core::kAltSqlStmtCore70)) { // materialize
         Error() << statementName.Human << " statement is not supported in limited views";
         return false;
     }
 
     if (Mode_ == NSQLTranslation::ESqlMode::SUBQUERY && (altCase >= TRule_sql_stmt_core::kAltSqlStmtCore4 &&
-                                                         altCase != TRule_sql_stmt_core::kAltSqlStmtCore13 && altCase != TRule_sql_stmt_core::kAltSqlStmtCore6 &&
-                                                         altCase != TRule_sql_stmt_core::kAltSqlStmtCore18)) {
+                                                         altCase != TRule_sql_stmt_core::kAltSqlStmtCore13 &&  // import
+                                                         altCase != TRule_sql_stmt_core::kAltSqlStmtCore6 &&   // use
+                                                         altCase != TRule_sql_stmt_core::kAltSqlStmtCore18 &&  // define_action_or_subquery
+                                                         altCase != TRule_sql_stmt_core::kAltSqlStmtCore70)) { // materialize
         Error() << statementName.Human << " statement is not supported in subqueries";
         return false;
     }
@@ -2350,6 +2354,53 @@ bool TSqlQuery::Statement(TVector<TNodePtr>& blocks, const TRule_sql_stmt_core&
             AddStatementToBlocks(blocks, BuildTruncateTable(Ctx_.Pos(), tr, params, Ctx_.Scoped));
             break;
         }
+        case TRule_sql_stmt_core::kAltSqlStmtCore70: {
+            Ctx_.BodyPart();
+            const auto& rule = core.GetAlt_sql_stmt_core70().GetRule_materialize_stmt1();
+            Token(rule.GetToken1()); // MATERIALIZE
+
+            TSourcePtr source = TSqlSelect(*this).NamedSingleSource(rule.GetRule_named_single_source2(), false);
+            if (!source) {
+                return false;
+            }
+
+            TString service = Ctx_.Scoped->CurrService;
+            TDeferredAtom cluster = Ctx_.Scoped->CurrCluster;
+            if (rule.HasBlock3()) {
+                if (!ClusterExpr(rule.GetBlock3().GetRule_cluster_expr2(), false, service, cluster)) {
+                    return false;
+                }
+            }
+            if (cluster.Empty()) {
+                Error() << "USE statement is missing or cluster not specified in MATERIALIZE";
+                return false;
+            }
+            TNodePtr clusterNode = Ctx_.Scoped->WrapCluster(cluster, Ctx_);
+
+            TTableHints hints;
+            if (rule.HasBlock4()) {
+                auto tmp = TableHintsImpl(rule.GetBlock4().GetRule_table_hints1(), service, "");
+                if (!tmp) {
+                    return false;
+                }
+                hints = std::move(*tmp);
+            }
+
+            TString varName;
+            TPosition intoPos = Ctx_.Pos();
+            if (!NamedNodeImpl(rule.GetRule_bind_parameter6(), varName, *this)) {
+                return false;
+            }
+
+            TString alias = Ctx_.MakeName("materializenode");
+            TString ref = Ctx_.MakeName("materialize");
+            auto materializeNode = BuildMaterialize(Ctx_.Pos(), std::move(source), service, clusterNode, std::move(hints), std::move(alias), Ctx_.Scoped);
+            materializeNode->SetLabel(ref);
+            blocks.push_back(materializeNode);
+            auto refNode = BuildYqlSubqueryRef(materializeNode, ref);
+            PushNamedNode(intoPos, varName, refNode);
+            break;
+        }
         case TRule_sql_stmt_core::ALT_NOT_SET:
             YQL_ENSURE(false, "Unreachable");
     }
diff --git a/yql/essentials/sql/v1/sql_select.h b/yql/essentials/sql/v1/sql_select.h
index e6a0b89d435..5fcd2dcff48 100644
--- a/yql/essentials/sql/v1/sql_select.h
+++ b/yql/essentials/sql/v1/sql_select.h
@@ -28,6 +28,7 @@ public:
     TSourcePtr Build(const TRule_select_unparenthesized_stmt& node, TPosition& selectPos);
     TSourcePtr BuildSubSelect(const TRule_select_kind_partial& node);
     TSourcePtr BuildSubSelect(const TRule_select_subexpr& node);
+    TSourcePtr NamedSingleSource(const TRule_named_single_source& node, bool unorderedSubquery);
 
 private:
     TSourcePtr CheckSubSelectOnDiscard(TSourcePtr source);
@@ -40,7 +41,6 @@ private:
     bool NamedColumn(TVector<TNodePtr>& columnList, const TRule_named_column& node);
     TSourcePtr SingleSource(const TRule_single_source& node, const TVector<TString>& derivedColumns, TPosition derivedColumnsPos, bool unorderedSubquery, TTableHints& tableHints, TMaybe<TString>& keyFunc, TString& provider, bool& isAnonymous);
     TSourcePtr HintedSingleSource(const TRule_hinted_single_source& node, const TVector<TString>& derivedColumns, TPosition derivedColumnsPos, bool unorderedSubquery);
-    TSourcePtr NamedSingleSource(const TRule_named_single_source& node, bool unorderedSubquery);
     bool FlattenByArg(const TString& sourceLabel, TVector<TNodePtr>& flattenByColumns, TVector<TNodePtr>& flattenByExprs, const TRule_flatten_by_arg& node);
     TSourcePtr FlattenSource(const TRule_flatten_source& node);
     TSourcePtr JoinSource(const TRule_join_source& node);
diff --git a/yql/essentials/sql/v1/sql_ut_antlr4.cpp b/yql/essentials/sql/v1/sql_ut_antlr4.cpp
index 835dcb13227..6bd44e1f44f 100644
--- a/yql/essentials/sql/v1/sql_ut_antlr4.cpp
+++ b/yql/essentials/sql/v1/sql_ut_antlr4.cpp
@@ -4,6 +4,7 @@
 #include "lexer/lexer.h"
 
 #include <yql/essentials/providers/common/provider/yql_provider_names.h>
+#include <yql/essentials/core/langver/feature.gen.h>
 #include <yql/essentials/sql/sql.h>
 #include <yql/essentials/sql/v1/lexer/antlr4/lexer.h>
 #include <util/generic/map.h>
@@ -308,3 +309,284 @@ Y_UNIT_TEST(AlterColumnCompressionLevelNegative) {
 }
 
 } // Y_UNIT_TEST_SUITE(ColumnCompression)
+
+Y_UNIT_TEST_SUITE(MaterializeStatement) {
+
+Y_UNIT_TEST(TopLevelBasic) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE Input INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 1, prog);
+}
+
+Y_UNIT_TEST(TopLevelUnused) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE Input INTO $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 0, prog);
+}
+
+Y_UNIT_TEST(TopLevelWithCluster) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        MATERIALIZE plato.Input ON plato INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 1, prog);
+}
+
+Y_UNIT_TEST(WithInnerSelectFromTable) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE (SELECT * FROM Input) INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 1, prog);
+}
+
+Y_UNIT_TEST(WithInnerSelectWithoutSource) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE (SELECT 1 as a) INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 1, prog);
+}
+
+Y_UNIT_TEST(WithInnerNamedExpr) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        $a = SELECT 1 as a;
+        MATERIALIZE $a INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 1, prog);
+}
+
+Y_UNIT_TEST(CompileAsSubquery) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+    settings.Mode = NSQLTranslation::ESqlMode::SUBQUERY;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE Input INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+}
+
+Y_UNIT_TEST(CompileAsLimitedView) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+    settings.Mode = NSQLTranslation::ESqlMode::LIMITED_VIEW;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE Input INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+}
+
+Y_UNIT_TEST(UseInSubquery) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        DEFINE SUBQUERY $sub() AS
+            MATERIALIZE (SELECT 1 as a) INTO $tmp;
+            MATERIALIZE $tmp INTO $result;
+            SELECT * FROM $result;
+        END DEFINE;
+        SELECT * FROM $sub();
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 2, prog);
+}
+
+Y_UNIT_TEST(UseInAction) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        DEFINE ACTION $sub() AS
+            MATERIALIZE (SELECT 1 as a) INTO $tmp;
+            MATERIALIZE $tmp INTO $result;
+            SELECT * FROM $result;
+        END DEFINE;
+        DO $sub();
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Materialize!"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Materialize!"], 2, prog);
+}
+
+Y_UNIT_TEST(MissingCluster) {
+    // No USE and no ON clause - should fail
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        MATERIALIZE plato.Input INTO $result;
+    )sql", settings);
+    UNIT_ASSERT(!res.IsOk());
+    UNIT_ASSERT_STRING_CONTAINS(Err2Str(res), "USE statement is missing or cluster not specified in MATERIALIZE");
+}
+
+Y_UNIT_TEST(NotAllowedBeforeVer) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = NYql::MinLangVersion;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE Input INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT(!res.IsOk());
+    UNIT_ASSERT_STRING_CONTAINS(Err2Str(res), "MATERIALIZE is not available before language version");
+}
+
+Y_UNIT_TEST(PreserveSortInRefSelect) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        $a = SELECT * FROM Input ORDER BY a;
+        MATERIALIZE $a INTO $b;
+        MATERIALIZE $b INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Sort"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Sort"], 1, prog);
+}
+
+Y_UNIT_TEST(PreserveSortInSubSelect) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        MATERIALIZE (SELECT * FROM Input ORDER BY a) INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Sort"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Sort"], 1, prog);
+}
+
+Y_UNIT_TEST(PreserveSortInSubquery) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        DEFINE SUBQUERY $sub() AS
+            SELECT * FROM Input ORDER BY key;
+        END DEFINE;
+        MATERIALIZE $sub() INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+    TWordCountHive elementStat = {{"Sort"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Sort"], 1, prog);
+}
+
+Y_UNIT_TEST(PreserveSortForTopLevelOnly1) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        $a = SELECT * FROM Input ORDER BY key;
+        $b = SELECT * FROM $a ORDER BY key;
+        MATERIALIZE $b INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+    UNIT_ASSERT_STRING_CONTAINS(Err2Str(res), "ORDER BY without LIMIT in subquery will be ignored");
+
+    TWordCountHive elementStat = {{"Sort"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Sort"], 1, prog);
+}
+
+Y_UNIT_TEST(PreserveSortForTopLevelOnly2) {
+    NSQLTranslation::TTranslationSettings settings;
+    settings.LangVer = ::NYql::NFeature::Materialize.MinLangVer;
+
+    auto res = SqlToYqlWithSettings(R"sql(
+        USE plato;
+        $a = SELECT * FROM Input ORDER BY key;
+        $b = SELECT * FROM $a;
+        MATERIALIZE $b INTO $result;
+        SELECT * FROM $result;
+    )sql", settings);
+    UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+    UNIT_ASSERT_STRING_CONTAINS(Err2Str(res), "ORDER BY without LIMIT in subquery will be ignored");
+
+    TWordCountHive elementStat = {{"Sort"}};
+    auto prog = VerifyProgram(res, elementStat);
+    UNIT_ASSERT_VALUES_EQUAL_C(elementStat["Sort"], 0, prog);
+}
+
+} // Y_UNIT_TEST_SUITE(MaterializeStatement)
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index 5563b96b172..9515cdb80d5 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -8910,7 +8910,7 @@ Y_UNIT_TEST(MultilineComments) {
 #if ANTLR_VER == 3
     UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:4:0: Error: Unexpected token '*' : cannot match to any predicted input...\n\n");
 #else
-    UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:4:0: Error: mismatched input '*' expecting {<EOF>, ';', '(', '$', ALTER, ANALYZE, BACKUP, BATCH, COMBINE, COMMIT, CREATE, DECLARE, DEFINE, DELETE, DISCARD, DO, DROP, EVALUATE, EXPLAIN, EXPORT, FOR, FROM, GRANT, IF, IMPORT, INSERT, PARALLEL, PRAGMA, PROCESS, REDUCE, REPLACE, RESTORE, REVOKE, ROLLBACK, SELECT, SHOW, TRUNCATE, UPDATE, UPSERT, USE, VALUES, WITH}\n");
+    UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:4:0: Error: mismatched input '*' expecting {<EOF>, ';', '(', '$', ALTER, ANALYZE, BACKUP, BATCH, COMBINE, COMMIT, CREATE, DECLARE, DEFINE, DELETE, DISCARD, DO, DROP, EVALUATE, EXPLAIN, EXPORT, FOR, FROM, GRANT, IF, IMPORT, INSERT, MATERIALIZE, PARALLEL, PRAGMA, PROCESS, REDUCE, REPLACE, RESTORE, REVOKE, ROLLBACK, SELECT, SHOW, TRUNCATE, UPDATE, UPSERT, USE, VALUES, WITH}\n");
 #endif
     res = SqlToYqlWithAnsiLexer(req);
     UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
diff --git a/yql/essentials/sql/v1/ut_antlr4/ya.make b/yql/essentials/sql/v1/ut_antlr4/ya.make
index 0491686f6fe..655632a2001 100644
--- a/yql/essentials/sql/v1/ut_antlr4/ya.make
+++ b/yql/essentials/sql/v1/ut_antlr4/ya.make
@@ -10,6 +10,7 @@ PEERDIR(
     yql/essentials/utils/string
     yql/essentials/public/udf/service/exception_policy
     yql/essentials/core/sql_types
+    yql/essentials/core/langver
     yql/essentials/sql
     yql/essentials/sql/pg_dummy
     yql/essentials/sql/v1/format