summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorVarun Gupta <varunraiko1803@gmail.com>2017-07-17 15:23:21 +0530
committerVarun Gupta <varunraiko1803@gmail.com>2017-11-01 23:13:01 +0530
commitf04426f7271388416b69feeb8252d03f0a29df46 (patch)
tree50d8ff9fe1c53d2e04d5e09116fcd4ba93b2d7f1
parent03ed22326a85c50a67d4c43e9392de8c135cf649 (diff)
downloadmariadb-git-f04426f7271388416b69feeb8252d03f0a29df46.tar.gz
Added more tests for the percentile functions
-rw-r--r--mysql-test/r/win_percentile_cont.result111
-rw-r--r--mysql-test/t/win_percentile_cont.test55
2 files changed, 166 insertions, 0 deletions
diff --git a/mysql-test/r/win_percentile_cont.result b/mysql-test/r/win_percentile_cont.result
new file mode 100644
index 00000000000..61f70892887
--- /dev/null
+++ b/mysql-test/r/win_percentile_cont.result
@@ -0,0 +1,111 @@
+CREATE TABLE student (name CHAR(10), test double, score DECIMAL(19,4));
+INSERT INTO student 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);
+select name, percentile_disc(0.5) within group(order by score) over () from student;
+name percentile_disc(0.5) within group(order by score) over ()
+Chun 4.0000000000
+Chun 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Tatiana 4.0000000000
+Tata 4.0000000000
+select name, percentile_cont(0.5) within group(order by score) over () from student;
+name percentile_cont(0.5) within group(order by score) over ()
+Chun 4.0000000000
+Chun 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Tatiana 4.0000000000
+Tata 4.0000000000
+select name, percentile_cont(null) within group(order by score) over (partition by name) from student;
+name percentile_cont(null) within group(order by score) over (partition by name)
+Chun NULL
+Chun NULL
+Kaolin NULL
+Kaolin NULL
+Kaolin NULL
+Tatiana NULL
+Tata NULL
+select name, percentile_disc(null) within group(order by score) over (partition by name) from student;
+name percentile_disc(null) within group(order by score) over (partition by name)
+Chun NULL
+Chun NULL
+Kaolin NULL
+Kaolin NULL
+Kaolin NULL
+Tatiana NULL
+Tata NULL
+select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from student;
+name c
+Chun 5.0000000000
+Chun 5.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Tatiana 4.0000000000
+Tata 4.0000000000
+select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from student;
+name c
+Chun 3.0000000000
+Chun 3.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Tatiana 4.0000000000
+Tata 4.0000000000
+select * from ( select name , percentile_cont(0.5) within group ( order by score) over (partition by name ) from student ) as t;
+name percentile_cont(0.5) within group ( order by score) over (partition by name )
+Chun 5.0000000000
+Chun 5.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Tatiana 4.0000000000
+Tata 4.0000000000
+select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from student ) as t;
+name percentile_disc(0.5) within group ( order by score) over (partition by name )
+Chun 3.0000000000
+Chun 3.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Kaolin 4.0000000000
+Tatiana 4.0000000000
+Tata 4.0000000000
+select name from student a where (select percentile_disc(0.5) within group (order by score) over (partition by name) from student b limit 1) >= 0.5;
+name
+Chun
+Chun
+Kaolin
+Kaolin
+Kaolin
+Tatiana
+Tata
+select score, percentile_cont(0.5) within group(order by name) over (partition by score) from student;
+ERROR HY000: Numeric datatype is required for Percentile_CONT function
+select score, percentile_disc(0.5) within group(order by name) over (partition by score) from student;
+score percentile_disc(0.5) within group(order by name) over (partition by score)
+3.0000 Chun
+7.0000 Chun
+3.0000 Chun
+7.0000 Chun
+4.0000 Tata
+4.0000 Tata
+4.0000 Tata
+select percentile_disc(0.5) within group(order by score,test) over (partition by name) from student;
+ERROR HY000: Incorrect number of elements in the order list for 'percentile_disc'
+select percentile_cont(0.5) within group(order by score,test) over (partition by name) from student;
+ERROR HY000: Incorrect number of elements in the order list for 'percentile_cont'
+select percentile_disc(1.5) within group(order by score) over (partition by name) from student;
+ERROR HY000: Argument to the percentile functions does not belong to the range [0,1]
+select percentile_cont(1.5) within group(order by score) over (partition by name) from student;
+ERROR HY000: Argument to the percentile functions does not belong to the range [0,1]
+select name,percentile_cont(test) within group(order by score) over (partition by name) from student;
+ERROR HY000: Argument to the percentile functions is not a constant
+select name, percentile_disc(test) within group(order by score) over (partition by name) from student;
+ERROR HY000: Argument to the percentile functions is not a constant
+drop table student;
diff --git a/mysql-test/t/win_percentile_cont.test b/mysql-test/t/win_percentile_cont.test
new file mode 100644
index 00000000000..75fde963b2a
--- /dev/null
+++ b/mysql-test/t/win_percentile_cont.test
@@ -0,0 +1,55 @@
+CREATE TABLE student (name CHAR(10), test double, score DECIMAL(19,4));
+INSERT INTO student 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);
+
+#no partition clause
+select name, percentile_disc(0.5) within group(order by score) over () from student;
+select name, percentile_cont(0.5) within group(order by score) over () from student;
+
+# argument set to null
+select name, percentile_cont(null) within group(order by score) over (partition by name) from student;
+select name, percentile_disc(null) within group(order by score) over (partition by name) from student;
+
+# complete query with partition column
+select name, percentile_cont(0.5) within group(order by score) over (partition by name) as c from student;
+select name, percentile_disc(0.5) within group(order by score) over (partition by name) as c from student;
+
+#subqueries having percentile functions
+
+select * from ( select name , percentile_cont(0.5) within group ( order by score) over (partition by name ) from student ) as t;
+select * from ( select name , percentile_disc(0.5) within group ( order by score) over (partition by name ) from student ) as t;
+select name from student a where (select percentile_disc(0.5) within group (order by score) over (partition by name) from student b limit 1) >= 0.5;
+
+# WITH STORED PROCEDURES
+
+
+#DISALLOWED FIELDS IN ORDER BY CLAUSE
+--error ER_WRONG_TYPE_FOR_PERCENTILE_CONT
+select score, percentile_cont(0.5) within group(order by name) over (partition by score) from student;
+select score, percentile_disc(0.5) within group(order by name) over (partition by score) from student;
+
+# error with 2 order by elements
+
+--error ER_NOT_SINGLE_ELEMENT_ORDER_LIST
+select percentile_disc(0.5) within group(order by score,test) over (partition by name) from student;
+--error ER_NOT_SINGLE_ELEMENT_ORDER_LIST
+select percentile_cont(0.5) within group(order by score,test) over (partition by name) from student;
+
+#parameter value should be in the range of 0 to 1( NEED TO THINK A WAY FOR THIS)
+--error ER_ARGUMENT_OUT_OF_RANGE
+select percentile_disc(1.5) within group(order by score) over (partition by name) from student;
+--error ER_ARGUMENT_OUT_OF_RANGE
+select percentile_cont(1.5) within group(order by score) over (partition by name) from student;
+
+--error ER_ARGUMENT_NOT_CONSTANT
+select name,percentile_cont(test) within group(order by score) over (partition by name) from student;
+--error ER_ARGUMENT_NOT_CONSTANT
+select name, percentile_disc(test) within group(order by score) over (partition by name) from student;
+
+#CHECK TYPE OF THE ARGUMENT, SHOULD BE ONLY NUMERICAL
+#select name, percentile_cont(name) within group(order by score) over (partition by name) from student;
+
+drop table student;