mysql-test/main/win_percentile.test

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;

--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;
--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) from t1;

select score,  percentile_disc(0.5)  within group(order by name) over (partition by score) 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;