KIKIMR-18864: fix column order for index table reading result

4 files changed, 116 insertions, 20 deletions

diff --git a/ydb/core/kqp/opt/logical/kqp_opt_log_indexes.cpp b/ydb/core/kqp/opt/logical/kqp_opt_log_indexes.cpp
index c843c1a13d..f8818534ad 100644
--- a/ydb/core/kqp/opt/logical/kqp_opt_log_indexes.cpp
+++ b/ydb/core/kqp/opt/logical/kqp_opt_log_indexes.cpp
@@ -16,43 +16,45 @@ using namespace NYql::NNodes;
 namespace {
 
 TCoAtomList BuildKeyColumnsList(const TKikimrTableDescription& table, TPositionHandle pos, TExprContext& ctx) {
-    TVector<TExprBase> columnsToSelect;
-    columnsToSelect.reserve(table.Metadata->KeyColumnNames.size());
-    for (auto key : table.Metadata->KeyColumnNames) {
-        auto value = table.Metadata->Columns.at(key);
+    TSet<TString> columnsToSelect(table.Metadata->KeyColumnNames.begin(), table.Metadata->KeyColumnNames.end());
+    TVector<TExprBase> columnsList;
+    columnsList.reserve(columnsToSelect.size());
+    for (auto column : columnsToSelect) {
         auto atom = Build<TCoAtom>(ctx, pos)
-            .Value(value.Name)
+            .Value(column)
             .Done();
 
-        columnsToSelect.push_back(atom);
+        columnsList.emplace_back(std::move(atom));
     }
 
     return Build<TCoAtomList>(ctx, pos)
-        .Add(columnsToSelect)
+        .Add(columnsList)
         .Done();
 }
 
 TCoAtomList MergeColumns(const NNodes::TCoAtomList& col1, const TVector<TString>& col2, TExprContext& ctx) {
-    TVector<TCoAtom> columns;
-    THashSet<TString> uniqColumns;
-    columns.reserve(col1.Size() + col2.size());
-
+    TMap<TString, TCoAtom> columns;
     for (const auto& c : col1) {
-        YQL_ENSURE(uniqColumns.emplace(c.StringValue()).second);
-        columns.push_back(c);
+        YQL_ENSURE(columns.insert({c.StringValue(), c}).second);
     }
 
     for (const auto& c : col2) {
-        if (uniqColumns.emplace(c).second) {
+        if (!columns.contains(c)) {
             auto atom = Build<TCoAtom>(ctx, col1.Pos())
                 .Value(c)
                 .Done();
-            columns.push_back(atom);
+            columns.insert({c, std::move(atom)});
         }
     }
 
+    TVector<TCoAtom> columnsList;
+    columnsList.reserve(columns.size());
+    for (auto [_, column] : columns) {
+        columnsList.emplace_back(std::move(column));
+    }
+
     return Build<TCoAtomList>(ctx, col1.Pos())
-        .Add(columns)
+        .Add(columnsList)
         .Done();
 }
 
diff --git a/ydb/core/kqp/ut/scan/kqp_scan_ut.cpp b/ydb/core/kqp/ut/scan/kqp_scan_ut.cpp
index 550145ef77..b8e17e1218 100644
--- a/ydb/core/kqp/ut/scan/kqp_scan_ut.cpp
+++ b/ydb/core/kqp/ut/scan/kqp_scan_ut.cpp
@@ -1823,6 +1823,100 @@ Y_UNIT_TEST_SUITE(KqpScan) {
         }
     }
 
+    Y_UNIT_TEST(SecondaryIndexCustomColumnOrder) {
+        TKikimrRunner kikimr;
+        auto db = kikimr.GetTableClient();
+        auto session = db.CreateSession().GetValueSync().GetSession();
+
+        CreateSampleTablesWithIndex(session);
+
+        {  // prepare table
+            auto res = session.ExecuteSchemeQuery(R"(
+                --!syntax_v1
+                CREATE TABLE `/Root/SecondaryKeysCustomOrder` (
+                    Key2 Int32,
+                    Key1 String,
+                    Fk2 Int32,
+                    Fk1 String,
+                    Value String,
+                    PRIMARY KEY (Key2, Key1),
+                    INDEX Index GLOBAL ON (Fk2, Fk1)
+                );
+            )").GetValueSync();
+            UNIT_ASSERT_C(res.IsSuccess(), res.GetIssues().ToString());
+
+            auto result = session.ExecuteDataQuery(R"(
+
+            REPLACE INTO `/Root/SecondaryKeysCustomOrder` (Key2, Key1, Fk2, Fk1, Value) VALUES
+                (1u, "One", 1u, "Fk1", "Value1"),
+                (2u, "Two", 2u, "Fk2", "Value2"),
+                (3u, "Three", 3u, "Fk3", Null),
+                (NULL, "Four", 4u, Null, "Value4"),
+                (5u, Null, 5u, "Fk5",  "Value5");
+            )", TTxControl::BeginTx().CommitTx()).GetValueSync();
+            UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+        }
+
+        {
+            auto itIndex = db.StreamExecuteScanQuery(R"(
+                SELECT Value, Fk1
+                FROM `/Root/SecondaryKeysCustomOrder` VIEW Index
+                WHERE Fk2 = 2u;
+            )").GetValueSync();
+
+            UNIT_ASSERT_C(itIndex.IsSuccess(), itIndex.GetIssues().ToString());
+            CompareYson(R"([
+                [["Value2"];["Fk2"]]
+            ])", StreamResultToYson(itIndex));
+        }
+
+        {
+            auto itIndex = db.StreamExecuteScanQuery(R"(
+                SELECT Value, Fk1, Key2
+                FROM `/Root/SecondaryKeysCustomOrder` VIEW Index
+                WHERE Fk2 >= 4u AND Fk1 IS NULL;
+            )").GetValueSync();
+
+            UNIT_ASSERT_C(itIndex.IsSuccess(), itIndex.GetIssues().ToString());
+            CompareYson(R"([
+                [["Value4"];#;#]
+            ])", StreamResultToYson(itIndex));
+        }
+
+        {
+            auto itIndex = db.StreamExecuteScanQuery(R"(
+                PRAGMA AnsiInForEmptyOrNullableItemsCollections;
+                SELECT Value, Fk1, Key1
+                FROM `/Root/SecondaryKeysCustomOrder` VIEW Index
+                WHERE (Fk2, Fk1) IN AsList((1u, "Fk1"), (2u, "Fk2"), (5u, "Fk5"))
+                ORDER BY Value;
+            )").GetValueSync();
+
+            UNIT_ASSERT_C(itIndex.IsSuccess(), itIndex.GetIssues().ToString());
+            CompareYson(R"([
+                [["Value1"];["Fk1"];["One"]];
+                [["Value2"];["Fk2"];["Two"]];
+                [["Value5"];["Fk5"];#]
+            ])", StreamResultToYson(itIndex));
+        }
+
+        {
+            auto itIndex = db.StreamExecuteScanQuery(R"(
+                SELECT r.Value, l.Value
+                FROM `/Root/SecondaryKeys` VIEW Index AS l
+                INNER JOIN `/Root/SecondaryKeysCustomOrder` VIEW Index AS r
+                ON l.Fk = r.Fk2 ORDER BY r.Value;
+            )").GetValueSync();
+
+            UNIT_ASSERT_C(itIndex.IsSuccess(), itIndex.GetIssues().ToString());
+            CompareYson(R"([
+                [["Value1"];["Payload1"]];
+                [["Value2"];["Payload2"]];
+                [["Value5"];["Payload5"]]
+            ])", StreamResultToYson(itIndex));
+        }
+    }
+
     Y_UNIT_TEST(BoolFlag) {
         auto kikimr = DefaultKikimrRunner({}, AppCfg());
 
diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_nonselector_aliases.sql-plan_/index_topsort_index_with_nonselector_aliases.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_nonselector_aliases.sql-plan_/index_topsort_index_with_nonselector_aliases.sql.plan
index 52d567d81a..5a1eb9c14a 100644
--- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_nonselector_aliases.sql-plan_/index_topsort_index_with_nonselector_aliases.sql.plan
+++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_nonselector_aliases.sql-plan_/index_topsort_index_with_nonselector_aliases.sql.plan
@@ -22,9 +22,9 @@
             "reads": [
                 {
                     "columns": [
-                        "Key1",
                         "Index1A",
-                        "Index1B"
+                        "Index1B",
+                        "Key1"
                     ],
                     "limit": "2",
                     "reverse": true,
diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_selector_aliases.sql-plan_/index_topsort_index_with_selector_aliases.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_selector_aliases.sql-plan_/index_topsort_index_with_selector_aliases.sql.plan
index ea5a82d5f0..a185d5fc1c 100644
--- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_selector_aliases.sql-plan_/index_topsort_index_with_selector_aliases.sql.plan
+++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_index_topsort_index_with_selector_aliases.sql-plan_/index_topsort_index_with_selector_aliases.sql.plan
@@ -22,9 +22,9 @@
             "reads": [
                 {
                     "columns": [
-                        "Key1",
                         "Index1A",
-                        "Index1B"
+                        "Index1B",
+                        "Key1"
                     ],
                     "limit": "2",
                     "reverse": true,