YQL-21046: Support RANGE window on YqlSelect

commit_hash:2cb369cb2091489e28790782fe9e7679d367fbb3

3 files changed, 80 insertions, 48 deletions

diff --git a/yql/essentials/tests/sql/suites/select_yql/window_frame.yql b/yql/essentials/tests/sql/suites/select_yql/window_frame.yql
deleted file mode 100644
index 0fe7dd5f89e..00000000000
--- a/yql/essentials/tests/sql/suites/select_yql/window_frame.yql
+++ /dev/null
@@ -1,48 +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/suites/select_yql/window_order_by_range_frame.yql b/yql/essentials/tests/sql/suites/select_yql/window_order_by_range_frame.yql
new file mode 100644
index 00000000000..466e0567f87
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/select_yql/window_order_by_range_frame.yql
@@ -0,0 +1,40 @@
+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/suites/select_yql/window_order_by_rows_frame.yql b/yql/essentials/tests/sql/suites/select_yql/window_order_by_rows_frame.yql
new file mode 100644
index 00000000000..d452a7ef2e5
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/select_yql/window_order_by_rows_frame.yql
@@ -0,0 +1,40 @@
+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);