[RBO] Fix side metadata in right-to-left join rewrite (#46036)

5 files changed, 200 insertions, 7 deletions

diff --git a/ydb/core/kqp/ut/rbo/kqp_rbo_yql_ut.cpp b/ydb/core/kqp/ut/rbo/kqp_rbo_yql_ut.cpp
index 41c899aff90..f487b4a465f 100644
--- a/ydb/core/kqp/ut/rbo/kqp_rbo_yql_ut.cpp
+++ b/ydb/core/kqp/ut/rbo/kqp_rbo_yql_ut.cpp
@@ -8580,6 +8580,199 @@ PRAGMA ydb.OptimizerHints = '
                              << JoinSeq(", ", hashShuffles) << "\n" << plan);
     }
 
+    Y_UNIT_TEST(ShuffleEliminationRightJoinAliasReproducer) {
+        NKikimrConfig::TAppConfig appConfig;
+        appConfig.MutableTableServiceConfig()->SetEnableNewRBO(false);
+        appConfig.MutableTableServiceConfig()->SetEnableFallbackToYqlOptimizer(false);
+        appConfig.MutableTableServiceConfig()->SetAllowOlapDataQuery(true);
+        appConfig.MutableTableServiceConfig()->SetDefaultLangVer(NYql::GetMaxLangVersion());
+        appConfig.MutableTableServiceConfig()->SetBackportMode(NKikimrConfig::TTableServiceConfig_EBackportMode_All);
+        appConfig.MutableTableServiceConfig()->SetDefaultCostBasedOptimizationLevel(4);
+
+        NKikimrKqp::TKqpSetting statsSetting;
+        statsSetting.SetName("OptOverrideStatistics");
+        statsSetting.SetValue(R"({
+            "/Root/_temp/_pool_249": {"n_rows": 12, "byte_size": 1024},
+            "/Root/_temp/_pool_252": {"n_rows": 66464, "byte_size": 8517376},
+            "/Root/_temp/_pool_256": {"n_rows": 36, "byte_size": 2048},
+            "/Root/public/_accumrg28120": {"n_rows": 32601254, "byte_size": 4172960512}
+        })");
+
+        auto settings = NKqp::TKikimrSettings(appConfig).SetWithSampleTables(false);
+        settings.SetKqpSettings({statsSetting});
+        TKikimrRunner kikimr(settings);
+
+        auto schemeClient = kikimr.GetSchemeClient();
+        auto mkDir = schemeClient.MakeDirectory("/Root/_temp").GetValueSync();
+        UNIT_ASSERT_C(mkDir.IsSuccess(), mkDir.GetIssues().ToString());
+        mkDir = schemeClient.MakeDirectory("/Root/public").GetValueSync();
+        UNIT_ASSERT_C(mkDir.IsSuccess(), mkDir.GetIssues().ToString());
+
+        auto tableClient = kikimr.GetTableClient();
+        auto tableSession = tableClient.CreateSession().GetValueSync().GetSession();
+        auto schemeResult = tableSession.ExecuteSchemeQuery(R"(
+            CREATE TABLE `/Root/_temp/_pool_252` (
+                `_q_000_f_000` Decimal(10, 0),
+                `_q_000_f_001rref` String,
+                `_q_000_f_002rref` String,
+                `_q_000_f_003rref` String,
+                `_q_000_f_004rref` String,
+                `_q_000_f_005` Decimal(15, 3),
+                `_q_000_f_006` Decimal(31, 18),
+                `_ydb_pk` Int64 NOT NULL,
+                PRIMARY KEY (`_ydb_pk`)
+            );
+
+            CREATE TABLE `/Root/_temp/_pool_256` (
+                `_q_000_f_000rref` String,
+                `_ydb_pk` Int64 NOT NULL,
+                PRIMARY KEY (`_ydb_pk`)
+            );
+
+            CREATE TABLE `/Root/_temp/_pool_249` (
+                `_q_000_f_000_type` String,
+                `_q_000_f_000_rtref` String,
+                `_q_000_f_000_rrref` String,
+                `_ydb_pk` Int64 NOT NULL,
+                PRIMARY KEY (`_ydb_pk`)
+            );
+
+            CREATE TABLE `/Root/public/_accumrg28120` (
+                `_period` Timestamp64 NOT NULL,
+                `_recordertref` String NOT NULL,
+                `_recorderrref` String NOT NULL,
+                `_lineno` Decimal(9, 0) NOT NULL,
+                `_active` Bool NOT NULL,
+                `_recordkind` Decimal(1, 0) NOT NULL,
+                `_fld28121rref` String NOT NULL,
+                `_fld28122rref` String NOT NULL,
+                `_fld28123rref` String NOT NULL,
+                `_fld28124rref` String NOT NULL,
+                `_fld28125` Decimal(15, 3) NOT NULL,
+                `_fld28126` Decimal(15, 2) NOT NULL,
+                `_fld28127_type` String NOT NULL,
+                `_fld28127_rtref` String NOT NULL,
+                `_fld28127_rrref` String NOT NULL,
+                `_fld28128_type` String NOT NULL,
+                `_fld28128_rtref` String NOT NULL,
+                `_fld28128_rrref` String NOT NULL,
+                `_fld28129rref` String NOT NULL,
+                `_fld28130rref` String NOT NULL,
+                `_fld28131rref` String NOT NULL,
+                `_ydb_pk` Int64 NOT NULL,
+                PRIMARY KEY (`_ydb_pk`)
+            );
+        )").GetValueSync();
+        UNIT_ASSERT_C(schemeResult.IsSuccess(), schemeResult.GetIssues().ToString());
+
+        const TString query = R"sql(
+            PRAGMA ydb.CostBasedOptimizationLevel = "4";
+            PRAGMA ydb.OptShuffleElimination = "true";
+
+            DECLARE $const_0 AS String;
+            DECLARE $const_1 AS String;
+            DECLARE $const_2 AS Timestamp64;
+            DECLARE $const_3 AS Timestamp64;
+            DECLARE $const_4 AS Decimal(1, 0);
+
+            SELECT
+                `s2`.`__ydb_1_7`,
+                `s2`.`__ydb_1_8`,
+                `s2`.`__ydb_1_9`,
+                `s2`.`__ydb_1_10`,
+                `s2`.`__ydb_1_13`,
+                `s2`.`__ydb_1_14`,
+                `s2`.`__ydb_1_15`,
+                `t4`.`_q_000_f_000`,
+                `s2`.`__ydb_1_16`,
+                `s2`.`__ydb_1_17`,
+                `s2`.`__ydb_1_18`,
+                `s2`.`__ydb_1_19`,
+                `s2`.`__ydb_1_20`,
+                `s2`.`__ydb_1_21`,
+                `s2`.`__ydb_6_1`,
+                `s2`.`__ydb_1_12`
+            FROM `/Root/_temp/_pool_252` AS `t4`
+            RIGHT JOIN (
+                SELECT
+                    `s1`.`__ydb_1_7` AS `__ydb_1_7`,
+                    `s1`.`__ydb_1_8` AS `__ydb_1_8`,
+                    `s1`.`__ydb_1_9` AS `__ydb_1_9`,
+                    `s1`.`__ydb_1_10` AS `__ydb_1_10`,
+                    `s1`.`__ydb_1_13` AS `__ydb_1_13`,
+                    `s1`.`__ydb_1_14` AS `__ydb_1_14`,
+                    `s1`.`__ydb_1_15` AS `__ydb_1_15`,
+                    `s1`.`__ydb_1_16` AS `__ydb_1_16`,
+                    `s1`.`__ydb_1_17` AS `__ydb_1_17`,
+                    `s1`.`__ydb_1_18` AS `__ydb_1_18`,
+                    `s1`.`__ydb_1_19` AS `__ydb_1_19`,
+                    `s1`.`__ydb_1_20` AS `__ydb_1_20`,
+                    `s1`.`__ydb_1_21` AS `__ydb_1_21`,
+                    `s1`.`__ydb_1_12` AS `__ydb_1_12`,
+                    `s1`.`__ydb_6_1` AS `__ydb_6_1`
+                FROM `/Root/_temp/_pool_256` AS `t2`
+                INNER JOIN (
+                    SELECT
+                        `t1`.`_fld28121rref` AS `__ydb_1_7`,
+                        `t1`.`_fld28122rref` AS `__ydb_1_8`,
+                        `t1`.`_fld28123rref` AS `__ydb_1_9`,
+                        `t1`.`_fld28124rref` AS `__ydb_1_10`,
+                        `t1`.`_fld28127_type` AS `__ydb_1_13`,
+                        `t1`.`_fld28127_rtref` AS `__ydb_1_14`,
+                        `t1`.`_fld28127_rrref` AS `__ydb_1_15`,
+                        `t1`.`_fld28128_type` AS `__ydb_1_16`,
+                        `t1`.`_fld28128_rtref` AS `__ydb_1_17`,
+                        `t1`.`_fld28128_rrref` AS `__ydb_1_18`,
+                        `t1`.`_fld28129rref` AS `__ydb_1_19`,
+                        `t1`.`_fld28130rref` AS `__ydb_1_20`,
+                        `t1`.`_fld28131rref` AS `__ydb_1_21`,
+                        `t1`.`_fld28126` AS `__ydb_1_12`,
+                        `t6`.`__ydb_6_1` AS `__ydb_6_1`
+                    FROM `/Root/public/_accumrg28120` AS `t1`
+                    LEFT JOIN (
+                        SELECT
+                            `t1`.`_ydb_pk` AS `__ydb_pk_0`,
+                            `t6`.`_q_000_f_000` AS `__ydb_6_1`,
+                            `t6`.`_q_000_f_001rref` AS `__ydb_6_2`,
+                            `t6`.`_q_000_f_002rref` AS `__ydb_6_3`,
+                            `t6`.`_q_000_f_003rref` AS `__ydb_6_4`,
+                            `t6`.`_q_000_f_004rref` AS `__ydb_6_5`,
+                            `t6`.`_ydb_pk` AS `_ydb_pk`
+                        FROM `/Root/public/_accumrg28120` AS `t1`
+                        INNER JOIN `/Root/_temp/_pool_252` AS `t6`
+                            ON ((`t6`.`_q_000_f_001rref` = `t1`.`_fld28128_rrref`))
+                            AND ((`t6`.`_q_000_f_002rref` = `t1`.`_fld28129rref`))
+                            AND ((`t6`.`_q_000_f_003rref` = `t1`.`_fld28130rref`))
+                            AND ((`t6`.`_q_000_f_004rref` = `t1`.`_fld28131rref`))
+                        WHERE (($const_0 = `t1`.`_fld28128_type`))
+                            AND (($const_1 = `t1`.`_fld28128_rtref`))
+                    ) AS `t6`
+                        ON (`t1`.`_ydb_pk` = `t6`.`__ydb_pk_0`)
+                    LEFT ONLY JOIN `/Root/_temp/_pool_249` AS `t8`
+                        ON ((`t1`.`_fld28127_type` = `t8`.`_q_000_f_000_type`))
+                        AND ((`t1`.`_fld28127_rtref` = `t8`.`_q_000_f_000_rtref`))
+                        AND ((`t1`.`_fld28127_rrref` = `t8`.`_q_000_f_000_rrref`))
+                    WHERE ((`t1`.`_period` >= $const_2))
+                        AND ((`t1`.`_period` <= $const_3))
+                        AND (`t1`.`_active`)
+                        AND ((`t1`.`_recordkind` = $const_4))
+                ) AS `s1`
+                    ON ((`s1`.`__ydb_1_7` = `t2`.`_q_000_f_000rref`))
+            ) AS `s2`
+                ON ((`t4`.`_q_000_f_001rref` = `s2`.`__ydb_1_7`))
+                AND ((`t4`.`_q_000_f_002rref` = `s2`.`__ydb_1_8`))
+                AND ((`t4`.`_q_000_f_003rref` = `s2`.`__ydb_1_9`))
+                AND ((`t4`.`_q_000_f_004rref` = `s2`.`__ydb_1_10`))
+        )sql";
+
+        auto result = tableSession.ExplainDataQuery(query).GetValueSync();
+
+        result.GetIssues().PrintTo(Cerr);
+        UNIT_ASSERT_C(result.IsSuccess(), result.GetIssues().ToString());
+
+        UNIT_ASSERT_C(!result.GetPlan().empty(), result.GetPlan());
+    }
+
     void InsertIntoAliasesRenames(NYdb::NTable::TTableClient &db, std::string tableName, int numRows) {
         NYdb::TValueBuilder rows;
         rows.BeginList();
diff --git a/ydb/library/yql/dq/opt/dq_opt_join.cpp b/ydb/library/yql/dq/opt/dq_opt_join.cpp
index 897ec2b8904..2894894a2e9 100644
--- a/ydb/library/yql/dq/opt/dq_opt_join.cpp
+++ b/ydb/library/yql/dq/opt/dq_opt_join.cpp
@@ -759,11 +759,11 @@ TExprBase DqRewriteRightJoinToLeft(const TExprBase node, TExprContext& ctx) {
             .Value(RotateRightJoinType(dqJoin.JoinType().Value()))
             .Build()
         .JoinKeys(joinKeysBuilder.Done())
-        .LeftJoinKeyNames(dqJoin.LeftJoinKeyNames())
-        .RightJoinKeyNames(dqJoin.RightJoinKeyNames())
+        .LeftJoinKeyNames(dqJoin.RightJoinKeyNames())
+        .RightJoinKeyNames(dqJoin.LeftJoinKeyNames())
         .JoinAlgo(dqJoin.JoinAlgo())
-        .ShuffleLeftSideBy(dqJoin.ShuffleLeftSideBy())
-        .ShuffleRightSideBy(dqJoin.ShuffleRightSideBy())
+        .ShuffleLeftSideBy(dqJoin.ShuffleRightSideBy())
+        .ShuffleRightSideBy(dqJoin.ShuffleLeftSideBy())
         .JoinAlgoOptions(dqJoin.JoinAlgoOptions())
         .Flags(newFlags)
         .Done();
diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_10.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_10.plan
index b47325c7443..b83da6a1e42 100644
--- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_10.plan
+++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_10.plan
@@ -42,7 +42,7 @@
                                                 "TopSortBy": "[Arg.J2_TBL.i,Arg.J1_TBL.j,Arg.J1_TBL.t,Arg.J2_TBL.k]"
                                             },
                                             {
-                                                "Condition": "J1_TBL.i = J2_TBL.i",
+                                                "Condition": "J2_TBL.i = J1_TBL.i",
                                                 "Inputs": [
                                                     {
                                                         "ExternalPlanNodeId": 4
diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_9.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_9.plan
index b47325c7443..b83da6a1e42 100644
--- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_9.plan
+++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_9.plan
@@ -42,7 +42,7 @@
                                                 "TopSortBy": "[Arg.J2_TBL.i,Arg.J1_TBL.j,Arg.J1_TBL.t,Arg.J2_TBL.k]"
                                             },
                                             {
-                                                "Condition": "J1_TBL.i = J2_TBL.i",
+                                                "Condition": "J2_TBL.i = J1_TBL.i",
                                                 "Inputs": [
                                                     {
                                                         "ExternalPlanNodeId": 4
diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_9.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_9.plan
index d1b9d85cc83..0a55a817146 100644
--- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_9.plan
+++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_9.plan
@@ -42,7 +42,7 @@
                                                 "TopSortBy": "[Arg.tt1.tt1_id,Arg.tt1.joincol,Arg.tt2.tt2_id]"
                                             },
                                             {
-                                                "Condition": "tt2.joincol = tt1.joincol",
+                                                "Condition": "tt1.joincol = tt2.joincol",
                                                 "Inputs": [
                                                     {
                                                         "ExternalPlanNodeId": 4