diff options
| author | atarasov5 <[email protected]> | 2026-01-19 11:04:24 +0300 |
|---|---|---|
| committer | atarasov5 <[email protected]> | 2026-01-19 11:45:10 +0300 |
| commit | 15a5dcdc957b7700cfb15e3f0b4fd5e5e94de9a7 (patch) | |
| tree | f4306a04f7e31698ef6a0577b7a4bc7e733eb362 /yql/essentials/tests/sql | |
| parent | ce297ea2facfd2c98298486d4c3535bc00d05e66 (diff) | |
YQL-13448: Introduce window RANGE logic
Introduce window RANGE logic.
Зеркальный пр с включение фичи путем инлайнинга в коде <https://nda.ya.ru/t/p2qAEoNq7SNwUJ>
Зеркальный пр где я провожу perf измерения <https://nda.ya.ru/t/7UdlI38n7SNwUL> - в нем я описал результаты
commit_hash:2626d7d6b77f1ccb31e395d974a2beaa60f27a97
Diffstat (limited to 'yql/essentials/tests/sql')
112 files changed, 3408 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 daeba65f049..57c9accf0b7 100644 --- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json @@ -1978,6 +1978,62 @@ "uri": "https://{canondata_backend}/1777230/9825a8684763af6f5aebf2f2145914be57ea6969/resource.tar.gz#test.test_window-win_func_mutable_resource-default.txt-Results_/results.txt" } ], + "test.test[window-win_range_datetime--Debug]": [ + { + "checksum": "a097b1bff7d23c34b28e0871c5a00d37", + "size": 1637, + "uri": "https://{canondata_backend}/1937367/f34c72b1cf97e78038e516c0ce86b7993e721cef/resource.tar.gz#test.test_window-win_range_datetime--Debug_/opt.yql" + } + ], + "test.test[window-win_range_datetime--Results]": [ + { + "checksum": "787d452ab683e65eee8e6bce811c35af", + "size": 987, + "uri": "https://{canondata_backend}/1937492/b5dd38b5f8f339a35fb473f566f1eaa247e2a363/resource.tar.gz#test.test_window-win_range_datetime--Results_/results.txt" + } + ], + "test.test[window-win_range_two_columns_success--Debug]": [ + { + "checksum": "681a330d11e8d8cac9db241c06bceb54", + "size": 1552, + "uri": "https://{canondata_backend}/1937367/f34c72b1cf97e78038e516c0ce86b7993e721cef/resource.tar.gz#test.test_window-win_range_two_columns_success--Debug_/opt.yql" + } + ], + "test.test[window-win_range_two_columns_success--Results]": [ + { + "checksum": "b3443155d73266ef1a1265bc1630e8d3", + "size": 914, + "uri": "https://{canondata_backend}/1937492/b5dd38b5f8f339a35fb473f566f1eaa247e2a363/resource.tar.gz#test.test_window-win_range_two_columns_success--Results_/results.txt" + } + ], + "test.test[window-win_range_uint16--Debug]": [ + { + "checksum": "c33a5a66c10f38ccbd0d3f4116a8afa8", + "size": 2169, + "uri": "https://{canondata_backend}/1937367/f34c72b1cf97e78038e516c0ce86b7993e721cef/resource.tar.gz#test.test_window-win_range_uint16--Debug_/opt.yql" + } + ], + "test.test[window-win_range_uint16--Results]": [ + { + "checksum": "ae4149d74cebf9c7610a4213050da604", + "size": 1703, + "uri": "https://{canondata_backend}/1937492/b5dd38b5f8f339a35fb473f566f1eaa247e2a363/resource.tar.gz#test.test_window-win_range_uint16--Results_/results.txt" + } + ], + "test.test[window-win_range_uint64--Debug]": [ + { + "checksum": "f63634271a387f2c84631c637dda9793", + "size": 2054, + "uri": "https://{canondata_backend}/1937367/f34c72b1cf97e78038e516c0ce86b7993e721cef/resource.tar.gz#test.test_window-win_range_uint64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_uint64--Results]": [ + { + "checksum": "895f0c71af1fb5043e3c1aca34e25e5e", + "size": 1715, + "uri": "https://{canondata_backend}/1937492/b5dd38b5f8f339a35fb473f566f1eaa247e2a363/resource.tar.gz#test.test_window-win_range_uint64--Results_/results.txt" + } + ], "test.test[window-yql-14179-default.txt-Debug]": [ { "checksum": "ff4b3d459a8abbe21625fc36854cff0a", diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json index 7e2961d9cc1..3e77c48e2fc 100644 --- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json @@ -1853,6 +1853,34 @@ "uri": "https://{canondata_backend}/1871102/ab8415873dd63e6dbefac4800c4fa4b5a54faba9/resource.tar.gz#test.test_window-win_peephole_double_usage-default.txt-Results_/results.txt" } ], + "test.test[window-win_range_date--Debug]": [ + { + "checksum": "0c91c5e23899806a7f2ad12f77da1bb6", + "size": 1586, + "uri": "https://{canondata_backend}/1599023/6ec8ac6b6f069ce55f95e839456b095494864ede/resource.tar.gz#test.test_window-win_range_date--Debug_/opt.yql" + } + ], + "test.test[window-win_range_date--Results]": [ + { + "checksum": "2e560cda041c0b1b0f04d0c104e379c9", + "size": 1060, + "uri": "https://{canondata_backend}/1900335/559b16c25ae1b1143411eb2a157b3820e72a378d/resource.tar.gz#test.test_window-win_range_date--Results_/results.txt" + } + ], + "test.test[window-win_range_no_sort_current_row--Debug]": [ + { + "checksum": "c8f4dee12e5befd7af519825d4f5571d", + "size": 1045, + "uri": "https://{canondata_backend}/1599023/6ec8ac6b6f069ce55f95e839456b095494864ede/resource.tar.gz#test.test_window-win_range_no_sort_current_row--Debug_/opt.yql" + } + ], + "test.test[window-win_range_no_sort_current_row--Results]": [ + { + "checksum": "52ce419458071c00982a662cb28a9d0a", + "size": 987, + "uri": "https://{canondata_backend}/1900335/559b16c25ae1b1143411eb2a157b3820e72a378d/resource.tar.gz#test.test_window-win_range_no_sort_current_row--Results_/results.txt" + } + ], "test.test[window-yql-14277-default.txt-Debug]": [ { "checksum": "f4c3c9898c84a6d7fe78ff589ea46fd5", diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json index 4a54c89b621..3cf7c5a5101 100644 --- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json @@ -1798,5 +1798,36 @@ "size": 924, "uri": "https://{canondata_backend}/1777230/0109601db9d14d0697bcd3bd39c4b6bd3fc9ce55/resource.tar.gz#test.test_window-win_func_cume_dist_ansi-default.txt-Results_/results.txt" } + ], + "test.test[window-win_range_always_empty--Debug]": [ + { + "checksum": "eda332ac0b1051f885e72c75e8b9cc8f", + "size": 2025, + "uri": "https://{canondata_backend}/1781765/7fcaa8c3cfd45c988d8c601c510553b10bd721cd/resource.tar.gz#test.test_window-win_range_always_empty--Debug_/opt.yql" + } + ], + "test.test[window-win_range_always_empty--Results]": [ + { + "checksum": "f3aaf06891c2a4c22ab670e89c170a4a", + "size": 1341, + "uri": "https://{canondata_backend}/1942278/bdb2b9c0113be9e14ec61c9f26a445e3ecc397de/resource.tar.gz#test.test_window-win_range_always_empty--Results_/results.txt" + }, + { + "uri": "file://test.test_window-win_range_always_empty--Results_/extracted" + } + ], + "test.test[window-win_range_interval64--Debug]": [ + { + "checksum": "7aed367b5940b4f3765e8f14cdf3d4c0", + "size": 1911, + "uri": "https://{canondata_backend}/1781765/7fcaa8c3cfd45c988d8c601c510553b10bd721cd/resource.tar.gz#test.test_window-win_range_interval64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_interval64--Results]": [ + { + "checksum": "f725705a8e0f1cd59215d9cf426723af", + "size": 1396, + "uri": "https://{canondata_backend}/1942278/bdb2b9c0113be9e14ec61c9f26a445e3ecc397de/resource.tar.gz#test.test_window-win_range_interval64--Results_/results.txt" + } ] } diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/test.test_window-win_range_always_empty--Results_/extracted b/yql/essentials/tests/sql/minirun/part2/canondata/test.test_window-win_range_always_empty--Results_/extracted new file mode 100644 index 00000000000..09fcd88f5d3 --- /dev/null +++ b/yql/essentials/tests/sql/minirun/part2/canondata/test.test_window-win_range_always_empty--Results_/extracted @@ -0,0 +1,11 @@ +<tmp_path>/program.sql:<main>: Warning: Type annotation + + <tmp_path>/program.sql:<main>:35:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: PersistableRepr, At function: OrderedSqlProject, At tuple, At function: SqlProjectItem, At lambda + SELECT + ^ + <tmp_path>/program.sql:<main>:36:5: Warning: At function: EnsureWarn + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + ^ + <tmp_path>/program.sql:<main>:36:12: Warning: Consider using non-singular type as first argument of Ensure + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + ^
\ No newline at end of file diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json index a49befd327f..7b06929c098 100644 --- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json @@ -1521,5 +1521,47 @@ "size": 2434, "uri": "https://{canondata_backend}/1777230/5e7f4f59451d2786c03123facb2a9cec2ff42353/resource.tar.gz#test.test_window-win_group_peephole-default.txt-Results_/results.txt" } + ], + "test.test[window-win_range_float--Debug]": [ + { + "checksum": "0d2d82258bb88cfed82d2eca1d36aae8", + "size": 2013, + "uri": "https://{canondata_backend}/1942415/7b6d0aa3a54d3e4f890b6754f1cb4e6217c888d0/resource.tar.gz#test.test_window-win_range_float--Debug_/opt.yql" + } + ], + "test.test[window-win_range_float--Results]": [ + { + "checksum": "05574cedd3319b24bf77098bae8204a2", + "size": 1403, + "uri": "https://{canondata_backend}/1889210/66a223cf4e14995d44a609d001bc322262820d96/resource.tar.gz#test.test_window-win_range_float--Results_/results.txt" + } + ], + "test.test[window-win_range_string_success--Debug]": [ + { + "checksum": "33e46a0356f2a7955ecfd866f81afbe8", + "size": 1477, + "uri": "https://{canondata_backend}/1942415/7b6d0aa3a54d3e4f890b6754f1cb4e6217c888d0/resource.tar.gz#test.test_window-win_range_string_success--Debug_/opt.yql" + } + ], + "test.test[window-win_range_string_success--Results]": [ + { + "checksum": "d96ce6dec2fbc3457fcb0833ff24c873", + "size": 1060, + "uri": "https://{canondata_backend}/1889210/66a223cf4e14995d44a609d001bc322262820d96/resource.tar.gz#test.test_window-win_range_string_success--Results_/results.txt" + } + ], + "test.test[window-win_range_tzdatetime--Debug]": [ + { + "checksum": "942c5500cc850d33c016847ce06110fd", + "size": 1827, + "uri": "https://{canondata_backend}/1942415/7b6d0aa3a54d3e4f890b6754f1cb4e6217c888d0/resource.tar.gz#test.test_window-win_range_tzdatetime--Debug_/opt.yql" + } + ], + "test.test[window-win_range_tzdatetime--Results]": [ + { + "checksum": "e419ee4ba6c42b590fdccde3949ab61c", + "size": 1206, + "uri": "https://{canondata_backend}/1889210/66a223cf4e14995d44a609d001bc322262820d96/resource.tar.gz#test.test_window-win_range_tzdatetime--Results_/results.txt" + } ] } diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json index 71b83380fb2..17fc54855c2 100644 --- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json @@ -2079,6 +2079,62 @@ "uri": "https://{canondata_backend}/1899731/28742e9a38b138bb0af7f4cb8effc6378a2e6625/resource.tar.gz#test.test_union_all-union_all_incompatible-default.txt-Results_/results.txt" } ], + "test.test[window-win_range_tzdate32--Debug]": [ + { + "checksum": "85e2fc3beff38c1d9f149e4307e4fa95", + "size": 1564, + "uri": "https://{canondata_backend}/1599023/d60a483c8471811c129bd7bed1b7f6d295d581e4/resource.tar.gz#test.test_window-win_range_tzdate32--Debug_/opt.yql" + } + ], + "test.test[window-win_range_tzdate32--Results]": [ + { + "checksum": "787d452ab683e65eee8e6bce811c35af", + "size": 987, + "uri": "https://{canondata_backend}/1942278/7e85b51c46267e1bf6df91e2940f54ad3bb724d5/resource.tar.gz#test.test_window-win_range_tzdate32--Results_/results.txt" + } + ], + "test.test[window-win_range_uint32--Debug]": [ + { + "checksum": "41be6d47aeeb3189777ccc3b8357441f", + "size": 2110, + "uri": "https://{canondata_backend}/1599023/d60a483c8471811c129bd7bed1b7f6d295d581e4/resource.tar.gz#test.test_window-win_range_uint32--Debug_/opt.yql" + } + ], + "test.test[window-win_range_uint32--Results]": [ + { + "checksum": "723905774c33df624731afe4a7dc2d2f", + "size": 1412, + "uri": "https://{canondata_backend}/1942278/7e85b51c46267e1bf6df91e2940f54ad3bb724d5/resource.tar.gz#test.test_window-win_range_uint32--Results_/results.txt" + } + ], + "test.test[window-win_range_uint8--Debug]": [ + { + "checksum": "c83614de491b493f2623e8621604b478", + "size": 2131, + "uri": "https://{canondata_backend}/1599023/d60a483c8471811c129bd7bed1b7f6d295d581e4/resource.tar.gz#test.test_window-win_range_uint8--Debug_/opt.yql" + } + ], + "test.test[window-win_range_uint8--Results]": [ + { + "checksum": "a27ca8b8e6e9f7fe7b578e4bcbbc19ae", + "size": 1698, + "uri": "https://{canondata_backend}/1942278/7e85b51c46267e1bf6df91e2940f54ad3bb724d5/resource.tar.gz#test.test_window-win_range_uint8--Results_/results.txt" + } + ], + "test.test[window-win_range_unbounded_non_compact--Debug]": [ + { + "checksum": "c29a02bb9b6b2016616dca819474e3da", + "size": 1971, + "uri": "https://{canondata_backend}/1599023/d60a483c8471811c129bd7bed1b7f6d295d581e4/resource.tar.gz#test.test_window-win_range_unbounded_non_compact--Debug_/opt.yql" + } + ], + "test.test[window-win_range_unbounded_non_compact--Results]": [ + { + "checksum": "fd2a1086451c4b03d885b341c514e553", + "size": 1407, + "uri": "https://{canondata_backend}/1942278/7e85b51c46267e1bf6df91e2940f54ad3bb724d5/resource.tar.gz#test.test_window-win_range_unbounded_non_compact--Results_/results.txt" + } + ], "test.test[window-win_with_as_table-default.txt-Debug]": [ { "checksum": "7c2709c5c8d1842e45523f18744b56f6", diff --git a/yql/essentials/tests/sql/minirun/part5/canondata/result.json b/yql/essentials/tests/sql/minirun/part5/canondata/result.json index 0ef0c06f729..506f7cd1389 100644 --- a/yql/essentials/tests/sql/minirun/part5/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part5/canondata/result.json @@ -2183,5 +2183,47 @@ "size": 4954, "uri": "https://{canondata_backend}/1871102/08c95297ac754f0fe3fecfae40e6440beb75dd66/resource.tar.gz#test.test_window-udaf_with_def_value-default.txt-Results_/results.txt" } + ], + "test.test[window-win_range_date32--Debug]": [ + { + "checksum": "1d6c5d4ff922b3e50052cc5b3d0096bd", + "size": 1492, + "uri": "https://{canondata_backend}/1937367/d5c6f47da27e7bb1b38500104c909a1b0c5047e0/resource.tar.gz#test.test_window-win_range_date32--Debug_/opt.yql" + } + ], + "test.test[window-win_range_date32--Results]": [ + { + "checksum": "4ba8418e9e6a43f1c4208296d39eed41", + "size": 987, + "uri": "https://{canondata_backend}/1942173/6473d32519b28df787f5dcefdfa39d698f7ce5c8/resource.tar.gz#test.test_window-win_range_date32--Results_/results.txt" + } + ], + "test.test[window-win_range_interval--Debug]": [ + { + "checksum": "7512fa519cf26c1700d442325ad3d90a", + "size": 1516, + "uri": "https://{canondata_backend}/1937367/d5c6f47da27e7bb1b38500104c909a1b0c5047e0/resource.tar.gz#test.test_window-win_range_interval--Debug_/opt.yql" + } + ], + "test.test[window-win_range_interval--Results]": [ + { + "checksum": "d8b1c92636ad3bf40250f918d8988ed7", + "size": 987, + "uri": "https://{canondata_backend}/1942173/6473d32519b28df787f5dcefdfa39d698f7ce5c8/resource.tar.gz#test.test_window-win_range_interval--Results_/results.txt" + } + ], + "test.test[window-win_range_timestamp--Debug]": [ + { + "checksum": "ccc84c02f6174531092668f6591c4eb3", + "size": 1525, + "uri": "https://{canondata_backend}/1937367/d5c6f47da27e7bb1b38500104c909a1b0c5047e0/resource.tar.gz#test.test_window-win_range_timestamp--Debug_/opt.yql" + } + ], + "test.test[window-win_range_timestamp--Results]": [ + { + "checksum": "4ba8418e9e6a43f1c4208296d39eed41", + "size": 987, + "uri": "https://{canondata_backend}/1942173/6473d32519b28df787f5dcefdfa39d698f7ce5c8/resource.tar.gz#test.test_window-win_range_timestamp--Results_/results.txt" + } ] } diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json index 0c4e4b83982..9804f808014 100644 --- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json @@ -1860,5 +1860,61 @@ "size": 2663, "uri": "https://{canondata_backend}/1817427/83903caba057ddc7f2c6584714c26b7c6a60a3ab/resource.tar.gz#test.test_window-rank_nulls_legacy-default.txt-Results_/results.txt" } + ], + "test.test[window-win_range_datetime64--Debug]": [ + { + "checksum": "632c988ab8ebca9cba710c831e302b4a", + "size": 1530, + "uri": "https://{canondata_backend}/1599023/c6beed05a0654750d6dd35f156eb5e92e272fef2/resource.tar.gz#test.test_window-win_range_datetime64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_datetime64--Results]": [ + { + "checksum": "d8b1c92636ad3bf40250f918d8988ed7", + "size": 987, + "uri": "https://{canondata_backend}/1924537/09925e74ce5b206f610213346c641877087da325/resource.tar.gz#test.test_window-win_range_datetime64--Results_/results.txt" + } + ], + "test.test[window-win_range_int16--Debug]": [ + { + "checksum": "4f8babcb7143b6e9a7bd32b9ae5e5e9b", + "size": 2156, + "uri": "https://{canondata_backend}/1599023/c6beed05a0654750d6dd35f156eb5e92e272fef2/resource.tar.gz#test.test_window-win_range_int16--Debug_/opt.yql" + } + ], + "test.test[window-win_range_int16--Results]": [ + { + "checksum": "31be6a8314d9c22a8575e7936482336d", + "size": 1703, + "uri": "https://{canondata_backend}/1924537/09925e74ce5b206f610213346c641877087da325/resource.tar.gz#test.test_window-win_range_int16--Results_/results.txt" + } + ], + "test.test[window-win_range_int32--Debug]": [ + { + "checksum": "4d6a62c6bf21c2593b4879f184f1f5a1", + "size": 2939, + "uri": "https://{canondata_backend}/1599023/c6beed05a0654750d6dd35f156eb5e92e272fef2/resource.tar.gz#test.test_window-win_range_int32--Debug_/opt.yql" + } + ], + "test.test[window-win_range_int32--Results]": [ + { + "checksum": "99fd822e9f9fa847c80ca0ce300bc78b", + "size": 2781, + "uri": "https://{canondata_backend}/1924537/09925e74ce5b206f610213346c641877087da325/resource.tar.gz#test.test_window-win_range_int32--Results_/results.txt" + } + ], + "test.test[window-win_range_int64--Debug]": [ + { + "checksum": "de9b719efb4d6442fe5e0d7751ae18a0", + "size": 2038, + "uri": "https://{canondata_backend}/1599023/c6beed05a0654750d6dd35f156eb5e92e272fef2/resource.tar.gz#test.test_window-win_range_int64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_int64--Results]": [ + { + "checksum": "c7dcff5bc39d0d5de7dff008a50bcfc4", + "size": 1423, + "uri": "https://{canondata_backend}/1924537/09925e74ce5b206f610213346c641877087da325/resource.tar.gz#test.test_window-win_range_int64--Results_/results.txt" + } ] } diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json index 40644dbb6ac..0078139c334 100644 --- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json @@ -1656,6 +1656,51 @@ "uri": "https://{canondata_backend}/1817427/cd7fe4c1c700931e8c564489ae0d616c780dd82b/resource.tar.gz#test.test_window-win_func_percent_rank-default.txt-Results_/results.txt" } ], + "test.test[window-win_range_double--Debug]": [ + { + "checksum": "d21780ec012c37ad4ebc77d4197f051c", + "size": 2598, + "uri": "https://{canondata_backend}/1942415/145bc86597611c9d3d2463dc11b7685e37f15121/resource.tar.gz#test.test_window-win_range_double--Debug_/opt.yql" + } + ], + "test.test[window-win_range_double--Results]": [ + { + "checksum": "4a684f17188cd2d3b302d7d2398be4d2", + "size": 2094, + "uri": "https://{canondata_backend}/1784826/43bd381e92058647bc0e86e95ac2ba41fdde5f73/resource.tar.gz#test.test_window-win_range_double--Results_/results.txt" + }, + { + "uri": "file://test.test_window-win_range_double--Results_/extracted" + } + ], + "test.test[window-win_range_int8--Debug]": [ + { + "checksum": "65f986fdaae957c8995aadbffa996d49", + "size": 2865, + "uri": "https://{canondata_backend}/1942415/145bc86597611c9d3d2463dc11b7685e37f15121/resource.tar.gz#test.test_window-win_range_int8--Debug_/opt.yql" + } + ], + "test.test[window-win_range_int8--Results]": [ + { + "checksum": "2e3360cb27a204b4646f88f3a9e2f358", + "size": 2701, + "uri": "https://{canondata_backend}/1924537/bd5e62bd933224323303716cb90137141d0e39ad/resource.tar.gz#test.test_window-win_range_int8--Results_/results.txt" + } + ], + "test.test[window-win_range_tztimestamp64--Debug]": [ + { + "checksum": "c2752a85fb26b5bdc84fc14ee1cb0069", + "size": 1541, + "uri": "https://{canondata_backend}/1942415/145bc86597611c9d3d2463dc11b7685e37f15121/resource.tar.gz#test.test_window-win_range_tztimestamp64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_tztimestamp64--Results]": [ + { + "checksum": "a4e1edc209e5e7b458a3c4c1fedbe9df", + "size": 987, + "uri": "https://{canondata_backend}/1924537/bd5e62bd933224323303716cb90137141d0e39ad/resource.tar.gz#test.test_window-win_range_tztimestamp64--Results_/results.txt" + } + ], "test.test[window-yql-19709-default.txt-Debug]": [ { "checksum": "b4f5b1907698dfd26478f9b7345f7794", diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/test.test_window-win_range_double--Results_/extracted b/yql/essentials/tests/sql/minirun/part7/canondata/test.test_window-win_range_double--Results_/extracted new file mode 100644 index 00000000000..36cd19deff4 --- /dev/null +++ b/yql/essentials/tests/sql/minirun/part7/canondata/test.test_window-win_range_double--Results_/extracted @@ -0,0 +1,5 @@ +<tmp_path>/program.sql:<main>: Warning: Parse Sql + + <tmp_path>/program.sql:<main>:39:1: Warning: Symbol $str is not used + $str = ($x) -> { + ^
\ No newline at end of file diff --git a/yql/essentials/tests/sql/minirun/part8/canondata/result.json b/yql/essentials/tests/sql/minirun/part8/canondata/result.json index 65d538dc199..730eedead1e 100644 --- a/yql/essentials/tests/sql/minirun/part8/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part8/canondata/result.json @@ -1774,5 +1774,47 @@ "size": 14988, "uri": "https://{canondata_backend}/1937027/768c048f063cb934e42138f86a1cf134863bee57/resource.tar.gz#test.test_window-fuse_with_rename_session_start_first-default.txt-Results_/results.txt" } + ], + "test.test[window-win_range_timestamp64--Debug]": [ + { + "checksum": "ad308cf67de4aa7b6375363cd467950f", + "size": 1546, + "uri": "https://{canondata_backend}/1903280/07355a9d83b3f401ae28f4fdf7530075a1f5522a/resource.tar.gz#test.test_window-win_range_timestamp64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_timestamp64--Results]": [ + { + "checksum": "a575cbd7f19992ab67f1cfade2c77758", + "size": 987, + "uri": "https://{canondata_backend}/1942525/99c15fa016128c7fb56eed5e8923d2dab837b3cd/resource.tar.gz#test.test_window-win_range_timestamp64--Results_/results.txt" + } + ], + "test.test[window-win_range_tzdatetime64--Debug]": [ + { + "checksum": "dd86e84592a6a8aa0f24179147380168", + "size": 1783, + "uri": "https://{canondata_backend}/1903280/07355a9d83b3f401ae28f4fdf7530075a1f5522a/resource.tar.gz#test.test_window-win_range_tzdatetime64--Debug_/opt.yql" + } + ], + "test.test[window-win_range_tzdatetime64--Results]": [ + { + "checksum": "97ddb669e7c762b87a2e058349039a60", + "size": 1206, + "uri": "https://{canondata_backend}/1942525/99c15fa016128c7fb56eed5e8923d2dab837b3cd/resource.tar.gz#test.test_window-win_range_tzdatetime64--Results_/results.txt" + } + ], + "test.test[window-win_range_tztimestamp--Debug]": [ + { + "checksum": "e2daa8df0186eef70f55fa9d118d649c", + "size": 1548, + "uri": "https://{canondata_backend}/1903280/07355a9d83b3f401ae28f4fdf7530075a1f5522a/resource.tar.gz#test.test_window-win_range_tztimestamp--Debug_/opt.yql" + } + ], + "test.test[window-win_range_tztimestamp--Results]": [ + { + "checksum": "4e65f1b76331d053068bf8a6fc242084", + "size": 914, + "uri": "https://{canondata_backend}/1942525/99c15fa016128c7fb56eed5e8923d2dab837b3cd/resource.tar.gz#test.test_window-win_range_tztimestamp--Results_/results.txt" + } ] } diff --git a/yql/essentials/tests/sql/minirun/part9/canondata/result.json b/yql/essentials/tests/sql/minirun/part9/canondata/result.json index 32c417c69d5..31f91031c18 100644 --- a/yql/essentials/tests/sql/minirun/part9/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part9/canondata/result.json @@ -1873,5 +1873,47 @@ "size": 983, "uri": "https://{canondata_backend}/1871102/116385eb6fe562fc7bfbaa4f34a104024a4dae99/resource.tar.gz#test.test_window-win_over_joined-default.txt-Results_/results.txt" } + ], + "test.test[window-win_range_int64_order_by_multiply--Debug]": [ + { + "checksum": "4916124a2a600b1d21a7544a63d2b7d3", + "size": 1853, + "uri": "https://{canondata_backend}/1942415/abc8ff0bc7ce1b4382b676962a2321d49a930356/resource.tar.gz#test.test_window-win_range_int64_order_by_multiply--Debug_/opt.yql" + } + ], + "test.test[window-win_range_int64_order_by_multiply--Results]": [ + { + "checksum": "1b703b12cc68cead213448c9d445199a", + "size": 1195, + "uri": "https://{canondata_backend}/995452/f47564f6c57d297ad9007057d5687a16a72d1529/resource.tar.gz#test.test_window-win_range_int64_order_by_multiply--Results_/results.txt" + } + ], + "test.test[window-win_range_tzdate--Debug]": [ + { + "checksum": "e0a33e49d4226d5826f2ea159b72b043", + "size": 1506, + "uri": "https://{canondata_backend}/1942415/abc8ff0bc7ce1b4382b676962a2321d49a930356/resource.tar.gz#test.test_window-win_range_tzdate--Debug_/opt.yql" + } + ], + "test.test[window-win_range_tzdate--Results]": [ + { + "checksum": "a575cbd7f19992ab67f1cfade2c77758", + "size": 987, + "uri": "https://{canondata_backend}/1942278/0ddda7a1843b8f1bd86bd3f85a3608f01cefd34f/resource.tar.gz#test.test_window-win_range_tzdate--Results_/results.txt" + } + ], + "test.test[window-win_range_unbounded_compact--Debug]": [ + { + "checksum": "d6bf3ea2a4d79802126ffca10d6b63ba", + "size": 2005, + "uri": "https://{canondata_backend}/1942415/abc8ff0bc7ce1b4382b676962a2321d49a930356/resource.tar.gz#test.test_window-win_range_unbounded_compact--Debug_/opt.yql" + } + ], + "test.test[window-win_range_unbounded_compact--Results]": [ + { + "checksum": "fd2a1086451c4b03d885b341c514e553", + "size": 1407, + "uri": "https://{canondata_backend}/1936997/f1acad6ad8fea2ff9a9ddc4955b72dc215714c63/resource.tar.gz#test.test_window-win_range_unbounded_compact--Results_/results.txt" + } ] } diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json index dccc1cbe3fb..4499fbf101c 100644 --- a/yql/essentials/tests/sql/sql2yql/canondata/result.json +++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json @@ -9736,6 +9736,237 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_window-win_peephole_double_usage_/sql.yql" } ], + "test_sql2yql.test[window-win_range_always_empty]": [ + { + "checksum": "2ef693d3b03cc8c3f4e7aae2bb3ad8bb", + "size": 3899, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_always_empty_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_date32]": [ + { + "checksum": "84d51512d1b4296e63bbc337e9633717", + "size": 3121, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_date32_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_date]": [ + { + "checksum": "52d60db7624fe371ef604361700d7322", + "size": 3234, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_date_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_datetime64]": [ + { + "checksum": "2e8aff0050289ba6c109e9cc314419a6", + "size": 3145, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_datetime64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_datetime]": [ + { + "checksum": "6eb63937b94d44377a08d68a182a30b1", + "size": 3286, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_datetime_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_double]": [ + { + "checksum": "a6c70091fabedabb808bc762184563b1", + "size": 5925, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_double_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_float]": [ + { + "checksum": "d8a950a21a1906f09488fee8a026eb5d", + "size": 3935, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_float_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_int16]": [ + { + "checksum": "3631c6caea719a0bac631b1f7a5ca820", + "size": 3903, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_int16_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_int32]": [ + { + "checksum": "99abd04f1ef9c008ac0123e8ef1e8afd", + "size": 6197, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_int32_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_int64]": [ + { + "checksum": "d6424c526be41be015300f479e4912b4", + "size": 3923, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_int64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_int64_order_by_multiply]": [ + { + "checksum": "66343f5e37b62e24b2160c9ade25f3de", + "size": 3735, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_int64_order_by_multiply_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_int8]": [ + { + "checksum": "e76d148344c6a2035db99d27ddd2e853", + "size": 6141, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_int8_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_interval64]": [ + { + "checksum": "40763826db068ea9e07a0c62e14c8d49", + "size": 4530, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_interval64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_interval]": [ + { + "checksum": "be781f7e90a3b0ee39e95a6bf454ae39", + "size": 3133, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_interval_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_no_sort_current_row]": [ + { + "checksum": "37688fb1e53c8bd60bf8e4dcdb0bab46", + "size": 2951, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_no_sort_current_row_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_string_current_row_fail]": [ + { + "checksum": "594d135d591c0043ed9b4fa1dfefaad5", + "size": 2006, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_string_current_row_fail_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_string_success]": [ + { + "checksum": "f0ba74785bd3ff0a8696f2e1b73f3ae5", + "size": 3174, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_string_success_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_timestamp64]": [ + { + "checksum": "745e3f40873c24217cbad230c20abd50", + "size": 3203, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_timestamp64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_timestamp]": [ + { + "checksum": "96e73cba85259e13a07e8863248fa711", + "size": 3151, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_timestamp_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_two_columns_success]": [ + { + "checksum": "3883650132f04bd03b437993588ef499", + "size": 3205, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_two_columns_success_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_two_columns_with_current_row_fail]": [ + { + "checksum": "f527652b7b97b174cbc60d43bfb5bc01", + "size": 2092, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_two_columns_with_current_row_fail_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_tzdate32]": [ + { + "checksum": "a4365ef89f1049c8defda0e6b8dabf09", + "size": 3202, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_tzdate32_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_tzdate]": [ + { + "checksum": "8b5ed5ef6ec4638b4124aaf7c2383d2c", + "size": 3151, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_tzdate_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_tzdatetime64]": [ + { + "checksum": "115f5d3e4966934a312d67658ae40812", + "size": 3610, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_tzdatetime64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_tzdatetime]": [ + { + "checksum": "6d8fef79eb6efdb4321cd228704bf899", + "size": 3594, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_tzdatetime_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_tztimestamp64]": [ + { + "checksum": "a4f5f46e05c4fecf196e3ad27f4bc797", + "size": 3167, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_tztimestamp64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_tztimestamp]": [ + { + "checksum": "eeab5e42c4eda6809499b5f1478ded52", + "size": 3108, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_tztimestamp_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_uint16]": [ + { + "checksum": "9e0277c25ec78b91a4ed4e781dac54bd", + "size": 3918, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_uint16_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_uint32]": [ + { + "checksum": "9cdbdd22c659581853def10f97aea862", + "size": 3956, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_uint32_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_uint64]": [ + { + "checksum": "b52a29c00d494ea8f67783b156e0add1", + "size": 3913, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_uint64_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_uint8]": [ + { + "checksum": "e438f141a5550ef90f83c93f1fab81c7", + "size": 3892, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_uint8_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_unbounded_compact]": [ + { + "checksum": "5669928ee21a86cba611a0d3e9ed428b", + "size": 3880, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_unbounded_compact_/sql.yql" + } + ], + "test_sql2yql.test[window-win_range_unbounded_non_compact]": [ + { + "checksum": "c9ae323ef7604e673b54e7394d731f7a", + "size": 3868, + "uri": "https://{canondata_backend}/1903280/397315720fa648d3b3b50280c855e13986b1a22e/resource.tar.gz#test_sql2yql.test_window-win_range_unbounded_non_compact_/sql.yql" + } + ], "test_sql2yql.test[window-win_with_as_table]": [ { "checksum": "a0854b2700bb786745d0d98dfeb2c4fa", @@ -15263,6 +15494,171 @@ "uri": "file://test_sql_format.test_window-win_peephole_double_usage_/formatted.sql" } ], + "test_sql_format.test[window-win_range_always_empty]": [ + { + "uri": "file://test_sql_format.test_window-win_range_always_empty_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_date32]": [ + { + "uri": "file://test_sql_format.test_window-win_range_date32_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_date]": [ + { + "uri": "file://test_sql_format.test_window-win_range_date_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_datetime64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_datetime64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_datetime]": [ + { + "uri": "file://test_sql_format.test_window-win_range_datetime_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_double]": [ + { + "uri": "file://test_sql_format.test_window-win_range_double_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_float]": [ + { + "uri": "file://test_sql_format.test_window-win_range_float_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_int16]": [ + { + "uri": "file://test_sql_format.test_window-win_range_int16_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_int32]": [ + { + "uri": "file://test_sql_format.test_window-win_range_int32_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_int64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_int64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_int64_order_by_multiply]": [ + { + "uri": "file://test_sql_format.test_window-win_range_int64_order_by_multiply_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_int8]": [ + { + "uri": "file://test_sql_format.test_window-win_range_int8_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_interval64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_interval64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_interval]": [ + { + "uri": "file://test_sql_format.test_window-win_range_interval_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_no_sort_current_row]": [ + { + "uri": "file://test_sql_format.test_window-win_range_no_sort_current_row_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_string_current_row_fail]": [ + { + "uri": "file://test_sql_format.test_window-win_range_string_current_row_fail_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_string_success]": [ + { + "uri": "file://test_sql_format.test_window-win_range_string_success_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_timestamp64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_timestamp64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_timestamp]": [ + { + "uri": "file://test_sql_format.test_window-win_range_timestamp_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_two_columns_success]": [ + { + "uri": "file://test_sql_format.test_window-win_range_two_columns_success_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_two_columns_with_current_row_fail]": [ + { + "uri": "file://test_sql_format.test_window-win_range_two_columns_with_current_row_fail_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_tzdate32]": [ + { + "uri": "file://test_sql_format.test_window-win_range_tzdate32_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_tzdate]": [ + { + "uri": "file://test_sql_format.test_window-win_range_tzdate_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_tzdatetime64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_tzdatetime64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_tzdatetime]": [ + { + "uri": "file://test_sql_format.test_window-win_range_tzdatetime_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_tztimestamp64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_tztimestamp64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_tztimestamp]": [ + { + "uri": "file://test_sql_format.test_window-win_range_tztimestamp_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_uint16]": [ + { + "uri": "file://test_sql_format.test_window-win_range_uint16_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_uint32]": [ + { + "uri": "file://test_sql_format.test_window-win_range_uint32_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_uint64]": [ + { + "uri": "file://test_sql_format.test_window-win_range_uint64_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_uint8]": [ + { + "uri": "file://test_sql_format.test_window-win_range_uint8_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_unbounded_compact]": [ + { + "uri": "file://test_sql_format.test_window-win_range_unbounded_compact_/formatted.sql" + } + ], + "test_sql_format.test[window-win_range_unbounded_non_compact]": [ + { + "uri": "file://test_sql_format.test_window-win_range_unbounded_non_compact_/formatted.sql" + } + ], "test_sql_format.test[window-win_with_as_table]": [ { "uri": "file://test_sql_format.test_window-win_with_as_table_/formatted.sql" diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_always_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_always_empty_/formatted.sql new file mode 100644 index 00000000000..c4fcc04c2d0 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_always_empty_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 0|>, + <|a: NULL, b: 1, sum: NULL, count: 0|>, + <|a: uint8('8'), b: 1, sum: NULL, count: 0|>, + <|a: uint8('10'), b: 1, sum: NULL, count: 0|>, + <|a: uint8('11'), b: 1, sum: NULL, count: 0|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN uint8('1') PRECEDING AND uint8('2') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_date32_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_date32_/formatted.sql new file mode 100644 index 00000000000..dda5c6fb20f --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_date32_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: Date32('2017-11-25'), b: 1, count: 2|>, + <|a: Date32('2017-11-26'), b: 1, count: 3|>, + <|a: Date32('2017-11-27'), b: 1, count: 4|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND Interval64('P1D') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_date_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_date_/formatted.sql new file mode 100644 index 00000000000..dbc02715b0a --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_date_/formatted.sql @@ -0,0 +1,37 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Date('2017-11-24'), b: 1, count: 0|>, + <|a: Date('2017-11-25'), b: 1, count: 1|>, + <|a: Date('2017-11-26'), b: 1, count: 2|>, + <|a: Date('2017-11-27'), b: 1, count: 3|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval('P3D') PRECEDING AND Interval('P1D') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_datetime64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_datetime64_/formatted.sql new file mode 100644 index 00000000000..94a1b2a39ea --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_datetime64_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, + <|a: Datetime64('2017-11-27T13:22:00Z'), b: 1, count: 2|>, + <|a: Datetime64('2017-11-27T13:23:00Z'), b: 1, count: 1|>, + <|a: Datetime64('2017-11-27T13:24:00Z'), b: 1, count: 0|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64('PT1M') FOLLOWING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_datetime_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_datetime_/formatted.sql new file mode 100644 index 00000000000..1ab6b9f5e0c --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_datetime_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Datetime('2017-11-27T13:22:00Z'), b: 1, count: 0|>, + <|a: Datetime('2017-11-27T13:23:00Z'), b: 1, count: 1|>, + <|a: Datetime('2017-11-27T13:24:00Z'), b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a DESC + RANGE BETWEEN Interval('PT1M') FOLLOWING AND Interval('PT3M') FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, 'Got: ' || $str(actual_count) || ', but expected: ' || $str(count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_double_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_double_/formatted.sql new file mode 100644 index 00000000000..1bcf29bd41d --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_double_/formatted.sql @@ -0,0 +1,54 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: double('-10.5'), b: 1, sum1: double('-10.5'), count1: 1, sum2: NULL, count2: 0|>, + <|a: double('-5.0'), b: 1, sum1: double('-15.5'), count1: 2, sum2: NULL, count2: 0|>, + <|a: double('0.0'), b: 1, sum1: double('-5.0'), count1: 2, sum2: double('-5.0'), count2: 1|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum1, + COUNT(*) OVER w1 AS actual_count1, + SUM(a) OVER w2 AS actual_sum2, + COUNT(*) OVER w2 AS actual_count2, + sum1, + count1, + sum2, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN double('10.0') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN double('5.0') PRECEDING AND double('0.5') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum1, sum1 IS NOT DISTINCT FROM actual_sum1, $str(actual_sum1)), + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(sum2, sum2 IS NOT DISTINCT FROM actual_sum2, $str(actual_sum2)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_float_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_float_/formatted.sql new file mode 100644 index 00000000000..313cd70eee2 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_float_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: float('1.5'), b: 1, sum: float('1.5'), count: 1|>, + <|a: float('2.0'), b: 1, sum: float('3.5'), count: 2|>, + <|a: float('2.8'), b: 1, sum: float('6.3'), count: 3|>, + <|a: float('5.0'), b: 1, sum: float('5.0'), count: 1|>, + <|a: float('6.0'), b: 1, sum: float('11.0'), count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN float('1.5') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int16_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int16_/formatted.sql new file mode 100644 index 00000000000..78f9811181e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int16_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int16('-1000'), b: 1, sum: int16('-1500'), count: 5|>, + <|a: int16('-500'), b: 1, sum: int16('-500'), count: 4|>, + <|a: int16('0'), b: 1, sum: int16('0'), count: 3|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + -a DESC + RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int32_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int32_/formatted.sql new file mode 100644 index 00000000000..d5da54eaece --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int32_/formatted.sql @@ -0,0 +1,52 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int32('-50000'), b: 1, sum1: int32('-50000'), count1: 1, sum2: NULL, count2: 0|>, + <|a: int32('-10000'), b: 1, sum1: int32('-60000'), count1: 2, sum2: int32('-50000'), count2: 1|>, + <|a: int32('0'), b: 1, sum1: int32('-60000'), count1: 3, sum2: int32('-10000'), count2: 1|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum1, + COUNT(*) OVER w1 AS actual_count1, + SUM(a) OVER w2 AS actual_sum2, + COUNT(*) OVER w2 AS actual_count2, + sum1, + count1, + sum2, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int32('50000') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int32('40000') PRECEDING AND int32('10000') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum1, sum1 IS NOT DISTINCT FROM actual_sum1, $str(actual_sum1)), + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(sum2, sum2 IS NOT DISTINCT FROM actual_sum2, $str(actual_sum2)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int64_/formatted.sql new file mode 100644 index 00000000000..b09eb71db88 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int64_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: int64('-1500000'), count: 5|>, + <|a: NULL, b: 1, sum: int64('-1500000'), count: 5|>, + <|a: int64('-1000000'), b: 1, sum: int64('-1500000'), count: 3|>, + <|a: int64('-500000'), b: 1, sum: int64('-500000'), count: 2|>, + <|a: int64('0'), b: 1, sum: int64('0'), count: 1|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int64_order_by_multiply_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int64_order_by_multiply_/formatted.sql new file mode 100644 index 00000000000..5d7b6de4550 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int64_order_by_multiply_/formatted.sql @@ -0,0 +1,37 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int64('-1'), b: 1, sum: int64('-3'), count: 2|>, + <|a: int64('-2'), b: 1, sum: int64('-6'), count: 3|>, + <|a: int64('-3'), b: 1, sum: int64('-5'), count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION BY + b + ORDER BY + a * 5 ASC + RANGE BETWEEN 5l PRECEDING AND 5l FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int8_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int8_/formatted.sql new file mode 100644 index 00000000000..4ecbd2bdf9e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_int8_/formatted.sql @@ -0,0 +1,52 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int8('-8'), b: 1, sum1: int8('-8'), count1: 1, sum2: NULL, count2: 0|>, + <|a: int8('-5'), b: 1, sum1: int8('-13'), count1: 2, sum2: int8('-8'), count2: 1|>, + <|a: int8('0'), b: 1, sum1: int8('-5'), count1: 2, sum2: NULL, count2: 0|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum1, + COUNT(*) OVER w1 AS actual_count1, + SUM(a) OVER w2 AS actual_sum2, + COUNT(*) OVER w2 AS actual_count2, + sum1, + count1, + sum2, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int8('5') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int8('3') PRECEDING AND int8('2') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum1, sum1 IS NOT DISTINCT FROM actual_sum1, $str(actual_sum1)), + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(sum2, sum2 IS NOT DISTINCT FROM actual_sum2, $str(actual_sum2)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_interval64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_interval64_/formatted.sql new file mode 100644 index 00000000000..2d5181462ef --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_interval64_/formatted.sql @@ -0,0 +1,46 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Interval64('P1DT2H3M4.567888S'), b: 1, count1: 1, count2: 2|>, + <|a: Interval64('P1DT2H3M4.567889S'), b: 1, count1: 2, count2: 2|>, + <|a: Interval64('P1DT2H3M4.567890S'), b: 1, count1: 2, count2: 1|>, + <|a: NULL, b: 1, count1: 2, count2: 2|>, + <|a: NULL, b: 1, count1: 2, count2: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count1, + COUNT(*) OVER w2 AS actual_count2, + count1, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64('PT0.000001S') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND Interval64('PT0.000001S') FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_interval_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_interval_/formatted.sql new file mode 100644 index 00000000000..2cb255ee45e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_interval_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Interval('P1DT2H3M4.567888S'), b: 1, count: 2|>, + <|a: Interval('P1DT2H3M4.567889S'), b: 1, count: 1|>, + <|a: Interval('P1DT2H3M4.567890S'), b: 1, count: 0|>, + <|a: NULL, b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval('PT0.000001S') FOLLOWING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_no_sort_current_row_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_no_sort_current_row_/formatted.sql new file mode 100644 index 00000000000..f15e0033008 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_no_sort_current_row_/formatted.sql @@ -0,0 +1,34 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Timestamp('2017-11-27T13:24:00.123454Z'), b: 1, count: 5|>, + <|a: Timestamp('2017-11-27T13:24:00.123455Z'), b: 1, count: 5|>, + <|a: Timestamp('2017-11-27T13:24:00.123456Z'), b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + RANGE BETWEEN CURRENT ROW AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_string_current_row_fail_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_string_current_row_fail_/formatted.sql new file mode 100644 index 00000000000..27b585f75a8 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_string_current_row_fail_/formatted.sql @@ -0,0 +1,23 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +/* custom error: Range frame for non numeric expressions is only allowed to be UNBOUNDED PRECEDING AND CURRENT ROW */ +$data = [ + <|a: 'apple', b: 1, count: 1|>, + <|a: 'banana', b: 1, count: 2|>, +]; + +SELECT + COUNT(*) OVER w1 AS actual_count, + count, +FROM + AS_TABLE($data) +WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND CURRENT ROW + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_string_success_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_string_success_/formatted.sql new file mode 100644 index 00000000000..2d4b6902c21 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_string_success_/formatted.sql @@ -0,0 +1,37 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: 'apple', b: 1, count: 3|>, + <|a: 'banana', b: 1, count: 4|>, + <|a: 'cherry', b: 1, count: 6|>, + <|a: 'cherry', b: 1, count: 6|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_timestamp64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_timestamp64_/formatted.sql new file mode 100644 index 00000000000..567ff24b6cd --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_timestamp64_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Timestamp64('2017-11-27T13:24:00.123454Z'), b: 1, count: 1|>, + <|a: Timestamp64('2017-11-27T13:24:00.123455Z'), b: 1, count: 2|>, + <|a: Timestamp64('2017-11-27T13:24:00.123456Z'), b: 1, count: 2|>, + <|a: Timestamp64('2017-11-27T13:24:00.123457Z'), b: 1, count: 2|>, + <|a: Timestamp64('2017-11-27T13:24:00.123458Z'), b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64('PT0.000001S') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_timestamp_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_timestamp_/formatted.sql new file mode 100644 index 00000000000..1aa8b5c73de --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_timestamp_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Timestamp('2017-11-27T13:24:00.123454Z'), b: 1, count: 2|>, + <|a: Timestamp('2017-11-27T13:24:00.123455Z'), b: 1, count: 3|>, + <|a: Timestamp('2017-11-27T13:24:00.123456Z'), b: 1, count: 4|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND Interval('PT0.000001S') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_two_columns_success_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_two_columns_success_/formatted.sql new file mode 100644 index 00000000000..c49e8e16ab1 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_two_columns_success_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: 'apple', c: 1, b: 1, count: 1|>, + <|a: 'apple', c: 2, b: 1, count: 2|>, + <|a: 'banana', c: 1, b: 1, count: 3|>, + <|a: 'banana', c: 2, b: 1, count: 4|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC, + c ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_two_columns_with_current_row_fail_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_two_columns_with_current_row_fail_/formatted.sql new file mode 100644 index 00000000000..787e7527f32 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_two_columns_with_current_row_fail_/formatted.sql @@ -0,0 +1,25 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +/* custom error: Range frame for multiple expressions is only allowed to be UNBOUNDED PRECEDING AND CURRENT ROW. */ +$data = [ + <|a: 1, c: 1, b: 1|>, + <|a: 1, c: 2, b: 1|>, + <|a: 2, c: 1, b: 1|>, + <|a: 2, c: 2, b: 1|>, +]; + +SELECT + COUNT(*) OVER w1 AS cnt, +FROM + AS_TABLE($data) +WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC, + c ASC + RANGE BETWEEN CURRENT ROW AND CURRENT ROW + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdate32_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdate32_/formatted.sql new file mode 100644 index 00000000000..be9ca56009a --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdate32_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDate32('2017-11-25,Europe/Moscow'), b: 1, count: 0|>, + <|a: TzDate32('2017-11-26,Europe/Moscow'), b: 1, count: 1|>, + <|a: TzDate32('2017-11-27,Europe/Moscow'), b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64('P3D') PRECEDING AND Interval64('P1D') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdate_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdate_/formatted.sql new file mode 100644 index 00000000000..9bb5a15ac73 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdate_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDate('2017-11-25,Europe/Moscow'), b: 1, count: 1|>, + <|a: TzDate('2017-11-26,Europe/Moscow'), b: 1, count: 2|>, + <|a: TzDate('2017-11-27,Europe/Moscow'), b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval('P1D') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdatetime64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdatetime64_/formatted.sql new file mode 100644 index 00000000000..9d7a7717dc4 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdatetime64_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDatetime64('2017-11-27T13:22:00,America/Los_Angeles'), b: 1, count: 3|>, + <|a: TzDatetime64('2017-11-27T13:23:00,America/Los_Angeles'), b: 1, count: 3|>, + <|a: TzDatetime64('2017-11-27T13:24:00,America/Los_Angeles'), b: 1, count: 2|>, + <|a: TzDatetime64('2017-11-27T13:22:00,America/Los_Angeles'), b: 2, count: 3|>, + <|a: TzDatetime64('2017-11-27T13:23:00,America/Los_Angeles'), b: 2, count: 3|>, + <|a: TzDatetime64('2017-11-27T13:24:00,America/Los_Angeles'), b: 2, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64('PT1M') PRECEDING AND Interval64('PT3M') FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdatetime_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdatetime_/formatted.sql new file mode 100644 index 00000000000..9ba5e614335 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tzdatetime_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDatetime('2017-11-27T13:22:00,America/Los_Angeles'), b: 1, count: 0|>, + <|a: TzDatetime('2017-11-27T13:23:00,America/Los_Angeles'), b: 1, count: 1|>, + <|a: TzDatetime('2017-11-27T13:24:00,America/Los_Angeles'), b: 1, count: 2|>, + <|a: TzDatetime('2017-11-27T13:22:00,America/Los_Angeles'), b: 2, count: 0|>, + <|a: TzDatetime('2017-11-27T13:23:00,America/Los_Angeles'), b: 2, count: 1|>, + <|a: TzDatetime('2017-11-27T13:24:00,America/Los_Angeles'), b: 3, count: 0|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval('PT3M') PRECEDING AND Interval('PT1M') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tztimestamp64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tztimestamp64_/formatted.sql new file mode 100644 index 00000000000..cb2f7a4edfe --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tztimestamp64_/formatted.sql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzTimestamp64('2017-11-27T13:24:00.123454,GMT'), b: 1, count: 2|>, + <|a: TzTimestamp64('2017-11-27T13:24:00.123455,GMT'), b: 1, count: 1|>, + <|a: TzTimestamp64('2017-11-27T13:24:00.123459,GMT'), b: 1, count: 1|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND Interval64('PT0.000001S') FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tztimestamp_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tztimestamp_/formatted.sql new file mode 100644 index 00000000000..3fd6f2dfa1e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_tztimestamp_/formatted.sql @@ -0,0 +1,35 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzTimestamp('2017-11-27T13:24:00.123454,GMT'), b: 1, count: 2|>, + <|a: TzTimestamp('2017-11-27T13:24:00.123455,GMT'), b: 1, count: 1|>, + <|a: TzTimestamp('2017-11-27T13:24:00.123456,GMT'), b: 1, count: 0|>, + <|a: NULL, b: 1, count: 1|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval('PT0.000001S') FOLLOWING AND Interval('PT0.000003S') FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint16_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint16_/formatted.sql new file mode 100644 index 00000000000..b8370c61bd4 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint16_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: uint16('100'), b: 1, sum: uint16('100'), count: 3|>, + <|a: uint16('200'), b: 1, sum: uint16('300'), count: 4|>, + <|a: uint16('250'), b: 1, sum: uint16('550'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND uint16('10') FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint32_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint32_/formatted.sql new file mode 100644 index 00000000000..244f6530525 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint32_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: uint32('1000'), b: 1, sum: uint32('1000'), count: 1|>, + <|a: uint32('2000'), b: 1, sum: uint32('3000'), count: 2|>, + <|a: uint32('2500'), b: 1, sum: uint32('4500'), count: 2|>, + <|a: uint32('3000'), b: 1, sum: uint32('7500'), count: 3|>, + <|a: uint32('5000'), b: 1, sum: uint32('5000'), count: 1|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN uint32('1000') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint64_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint64_/formatted.sql new file mode 100644 index 00000000000..c8d95198094 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint64_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: uint64('1000000'), b: 1, sum: uint64('1000000'), count: 3|>, + <|a: uint64('2000000'), b: 1, sum: uint64('3000000'), count: 4|>, + <|a: uint64('3000000'), b: 1, sum: uint64('6000000'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint8_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint8_/formatted.sql new file mode 100644 index 00000000000..0559b41284e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_uint8_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: uint8('8'), b: 1, sum: uint8('8'), count: 1|>, + <|a: uint8('10'), b: 1, sum: uint8('10'), count: 1|>, + <|a: uint8('11'), b: 1, sum: uint8('21'), count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN uint8('1') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_unbounded_compact_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_unbounded_compact_/formatted.sql new file mode 100644 index 00000000000..0efd929a58a --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_unbounded_compact_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('100'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('200'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('250'), b: 1, sum: uint16('550'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_unbounded_non_compact_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_unbounded_non_compact_/formatted.sql new file mode 100644 index 00000000000..47a5828b134 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_window-win_range_unbounded_non_compact_/formatted.sql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('100'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('200'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('250'), b: 1, sum: uint16('550'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/suites/window/win_range_always_empty.cfg b/yql/essentials/tests/sql/suites/window/win_range_always_empty.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_always_empty.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_always_empty.yql b/yql/essentials/tests/sql/suites/window/win_range_always_empty.yql new file mode 100644 index 00000000000..701b7451ce2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_always_empty.yql @@ -0,0 +1,40 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 0|>, + <|a: NULL, b: 1, sum: NULL, count: 0|>, + <|a: uint8('8'), b: 1, sum: NULL, count: 0|>, + <|a: uint8('10'), b: 1, sum: NULL, count: 0|>, + <|a: uint8('11'), b: 1, sum: NULL, count: 0|>, + +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN uint8('1') PRECEDING AND uint8('2') PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/suites/window/win_range_date.cfg b/yql/essentials/tests/sql/suites/window/win_range_date.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_date.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_date.yql b/yql/essentials/tests/sql/suites/window/win_range_date.yql new file mode 100644 index 00000000000..91dd8c07419 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_date.yql @@ -0,0 +1,37 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Date("2017-11-24"), b: 1, count: 0|>, + <|a: Date("2017-11-25"), b: 1, count: 1|>, + <|a: Date("2017-11-26"), b: 1, count: 2|>, + <|a: Date("2017-11-27"), b: 1, count: 3|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval("P3D") PRECEDING AND Interval("P1D") PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_date32.cfg b/yql/essentials/tests/sql/suites/window/win_range_date32.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_date32.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_date32.yql b/yql/essentials/tests/sql/suites/window/win_range_date32.yql new file mode 100644 index 00000000000..747b5df4c00 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_date32.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: Date32("2017-11-25"), b: 1, count: 2|>, + <|a: Date32("2017-11-26"), b: 1, count: 3|>, + <|a: Date32("2017-11-27"), b: 1, count: 4|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND Interval64("P1D") PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_datetime.cfg b/yql/essentials/tests/sql/suites/window/win_range_datetime.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_datetime.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_datetime.yql b/yql/essentials/tests/sql/suites/window/win_range_datetime.yql new file mode 100644 index 00000000000..12a70dfaa4d --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_datetime.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Datetime("2017-11-27T13:22:00Z"), b: 1, count: 0|>, + <|a: Datetime("2017-11-27T13:23:00Z"), b: 1, count: 1|>, + <|a: Datetime("2017-11-27T13:24:00Z"), b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a DESC + RANGE BETWEEN Interval("PT1M") FOLLOWING AND Interval("PT3M") FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, "Got: " || $str(actual_count) || ", but expected: " || $str(count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_datetime64.cfg b/yql/essentials/tests/sql/suites/window/win_range_datetime64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_datetime64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_datetime64.yql b/yql/essentials/tests/sql/suites/window/win_range_datetime64.yql new file mode 100644 index 00000000000..68c2a7561b0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_datetime64.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, + <|a: Datetime64("2017-11-27T13:22:00Z"), b: 1, count: 2|>, + <|a: Datetime64("2017-11-27T13:23:00Z"), b: 1, count: 1|>, + <|a: Datetime64("2017-11-27T13:24:00Z"), b: 1, count: 0|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64("PT1M") FOLLOWING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_double.cfg b/yql/essentials/tests/sql/suites/window/win_range_double.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_double.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_double.yql b/yql/essentials/tests/sql/suites/window/win_range_double.yql new file mode 100644 index 00000000000..5207c207874 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_double.yql @@ -0,0 +1,54 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: double('-10.5'), b: 1, sum1: double('-10.5'), count1: 1, sum2: NULL, count2: 0|>, + <|a: double('-5.0'), b: 1, sum1: double('-15.5'), count1: 2, sum2: NULL, count2: 0|>, + <|a: double('0.0'), b: 1, sum1: double('-5.0'), count1: 2, sum2: double('-5.0'), count2: 1|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum1, + COUNT(*) OVER w1 AS actual_count1, + SUM(a) OVER w2 AS actual_sum2, + COUNT(*) OVER w2 AS actual_count2, + sum1, + count1, + sum2, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN double('10.0') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN double('5.0') PRECEDING AND double('0.5') PRECEDING + ) +); + +$str = ($x) -> { + RETURN CAST($x AS String) ?? 'null'; +}; + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum1, sum1 IS NOT DISTINCT FROM actual_sum1, $str(actual_sum1)), + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(sum2, sum2 IS NOT DISTINCT FROM actual_sum2, $str(actual_sum2)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/suites/window/win_range_float.cfg b/yql/essentials/tests/sql/suites/window/win_range_float.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_float.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_float.yql b/yql/essentials/tests/sql/suites/window/win_range_float.yql new file mode 100644 index 00000000000..ec69c8411a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_float.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: float('1.5'), b: 1, sum: float('1.5'), count: 1|>, + <|a: float('2.0'), b: 1, sum: float('3.5'), count: 2|>, + <|a: float('2.8'), b: 1, sum: float('6.3'), count: 3|>, + <|a: float('5.0'), b: 1, sum: float('5.0'), count: 1|>, + <|a: float('6.0'), b: 1, sum: float('11.0'), count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN float('1.5') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_int16.cfg b/yql/essentials/tests/sql/suites/window/win_range_int16.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int16.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_int16.yql b/yql/essentials/tests/sql/suites/window/win_range_int16.yql new file mode 100644 index 00000000000..26e40e3e285 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int16.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int16('-1000'), b: 1, sum: int16('-1500'), count: 5|>, + <|a: int16('-500'), b: 1, sum: int16('-500'), count: 4|>, + <|a: int16('0'), b: 1, sum: int16('0'), count: 3|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + -a DESC + RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_int32.cfg b/yql/essentials/tests/sql/suites/window/win_range_int32.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int32.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_int32.yql b/yql/essentials/tests/sql/suites/window/win_range_int32.yql new file mode 100644 index 00000000000..a9a15439877 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int32.yql @@ -0,0 +1,52 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int32('-50000'), b: 1, sum1: int32('-50000'), count1: 1, sum2: NULL, count2: 0|>, + <|a: int32('-10000'), b: 1, sum1: int32('-60000'), count1: 2, sum2: int32('-50000'), count2: 1|>, + <|a: int32('0'), b: 1, sum1: int32('-60000'), count1: 3, sum2: int32('-10000'), count2: 1|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum1, + COUNT(*) OVER w1 AS actual_count1, + SUM(a) OVER w2 AS actual_sum2, + COUNT(*) OVER w2 AS actual_count2, + sum1, + count1, + sum2, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int32('50000') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int32('40000') PRECEDING AND int32('10000') PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum1, sum1 IS NOT DISTINCT FROM actual_sum1, $str(actual_sum1)), + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(sum2, sum2 IS NOT DISTINCT FROM actual_sum2, $str(actual_sum2)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_int64.cfg b/yql/essentials/tests/sql/suites/window/win_range_int64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_int64.yql b/yql/essentials/tests/sql/suites/window/win_range_int64.yql new file mode 100644 index 00000000000..8a852fe1b54 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int64.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: int64('-1500000'), count: 5|>, + <|a: NULL, b: 1, sum: int64('-1500000'), count: 5|>, + <|a: int64('-1000000'), b: 1, sum: int64('-1500000'), count: 3|>, + <|a: int64('-500000'), b: 1, sum: int64('-500000'), count: 2|>, + <|a: int64('0'), b: 1, sum: int64('0'), count: 1|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_int64_order_by_multiply.cfg b/yql/essentials/tests/sql/suites/window/win_range_int64_order_by_multiply.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int64_order_by_multiply.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_int64_order_by_multiply.yql b/yql/essentials/tests/sql/suites/window/win_range_int64_order_by_multiply.yql new file mode 100644 index 00000000000..b6027791153 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int64_order_by_multiply.yql @@ -0,0 +1,37 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int64('-1'), b: 1, sum: int64('-3'), count: 2|>, + <|a: int64('-2'), b: 1, sum: int64('-6'), count: 3|>, + <|a: int64('-3'), b: 1, sum: int64('-5'), count: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION BY + b + ORDER BY + a * 5 ASC + RANGE BETWEEN 5l PRECEDING AND 5l FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_int8.cfg b/yql/essentials/tests/sql/suites/window/win_range_int8.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int8.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_int8.yql b/yql/essentials/tests/sql/suites/window/win_range_int8.yql new file mode 100644 index 00000000000..837d951d058 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_int8.yql @@ -0,0 +1,52 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: int8('-8'), b: 1, sum1: int8('-8'), count1: 1, sum2: NULL, count2: 0|>, + <|a: int8('-5'), b: 1, sum1: int8('-13'), count1: 2, sum2: int8('-8'), count2: 1|>, + <|a: int8('0'), b: 1, sum1: int8('-5'), count1: 2, sum2: NULL, count2: 0|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, + <|a: NULL, b: 1, sum1: NULL, count1: 2, sum2: NULL, count2: 2|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum1, + COUNT(*) OVER w1 AS actual_count1, + SUM(a) OVER w2 AS actual_sum2, + COUNT(*) OVER w2 AS actual_count2, + sum1, + count1, + sum2, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int8('5') PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN int8('3') PRECEDING AND int8('2') PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum1, sum1 IS NOT DISTINCT FROM actual_sum1, $str(actual_sum1)), + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(sum2, sum2 IS NOT DISTINCT FROM actual_sum2, $str(actual_sum2)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_interval.cfg b/yql/essentials/tests/sql/suites/window/win_range_interval.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_interval.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_interval.yql b/yql/essentials/tests/sql/suites/window/win_range_interval.yql new file mode 100644 index 00000000000..9c11cfaeccd --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_interval.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Interval("P1DT2H3M4.567888S"), b: 1, count: 2|>, + <|a: Interval("P1DT2H3M4.567889S"), b: 1, count: 1|>, + <|a: Interval("P1DT2H3M4.567890S"), b: 1, count: 0|>, + <|a: NULL, b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval("PT0.000001S") FOLLOWING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_interval64.cfg b/yql/essentials/tests/sql/suites/window/win_range_interval64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_interval64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_interval64.yql b/yql/essentials/tests/sql/suites/window/win_range_interval64.yql new file mode 100644 index 00000000000..0532c304f7f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_interval64.yql @@ -0,0 +1,46 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Interval64("P1DT2H3M4.567888S"), b: 1, count1: 1, count2: 2|>, + <|a: Interval64("P1DT2H3M4.567889S"), b: 1, count1: 2, count2: 2|>, + <|a: Interval64("P1DT2H3M4.567890S"), b: 1, count1: 2, count2: 1|>, + <|a: NULL, b: 1, count1: 2, count2: 2|>, + <|a: NULL, b: 1, count1: 2, count2: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count1, + COUNT(*) OVER w2 AS actual_count2, + count1, + count2, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64("PT0.000001S") PRECEDING AND CURRENT ROW + ), + w2 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND Interval64("PT0.000001S") FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count1, count1 IS NOT DISTINCT FROM actual_count1, $str(actual_count1)), + Ensure(count2, count2 IS NOT DISTINCT FROM actual_count2, $str(actual_count2)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_no_sort_current_row.cfg b/yql/essentials/tests/sql/suites/window/win_range_no_sort_current_row.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_no_sort_current_row.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_no_sort_current_row.yql b/yql/essentials/tests/sql/suites/window/win_range_no_sort_current_row.yql new file mode 100644 index 00000000000..6516912c20b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_no_sort_current_row.yql @@ -0,0 +1,34 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Timestamp("2017-11-27T13:24:00.123454Z"), b: 1, count: 5|>, + <|a: Timestamp("2017-11-27T13:24:00.123455Z"), b: 1, count: 5|>, + <|a: Timestamp("2017-11-27T13:24:00.123456Z"), b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, + <|a: NULL, b: 1, count: 5|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + RANGE BETWEEN CURRENT ROW AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_string_current_row_fail.cfg b/yql/essentials/tests/sql/suites/window/win_range_string_current_row_fail.cfg new file mode 100644 index 00000000000..2bf6b4f432b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_string_current_row_fail.cfg @@ -0,0 +1,2 @@ +xfail +langver 2025.05
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_string_current_row_fail.yql b/yql/essentials/tests/sql/suites/window/win_range_string_current_row_fail.yql new file mode 100644 index 00000000000..4aefb8a695c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_string_current_row_fail.yql @@ -0,0 +1,23 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +/* custom error: Range frame for non numeric expressions is only allowed to be UNBOUNDED PRECEDING AND CURRENT ROW */ + +$data = [ + <|a: "apple", b: 1, count: 1|>, + <|a: "banana", b: 1, count: 2|>, +]; + +SELECT + COUNT(*) OVER w1 AS actual_count, + count, +FROM + AS_TABLE($data) +WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND CURRENT ROW + )
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_string_success.cfg b/yql/essentials/tests/sql/suites/window/win_range_string_success.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_string_success.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_string_success.yql b/yql/essentials/tests/sql/suites/window/win_range_string_success.yql new file mode 100644 index 00000000000..7d6cc348107 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_string_success.yql @@ -0,0 +1,37 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: "apple", b: 1, count: 3|>, + <|a: "banana", b: 1, count: 4|>, + <|a: "cherry", b: 1, count: 6|>, + <|a: "cherry", b: 1, count: 6|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_timestamp.cfg b/yql/essentials/tests/sql/suites/window/win_range_timestamp.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_timestamp.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_timestamp.yql b/yql/essentials/tests/sql/suites/window/win_range_timestamp.yql new file mode 100644 index 00000000000..8615b00c4c8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_timestamp.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Timestamp("2017-11-27T13:24:00.123454Z"), b: 1, count: 2|>, + <|a: Timestamp("2017-11-27T13:24:00.123455Z"), b: 1, count: 3|>, + <|a: Timestamp("2017-11-27T13:24:00.123456Z"), b: 1, count: 4|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND Interval("PT0.000001S") PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_timestamp64.cfg b/yql/essentials/tests/sql/suites/window/win_range_timestamp64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_timestamp64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_timestamp64.yql b/yql/essentials/tests/sql/suites/window/win_range_timestamp64.yql new file mode 100644 index 00000000000..469716d81a5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_timestamp64.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: Timestamp64("2017-11-27T13:24:00.123454Z"), b: 1, count: 1|>, + <|a: Timestamp64("2017-11-27T13:24:00.123455Z"), b: 1, count: 2|>, + <|a: Timestamp64("2017-11-27T13:24:00.123456Z"), b: 1, count: 2|>, + <|a: Timestamp64("2017-11-27T13:24:00.123457Z"), b: 1, count: 2|>, + <|a: Timestamp64("2017-11-27T13:24:00.123458Z"), b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64("PT0.000001S") PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_two_columns_success.cfg b/yql/essentials/tests/sql/suites/window/win_range_two_columns_success.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_two_columns_success.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_two_columns_success.yql b/yql/essentials/tests/sql/suites/window/win_range_two_columns_success.yql new file mode 100644 index 00000000000..53636599ec6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_two_columns_success.yql @@ -0,0 +1,35 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: "apple", c: 1, b: 1, count: 1|>, + <|a: "apple", c: 2, b: 1, count: 2|>, + <|a: "banana", c: 1, b: 1, count: 3|>, + <|a: "banana", c: 2, b: 1, count: 4|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC, c ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_two_columns_with_current_row_fail.cfg b/yql/essentials/tests/sql/suites/window/win_range_two_columns_with_current_row_fail.cfg new file mode 100644 index 00000000000..2bf6b4f432b --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_two_columns_with_current_row_fail.cfg @@ -0,0 +1,2 @@ +xfail +langver 2025.05
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_two_columns_with_current_row_fail.yql b/yql/essentials/tests/sql/suites/window/win_range_two_columns_with_current_row_fail.yql new file mode 100644 index 00000000000..b1f0f491a77 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_two_columns_with_current_row_fail.yql @@ -0,0 +1,25 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +/* custom error: Range frame for multiple expressions is only allowed to be UNBOUNDED PRECEDING AND CURRENT ROW. */ + +$data = [ + <|a: 1, c: 1, b: 1|>, + <|a: 1, c: 2, b: 1|>, + <|a: 2, c: 1, b: 1|>, + <|a: 2, c: 2, b: 1|>, +]; + +SELECT + COUNT(*) OVER w1 AS cnt, +FROM + AS_TABLE($data) +WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC, c ASC + RANGE BETWEEN CURRENT ROW AND CURRENT ROW + ) +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdate.cfg b/yql/essentials/tests/sql/suites/window/win_range_tzdate.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdate.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdate.yql b/yql/essentials/tests/sql/suites/window/win_range_tzdate.yql new file mode 100644 index 00000000000..0c26c710360 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdate.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDate("2017-11-25,Europe/Moscow"), b: 1, count: 1|>, + <|a: TzDate("2017-11-26,Europe/Moscow"), b: 1, count: 2|>, + <|a: TzDate("2017-11-27,Europe/Moscow"), b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval("P1D") PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdate32.cfg b/yql/essentials/tests/sql/suites/window/win_range_tzdate32.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdate32.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdate32.yql b/yql/essentials/tests/sql/suites/window/win_range_tzdate32.yql new file mode 100644 index 00000000000..79dde992734 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdate32.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDate32("2017-11-25,Europe/Moscow"), b: 1, count: 0|>, + <|a: TzDate32("2017-11-26,Europe/Moscow"), b: 1, count: 1|>, + <|a: TzDate32("2017-11-27,Europe/Moscow"), b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64("P3D") PRECEDING AND Interval64("P1D") PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdatetime.cfg b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdatetime.yql b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime.yql new file mode 100644 index 00000000000..4f0f69774ca --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDatetime("2017-11-27T13:22:00,America/Los_Angeles"), b: 1, count: 0|>, + <|a: TzDatetime("2017-11-27T13:23:00,America/Los_Angeles"), b: 1, count: 1|>, + <|a: TzDatetime("2017-11-27T13:24:00,America/Los_Angeles"), b: 1, count: 2|>, + <|a: TzDatetime("2017-11-27T13:22:00,America/Los_Angeles"), b: 2, count: 0|>, + <|a: TzDatetime("2017-11-27T13:23:00,America/Los_Angeles"), b: 2, count: 1|>, + <|a: TzDatetime("2017-11-27T13:24:00,America/Los_Angeles"), b: 3, count: 0|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval("PT3M") PRECEDING AND Interval("PT1M") PRECEDING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdatetime64.cfg b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_tzdatetime64.yql b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime64.yql new file mode 100644 index 00000000000..c310de4ed54 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tzdatetime64.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzDatetime64("2017-11-27T13:22:00,America/Los_Angeles"), b: 1, count: 3|>, + <|a: TzDatetime64("2017-11-27T13:23:00,America/Los_Angeles"), b: 1, count: 3|>, + <|a: TzDatetime64("2017-11-27T13:24:00,America/Los_Angeles"), b: 1, count: 2|>, + <|a: TzDatetime64("2017-11-27T13:22:00,America/Los_Angeles"), b: 2, count: 3|>, + <|a: TzDatetime64("2017-11-27T13:23:00,America/Los_Angeles"), b: 2, count: 3|>, + <|a: TzDatetime64("2017-11-27T13:24:00,America/Los_Angeles"), b: 2, count: 2|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval64("PT1M") PRECEDING AND Interval64("PT3M") FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_tztimestamp.cfg b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_tztimestamp.yql b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp.yql new file mode 100644 index 00000000000..fb506905e6e --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp.yql @@ -0,0 +1,35 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzTimestamp("2017-11-27T13:24:00.123454,GMT"), b: 1, count: 2|>, + <|a: TzTimestamp("2017-11-27T13:24:00.123455,GMT"), b: 1, count: 1|>, + <|a: TzTimestamp("2017-11-27T13:24:00.123456,GMT"), b: 1, count: 0|>, + <|a: NULL, b: 1, count: 1|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN Interval("PT0.000001S") FOLLOWING AND Interval("PT0.000003S") FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_tztimestamp64.cfg b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_tztimestamp64.yql b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp64.yql new file mode 100644 index 00000000000..20c2e518026 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_tztimestamp64.yql @@ -0,0 +1,36 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: TzTimestamp64("2017-11-27T13:24:00.123454,GMT"), b: 1, count: 2|>, + <|a: TzTimestamp64("2017-11-27T13:24:00.123455,GMT"), b: 1, count: 1|>, + <|a: TzTimestamp64("2017-11-27T13:24:00.123459,GMT"), b: 1, count: 1|>, + <|a: NULL, b: 1, count: 2|>, + <|a: NULL, b: 1, count: 2|>, +]; + +$win_result = ( + SELECT + COUNT(*) OVER w1 AS actual_count, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN CURRENT ROW AND Interval64("PT0.000001S") FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint16.cfg b/yql/essentials/tests/sql/suites/window/win_range_uint16.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint16.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint16.yql b/yql/essentials/tests/sql/suites/window/win_range_uint16.yql new file mode 100644 index 00000000000..0a566be4e8c --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint16.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: uint16('100'), b: 1, sum: uint16('100'), count: 3|>, + <|a: uint16('200'), b: 1, sum: uint16('300'), count: 4|>, + <|a: uint16('250'), b: 1, sum: uint16('550'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND uint16("10") FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint32.cfg b/yql/essentials/tests/sql/suites/window/win_range_uint32.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint32.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint32.yql b/yql/essentials/tests/sql/suites/window/win_range_uint32.yql new file mode 100644 index 00000000000..43786333579 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint32.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: uint32('1000'), b: 1, sum: uint32('1000'), count: 1|>, + <|a: uint32('2000'), b: 1, sum: uint32('3000'), count: 2|>, + <|a: uint32('2500'), b: 1, sum: uint32('4500'), count: 2|>, + <|a: uint32('3000'), b: 1, sum: uint32('7500'), count: 3|>, + <|a: uint32('5000'), b: 1, sum: uint32('5000'), count: 1|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN uint32('1000') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint64.cfg b/yql/essentials/tests/sql/suites/window/win_range_uint64.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint64.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint64.yql b/yql/essentials/tests/sql/suites/window/win_range_uint64.yql new file mode 100644 index 00000000000..ae1e9503be4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint64.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: uint64('1000000'), b: 1, sum: uint64('1000000'), count: 3|>, + <|a: uint64('2000000'), b: 1, sum: uint64('3000000'), count: 4|>, + <|a: uint64('3000000'), b: 1, sum: uint64('6000000'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint8.cfg b/yql/essentials/tests/sql/suites/window/win_range_uint8.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint8.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_uint8.yql b/yql/essentials/tests/sql/suites/window/win_range_uint8.yql new file mode 100644 index 00000000000..3a8a713aff8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_uint8.yql @@ -0,0 +1,40 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: NULL, b: 1, sum: NULL, count: 2|>, + <|a: uint8('8'), b: 1, sum: uint8('8'), count: 1|>, + <|a: uint8('10'), b: 1, sum: uint8('10'), count: 1|>, + <|a: uint8('11'), b: 1, sum: uint8('21'), count: 2|>, + +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN uint8('1') PRECEDING AND CURRENT ROW + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +; diff --git a/yql/essentials/tests/sql/suites/window/win_range_unbounded_compact.cfg b/yql/essentials/tests/sql/suites/window/win_range_unbounded_compact.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_unbounded_compact.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_unbounded_compact.yql b/yql/essentials/tests/sql/suites/window/win_range_unbounded_compact.yql new file mode 100644 index 00000000000..b69a3b10c07 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_unbounded_compact.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('100'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('200'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('250'), b: 1, sum: uint16('550'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION COMPACT BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/window/win_range_unbounded_non_compact.cfg b/yql/essentials/tests/sql/suites/window/win_range_unbounded_non_compact.cfg new file mode 100644 index 00000000000..b01e0c8c619 --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_unbounded_non_compact.cfg @@ -0,0 +1 @@ +langver 2025.05 diff --git a/yql/essentials/tests/sql/suites/window/win_range_unbounded_non_compact.yql b/yql/essentials/tests/sql/suites/window/win_range_unbounded_non_compact.yql new file mode 100644 index 00000000000..d77896a087f --- /dev/null +++ b/yql/essentials/tests/sql/suites/window/win_range_unbounded_non_compact.yql @@ -0,0 +1,39 @@ +PRAGMA WindowNewPipeline; +PRAGMA config.flags('OptimizerFlags', 'ForbidConstantDependsOnFuse'); + +$data = [ + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: NULL, b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('100'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('200'), b: 1, sum: uint16('550'), count: 5|>, + <|a: uint16('250'), b: 1, sum: uint16('550'), count: 5|>, +]; + +$win_result = ( + SELECT + SUM(a) OVER w1 AS actual_sum, + COUNT(*) OVER w1 AS actual_count, + sum, + count, + FROM + AS_TABLE($data) + WINDOW + w1 AS ( + PARTITION BY + b + ORDER BY + a ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING + ) +); + +$str = ($x) -> { + return CAST($x as String) ?? "null"; +}; + +SELECT + Ensure(sum, sum IS NOT DISTINCT FROM actual_sum, $str(actual_sum)), + Ensure(count, count IS NOT DISTINCT FROM actual_count, $str(actual_count)) +FROM + $win_result +;
\ No newline at end of file |