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/suites/window | |
| 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/suites/window')
66 files changed, 1296 insertions, 0 deletions
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 |