YQL-20211 YQL-20223: skip nulls in pushdown over optional side

Добавляю поддержку фильтрации нуллов при пушдауне на необязательную сторону джоина (до этого она работала только при пушдауне на обязательную сторону).

Как в пушдауне предиката с левой стороны одновременно на правую (PredicatePushdownOverEquiJoinBothSides), так и в пушдауне сразу на правую (FilterPushdownOverJoinOptionalSide).

Включается по флагу PredicatePushdownOverEquiJoinBothSides.

Тесты с включением можно посмотреть тут: <https://nda.ya.ru/t/e2f09Qw_7HNvu6>
commit_hash:0f101bfced2a510995577e1bca90eef6d2505992

7 files changed, 451 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/minirun/part0/canondata/result.json b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
index e66d2814bdf..b536f60c6aa 100644
--- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
@@ -782,6 +782,20 @@
             "uri": "https://{canondata_backend}/1946324/fbb1b986a8af3f4a5932aa11a3d24263ba935543/resource.tar.gz#test.test_join-eq_over_join_bad_rotate-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[join-pushdown_all_sides_optional-default.txt-Debug]": [
+        {
+            "checksum": "61f0877b7590e8f81d29cc7a84131fbe",
+            "size": 2326,
+            "uri": "https://{canondata_backend}/1936273/ead237c1a2040f1293d0557157f92ea0bcd0594a/resource.tar.gz#test.test_join-pushdown_all_sides_optional-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[join-pushdown_all_sides_optional-default.txt-Results]": [
+        {
+            "checksum": "f0124497cde540f67b0b8b7aab77a2c3",
+            "size": 8639,
+            "uri": "https://{canondata_backend}/1847551/f0533a5ac6edf5a24c59207bca8bfb4796e9d435/resource.tar.gz#test.test_join-pushdown_all_sides_optional-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-skip_nulls_tuple-default.txt-Debug]": [
         {
             "checksum": "a7eb8180b127ef9df34197c8fcd40875",
diff --git a/yql/essentials/tests/sql/minirun/part9/canondata/result.json b/yql/essentials/tests/sql/minirun/part9/canondata/result.json
index da5bc71c7c3..c7721d48e28 100644
--- a/yql/essentials/tests/sql/minirun/part9/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part9/canondata/result.json
@@ -835,6 +835,20 @@
             "uri": "https://{canondata_backend}/1942415/88908f3f1c2c172f76ce2e0c2ad414ba2d95573e/resource.tar.gz#test.test_join-eq_over_join_same_keys-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[join-left_join_right_pushdown_skipnulls-default.txt-Debug]": [
+        {
+            "checksum": "904eade316dffd651d4968718eb4509a",
+            "size": 1359,
+            "uri": "https://{canondata_backend}/1931696/f0c68bacea8a8138ec8febaefafcda5c4134fc00/resource.tar.gz#test.test_join-left_join_right_pushdown_skipnulls-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[join-left_join_right_pushdown_skipnulls-default.txt-Results]": [
+        {
+            "checksum": "d140d71fd4a07691146ca01f9768e089",
+            "size": 1212,
+            "uri": "https://{canondata_backend}/1809005/b4bb54171399cd338ba315035bc180653da06b4c/resource.tar.gz#test.test_join-left_join_right_pushdown_skipnulls-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-left_join_with_self_aggr-default.txt-Debug]": [
         {
             "checksum": "f7815acd195d1009991c6589bc3a2683",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 1fbcdf32aa4..c8ddb93f8b4 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -4283,6 +4283,13 @@
             "uri": "https://{canondata_backend}/1777230/24f082d0aa1f2ba194e39825cf286fc93a91a2fa/resource.tar.gz#test_sql2yql.test_join-left_join_right_pushdown_onlykeys_/sql.yql"
         }
     ],
+    "test_sql2yql.test[join-left_join_right_pushdown_skipnulls]": [
+        {
+            "checksum": "edcc42a6da58662d913f408c90fab0d0",
+            "size": 2029,
+            "uri": "https://{canondata_backend}/1773845/49753b70b2dfcaa939d2a38c9ab5411f48c0f5b1/resource.tar.gz#test_sql2yql.test_join-left_join_right_pushdown_skipnulls_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-left_join_with_self_aggr]": [
         {
             "checksum": "5ae7a0cc9f3c5302ca5e6631df5b6650",
@@ -4311,6 +4318,13 @@
             "uri": "https://{canondata_backend}/1599023/96e9363c53a78c2884ae76b39bd982d37627eefa/resource.tar.gz#test_sql2yql.test_join-pushdown_all_sides_/sql.yql"
         }
     ],
+    "test_sql2yql.test[join-pushdown_all_sides_optional]": [
+        {
+            "checksum": "aa7b2e11d6f0d0b34394225bd1eafb16",
+            "size": 10402,
+            "uri": "https://{canondata_backend}/1773845/3b2febd400f6a2906341f53c6ab5de0951a73668/resource.tar.gz#test_sql2yql.test_join-pushdown_all_sides_optional_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-skip_nulls_tuple]": [
         {
             "checksum": "1a8072181d163bf6c38aa229a7825171",
@@ -11074,6 +11088,11 @@
             "uri": "file://test_sql_format.test_join-left_join_right_pushdown_onlykeys_/formatted.sql"
         }
     ],
+    "test_sql_format.test[join-left_join_right_pushdown_skipnulls]": [
+        {
+            "uri": "file://test_sql_format.test_join-left_join_right_pushdown_skipnulls_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[join-left_join_with_self_aggr]": [
         {
             "uri": "file://test_sql_format.test_join-left_join_with_self_aggr_/formatted.sql"
@@ -11094,6 +11113,11 @@
             "uri": "file://test_sql_format.test_join-pushdown_all_sides_/formatted.sql"
         }
     ],
+    "test_sql_format.test[join-pushdown_all_sides_optional]": [
+        {
+            "uri": "file://test_sql_format.test_join-pushdown_all_sides_optional_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[join-skip_nulls_tuple]": [
         {
             "uri": "file://test_sql_format.test_join-skip_nulls_tuple_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-left_join_right_pushdown_skipnulls_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-left_join_right_pushdown_skipnulls_/formatted.sql
new file mode 100644
index 00000000000..f3c5738978c
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-left_join_right_pushdown_skipnulls_/formatted.sql
@@ -0,0 +1,30 @@
+PRAGMA FilterPushdownOverJoinOptionalSide;
+PRAGMA config.flags('OptimizerFlags', 'FilterPushdownOverJoinOptionalSideIgnoreOnlyKeys', 'PredicatePushdownOverEquiJoinBothSides');
+
+$t1_data = AsList(
+    AsStruct(0 AS k1),
+    AsStruct(1 AS k1),
+    AsStruct(2 AS k1),
+    AsStruct(3 AS k1),
+);
+
+$t2_data = AsList(
+    AsStruct(Just(2) AS k2),
+    AsStruct(Just(3) AS k2),
+    AsStruct(Just(4) AS k2),
+    AsStruct(Just(5) AS k2),
+    AsStruct(NULL AS k2),
+    AsStruct(NULL AS k2),
+);
+
+SELECT
+    *
+FROM
+    AS_TABLE($t1_data) AS t1
+LEFT JOIN
+    AS_TABLE($t2_data) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t2.k2 IS NULL
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-pushdown_all_sides_optional_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-pushdown_all_sides_optional_/formatted.sql
new file mode 100644
index 00000000000..78d4d87c07e
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_join-pushdown_all_sides_optional_/formatted.sql
@@ -0,0 +1,187 @@
+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|>,
+        <|k1: NULL|>,
+    ]) AS t1
+LEFT JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+        <|k2: NULL|>,
+    ]) 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|>,
+        <|k1: NULL|>,
+    ]) AS t1
+RIGHT JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+        <|k2: NULL|>,
+    ]) 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|>,
+        <|k1: NULL|>,
+    ]) AS t1
+LEFT SEMI JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+        <|k2: NULL|>,
+    ]) 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|>,
+        <|k1: NULL|>,
+    ]) AS t1
+RIGHT SEMI JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+        <|k2: NULL|>,
+    ]) 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|>,
+        <|k1: NULL|>,
+    ]) AS t1
+LEFT ONLY JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+        <|k2: NULL|>,
+    ]) 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|>,
+        <|k1: NULL|>,
+    ]) AS t1
+RIGHT ONLY JOIN
+    AS_TABLE([
+        <|k2: -2|>,
+        <|k2: 0|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 2|>,
+        <|k2: 4|>,
+        <|k2: 6|>,
+        <|k2: NULL|>,
+    ]) AS t2
+ON
+    t1.k1 == t2.k2
+WHERE
+    t2.k2 > 0
+;
diff --git a/yql/essentials/tests/sql/suites/join/left_join_right_pushdown_skipnulls.sql b/yql/essentials/tests/sql/suites/join/left_join_right_pushdown_skipnulls.sql
new file mode 100644
index 00000000000..781722a5a29
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/join/left_join_right_pushdown_skipnulls.sql
@@ -0,0 +1,25 @@
+PRAGMA FilterPushdownOverJoinOptionalSide;
+PRAGMA config.flags("OptimizerFlags", "FilterPushdownOverJoinOptionalSideIgnoreOnlyKeys", "PredicatePushdownOverEquiJoinBothSides");
+
+$t1_data = AsList(
+    AsStruct(0 AS k1),
+    AsStruct(1 AS k1),
+    AsStruct(2 AS k1),
+    AsStruct(3 AS k1),
+);
+
+$t2_data = AsList(
+    AsStruct(Just(2) AS k2),
+    AsStruct(Just(3) AS k2),
+    AsStruct(Just(4) AS k2),
+    AsStruct(Just(5) AS k2),
+    AsStruct(NULL AS k2),
+    AsStruct(NULL AS k2),
+);
+
+SELECT *
+FROM AS_TABLE($t1_data) AS t1
+LEFT JOIN AS_TABLE($t2_data) AS t2
+ON t1.k1 == t2.k2
+WHERE t2.k2 IS NULL
+;
diff --git a/yql/essentials/tests/sql/suites/join/pushdown_all_sides_optional.sql b/yql/essentials/tests/sql/suites/join/pushdown_all_sides_optional.sql
new file mode 100644
index 00000000000..90e6819e260
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/join/pushdown_all_sides_optional.sql
@@ -0,0 +1,157 @@
+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|>,
+    <|k1: NULL|>,
+]) AS t1
+LEFT JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+    <|k2: NULL|>,
+]) 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|>,
+    <|k1: NULL|>,
+]) AS t1
+RIGHT JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+    <|k2: NULL|>,
+]) 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|>,
+    <|k1: NULL|>,
+]) AS t1
+LEFT SEMI JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+    <|k2: NULL|>,
+]) 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|>,
+    <|k1: NULL|>,
+]) AS t1
+RIGHT SEMI JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+    <|k2: NULL|>,
+]) 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|>,
+    <|k1: NULL|>,
+]) AS t1
+LEFT ONLY JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+    <|k2: NULL|>,
+]) 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|>,
+    <|k1: NULL|>,
+]) AS t1
+RIGHT ONLY JOIN AS_TABLE([
+    <|k2: -2|>,
+    <|k2: 0|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 2|>,
+    <|k2: 4|>,
+    <|k2: 6|>,
+    <|k2: NULL|>,
+]) AS t2
+ON t1.k1 == t2.k2
+WHERE t2.k2 > 0
+;