YQL-21046: Support RANGE window on YqlSelect

commit_hash:2cb369cb2091489e28790782fe9e7679d367fbb3

4 files changed, 488 insertions, 151 deletions

diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index a12963cb3d8..4b389d2af1a 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -10100,11 +10100,18 @@
             "uri": "https://{canondata_backend}/1942525/e20b628f919c38aabdbb37b6f67f0541495a8fc5/resource.tar.gz#test_sql2yql.test_select_yql-window_fn_rownumber_0_desc_/sql.yql"
         }
     ],
-    "test_sql2yql.test[select_yql-window_frame]": [
+    "test_sql2yql.test[select_yql-window_order_by_range_frame]": [
         {
-            "checksum": "da3b99ec74b8c7db99cb5f71d1be5ae2",
-            "size": 8150,
-            "uri": "https://{canondata_backend}/1881367/3620f00662dba64d30bf3c93837b76b97c3a5f1f/resource.tar.gz#test_sql2yql.test_select_yql-window_frame_/sql.yql"
+            "checksum": "8bb7b70177da928537c33577641da90e",
+            "size": 13099,
+            "uri": "https://{canondata_backend}/1773845/e7a7cc669a6454b75022f11843967126ae2e0bca/resource.tar.gz#test_sql2yql.test_select_yql-window_order_by_range_frame_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[select_yql-window_order_by_rows_frame]": [
+        {
+            "checksum": "47838b00d58d248462e6c2555d022560",
+            "size": 13589,
+            "uri": "https://{canondata_backend}/1773845/e7a7cc669a6454b75022f11843967126ae2e0bca/resource.tar.gz#test_sql2yql.test_select_yql-window_order_by_rows_frame_/sql.yql"
         }
     ],
     "test_sql2yql.test[select_yql-window_pg_tutorial_1]": [
@@ -17719,9 +17726,14 @@
             "uri": "file://test_sql_format.test_select_yql-window_fn_rownumber_0_desc_/formatted.sql"
         }
     ],
-    "test_sql_format.test[select_yql-window_frame]": [
+    "test_sql_format.test[select_yql-window_order_by_range_frame]": [
+        {
+            "uri": "file://test_sql_format.test_select_yql-window_order_by_range_frame_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[select_yql-window_order_by_rows_frame]": [
         {
-            "uri": "file://test_sql_format.test_select_yql-window_frame_/formatted.sql"
+            "uri": "file://test_sql_format.test_select_yql-window_order_by_rows_frame_/formatted.sql"
         }
     ],
     "test_sql_format.test[select_yql-window_pg_tutorial_1]": [
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_frame_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_frame_/formatted.sql
deleted file mode 100644
index 619056cc89e..00000000000
--- a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_frame_/formatted.sql
+++ /dev/null
@@ -1,145 +0,0 @@
-PRAGMA YqlSelect = 'force';
-
--- (1,1), (2,3), (3,6)
-SELECT
-    a,
-    Sum(a) OVER (
-        ORDER BY
-            a
-        ROWS UNBOUNDED PRECEDING
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- Unsupported frame type: range
--- (1,1), (2,5), (2,5), (3,8)
--- SELECT a, SUM(a) OVER (ORDER BY a RANGE UNBOUNDED PRECEDING) AS result
--- FROM (VALUES (1),(2),(2),(3)) AS x(a);
--- (1,1), (2,3), (3,5)
-SELECT
-    a,
-    Sum(a) OVER (
-        ORDER BY
-            a
-        ROWS 1 PRECEDING
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- (1,6), (2,6), (3,6)
-SELECT
-    a,
-    Sum(a) OVER (
-        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- (1,1), (2,3), (3,6)
-SELECT
-    a,
-    Sum(a) OVER (
-        ORDER BY
-            a
-        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- (1,3), (2,6), (3,5)
-SELECT
-    a,
-    Sum(a) OVER (
-        ORDER BY
-            a
-        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- FIXME(YQL-21046): List<Struct<'a':Int32,'result':Int64>>, but it is: List<Struct<'a':Int32,'result':Int64?>>
--- (1,NULL), (2,1), (3,3)
--- SELECT a, Sum(a) OVER (ORDER BY a ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS result
--- FROM (VALUES (1),(2),(3)) AS x(a);
--- (1,6), (2,5), (3,3)
-SELECT
-    a,
-    Sum(a) OVER (
-        ORDER BY
-            a
-        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- (1,1), (2,2), (3,3)
-SELECT
-    a,
-    Sum(a) OVER (
-        ROWS BETWEEN CURRENT ROW AND CURRENT ROW
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
-
--- Unsupported frame type: range
--- (1,3), (2,3), (4,4)
--- SELECT a, SUM(a) OVER (ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS result
--- FROM (VALUES (1),(2),(4)) AS x(a);
--- (1,1), (2,3), (3,6)
-SELECT
-    a,
-    Sum(a) OVER (
-        ORDER BY
-            a
-        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
-    ) AS result
-FROM (
-    VALUES
-        (1),
-        (2),
-        (3)
-) AS x (
-    a
-);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_order_by_range_frame_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_order_by_range_frame_/formatted.sql
new file mode 100644
index 00000000000..945998c0c7c
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_order_by_range_frame_/formatted.sql
@@ -0,0 +1,235 @@
+PRAGMA YqlSelect = 'force';
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE UNBOUNDED PRECEDING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE 1 PRECEDING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN CURRENT ROW AND CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_order_by_rows_frame_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_order_by_rows_frame_/formatted.sql
new file mode 100644
index 00000000000..1174a27d24a
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_yql-window_order_by_rows_frame_/formatted.sql
@@ -0,0 +1,235 @@
+PRAGMA YqlSelect = 'force';
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS UNBOUNDED PRECEDING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS 1 PRECEDING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN CURRENT ROW AND CURRENT ROW
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);
+
+SELECT
+    a,
+    Sum(a) OVER (
+        ORDER BY
+            a
+        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    ) AS r
+FROM (
+    VALUES
+        (1),
+        (2),
+        (3),
+        (4),
+        (5)
+) AS x (
+    a
+);