YQL-19896: allow optional side in SingleInputPredicatePushdownOverEquiJoin in some cases

Разрешаем пушить на необязательную сторону предикат, который использует только ключевые колонки с обязательной стороны джоина.

1) рассматриваемый предикат зависит только 1 стороны и это обязательная сторона джоина

2) предикат зависит только от ключевых колонок джоина

3) все соответствующие по ключу джоина колонки в обязательном  и необязательном инпутах должны полностью совпадать по типу

В этих случаях мы пушим предикат не только на обязательную, но и на необязательную сторону
commit_hash:1404e877fdd271dbe8342478a20b99b6f99a4fda

4 files changed, 346 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
index e6d2cfd6553..c1628656b33 100644
--- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
@@ -877,6 +877,20 @@
             "uri": "https://{canondata_backend}/1784117/dbcd3ba4a0d5f6082e54161256fb75ae88cbff44/resource.tar.gz#test.test_join-join_comp_inmem-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[join-pushdown_all_sides-default.txt-Debug]": [
+        {
+            "checksum": "c6ad5b6c8904b81eda5617d3f90e0f03",
+            "size": 1927,
+            "uri": "https://{canondata_backend}/1599023/db1fc424c33f1e3d7379b8d279c7b3ecfd28ced1/resource.tar.gz#test.test_join-pushdown_all_sides-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[join-pushdown_all_sides-default.txt-Results]": [
+        {
+            "checksum": "71f164814672fba7d6bb48c998ecbd0d",
+            "size": 6703,
+            "uri": "https://{canondata_backend}/1597364/a08acb9a39de513be38f4870d1fa364d442a056c/resource.tar.gz#test.test_join-pushdown_all_sides-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[json-json_query/on_error-default.txt-Debug]": [
         {
             "checksum": "e88d18b7b03f5445093c7ffb4f15d241",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 3f6e7f09e9a..b4351ac41d7 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -4234,6 +4234,13 @@
             "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql"
         }
     ],
+    "test_sql2yql.test[join-pushdown_all_sides]": [
+        {
+            "checksum": "238c8b175f8fc2f12799c91433faf584",
+            "size": 10078,
+            "uri": "https://{canondata_backend}/1599023/96e9363c53a78c2884ae76b39bd982d37627eefa/resource.tar.gz#test_sql2yql.test_join-pushdown_all_sides_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-yql-19192]": [
         {
             "checksum": "fffdf1cbb40643da9daf9bdf3edec121",
@@ -10871,6 +10878,11 @@
             "uri": "file://test_sql_format.test_join-prune_keys_YQL-19979_/formatted.sql"
         }
     ],
+    "test_sql_format.test[join-pushdown_all_sides]": [
+        {
+            "uri": "file://test_sql_format.test_join-pushdown_all_sides_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[join-yql-19192]": [
         {
             "uri": "file://test_sql_format.test_join-yql-19192_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-pushdown_all_sides_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-pushdown_all_sides_/formatted.sql
new file mode 100644
index 00000000000..e634a7de154
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-pushdown_all_sides_/formatted.sql
@@ -0,0 +1,175 @@
+PRAGMA config.flags('OptimizerFlags', 'PredicatePushdownOverEquiJoinBothSides');
+
+SELECT
+    *
+FROM
+    AS_TABLE([
+        <|k1: -2|>,
+        <|k1: -1|>,
+        <|k1: 0|>,
+        <|k1: 0|>,
+        <|k1: 1|>,
+        <|k1: 2|>,
+        <|k1: 3|>,
+        <|k1: 4|>,
+    ]) AS t1
+LEFT JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t1.k1 > 0
+;
+
+SELECT
+    *
+FROM
+    AS_TABLE([
+        <|k1: -2|>,
+        <|k1: -1|>,
+        <|k1: 0|>,
+        <|k1: 0|>,
+        <|k1: 1|>,
+        <|k1: 2|>,
+        <|k1: 3|>,
+        <|k1: 4|>,
+    ]) AS t1
+RIGHT JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t2.k2 > 0
+;
+
+SELECT
+    *
+FROM
+    AS_TABLE([
+        <|k1: -2|>,
+        <|k1: -1|>,
+        <|k1: 0|>,
+        <|k1: 0|>,
+        <|k1: 1|>,
+        <|k1: 2|>,
+        <|k1: 3|>,
+        <|k1: 4|>,
+    ]) AS t1
+LEFT SEMI JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t1.k1 > 0
+;
+
+SELECT
+    *
+FROM
+    AS_TABLE([
+        <|k1: -2|>,
+        <|k1: -1|>,
+        <|k1: 0|>,
+        <|k1: 0|>,
+        <|k1: 1|>,
+        <|k1: 2|>,
+        <|k1: 3|>,
+        <|k1: 4|>,
+    ]) AS t1
+RIGHT SEMI JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t2.k2 > 0
+;
+
+SELECT
+    *
+FROM
+    AS_TABLE([
+        <|k1: -2|>,
+        <|k1: -1|>,
+        <|k1: 0|>,
+        <|k1: 0|>,
+        <|k1: 1|>,
+        <|k1: 2|>,
+        <|k1: 3|>,
+        <|k1: 4|>,
+    ]) AS t1
+LEFT ONLY JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t1.k1 > 0
+;
+
+SELECT
+    *
+FROM
+    AS_TABLE([
+        <|k1: -2|>,
+        <|k1: -1|>,
+        <|k1: 0|>,
+        <|k1: 0|>,
+        <|k1: 1|>,
+        <|k1: 2|>,
+        <|k1: 3|>,
+        <|k1: 4|>,
+    ]) AS t1
+RIGHT ONLY JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t2.k2 > 0
+;
diff --git a/yql/essentials/tests/sql/suites/join/pushdown_all_sides.sql b/yql/essentials/tests/sql/suites/join/pushdown_all_sides.sql
new file mode 100644
index 00000000000..6abbd0890a9
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/join/pushdown_all_sides.sql
@@ -0,0 +1,145 @@
+PRAGMA config.flags("OptimizerFlags", "PredicatePushdownOverEquiJoinBothSides");
+
+SELECT *
+FROM AS_TABLE([
+    <|k1: -2|>,
+    <|k1: -1|>,
+    <|k1: 0|>,
+    <|k1: 0|>,
+    <|k1: 1|>,
+    <|k1: 2|>,
+    <|k1: 3|>,
+    <|k1: 4|>,
+]) AS t1
+LEFT JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t1.k1 > 0
+;
+
+SELECT *
+FROM AS_TABLE([
+    <|k1: -2|>,
+    <|k1: -1|>,
+    <|k1: 0|>,
+    <|k1: 0|>,
+    <|k1: 1|>,
+    <|k1: 2|>,
+    <|k1: 3|>,
+    <|k1: 4|>,
+]) AS t1
+RIGHT JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t2.k2 > 0
+;
+
+SELECT *
+FROM AS_TABLE([
+    <|k1: -2|>,
+    <|k1: -1|>,
+    <|k1: 0|>,
+    <|k1: 0|>,
+    <|k1: 1|>,
+    <|k1: 2|>,
+    <|k1: 3|>,
+    <|k1: 4|>,
+]) AS t1
+LEFT SEMI JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t1.k1 > 0
+;
+
+SELECT *
+FROM AS_TABLE([
+    <|k1: -2|>,
+    <|k1: -1|>,
+    <|k1: 0|>,
+    <|k1: 0|>,
+    <|k1: 1|>,
+    <|k1: 2|>,
+    <|k1: 3|>,
+    <|k1: 4|>,
+]) AS t1
+RIGHT SEMI JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t2.k2 > 0
+;
+
+SELECT *
+FROM AS_TABLE([
+    <|k1: -2|>,
+    <|k1: -1|>,
+    <|k1: 0|>,
+    <|k1: 0|>,
+    <|k1: 1|>,
+    <|k1: 2|>,
+    <|k1: 3|>,
+    <|k1: 4|>,
+]) AS t1
+LEFT ONLY JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t1.k1 > 0
+;
+
+SELECT *
+FROM AS_TABLE([
+    <|k1: -2|>,
+    <|k1: -1|>,
+    <|k1: 0|>,
+    <|k1: 0|>,
+    <|k1: 1|>,
+    <|k1: 2|>,
+    <|k1: 3|>,
+    <|k1: 4|>,
+]) AS t1
+RIGHT ONLY JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t2.k2 > 0
+;