YQL-21046: Support RANGE window on YqlSelect

commit_hash:2cb369cb2091489e28790782fe9e7679d367fbb3

24 files changed, 890 insertions, 310 deletions

diff --git a/yql/essentials/core/common_opt/yql_co_sqlselect.cpp b/yql/essentials/core/common_opt/yql_co_sqlselect.cpp
index 91a4f6bb984..64c1e9cf45e 100644
--- a/yql/essentials/core/common_opt/yql_co_sqlselect.cpp
+++ b/yql/essentials/core/common_opt/yql_co_sqlselect.cpp
@@ -2692,7 +2692,9 @@ TExprNode::TPtr BuildHaving(
         .Build();
 }
 
-std::tuple<TExprNode::TPtr, TExprNode::TPtr> BuildFrame(TPositionHandle pos, const TExprNode& frameSettings, TExprContext& ctx) {
+std::tuple<TExprNode::TPtr, TExprNode::TPtr> BuildRowsFrame(TPositionHandle pos, const TExprNode& frameSettings, TExprContext& ctx) {
+    YQL_ENSURE(GetSetting(frameSettings, "type")->Tail().Content() == "rows");
+
     TExprNode::TPtr begin;
     TExprNode::TPtr end;
     const auto& from = GetSetting(frameSettings, "from");
@@ -2732,8 +2734,165 @@ std::tuple<TExprNode::TPtr, TExprNode::TPtr> BuildFrame(TPositionHandle pos, con
     return { begin, end };
 }
 
-TExprNode::TPtr BuildSortTraits(TPositionHandle pos, const TExprNode& sortColumns, const TExprNode::TPtr& list,
-    const TAggregationMap* aggId, TExprContext& ctx, TOptimizeContext& optCtx) {
+/// @param value nullable
+TExprNode::TPtr BuildRangeFrameBound(
+    TPositionHandle pos,
+    TStringBuf name,
+    TExprNode::TPtr value,
+    TExprContext& ctx)
+{
+    if (name == "up") {
+        YQL_ENSURE(!value);
+        // clang-format off
+        return ctx.Builder(pos)
+            .List()
+                .Atom(0, "preceding")
+                .Atom(1, "unbounded")
+            .Seal()
+            .Build();
+        // clang-format on
+    }
+
+    if (name == "p") {
+        YQL_ENSURE(value);
+        // clang-format off
+        return ctx.Builder(pos)
+            .List()
+                .Atom(0, "preceding")
+                .Add(1, std::move(value))
+            .Seal()
+            .Build();
+        // clang-format on
+    }
+
+    if (name == "c") {
+        YQL_ENSURE(!value);
+        // clang-format off
+        return ctx.Builder(pos)
+            .List()
+                .Atom(0, "currentRow")
+            .Seal()
+            .Build();
+        // clang-format on
+    }
+
+    if (name == "f") {
+        YQL_ENSURE(value);
+        // clang-format off
+        return ctx.Builder(pos)
+            .List()
+                .Atom(0, "following")
+                .Add(1, std::move(value))
+            .Seal()
+            .Build();
+        // clang-format on
+    }
+
+    if (name == "uf") {
+        YQL_ENSURE(!value);
+        // clang-format off
+        return ctx.Builder(pos)
+            .List()
+                .Atom(0, "following")
+                .Atom(1, "unbounded")
+            .Seal()
+            .Build();
+        // clang-format on
+    }
+
+    YQL_ENSURE(false, "Unknown range frame bound name: " << name);
+}
+
+std::tuple<TStringBuf, TExprNode::TPtr> UnpackRangeFrameBound(TString direction, const TExprNode& frameSettings) {
+    TStringBuf name = GetSetting(frameSettings, direction)->Tail().Content();
+
+    direction += "_value";
+    TExprNode::TPtr value = GetSetting(frameSettings, direction);
+
+    value = value ? value->TailPtr() : nullptr;
+
+    return { name, std::move(value) };
+}
+
+std::tuple<TExprNode::TPtr, TExprNode::TPtr> BuildRangeFrame(TPositionHandle pos, const TExprNode& frameSettings, TExprContext& ctx) {
+    YQL_ENSURE(GetSetting(frameSettings, "type")->Tail().Content() == "range");
+
+    auto [from, fromValue] = UnpackRangeFrameBound("from", frameSettings);
+    auto [to, toValue] = UnpackRangeFrameBound("to", frameSettings);
+
+    return {
+        BuildRangeFrameBound(pos, from, std::move(fromValue), ctx),
+        BuildRangeFrameBound(pos, to, std::move(toValue), ctx),
+    };
+}
+
+TExprNode::TPtr BuildSingletonSortTraits(
+    TPositionHandle pos,
+    TExprNode::TPtr column,
+    const TExprNode::TPtr& list,
+    const TAggregationMap* aggId,
+    TExprContext& ctx,
+    TOptimizeContext& optCtx)
+{
+    auto lambda = column->ChildPtr(1);
+    if (aggId) {
+        RewriteAggs(lambda, *aggId, ctx, optCtx, /*testExpr=*/false);
+    }
+
+    // clang-format off
+    return ctx.Builder(pos)
+        .Callable("SortTraits")
+            .Callable(0, "TypeOf")
+                .Add(0, list)
+            .Seal()
+            .Callable(1, "Bool")
+                .Atom(0, column->Child(2)->Content() == "asc" ? "true" : "false")
+            .Seal()
+            .Lambda(2)
+                .Param("row")
+                .Do([&](TExprNodeBuilder& parent) -> TExprNodeBuilder& {
+                    if (column->ChildPtr(3)->Content() == "last") {
+                        return parent
+                            .List()
+                                .Callable(0, "Not")
+                                    .Callable(0, "Exists")
+                                        .Apply(0, lambda)
+                                            .With(0, "row")
+                                        .Seal()
+                                    .Seal()
+                                .Seal()
+                                .Apply(1, lambda)
+                                    .With(0, "row")
+                                .Seal()
+                            .Seal();
+                    }
+
+                    return parent
+                        .Apply(lambda)
+                            .With(0, "row")
+                        .Seal();
+                })
+            .Seal()
+        .Seal()
+        .Build();
+    // clang-format on
+}
+
+TExprNode::TPtr BuildSortTraits(
+    TPositionHandle pos,
+    const TExprNode& sortColumns,
+    const TExprNode::TPtr& list,
+    const TAggregationMap* aggId,
+    bool isYql,
+    TExprContext& ctx,
+    TOptimizeContext& optCtx)
+{
+    // RANGE over a tuple was not supported
+    if (sortColumns.ChildrenSize() == 1 && isYql) {
+        return BuildSingletonSortTraits(pos, sortColumns.ChildPtr(0), list, aggId, ctx, optCtx);
+    }
+
+    // clang-format off
     return ctx.Builder(pos)
         .Callable("SortTraits")
             .Callable(0, "TypeOf")
@@ -2785,6 +2944,7 @@ TExprNode::TPtr BuildSortTraits(TPositionHandle pos, const TExprNode& sortColumn
             .Seal()
         .Seal()
         .Build();
+    // clang-format on
 }
 
 TExprNode::TPtr ExpandWindowCall(
@@ -2864,6 +3024,7 @@ TExprNode::TPtr BuildWindows(
     TExprNode::TPtr& projectionRoot,
     const TExprNode::TPtr& projectionArg,
     const TAggregationMap& aggId,
+    bool isYql,
     TExprContext& ctx,
     TOptimizeContext& optCtx)
 {
@@ -2928,7 +3089,7 @@ TExprNode::TPtr BuildWindows(
         auto sortNode = ctx.NewCallable(pos, "Void", {});
         TExprNode::TPtr keyLambda;
         if (winDef->Child(3)->ChildrenSize() > 0) {
-            sortNode = BuildSortTraits(pos, *winDef->Child(3), ret, &aggId, ctx, optCtx);
+            sortNode = BuildSortTraits(pos, *winDef->Child(3), ret, &aggId, isYql, ctx, optCtx);
             keyLambda = sortNode->TailPtr();
         } else {
             keyLambda = ctx.Builder(pos)
@@ -2941,26 +3102,25 @@ TExprNode::TPtr BuildWindows(
         }
 
         TExprNode::TListType args;
+
         TExprNode::TPtr begin;
         TExprNode::TPtr end;
         bool useRange = false;
-        if (HasSetting(frameSettings, "type")) {
-            std::tie(begin, end) = BuildFrame(pos, frameSettings, ctx);
+        if (auto setting = GetSetting(frameSettings, "type");
+            setting && setting->Tail().Content() == "rows")
+        {
+            std::tie(begin, end) = BuildRowsFrame(pos, frameSettings, ctx);
+        } else if (setting && setting->Tail().Content() == "range") {
+            useRange = true;
+            std::tie(begin, end) = BuildRangeFrame(pos, frameSettings, ctx);
+        } else if (setting) {
+            YQL_ENSURE(false, "Unknown frame type: " << setting->Tail().Content());
         } else {
             // default frame
             if (winDef->Child(3)->ChildrenSize() > 0) {
                 useRange = true;
-                begin = ctx.Builder(pos)
-                    .List()
-                        .Atom(0, "preceding")
-                        .Atom(1, "unbounded")
-                    .Seal()
-                    .Build();
-                end = ctx.Builder(pos)
-                    .List()
-                        .Atom(0, "currentRow")
-                    .Seal()
-                    .Build();
+                begin = BuildRangeFrameBound(pos, "up", /*value=*/nullptr, ctx);
+                end = BuildRangeFrameBound(pos, "c", /*value=*/nullptr, ctx);
             } else {
                 begin = ctx.NewCallable(pos, "Void", {});
                 end = begin;
@@ -3129,7 +3289,10 @@ TExprNode::TPtr BuildSort(TPositionHandle pos, const TExprNode::TPtr& list, cons
 TExprNode::TPtr BuildDistinctOn(TPositionHandle pos,
                                 TExprNode::TPtr list,
                                 const TExprNode::TPtr& distinctOn,
-                                const TExprNode::TPtr& sort, TExprContext& ctx, TOptimizeContext& optCtx) {
+                                const TExprNode::TPtr& sort,
+                                bool isYql,
+                                TExprContext& ctx,
+                                TOptimizeContext& optCtx) {
     // filter by RowNumber() == 1
 
     const auto value = ctx.Builder(pos)
@@ -3206,7 +3369,7 @@ TExprNode::TPtr BuildDistinctOn(TPositionHandle pos,
 
     auto sortNode = ctx.NewCallable(pos, "Void", {});
     if (sort && sort->Tail().ChildrenSize() > 0) {
-        sortNode = BuildSortTraits(pos, sort->Tail(), list, nullptr, ctx, optCtx);
+        sortNode = BuildSortTraits(pos, sort->Tail(), list, nullptr, isYql, ctx, optCtx);
     }
 
     const auto begin = ctx.NewCallable(pos, "Void", {});
@@ -4100,7 +4263,7 @@ TExprNode::TPtr ExpandSqlSelectImpl(
             }
 
             if (!winCtx.Funcs.empty()) {
-                list = BuildWindows(node->Pos(), list, window, winCtx, projectionRoot, projectionArg, aggId, ctx, optCtx);
+                list = BuildWindows(node->Pos(), list, window, winCtx, projectionRoot, projectionArg, aggId, isYql, ctx, optCtx);
             }
 
             RewriteAggsPartial(projectionRoot, projectionArg, aggId, ctx, optCtx, false);
@@ -4148,7 +4311,7 @@ TExprNode::TPtr ExpandSqlSelectImpl(
                 list = ctx.NewCallable(node->Pos(), "SqlAggregateAll", { list });
             } else if (distinctOn) {
                 YQL_ENSURE(!extraSortColumns);
-                list = BuildDistinctOn(node->Pos(), list, distinctOn->TailPtr(), sort, ctx, optCtx);
+                list = BuildDistinctOn(node->Pos(), list, distinctOn->TailPtr(), sort, isYql, ctx, optCtx);
             }
 
             TVector<TString> sublinkColumns;
diff --git a/yql/essentials/core/type_ann/type_ann_sql.cpp b/yql/essentials/core/type_ann/type_ann_sql.cpp
index 60b52e434ce..7c9012c3232 100644
--- a/yql/essentials/core/type_ann/type_ann_sql.cpp
+++ b/yql/essentials/core/type_ann/type_ann_sql.cpp
@@ -5075,7 +5075,7 @@ IGraphTransformer::TStatus SqlWindowWrapper(const TExprNode::TPtr& input, TExprN
             }
 
             auto type = x->Tail().Content();
-            if (type != "rows") {
+            if (!IsIn({"rows", "range"}, type)) {
                 ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(x->Pos()), TStringBuilder() << "Unsupported frame type: " << type));
                 return IGraphTransformer::TStatus::Error;
             }
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index b2278645fb8..4d25b499241 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -14834,6 +14834,40 @@ Y_UNIT_TEST(WindowBad) {
     UNIT_ASSERT_STRING_CONTAINS(Err2Str(res), ":2:27: Error: Expected a YqlSelect-compatible window function, but got Length");
 }
 
+Y_UNIT_TEST(AnsiCurrentRow) {
+    const auto check = [](TString spec, TString a, TString b, TString c, THashSet<TString> flags) {
+        TString query = R"sql(
+            PRAGMA YqlSelect = 'force';
+            $events = (SELECT * FROM (VALUES
+                (1, 10,  5),
+                (2, 10,  5),
+                (3, 20, 10)
+            ) AS events (event_id, ts, val));
+            SELECT ts, val, SUM(val) OVER (SPEC) AS run_sum FROM $events ORDER BY ts, event_id;
+        )sql";
+        SubstGlobal(query, "SPEC", spec);
+
+        NSQLTranslation::TTranslationSettings settings;
+        settings.Flags = std::move(flags);
+        settings.LangVer = NSQLTranslationV1::YqlSelectLangVersion();
+
+        NYql::TAstParseResult res = SqlToYqlWithSettings(query, settings);
+        UNIT_ASSERT_C(res.IsOk(), Err2Str(res));
+
+        TWordCountHive stat = {"YqlSelect"};
+        TString program = VerifyProgram(res, stat);
+        UNIT_ASSERT_VALUES_EQUAL(stat["YqlSelect"], 4);
+        UNIT_ASSERT_STRING_CONTAINS(program, "'('type '" + a + ")");
+        UNIT_ASSERT_STRING_CONTAINS(program, "'('from '" + b + ")");
+        UNIT_ASSERT_STRING_CONTAINS(program, "'('to '" + c + ")");
+    };
+
+    check("ORDER BY ts", "rows", "up", "f", {}); // to_value is 0
+    check("", /*      */ "rows", "up", "uf", {});
+    check("ORDER BY ts", "range", "up", "c", {"AnsiCurrentRow"});
+    check("", /*      */ "rows", "up", "uf", {"AnsiCurrentRow"});
+}
+
 } // Y_UNIT_TEST_SUITE(YqlSelect)
 
 Y_UNIT_TEST_SUITE(ColumnDefault) {
diff --git a/yql/essentials/tests/sql/minirun/part0/canondata/result.json b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
index 82dd292a660..23ac8fe4b3e 100644
--- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json
@@ -1287,6 +1287,20 @@
             "uri": "https://{canondata_backend}/1937424/d02bb271c9ab67557c669f52bc80a0a698a8a91e/resource.tar.gz#test.test_select_yql-uncorrelated_asterisk_subquery_in-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[select_yql-window_order_by_rows_frame-default.txt-Debug]": [
+        {
+            "checksum": "9b871b2cba6a4537f9b024c6aa8ed91c",
+            "size": 2922,
+            "uri": "https://{canondata_backend}/1942415/7895e7e05bcaa9dd2126ae552ba88373276b4842/resource.tar.gz#test.test_select_yql-window_order_by_rows_frame-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[select_yql-window_order_by_rows_frame-default.txt-Results]": [
+        {
+            "checksum": "69f4d36a78a6bfbf852dcd731015b12b",
+            "size": 17988,
+            "uri": "https://{canondata_backend}/1871102/7e63b87a0695423742cc222c0fe2d12858a59347/resource.tar.gz#test.test_select_yql-window_order_by_rows_frame-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[select_yql-window_sum-default.txt-Debug]": [
         {
             "checksum": "ea520dd5c6062bbc942075b4a956da36",
@@ -1303,9 +1317,9 @@
     ],
     "test.test[select_yql-window_sum_order_by_distinct_0-default.txt-Debug]": [
         {
-            "checksum": "e2cffbdfe0e680122b065d7718fd0eb7",
-            "size": 1386,
-            "uri": "https://{canondata_backend}/1937424/d02bb271c9ab67557c669f52bc80a0a698a8a91e/resource.tar.gz#test.test_select_yql-window_sum_order_by_distinct_0-default.txt-Debug_/opt.yql"
+            "checksum": "4c014d466e9aa75588db702ff2902d32",
+            "size": 1380,
+            "uri": "https://{canondata_backend}/1942415/7895e7e05bcaa9dd2126ae552ba88373276b4842/resource.tar.gz#test.test_select_yql-window_sum_order_by_distinct_0-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_sum_order_by_distinct_0-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 6826316a901..d24c66d94d6 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -1177,9 +1177,9 @@
     ],
     "test.test[select_yql-window_fn_lead_2-default.txt-Debug]": [
         {
-            "checksum": "d6a6ef449b7bdefa34cd89a22b19ab6f",
-            "size": 1234,
-            "uri": "https://{canondata_backend}/1600758/c117864042cf9137d901ec8d7c77a73c67c6c60d/resource.tar.gz#test.test_select_yql-window_fn_lead_2-default.txt-Debug_/opt.yql"
+            "checksum": "bb2f4f70e9eaa609f104ef7cd82cf9f6",
+            "size": 1203,
+            "uri": "https://{canondata_backend}/1931696/b5e06ed311ea287075c73079de796ccfb52d0a6a/resource.tar.gz#test.test_select_yql-window_fn_lead_2-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_lead_2-default.txt-Results]": [
@@ -1189,20 +1189,6 @@
             "uri": "https://{canondata_backend}/1600758/c117864042cf9137d901ec8d7c77a73c67c6c60d/resource.tar.gz#test.test_select_yql-window_fn_lead_2-default.txt-Results_/results.txt"
         }
     ],
-    "test.test[select_yql-window_frame-default.txt-Debug]": [
-        {
-            "checksum": "357893bf03bc539d97fa9e45c51b867c",
-            "size": 2307,
-            "uri": "https://{canondata_backend}/1600758/c117864042cf9137d901ec8d7c77a73c67c6c60d/resource.tar.gz#test.test_select_yql-window_frame-default.txt-Debug_/opt.yql"
-        }
-    ],
-    "test.test[select_yql-window_frame-default.txt-Results]": [
-        {
-            "checksum": "7e54269ce3392b8516c37670aff57de2",
-            "size": 9466,
-            "uri": "https://{canondata_backend}/1600758/c117864042cf9137d901ec8d7c77a73c67c6c60d/resource.tar.gz#test.test_select_yql-window_frame-default.txt-Results_/results.txt"
-        }
-    ],
     "test.test[select_yql-window_sum_agg-default.txt-Debug]": [
         {
             "checksum": "98a803996eaf1da4898eadcd36e68f1f",
diff --git a/yql/essentials/tests/sql/minirun/part11/canondata/result.json b/yql/essentials/tests/sql/minirun/part11/canondata/result.json
index 0a85f811846..00f1cd0a837 100644
--- a/yql/essentials/tests/sql/minirun/part11/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part11/canondata/result.json
@@ -1183,9 +1183,9 @@
     ],
     "test.test[select_yql-window_pg_tutorial_6-default.txt-Debug]": [
         {
-            "checksum": "33cf4d72316a666cd4829133d6c8969d",
-            "size": 1917,
-            "uri": "https://{canondata_backend}/1847551/c9103bf55cc11afa84471242d8aeba02b20d6dea/resource.tar.gz#test.test_select_yql-window_pg_tutorial_6-default.txt-Debug_/opt.yql"
+            "checksum": "36964cc9db635115f7b47be123206795",
+            "size": 1911,
+            "uri": "https://{canondata_backend}/1946324/899b26cf97dd7eb99bb4ee510428e8792e23880e/resource.tar.gz#test.test_select_yql-window_pg_tutorial_6-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_pg_tutorial_6-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part12/canondata/result.json b/yql/essentials/tests/sql/minirun/part12/canondata/result.json
index 4656df0ce34..9d7c080d9aa 100644
--- a/yql/essentials/tests/sql/minirun/part12/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part12/canondata/result.json
@@ -1166,9 +1166,9 @@
     ],
     "test.test[select_yql-window_fn_rank_1-default.txt-Debug]": [
         {
-            "checksum": "2bcdba8fc911f299fdf97a2065cdfdf7",
-            "size": 1100,
-            "uri": "https://{canondata_backend}/1899731/db557cf50e84fba5e07ed252ed220f77f5fa9cc9/resource.tar.gz#test.test_select_yql-window_fn_rank_1-default.txt-Debug_/opt.yql"
+            "checksum": "7a570a34ef475b4c1d16a590fd01d170",
+            "size": 1094,
+            "uri": "https://{canondata_backend}/1931696/c2bbea47e91578d9953eb94f86c0678a066e01fe/resource.tar.gz#test.test_select_yql-window_fn_rank_1-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rank_1-default.txt-Results]": [
@@ -1180,9 +1180,9 @@
     ],
     "test.test[select_yql-window_fn_rank_1_desc-default.txt-Debug]": [
         {
-            "checksum": "71406981b0060633ea002553b54b2724",
-            "size": 1101,
-            "uri": "https://{canondata_backend}/1899731/db557cf50e84fba5e07ed252ed220f77f5fa9cc9/resource.tar.gz#test.test_select_yql-window_fn_rank_1_desc-default.txt-Debug_/opt.yql"
+            "checksum": "2d84655993e9bbb28011a47eaa32d7e1",
+            "size": 1095,
+            "uri": "https://{canondata_backend}/1931696/c2bbea47e91578d9953eb94f86c0678a066e01fe/resource.tar.gz#test.test_select_yql-window_fn_rank_1_desc-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rank_1_desc-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part13/canondata/result.json b/yql/essentials/tests/sql/minirun/part13/canondata/result.json
index 39f2a6ee439..25507904273 100644
--- a/yql/essentials/tests/sql/minirun/part13/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part13/canondata/result.json
@@ -1445,9 +1445,9 @@
     ],
     "test.test[select_yql-window_fn_rank_0-default.txt-Debug]": [
         {
-            "checksum": "2bcdba8fc911f299fdf97a2065cdfdf7",
-            "size": 1100,
-            "uri": "https://{canondata_backend}/1871002/12af85b5b27eccd0e3391513f34ad8082ac84843/resource.tar.gz#test.test_select_yql-window_fn_rank_0-default.txt-Debug_/opt.yql"
+            "checksum": "7a570a34ef475b4c1d16a590fd01d170",
+            "size": 1094,
+            "uri": "https://{canondata_backend}/1931696/c5246b9f3f52624077951948336441ce6a73369d/resource.tar.gz#test.test_select_yql-window_fn_rank_0-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rank_0-default.txt-Results]": [
@@ -1459,9 +1459,9 @@
     ],
     "test.test[select_yql-window_pg_tutorial_2-default.txt-Debug]": [
         {
-            "checksum": "8040709fd7594143473457b644ea3a9f",
-            "size": 1761,
-            "uri": "https://{canondata_backend}/1871002/12af85b5b27eccd0e3391513f34ad8082ac84843/resource.tar.gz#test.test_select_yql-window_pg_tutorial_2-default.txt-Debug_/opt.yql"
+            "checksum": "794724cdf0d3b2117beeaf050dd16ced",
+            "size": 1755,
+            "uri": "https://{canondata_backend}/1931696/c5246b9f3f52624077951948336441ce6a73369d/resource.tar.gz#test.test_select_yql-window_pg_tutorial_2-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_pg_tutorial_2-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part14/canondata/result.json b/yql/essentials/tests/sql/minirun/part14/canondata/result.json
index aadcab8aed5..d6e25e9d850 100644
--- a/yql/essentials/tests/sql/minirun/part14/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part14/canondata/result.json
@@ -1232,9 +1232,9 @@
     ],
     "test.test[select_yql-window_fn_rank_2-default.txt-Debug]": [
         {
-            "checksum": "2bcdba8fc911f299fdf97a2065cdfdf7",
-            "size": 1100,
-            "uri": "https://{canondata_backend}/1942671/177e34c44b7724978f8e24b71ca17680c51e77ee/resource.tar.gz#test.test_select_yql-window_fn_rank_2-default.txt-Debug_/opt.yql"
+            "checksum": "7a570a34ef475b4c1d16a590fd01d170",
+            "size": 1094,
+            "uri": "https://{canondata_backend}/1942415/afadcc47994623b4b7ccf521aa28e220ea0a2f28/resource.tar.gz#test.test_select_yql-window_fn_rank_2-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rank_2-default.txt-Results]": [
@@ -1246,9 +1246,9 @@
     ],
     "test.test[select_yql-window_fn_rownumber_0_desc-default.txt-Debug]": [
         {
-            "checksum": "e1fa76b445be36201dcd6073db04e051",
-            "size": 1072,
-            "uri": "https://{canondata_backend}/1942671/177e34c44b7724978f8e24b71ca17680c51e77ee/resource.tar.gz#test.test_select_yql-window_fn_rownumber_0_desc-default.txt-Debug_/opt.yql"
+            "checksum": "f0b37253d9a37b1caa89dfdb0b1fae87",
+            "size": 1066,
+            "uri": "https://{canondata_backend}/1942415/afadcc47994623b4b7ccf521aa28e220ea0a2f28/resource.tar.gz#test.test_select_yql-window_fn_rownumber_0_desc-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rownumber_0_desc-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part15/canondata/result.json b/yql/essentials/tests/sql/minirun/part15/canondata/result.json
index f5324cc68f0..de72c7f04ef 100644
--- a/yql/essentials/tests/sql/minirun/part15/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part15/canondata/result.json
@@ -994,9 +994,9 @@
     ],
     "test.test[select_yql-window_fn_cumedist_0-default.txt-Debug]": [
         {
-            "checksum": "2d39ed204a785c79a400c7aec8ddc666",
-            "size": 1138,
-            "uri": "https://{canondata_backend}/1777230/1de313d944cae10d4384ec6656e7270944aba259/resource.tar.gz#test.test_select_yql-window_fn_cumedist_0-default.txt-Debug_/opt.yql"
+            "checksum": "4321b5cc8380c1aec98022db246896cf",
+            "size": 1130,
+            "uri": "https://{canondata_backend}/1597364/1bca78d7b758defd06b4be98f2fe193a42e01356/resource.tar.gz#test.test_select_yql-window_fn_cumedist_0-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_cumedist_0-default.txt-Results]": [
@@ -1022,9 +1022,9 @@
     ],
     "test.test[select_yql-window_sum_order_by-default.txt-Debug]": [
         {
-            "checksum": "d18704b7cd5687a47a6223cc27c22273",
-            "size": 1489,
-            "uri": "https://{canondata_backend}/1777230/1de313d944cae10d4384ec6656e7270944aba259/resource.tar.gz#test.test_select_yql-window_sum_order_by-default.txt-Debug_/opt.yql"
+            "checksum": "0c8a8e52161c45ab0943f3e3de0201b7",
+            "size": 1483,
+            "uri": "https://{canondata_backend}/1597364/1bca78d7b758defd06b4be98f2fe193a42e01356/resource.tar.gz#test.test_select_yql-window_sum_order_by-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_sum_order_by-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
index c8fd403de66..7949e4d7f3c 100644
--- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
@@ -1185,9 +1185,9 @@
     ],
     "test.test[select_yql-window_fn_denserank_0-default.txt-Debug]": [
         {
-            "checksum": "6a9ba42bcbdc4ddc96ec039343557b74",
-            "size": 1105,
-            "uri": "https://{canondata_backend}/1773845/5e187b5c8712eed0b26575ad1c84f3158af43d1d/resource.tar.gz#test.test_select_yql-window_fn_denserank_0-default.txt-Debug_/opt.yql"
+            "checksum": "ca5a50ee5060ec9afe7736861432809b",
+            "size": 1099,
+            "uri": "https://{canondata_backend}/1925821/f2b28aa0e5f355230fa71a34945165bb61cc83c8/resource.tar.gz#test.test_select_yql-window_fn_denserank_0-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_denserank_0-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index 66564ad0533..8364da6b360 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -998,9 +998,9 @@
     ],
     "test.test[select_yql-window_fn_denserank_1-default.txt-Debug]": [
         {
-            "checksum": "6a9ba42bcbdc4ddc96ec039343557b74",
-            "size": 1105,
-            "uri": "https://{canondata_backend}/1781765/e6df7efbe28b8ae403e3d9815e1579ecc8bb5445/resource.tar.gz#test.test_select_yql-window_fn_denserank_1-default.txt-Debug_/opt.yql"
+            "checksum": "ca5a50ee5060ec9afe7736861432809b",
+            "size": 1099,
+            "uri": "https://{canondata_backend}/1925821/99c65ad0d48eb8635be35070ffbac7562a8caeb6/resource.tar.gz#test.test_select_yql-window_fn_denserank_1-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_denserank_1-default.txt-Results]": [
@@ -1012,9 +1012,9 @@
     ],
     "test.test[select_yql-window_fn_lead_1-default.txt-Debug]": [
         {
-            "checksum": "6f13a6e531cdac6b385cc8d0f91bc542",
-            "size": 1142,
-            "uri": "https://{canondata_backend}/1781765/e6df7efbe28b8ae403e3d9815e1579ecc8bb5445/resource.tar.gz#test.test_select_yql-window_fn_lead_1-default.txt-Debug_/opt.yql"
+            "checksum": "fb05867dbb5c6029e2cc81b9f329b764",
+            "size": 1111,
+            "uri": "https://{canondata_backend}/1925821/99c65ad0d48eb8635be35070ffbac7562a8caeb6/resource.tar.gz#test.test_select_yql-window_fn_lead_1-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_lead_1-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
index 376d61c5bbc..1f34e8fa04d 100644
--- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
@@ -1358,6 +1358,20 @@
             "uri": "https://{canondata_backend}/1781765/06cae327ea6c6fc81b5ac8d206f80c5f2f779d8e/resource.tar.gz#test.test_select_yql-select_op_except_all-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[select_yql-window_order_by_range_frame-default.txt-Debug]": [
+        {
+            "checksum": "f5594a11491deec2f4dc5ef186e3dfc9",
+            "size": 3029,
+            "uri": "https://{canondata_backend}/1946324/eddd882fce53f309ce53fc129a9daba6e32eab76/resource.tar.gz#test.test_select_yql-window_order_by_range_frame-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[select_yql-window_order_by_range_frame-default.txt-Results]": [
+        {
+            "checksum": "69f4d36a78a6bfbf852dcd731015b12b",
+            "size": 17988,
+            "uri": "https://{canondata_backend}/1946324/eddd882fce53f309ce53fc129a9daba6e32eab76/resource.tar.gz#test.test_select_yql-window_order_by_range_frame-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[subselect-arg_binary_op_rhs-default.txt-Debug]": [
         {
             "checksum": "efcb1500eee7969513053fff14cb9559",
diff --git a/yql/essentials/tests/sql/minirun/part5/canondata/result.json b/yql/essentials/tests/sql/minirun/part5/canondata/result.json
index 7972db4929c..b8f2582c16a 100644
--- a/yql/essentials/tests/sql/minirun/part5/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part5/canondata/result.json
@@ -1144,9 +1144,9 @@
     ],
     "test.test[select_yql-window_fn_lag_1-default.txt-Debug]": [
         {
-            "checksum": "224916ec77f66d5460c1dc5b3e4ae684",
-            "size": 1141,
-            "uri": "https://{canondata_backend}/1847551/58f37c8e1b8c74f66657e94c231b8acc11b82bf5/resource.tar.gz#test.test_select_yql-window_fn_lag_1-default.txt-Debug_/opt.yql"
+            "checksum": "912f670ac04c8a6d0cd19fa36fea6246",
+            "size": 1110,
+            "uri": "https://{canondata_backend}/1942671/45f9d553b9d6acfe784f6ad7decda0ba8d27b40b/resource.tar.gz#test.test_select_yql-window_fn_lag_1-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_lag_1-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
index 95290bd70fe..1ec77cc52d2 100644
--- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
@@ -1355,9 +1355,9 @@
     ],
     "test.test[select_yql-window_fn_ntile_3-default.txt-Debug]": [
         {
-            "checksum": "bbd537fdd5185b5093ad5a8a0323c1ab",
-            "size": 1193,
-            "uri": "https://{canondata_backend}/1917492/53531106e44e755ea77008ec27c01bc41d81b31c/resource.tar.gz#test.test_select_yql-window_fn_ntile_3-default.txt-Debug_/opt.yql"
+            "checksum": "9fe8b0b5a35c5841d155d0fb23513440",
+            "size": 1187,
+            "uri": "https://{canondata_backend}/1946324/c8c3a62c287a7f6a5b775052a3e9fafe254ab713/resource.tar.gz#test.test_select_yql-window_fn_ntile_3-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_ntile_3-default.txt-Results]": [
@@ -1369,9 +1369,9 @@
     ],
     "test.test[select_yql-window_fn_percentrank_1-default.txt-Debug]": [
         {
-            "checksum": "17e793057709520e76d94345aa4d41be",
-            "size": 1150,
-            "uri": "https://{canondata_backend}/1917492/53531106e44e755ea77008ec27c01bc41d81b31c/resource.tar.gz#test.test_select_yql-window_fn_percentrank_1-default.txt-Debug_/opt.yql"
+            "checksum": "50c2e6e23fe4a5b254f15d81951263e0",
+            "size": 1144,
+            "uri": "https://{canondata_backend}/1946324/c8c3a62c287a7f6a5b775052a3e9fafe254ab713/resource.tar.gz#test.test_select_yql-window_fn_percentrank_1-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_percentrank_1-default.txt-Results]": [
@@ -1383,9 +1383,9 @@
     ],
     "test.test[select_yql-window_fn_rownumber_0-default.txt-Debug]": [
         {
-            "checksum": "c64b363527d439c313fa626112a5ceae",
-            "size": 1071,
-            "uri": "https://{canondata_backend}/1917492/53531106e44e755ea77008ec27c01bc41d81b31c/resource.tar.gz#test.test_select_yql-window_fn_rownumber_0-default.txt-Debug_/opt.yql"
+            "checksum": "0fde3444628a3c8ed05d73bf886b4bca",
+            "size": 1065,
+            "uri": "https://{canondata_backend}/1946324/c8c3a62c287a7f6a5b775052a3e9fafe254ab713/resource.tar.gz#test.test_select_yql-window_fn_rownumber_0-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rownumber_0-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
index a675aca8f2c..a281e0c790d 100644
--- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json
@@ -1044,9 +1044,9 @@
     ],
     "test.test[select_yql-window_fn_lag_2-default.txt-Debug]": [
         {
-            "checksum": "572d0bdd6682329001401d4e9c30d704",
-            "size": 1233,
-            "uri": "https://{canondata_backend}/1903280/44f4f1690f38bce1fadfbce889480224fbb8b527/resource.tar.gz#test.test_select_yql-window_fn_lag_2-default.txt-Debug_/opt.yql"
+            "checksum": "11533c2674d775fae62cfadca07460f5",
+            "size": 1202,
+            "uri": "https://{canondata_backend}/1925821/7d68b2f01047644ac6f6a4c865f6380887799c7b/resource.tar.gz#test.test_select_yql-window_fn_lag_2-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_lag_2-default.txt-Results]": [
@@ -1058,9 +1058,9 @@
     ],
     "test.test[select_yql-window_fn_ntile_2-default.txt-Debug]": [
         {
-            "checksum": "b11d7dfef6c7337fd9efbf2bc14a7aea",
-            "size": 1111,
-            "uri": "https://{canondata_backend}/1903280/44f4f1690f38bce1fadfbce889480224fbb8b527/resource.tar.gz#test.test_select_yql-window_fn_ntile_2-default.txt-Debug_/opt.yql"
+            "checksum": "79a7557e7ac63e5dd49494c714cacb8e",
+            "size": 1105,
+            "uri": "https://{canondata_backend}/1925821/7d68b2f01047644ac6f6a4c865f6380887799c7b/resource.tar.gz#test.test_select_yql-window_fn_ntile_2-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_ntile_2-default.txt-Results]": [
@@ -1072,9 +1072,9 @@
     ],
     "test.test[select_yql-window_fn_percentrank_0-default.txt-Debug]": [
         {
-            "checksum": "a82129de6268c51ac6d6afefbbc26fa0",
-            "size": 1201,
-            "uri": "https://{canondata_backend}/1031349/dfe7d66014175a37cee3b83de938f9a55cf3d93f/resource.tar.gz#test.test_select_yql-window_fn_percentrank_0-default.txt-Debug_/opt.yql"
+            "checksum": "920ee8991bd57f9e15bc5f08d7f0b765",
+            "size": 1195,
+            "uri": "https://{canondata_backend}/1925821/7d68b2f01047644ac6f6a4c865f6380887799c7b/resource.tar.gz#test.test_select_yql-window_fn_percentrank_0-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_percentrank_0-default.txt-Results]": [
diff --git a/yql/essentials/tests/sql/minirun/part8/canondata/result.json b/yql/essentials/tests/sql/minirun/part8/canondata/result.json
index 2350769405f..8d86df82f3e 100644
--- a/yql/essentials/tests/sql/minirun/part8/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part8/canondata/result.json
@@ -1110,9 +1110,9 @@
     ],
     "test.test[select_yql-window_fn_percentrank_1_desc-default.txt-Debug]": [
         {
-            "checksum": "cfabad8ec49898e1ba0645dc9fff2c41",
-            "size": 1202,
-            "uri": "https://{canondata_backend}/1923547/79dc99ee790ce067082d276247b7fcfa5c35fc11/resource.tar.gz#test.test_select_yql-window_fn_percentrank_1_desc-default.txt-Debug_/opt.yql"
+            "checksum": "d19689c115fb1651c5b8b005c1d53a14",
+            "size": 1196,
+            "uri": "https://{canondata_backend}/1942671/8770a73d309bdba9f72b9849fbdacbfd60965c72/resource.tar.gz#test.test_select_yql-window_fn_percentrank_1_desc-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_percentrank_1_desc-default.txt-Results]": [
@@ -1124,9 +1124,9 @@
     ],
     "test.test[select_yql-window_fn_rank_0_desc-default.txt-Debug]": [
         {
-            "checksum": "71406981b0060633ea002553b54b2724",
-            "size": 1101,
-            "uri": "https://{canondata_backend}/1781765/ee7884f77a94b3c00c74a5d31da7bffa9735f156/resource.tar.gz#test.test_select_yql-window_fn_rank_0_desc-default.txt-Debug_/opt.yql"
+            "checksum": "2d84655993e9bbb28011a47eaa32d7e1",
+            "size": 1095,
+            "uri": "https://{canondata_backend}/1942671/8770a73d309bdba9f72b9849fbdacbfd60965c72/resource.tar.gz#test.test_select_yql-window_fn_rank_0_desc-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_fn_rank_0_desc-default.txt-Results]": [
@@ -1138,9 +1138,9 @@
     ],
     "test.test[select_yql-window_pg_tutorial_4-default.txt-Debug]": [
         {
-            "checksum": "1a6a5665f6d2d35a76aa64fe1adf8ac4",
-            "size": 1644,
-            "uri": "https://{canondata_backend}/1781765/ee7884f77a94b3c00c74a5d31da7bffa9735f156/resource.tar.gz#test.test_select_yql-window_pg_tutorial_4-default.txt-Debug_/opt.yql"
+            "checksum": "a3a1f2630eae51655d76deca8e7bbd33",
+            "size": 1638,
+            "uri": "https://{canondata_backend}/1942671/8770a73d309bdba9f72b9849fbdacbfd60965c72/resource.tar.gz#test.test_select_yql-window_pg_tutorial_4-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_pg_tutorial_4-default.txt-Results]": [
@@ -1166,9 +1166,9 @@
     ],
     "test.test[select_yql-window_sum_partition_by_order_by-default.txt-Debug]": [
         {
-            "checksum": "351d0b648ce68dd110d2deb4785068c4",
-            "size": 1716,
-            "uri": "https://{canondata_backend}/1781765/ee7884f77a94b3c00c74a5d31da7bffa9735f156/resource.tar.gz#test.test_select_yql-window_sum_partition_by_order_by-default.txt-Debug_/opt.yql"
+            "checksum": "1aa44e07fd43ed9039b1c15171c7b62c",
+            "size": 1710,
+            "uri": "https://{canondata_backend}/1942671/8770a73d309bdba9f72b9849fbdacbfd60965c72/resource.tar.gz#test.test_select_yql-window_sum_partition_by_order_by-default.txt-Debug_/opt.yql"
         }
     ],
     "test.test[select_yql-window_sum_partition_by_order_by-default.txt-Results]": [
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
+);
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);