YQL-19747: Synthesise subquery columns

commit_hash:40a2de243a67135e44505619fb766954ba24e2e8

21 files changed, 431 insertions, 37 deletions

diff --git a/yql/essentials/sql/v1/complete/analysis/global/base_visitor.cpp b/yql/essentials/sql/v1/complete/analysis/global/base_visitor.cpp
new file mode 100644
index 00000000000..f426e17b62f
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/analysis/global/base_visitor.cpp
@@ -0,0 +1,12 @@
+#include "base_visitor.h"
+
+namespace NSQLComplete {
+
+    std::any TSQLv1BaseVisitor::aggregateResult(std::any aggregate, std::any nextResult) {
+        if (nextResult.has_value()) {
+            return nextResult;
+        }
+        return aggregate;
+    }
+
+} // namespace NSQLComplete
diff --git a/yql/essentials/sql/v1/complete/analysis/global/base_visitor.h b/yql/essentials/sql/v1/complete/analysis/global/base_visitor.h
new file mode 100644
index 00000000000..bbee6b796f2
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/analysis/global/base_visitor.h
@@ -0,0 +1,12 @@
+#pragma once
+
+#include "parse_tree.h"
+
+namespace NSQLComplete {
+
+    class TSQLv1BaseVisitor: public SQLv1Antlr4BaseVisitor {
+    protected:
+        std::any aggregateResult(std::any aggregate, std::any nextResult) override;
+    };
+
+} // namespace NSQLComplete
diff --git a/yql/essentials/sql/v1/complete/analysis/global/column.cpp b/yql/essentials/sql/v1/complete/analysis/global/column.cpp
index b64b59a9f47..00da14c4b2d 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/column.cpp
+++ b/yql/essentials/sql/v1/complete/analysis/global/column.cpp
@@ -1,5 +1,6 @@
 #include "column.h"
 
+#include "base_visitor.h"
 #include "narrowing_visitor.h"
 
 #include <yql/essentials/sql/v1/complete/syntax/format.h>
@@ -10,7 +11,7 @@ namespace NSQLComplete {
 
         // TODO: Extract it to `identifier.cpp` and reuse it also at `use.cpp`
         //       and replace `GetId` at `parse_tree.cpp`.
-        class TIdentifierVisitor: public SQLv1Antlr4BaseVisitor {
+        class TIdentifierVisitor: public TSQLv1BaseVisitor {
         public:
             std::any visitCluster_expr(SQLv1::Cluster_exprContext* ctx) override {
                 if (auto* x = ctx->pure_column_or_named()) {
@@ -54,20 +55,10 @@ namespace NSQLComplete {
             return std::any_cast<TString>(result);
         }
 
-        class TInferenceVisitor: public SQLv1Antlr4BaseVisitor {
+        class TInferenceVisitor: public TSQLv1BaseVisitor {
         public:
             std::any visitJoin_source(SQLv1::Join_sourceContext* ctx) override {
-                TColumnContext united;
-                for (SQLv1::Flatten_sourceContext* ctx : ctx->flatten_source()) {
-                    std::any any = visit(ctx);
-                    if (!any.has_value()) {
-                        continue;
-                    }
-
-                    TColumnContext child = std::move(std::any_cast<TColumnContext>(any));
-                    united = United(std::move(united), std::move(child));
-                }
-                return united;
+                return AccumulatingVisit(ctx->flatten_source());
             }
 
             std::any visitNamed_single_source(SQLv1::Named_single_sourceContext* ctx) override {
@@ -87,7 +78,7 @@ namespace NSQLComplete {
                     return context;
                 }
 
-                return Renamed(std::move(context), *alias);
+                return std::move(context).Renamed(*alias);
             }
 
             std::any visitTable_ref(SQLv1::Table_refContext* ctx) override {
@@ -105,6 +96,35 @@ namespace NSQLComplete {
                 };
             }
 
+            std::any visitSelect_stmt(SQLv1::Select_stmtContext* ctx) override {
+                return AccumulatingVisit(ctx->select_kind_parenthesis());
+            }
+
+            std::any visitSelect_core(SQLv1::Select_coreContext* ctx) override {
+                TMaybe<TColumnContext> head = Head(ctx);
+                if (head.Empty() || head->IsAsterisk()) {
+                    return AccumulatingVisit(ctx->join_source());
+                }
+
+                return AccumulatingVisit(ctx->result_column());
+            }
+
+            std::any visitResult_column(SQLv1::Result_columnContext* ctx) override {
+                if (ctx->TOKEN_ASTERISK() != nullptr) {
+                    return TColumnContext::Asterisk();
+                }
+
+                TMaybe<TString> column = GetAlias(ctx);
+                if (column.Defined()) {
+                    return TColumnContext{
+                        .Columns = {
+                            {.Name = std::move(*column)},
+                        }};
+                }
+
+                return {};
+            }
+
         private:
             TMaybe<TString> GetAlias(SQLv1::Named_single_sourceContext* ctx) const {
                 TMaybe<TString> alias = GetId(ctx->an_id());
@@ -112,21 +132,45 @@ namespace NSQLComplete {
                 return alias;
             }
 
-            TColumnContext Renamed(TColumnContext context, TString alias) {
-                Y_ENSURE(!alias.empty());
+            TMaybe<TString> GetAlias(SQLv1::Result_columnContext* ctx) const {
+                antlr4::ParserRuleContext* id = nullptr;
+                if (ctx->TOKEN_AS() == nullptr) {
+                    id = ctx->expr();
+                } else {
+                    id = ctx->an_id_or_type();
+                    id = id ? id : ctx->an_id_as_compat();
+                }
+                return GetId(id);
+            }
 
-                for (TAliased<TTableId>& table : context.Tables) {
-                    table.Alias = alias;
+            TMaybe<TColumnContext> Head(SQLv1::Select_coreContext* ctx) {
+                SQLv1::Result_columnContext* column = ctx->result_column(0);
+                if (column == nullptr) {
+                    return Nothing();
+                }
+
+                std::any any = visit(column);
+                if (!any.has_value()) {
+                    return Nothing();
                 }
 
-                return context;
+                return std::any_cast<TColumnContext>(any);
             }
 
-            static TColumnContext United(TColumnContext&& lhs, TColumnContext&& rhs) {
-                lhs.Tables.reserve(lhs.Tables.size() + rhs.Tables.size());
-                std::move(rhs.Tables.begin(), rhs.Tables.end(), std::back_inserter(lhs.Tables));
-                SortUnique(lhs.Tables);
-                return lhs;
+            template <std::derived_from<antlr4::ParserRuleContext> T>
+            TColumnContext AccumulatingVisit(std::vector<T*> contexts) {
+                return Accumulate(
+                    contexts,
+                    TColumnContext(),
+                    [this](TColumnContext&& acc, T* ctx) {
+                        std::any any = visit(ctx);
+                        if (!any.has_value()) {
+                            return acc;
+                        }
+
+                        TColumnContext child = std::move(std::any_cast<TColumnContext>(any));
+                        return std::move(acc) | std::move(child);
+                    });
             }
         };
 
@@ -153,7 +197,7 @@ namespace NSQLComplete {
                     return {};
                 }
 
-                return TInferenceVisitor().visit(ctx);
+                return TInferenceVisitor().visit(source);
             }
         };
 
diff --git a/yql/essentials/sql/v1/complete/analysis/global/global.cpp b/yql/essentials/sql/v1/complete/analysis/global/global.cpp
index f73cad926e3..74f9b6909e8 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/global.cpp
+++ b/yql/essentials/sql/v1/complete/analysis/global/global.cpp
@@ -15,6 +15,10 @@
 
 namespace NSQLComplete {
 
+    bool operator<(const TColumnId& lhs, const TColumnId& rhs) {
+        return std::tie(lhs.TableAlias, lhs.Name) < std::tie(rhs.TableAlias, rhs.Name);
+    }
+
     TVector<TTableId> TColumnContext::TablesWithAlias(TStringBuf alias) const {
         if (alias.empty()) {
             return TVector<TTableId>(Tables.begin(), Tables.end());
@@ -24,6 +28,38 @@ namespace NSQLComplete {
         return TVector<TTableId>(filtered.begin(), filtered.end());
     }
 
+    bool TColumnContext::IsAsterisk() const {
+        return Columns.size() == 1 && Columns[0].Name == "*";
+    }
+
+    TColumnContext TColumnContext::Renamed(TStringBuf alias) && {
+        Y_ENSURE(!alias.empty());
+        for (TAliased<TTableId>& table : Tables) {
+            table.Alias = alias;
+        }
+        for (TColumnId& column : Columns) {
+            column.TableAlias = alias;
+        }
+        return *this;
+    }
+
+    TColumnContext operator|(TColumnContext lhs, TColumnContext rhs) {
+        lhs.Tables.reserve(lhs.Tables.size() + rhs.Tables.size());
+        lhs.Columns.reserve(lhs.Columns.size() + rhs.Columns.size());
+
+        std::move(rhs.Tables.begin(), rhs.Tables.end(), std::back_inserter(lhs.Tables));
+        std::move(rhs.Columns.begin(), rhs.Columns.end(), std::back_inserter(lhs.Columns));
+
+        SortUnique(lhs.Tables);
+        SortUnique(lhs.Columns);
+
+        return lhs;
+    }
+
+    TColumnContext TColumnContext::Asterisk() {
+        return {.Columns = {{.Name = "*"}}};
+    }
+
     class TErrorStrategy: public antlr4::DefaultErrorStrategy {
     public:
         antlr4::Token* singleTokenDeletion(antlr4::Parser* /* recognizer */) override {
@@ -132,8 +168,14 @@ void Out<NSQLComplete::TAliased<NSQLComplete::TTableId>>(IOutputStream& out, con
 }
 
 template <>
+void Out<NSQLComplete::TColumnId>(IOutputStream& out, const NSQLComplete::TColumnId& value) {
+    out << value.TableAlias << "." << value.Name;
+}
+
+template <>
 void Out<NSQLComplete::TColumnContext>(IOutputStream& out, const NSQLComplete::TColumnContext& value) {
     out << "TColumnContext { ";
     out << "Tables: " << JoinSeq(", ", value.Tables);
+    out << ", Columns: " << JoinSeq(", ", value.Columns);
     out << " }";
 }
diff --git a/yql/essentials/sql/v1/complete/analysis/global/global.h b/yql/essentials/sql/v1/complete/analysis/global/global.h
index 31e5ea3ea53..972b2caa609 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/global.h
+++ b/yql/essentials/sql/v1/complete/analysis/global/global.h
@@ -41,12 +41,26 @@ namespace NSQLComplete {
         friend bool operator==(const TAliased& lhs, const TAliased& rhs) = default;
     };
 
+    struct TColumnId {
+        TString TableAlias;
+        TString Name;
+
+        friend bool operator<(const TColumnId& lhs, const TColumnId& rhs);
+        friend bool operator==(const TColumnId& lhs, const TColumnId& rhs) = default;
+    };
+
     struct TColumnContext {
         TVector<TAliased<TTableId>> Tables;
+        TVector<TColumnId> Columns;
 
         TVector<TTableId> TablesWithAlias(TStringBuf alias) const;
+        bool IsAsterisk() const;
+        TColumnContext Renamed(TStringBuf alias) &&;
 
         friend bool operator==(const TColumnContext& lhs, const TColumnContext& rhs) = default;
+        friend TColumnContext operator|(TColumnContext lhs, TColumnContext rhs);
+
+        static TColumnContext Asterisk();
     };
 
     struct TGlobalContext {
diff --git a/yql/essentials/sql/v1/complete/analysis/global/global_ut.cpp b/yql/essentials/sql/v1/complete/analysis/global/global_ut.cpp
index 1065446c952..5e180fcb431 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/global_ut.cpp
+++ b/yql/essentials/sql/v1/complete/analysis/global/global_ut.cpp
@@ -149,4 +149,72 @@ Y_UNIT_TEST_SUITE(GlobalAnalysisTests) {
         }
     }
 
+    Y_UNIT_TEST(Subquery) {
+        IGlobalAnalysis::TPtr global = MakeGlobalAnalysis();
+        {
+            TString query = "SELECT # FROM (SELECT * FROM x)";
+
+            TGlobalContext ctx = global->Analyze(SharpedInput(query), {});
+
+            TColumnContext expected = {.Tables = {TAliased<TTableId>("", {"", "x"})}};
+            UNIT_ASSERT_VALUES_EQUAL(ctx.Column, expected);
+        }
+        {
+            TString query = "SELECT # FROM (SELECT a, b FROM x)";
+
+            TGlobalContext ctx = global->Analyze(SharpedInput(query), {});
+
+            TColumnContext expected = {.Columns = {{.Name = "a"}, {.Name = "b"}}};
+            UNIT_ASSERT_VALUES_EQUAL(ctx.Column, expected);
+        }
+        {
+            TString query = "SELECT # FROM (SELECT 1 AS a, 2 AS b)";
+
+            TGlobalContext ctx = global->Analyze(SharpedInput(query), {});
+
+            TColumnContext expected = {.Columns = {{.Name = "a"}, {.Name = "b"}}};
+            UNIT_ASSERT_VALUES_EQUAL(ctx.Column, expected);
+        }
+        {
+            TString query = "SELECT # FROM (SELECT 1 AS a, 2 AS b) AS x";
+
+            TGlobalContext ctx = global->Analyze(SharpedInput(query), {});
+
+            TColumnContext expected = {.Columns = {{"x", "a"}, {"x", "b"}}};
+            UNIT_ASSERT_VALUES_EQUAL(ctx.Column, expected);
+        }
+        {
+            TString query = R"(
+                SELECT #
+                FROM (SELECT * FROM example.`/people`) AS ep
+                JOIN (SELECT room AS Room, time FROM example.`/yql/tutorial`) AS et ON 1 = 1
+            )";
+
+            TGlobalContext ctx = global->Analyze(SharpedInput(query), {});
+
+            TColumnContext expected = {
+                .Tables = {
+                    TAliased<TTableId>("ep", {"example", "/people"}),
+                },
+                .Columns = {
+                    {.TableAlias = "et", .Name = "Room"},
+                    {.TableAlias = "et", .Name = "time"},
+                },
+            };
+            UNIT_ASSERT_VALUES_EQUAL(ctx.Column, expected);
+        }
+    }
+
+    Y_UNIT_TEST(Projection) {
+        IGlobalAnalysis::TPtr global = MakeGlobalAnalysis();
+        {
+            TString query = "SELECT a, b, # FROM x";
+
+            TGlobalContext ctx = global->Analyze(SharpedInput(query), {});
+
+            TColumnContext expected = {.Tables = {TAliased<TTableId>("", {"", "x"})}};
+            UNIT_ASSERT_VALUES_EQUAL(ctx.Column, expected);
+        }
+    }
+
 } // Y_UNIT_TEST_SUITE(GlobalAnalysisTests)
diff --git a/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.cpp b/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.cpp
index a26523c94a0..f1cd303062c 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.cpp
+++ b/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.cpp
@@ -12,13 +12,6 @@ namespace NSQLComplete {
         return TextInterval(node).a < static_cast<ssize_t>(CursorPosition_);
     }
 
-    std::any TSQLv1NarrowingVisitor::aggregateResult(std::any aggregate, std::any nextResult) {
-        if (nextResult.has_value()) {
-            return nextResult;
-        }
-        return aggregate;
-    }
-
     bool TSQLv1NarrowingVisitor::IsEnclosing(antlr4::tree::ParseTree* tree) const {
         return TextInterval(tree).properlyContains(CursorInterval());
     }
diff --git a/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.h b/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.h
index 5a3947b9355..1ad4bbe2274 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.h
+++ b/yql/essentials/sql/v1/complete/analysis/global/narrowing_visitor.h
@@ -1,16 +1,16 @@
 #pragma once
 
+#include "base_visitor.h"
 #include "parse_tree.h"
 
 namespace NSQLComplete {
 
-    class TSQLv1NarrowingVisitor: public SQLv1Antlr4BaseVisitor {
+    class TSQLv1NarrowingVisitor: public TSQLv1BaseVisitor {
     public:
         TSQLv1NarrowingVisitor(antlr4::TokenStream* tokens, size_t cursorPosition);
 
     protected:
         bool shouldVisitNextChild(antlr4::tree::ParseTree* node, const std::any& /*currentResult*/) override;
-        std::any aggregateResult(std::any aggregate, std::any nextResult) override;
 
         bool IsEnclosing(antlr4::tree::ParseTree* tree) const;
         ssize_t CursorPosition() const;
diff --git a/yql/essentials/sql/v1/complete/analysis/global/ya.make b/yql/essentials/sql/v1/complete/analysis/global/ya.make
index 1f1670a9675..b67f290a3be 100644
--- a/yql/essentials/sql/v1/complete/analysis/global/ya.make
+++ b/yql/essentials/sql/v1/complete/analysis/global/ya.make
@@ -1,6 +1,7 @@
 LIBRARY()
 
 SRCS(
+    base_visitor.cpp
     column.cpp
     evaluate.cpp
     function.cpp
diff --git a/yql/essentials/sql/v1/complete/check/check_complete_ut.cpp b/yql/essentials/sql/v1/complete/check/check_complete_ut.cpp
new file mode 100644
index 00000000000..7743f574321
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/check/check_complete_ut.cpp
@@ -0,0 +1,46 @@
+#include "check_complete.h"
+
+#include <library/cpp/testing/unittest/registar.h>
+
+#include <yql/essentials/sql/sql.h>
+#include <yql/essentials/sql/v1/sql.h>
+#include <yql/essentials/sql/v1/lexer/antlr4/lexer.h>
+#include <yql/essentials/sql/v1/lexer/antlr4_ansi/lexer.h>
+#include <yql/essentials/sql/v1/proto_parser/antlr4/proto_parser.h>
+#include <yql/essentials/sql/v1/proto_parser/antlr4_ansi/proto_parser.h>
+
+using namespace NSQLComplete;
+
+Y_UNIT_TEST_SUITE(CheckTests) {
+
+    Y_UNIT_TEST(Runs) {
+        TString query = R"(
+            SELECT * FROM (SELECT 1 AS x)
+        )";
+
+        NSQLTranslationV1::TLexers lexers = {
+            .Antlr4 = NSQLTranslationV1::MakeAntlr4LexerFactory(),
+            .Antlr4Ansi = NSQLTranslationV1::MakeAntlr4AnsiLexerFactory(),
+        };
+
+        NSQLTranslationV1::TParsers parsers = {
+            .Antlr4 = NSQLTranslationV1::MakeAntlr4ParserFactory(),
+            .Antlr4Ansi = NSQLTranslationV1::MakeAntlr4AnsiParserFactory(),
+        };
+
+        NSQLTranslation::TTranslators translators(
+            /* V0 = */ nullptr,
+            /* V1 = */ NSQLTranslationV1::MakeTranslator(lexers, parsers),
+            /* PG = */ nullptr);
+
+        google::protobuf::Arena arena;
+        NSQLTranslation::TTranslationSettings settings;
+        settings.Arena = &arena;
+
+        NYql::TAstParseResult result = NSQLTranslation::SqlToYql(translators, query, settings);
+        Y_ENSURE(result.IsOk());
+
+        Y_ENSURE(CheckComplete(query, *result.Root, result.Issues), result.Issues.ToString());
+    }
+
+} // Y_UNIT_TEST_SUITE(CheckTests)
diff --git a/yql/essentials/sql/v1/complete/check/ut/ya.make b/yql/essentials/sql/v1/complete/check/ut/ya.make
new file mode 100644
index 00000000000..3a16e9246f8
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/check/ut/ya.make
@@ -0,0 +1,18 @@
+UNITTEST_FOR(yql/essentials/sql/v1/complete/check)
+
+SRCS(
+    check_complete_ut.cpp
+)
+
+PEERDIR(
+    yql/essentials/parser/pg_wrapper
+    yql/essentials/public/udf/service/stub
+    yql/essentials/sql
+    yql/essentials/sql/v1
+    yql/essentials/sql/v1/lexer/antlr4
+    yql/essentials/sql/v1/lexer/antlr4_ansi
+    yql/essentials/sql/v1/proto_parser/antlr4
+    yql/essentials/sql/v1/proto_parser/antlr4_ansi
+)
+
+END()
diff --git a/yql/essentials/sql/v1/complete/check/ya.make b/yql/essentials/sql/v1/complete/check/ya.make
index 2a4d577fb51..8312f6bede3 100644
--- a/yql/essentials/sql/v1/complete/check/ya.make
+++ b/yql/essentials/sql/v1/complete/check/ya.make
@@ -19,3 +19,7 @@ PEERDIR(
 )
 
 END()
+
+RECURSE_FOR_TESTS(
+    ut
+)
diff --git a/yql/essentials/sql/v1/complete/name/service/binding/name_service.cpp b/yql/essentials/sql/v1/complete/name/service/binding/name_service.cpp
index 9de6380410b..52a0afccf8d 100644
--- a/yql/essentials/sql/v1/complete/name/service/binding/name_service.cpp
+++ b/yql/essentials/sql/v1/complete/name/service/binding/name_service.cpp
@@ -44,7 +44,6 @@ namespace NSQLComplete {
             }
 
             TNameIndex Index_;
-            IRanking::TPtr Ranking_;
         };
 
     } // namespace
diff --git a/yql/essentials/sql/v1/complete/name/service/binding/name_service.h b/yql/essentials/sql/v1/complete/name/service/binding/name_service.h
index 8fdac1a8d7e..4c9de468a93 100644
--- a/yql/essentials/sql/v1/complete/name/service/binding/name_service.h
+++ b/yql/essentials/sql/v1/complete/name/service/binding/name_service.h
@@ -3,7 +3,6 @@
 #pragma once
 
 #include <yql/essentials/sql/v1/complete/name/service/name_service.h>
-#include <yql/essentials/sql/v1/complete/name/service/ranking/ranking.h>
 
 namespace NSQLComplete {
 
diff --git a/yql/essentials/sql/v1/complete/name/service/column/name_service.cpp b/yql/essentials/sql/v1/complete/name/service/column/name_service.cpp
new file mode 100644
index 00000000000..9b87f5fe5e1
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/name/service/column/name_service.cpp
@@ -0,0 +1,76 @@
+#include "name_service.h"
+
+#include <yql/essentials/sql/v1/complete/name/object/simple/static/schema.h>
+
+namespace NSQLComplete {
+
+    namespace {
+
+        class TNameService: public INameService {
+        public:
+            explicit TNameService(TVector<TColumnId> columns) {
+                TSchemaData data;
+                for (auto& column : columns) {
+                    Tables_.emplace(column.TableAlias);
+
+                    data.Tables[""]["/" + Escaped(column.TableAlias)]
+                        .Columns
+                        .emplace_back(std::move(column.Name));
+                }
+
+                Schema_ = MakeSimpleSchema(MakeStaticSimpleSchema(std::move(data)));
+            }
+
+            NThreading::TFuture<TNameResponse> Lookup(TNameRequest request) const override {
+                if (!request.Constraints.Column) {
+                    return NThreading::MakeFuture<TNameResponse>({});
+                }
+
+                TNameResponse response;
+
+                for (const TString& tableName : Tables_) {
+                    TDescribeTableRequest describeRequest = {
+                        .TableCluster = "",
+                        .TablePath = Escaped(tableName),
+                        .ColumnPrefix = request.Prefix,
+                        .ColumnsLimit = request.Limit,
+                    };
+
+                    TDescribeTableResponse table =
+                        Schema_
+                            ->Describe(std::move(describeRequest))
+                            .ExtractValue();
+
+                    Y_ENSURE(table.IsExisting);
+                    for (TString& column : table.Columns) {
+                        TColumnName name;
+                        name.Table = {.Cluster = "", .Path = tableName};
+                        name.Indentifier = std::move(column);
+
+                        response.RankedNames.emplace_back(std::move(name));
+                    }
+                }
+
+                response.RankedNames.crop(request.Limit);
+
+                return NThreading::MakeFuture(std::move(response));
+            }
+
+        private:
+            static TString Escaped(TString tableName) {
+                // Saves when name is empty
+                tableName.prepend("table_");
+                return tableName;
+            }
+
+            THashSet<TString> Tables_;
+            ISchema::TPtr Schema_;
+        };
+
+    } // namespace
+
+    INameService::TPtr MakeColumnNameService(TVector<TColumnId> columns) {
+        return new TNameService(std::move(columns));
+    }
+
+} // namespace NSQLComplete
diff --git a/yql/essentials/sql/v1/complete/name/service/column/name_service.h b/yql/essentials/sql/v1/complete/name/service/column/name_service.h
new file mode 100644
index 00000000000..51d3239748a
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/name/service/column/name_service.h
@@ -0,0 +1,10 @@
+#pragma once
+
+#include <yql/essentials/sql/v1/complete/analysis/global/global.h>
+#include <yql/essentials/sql/v1/complete/name/service/name_service.h>
+
+namespace NSQLComplete {
+
+    INameService::TPtr MakeColumnNameService(TVector<TColumnId> columns);
+
+} // namespace NSQLComplete
diff --git a/yql/essentials/sql/v1/complete/name/service/column/ya.make b/yql/essentials/sql/v1/complete/name/service/column/ya.make
new file mode 100644
index 00000000000..64c11175e2f
--- /dev/null
+++ b/yql/essentials/sql/v1/complete/name/service/column/ya.make
@@ -0,0 +1,13 @@
+LIBRARY()
+
+SRCS(
+    name_service.cpp
+)
+
+PEERDIR(
+    yql/essentials/sql/v1/complete/analysis/global
+    yql/essentials/sql/v1/complete/name/object/simple/static
+    yql/essentials/sql/v1/complete/name/service
+)
+
+END()
diff --git a/yql/essentials/sql/v1/complete/name/service/ya.make b/yql/essentials/sql/v1/complete/name/service/ya.make
index 75a55e0c71f..7ddd090559b 100644
--- a/yql/essentials/sql/v1/complete/name/service/ya.make
+++ b/yql/essentials/sql/v1/complete/name/service/ya.make
@@ -14,6 +14,7 @@ END()
 RECURSE(
     binding
     cluster
+    column
     impatient
     ranking
     schema
diff --git a/yql/essentials/sql/v1/complete/sql_complete.cpp b/yql/essentials/sql/v1/complete/sql_complete.cpp
index 8b732bd3913..2cb88c714a7 100644
--- a/yql/essentials/sql/v1/complete/sql_complete.cpp
+++ b/yql/essentials/sql/v1/complete/sql_complete.cpp
@@ -2,8 +2,11 @@
 
 #include <yql/essentials/sql/v1/complete/syntax/grammar.h>
 #include <yql/essentials/sql/v1/complete/text/word.h>
+#include <yql/essentials/sql/v1/complete/name/object/simple/static/schema.h>
 #include <yql/essentials/sql/v1/complete/name/service/ranking/dummy.h>
 #include <yql/essentials/sql/v1/complete/name/service/binding/name_service.h>
+#include <yql/essentials/sql/v1/complete/name/service/column/name_service.h>
+#include <yql/essentials/sql/v1/complete/name/service/schema/name_service.h>
 #include <yql/essentials/sql/v1/complete/name/service/static/name_service.h>
 #include <yql/essentials/sql/v1/complete/name/service/union/name_service.h>
 #include <yql/essentials/sql/v1/complete/syntax/format.h>
@@ -61,7 +64,13 @@ namespace NSQLComplete {
             }
 
             TVector<INameService::TPtr> children;
+
             children.emplace_back(MakeBindingNameService(std::move(global.Names)));
+
+            if (!context.Binding && global.Column) {
+                children.emplace_back(MakeColumnNameService(std::move(global.Column->Columns)));
+            }
+
             if (!context.Binding) {
                 children.emplace_back(Names_);
             }
@@ -300,7 +309,11 @@ namespace NSQLComplete {
                 }
 
                 if constexpr (std::is_base_of_v<TColumnName, T>) {
-                    const TString& alias = aliasByTable.at(name.Table);
+                    TString alias = name.Table.Path;
+                    if (auto it = aliasByTable.find(name.Table); it != end(aliasByTable)) {
+                        alias = it->second;
+                    }
+
                     if (context.Column->Table.empty() && !alias.empty()) {
                         name.Indentifier.prepend('.');
                         name.Indentifier.prepend(alias);
diff --git a/yql/essentials/sql/v1/complete/sql_complete_ut.cpp b/yql/essentials/sql/v1/complete/sql_complete_ut.cpp
index 89576f63c3d..603ddae812a 100644
--- a/yql/essentials/sql/v1/complete/sql_complete_ut.cpp
+++ b/yql/essentials/sql/v1/complete/sql_complete_ut.cpp
@@ -1141,6 +1141,34 @@ Y_UNIT_TEST_SUITE(SqlCompleteTests) {
         }
     }
 
+    Y_UNIT_TEST(ColumnsAtSubquery) {
+        auto engine = MakeSqlCompletionEngineUT();
+        {
+            TString query = R"(
+                SELECT #
+                FROM (SELECT * FROM example.`/people`) AS ep
+                JOIN (SELECT room AS Room, time FROM example.`/yql/tutorial`) AS et ON 1 = 1
+            )";
+
+            TVector<TCandidate> expected = {
+                {ColumnName, "et.Room"},
+                {ColumnName, "et.time"},
+                {ColumnName, "ep.Age"},
+                {ColumnName, "ep.Name"},
+                {Keyword, "ALL"},
+            };
+            UNIT_ASSERT_VALUES_EQUAL(CompleteTop(5, engine, query), expected);
+        }
+        {
+            TString query = "SELECT # FROM (SELECT 1 AS x)";
+
+            TVector<TCandidate> expected = {
+                {ColumnName, "x"},
+            };
+            UNIT_ASSERT_VALUES_EQUAL(CompleteTop(1, engine, query), expected);
+        }
+    }
+
     Y_UNIT_TEST(Typing) {
         const auto queryUtf16 = TUtf16String::FromUtf8(
             "SELECT \n"
diff --git a/yql/essentials/sql/v1/complete/ya.make b/yql/essentials/sql/v1/complete/ya.make
index 8ad312703ea..e85ba90b524 100644
--- a/yql/essentials/sql/v1/complete/ya.make
+++ b/yql/essentials/sql/v1/complete/ya.make
@@ -16,6 +16,7 @@ PEERDIR(
     # TODO(YQL-19747): extract NameIndex
     yql/essentials/sql/v1/complete/name/service/ranking
     yql/essentials/sql/v1/complete/name/service/binding
+    yql/essentials/sql/v1/complete/name/service/column
 )
 
 END()