Fix DISTINCT over window over join

commit_hash:d7101ec6fbc95dde360e2a18ac52159dd4535764

5 files changed, 72 insertions, 0 deletions

diff --git a/yql/essentials/sql/v1/select.cpp b/yql/essentials/sql/v1/select.cpp
index 832495bc3c8..c4290f3268f 100644
--- a/yql/essentials/sql/v1/select.cpp
+++ b/yql/essentials/sql/v1/select.cpp
@@ -2956,6 +2956,10 @@ public:
         return Source->GetSessionWindowSpec();
     }
 
+    IJoin* GetJoin() override {
+        return Source->GetJoin();
+    }
+
     TNodePtr DoClone() const final {
         return {};
     }
diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
index af489ad7c1c..e8c9926ae75 100644
--- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
@@ -1285,5 +1285,19 @@
             "size": 2401,
             "uri": "https://{canondata_backend}/1817427/cd7fe4c1c700931e8c564489ae0d616c780dd82b/resource.tar.gz#test.test_window-win_func_percent_rank-default.txt-Results_/results.txt"
         }
+    ],
+    "test.test[window-yql-19709-default.txt-Debug]": [
+        {
+            "checksum": "b4f5b1907698dfd26478f9b7345f7794",
+            "size": 1095,
+            "uri": "https://{canondata_backend}/1942525/74ba357282f32d148ae3efbd223bc512d04c025e/resource.tar.gz#test.test_window-yql-19709-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[window-yql-19709-default.txt-Results]": [
+        {
+            "checksum": "0cc039edab483039733b1fe709ffe480",
+            "size": 2414,
+            "uri": "https://{canondata_backend}/1942525/74ba357282f32d148ae3efbd223bc512d04c025e/resource.tar.gz#test.test_window-yql-19709-default.txt-Results_/results.txt"
+        }
     ]
 }
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 6580d9eca77..bd605930b7f 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -7405,6 +7405,13 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_window-yql-18879_/sql.yql"
         }
     ],
+    "test_sql2yql.test[window-yql-19709]": [
+        {
+            "checksum": "8d68ed158e1fd7d258c5beea512209ea",
+            "size": 2013,
+            "uri": "https://{canondata_backend}/1847551/ee61b63f66a264ad4afe7437ef7f90941caf4456/resource.tar.gz#test_sql2yql.test_window-yql-19709_/sql.yql"
+        }
+    ],
     "test_sql_format.test[action-action_opt_args]": [
         {
             "uri": "file://test_sql_format.test_action-action_opt_args_/formatted.sql"
@@ -11240,6 +11247,11 @@
             "uri": "file://test_sql_format.test_window-yql-18879_/formatted.sql"
         }
     ],
+    "test_sql_format.test[window-yql-19709]": [
+        {
+            "uri": "file://test_sql_format.test_window-yql-19709_/formatted.sql"
+        }
+    ],
     "test_sql_negative.test[action-no_columns_in_do-default.txt]": [
         {
             "checksum": "0fad6da8e4c5a2ab2c1e5a231ea430d1",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-yql-19709_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-yql-19709_/formatted.sql
new file mode 100644
index 00000000000..ff407670255
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-yql-19709_/formatted.sql
@@ -0,0 +1,24 @@
+/* syntax version 1 */
+/* postgres can not */
+PRAGMA DistinctOverWindow;
+
+$input = AsList(
+    AsStruct(1 AS key, 1001 AS subkey, 'AAA' AS value),
+    AsStruct(150 AS key, 150 AS subkey, 'AAB' AS value),
+    AsStruct(3 AS key, 3003 AS subkey, 'AAC' AS value),
+    AsStruct(150 AS key, 150 AS subkey, 'AAD' AS value),
+    AsStruct(5 AS key, 5005 AS subkey, 'AAE' AS value),
+);
+
+SELECT
+    count(DISTINCT i1.key) OVER (
+        PARTITION BY
+            i1.subkey
+    ) AS cnt,
+FROM
+    AS_TABLE($input) AS i1
+CROSS JOIN
+    AS_TABLE($input) AS i2
+ORDER BY
+    cnt
+;
diff --git a/yql/essentials/tests/sql/suites/window/yql-19709.sql b/yql/essentials/tests/sql/suites/window/yql-19709.sql
new file mode 100644
index 00000000000..bbd71b1ac44
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/window/yql-19709.sql
@@ -0,0 +1,18 @@
+/* syntax version 1 */
+/* postgres can not */
+
+PRAGMA DistinctOverWindow;
+
+$input = AsList(
+    AsStruct(1 AS key, 1001 AS subkey, "AAA" AS value),
+    AsStruct(150 AS key, 150 AS subkey, "AAB" AS value),
+    AsStruct(3 AS key, 3003 AS subkey, "AAC" AS value),
+    AsStruct(150 AS key, 150 AS subkey, "AAD" AS value),
+    AsStruct(5 AS key, 5005 AS subkey, "AAE" AS value),
+);
+
+SELECT
+    count(DISTINCT i1.key) OVER (PARTITION BY i1.subkey) AS cnt,
+FROM AS_TABLE($input) AS i1
+CROSS JOIN AS_TABLE($input) AS i2
+ORDER BY cnt;