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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
# name: test/sql/window/test_invalid_window.test
# description: Illegal window function
# group: [window]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE empsalary (depname varchar, empno bigint, salary int, enroll_date date)
statement ok
INSERT INTO empsalary VALUES ('develop', 10, 5200, '2007-08-01'), ('sales', 1, 5000, '2006-10-01'), ('personnel', 5, 3500, '2007-12-10'), ('sales', 4, 4800, '2007-08-08'), ('personnel', 2, 3900, '2006-12-23'), ('develop', 7, 4200, '2008-01-01'), ('develop', 9, 4500, '2008-01-01'), ('sales', 3, 4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15')
# GROUP BY window function is not allowed
statement error
SELECT depname, min(salary) OVER (PARTITION BY depname ORDER BY salary, empno) m1 FROM empsalary GROUP BY m1 ORDER BY depname, empno
----
statement error
select row_number() over (range between unbounded following and unbounded preceding);
----
statement error
select row_number() over (range between unbounded preceding and unbounded preceding);
----
# ORDER BY is now implemented for window functions!
query I
select LIST(salary ORDER BY enroll_date, salary) OVER (PARTITION BY depname) FROM empsalary
ORDER BY ALL DESC
----
[6000, 5200, 5200, 4200, 4500]
[6000, 5200, 5200, 4200, 4500]
[6000, 5200, 5200, 4200, 4500]
[6000, 5200, 5200, 4200, 4500]
[6000, 5200, 5200, 4200, 4500]
[5000, 4800, 4800]
[5000, 4800, 4800]
[5000, 4800, 4800]
[3900, 3500]
[3900, 3500]
# GROUPS frame spec is now implemented for window functions!
query I
SELECT sum(i) OVER (ORDER BY i GROUPS 1 PRECEDING)
FROM generate_series(1,10) AS _(i)
ORDER BY i
----
1
3
5
7
9
11
13
15
17
19
# Invalid window names
foreach invalid PARTITION RANGE ROWS GROUPS
statement error
SELECT array_agg(i) OVER (${invalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM generate_series(1,5) AS _(i)
WINDOW ${invalid} AS (ORDER BY i);
----
syntax error at or near
endloop
# Framed non-aggregate functions can't handle EXCLUDE
foreach func lead lag ntile
statement error
SELECT
i,
${func}(i ORDER BY i // 2, i) OVER (
ORDER BY i // 2
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
EXCLUDE TIES
) AS f,
FROM range(10) tbl(i)
ORDER BY i;
----
EXCLUDE is not supported for the window function
statement error
SELECT
i,
lead(i ORDER BY i // 2, i) OVER w AS f,
FROM range(10) tbl(i)
WINDOW w AS (
ORDER BY i // 2
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
EXCLUDE TIES
)
ORDER BY i;
----
EXCLUDE is not supported for the window function
endloop
foreach func row_number rank cume_dist
statement error
SELECT
i,
${func}(ORDER BY i // 2, i) OVER (
ORDER BY i // 2
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
EXCLUDE TIES
) AS f,
FROM range(10) tbl(i)
ORDER BY i;
----
EXCLUDE is not supported for the window function
statement error
SELECT
i,
${func}(ORDER BY i // 2, i) OVER w AS f,
FROM range(10) tbl(i)
WINDOW w AS (
ORDER BY i // 2
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
EXCLUDE TIES
)
ORDER BY i;
----
EXCLUDE is not supported for the window function
endloop
|
