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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
CREATE TABLE t1 (name CHAR(10), test double, score DECIMAL(19,4));
INSERT INTO t1 VALUES
('Chun', 0, 3), ('Chun', 0, 7),
('Kaolin', 0.5, 3), ('Kaolin', 0.6, 7),
('Kaolin', 0.5, 4),
('Tatiana', 0.8, 4), ('Tata', 0.8, 4);
--echo #
--echo # Test invalid syntax
--echo #
--echo # Order by clause has more than one element
--error ER_PARSE_ERROR
select percentile_disc(0.5) within group(order by score,test) over (partition by name) from t1;
--error ER_PARSE_ERROR
select percentile_cont(0.5) within group(order by score,test) over (partition by name) from t1;
--echo # Order by clause has no element
--error ER_PARSE_ERROR
select percentile_disc(0.5) within group() over (partition by name) from t1;
--error ER_PARSE_ERROR
select percentile_cont(0.5) within group() over (partition by name) from t1;
--echo # No parameters to the percentile functions
--error ER_PARSE_ERROR
select percentile_disc() within group() over (partition by name) from t1;
--error ER_PARSE_ERROR
select percentile_cont() within group() over (partition by name) from t1;
--echo #
--echo # Test simple syntax
--echo #
--sorted_result
select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1;
--sorted_result
select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1;
--echo # no partition clause
--sorted_result
select name, percentile_disc(0.5) within group(order by score) over () from t1;
--sorted_result
select name, percentile_cont(0.5) within group(order by score) over () from t1;
--echo # argument set to null
--error ER_WRONG_TYPE_OF_ARGUMENT
select name, percentile_cont(null) within group(order by score) over (partition by name) from t1;
--error ER_WRONG_TYPE_OF_ARGUMENT
select name, percentile_disc(null) within group(order by score) over (partition by name) from t1;
--disable_view_protocol
--echo #subqueries having percentile functions
--sorted_result
select * from ( select name , percentile_cont(0.5) within group ( order by score) over (partition by name ) from t1 ) as t;
--sorted_result
select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from t1 ) as t;
--enable_view_protocol
--sorted_result
select name from t1 a where (select percentile_disc(0.5) within group (order by score) over (partition by name) from t1 b limit 1) >= 0.5;
--echo #disallowed fields in order by
--error ER_WRONG_TYPE_FOR_PERCENTILE_FUNC
select score, percentile_cont(0.5) within group(order by name) over (partition by score) as result from t1;
select score, percentile_disc(0.5) within group(order by name) over (partition by score) as result from t1;
--echo #parameter value should be in the range of [0,1]
--error ER_ARGUMENT_OUT_OF_RANGE
select percentile_disc(1.5) within group(order by score) over (partition by name) from t1;
--error ER_ARGUMENT_OUT_OF_RANGE
select percentile_cont(1.5) within group(order by score) over (partition by name) from t1;
--echo #Argument should remain constant for the entire partition
--error ER_ARGUMENT_NOT_CONSTANT
select name,percentile_cont(test) within group(order by score) over (partition by name) from t1;
--error ER_ARGUMENT_NOT_CONSTANT
select name, percentile_disc(test) within group(order by score) over (partition by name) from t1;
--echo #only numerical types are allowed as argument to percentile functions
--error ER_WRONG_TYPE_OF_ARGUMENT
select name, percentile_cont(name) within group(order by score) over (partition by name) from t1;
--error ER_WRONG_TYPE_OF_ARGUMENT
select name, percentile_disc(name) within group(order by score) over (partition by name) from t1;
--echo #complete query with partition column
select name,cume_dist() over (partition by name order by score), percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1;
select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.1) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.2) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.3) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.4) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.6) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.7) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.8) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(0.9) within group(order by score) over (partition by name) as c from t1;
select name,cume_dist() over (partition by name order by score) as b, percentile_disc(1) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.1) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.2) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.3) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.4) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.6) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.7) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.8) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(0.9) within group(order by score) over (partition by name) as c from t1;
select median(score) over (partition by name), percentile_cont(1) within group(order by score) over (partition by name) as c from t1;
drop table t1;
--echo #
--echo # MDEV-13352: MEDIAN window function over a table with virtual column
--echo # in select with CTE and ORDER BY
--echo #
CREATE TABLE t1 (f1 int ,f2 int ,f3 int, f4 int, v1 int AS (-f4) virtual);
INSERT INTO t1(f1,f2,f3,f4) VALUES
(1,10,100,10), (7,11,112,15), (3,14,121,12);
WITH CTE AS (SELECT MIN(f3) OVER () FROM t1)
SELECT MEDIAN(f3) OVER () FROM t1
ORDER BY f1, f2, f3, f4, v1;
DROP TABLE t1;
--echo #
--echo # MDEV-15846: Sever crashed with MEDIAN() window function
--echo #
CREATE TABLE t1 ( pk int PRIMARY KEY, a1 int, a2 int);
SELECT MEDIAN(`a1`) OVER (),
MEDIAN(`a2`) OVER (PARTITION BY `pk`)
FROM t1;
DROP TABLE t1;
--echo #
--echo # MDEV-17137: Syntax errors with VIEW using MEDIAN
--echo #
CREATE TABLE t1(val int);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE VIEW v1 AS SELECT MEDIAN(val) OVER() FROM t1;
select * from v1;
select median(val) OVER () FROM t1;
drop table t1;
drop view v1;
--echo #
--echo # MDEV-20278 PERCENTILE_DISC() returns a wrong data type
--echo #
--echo # INT variants
CREATE TABLE t1 (name CHAR(30), star_rating INT);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3);
INSERT INTO t1 VALUES ('Lady of the Flies', 1);
INSERT INTO t1 VALUES ('Lady of the Flies', 2);
INSERT INTO t1 VALUES ('Lady of the Flies', 5);
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2, t1;
--echo # UNSIGNED INT variants
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating BIGINT UNSIGNED);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000005);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000003);
INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000001);
INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000002);
INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000003);
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
SHOW CREATE TABLE t2;
SELECT name, pc, HEX(pc) FROM t2 ORDER BY name, pc;
DROP TABLE t2, t1;
--echo # FLOAT variants
CREATE TABLE t1 (name CHAR(30), star_rating FLOAT);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3);
INSERT INTO t1 VALUES ('Lady of the Flies', 1);
INSERT INTO t1 VALUES ('Lady of the Flies', 2);
INSERT INTO t1 VALUES ('Lady of the Flies', 5);
CREATE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2, t1;
--echo # DECIMAL variants
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating DECIMAL(30,2));
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 50000000000);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 30000000000);
INSERT INTO t1 VALUES ('Lady of the Flies', 10000000000);
INSERT INTO t1 VALUES ('Lady of the Flies', 20000000000);
INSERT INTO t1 VALUES ('Lady of the Flies', 50000000000);
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
SHOW CREATE TABLE t2;
SELECT * FROM t2 ORDER BY name, pc;
DROP TABLE t2, t1;
--echo #
--echo # MDEV-20280 PERCENTILE_DISC() rejects temporal and string input
--echo #
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating TIME);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5);
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3);
INSERT INTO t1 VALUES ('Lady of the Flies', 1);
INSERT INTO t1 VALUES ('Lady of the Flies', 2);
INSERT INTO t1 VALUES ('Lady of the Flies', 5);
SELECT name, PERCENTILE_DISC(0.5)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
SELECT name, PERCENTILE_DISC(0)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
SELECT name, PERCENTILE_DISC(1)
WITHIN GROUP (ORDER BY star_rating)
OVER (PARTITION BY name) AS pc FROM t1;
DROP TABLE t1;
|
