YQL-20307: Support inline subqueries

- Alter grammar to support inline subqueries.
- Support inline subqueries in `sql/v1` (translator).
- Introduce `sql/v1/proto_ast/parse_tree.h` for reusable parse tree predicates.
- Support inline subqueries in `sql/v1/format`.
- Support inline subqueries in `sql/v1/complete`.
- Add some SQL-tests.
- Pass all tests.
commit_hash:075b2240778d071e1c7542f912d3cc83019ef849

62 files changed, 2613 insertions, 206 deletions

diff --git a/yql/essentials/sql/v1/SQLv1.g.in b/yql/essentials/sql/v1/SQLv1.g.in
index 9035701614f..8675d3cfef8 100644
--- a/yql/essentials/sql/v1/SQLv1.g.in
+++ b/yql/essentials/sql/v1/SQLv1.g.in
@@ -174,7 +174,6 @@ in_atom_expr:
   | cast_expr
   | case_expr
   | an_id_or_type NAMESPACE (id_or_type | STRING_VALUE)
-  | LPAREN select_stmt RPAREN
 // TODO: resolve ANTLR error: rule in_atom_expr has non-LL(*) decision due to recursive rule invocations reachable from alts 3,8
 //  | LPAREN values_stmt RPAREN
   | value_constructor
@@ -234,8 +233,19 @@ json_query: JSON_QUERY LPAREN
   (json_query_handler ON ERROR)?
 RPAREN;
 
-// struct, tuple or named list
-smart_parenthesis: LPAREN named_expr_list? COMMA? RPAREN;
+select_subexpr:
+  select_subexpr_intersect (union_op select_subexpr_intersect)*
+;
+
+select_subexpr_intersect:
+  select_or_expr (intersect_op select_or_expr)*
+;
+
+select_or_expr: select_kind_partial | tuple_or_expr;
+
+tuple_or_expr: expr (AS an_id_or_type)? (COMMA named_expr)* COMMA?;
+
+smart_parenthesis: LPAREN (select_subexpr | COMMA?) RPAREN;
 
 expr_list: expr (COMMA expr)*;
 
@@ -601,8 +611,6 @@ values_source: values_stmt | select_stmt;
 values_source_row_list: values_source_row (COMMA values_source_row)*;
 values_source_row: LPAREN expr_list RPAREN;
 
-simple_values_source: expr_list | select_stmt;
-
 create_external_data_source_stmt: CREATE (OR REPLACE)? EXTERNAL DATA SOURCE (IF NOT EXISTS)? object_ref
     with_table_settings
 ;
@@ -816,7 +824,7 @@ column_option:
     ;
 
 family_relation: FAMILY an_id;
-nullability: NOT? NULL;  
+nullability: NOT? NULL;
 default_value: DEFAULT expr;
 
 column_order_by_specification: an_id (ASC | DESC)?;
@@ -1041,7 +1049,7 @@ set_clause_choice: set_clause_list | multiple_column_assignment;
 set_clause_list: set_clause (COMMA set_clause)*;
 set_clause: set_target EQUALS expr;
 set_target: column_name;
-multiple_column_assignment: set_target_list EQUALS LPAREN simple_values_source RPAREN;
+multiple_column_assignment: set_target_list EQUALS smart_parenthesis;
 set_target_list: LPAREN set_target (COMMA set_target)* RPAREN;
 
 // topics
@@ -1145,11 +1153,9 @@ window_frame_exclusion: EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXC
 // EXTRAS
 use_stmt: USE cluster_expr;
 
-subselect_stmt: (LPAREN select_stmt RPAREN | select_unparenthesized_stmt);
-
 // TODO: [fatal] rule named_nodes_stmt has non-LL(*) decision due to recursive rule invocations reachable from alts 1,3
 // named_nodes_stmt: bind_parameter_list EQUALS (expr | subselect_stmt | values_stmt | LPAREN values_stmt RPAREN);
-named_nodes_stmt: bind_parameter_list EQUALS (expr | subselect_stmt);
+named_nodes_stmt: bind_parameter_list EQUALS (expr | select_unparenthesized_stmt);
 
 commit_stmt: COMMIT;
 
diff --git a/yql/essentials/sql/v1/SQLv1Antlr4.g.in b/yql/essentials/sql/v1/SQLv1Antlr4.g.in
index b52309c484f..919da25318d 100644
--- a/yql/essentials/sql/v1/SQLv1Antlr4.g.in
+++ b/yql/essentials/sql/v1/SQLv1Antlr4.g.in
@@ -173,7 +173,6 @@ in_atom_expr:
   | cast_expr
   | case_expr
   | an_id_or_type NAMESPACE (id_or_type | STRING_VALUE)
-  | LPAREN select_stmt RPAREN
 // TODO: resolve ANTLR error: rule in_atom_expr has non-LL(*) decision due to recursive rule invocations reachable from alts 3,8
 //  | LPAREN values_stmt RPAREN
   | value_constructor
@@ -233,8 +232,19 @@ json_query: JSON_QUERY LPAREN
   (json_query_handler ON ERROR)?
 RPAREN;
 
-// struct, tuple or named list
-smart_parenthesis: LPAREN named_expr_list? COMMA? RPAREN;
+select_subexpr:
+  select_subexpr_intersect (union_op select_subexpr_intersect)*
+;
+
+select_subexpr_intersect:
+  select_or_expr (intersect_op select_or_expr)*
+;
+
+select_or_expr: select_kind_partial | tuple_or_expr;
+
+tuple_or_expr: expr (AS an_id_or_type)? (COMMA named_expr)* COMMA?;
+
+smart_parenthesis: LPAREN (select_subexpr | COMMA?) RPAREN;
 
 expr_list: expr (COMMA expr)*;
 
@@ -600,8 +610,6 @@ values_source: values_stmt | select_stmt;
 values_source_row_list: values_source_row (COMMA values_source_row)*;
 values_source_row: LPAREN expr_list RPAREN;
 
-simple_values_source: expr_list | select_stmt;
-
 create_external_data_source_stmt: CREATE (OR REPLACE)? EXTERNAL DATA SOURCE (IF NOT EXISTS)? object_ref
     with_table_settings
 ;
@@ -815,7 +823,7 @@ column_option:
     ;
 
 family_relation: FAMILY an_id;
-nullability: NOT? NULL;  
+nullability: NOT? NULL;
 default_value: DEFAULT expr;
 
 column_order_by_specification: an_id (ASC | DESC)?;
@@ -1041,7 +1049,7 @@ set_clause_choice: set_clause_list | multiple_column_assignment;
 set_clause_list: set_clause (COMMA set_clause)*;
 set_clause: set_target EQUALS expr;
 set_target: column_name;
-multiple_column_assignment: set_target_list EQUALS LPAREN simple_values_source RPAREN;
+multiple_column_assignment: set_target_list EQUALS smart_parenthesis;
 set_target_list: LPAREN set_target (COMMA set_target)* RPAREN;
 
 // topics
@@ -1145,11 +1153,9 @@ window_frame_exclusion: EXCLUDE CURRENT ROW | EXCLUDE GROUP | EXCLUDE TIES | EXC
 // EXTRAS
 use_stmt: USE cluster_expr;
 
-subselect_stmt: (LPAREN select_stmt RPAREN | select_unparenthesized_stmt);
-
 // TODO: [fatal] rule named_nodes_stmt has non-LL(*) decision due to recursive rule invocations reachable from alts 1,3
 // named_nodes_stmt: bind_parameter_list EQUALS (expr | subselect_stmt | values_stmt | LPAREN values_stmt RPAREN);
-named_nodes_stmt: bind_parameter_list EQUALS (expr | subselect_stmt);
+named_nodes_stmt: bind_parameter_list EQUALS (expr | select_unparenthesized_stmt);
 
 commit_stmt: COMMIT;
 
diff --git a/yql/essentials/sql/v1/complete/analysis/global/column.cpp b/yql/essentials/sql/v1/complete/analysis/global/column.cpp
index 3f71ae6763d..d8c73e00b7f 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/column.cpp
+++ b/yql/essentials/sql/v1/complete/analysis/global/column.cpp
@@ -295,6 +295,30 @@ namespace NSQLComplete {
             const TNamedNodes* Nodes_;
         };
 
+        class TEnclosingSelectVisitor: public TSQLv1NarrowingVisitor {
+        public:
+            explicit TEnclosingSelectVisitor(const TParsedInput& input)
+                : TSQLv1NarrowingVisitor(input)
+            {
+            }
+
+            std::any visitSelect_core(SQLv1::Select_coreContext* ctx) override {
+                if (!IsEnclosing(ctx)) {
+                    return {};
+                }
+
+                Enclosing_ = ctx;
+                return visitChildren(ctx);
+            }
+
+            SQLv1::Select_coreContext* GetEnclosing() && {
+                return Enclosing_;
+            }
+
+        private:
+            SQLv1::Select_coreContext* Enclosing_ = nullptr;
+        };
+
         class TVisitor: public TSQLv1NarrowingVisitor {
         public:
             TVisitor(const TParsedInput& input, const TNamedNodes* nodes)
@@ -343,11 +367,23 @@ namespace NSQLComplete {
             const TNamedNodes* Nodes_;
         };
 
+        antlr4::ParserRuleContext* Enclosing(const TParsedInput& input) {
+            TEnclosingSelectVisitor visitor(input);
+            visitor.visit(input.SqlQuery);
+
+            antlr4::ParserRuleContext* ctx = std::move(visitor).GetEnclosing();
+            if (!ctx) {
+                ctx = input.SqlQuery;
+            }
+
+            return ctx;
+        }
+
     } // namespace
 
     TMaybe<TColumnContext> InferColumnContext(TParsedInput input, const TNamedNodes& nodes) {
         // TODO: add utility `auto ToMaybe<T>(std::any any) -> TMaybe<T>`
-        std::any result = TVisitor(input, &nodes).visit(input.SqlQuery);
+        std::any result = TVisitor(input, &nodes).visit(Enclosing(input));
         if (!result.has_value()) {
             return Nothing();
         }
diff --git a/yql/essentials/sql/v1/complete/analysis/global/named_node.cpp b/yql/essentials/sql/v1/complete/analysis/global/named_node.cpp
index af8c17d3da2..4199da625c2 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/named_node.cpp
+++ b/yql/essentials/sql/v1/complete/analysis/global/named_node.cpp
@@ -99,8 +99,6 @@ namespace NSQLComplete {
 
                 if (auto* expr = ctx->expr()) {
                     (*Names_)[std::move(*id)] = expr;
-                } else if (auto* subselect = ctx->subselect_stmt()) {
-                    (*Names_)[std::move(*id)] = subselect;
                 } else {
                     (*Names_)[std::move(*id)] = std::monostate();
                 }
diff --git a/yql/essentials/sql/v1/complete/analysis/global/named_node.h b/yql/essentials/sql/v1/complete/analysis/global/named_node.h
index 07e1e6f91b9..0050915f523 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/named_node.h
+++ b/yql/essentials/sql/v1/complete/analysis/global/named_node.h
@@ -13,7 +13,6 @@ namespace NSQLComplete {
 
     using TNamedNode = std::variant<
         SQLv1::ExprContext*,
-        SQLv1::Subselect_stmtContext*,
         NYT::TNode,
         std::monostate>;
 
diff --git a/yql/essentials/sql/v1/complete/sql_complete_ut.cpp b/yql/essentials/sql/v1/complete/sql_complete_ut.cpp
index ecb3084dcba..679230c7a24 100644
--- a/yql/essentials/sql/v1/complete/sql_complete_ut.cpp
+++ b/yql/essentials/sql/v1/complete/sql_complete_ut.cpp
@@ -1,6 +1,6 @@
 #include "sql_complete.h"
-
 #include <yql/essentials/sql/v1/complete/syntax/grammar.h>
+
 #include <yql/essentials/sql/v1/complete/name/cache/local/cache.h>
 #include <yql/essentials/sql/v1/complete/name/cluster/static/discovery.h>
 #include <yql/essentials/sql/v1/complete/name/object/simple/schema.h>
@@ -857,6 +857,8 @@ Y_UNIT_TEST_SUITE(SqlCompleteTests) {
 
         UNIT_ASSERT_VALUES_EQUAL(Complete(engine, "$x = sel#"), expected);
         UNIT_ASSERT_VALUES_EQUAL(Complete(engine, "$x = (sel#)"), expected);
+        UNIT_ASSERT_VALUES_EQUAL(Complete(engine, "SELECT (sel#)"), expected);
+        UNIT_ASSERT_VALUES_EQUAL(Complete(engine, "SELECT * FROM t WHERE (sel#)"), expected);
     }
 
     Y_UNIT_TEST(Upsert) {
@@ -1813,6 +1815,26 @@ Y_UNIT_TEST_SUITE(SqlCompleteTests) {
         UNIT_ASSERT_VALUES_EQUAL(CompleteTop(expected.size(), engine, query), expected);
     }
 
+    Y_UNIT_TEST(ColumnAtSubqueryExpresson) {
+        auto engine = MakeSqlCompletionEngineUT();
+
+        TVector<TString> input = {
+            R"sql(SELECT (SELECT # FROM (SELECT 1 AS a));)sql",
+            R"sql(SELECT 1 + (SELECT # FROM (SELECT 1 AS a));)sql",
+            R"sql(SELECT * FROM t WHERE (SELECT # FROM (SELECT 1 AS a));)sql",
+            R"sql(SELECT * FROM t WHERE 1 < (SELECT # FROM (SELECT 1 AS a));)sql",
+        };
+
+        TVector<TCandidate> expected = {
+            {ColumnName, "a"},
+        };
+
+        UNIT_ASSERT_VALUES_EQUAL(CompleteTop(expected.size(), engine, input[0]), expected);
+        UNIT_ASSERT_VALUES_EQUAL(CompleteTop(expected.size(), engine, input[1]), expected);
+        UNIT_ASSERT_VALUES_EQUAL(CompleteTop(expected.size(), engine, input[2]), expected);
+        UNIT_ASSERT_VALUES_EQUAL(CompleteTop(expected.size(), engine, input[3]), expected);
+    }
+
     Y_UNIT_TEST(NoBindingAtQuoted) {
         auto engine = MakeSqlCompletionEngineUT();
 
diff --git a/yql/essentials/sql/v1/format/sql_format.cpp b/yql/essentials/sql/v1/format/sql_format.cpp
index 4308e2b2baf..d76b64df3b7 100644
--- a/yql/essentials/sql/v1/format/sql_format.cpp
+++ b/yql/essentials/sql/v1/format/sql_format.cpp
@@ -1,5 +1,7 @@
 #include "sql_format.h"
 
+#include <yql/essentials/sql/v1/proto_parser/parse_tree.h>
+
 #include <yql/essentials/parser/lexer_common/lexer.h>
 #include <yql/essentials/core/sql_types/simple_types.h>
 
@@ -20,10 +22,10 @@ namespace NSQLFormat {
 namespace {
 
 using namespace NSQLv1Generated;
+using namespace NSQLTranslationV1;
 
 using NSQLTranslation::TParsedToken;
 using NSQLTranslation::TParsedTokenList;
-using NSQLTranslationV1::IsProbablyKeyword;
 using TTokenIterator = TParsedTokenList::const_iterator;
 
 TTokenIterator SkipWS(TTokenIterator curr, TTokenIterator end) {
@@ -568,6 +570,14 @@ private:
             ForceExpandedColumn_ = paren.GetColumn();
             ForceExpandedLine_ = paren.GetLine();
             suppressExpr = true;
+        } else if (descr == TRule_smart_parenthesis::GetDescriptor()) {
+            const auto& value = dynamic_cast<const TRule_smart_parenthesis&>(msg);
+            if (IsSelect(value)) {
+                auto& paren = value.GetToken1();
+                ForceExpandedColumn_ = paren.GetColumn();
+                ForceExpandedLine_ = paren.GetLine();
+                suppressExpr = true;
+            }
         } else if (descr == TRule_case_expr::GetDescriptor()) {
             const auto& value = dynamic_cast<const TRule_case_expr&>(msg);
             auto& token = value.GetToken1();
@@ -703,7 +713,13 @@ private:
                 return true;
             case TRule_sql_stmt_core::kAltSqlStmtCore3: { // named nodes
                 const auto& stmt = msg.GetAlt_sql_stmt_core3().GetRule_named_nodes_stmt1();
-                if (stmt.GetBlock3().HasAlt1()) {
+
+                const bool isSelect = (
+                    (stmt.GetBlock3().HasAlt1() &&
+                     IsSelect(stmt.GetBlock3().GetAlt1().GetRule_expr1())) ||
+                    (stmt.GetBlock3().HasAlt2()));
+
+                if (!isSelect) {
                     return true;
                 }
                 break;
@@ -856,6 +872,42 @@ private:
         }
     }
 
+    void VisitSmartParenthesis(const TRule_smart_parenthesis& msg) {
+        if (!IsSelect(msg)) {
+            return VisitAllFields(msg.GetDescriptor(), msg);
+        }
+
+        Y_ENSURE(msg.GetBlock2().HasAlt1());
+
+        Visit(msg.GetToken1());
+        PushCurrentIndent();
+        NewLine();
+        Visit(msg.GetBlock2().GetAlt1().GetRule_select_subexpr1());
+        NewLine();
+        PopCurrentIndent();
+        Visit(msg.GetToken3());
+    }
+
+    void VisitSelectSubExpr(const TRule_select_subexpr& msg) {
+        Visit(msg.GetRule_select_subexpr_intersect1());
+        for (const auto& block : msg.GetBlock2()) {
+            NewLine();
+            Visit(block.GetRule_union_op1());
+            NewLine();
+            Visit(block.GetRule_select_subexpr_intersect2());
+        }
+    }
+
+    void VisitSelectSubExprIntersect(const TRule_select_subexpr_intersect& msg) {
+        Visit(msg.GetRule_select_or_expr1());
+        for (const auto& block : msg.GetBlock2()) {
+            NewLine();
+            Visit(block.GetRule_intersect_op1());
+            NewLine();
+            Visit(block.GetRule_select_or_expr2());
+        }
+    }
+
     void VisitSelectUnparenthesized(const TRule_select_unparenthesized_stmt& msg) {
         NewLine();
         Visit(msg.GetRule_select_unparenthesized_stmt_intersect1());
@@ -893,36 +945,13 @@ private:
 
         case TRule_named_nodes_stmt::TBlock3::kAlt2: {
             const auto& alt = msg.GetBlock3().GetAlt2();
-            const auto& subselect = alt.GetRule_subselect_stmt1();
-            switch (subselect.GetBlock1().Alt_case()) {
-            case TRule_subselect_stmt::TBlock1::kAlt1: {
-                const auto& alt = subselect.GetBlock1().GetAlt1();
-                Visit(alt.GetToken1());
-                NewLine();
-                PushCurrentIndent();
-                Visit(alt.GetRule_select_stmt2());
-                PopCurrentIndent();
-                NewLine();
-                Visit(alt.GetToken3());
-                break;
-            }
-
-            case TRule_subselect_stmt::TBlock1::kAlt2: {
-                const auto& alt = subselect.GetBlock1().GetAlt2();
-                Out(" (");
-                NewLine();
-                PushCurrentIndent();
-                Visit(alt);
-                PopCurrentIndent();
-                NewLine();
-                Out(')');
-                break;
-            }
-
-            default:
-                ythrow yexception() << "Alt is not supported";
-            }
-
+            Out(" (");
+            NewLine();
+            PushCurrentIndent();
+            Visit(alt);
+            PopCurrentIndent();
+            NewLine();
+            Out(')');
             break;
         }
 
@@ -1092,39 +1121,43 @@ private:
                 PopCurrentIndent();
                 Visit(targets.GetToken4());
                 Visit(multiColumn.GetToken2());
-                Visit(multiColumn.GetToken3());
-                NewLine();
-                const auto& simpleValues = multiColumn.GetRule_simple_values_source4();
-                switch (simpleValues.Alt_case()) {
-                case TRule_simple_values_source::kAltSimpleValuesSource1: {
-                    const auto& exprs = simpleValues.GetAlt_simple_values_source1().GetRule_expr_list1();
-                    NewLine();
-                    PushCurrentIndent();
-                    Visit(exprs.GetRule_expr1());
-                    for (const auto& block : exprs.GetBlock2()) {
-                        Visit(block.GetToken1());
-                        NewLine();
-                        Visit(block.GetRule_expr2());
-                    }
 
-                    PopCurrentIndent();
-                    NewLine();
+                const auto& parenthesis = multiColumn.GetRule_smart_parenthesis3();
+
+                const auto* tuple_or_expr = GetTupleOrExpr(parenthesis);
+                if (!tuple_or_expr) {
+                    Visit(parenthesis);
                     break;
                 }
-                case TRule_simple_values_source::kAltSimpleValuesSource2: {
-                    NewLine();
-                    PushCurrentIndent();
-                    Visit(simpleValues.GetAlt_simple_values_source2());
-                    PopCurrentIndent();
-                    NewLine();
+
+                const bool isHeadNamed = tuple_or_expr->HasBlock2();
+                const bool isTailNamed = AnyOf(tuple_or_expr->GetBlock3(), [](const auto& block) {
+                    return block.GetRule_named_expr2().HasBlock2();
+                });
+                if (isHeadNamed || isTailNamed) {
+                    Visit(parenthesis);
                     break;
                 }
-                default:
-                    ythrow yexception() << "Alt is not supported";
+
+
+                Visit(parenthesis.GetToken1());
+                PushCurrentIndent();
+                NewLine();
+
+                Visit(tuple_or_expr->GetRule_expr1());
+                for (auto& block : tuple_or_expr->GetBlock3()) {
+                    Visit(block.GetToken1());
+                    NewLine();
+                    Visit(block.GetRule_named_expr2().GetRule_expr1());
+                }
+                if (tuple_or_expr->HasBlock4()) {
+                    Visit(tuple_or_expr->GetBlock4().GetToken1());
                 }
 
                 NewLine();
-                Visit(multiColumn.GetToken5());
+                PopCurrentIndent();
+                Visit(parenthesis.GetToken3());
+
                 break;
             }
             default:
@@ -2576,21 +2609,6 @@ private:
         NewLine();
     }
 
-    void VisitInAtomExpr(const TRule_in_atom_expr& msg) {
-        if (msg.Alt_case() == TRule_in_atom_expr::kAltInAtomExpr7) {
-            const auto& alt = msg.GetAlt_in_atom_expr7();
-            Visit(alt.GetToken1());
-            NewLine();
-            PushCurrentIndent();
-            Visit(alt.GetRule_select_stmt2());
-            NewLine();
-            PopCurrentIndent();
-            Visit(alt.GetToken3());
-        } else {
-            VisitAllFields(TRule_in_atom_expr::GetDescriptor(), msg);
-        }
-    }
-
     void VisitSelectKindParenthesis(const TRule_select_kind_parenthesis& msg) {
         if (msg.Alt_case() == TRule_select_kind_parenthesis::kAltSelectKindParenthesis2) {
             const auto& alt = msg.GetAlt_select_kind_parenthesis2();
@@ -3063,7 +3081,6 @@ TStaticData::TStaticData()
         {TRule_window_specification::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitWindowSpecification)},
         {TRule_window_partition_clause::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitWindowParitionClause)},
         {TRule_lambda_body::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitLambdaBody)},
-        {TRule_in_atom_expr::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitInAtomExpr)},
         {TRule_select_kind_parenthesis::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelectKindParenthesis)},
         {TRule_cast_expr::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitCastExpr)},
         {TRule_bitcast_expr::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitBitCastExpr)},
@@ -3092,6 +3109,9 @@ TStaticData::TStaticData()
         {TRule_pragma_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitPragma)},
         {TRule_select_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelect)},
         {TRule_select_stmt_intersect::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelectIntersect)},
+        {TRule_smart_parenthesis::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSmartParenthesis)},
+        {TRule_select_subexpr::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelectSubExpr)},
+        {TRule_select_subexpr_intersect::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelectSubExprIntersect)},
         {TRule_select_unparenthesized_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelectUnparenthesized)},
         {TRule_select_unparenthesized_stmt_intersect::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitSelectUnparenthesizedIntersect)},
         {TRule_named_nodes_stmt::GetDescriptor(), MakePrettyFunctor(&TPrettyVisitor::VisitNamedNodes)},
diff --git a/yql/essentials/sql/v1/format/sql_format_ut.h b/yql/essentials/sql/v1/format/sql_format_ut.h
index e33858d0b62..d487b956e37 100644
--- a/yql/essentials/sql/v1/format/sql_format_ut.h
+++ b/yql/essentials/sql/v1/format/sql_format_ut.h
@@ -2001,3 +2001,90 @@ Y_UNIT_TEST(DropStreamingQuery) {
     TSetup setup;
     setup.Run(cases);
 }
+
+Y_UNIT_TEST(NamedNodeNewLine) {
+    TString input = R"sql(
+DEFINE SUBQUERY $x() AS
+    $a = SELECT 1;
+    $b = SELECT $a;
+    SELECT $b;
+END DEFINE;
+)sql";
+
+    TString expected = R"sql(
+DEFINE SUBQUERY $x() AS
+    $a = (
+        SELECT
+            1
+    );
+
+    $b = (
+        SELECT
+            $a
+    );
+
+    SELECT
+        $b
+    ;
+END DEFINE;
+)sql";
+
+    input.erase(0, 1);
+    expected.erase(0, 1);
+
+    TCases cases = {
+        {input, expected},
+    };
+
+    TSetup setup;
+    setup.Run(cases);
+}
+
+Y_UNIT_TEST(InlineSubquery) {
+        TString input = R"sql(
+SELECT (SELECT 1);
+SELECT (SELECT * FROM t WHERE p);
+SELECT * FROM t WHERE x > (SELECT 1);
+)sql";
+
+    TString expected = R"sql(
+SELECT
+    (
+        SELECT
+            1
+    )
+;
+
+SELECT
+    (
+        SELECT
+            *
+        FROM
+            t
+        WHERE
+            p
+    )
+;
+
+SELECT
+    *
+FROM
+    t
+WHERE
+    x > (
+        SELECT
+            1
+    )
+;
+)sql";
+
+    input.erase(0, 1);
+    expected.erase(0, 1);
+
+    TCases cases = {
+        {input, expected},
+    };
+
+    TSetup setup;
+    setup.Run(cases);
+}
diff --git a/yql/essentials/sql/v1/proto_parser/parse_tree.cpp b/yql/essentials/sql/v1/proto_parser/parse_tree.cpp
new file mode 100644
index 00000000000..a9318368436
--- /dev/null
+++ b/yql/essentials/sql/v1/proto_parser/parse_tree.cpp
@@ -0,0 +1,110 @@
+#include "parse_tree.h"
+
+namespace NSQLTranslationV1 {
+
+    const TRule_select_or_expr* GetSelectOrExpr(const TRule_smart_parenthesis& msg) {
+        if (!msg.GetBlock2().HasAlt1()) {
+            return nullptr;
+        }
+
+        return &msg.GetBlock2()
+                    .GetAlt1()
+                    .GetRule_select_subexpr1()
+                    .GetRule_select_subexpr_intersect1()
+                    .GetRule_select_or_expr1();
+    }
+
+    const TRule_tuple_or_expr* GetTupleOrExpr(const TRule_smart_parenthesis& msg) {
+        const auto* select_or_expr = GetSelectOrExpr(msg);
+        if (!select_or_expr) {
+            return nullptr;
+        }
+
+        if (!select_or_expr->HasAlt_select_or_expr2()) {
+            return nullptr;
+        }
+
+        return &select_or_expr
+                    ->GetAlt_select_or_expr2()
+                    .GetRule_tuple_or_expr1();
+    }
+
+    const TRule_smart_parenthesis* GetParenthesis(const TRule_expr& msg) {
+        if (!msg.HasAlt_expr1()) {
+            return nullptr;
+        }
+
+        const auto& con = msg.GetAlt_expr1()
+                              .GetRule_or_subexpr1()
+                              .GetRule_and_subexpr1()
+                              .GetRule_xor_subexpr1()
+                              .GetRule_eq_subexpr1()
+                              .GetRule_neq_subexpr1()
+                              .GetRule_bit_subexpr1()
+                              .GetRule_add_subexpr1()
+                              .GetRule_mul_subexpr1()
+                              .GetRule_con_subexpr1();
+
+        if (!con.HasAlt_con_subexpr1()) {
+            return nullptr;
+        }
+
+        const auto& unary_subexpr = con.GetAlt_con_subexpr1()
+                                        .GetRule_unary_subexpr1();
+
+        if (!unary_subexpr.HasAlt_unary_subexpr1()) {
+            return nullptr;
+        }
+
+        const auto& block = unary_subexpr.GetAlt_unary_subexpr1()
+                                .GetRule_unary_casual_subexpr1()
+                                .GetBlock1();
+
+        if (!block.HasAlt2()) {
+            return nullptr;
+        }
+
+        const auto& atom = block.GetAlt2()
+                               .GetRule_atom_expr1();
+
+        if (!atom.HasAlt_atom_expr3()) {
+            return nullptr;
+        }
+
+        return &atom.GetAlt_atom_expr3()
+                    .GetRule_lambda1()
+                    .GetRule_smart_parenthesis1();
+    }
+
+    bool IsSelect(const TRule_smart_parenthesis& msg) {
+        const auto* select_or_expr = GetSelectOrExpr(msg);
+        if (!select_or_expr) {
+            return false;
+        }
+
+        if (select_or_expr->HasAlt_select_or_expr1()) {
+            return true;
+        }
+
+        return IsSelect(
+            select_or_expr
+                ->GetAlt_select_or_expr2()
+                .GetRule_tuple_or_expr1()
+                .GetRule_expr1());
+    }
+
+    bool IsSelect(const TRule_expr& msg) {
+        const auto* parenthesis = GetParenthesis(msg);
+        if (!parenthesis) {
+            return false;
+        }
+
+        return IsSelect(*parenthesis);
+    }
+
+    bool IsOnlySubExpr(const TRule_select_subexpr& node) {
+        return node.GetBlock2().size() == 0 &&
+               node.GetRule_select_subexpr_intersect1().GetBlock2().size() == 0;
+    }
+
+} // namespace NSQLTranslationV1
diff --git a/yql/essentials/sql/v1/proto_parser/parse_tree.h b/yql/essentials/sql/v1/proto_parser/parse_tree.h
new file mode 100644
index 00000000000..74936d90466
--- /dev/null
+++ b/yql/essentials/sql/v1/proto_parser/parse_tree.h
@@ -0,0 +1,17 @@
+#pragma once
+
+#include <yql/essentials/parser/proto_ast/gen/v1_proto_split/SQLv1Parser.pb.main.h>
+
+namespace NSQLTranslationV1 {
+
+    using namespace NSQLv1Generated;
+
+    const TRule_tuple_or_expr* GetTupleOrExpr(const TRule_smart_parenthesis& msg);
+
+    bool IsSelect(const TRule_smart_parenthesis& msg);
+
+    bool IsSelect(const TRule_expr& msg);
+
+    bool IsOnlySubExpr(const TRule_select_subexpr& msg);
+
+} // namespace NSQLTranslationV1
diff --git a/yql/essentials/sql/v1/proto_parser/ya.make b/yql/essentials/sql/v1/proto_parser/ya.make
index d87741f8a91..0fbe9f45469 100644
--- a/yql/essentials/sql/v1/proto_parser/ya.make
+++ b/yql/essentials/sql/v1/proto_parser/ya.make
@@ -3,9 +3,11 @@ LIBRARY()
 PEERDIR(
     yql/essentials/utils
     yql/essentials/parser/proto_ast/collect_issues
+    yql/essentials/parser/proto_ast/gen/v1_proto_split
 )
 
 SRCS(
+    parse_tree.cpp
     proto_parser.cpp
 )
 
@@ -17,4 +19,3 @@ RECURSE(
     antlr4
     antlr4_ansi
 )
-
diff --git a/yql/essentials/sql/v1/select.cpp b/yql/essentials/sql/v1/select.cpp
index c6ba387e7bd..41689db592c 100644
--- a/yql/essentials/sql/v1/select.cpp
+++ b/yql/essentials/sql/v1/select.cpp
@@ -662,6 +662,10 @@ TNodePtr BuildSubqueryRef(TNodePtr subquery, const TString& alias, int tupleInde
     return new TSubqueryRefNode(std::move(subquery), alias, tupleIndex);
 }
 
+bool IsSubqueryRef(const TSourcePtr& source) {
+    return dynamic_cast<const TSubqueryRefNode*>(source.Get()) != nullptr;
+}
+
 class TInvalidSubqueryRefNode: public ISource {
 public:
     TInvalidSubqueryRefNode(TPosition pos)
diff --git a/yql/essentials/sql/v1/source.cpp b/yql/essentials/sql/v1/source.cpp
index 613c521da6e..1a5f9d1cd09 100644
--- a/yql/essentials/sql/v1/source.cpp
+++ b/yql/essentials/sql/v1/source.cpp
@@ -939,7 +939,7 @@ bool ISource::InitFilters(TContext& ctx) {
 }
 
 TAstNode* ISource::Translate(TContext& ctx) const {
-    Y_DEBUG_ABORT_UNLESS(false);
+    Y_DEBUG_ABORT_UNLESS(false, "Can't tranlsate ISource, maybe it is used in a scalar context");
     Y_UNUSED(ctx);
     return nullptr;
 }
@@ -991,6 +991,16 @@ TNodePtr ISource::BuildMatchRecognize(TContext& ctx, TString&& inputTable){
     return MatchRecognizeBuilder_->Build(ctx, std::move(inputTable), this);
 };
 
+TSourcePtr MoveOutIfSource(TNodePtr& node) {
+    ISource* source = dynamic_cast<ISource*>(node.Get());
+    if (!source) {
+        return nullptr;
+    }
+
+    YQL_ENSURE(source == node.Release());
+    return source;
+}
+
 IJoin::IJoin(TPosition pos)
     : ISource(pos)
 {
diff --git a/yql/essentials/sql/v1/source.h b/yql/essentials/sql/v1/source.h
index 20f81135cd4..bd3a0327cb2 100644
--- a/yql/essentials/sql/v1/source.h
+++ b/yql/essentials/sql/v1/source.h
@@ -164,6 +164,8 @@ namespace NSQLTranslationV1 {
         return cloneArgs;
     }
 
+    TSourcePtr MoveOutIfSource(TNodePtr& node);
+
     struct TJoinLinkSettings {
         enum class EStrategy {
             Default,
@@ -243,6 +245,8 @@ namespace NSQLTranslationV1 {
     // Implemented in select.cpp
     TNodePtr BuildSubquery(TSourcePtr source, const TString& alias, bool inSubquery, int ensureTupleSize, TScopedStatePtr scoped);
     TNodePtr BuildSubqueryRef(TNodePtr subquery, const TString& alias, int tupleIndex = -1);
+    bool IsSubqueryRef(const TSourcePtr& source);
+
     TNodePtr BuildInvalidSubqueryRef(TPosition subqueryPos);
     TNodePtr BuildSourceNode(TPosition pos, TSourcePtr source, bool checkExist = false, bool withTables = false);
     TSourcePtr BuildMuxSource(TPosition pos, TVector<TSourcePtr>&& sources);
diff --git a/yql/essentials/sql/v1/sql_expression.cpp b/yql/essentials/sql/v1/sql_expression.cpp
index 4451e58a9b6..79d7c7c66b7 100644
--- a/yql/essentials/sql/v1/sql_expression.cpp
+++ b/yql/essentials/sql/v1/sql_expression.cpp
@@ -2,10 +2,12 @@
 #include "sql_call_expr.h"
 #include "sql_select.h"
 #include "sql_values.h"
+#include <yql/essentials/sql/v1/proto_parser/parse_tree.h>
 #include <yql/essentials/utils/utf8.h>
 #include <util/charset/wide.h>
 #include <util/string/ascii.h>
 #include <util/string/hex.h>
+#include <util/generic/scope.h>
 #include "antlr_token.h"
 
 namespace NSQLTranslationV1 {
@@ -15,7 +17,7 @@ using NALPDefaultAntlr4::SQLv1Antlr4Lexer;
 
 using namespace NSQLv1Generated;
 
-TNodePtr TSqlExpression::Build(const TRule_expr& node) {
+TNodePtr TSqlExpression::BuildSourceOrNode(const TRule_expr& node) {
         // expr:
         //     or_subexpr (OR or_subexpr)*
         //   | type_name_composite
@@ -33,6 +35,16 @@ TNodePtr TSqlExpression::Build(const TRule_expr& node) {
         }
     }
 
+TNodePtr TSqlExpression::Build(const TRule_expr& node) {
+    const bool prevIsSourceAllowed = IsSourceAllowed_;
+    Y_DEFER {
+        IsSourceAllowed_ = prevIsSourceAllowed;
+    };
+
+    IsSourceAllowed_ = false;
+    return BuildSourceOrNode(node);
+}
+
 TNodePtr TSqlExpression::Build(const TRule_lambda_or_parameter& node) {
         // lambda_or_parameter:
         //    lambda
@@ -58,6 +70,24 @@ TNodePtr TSqlExpression::Build(const TRule_lambda_or_parameter& node) {
         }
     }
 
+TSourcePtr TSqlExpression::BuildSource(const TRule_select_or_expr& node) {
+    TNodePtr result = SelectOrExpr(node);
+    if (!result) {
+        return nullptr;
+    }
+
+    if (TSourcePtr source = MoveOutIfSource(result)) {
+        return source;
+    }
+
+    Ctx_.Error(result->GetPos()) << "Expected SELECT/PROCESS/REDUCE statement";
+    return nullptr;
+}
+
+TNodePtr TSqlExpression::BuildSourceOrNode(const TRule_smart_parenthesis& node) {
+    return SmartParenthesis(node);
+}
+
 TNodePtr TSqlExpression::SubExpr(const TRule_mul_subexpr& node, const TTrailingQuestions& tail) {
         // mul_subexpr: con_subexpr (DOUBLE_PIPE con_subexpr)*;
         auto getNode = [](const TRule_mul_subexpr::TBlock2& b) -> const TRule_con_subexpr& { return b.GetRule_con_subexpr2(); };
@@ -1520,40 +1550,20 @@ TMaybe<TExprOrIdent> TSqlExpression::InAtomExpr(const TRule_in_atom_expr& node,
             break;
         }
         case TRule_in_atom_expr::kAltInAtomExpr7: {
-            Token(node.GetAlt_in_atom_expr7().GetToken1());
-            // reset column reference scope (select will reenable it where needed)
-            TColumnRefScope scope(Ctx_, EColumnRefState::Deny);
-            TSqlSelect select(Ctx_, Mode_);
-            TPosition pos;
-            auto source = select.Build(node.GetAlt_in_atom_expr7().GetRule_select_stmt2(), pos);
-            if (!source) {
-                Ctx_.IncrementMonCounter("sql_errors", "BadSource");
-                return {};
-            }
-            Ctx_.IncrementMonCounter("sql_features", "InSubquery");
-            const auto alias = Ctx_.MakeName("subquerynode");
-            const auto ref = Ctx_.MakeName("subquery");
-            auto& blocks = Ctx_.GetCurrentBlocks();
-            blocks.push_back(BuildSubquery(std::move(source), alias, Mode_ == NSQLTranslation::ESqlMode::SUBQUERY, -1, Ctx_.Scoped));
-            blocks.back()->SetLabel(ref);
-            result.Expr = BuildSubqueryRef(blocks.back(), ref, -1);
+            result.Expr = ValueConstructor(node.GetAlt_in_atom_expr7().GetRule_value_constructor1());
             break;
         }
-        case TRule_in_atom_expr::kAltInAtomExpr8: {
-            result.Expr = ValueConstructor(node.GetAlt_in_atom_expr8().GetRule_value_constructor1());
+        case TRule_in_atom_expr::kAltInAtomExpr8:
+            result.Expr = BitCastRule(node.GetAlt_in_atom_expr8().GetRule_bitcast_expr1());
             break;
-        }
         case TRule_in_atom_expr::kAltInAtomExpr9:
-            result.Expr = BitCastRule(node.GetAlt_in_atom_expr9().GetRule_bitcast_expr1());
+            result.Expr = ListLiteral(node.GetAlt_in_atom_expr9().GetRule_list_literal1());
             break;
         case TRule_in_atom_expr::kAltInAtomExpr10:
-            result.Expr = ListLiteral(node.GetAlt_in_atom_expr10().GetRule_list_literal1());
+            result.Expr = DictLiteral(node.GetAlt_in_atom_expr10().GetRule_dict_literal1());
             break;
         case TRule_in_atom_expr::kAltInAtomExpr11:
-            result.Expr = DictLiteral(node.GetAlt_in_atom_expr11().GetRule_dict_literal1());
-            break;
-        case TRule_in_atom_expr::kAltInAtomExpr12:
-            result.Expr = StructLiteral(node.GetAlt_in_atom_expr12().GetRule_struct_literal1());
+            result.Expr = StructLiteral(node.GetAlt_in_atom_expr11().GetRule_struct_literal1());
             break;
         case TRule_in_atom_expr::ALT_NOT_SET:
             AltNotImplemented("in_atom_expr", node);
@@ -2310,25 +2320,119 @@ TNodePtr TSqlExpression::SqlInExpr(const TRule_in_expr& node, const TTrailingQue
     TSqlExpression expr(Ctx_, Mode_);
     expr.SetSmartParenthesisMode(TSqlExpression::ESmartParenthesis::InStatement);
     auto result = expr.UnaryExpr(node.GetRule_in_unary_subexpr1(), tail);
+
+    if (TSourcePtr source = MoveOutIfSource(result)) {
+        if (IsSubqueryRef(source)) { // Prevent redundant ref to ref
+            return source;
+        }
+
+        Ctx_.IncrementMonCounter("sql_features", "InSubquery");
+
+        const auto alias = Ctx_.MakeName("subquerynode");
+        const auto ref = Ctx_.MakeName("subquery");
+
+        auto& blocks = Ctx_.GetCurrentBlocks();
+        blocks.emplace_back(BuildSubquery(
+            std::move(source),
+            alias,
+            /* inSubquery = */ Mode_ == NSQLTranslation::ESqlMode::SUBQUERY,
+            /* ensureTupleSize = */ -1,
+            Ctx_.Scoped));
+        blocks.back()->SetLabel(ref);
+
+        return BuildSubqueryRef(blocks.back(), ref, /* tupleIndex = */ -1);
+    }
+
     return result;
 }
 
-TNodePtr TSqlExpression::SmartParenthesis(const TRule_smart_parenthesis& node) {
+bool TSqlExpression::IsTopLevelGroupBy() const {
+    return MaybeUnnamedSmartParenOnTop_ &&
+           SmartParenthesisMode_ == ESmartParenthesis::GroupBy;
+}
+
+TSourcePtr TSqlExpression::LangVersionedSubSelect(TSourcePtr source) {
+    if (!source) {
+        return nullptr;
+    }
+
+    if (!IsSourceAllowed_ && !IsBackwardCompatibleFeatureAvailable(MakeLangVersion(2025, 04))) {
+        Ctx_.Error(source->GetPos()) << "Inline subquery is not available before 2025.04";
+        return nullptr;
+    }
+
+    return source;
+}
+
+TNodePtr TSqlExpression::SelectSubExpr(const TRule_select_subexpr& node) {
+    TNodePtr result;
+    if (IsOnlySubExpr(node)) {
+        result = SelectOrExpr(node.GetRule_select_subexpr_intersect1()
+                                  .GetRule_select_or_expr1());
+    } else {
+        result = LangVersionedSubSelect(TSqlSelect(Ctx_, Mode_).BuildSubSelect(node));
+    }
+
+    if (TSourcePtr source = MoveOutIfSource(result)) {
+        if (IsSourceAllowed_ || IsSubqueryRef(source)) {
+            return source;
+        }
+
+        source->UseAsInner();
+        result = BuildSourceNode(source->GetPos(), std::move(source));
+    }
+
+    return result;
+}
+
+TNodePtr TSqlExpression::SelectOrExpr(const TRule_select_or_expr& node) {
+    switch (node.Alt_case()) {
+    case NSQLv1Generated::TRule_select_or_expr::kAltSelectOrExpr1: {
+        const auto& select_kind = node.GetAlt_select_or_expr1().GetRule_select_kind_partial1();
+        TSourcePtr source = TSqlSelect(Ctx_, Mode_).BuildSubSelect(select_kind);
+        return LangVersionedSubSelect(std::move(source));
+    }
+    case NSQLv1Generated::TRule_select_or_expr::kAltSelectOrExpr2:
+        return TupleOrExpr(node.GetAlt_select_or_expr2().GetRule_tuple_or_expr1());
+    case NSQLv1Generated::TRule_select_or_expr::ALT_NOT_SET:
+        Y_ABORT("You should change implementation according to grammar changes");
+    }
+}
+
+TNodePtr TSqlExpression::TupleOrExpr(const TRule_tuple_or_expr& node) {
     TVector<TNodePtr> exprs;
-    Token(node.GetToken1());
     const TPosition pos(Ctx_.Pos());
-    const bool isTuple = node.HasBlock3();
+
+    const bool isTuple = node.HasBlock4();
+
     bool expectTuple = SmartParenthesisMode_ == ESmartParenthesis::InStatement;
     EExpr mode = EExpr::Regular;
     if (SmartParenthesisMode_ == ESmartParenthesis::SqlLambdaParams) {
         mode = EExpr::SqlLambdaParams;
         expectTuple = true;
     }
-    if (node.HasBlock2() && !NamedExprList(node.GetBlock2().GetRule_named_expr_list1(), exprs, mode)) {
-        return {};
-    }
 
-    bool topLevelGroupBy = MaybeUnnamedSmartParenOnTop_ && SmartParenthesisMode_ == ESmartParenthesis::GroupBy;
+    {
+        const auto& head = node.GetRule_expr1();
+        const auto* headName = node.HasBlock2() ? &node.GetBlock2().GetRule_an_id_or_type2() : nullptr;
+
+        bool isDefinitelyTuple = isTuple || expectTuple || !node.GetBlock3().empty();
+        if ((!headName && !isDefinitelyTuple) || IsSelect(head)) {
+            return BuildSourceOrNode(head);
+        }
+
+        exprs.emplace_back(NamedExpr(head, headName, mode));
+        if (!exprs.back()) {
+            return nullptr;
+        }
+
+        for (const auto& item : node.GetBlock3()) {
+            exprs.emplace_back(NamedExpr(item.GetRule_named_expr2(), mode));
+            if (!exprs.back()) {
+                return nullptr;
+            }
+        }
+    }
 
     bool hasAliases = false;
     bool hasUnnamed = false;
@@ -2338,19 +2442,16 @@ TNodePtr TSqlExpression::SmartParenthesis(const TRule_smart_parenthesis& node) {
         } else {
             hasUnnamed = true;
         }
-        if (hasAliases && hasUnnamed && !topLevelGroupBy) {
+        if (hasAliases && hasUnnamed && !IsTopLevelGroupBy()) {
             Ctx_.IncrementMonCounter("sql_errors", "AnonymousStructMembers");
             Ctx_.Error(pos) << "Structure does not allow anonymous members";
             return nullptr;
         }
     }
-    if (exprs.size() == 1 && hasUnnamed && !isTuple && !expectTuple) {
-        return exprs.back();
-    }
-    if (topLevelGroupBy) {
+    if (IsTopLevelGroupBy()) {
         if (isTuple) {
             Ctx_.IncrementMonCounter("sql_errors", "SimpleTupleInGroupBy");
-            Token(node.GetBlock3().GetToken1());
+            Token(node.GetBlock4().GetToken1());
             Ctx_.Error() << "Unexpected trailing comma in grouping elements list";
             return nullptr;
         }
@@ -2361,4 +2462,24 @@ TNodePtr TSqlExpression::SmartParenthesis(const TRule_smart_parenthesis& node) {
     return (hasUnnamed || expectTuple || exprs.size() == 0) ? BuildTuple(pos, exprs) : BuildStructure(pos, exprs);
 }
 
+TNodePtr TSqlExpression::EmptyTuple() {
+    if (IsTopLevelGroupBy()) {
+        return BuildListOfNamedNodes(Ctx_.Pos(), TVector<TNodePtr>{});
+    }
+
+    return BuildTuple(Ctx_.Pos(), TVector<TNodePtr>{});
+}
+
+TNodePtr TSqlExpression::SmartParenthesis(const TRule_smart_parenthesis& node) {
+    Token(node.GetToken1());
+    switch (node.GetBlock2().GetAltCase()) {
+    case NSQLv1Generated::TRule_smart_parenthesis_TBlock2::kAlt1:
+        return SelectSubExpr(node.GetBlock2().GetAlt1().GetRule_select_subexpr1());
+    case NSQLv1Generated::TRule_smart_parenthesis_TBlock2::kAlt2:
+        return EmptyTuple();
+    case NSQLv1Generated::TRule_smart_parenthesis_TBlock2::ALT_NOT_SET:
+        Y_ABORT("You should change implementation according to grammar changes");
+    }
+}
+
 } // namespace NSQLTranslationV1
diff --git a/yql/essentials/sql/v1/sql_expression.h b/yql/essentials/sql/v1/sql_expression.h
index 794f5c5db3f..3751d80aefd 100644
--- a/yql/essentials/sql/v1/sql_expression.h
+++ b/yql/essentials/sql/v1/sql_expression.h
@@ -21,8 +21,11 @@ public:
     {
     }
 
+    TNodePtr BuildSourceOrNode(const TRule_expr& node);
     TNodePtr Build(const TRule_expr& node);
     TNodePtr Build(const TRule_lambda_or_parameter& node);
+    TSourcePtr BuildSource(const TRule_select_or_expr& node);
+    TNodePtr BuildSourceOrNode(const TRule_smart_parenthesis& node);
 
     void SetSmartParenthesisMode(ESmartParenthesis mode) {
         SmartParenthesisMode_ = mode;
@@ -127,10 +130,17 @@ private:
         Ctx_.Error(tail.Pos) << "Unexpected token '?' at the end of expression";
     }
 
+    bool IsTopLevelGroupBy() const;
+    TSourcePtr LangVersionedSubSelect(TSourcePtr source);
+    TNodePtr SelectSubExpr(const TRule_select_subexpr& node);
+    TNodePtr SelectOrExpr(const TRule_select_or_expr& node);
+    TNodePtr TupleOrExpr(const TRule_tuple_or_expr& node);
+    TNodePtr EmptyTuple();
     TNodePtr SmartParenthesis(const TRule_smart_parenthesis& node);
 
     ESmartParenthesis SmartParenthesisMode_ = ESmartParenthesis::Default;
     bool MaybeUnnamedSmartParenOnTop_ = true;
+    bool IsSourceAllowed_ = true;
 
     THashMap<TString, TNodePtr> ExprShortcuts_;
 };
diff --git a/yql/essentials/sql/v1/sql_query.cpp b/yql/essentials/sql/v1/sql_query.cpp
index 5de1a6de2ef..3f31663f8b1 100644
--- a/yql/essentials/sql/v1/sql_query.cpp
+++ b/yql/essentials/sql/v1/sql_query.cpp
@@ -3943,30 +3943,18 @@ TSourcePtr TSqlQuery::Build(const TRule_set_clause_list& stmt) {
 TSourcePtr TSqlQuery::Build(const TRule_multiple_column_assignment& stmt) {
     TVector<TString> targetList;
     FillTargetList(*this, stmt.GetRule_set_target_list1(), targetList);
-    auto simpleValuesNode = stmt.GetRule_simple_values_source4();
+
     const TPosition pos(Ctx_.Pos());
-    switch (simpleValuesNode.Alt_case()) {
-        case TRule_simple_values_source::kAltSimpleValuesSource1: {
-            TVector<TNodePtr> values;
-            TSqlExpression sqlExpr(Ctx_, Mode_);
-            if (!ExprList(sqlExpr, values, simpleValuesNode.GetAlt_simple_values_source1().GetRule_expr_list1())) {
-                return nullptr;
-            }
-            return BuildUpdateValues(pos, targetList, values);
-        }
-        case TRule_simple_values_source::kAltSimpleValuesSource2: {
-            TSqlSelect select(Ctx_, Mode_);
-            TPosition selectPos;
-            auto source = select.Build(simpleValuesNode.GetAlt_simple_values_source2().GetRule_select_stmt1(), selectPos);
-            if (!source) {
-                return nullptr;
-            }
-            return BuildWriteValues(pos, "UPDATE", targetList, std::move(source));
-        }
-        case TRule_simple_values_source::ALT_NOT_SET:
-            Ctx_.IncrementMonCounter("sql_errors", "UnknownSimpleValuesSourceAlt");
-            AltNotImplemented("simple_values_source", simpleValuesNode);
-            return nullptr;
+    auto parenthesis = stmt.GetRule_smart_parenthesis3();
+
+    TNodePtr node = TSqlExpression(Ctx_, Mode_).BuildSourceOrNode(parenthesis);
+    if (TSourcePtr source = MoveOutIfSource(node)) {
+        return BuildWriteValues(pos, "UPDATE", targetList, std::move(source));
+    } else if (TTupleNode* tuple = dynamic_cast<TTupleNode*>(node.Get())) {
+        return BuildUpdateValues(pos, targetList, tuple->Elements());
+    } else {
+        Error() << "Expected source or tuple, but got something else";
+        return nullptr;
     }
 }
 
diff --git a/yql/essentials/sql/v1/sql_select.cpp b/yql/essentials/sql/v1/sql_select.cpp
index ea769eb5385..3d84bc9f532 100644
--- a/yql/essentials/sql/v1/sql_select.cpp
+++ b/yql/essentials/sql/v1/sql_select.cpp
@@ -1383,15 +1383,36 @@ bool TSqlSelect::IsAllQualifiedOp(const TRule& node) {
 
 template <typename TRule>
     requires std::same_as<TRule, TRule_select_stmt> ||
-             std::same_as<TRule, TRule_select_unparenthesized_stmt>
+             std::same_as<TRule, TRule_select_unparenthesized_stmt> ||
+             std::same_as<TRule, TRule_select_subexpr>
 TSourcePtr TSqlSelect::BuildStmt(const TRule& node, TPosition& pos) {
     TBuildExtra extra;
-    auto result = BuildUnionException(node, pos, extra);
-    pos = extra.FirstPos;
+    TSourcePtr result = BuildUnionException(node, pos, extra);
+    return BuildStmt(std::move(result), std::move(extra));
+}
+
+TSourcePtr TSqlSelect::BuildSubSelect(const TRule_select_kind_partial& node) {
+    TColumnRefScope scope(Ctx_, EColumnRefState::Deny);
+
+    TPosition position;
+    TSelectKindResult result = SelectKind(node, position, /* placement = */ Nothing());
+
+    TBuildExtra extra = {
+        .First = result,
+        .FirstPos = position,
+        .Last = result,
+    };
+
+    return BuildStmt(std::move(result.Source), std::move(extra));
+}
+
+TSourcePtr TSqlSelect::BuildStmt(TSourcePtr result, TBuildExtra extra) {
     if (!result) {
         return nullptr;
     }
 
+    TPosition pos = extra.FirstPos;
+
     if (extra.First.Source == extra.Last.Source) {
         return result;
     }
@@ -1447,7 +1468,8 @@ TSourcePtr TSqlSelect::BuildStmt(const TRule& node, TPosition& pos) {
 
 template <typename TRule>
     requires std::same_as<TRule, TRule_select_stmt> ||
-             std::same_as<TRule, TRule_select_unparenthesized_stmt>
+             std::same_as<TRule, TRule_select_unparenthesized_stmt> ||
+             std::same_as<TRule, TRule_select_subexpr>
 TSourcePtr TSqlSelect::BuildUnionException(const TRule& node, TPosition& pos, TSqlSelect::TBuildExtra& extra) {
     const TSelectKindPlacement firstPlacement = {
         .IsFirstInSelectOp = true,
@@ -1459,6 +1481,8 @@ TSourcePtr TSqlSelect::BuildUnionException(const TRule& node, TPosition& pos, TS
         first = BuildIntersection(node.GetRule_select_stmt_intersect1(), pos, firstPlacement, extra);
     } else if constexpr (std::is_same_v<TRule, TRule_select_unparenthesized_stmt>) {
         first = BuildIntersection(node.GetRule_select_unparenthesized_stmt_intersect1(), pos, firstPlacement, extra);
+    } else if constexpr (std::is_same_v<TRule, TRule_select_subexpr>) {
+        first = BuildIntersection(node.GetRule_select_subexpr_intersect1(), pos, firstPlacement, extra);
     } else {
         static_assert(false, "Change implementation according to grammar changes.");
     }
@@ -1503,7 +1527,14 @@ TSourcePtr TSqlSelect::BuildUnionException(const TRule& node, TPosition& pos, TS
             .IsFirstInSelectOp = false,
             .IsLastInSelectOp = (i + 1 == tail.size()),
         };
-        TSourcePtr next = BuildIntersection(nextBlock.GetRule_select_stmt_intersect2(), pos, nextPlacement, extra);
+
+        TSourcePtr next;
+        if constexpr (std::is_same_v<TRule, TRule_select_subexpr>) {
+            next = BuildIntersection(nextBlock.GetRule_select_subexpr_intersect2(), pos, nextPlacement, extra);
+        } else {
+            next = BuildIntersection(nextBlock.GetRule_select_stmt_intersect2(), pos, nextPlacement, extra);
+        }
+
         if (!next) {
             return nullptr;
         }
@@ -1541,7 +1572,8 @@ TSourcePtr TSqlSelect::BuildUnionException(const TRule& node, TPosition& pos, TS
 
 template <typename TRule>
     requires std::same_as<TRule, TRule_select_stmt_intersect> ||
-             std::same_as<TRule, TRule_select_unparenthesized_stmt_intersect>
+             std::same_as<TRule, TRule_select_unparenthesized_stmt_intersect> ||
+             std::same_as<TRule, TRule_select_subexpr_intersect>
 TSourcePtr TSqlSelect::BuildIntersection(
     const TRule& node,
     TPosition& pos,
@@ -1558,6 +1590,8 @@ TSourcePtr TSqlSelect::BuildIntersection(
         first = BuildAtom(node.GetRule_select_kind_parenthesis1(), pos, firstPlacement, extra);
     } else if constexpr (std::is_same_v<TRule, TRule_select_unparenthesized_stmt_intersect>) {
         first = BuildAtom(node.GetRule_select_kind_partial1(), pos, firstPlacement, extra);
+    } else if constexpr (std::is_same_v<TRule, TRule_select_subexpr_intersect>) {
+        first = BuildAtom(node.GetRule_select_or_expr1(), pos, firstPlacement, extra);
     } else {
         static_assert(false, "Change implementation according to grammar changes.");
     }
@@ -1587,7 +1621,14 @@ TSourcePtr TSqlSelect::BuildIntersection(
             .IsFirstInSelectOp = false,
             .IsLastInSelectOp = (i + 1 == tail.size()) && placement.IsLastInSelectOp,
         };
-        TSelectKindResult next = BuildAtom(nextBlock.GetRule_select_kind_parenthesis2(), pos, nextPlacement, extra);
+
+        TSelectKindResult next;
+        if constexpr (std::is_same_v<TRule, TRule_select_subexpr_intersect>) {
+            next = BuildAtom(nextBlock.GetRule_select_or_expr2(), pos, nextPlacement, extra);
+        } else {
+            next = BuildAtom(nextBlock.GetRule_select_kind_parenthesis2(), pos, nextPlacement, extra);
+        }
+
         if (!next) {
             return nullptr;
         }
@@ -1601,7 +1642,8 @@ TSourcePtr TSqlSelect::BuildIntersection(
 
 template <typename TRule>
     requires std::same_as<TRule, TRule_select_kind_parenthesis> ||
-             std::same_as<TRule, TRule_select_kind_partial>
+             std::same_as<TRule, TRule_select_kind_partial> ||
+             std::same_as<TRule, TRule_select_or_expr>
 TSqlSelect::TSelectKindResult TSqlSelect::BuildAtom(
     const TRule& node,
     TPosition& pos,
@@ -1609,7 +1651,21 @@ TSqlSelect::TSelectKindResult TSqlSelect::BuildAtom(
     TBuildExtra& extra)
 {
     TSqlSelect::TSelectKindResult result;
-    if (placement.IsFirstInSelectOp && placement.IsLastInSelectOp) {
+    if constexpr (std::is_same_v<TRule, TRule_select_or_expr>) {
+        switch (node.Alt_case()) {
+        case NSQLv1Generated::TRule_select_or_expr::kAltSelectOrExpr1: {
+            const auto& select_kind = node.GetAlt_select_or_expr1().GetRule_select_kind_partial1();
+            result = SelectKind(select_kind, pos, placement);
+            break;
+        }
+        case NSQLv1Generated::TRule_select_or_expr::kAltSelectOrExpr2: {
+            result.Source = TSqlExpression(Ctx_, Mode_).BuildSource(node);
+            break;
+        }
+        case NSQLv1Generated::TRule_select_or_expr::ALT_NOT_SET:
+            Y_ABORT("You should change implementation according to grammar changes");
+        }
+    } else if (placement.IsFirstInSelectOp && placement.IsLastInSelectOp) {
         result = SelectKind(node, pos, /* placement = */ Nothing());
     } else {
         result = SelectKind(node, pos, placement);
@@ -1633,4 +1689,10 @@ TSourcePtr TSqlSelect::Build(const TRule_select_unparenthesized_stmt& node, TPos
     return BuildStmt(node, selectPos);
 }
 
+TSourcePtr TSqlSelect::BuildSubSelect(const TRule_select_subexpr& node) {
+    TColumnRefScope scope(Ctx_, EColumnRefState::Deny);
+    TPosition pos;
+    return BuildStmt(node, pos);
+}
+
 } // namespace NSQLTranslationV1
diff --git a/yql/essentials/sql/v1/sql_select.h b/yql/essentials/sql/v1/sql_select.h
index f4097396745..9bd553d49ab 100644
--- a/yql/essentials/sql/v1/sql_select.h
+++ b/yql/essentials/sql/v1/sql_select.h
@@ -16,6 +16,8 @@ public:
 
     TSourcePtr Build(const TRule_select_stmt& node, TPosition& selectPos);
     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);
 
 private:
     bool SelectTerm(TVector<TNodePtr>& terms, const TRule_result_column& node);
@@ -75,22 +77,30 @@ private:
 
     template <typename TRule>
         requires std::same_as<TRule, TRule_select_stmt> ||
-                 std::same_as<TRule, TRule_select_unparenthesized_stmt>
+                 std::same_as<TRule, TRule_select_unparenthesized_stmt> ||
+                 std::same_as<TRule, TRule_select_subexpr>
     TSourcePtr BuildStmt(const TRule& node, TPosition& pos);
 
+    TSourcePtr BuildStmt(const TRule_select_kind_partial& node);
+
+    TSourcePtr BuildStmt(TSourcePtr result, TBuildExtra extra);
+
     template <typename TRule>
         requires std::same_as<TRule, TRule_select_stmt> ||
-                 std::same_as<TRule, TRule_select_unparenthesized_stmt>
+                 std::same_as<TRule, TRule_select_unparenthesized_stmt> ||
+                 std::same_as<TRule, TRule_select_subexpr>
     TSourcePtr BuildUnionException(const TRule& node, TPosition& pos, TBuildExtra& extra);
 
     template <typename TRule>
         requires std::same_as<TRule, TRule_select_stmt_intersect> ||
-                 std::same_as<TRule, TRule_select_unparenthesized_stmt_intersect>
+                 std::same_as<TRule, TRule_select_unparenthesized_stmt_intersect> ||
+                 std::same_as<TRule, TRule_select_subexpr_intersect>
     TSourcePtr BuildIntersection(const TRule& node, TPosition& pos, TSelectKindPlacement placement, TBuildExtra& extra);
 
     template <typename TRule>
         requires std::same_as<TRule, TRule_select_kind_parenthesis> ||
-                 std::same_as<TRule, TRule_select_kind_partial>
+                 std::same_as<TRule, TRule_select_kind_partial> ||
+                 std::same_as<TRule, TRule_select_or_expr>
     TSelectKindResult BuildAtom(const TRule& node, TPosition& pos, TSelectKindPlacement placement, TBuildExtra& extra);
 
     TSelectKindResult SelectKind(const TRule_select_kind& node, TPosition& selectPos, TMaybe<TSelectKindPlacement> placement);
diff --git a/yql/essentials/sql/v1/sql_translation.cpp b/yql/essentials/sql/v1/sql_translation.cpp
index 40d7a88776b..81d177cb51b 100644
--- a/yql/essentials/sql/v1/sql_translation.cpp
+++ b/yql/essentials/sql/v1/sql_translation.cpp
@@ -944,29 +944,39 @@ TTableHints GetTableFuncHints(TStringBuf funcName) {
     return res;
 }
 
-
-TNodePtr TSqlTranslation::NamedExpr(const TRule_named_expr& node, EExpr exprMode) {
+TNodePtr TSqlTranslation::NamedExpr(
+    const TRule_expr& exprTree,
+    const TRule_an_id_or_type* nameTree,
+    EExpr exprMode)
+{
     TSqlExpression expr(Ctx_, Mode_);
     if (exprMode == EExpr::GroupBy) {
         expr.SetSmartParenthesisMode(TSqlExpression::ESmartParenthesis::GroupBy);
     } else if (exprMode == EExpr::SqlLambdaParams) {
         expr.SetSmartParenthesisMode(TSqlExpression::ESmartParenthesis::SqlLambdaParams);
     }
-    if (node.HasBlock2()) {
+    if (nameTree) {
         expr.MarkAsNamed();
     }
-    TNodePtr exprNode(expr.Build(node.GetRule_expr1()));
+    TNodePtr exprNode = expr.Build(exprTree);
     if (!exprNode) {
         Ctx_.IncrementMonCounter("sql_errors", "NamedExprInvalid");
         return nullptr;
     }
-    if (node.HasBlock2()) {
+    if (nameTree) {
         exprNode = SafeClone(exprNode);
-        exprNode->SetLabel(Id(node.GetBlock2().GetRule_an_id_or_type2(), *this));
+        exprNode->SetLabel(Id(*nameTree, *this));
     }
     return exprNode;
 }
 
+TNodePtr TSqlTranslation::NamedExpr(const TRule_named_expr& node, EExpr exprMode) {
+    return NamedExpr(
+        node.GetRule_expr1(),
+        (node.HasBlock2() ? &node.GetBlock2().GetRule_an_id_or_type2() : nullptr),
+        exprMode);
+}
+
 bool TSqlTranslation::NamedExprList(const TRule_named_expr_list& node, TVector<TNodePtr>& exprs, EExpr exprMode) {
     exprs.emplace_back(NamedExpr(node.GetRule_named_expr1(), exprMode));
     if (!exprs.back()) {
@@ -3803,8 +3813,7 @@ bool TSqlTranslation::TopicRefImpl(const TRule_topic_ref& node, TTopicRef& resul
 }
 
 TNodePtr TSqlTranslation::NamedNode(const TRule_named_nodes_stmt& rule, TVector<TSymbolNameWithPos>& names) {
-    // named_nodes_stmt: bind_parameter_list EQUALS (expr | subselect_stmt);
-    // subselect_stmt: (LPAREN select_stmt RPAREN | select_unparenthesized_stmt);
+    // named_nodes_stmt: bind_parameter_list EQUALS (expr | select_unparenthesized_stmt);
     if (!BindList(rule.GetRule_bind_parameter_list1(), names)) {
         return {};
     }
@@ -3813,30 +3822,18 @@ TNodePtr TSqlTranslation::NamedNode(const TRule_named_nodes_stmt& rule, TVector<
     switch (rule.GetBlock3().Alt_case()) {
     case TRule_named_nodes_stmt::TBlock3::kAlt1: {
         TSqlExpression expr(Ctx_, Mode_);
-        auto result = expr.Build(rule.GetBlock3().GetAlt1().GetRule_expr1());
+        auto result = expr.BuildSourceOrNode(rule.GetBlock3().GetAlt1().GetRule_expr1());
+        if (TSourcePtr source = MoveOutIfSource(result)) {
+            result = BuildSourceNode(Ctx_.Pos(), std::move(source));
+        }
         return result;
     }
 
     case TRule_named_nodes_stmt::TBlock3::kAlt2:{
-        const auto& subselect_rule = rule.GetBlock3().GetAlt2().GetRule_subselect_stmt1();
+        const auto& subselect_rule = rule.GetBlock3().GetAlt2().GetRule_select_unparenthesized_stmt1();
 
-        TSqlSelect expr(Ctx_, Mode_);
         TPosition pos;
-        TSourcePtr source = nullptr;
-        switch (subselect_rule.GetBlock1().Alt_case()) {
-            case TRule_subselect_stmt::TBlock1::kAlt1:
-                source = expr.Build(subselect_rule.GetBlock1().GetAlt1().GetRule_select_stmt2(), pos);
-                break;
-
-            case TRule_subselect_stmt::TBlock1::kAlt2:
-                source = expr.Build(subselect_rule.GetBlock1().GetAlt2().GetRule_select_unparenthesized_stmt1(), pos);
-                break;
-
-            case TRule_subselect_stmt::TBlock1::ALT_NOT_SET:
-                AltNotImplemented("subselect_stmt", subselect_rule.GetBlock1());
-                Ctx_.IncrementMonCounter("sql_errors", "UnknownNamedNode");
-                return nullptr;
-        }
+        TSourcePtr source = TSqlSelect(Ctx_, Mode_).Build(subselect_rule, pos);
 
         if (!source) {
             return {};
diff --git a/yql/essentials/sql/v1/sql_translation.h b/yql/essentials/sql/v1/sql_translation.h
index 63b961b0dae..6858ee3a588 100644
--- a/yql/essentials/sql/v1/sql_translation.h
+++ b/yql/essentials/sql/v1/sql_translation.h
@@ -135,6 +135,12 @@ protected:
         GroupBy,
         SqlLambdaParams,
     };
+
+    TNodePtr NamedExpr(
+        const TRule_expr& exprTree,
+        const TRule_an_id_or_type* nameTree,
+        EExpr exprMode = EExpr::Regular);
+
     TNodePtr NamedExpr(const TRule_named_expr& node, EExpr exprMode = EExpr::Regular);
     bool NamedExprList(const TRule_named_expr_list& node, TVector<TNodePtr>& exprs, EExpr exprMode = EExpr::Regular);
     bool BindList(const TRule_bind_parameter_list& node, TVector<TSymbolNameWithPos>& bindNames);
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index 3837b892464..d6d86e9954b 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -2516,7 +2516,7 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
             SELECT * FROM $squ2;
             SELECT * FROM $squ3;
         )");
-        UNIT_ASSERT(res.Root);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
     }
 
     Y_UNIT_TEST(SubqueriesJoin) {
@@ -10525,3 +10525,238 @@ return /*Комментарий*/ $x;
         UNIT_ASSERT_VALUES_EQUAL(0, NSQLTranslationV1::GetQueryPosition(query, tokenProto, antlr4));
     }
 }
+
+Y_UNIT_TEST_SUITE(InlineUncorrelatedSubquery) {
+    Y_UNIT_TEST(EmptyTuple) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            SELECT ();
+            SELECT (());
+            SELECT (,);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(ParenthesisedExpression) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            SELECT 1;
+            SELECT (1);
+            SELECT ((1));
+            SELECT (((1)));
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(Tuple) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            SELECT (1,);
+            SELECT (1, 2);
+            SELECT (1, 2, 3);
+            SELECT (1, 2, 3, 4);
+            SELECT ((1, 2));
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(Struct) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            SELECT (1 AS a);
+            SELECT (1 AS a, 2 AS b);
+            SELECT (1 AS a, 2 AS b, 3 AS c);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(Lambda) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            SELECT (($a) -> { RETURN $a; })(1);
+            SELECT (($a, $b) -> { RETURN $a + $b; })(1, 2);
+            SELECT (($a, $b, $c) -> { RETURN $a + $b + $c; })(1, 2, 3);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(AtProjection) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT (SELECT 1);
+            SELECT (SELECT (SELECT 1));
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(AtExpression) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT 1 + (SELECT 1);
+            SELECT (SELECT 1) + 1;
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(UnionParenthesis) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT ( SELECT 1  UNION  SELECT 1);
+            SELECT ( SELECT 1  UNION (SELECT 1));
+            SELECT ((SELECT 1) UNION  SELECT 1);
+            SELECT ((SELECT 1) UNION (SELECT 1));
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(IntersectParenthesis) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT ( SELECT 1  INTERSECT  SELECT 1);
+            SELECT ( SELECT 1  INTERSECT (SELECT 1));
+            SELECT ((SELECT 1) INTERSECT  SELECT 1);
+            SELECT ((SELECT 1) INTERSECT (SELECT 1));
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(UnionIntersect) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT (SELECT 1 UNION     SELECT 1 UNION     SELECT 1);
+            SELECT (SELECT 1 UNION     SELECT 1 INTERSECT SELECT 1);
+            SELECT (SELECT 1 INTERSECT SELECT 1 UNION     SELECT 1);
+            SELECT (SELECT 1 INTERSECT SELECT 1 INTERSECT SELECT 1);
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(ScalarExpressionUnion) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT ((2 + 2) UNION (2 * 2));
+        )sql", settings);
+        UNIT_ASSERT(!res.IsOk());
+        UNIT_ASSERT_STRING_CONTAINS(
+            res.Issues.ToOneLineString(),
+            "2:24: Error: Expected SELECT/PROCESS/REDUCE statement");
+    }
+
+    Y_UNIT_TEST(OrderByIgnorance1) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT (SELECT * FROM (SELECT * FROM (SELECT 1 AS x UNION SELECT 2 AS x) ORDER BY x));
+
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+        UNIT_ASSERT_STRING_CONTAINS(
+            res.Issues.ToOneLineString(),
+            "ORDER BY without LIMIT in subquery will be ignored");
+
+        TWordCountHive stat = {{TString("Sort"), 0}};
+        VerifyProgram(res, stat);
+        UNIT_ASSERT_VALUES_EQUAL(stat["Sort"], 0);
+    }
+
+    Y_UNIT_TEST(OrderByIgnorance2) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = NYql::MakeLangVersion(2025, 4);
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(R"sql(
+            SELECT (SELECT * FROM (SELECT 1 AS x UNION SELECT 2 AS x) ORDER BY x);
+        )sql", settings);
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+        UNIT_ASSERT_STRING_CONTAINS(
+            res.Issues.ToOneLineString(),
+            "ORDER BY without LIMIT in subquery will be ignored");
+
+        TWordCountHive stat = {{TString("Sort"), 0}};
+        VerifyProgram(res, stat);
+        UNIT_ASSERT_VALUES_EQUAL(stat["Sort"], 0);
+    }
+
+    Y_UNIT_TEST(InSubquery) {
+        NYql::TAstParseResult res;
+
+        res = SqlToYql(R"sql(
+            SELECT 1 IN (SELECT 1);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+
+        res = SqlToYql(R"sql(
+            SELECT * FROM (SELECT 1 AS x) WHERE x IN (SELECT 1);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+
+        res = SqlToYql(R"sql(
+            SELECT * FROM (SELECT 1 AS x) WHERE x IN ((SELECT 1));
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(GroupByUnit) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            SELECT * FROM (SELECT 1) GROUP BY ();
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(NamedNodeUnion) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            $a = (SELECT 1);
+            $b = (SELECT 1);
+            $x = ($a UNION $b);
+            SELECT * FROM $x;
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(NamedNodeExpr) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            $a = 1;            SELECT $a;
+            $b = SELECT 1;     SELECT $b;
+            $c = (SELECT 1);   SELECT $c;
+            $d = ((SELECT 1)); SELECT $d;
+                               SELECT $b + 1;
+                               SELECT ($b);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToString());
+    }
+
+    Y_UNIT_TEST(NamedNodeProcess) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            $a = SELECT 1, 2;
+            $a = PROCESS $a;
+            SELECT $a;
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToString());
+    }
+
+    Y_UNIT_TEST(SubqueryDeduplication) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            DEFINE SUBQUERY $sub() AS
+                SELECT * FROM (SELECT 1);
+            END DEFINE;
+            SELECT * FROM $sub();
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+
+    Y_UNIT_TEST(NamedNode) {
+        NYql::TAstParseResult res = SqlToYql(R"sql(
+            $x = (SELECT 1 AS x);
+            SELECT 1 < $x;
+            SELECT 1 < ($x);
+        )sql");
+        UNIT_ASSERT_C(res.IsOk(), res.Issues.ToOneLineString());
+    }
+}
diff --git a/yql/essentials/tests/sql/minirun/part0/canondata/result.json b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
index 5e3880d6769..92663561e1b 100644
--- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
@@ -1611,6 +1611,48 @@
             "uri": "https://{canondata_backend}/1937367/129cce9c3960b60552c52a20100a5a905edd36fc/resource.tar.gz#test.test_side_effects-logical_bools_skipped-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[subselect-aggregate_over-default.txt-Debug]": [
+        {
+            "checksum": "606386651aefb1eabfe8751229b18525",
+            "size": 3416,
+            "uri": "https://{canondata_backend}/1784826/add1c2b07fe81b8c1e813913fe820100c8e6eb4e/resource.tar.gz#test.test_subselect-aggregate_over-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-aggregate_over-default.txt-Results]": [
+        {
+            "checksum": "4464d680db00468935deb5adb2b69e58",
+            "size": 5176,
+            "uri": "https://{canondata_backend}/1784826/add1c2b07fe81b8c1e813913fe820100c8e6eb4e/resource.tar.gz#test.test_subselect-aggregate_over-default.txt-Results_/results.txt"
+        }
+    ],
+    "test.test[subselect-having-default.txt-Debug]": [
+        {
+            "checksum": "bc154073eca159d685bbb12c255dacd5",
+            "size": 2436,
+            "uri": "https://{canondata_backend}/1925821/04cf2caa9b448793f65bdc6e39aad672f543f8be/resource.tar.gz#test.test_subselect-having-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-having-default.txt-Results]": [
+        {
+            "checksum": "222f12bc7e743b3fd54853e408ac2a24",
+            "size": 3251,
+            "uri": "https://{canondata_backend}/1925821/04cf2caa9b448793f65bdc6e39aad672f543f8be/resource.tar.gz#test.test_subselect-having-default.txt-Results_/results.txt"
+        }
+    ],
+    "test.test[subselect-where_named_node-default.txt-Debug]": [
+        {
+            "checksum": "defa97dd1b2b7ea02420da3d7694d377",
+            "size": 1477,
+            "uri": "https://{canondata_backend}/1936842/147b7134c23d5bb546271f2abe563dfa97d08781/resource.tar.gz#test.test_subselect-where_named_node-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-where_named_node-default.txt-Results]": [
+        {
+            "checksum": "d04cff341495a7c63a3bc924ad55726c",
+            "size": 3230,
+            "uri": "https://{canondata_backend}/1936842/147b7134c23d5bb546271f2abe563dfa97d08781/resource.tar.gz#test.test_subselect-where_named_node-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[udf-udf_with_settings--Debug]": [
         {
             "checksum": "a7b364b692c35af7b468f42ab6a7b43f",
diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 90e86721537..6994021845f 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -955,7 +955,7 @@
         {
             "checksum": "a6083e95a2a0ef0db3022a6481b79ab2",
             "size": 374,
-            "uri": "https://{canondata_backend}/1130705/0f717596bbab92f11dfd78d28fde4a2785f033ef/resource.tar.gz#test.test_linear-mutdict_copy-default.txt-Debug_/opt.yql"
+            "uri": "https://{canondata_backend}/1923547/0f717596bbab92f11dfd78d28fde4a2785f033ef/resource.tar.gz#test.test_linear-mutdict_copy-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[linear-mutdict_copy-default.txt-Results]": [
@@ -1531,6 +1531,20 @@
             "uri": "https://{canondata_backend}/1936842/f457790bc6a954bcb4a6a17ab144af05c57a7754/resource.tar.gz#test.test_select-digits--Results_/results.txt"
         }
     ],
+    "test.test[select_op-select_op_precedence_named_node-default.txt-Debug]": [
+        {
+            "checksum": "3cc5644e63d6d4d4601132f97c177a71",
+            "size": 2350,
+            "uri": "https://{canondata_backend}/1775319/60b6d8d1eea951b7a0bb833ba189ada82576641b/resource.tar.gz#test.test_select_op-select_op_precedence_named_node-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[select_op-select_op_precedence_named_node-default.txt-Results]": [
+        {
+            "checksum": "c0efe7c81a642f898ef8a9edaa9744eb",
+            "size": 2355,
+            "uri": "https://{canondata_backend}/1775319/60b6d8d1eea951b7a0bb833ba189ada82576641b/resource.tar.gz#test.test_select_op-select_op_precedence_named_node-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[seq_mode-shared_named_expr-default.txt-Debug]": [
         {
             "checksum": "6de9752b20a85eb7d5c4b1508dfd607d",
diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
index 43ad8e8356b..22871af88c9 100644
--- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
@@ -1555,6 +1555,20 @@
             "uri": "https://{canondata_backend}/1903885/cded149c62f5a6fd23e6f6d1687363c458e88b9b/resource.tar.gz#test.test_select-unary_op_interval-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[subselect-aggregate-default.txt-Debug]": [
+        {
+            "checksum": "940a87f9014f6f29192592e003a9e1bd",
+            "size": 2594,
+            "uri": "https://{canondata_backend}/1775059/f3a7cf026cbcbe29d7b02e81e0dd213db47fb2b4/resource.tar.gz#test.test_subselect-aggregate-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-aggregate-default.txt-Results]": [
+        {
+            "checksum": "91560dcf35e4c471d19f5a80d4ad07bb",
+            "size": 3524,
+            "uri": "https://{canondata_backend}/1775059/f3a7cf026cbcbe29d7b02e81e0dd213db47fb2b4/resource.tar.gz#test.test_subselect-aggregate-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[udf-complex_return_type--Debug]": [
         {
             "checksum": "b1dc51e1c2d1b8005c435896dc18fabe",
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index 120546d662c..b412c35851f 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -1295,6 +1295,48 @@
             "uri": "https://{canondata_backend}/1946324/99e40d03148e2f5db80e78803a41f37aeb697296/resource.tar.gz#test.test_select-result_label-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[subselect-partition_by-default.txt-Debug]": [
+        {
+            "checksum": "3db193f3cc8ff114a4ba987cf4ce4a2f",
+            "size": 2779,
+            "uri": "https://{canondata_backend}/1937001/1a8479476498108a8ea141894a0e564a74ce8ff3/resource.tar.gz#test.test_subselect-partition_by-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-partition_by-default.txt-Results]": [
+        {
+            "checksum": "51d305defc4153343d3bae685cce9712",
+            "size": 3922,
+            "uri": "https://{canondata_backend}/1937001/1a8479476498108a8ea141894a0e564a74ce8ff3/resource.tar.gz#test.test_subselect-partition_by-default.txt-Results_/results.txt"
+        }
+    ],
+    "test.test[subselect-projection-default.txt-Debug]": [
+        {
+            "checksum": "56f1254308c9c748e85a56f8dcd32b44",
+            "size": 1360,
+            "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-projection-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-projection-default.txt-Results]": [
+        {
+            "checksum": "9e682a9fa784c6f2dc5cb2cdc861b95c",
+            "size": 2641,
+            "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-projection-default.txt-Results_/results.txt"
+        }
+    ],
+    "test.test[subselect-where-default.txt-Debug]": [
+        {
+            "checksum": "0871dde5618bcfcd782de5385357d6ae",
+            "size": 1863,
+            "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-where-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-where-default.txt-Results]": [
+        {
+            "checksum": "d04cff341495a7c63a3bc924ad55726c",
+            "size": 3230,
+            "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-where-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[union-union_mix-default.txt-Debug]": [
         {
             "checksum": "f98189d19a514e38c537ad2190945414",
diff --git a/yql/essentials/tests/sql/minirun/part5/canondata/result.json b/yql/essentials/tests/sql/minirun/part5/canondata/result.json
index 67996d579cf..25d56e4e99a 100644
--- a/yql/essentials/tests/sql/minirun/part5/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part5/canondata/result.json
@@ -1943,6 +1943,34 @@
             "uri": "https://{canondata_backend}/1900335/2e7a3a0284dd25621ff685fc5c99cf0aa70fd6a5/resource.tar.gz#test.test_side_effects-x_not_x-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[subselect-limit-default.txt-Debug]": [
+        {
+            "checksum": "de86d4e45aba6e09a5162523125ace0b",
+            "size": 1813,
+            "uri": "https://{canondata_backend}/1871182/4bf1926730e8ab6d22db16bd5d230ab2169a7e96/resource.tar.gz#test.test_subselect-limit-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-limit-default.txt-Results]": [
+        {
+            "checksum": "8d7e718bf99968831d164e7607e8a3fd",
+            "size": 4714,
+            "uri": "https://{canondata_backend}/1871182/4bf1926730e8ab6d22db16bd5d230ab2169a7e96/resource.tar.gz#test.test_subselect-limit-default.txt-Results_/results.txt"
+        }
+    ],
+    "test.test[subselect-union-default.txt-Debug]": [
+        {
+            "checksum": "def1a0d99f02961b9f368ea19575da7f",
+            "size": 1686,
+            "uri": "https://{canondata_backend}/1781765/6864694787646cf22ed1568cf6a00daadd1d8609/resource.tar.gz#test.test_subselect-union-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-union-default.txt-Results]": [
+        {
+            "checksum": "dccec048b5008170c8a678a3def94f7a",
+            "size": 4536,
+            "uri": "https://{canondata_backend}/1781765/6864694787646cf22ed1568cf6a00daadd1d8609/resource.tar.gz#test.test_subselect-union-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[udf-depends2--Debug]": [
         {
             "checksum": "a4995c8a32627863503a6786da4f0d19",
diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
index 3d6311a2592..aa432ef0181 100644
--- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
@@ -1440,6 +1440,23 @@
             "uri": "https://{canondata_backend}/1937367/671e3ad35d46785ff29a521b44f889ed7a487cc7/resource.tar.gz#test.test_side_effects-logical_bools_unreachable-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[subselect-flatten_by-default.txt-Debug]": [
+        {
+            "checksum": "fedad718a56608b20b47747f0ac927c1",
+            "size": 2342,
+            "uri": "https://{canondata_backend}/1871102/53657df02c932e68f816dd09e0a344dbb19d715d/resource.tar.gz#test.test_subselect-flatten_by-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-flatten_by-default.txt-Results]": [
+        {
+            "checksum": "1bc9f8373b6e7b0b8166bda77d801ede",
+            "size": 9530,
+            "uri": "https://{canondata_backend}/1871102/53657df02c932e68f816dd09e0a344dbb19d715d/resource.tar.gz#test.test_subselect-flatten_by-default.txt-Results_/results.txt"
+        },
+        {
+            "uri": "file://test.test_subselect-flatten_by-default.txt-Results_/extracted"
+        }
+    ],
     "test.test[union-union_positional_mix-default.txt-Debug]": [
         {
             "checksum": "35839a396fdaff3806b0a6117135e8b7",
diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/test.test_subselect-flatten_by-default.txt-Results_/extracted b/yql/essentials/tests/sql/minirun/part7/canondata/test.test_subselect-flatten_by-default.txt-Results_/extracted
new file mode 100644
index 00000000000..1aca4843f0a
--- /dev/null
+++ b/yql/essentials/tests/sql/minirun/part7/canondata/test.test_subselect-flatten_by-default.txt-Results_/extracted
@@ -0,0 +1,29 @@
+<tmp_path>/program.sql:<main>: Warning: Type annotation
+
+    <tmp_path>/program.sql:<main>:11:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: RemoveSystemMembers, At function: PersistableRepr, At function: OrderedSqlProject, At function: OrderedFlatMap, At lambda
+    	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x);
+	^
+        <tmp_path>/program.sql:<main>:11:15: Warning: At function: FlattenByColumns
+        	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x);
+	              ^
+            <tmp_path>/program.sql:<main>:11:1: Warning: Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY
+            	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x);
+	^
+    <tmp_path>/program.sql:<main>:13:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: RemoveSystemMembers, At function: PersistableRepr, At function: OrderedSqlProject, At function: OrderedFlatMap, At lambda
+    	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x);
+	^
+        <tmp_path>/program.sql:<main>:13:15: Warning: At function: FlattenByColumns
+        	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x);
+	              ^
+            <tmp_path>/program.sql:<main>:13:1: Warning: Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY
+            	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x);
+	^
+    <tmp_path>/program.sql:<main>:15:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: RemoveSystemMembers, At function: PersistableRepr, At function: OrderedSqlProject, At function: OrderedFlatMap, At lambda
+    	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x);
+	^
+        <tmp_path>/program.sql:<main>:15:15: Warning: At function: FlattenByColumns
+        	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x);
+	              ^
+            <tmp_path>/program.sql:<main>:15:1: Warning: Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY
+            	SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x);
+	^
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/minirun/part9/canondata/result.json b/yql/essentials/tests/sql/minirun/part9/canondata/result.json
index 30c20fc7032..9557d452799 100644
--- a/yql/essentials/tests/sql/minirun/part9/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part9/canondata/result.json
@@ -1552,6 +1552,20 @@
             "uri": "https://{canondata_backend}/1937429/18d53b6e51c2d67a4139b1541925383ccf907eb1/resource.tar.gz#test.test_select_op-select_op_order_by-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[subselect-offset-default.txt-Debug]": [
+        {
+            "checksum": "407648bc04bab290eacbdbee1b1eeb91",
+            "size": 1848,
+            "uri": "https://{canondata_backend}/1936997/40b40d8aaac6174752a8f2949ccda8f4542f16d3/resource.tar.gz#test.test_subselect-offset-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[subselect-offset-default.txt-Results]": [
+        {
+            "checksum": "a7c83fed96588848d85fe7013b9bfcc8",
+            "size": 4186,
+            "uri": "https://{canondata_backend}/1936997/40b40d8aaac6174752a8f2949ccda8f4542f16d3/resource.tar.gz#test.test_subselect-offset-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[udf-udaf_default-default.txt-Debug]": [
         {
             "checksum": "8f8b1454954834dfed9debc99927346a",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 9994a22961d..5653adcf262 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -5099,7 +5099,7 @@
         {
             "checksum": "707b29e9ab12763d6c8a135a571bc120",
             "size": 1205,
-            "uri": "https://{canondata_backend}/1942671/d721729a70844cec9425cad0a129e47d493b2668/resource.tar.gz#test_sql2yql.test_linear-mutdict_copy_/sql.yql"
+            "uri": "https://{canondata_backend}/1031349/d721729a70844cec9425cad0a129e47d493b2668/resource.tar.gz#test_sql2yql.test_linear-mutdict_copy_/sql.yql"
         }
     ],
     "test_sql2yql.test[linear-mutdict_empty]": [
@@ -8140,6 +8140,13 @@
             "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_select_op-select_op_precedence_/sql.yql"
         }
     ],
+    "test_sql2yql.test[select_op-select_op_precedence_named_node]": [
+        {
+            "checksum": "56daeccbda41fa7744c105f671fa9dbd",
+            "size": 9595,
+            "uri": "https://{canondata_backend}/1942671/82ed7d067ef141d1fcef204dc3b89bf4e1c70c57/resource.tar.gz#test_sql2yql.test_select_op-select_op_precedence_named_node_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[seq_mode-shared_named_expr]": [
         {
             "checksum": "1bc276a9cb08e31273e24da9ad90582d",
@@ -8322,6 +8329,83 @@
             "uri": "https://{canondata_backend}/1775059/be020ceb210e118c9d8f06ae73e0f97466c49478/resource.tar.gz#test_sql2yql.test_side_effects-x_not_x_fail_/sql.yql"
         }
     ],
+    "test_sql2yql.test[subselect-aggregate]": [
+        {
+            "checksum": "f948f4ad0c25bd1bf52f63dc1ba367e7",
+            "size": 7018,
+            "uri": "https://{canondata_backend}/1936842/befed0b346683be8225cf804762adb36685c15a1/resource.tar.gz#test_sql2yql.test_subselect-aggregate_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-aggregate_over]": [
+        {
+            "checksum": "358b518ab969fee7019a1255fa3d89a5",
+            "size": 7950,
+            "uri": "https://{canondata_backend}/1689644/0989c91e43a233bd2d2fb3ce8744224bfdd8a98c/resource.tar.gz#test_sql2yql.test_subselect-aggregate_over_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-flatten_by]": [
+        {
+            "checksum": "8b2779a99abfdff442a129c801a4df87",
+            "size": 7562,
+            "uri": "https://{canondata_backend}/1871102/5cbff22143d7f6b1bacea6231269c3ce9cc755ce/resource.tar.gz#test_sql2yql.test_subselect-flatten_by_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-having]": [
+        {
+            "checksum": "c687c5999ef3ef849b792bd1af66fa58",
+            "size": 7572,
+            "uri": "https://{canondata_backend}/1937429/cde37b81316143769e7c3cb819c2d6539106c0f2/resource.tar.gz#test_sql2yql.test_subselect-having_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-limit]": [
+        {
+            "checksum": "0c74577c4404a48838bbb3c9d4599d28",
+            "size": 6692,
+            "uri": "https://{canondata_backend}/1936997/8a972c55862e0eb44e30da5b57454d9d28c36af5/resource.tar.gz#test_sql2yql.test_subselect-limit_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-offset]": [
+        {
+            "checksum": "bcffd7739b8bff115c15488f267c12fc",
+            "size": 7032,
+            "uri": "https://{canondata_backend}/1871182/5d02a77ae9eea603f089879e9ca07bcdd644314c/resource.tar.gz#test_sql2yql.test_subselect-offset_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-partition_by]": [
+        {
+            "checksum": "d6fcf13e209c8ae6e4fb77fd8a4a056b",
+            "size": 7950,
+            "uri": "https://{canondata_backend}/1937424/d07f8632fc7a29aabb6b585dfce3b46993c21722/resource.tar.gz#test_sql2yql.test_subselect-partition_by_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-projection]": [
+        {
+            "checksum": "0115369c6c6f8693e1a957fab28df068",
+            "size": 5702,
+            "uri": "https://{canondata_backend}/1942278/29cbf9b34bce7d402d33a3942ea29f5167d37a1d/resource.tar.gz#test_sql2yql.test_subselect-projection_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-union]": [
+        {
+            "checksum": "43d04e67d2f8629c531561e34aa41d90",
+            "size": 20115,
+            "uri": "https://{canondata_backend}/1781765/53187e0fae3b9cd8c495fb9f0a200c799399c140/resource.tar.gz#test_sql2yql.test_subselect-union_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-where]": [
+        {
+            "checksum": "865b800246b8889f3938d93da6ba9f85",
+            "size": 5912,
+            "uri": "https://{canondata_backend}/1942278/29cbf9b34bce7d402d33a3942ea29f5167d37a1d/resource.tar.gz#test_sql2yql.test_subselect-where_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[subselect-where_named_node]": [
+        {
+            "checksum": "dc8f25aaf267d2dcea6724a6df940b43",
+            "size": 4700,
+            "uri": "https://{canondata_backend}/1946324/adec6e8449b8734f0882dc7b5266ea4af0cf09c4/resource.tar.gz#test_sql2yql.test_subselect-where_named_node_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[type_literal-evaluate]": [
         {
             "checksum": "710dafde8986d41d985bd47be65641ab",
@@ -13053,6 +13137,11 @@
             "uri": "file://test_sql_format.test_select_op-select_op_precedence_/formatted.sql"
         }
     ],
+    "test_sql_format.test[select_op-select_op_precedence_named_node]": [
+        {
+            "uri": "file://test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[seq_mode-shared_named_expr]": [
         {
             "uri": "file://test_sql_format.test_seq_mode-shared_named_expr_/formatted.sql"
@@ -13183,6 +13272,61 @@
             "uri": "file://test_sql_format.test_side_effects-x_not_x_fail_/formatted.sql"
         }
     ],
+    "test_sql_format.test[subselect-aggregate]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-aggregate_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-aggregate_over]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-aggregate_over_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-flatten_by]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-flatten_by_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-having]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-having_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-limit]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-limit_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-offset]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-offset_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-partition_by]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-partition_by_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-projection]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-projection_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-union]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-union_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-where]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-where_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[subselect-where_named_node]": [
+        {
+            "uri": "file://test_sql_format.test_subselect-where_named_node_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[type_literal-evaluate]": [
         {
             "uri": "file://test_sql_format.test_type_literal-evaluate_/formatted.sql"
@@ -13641,7 +13785,7 @@
         {
             "checksum": "5362cc94a32ae614cc3808035e96d473",
             "size": 388,
-            "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql_negative.test_flatten_by-struct_with_wrong_correlation-default.txt_/err_file.out"
+            "uri": "https://{canondata_backend}/1889210/f76d30924636e40d4d8bbcffdf8ec586290b624e/resource.tar.gz#test_sql_negative.test_flatten_by-struct_with_wrong_correlation-default.txt_/err_file.out"
         }
     ],
     "test_sql_negative.test[flatten_by-table_funcs_spec_flatten_by-default.txt]": [
@@ -13805,6 +13949,48 @@
             "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql_negative.test_select-use_subrequest_as_table-default.txt_/err_file.out"
         }
     ],
+    "test_sql_negative.test[subselect-group_by_no_source-default.txt]": [
+        {
+            "checksum": "89df5ece669c02f9a18c773bda291633",
+            "size": 183,
+            "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-group_by_no_source-default.txt_/err_file.out"
+        }
+    ],
+    "test_sql_negative.test[subselect-group_by_source-default.txt]": [
+        {
+            "checksum": "803a3c4118ec1171dcccf83358e9364e",
+            "size": 204,
+            "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-group_by_source-default.txt_/err_file.out"
+        }
+    ],
+    "test_sql_negative.test[subselect-group_by_source_filter-default.txt]": [
+        {
+            "checksum": "2c653e2e3eabdf4881fe2b71486e8108",
+            "size": 217,
+            "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-group_by_source_filter-default.txt_/err_file.out"
+        }
+    ],
+    "test_sql_negative.test[subselect-order_by_no_source-default.txt]": [
+        {
+            "checksum": "96b93e9696061443a0bf95d2234aed49",
+            "size": 168,
+            "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-order_by_no_source-default.txt_/err_file.out"
+        }
+    ],
+    "test_sql_negative.test[subselect-order_by_source-default.txt]": [
+        {
+            "checksum": "f9b87f51cdd9dbb9bdc6ac499727ba55",
+            "size": 189,
+            "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-order_by_source-default.txt_/err_file.out"
+        }
+    ],
+    "test_sql_negative.test[subselect-order_by_source_filter-default.txt]": [
+        {
+            "checksum": "7cd36602aca05ed3e3f516bc1bb295ef",
+            "size": 202,
+            "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-order_by_source_filter-default.txt_/err_file.out"
+        }
+    ],
     "test_sql_negative.test[udf-named_args_for_script_with_wrong_order-default.txt]": [
         {
             "checksum": "e366faf0a12a73e0040753ae74cf0a55",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql
new file mode 100644
index 00000000000..0a94efda1ca
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql
@@ -0,0 +1,159 @@
+$t1 = (
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (1),
+                (2)
+        ) AS t (
+            x
+        )
+    )
+    UNION
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (2),
+                (3)
+        ) AS t (
+            x
+        )
+    )
+    INTERSECT
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (3)
+        ) AS t (
+            x
+        )
+    )
+);
+
+$t2 = (
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (3)
+        ) AS t (
+            x
+        )
+    )
+    UNION
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (2)
+        ) AS t (
+            x
+        )
+    )
+    EXCEPT
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (3)
+        ) AS t (
+            x
+        )
+    )
+);
+
+$t3 = (
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (1)
+        ) AS t (
+            x
+        )
+    )
+    UNION
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (2)
+        ) AS t (
+            x
+        )
+    )
+    INTERSECT
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (2),
+                (3)
+        ) AS t (
+            x
+        )
+    )
+    EXCEPT
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (3)
+        ) AS t (
+            x
+        )
+    )
+    UNION
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (4),
+                (3)
+        ) AS t (
+            x
+        )
+    )
+    EXCEPT
+    (
+        SELECT
+            *
+        FROM (
+            VALUES
+                (4)
+        ) AS t (
+            x
+        )
+    )
+);
+
+SELECT
+    *
+FROM
+    $t1
+;
+
+SELECT
+    *
+FROM
+    $t2
+;
+
+SELECT
+    *
+FROM
+    $t3
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_/formatted.sql
new file mode 100644
index 00000000000..09ae6730441
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_/formatted.sql
@@ -0,0 +1,57 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    Sum(3) AS x
+FROM
+    $input
+;
+
+SELECT
+    Sum(
+        (
+            SELECT
+                3
+        )
+    ) AS x
+FROM
+    $input
+;
+
+SELECT
+    Sum(
+        (
+            SELECT
+                Avg(v)
+            FROM
+                $input
+        )
+    ) AS x
+FROM
+    $input
+;
+
+SELECT
+    Sum(
+        (
+            SELECT
+                Avg(v)
+            FROM
+                $input
+            WHERE
+                v == 3
+        )
+    ) AS x
+FROM
+    $input
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_over_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_over_/formatted.sql
new file mode 100644
index 00000000000..a6399abcec2
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_over_/formatted.sql
@@ -0,0 +1,85 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    Sum(3) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            3
+        ORDER BY
+            k
+    )
+;
+
+SELECT
+    Sum(
+        (
+            SELECT
+                3
+        )
+    ) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            3
+        ORDER BY
+            k
+    )
+;
+
+SELECT
+    Sum(
+        (
+            SELECT
+                Avg(v)
+            FROM
+                $input
+        )
+    ) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            3
+        ORDER BY
+            k
+    )
+;
+
+SELECT
+    Sum(
+        (
+            SELECT
+                Avg(v)
+            FROM
+                $input
+            WHERE
+                v == 3
+        )
+    ) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            3
+        ORDER BY
+            k
+    )
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-flatten_by_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-flatten_by_/formatted.sql
new file mode 100644
index 00000000000..b52ae89010c
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-flatten_by_/formatted.sql
@@ -0,0 +1,69 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 1, v: 2|>,
+            <|k: 1, v: 3|>,
+            <|k: 1, v: 4|>,
+            <|k: 1, v: 5|>,
+        ])
+);
+
+SELECT
+    *
+FROM
+    $input
+    FLATTEN BY (
+        ListFromRange(1, 3) AS x
+    )
+;
+
+SELECT
+    *
+FROM
+    $input
+    FLATTEN BY (
+        ListFromRange(
+            1, (
+                SELECT
+                    3
+            )
+        ) AS x
+    )
+;
+
+SELECT
+    *
+FROM
+    $input
+    FLATTEN BY (
+        ListFromRange(
+            1, (
+                SELECT
+                    Avg(v)
+                FROM
+                    $input
+            )
+        ) AS x
+    )
+;
+
+SELECT
+    *
+FROM
+    $input
+    FLATTEN BY (
+        ListFromRange(
+            1, (
+                SELECT
+                    Avg(v)
+                FROM
+                    $input
+                WHERE
+                    v == 3
+            )
+        ) AS x
+    )
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-having_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-having_/formatted.sql
new file mode 100644
index 00000000000..ef64fe8521d
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-having_/formatted.sql
@@ -0,0 +1,60 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>, <|k: 2, v: 1|>,
+            <|k: 1, v: 2|>, <|k: 2, v: 2|>,
+            <|k: 1, v: 3|>, <|k: 2, v: 3|>,
+            <|k: 1, v: 4|>, <|k: 2, v: 4|>,
+            <|k: 1, v: 5|>, <|k: 2, v: 5|>,
+        ])
+);
+
+SELECT
+    k,
+    Avg(v)
+FROM
+    $input
+GROUP BY
+    k
+HAVING
+    Avg(v) == (
+        SELECT
+            3
+    )
+;
+
+SELECT
+    k,
+    Avg(v)
+FROM
+    $input
+GROUP BY
+    k
+HAVING
+    Avg(v) == (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+    )
+;
+
+SELECT
+    k,
+    Avg(v)
+FROM
+    $input
+GROUP BY
+    k
+HAVING
+    Avg(v) == (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+        WHERE
+            v == 3
+    )
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-limit_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-limit_/formatted.sql
new file mode 100644
index 00000000000..353ef99580c
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-limit_/formatted.sql
@@ -0,0 +1,57 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    *
+FROM
+    $input
+LIMIT CAST(3 AS UInt64);
+
+SELECT
+    *
+FROM
+    $input
+LIMIT CAST(
+    (
+        SELECT
+            3
+    ) AS UInt64
+);
+
+SELECT
+    *
+FROM
+    $input
+LIMIT CAST(
+    (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+    ) AS UInt64
+);
+
+SELECT
+    *
+FROM
+    $input
+LIMIT CAST(
+    (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+        WHERE
+            v == 3
+    ) AS UInt64
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-offset_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-offset_/formatted.sql
new file mode 100644
index 00000000000..df19827c782
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-offset_/formatted.sql
@@ -0,0 +1,65 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    *
+FROM
+    $input
+ORDER BY
+    k
+LIMIT NULL OFFSET CAST(3 AS Uint64);
+
+SELECT
+    *
+FROM
+    $input
+ORDER BY
+    k
+LIMIT NULL OFFSET CAST(
+    (
+        SELECT
+            3
+    ) AS Uint64
+);
+
+SELECT
+    *
+FROM
+    $input
+ORDER BY
+    k
+LIMIT NULL OFFSET CAST(
+    (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+    ) AS Uint64
+);
+
+SELECT
+    *
+FROM
+    $input
+ORDER BY
+    k
+LIMIT NULL OFFSET CAST(
+    (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+        WHERE
+            v == 3
+    ) AS Uint64
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-partition_by_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-partition_by_/formatted.sql
new file mode 100644
index 00000000000..cc34fcfe5b2
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-partition_by_/formatted.sql
@@ -0,0 +1,79 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    Count(*) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            3
+        ORDER BY
+            k
+    )
+;
+
+SELECT
+    Count(*) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            (
+                SELECT
+                    3
+            )
+        ORDER BY
+            k
+    )
+;
+
+SELECT
+    Count(*) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            (
+                SELECT
+                    Avg(v)
+                FROM
+                    $input
+            )
+        ORDER BY
+            k
+    )
+;
+
+SELECT
+    Count(*) OVER w AS x
+FROM
+    $input
+WINDOW
+    w AS (
+        PARTITION BY
+            (
+                SELECT
+                    Avg(v)
+                FROM
+                    $input
+                WHERE
+                    v == 3
+            )
+        ORDER BY
+            k
+    )
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-projection_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-projection_/formatted.sql
new file mode 100644
index 00000000000..6c66310617e
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-projection_/formatted.sql
@@ -0,0 +1,39 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    (
+        SELECT
+            3
+    ) AS x
+;
+
+SELECT
+    (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+    ) AS x
+;
+
+SELECT
+    (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+        WHERE
+            v == 3
+    ) AS x
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-union_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-union_/formatted.sql
new file mode 100644
index 00000000000..9a7b16eb0ec
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-union_/formatted.sql
@@ -0,0 +1,217 @@
+$t1 = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+$t2 = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 6, v: 6|>,
+            <|k: 7, v: 7|>,
+            <|k: 8, v: 8|>,
+            <|k: 9, v: 9|>,
+            <|k: 10, v: 10|>,
+        ])
+);
+
+$t3 = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 11, v: 11|>,
+            <|k: 12, v: 12|>,
+            <|k: 13, v: 13|>,
+            <|k: 14, v: 14|>,
+            <|k: 15, v: 15|>,
+        ])
+);
+
+SELECT
+    1 IN (
+        SELECT
+            k
+        FROM
+            $t1
+    ),
+    1 IN (
+        (
+            SELECT
+                k
+            FROM
+                $t1
+        )
+    ),
+    1 IN (
+        (
+            (
+                SELECT
+                    k
+                FROM
+                    $t1
+            )
+        )
+    ),
+    1 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+    ),
+    6 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+    ),
+    1 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        (
+            SELECT
+                k
+            FROM
+                $t2
+        )
+    ),
+    6 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        (
+            SELECT
+                k
+            FROM
+                $t2
+        )
+    ),
+    1 IN (
+        (
+            SELECT
+                k
+            FROM
+                $t1
+        )
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+    ),
+    6 IN (
+        (
+            SELECT
+                k
+            FROM
+                $t1
+        )
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+    ),
+    1 IN (
+        (
+            SELECT
+                k
+            FROM
+                $t1
+        )
+        UNION
+        (
+            SELECT
+                k
+            FROM
+                $t2
+        )
+    ),
+    6 IN (
+        (
+            SELECT
+                k
+            FROM
+                $t1
+        )
+        UNION
+        (
+            SELECT
+                k
+            FROM
+                $t2
+        )
+    ),
+    1 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+        UNION
+        SELECT
+            k
+        FROM
+            $t3
+    ),
+    6 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+        UNION
+        SELECT
+            k
+        FROM
+            $t3
+    ),
+    11 IN (
+        SELECT
+            k
+        FROM
+            $t1
+        UNION
+        SELECT
+            k
+        FROM
+            $t2
+        UNION
+        SELECT
+            k
+        FROM
+            $t3
+    ),
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_/formatted.sql
new file mode 100644
index 00000000000..1fae3b6f54f
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_/formatted.sql
@@ -0,0 +1,51 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+SELECT
+    *
+FROM
+    $input
+WHERE
+    v < (
+        SELECT
+            3
+    )
+;
+
+SELECT
+    *
+FROM
+    $input
+WHERE
+    v < (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+    )
+;
+
+SELECT
+    *
+FROM
+    $input
+WHERE
+    v < (
+        SELECT
+            Avg(v)
+        FROM
+            $input
+        WHERE
+            v == 3
+    )
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_named_node_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_named_node_/formatted.sql
new file mode 100644
index 00000000000..56aaaebf0af
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_named_node_/formatted.sql
@@ -0,0 +1,43 @@
+$input = (
+    SELECT
+        *
+    FROM
+        AS_TABLE([
+            <|k: 1, v: 1|>,
+            <|k: 2, v: 2|>,
+            <|k: 3, v: 3|>,
+            <|k: 4, v: 4|>,
+            <|k: 5, v: 5|>,
+        ])
+);
+
+$x = (
+    SELECT
+        Avg(v)
+    FROM
+        $input
+);
+
+SELECT
+    *
+FROM
+    $input
+WHERE
+    v < $x
+;
+
+SELECT
+    *
+FROM
+    $input
+WHERE
+    v < ($x)
+;
+
+SELECT
+    *
+FROM
+    $input
+WHERE
+    v < (($x))
+;
diff --git a/yql/essentials/tests/sql/suites/select_op/select_op_precedence_named_node.yql b/yql/essentials/tests/sql/suites/select_op/select_op_precedence_named_node.yql
new file mode 100644
index 00000000000..06b36ee3b3f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/select_op/select_op_precedence_named_node.yql
@@ -0,0 +1,34 @@
+$t1 = (
+    (SELECT * FROM (VALUES (1), (2)) AS t (x))
+UNION
+    (SELECT * FROM (VALUES (2), (3)) AS t (x))
+    INTERSECT
+    (SELECT * FROM (VALUES (3)) AS t (x))
+);
+
+
+$t2 = (
+(SELECT * FROM (VALUES (3)) AS t (x))
+UNION
+(SELECT * FROM (VALUES (2)) AS t (x))
+EXCEPT
+(SELECT * FROM (VALUES (3)) AS t (x))
+);
+
+$t3 = (
+    (SELECT * FROM (VALUES (1)) AS t (x))
+UNION
+    (SELECT * FROM (VALUES (2)) AS t (x))
+    INTERSECT
+    (SELECT * FROM (VALUES (2), (3)) AS t (x))
+EXCEPT
+    (SELECT * FROM (VALUES (3)) AS t (x))
+UNION
+    (SELECT * FROM (VALUES (4), (3)) AS t (x))
+EXCEPT
+    (SELECT * FROM (VALUES (4)) AS t (x))
+);
+
+SELECT * FROM $t1;
+SELECT * FROM $t2;
+SELECT * FROM $t3;
diff --git a/yql/essentials/tests/sql/suites/subselect/aggregate.yql b/yql/essentials/tests/sql/suites/subselect/aggregate.yql
new file mode 100644
index 00000000000..50f529522f3
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/aggregate.yql
@@ -0,0 +1,15 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT Sum(3) AS x FROM $input;
+
+SELECT Sum((SELECT 3)) AS x FROM $input;
+
+SELECT Sum((SELECT Avg(v) FROM $input)) AS x FROM $input;
+
+SELECT Sum((SELECT Avg(v) FROM $input WHERE v == 3)) AS x FROM $input;
diff --git a/yql/essentials/tests/sql/suites/subselect/aggregate_over.yql b/yql/essentials/tests/sql/suites/subselect/aggregate_over.yql
new file mode 100644
index 00000000000..7e81e3a08f0
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/aggregate_over.yql
@@ -0,0 +1,19 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT Sum(3) OVER w AS x FROM $input
+WINDOW w AS (PARTITION BY 3 ORDER BY k);
+
+SELECT Sum((SELECT 3)) OVER w AS x FROM $input
+WINDOW w AS (PARTITION BY 3 ORDER BY k);
+
+SELECT Sum((SELECT Avg(v) FROM $input)) OVER w AS x FROM $input
+WINDOW w AS (PARTITION BY 3 ORDER BY k);
+
+SELECT Sum((SELECT Avg(v) FROM $input WHERE v == 3)) OVER w AS x FROM $input
+WINDOW w AS (PARTITION BY 3 ORDER BY k);
diff --git a/yql/essentials/tests/sql/suites/subselect/default.cfg b/yql/essentials/tests/sql/suites/subselect/default.cfg
new file mode 100644
index 00000000000..617474f8d63
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/default.cfg
@@ -0,0 +1 @@
+langver 2025.04
diff --git a/yql/essentials/tests/sql/suites/subselect/flatten_by.yql b/yql/essentials/tests/sql/suites/subselect/flatten_by.yql
new file mode 100644
index 00000000000..e38cefffb2e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/flatten_by.yql
@@ -0,0 +1,15 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 1, v: 2|>,
+    <|k: 1, v: 3|>,
+    <|k: 1, v: 4|>,
+    <|k: 1, v: 5|>,
+]);
+
+SELECT * FROM $input FLATTEN BY (ListFromRange(1, 3) AS x);
+
+SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x);
+
+SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x);
+
+SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x);
diff --git a/yql/essentials/tests/sql/suites/subselect/group_by_no_source.sqlx b/yql/essentials/tests/sql/suites/subselect/group_by_no_source.sqlx
new file mode 100644
index 00000000000..ceb278835b2
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/group_by_no_source.sqlx
@@ -0,0 +1,9 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>, <|k: 2, v: 1|>,
+    <|k: 1, v: 2|>, <|k: 2, v: 2|>,
+    <|k: 1, v: 3|>, <|k: 2, v: 3|>,
+    <|k: 1, v: 4|>, <|k: 2, v: 4|>,
+    <|k: 1, v: 5|>, <|k: 2, v: 5|>,
+]);
+
+SELECT k, Avg(v) FROM $input GROUP BY (SELECT 3) AS k;
diff --git a/yql/essentials/tests/sql/suites/subselect/group_by_source.sqlx b/yql/essentials/tests/sql/suites/subselect/group_by_source.sqlx
new file mode 100644
index 00000000000..021948f79b1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/group_by_source.sqlx
@@ -0,0 +1,9 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>, <|k: 2, v: 1|>,
+    <|k: 1, v: 2|>, <|k: 2, v: 2|>,
+    <|k: 1, v: 3|>, <|k: 2, v: 3|>,
+    <|k: 1, v: 4|>, <|k: 2, v: 4|>,
+    <|k: 1, v: 5|>, <|k: 2, v: 5|>,
+]);
+
+SELECT k, Avg(v) FROM $input GROUP BY (SELECT Avg(v) FROM $input) AS k;
diff --git a/yql/essentials/tests/sql/suites/subselect/group_by_source_filter.sqlx b/yql/essentials/tests/sql/suites/subselect/group_by_source_filter.sqlx
new file mode 100644
index 00000000000..20fe7ca9696
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/group_by_source_filter.sqlx
@@ -0,0 +1,9 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>, <|k: 2, v: 1|>,
+    <|k: 1, v: 2|>, <|k: 2, v: 2|>,
+    <|k: 1, v: 3|>, <|k: 2, v: 3|>,
+    <|k: 1, v: 4|>, <|k: 2, v: 4|>,
+    <|k: 1, v: 5|>, <|k: 2, v: 5|>,
+]);
+
+SELECT k, Avg(v) FROM $input GROUP BY (SELECT Avg(v) FROM $input WHERE v == 3) AS k;
diff --git a/yql/essentials/tests/sql/suites/subselect/having.yql b/yql/essentials/tests/sql/suites/subselect/having.yql
new file mode 100644
index 00000000000..a041651aa6e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/having.yql
@@ -0,0 +1,13 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>, <|k: 2, v: 1|>,
+    <|k: 1, v: 2|>, <|k: 2, v: 2|>,
+    <|k: 1, v: 3|>, <|k: 2, v: 3|>,
+    <|k: 1, v: 4|>, <|k: 2, v: 4|>,
+    <|k: 1, v: 5|>, <|k: 2, v: 5|>,
+]);
+
+SELECT k, Avg(v) FROM $input GROUP BY k HAVING Avg(v) == (SELECT 3);
+
+SELECT k, Avg(v) FROM $input GROUP BY k HAVING Avg(v) == (SELECT Avg(v) FROM $input);
+
+SELECT k, Avg(v) FROM $input GROUP BY k HAVING Avg(v) == (SELECT Avg(v) FROM $input WHERE v == 3);
diff --git a/yql/essentials/tests/sql/suites/subselect/limit.yql b/yql/essentials/tests/sql/suites/subselect/limit.yql
new file mode 100644
index 00000000000..ae8c84cc068
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/limit.yql
@@ -0,0 +1,12 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT * FROM $input LIMIT CAST(3 AS UInt64);
+SELECT * FROM $input LIMIT CAST((SELECT 3) AS UInt64);
+SELECT * FROM $input LIMIT CAST((SELECT Avg(v) FROM $input) AS UInt64);
+SELECT * FROM $input LIMIT CAST((SELECT Avg(v) FROM $input WHERE v == 3) AS UInt64);
diff --git a/yql/essentials/tests/sql/suites/subselect/offset.yql b/yql/essentials/tests/sql/suites/subselect/offset.yql
new file mode 100644
index 00000000000..9a6dd2300f9
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/offset.yql
@@ -0,0 +1,15 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST(3 AS Uint64);
+
+SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST((SELECT 3) AS Uint64);
+
+SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST((SELECT Avg(v) FROM $input) AS Uint64);
+
+SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST((SELECT Avg(v) FROM $input WHERE v == 3) AS Uint64);
diff --git a/yql/essentials/tests/sql/suites/subselect/order_by_no_source.sqlx b/yql/essentials/tests/sql/suites/subselect/order_by_no_source.sqlx
new file mode 100644
index 00000000000..cd3e4824fd7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/order_by_no_source.sqlx
@@ -0,0 +1,9 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 1, v: 2|>,
+    <|k: 1, v: 3|>,
+    <|k: 1, v: 4|>,
+    <|k: 1, v: 5|>,
+]);
+
+SELECT * FROM $input ORDER BY (SELECT 3), k, v;
diff --git a/yql/essentials/tests/sql/suites/subselect/order_by_source.sqlx b/yql/essentials/tests/sql/suites/subselect/order_by_source.sqlx
new file mode 100644
index 00000000000..9f88c26badd
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/order_by_source.sqlx
@@ -0,0 +1,9 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 1, v: 2|>,
+    <|k: 1, v: 3|>,
+    <|k: 1, v: 4|>,
+    <|k: 1, v: 5|>,
+]);
+
+SELECT * FROM $input ORDER BY (SELECT Avg(v) FROM $input), k, v;
diff --git a/yql/essentials/tests/sql/suites/subselect/order_by_source_filter.sqlx b/yql/essentials/tests/sql/suites/subselect/order_by_source_filter.sqlx
new file mode 100644
index 00000000000..44aef1291f0
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/order_by_source_filter.sqlx
@@ -0,0 +1,9 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 1, v: 2|>,
+    <|k: 1, v: 3|>,
+    <|k: 1, v: 4|>,
+    <|k: 1, v: 5|>,
+]);
+
+SELECT * FROM $input ORDER BY (SELECT Avg(v) FROM $input WHERE v == 3), k, v;
diff --git a/yql/essentials/tests/sql/suites/subselect/partition_by.yql b/yql/essentials/tests/sql/suites/subselect/partition_by.yql
new file mode 100644
index 00000000000..2e228bb2ccc
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/partition_by.yql
@@ -0,0 +1,32 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT Count(*) OVER w AS x FROM $input
+WINDOW w AS (
+    PARTITION BY 3
+    ORDER BY k
+);
+
+SELECT Count(*) OVER w AS x FROM $input
+WINDOW w AS (
+    PARTITION BY (SELECT 3)
+    ORDER BY k
+);
+
+SELECT Count(*) OVER w AS x FROM $input
+WINDOW w AS (
+    PARTITION BY (SELECT Avg(v) FROM $input)
+    ORDER BY k
+);
+
+SELECT Count(*) OVER w AS x FROM $input
+WINDOW w AS (
+    PARTITION BY (SELECT Avg(v) FROM $input WHERE v == 3)
+    ORDER BY k
+);
+
diff --git a/yql/essentials/tests/sql/suites/subselect/projection.yql b/yql/essentials/tests/sql/suites/subselect/projection.yql
new file mode 100644
index 00000000000..f7012ec923e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/projection.yql
@@ -0,0 +1,13 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT (SELECT 3) AS x;
+
+SELECT (SELECT Avg(v) FROM $input) AS x;
+
+SELECT (SELECT Avg(v) FROM $input WHERE v == 3) AS x;
diff --git a/yql/essentials/tests/sql/suites/subselect/union.yql b/yql/essentials/tests/sql/suites/subselect/union.yql
new file mode 100644
index 00000000000..7b11993a149
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/union.yql
@@ -0,0 +1,42 @@
+$t1 = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+$t2 = SELECT * FROM AS_TABLE([
+    <|k:  6, v:  6|>,
+    <|k:  7, v:  7|>,
+    <|k:  8, v:  8|>,
+    <|k:  9, v:  9|>,
+    <|k: 10, v: 10|>,
+]);
+
+$t3 = SELECT * FROM AS_TABLE([
+    <|k: 11, v: 11|>,
+    <|k: 12, v: 12|>,
+    <|k: 13, v: 13|>,
+    <|k: 14, v: 14|>,
+    <|k: 15, v: 15|>,
+]);
+
+SELECT
+    1  IN (  SELECT k FROM $t1  ),
+    1  IN (( SELECT k FROM $t1 )),
+    1  IN (((SELECT k FROM $t1))),
+
+    1  IN ( SELECT k FROM $t1  UNION  SELECT k FROM $t2 ),
+    6  IN ( SELECT k FROM $t1  UNION  SELECT k FROM $t2 ),
+    1  IN ( SELECT k FROM $t1  UNION (SELECT k FROM $t2)),
+    6  IN ( SELECT k FROM $t1  UNION (SELECT k FROM $t2)),
+    1  IN ((SELECT k FROM $t1) UNION  SELECT k FROM $t2 ),
+    6  IN ((SELECT k FROM $t1) UNION  SELECT k FROM $t2 ),
+    1  IN ((SELECT k FROM $t1) UNION (SELECT k FROM $t2)),
+    6  IN ((SELECT k FROM $t1) UNION (SELECT k FROM $t2)),
+
+    1  IN (SELECT k FROM $t1 UNION SELECT k FROM $t2 UNION SELECT k FROM $t3),
+    6  IN (SELECT k FROM $t1 UNION SELECT k FROM $t2 UNION SELECT k FROM $t3),
+    11 IN (SELECT k FROM $t1 UNION SELECT k FROM $t2 UNION SELECT k FROM $t3),
+;
diff --git a/yql/essentials/tests/sql/suites/subselect/where.yql b/yql/essentials/tests/sql/suites/subselect/where.yql
new file mode 100644
index 00000000000..01a51f00240
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/where.yql
@@ -0,0 +1,13 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+SELECT * FROM $input WHERE v < (SELECT 3);
+
+SELECT * FROM $input WHERE v < (SELECT Avg(v) FROM $input);
+
+SELECT * FROM $input WHERE v < (SELECT Avg(v) FROM $input WHERE v == 3);
diff --git a/yql/essentials/tests/sql/suites/subselect/where_named_node.yql b/yql/essentials/tests/sql/suites/subselect/where_named_node.yql
new file mode 100644
index 00000000000..bb607a82cb1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/subselect/where_named_node.yql
@@ -0,0 +1,13 @@
+$input = SELECT * FROM AS_TABLE([
+    <|k: 1, v: 1|>,
+    <|k: 2, v: 2|>,
+    <|k: 3, v: 3|>,
+    <|k: 4, v: 4|>,
+    <|k: 5, v: 5|>,
+]);
+
+$x = SELECT Avg(v) FROM $input;
+
+SELECT * FROM $input WHERE v < $x;
+SELECT * FROM $input WHERE v < ($x);
+SELECT * FROM $input WHERE v < (($x));