1CREATE TABLE t1 (name CHAR(10), test double, score DECIMAL(19,4)); 2INSERT INTO t1 VALUES 3('Chun', 0, 3), ('Chun', 0, 7), 4('Kaolin', 0.5, 3), ('Kaolin', 0.6, 7), 5('Kaolin', 0.5, 4), 6('Tatiana', 0.8, 4), ('Tata', 0.8, 4); 7 8--echo # 9--echo # Test invalid syntax 10--echo # 11 12--echo # Order by clause has more than one element 13--error ER_PARSE_ERROR 14select percentile_disc(0.5) within group(order by score,test) over (partition by name) from t1; 15--error ER_PARSE_ERROR 16select percentile_cont(0.5) within group(order by score,test) over (partition by name) from t1; 17 18--echo # Order by clause has no element 19--error ER_PARSE_ERROR 20select percentile_disc(0.5) within group() over (partition by name) from t1; 21--error ER_PARSE_ERROR 22select percentile_cont(0.5) within group() over (partition by name) from t1; 23 24--echo # No parameters to the percentile functions 25--error ER_PARSE_ERROR 26select percentile_disc() within group() over (partition by name) from t1; 27--error ER_PARSE_ERROR 28select percentile_cont() within group() over (partition by name) from t1; 29 30 31 32--echo # 33--echo # Test simple syntax 34--echo # 35 36--sorted_result 37select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1; 38--sorted_result 39select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from t1; 40 41--echo # no partition clause 42--sorted_result 43select name, percentile_disc(0.5) within group(order by score) over () from t1; 44--sorted_result 45select name, percentile_cont(0.5) within group(order by score) over () from t1; 46 47--echo # argument set to null 48--error ER_WRONG_TYPE_OF_ARGUMENT 49select name, percentile_cont(null) within group(order by score) over (partition by name) from t1; 50--error ER_WRONG_TYPE_OF_ARGUMENT 51select name, percentile_disc(null) within group(order by score) over (partition by name) from t1; 52 53--echo #subqueries having percentile functions 54--sorted_result 55select * from ( select name , percentile_cont(0.5) within group ( order by score) over (partition by name ) from t1 ) as t; 56--sorted_result 57select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from t1 ) as t; 58--sorted_result 59select 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; 60 61--echo #disallowed fields in order by 62--error ER_WRONG_TYPE_FOR_PERCENTILE_FUNC 63select score, percentile_cont(0.5) within group(order by name) over (partition by score) from t1; 64 65select score, percentile_disc(0.5) within group(order by name) over (partition by score) from t1; 66 67--echo #parameter value should be in the range of [0,1] 68--error ER_ARGUMENT_OUT_OF_RANGE 69select percentile_disc(1.5) within group(order by score) over (partition by name) from t1; 70--error ER_ARGUMENT_OUT_OF_RANGE 71select percentile_cont(1.5) within group(order by score) over (partition by name) from t1; 72 73--echo #Argument should remain constant for the entire partition 74--error ER_ARGUMENT_NOT_CONSTANT 75select name,percentile_cont(test) within group(order by score) over (partition by name) from t1; 76--error ER_ARGUMENT_NOT_CONSTANT 77select name, percentile_disc(test) within group(order by score) over (partition by name) from t1; 78 79--echo #only numerical types are allowed as argument to percentile functions 80--error ER_WRONG_TYPE_OF_ARGUMENT 81select name, percentile_cont(name) within group(order by score) over (partition by name) from t1; 82--error ER_WRONG_TYPE_OF_ARGUMENT 83select name, percentile_disc(name) within group(order by score) over (partition by name) from t1; 84 85--echo #complete query with partition column 86select 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; 87select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1; 88 89select 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; 90select 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; 91select 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; 92select 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; 93select 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; 94select 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; 95select 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; 96select 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; 97select 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; 98select 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; 99 100select median(score) over (partition by name), percentile_cont(0) within group(order by score) over (partition by name) as c from t1; 101select median(score) over (partition by name), percentile_cont(0.1) within group(order by score) over (partition by name) as c from t1; 102select median(score) over (partition by name), percentile_cont(0.2) within group(order by score) over (partition by name) as c from t1; 103select median(score) over (partition by name), percentile_cont(0.3) within group(order by score) over (partition by name) as c from t1; 104select median(score) over (partition by name), percentile_cont(0.4) within group(order by score) over (partition by name) as c from t1; 105select median(score) over (partition by name), percentile_cont(0.5) within group(order by score) over (partition by name) as c from t1; 106select median(score) over (partition by name), percentile_cont(0.6) within group(order by score) over (partition by name) as c from t1; 107select median(score) over (partition by name), percentile_cont(0.7) within group(order by score) over (partition by name) as c from t1; 108select median(score) over (partition by name), percentile_cont(0.8) within group(order by score) over (partition by name) as c from t1; 109select median(score) over (partition by name), percentile_cont(0.9) within group(order by score) over (partition by name) as c from t1; 110select median(score) over (partition by name), percentile_cont(1) within group(order by score) over (partition by name) as c from t1; 111drop table t1; 112 113--echo # 114--echo # MDEV-13352: MEDIAN window function over a table with virtual column 115--echo # in select with CTE and ORDER BY 116--echo # 117 118CREATE TABLE t1 (f1 int ,f2 int ,f3 int, f4 int, v1 int AS (-f4) virtual); 119INSERT INTO t1(f1,f2,f3,f4) VALUES 120 (1,10,100,10), (7,11,112,15), (3,14,121,12); 121 122WITH CTE AS (SELECT MIN(f3) OVER () FROM t1) 123SELECT MEDIAN(f3) OVER () FROM t1 124ORDER BY f1, f2, f3, f4, v1; 125DROP TABLE t1; 126 127--echo # 128--echo # MDEV-15846: Sever crashed with MEDIAN() window function 129--echo # 130 131CREATE TABLE t1 ( pk int PRIMARY KEY, a1 int, a2 int); 132 133SELECT MEDIAN(`a1`) OVER (), 134 MEDIAN(`a2`) OVER (PARTITION BY `pk`) 135FROM t1; 136DROP TABLE t1; 137 138--echo # 139--echo # MDEV-17137: Syntax errors with VIEW using MEDIAN 140--echo # 141 142CREATE TABLE t1(val int); 143INSERT INTO t1 VALUES (1), (2), (3); 144CREATE VIEW v1 AS SELECT MEDIAN(val) OVER() FROM t1; 145select * from v1; 146select median(val) OVER () FROM t1; 147drop table t1; 148drop view v1; 149 150 151--echo # 152--echo # MDEV-20278 PERCENTILE_DISC() returns a wrong data type 153--echo # 154 155--echo # INT variants 156 157CREATE TABLE t1 (name CHAR(30), star_rating INT); 158INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); 159INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); 160INSERT INTO t1 VALUES ('Lady of the Flies', 1); 161INSERT INTO t1 VALUES ('Lady of the Flies', 2); 162INSERT INTO t1 VALUES ('Lady of the Flies', 5); 163CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) 164 WITHIN GROUP (ORDER BY star_rating) 165 OVER (PARTITION BY name) AS pc FROM t1; 166SHOW CREATE TABLE t2; 167DROP TABLE t2, t1; 168 169--echo # UNSIGNED INT variants 170 171CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating BIGINT UNSIGNED); 172INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000005); 173INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000003); 174INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000001); 175INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000002); 176INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000003); 177CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) 178 WITHIN GROUP (ORDER BY star_rating) 179 OVER (PARTITION BY name) AS pc FROM t1; 180SHOW CREATE TABLE t2; 181SELECT name, pc, HEX(pc) FROM t2 ORDER BY name, pc; 182DROP TABLE t2, t1; 183 184--echo # FLOAT variants 185 186CREATE TABLE t1 (name CHAR(30), star_rating FLOAT); 187INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); 188INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); 189INSERT INTO t1 VALUES ('Lady of the Flies', 1); 190INSERT INTO t1 VALUES ('Lady of the Flies', 2); 191INSERT INTO t1 VALUES ('Lady of the Flies', 5); 192CREATE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) 193 WITHIN GROUP (ORDER BY star_rating) 194 OVER (PARTITION BY name) AS pc FROM t1; 195SHOW CREATE TABLE t2; 196DROP TABLE t2, t1; 197 198--echo # DECIMAL variants 199 200CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating DECIMAL(30,2)); 201INSERT INTO t1 VALUES ('Lord of the Ladybirds', 50000000000); 202INSERT INTO t1 VALUES ('Lord of the Ladybirds', 30000000000); 203INSERT INTO t1 VALUES ('Lady of the Flies', 10000000000); 204INSERT INTO t1 VALUES ('Lady of the Flies', 20000000000); 205INSERT INTO t1 VALUES ('Lady of the Flies', 50000000000); 206CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) 207 WITHIN GROUP (ORDER BY star_rating) 208 OVER (PARTITION BY name) AS pc FROM t1; 209SHOW CREATE TABLE t2; 210SELECT * FROM t2 ORDER BY name, pc; 211DROP TABLE t2, t1; 212 213 214--echo # 215--echo # MDEV-20280 PERCENTILE_DISC() rejects temporal and string input 216--echo # 217 218CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating TIME); 219INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); 220INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); 221INSERT INTO t1 VALUES ('Lady of the Flies', 1); 222INSERT INTO t1 VALUES ('Lady of the Flies', 2); 223INSERT INTO t1 VALUES ('Lady of the Flies', 5); 224SELECT name, PERCENTILE_DISC(0.5) 225 WITHIN GROUP (ORDER BY star_rating) 226 OVER (PARTITION BY name) AS pc FROM t1; 227SELECT name, PERCENTILE_DISC(0) 228 WITHIN GROUP (ORDER BY star_rating) 229 OVER (PARTITION BY name) AS pc FROM t1; 230SELECT name, PERCENTILE_DISC(1) 231 WITHIN GROUP (ORDER BY star_rating) 232 OVER (PARTITION BY name) AS pc FROM t1; 233DROP TABLE t1; 234