1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
PRAGMA WindowNewPipeline;
$data = [
<|a: 1, expected_count: 0, expected_sum: null|>,
<|a: 2, expected_count: 0, expected_sum: null|>,
<|a: 3, expected_count: 0, expected_sum: null|>,
<|a: 4, expected_count: 0, expected_sum: null|>,
<|a: 5, expected_count: 0, expected_sum: null|>,
];
$win_result = (
SELECT
a,
COUNT(*) OVER w1 AS count_w1,
SUM(a) OVER w2 AS sum_w1,
expected_count,
expected_sum,
FROM
AS_TABLE($data)
WINDOW
w1 AS (
ORDER BY a ASC
RANGE BETWEEN Float("1.0") FOLLOWING AND 0 FOLLOWING
),
w2 AS (
ORDER BY a ASC
RANGE BETWEEN Float("10000000000000000") FOLLOWING AND 10000000000000ul FOLLOWING
)
);
$str = ($x) -> {
return CAST($x as String) ?? "null";
};
-- Verify empty window (left > right)
SELECT
Ensure(count_w1, count_w1 IS NOT DISTINCT FROM expected_count, "count_w1: " || $str(count_w1) || " expected: " || $str(expected_count)),
Ensure(sum_w1, sum_w1 IS NOT DISTINCT FROM expected_sum, "sum_w1: " || $str(sum_w1) || " expected: " || $str(expected_sum)),
FROM
$win_result
;
|
