Fix handling of uncorrelated column name in ORDER BY (#9602)

5 files changed, 81 insertions, 0 deletions

diff --git a/ydb/library/yql/sql/v1/node.cpp b/ydb/library/yql/sql/v1/node.cpp
index abf6047459b..f964c45ce84 100644
--- a/ydb/library/yql/sql/v1/node.cpp
+++ b/ydb/library/yql/sql/v1/node.cpp
@@ -1336,12 +1336,30 @@ bool MaybeAutogenerated(const TString& name) {
     return !suffix.empty() && AllOf(suffix, [](const auto c) { return std::isdigit(c); });
 }
 
+bool MatchDotSuffix(const TSet<TString>& columns, const TString& column) {
+    for (const auto& col: columns) {
+        const auto pos = col.find_first_of(".");
+        if (pos == TString::npos) {
+            continue;
+        }
+        if (column == col.substr(pos + 1)) {
+            return true;
+        }
+    }
+    return false;
+}
+
 }
 
 bool TColumns::IsColumnPossible(TContext& ctx, const TString& name) const {
     if (All || Real.contains(name) || Artificial.contains(name)) {
         return true;
     }
+
+    if (ctx.SimpleColumns && !name.Contains('.') && (MatchDotSuffix(Real, name) || MatchDotSuffix(Artificial, name))) {
+        return true;
+    }
+
     if (QualifiedAll) {
         if (ctx.SimpleColumns) {
             return true;
diff --git a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
index 46c16bd491e..3ca08f1d2b1 100644
--- a/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
+++ b/ydb/library/yql/tests/sql/sql2yql/canondata/result.json
@@ -11738,6 +11738,13 @@
             "uri": "https://{canondata_backend}/1937001/6c1efa96a2ae4050b12a9381285624ab71389741/resource.tar.gz#test_sql2yql.test_order_by-order_by_missing_project_column_as_table_/sql.yql"
         }
     ],
+    "test_sql2yql.test[order_by-order_by_missing_project_column_join2]": [
+        {
+            "checksum": "98039874784e044ceb5ba77e2e8158b8",
+            "size": 3496,
+            "uri": "https://{canondata_backend}/1814674/b5f58d06faf616e33e81d1596c8a849f37284058/resource.tar.gz#test_sql2yql.test_order_by-order_by_missing_project_column_join2_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[order_by-order_by_missing_project_column_join]": [
         {
             "checksum": "74c9e8c341785c084195974f3649ccc6",
@@ -31415,6 +31422,13 @@
             "uri": "https://{canondata_backend}/1937001/6c1efa96a2ae4050b12a9381285624ab71389741/resource.tar.gz#test_sql_format.test_order_by-order_by_missing_project_column_as_table_/formatted.sql"
         }
     ],
+    "test_sql_format.test[order_by-order_by_missing_project_column_join2]": [
+        {
+            "checksum": "2c530f4a349a9abe39d58c9251c325bb",
+            "size": 615,
+            "uri": "https://{canondata_backend}/1814674/b5f58d06faf616e33e81d1596c8a849f37284058/resource.tar.gz#test_sql_format.test_order_by-order_by_missing_project_column_join2_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[order_by-order_by_missing_project_column_join]": [
         {
             "checksum": "0ece4190b44e63e1633ee74eb8e135db",
diff --git a/ydb/library/yql/tests/sql/suites/order_by/order_by_missing_project_column_join2.cfg b/ydb/library/yql/tests/sql/suites/order_by/order_by_missing_project_column_join2.cfg
new file mode 100644
index 00000000000..bb349dd8abd
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/order_by/order_by_missing_project_column_join2.cfg
@@ -0,0 +1 @@
+providers yt
diff --git a/ydb/library/yql/tests/sql/suites/order_by/order_by_missing_project_column_join2.sql b/ydb/library/yql/tests/sql/suites/order_by/order_by_missing_project_column_join2.sql
new file mode 100644
index 00000000000..fa283b1894c
--- /dev/null
+++ b/ydb/library/yql/tests/sql/suites/order_by/order_by_missing_project_column_join2.sql
@@ -0,0 +1,27 @@
+/* syntax version 1 */
+/* postgres can not */
+/* dq can not */
+/* dqfile can not */
+/* yt can not */
+
+$src = [
+<|a:5, b:50, zz:500|>,
+<|a:4, b:40, zz:400|>,
+<|a:3, b:30, zz:300|>,
+<|a:2, b:20, zz:200|>,
+<|a:1, b:10, zz:100|>,
+];
+
+$src1 = [
+<|e:5, f:50|>,
+<|e:4, f:40|>,
+<|e:3, f:30|>,
+<|e:2, f:20|>,
+<|e:1, f:10|>,
+];
+
+
+$src = select * from as_table($src);
+$src1 = select * from as_table($src1);
+
+select x.zz, x.b + y.f as col1 from $src as x cross join $src1 as y where x.a = y.e order by zz, col1;
diff --git a/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json b/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json
index cde53ae0b96..a9093196fab 100644
--- a/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json
+++ b/ydb/library/yql/tests/sql/yt_native_file/part9/canondata/result.json
@@ -1713,6 +1713,27 @@
             "uri": "https://{canondata_backend}/1775059/d0c85f9ec0b8e84cf0b352e7062a1381f04420c8/resource.tar.gz#test.test_order_by-SortByOneFieldDesc--Results_/results.txt"
         }
     ],
+    "test.test[order_by-order_by_missing_project_column_join2--Debug]": [
+        {
+            "checksum": "ad887ace16ff2a422f178c08fef0c383",
+            "size": 1406,
+            "uri": "https://{canondata_backend}/1920236/1cce7ab93473c0f2eaed7fabadf44cba4fa8d8ca/resource.tar.gz#test.test_order_by-order_by_missing_project_column_join2--Debug_/opt.yql"
+        }
+    ],
+    "test.test[order_by-order_by_missing_project_column_join2--Plan]": [
+        {
+            "checksum": "b4dd508a329723c74293d80f0278c705",
+            "size": 505,
+            "uri": "https://{canondata_backend}/1920236/1cce7ab93473c0f2eaed7fabadf44cba4fa8d8ca/resource.tar.gz#test.test_order_by-order_by_missing_project_column_join2--Plan_/plan.txt"
+        }
+    ],
+    "test.test[order_by-order_by_missing_project_column_join2--Results]": [
+        {
+            "checksum": "3d9e8767efbc8ca0d58672911026268f",
+            "size": 1376,
+            "uri": "https://{canondata_backend}/1920236/1cce7ab93473c0f2eaed7fabadf44cba4fa8d8ca/resource.tar.gz#test.test_order_by-order_by_missing_project_column_join2--Results_/results.txt"
+        }
+    ],
     "test.test[order_by-single_item_tuple-default.txt-Debug]": [
         {
             "checksum": "22e49fe5842f57474ff573719f20251a",