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/sql2yql | |
| 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/sql2yql')
34 files changed, 1656 insertions, 0 deletions
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 +; |